Laravel 웅변 쿼리에서 200만 행까지 시간이 오래 걸린다

Laravel 웅변 쿼리에서 200만 행까지 시간이 오래 걸린다

200만 행이 있는 테이블에서 데이터를 가져와야 합니다.웅변적인 쿼리는 다음과 같습니다.

$imagesData = Images::whereIn('file_id', $fileIds)
                    ->with('image.user')
                    ->with('file')
                    ->orderBy('created_at', 'DESC')
                    ->simplePaginate(12);

그$fileIds사용되는 배열whereIn에는 100 또는 1000의 파일 ID를 포함할 수 있습니다.

위의 쿼리는 작은 테이블에서 정상적으로 동작합니다.하지만 200만 개가 넘는 생산 현장에서는Images15초 이상 걸려야 답장을 받을 수 있습니다.저는 라라벨을 API로만 사용합니다.

나는 이 주제에 대한 다른 토론들을 읽었다.나는 변했다paginate()로.simplePaginate()어떤 사람들은 아마 이 모든 것들을DB::에게 질문하다.whereRaw보다 효과가 있을지도 모른다whereIn처리 중 php의 PDO가 원인일 수 있다는 의견도 있습니다.whereIn또, 일부에서는,Images::whereIn이미 써놨어요.

저는 MariaDB를 사용하고 있으며, InnoDB는 db 엔진으로 RAM에 로드되어 있습니다.SQL 쿼리는 다른 모든 쿼리에 대해 잘 수행되지만 이와 같이 큰 테이블에서 데이터를 수집해야 하는 쿼리에만 시간이 걸립니다.

테이블에 수백만 개의 행이 있을 때 쿼리 응답을 몇 초로 줄일 수 있도록 위의 라벨 쿼리를 최적화하려면 어떻게 해야 합니까?

데이터를 특정 열로 분할하는 인덱싱이 필요합니다.접속하고 있습니다.file_id그리고.created_at따라서 이 지수는 퍼포먼스에 도움이 됩니다.

$table->index(['file_id', 'created_at']);

인덱싱하면 삽입 시간이 길어지고 쿼리에 이상한 실행 계획이 생길 수 있습니다.SQL을 사용하는 경우EXPLAIN쿼리 실행 후 이전 쿼리에서 문제가 해결되었는지 확인할 수 있습니다.

다음은 페이지 로드 속도를 높이기 위해 수행된 절차에 대한 업데이트입니다.

1. 이렇게 느린 쿼리의 진짜 원인은 위의 특정 쿼리뿐만이 아닙니다.위의 쿼리가 데이터를 가져오면 php는 데이터를 반복하고 그 안에서 서브쿼리를 수행하여 무언가를 확인합니다.이 쿼리는filename각 반복 중에 데이터를 검색합니다.부터filename스트링이며 인덱스가 되어 있지 않습니다.각 서브패킷이 서브패킷 내의 150만 행을 기어다니기 때문에 컨트롤러의 응답 시간이 너무 오래 걸렸습니다.foreach루프. 이 서브쿼리를 제거하자 로딩 시간이 많이 단축되었습니다.

2. 두 번째로 인덱스를 추가했습니다.file_id그리고.created_at위의 @mrn과 @ceejayoz가 제안한 바와 같이.다음과 같은 마이그레이션 파일을 만들었습니다.

   Schema::table('images', function (Blueprint $table) {
       $table->index('file_id', 'created_at');
   });        
   

3. PHP 스크립트를 한층 더 최적화.다음을 사용하여 검색을 수행하는 모든 쿼리를 제거했습니다.fileNames 이렇게 요.id결과를 가져옵니다.이로 인해 앱 전체에 큰 차이가 생겼고, 피크 시간대에 CPU 작업이 줄어들어 서버 속도가 향상되었습니다.

4. 마지막으로 다음 절차를 수행하여 서버를 최적화했습니다.

   • yum 업데이트를 완료했습니다.
   • 갱신된 라이트스피드
   • 일부 캐싱 모듈 및 EA-PHP 7.3 설치 완료 Apache 프로파일 조정
   • 갱신된 cPanel
   • 서버가 서버 리소스를 더 많이 사용할 수 있도록 Mysql을 조정했습니다.

위의 단계를 모두 수정한 결과 로드 속도에서 큰 차이를 발견했습니다.결과는 다음과 같습니다.

이전:

그 후:

이에 대해 의견을 주신 모든 분들께 감사드립니다.당신의 코멘트가 위의 모든 단계를 수행하는 데 도움이 되었고, 그 결과는 유익했습니다.정말 감사합니다.

언급URL : https://stackoverflow.com/questions/58903431/laravel-eloquent-query-to-2-million-rows-takes-long-time