Laravel 5.1에서 Laravel 5.8로 업그레이드한 후 whereHas()가 느려짐

Laravel 5.1에서 Laravel 5.8로 업그레이드한 후 whereHas()가 느려짐

새로운 5.8 프로젝트를 셋업하고 파일을 복사하여 Laravel 5.1에서 Laravel 5.8로 앱을 전환하여 여기저기 수정을 가했습니다.

문제는 where에 대한 쿼리가 매우 느려졌다는 것입니다.

다음은 코드 예시입니다.

Article::whereHas('categories', function ($category) {
            $category->where('link', 'foto');
        })
        ->active()
        ->recent()
        ->take(3)
        ->get();

이 코드는 Larabel 5.1에서 다음 쿼리를 생성하여 0.05~0.07초 만에 완료됩니다.

SELECT *
FROM `articles`
WHERE `articles`.`deleted_at` IS NULL
  AND
    (SELECT count(*)
     FROM `categories`
     INNER JOIN `article_category` 
       ON `categories`.`id` = `article_category`.`category_id`
     WHERE `article_category`.`article_id` = `articles`.`id`
       AND `link` = 'foto'
       AND `categories`.`deleted_at` IS NULL) >= 1
ORDER BY IFNULL(published_at, created_at) DESC
LIMIT 3

그 이유는 다음과 같습니다.

+------+--------------------+------------------+------+--------------------------------------------------------------------------+-------------------------------------+---------+-----------------+------+----------+------------------------------------+
| id   | select_type        | table            | type | possible_keys                                                            | key                                 | key_len | ref             | rows | filtered | Extra                              |
+------+--------------------+------------------+------+--------------------------------------------------------------------------+-------------------------------------+---------+-----------------+------+----------+------------------------------------+
|    1 | PRIMARY            | articles         | ALL  | NULL                                                                     | NULL                                | NULL    | NULL            | 4846 |   100.00 | Using where; Using filesort        |
|    2 | DEPENDENT SUBQUERY | categories       | ref  | PRIMARY,categories_link_index                                            | categories_link_index               | 767     | const           |    1 |   100.00 | Using index condition; Using where |
|    2 | DEPENDENT SUBQUERY | article_category | ref  | article_category_category_id_foreign,article_category_article_id_foreign | article_category_article_id_foreign | 4       | lcf.articles.id |    1 |   100.00 | Using where                        |
+------+--------------------+------------------+------+--------------------------------------------------------------------------+-------------------------------------+---------+-----------------+------+----------+------------------------------------+

Larabel 5.8에서는 10-13초 동안 다음 쿼리를 생성합니다.

SELECT *
FROM `articles`
WHERE EXISTS
    (SELECT *
     FROM `categories`
     INNER JOIN `article_category` 
       ON `categories`.`id` = `article_category`.`category_id`
     WHERE `articles`.`id` = `article_category`.`article_id`
       AND `link` = 'foto'
       AND `categories`.`deleted_at` IS NULL)
  AND `articles`.`deleted_at` IS NULL
ORDER BY IFNULL(published_at, created_at) DESC
LIMIT 3

그리고 이렇게 설명하겠습니다.

+------+--------------+------------------+------+--------------------------------------------------------------------------+--------------------------------------+---------+-------------------+------+----------+------------------------------------+
| id   | select_type  | table            | type | possible_keys                                                            | key                                  | key_len | ref               | rows | filtered | Extra                              |
+------+--------------+------------------+------+--------------------------------------------------------------------------+--------------------------------------+---------+-------------------+------+----------+------------------------------------+
|    1 | PRIMARY      | <subquery2>      | ALL  | distinct_key                                                             | NULL                                 | NULL    | NULL              |  107 |   100.00 | Using temporary; Using filesort    |
|    1 | PRIMARY      | articles         | ALL  | PRIMARY                                                                  | NULL                                 | NULL    | NULL              | 4846 |    75.01 | Using where                        |
|    2 | MATERIALIZED | categories       | ref  | PRIMARY,categories_link_index                                            | categories_link_index                | 767     | const             |    1 |   100.00 | Using index condition; Using where |
|    2 | MATERIALIZED | article_category | ref  | article_category_category_id_foreign,article_category_article_id_foreign | article_category_category_id_foreign | 4       | lcf.categories.id |  107 |   100.00 |                                    |
+------+--------------+------------------+------+--------------------------------------------------------------------------+--------------------------------------+---------+-------------------+------+----------+------------------------------------+

동일한 서버, 동일한 MariaDB 10.2.24 데이터베이스에서 두 코드베이스를 모두 실행했습니다.데이터 집합 크기는 피벗에 약 6,000개의 기사, 80개의 카테고리 및 10,000개의 레코드로 구성됩니다.

여기서 뭘 해야 하죠?지금까지 코드베이스에서 이 문제로 어려움을 겪고 있는 쿼리를 10개 이상 발견했습니다.어떻게 해서든 스위치를 설정해서 모두 오래된 방법으로 존재 여부를 확인할 수 있을까요?아니면 모든 질문에서 계획을 개선하도록 지시해야 할까요?

갱신하다

제가 방금 깨달은 건 만약에whereHas(..., '>', 0)오래된 쿼리를 거의 얻을 수 있습니다(실제로).WHERE (SELECT COUNT...) > 0이전 퍼포먼스와 함께)를 사용합니다.하지만,whereHas(..., '>=', 1)문의할 수 있는 범위가 축소됩니다.EXISTS각각의 쿼리를 편집하지 않고 이 동작을 앱 전체로 전환할 수 있을지 의문입니다.

코멘트에 대한 답변

기사 색인

+----------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| articles |          0 | PRIMARY                    |            1 | id          | A         |        4846 |     NULL | NULL   |      | BTREE      |         |               |
| articles |          1 | articles_author_id_foreign |            1 | author_id   | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

의 인덱스article_category

+------------------+------------+--------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name                             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+--------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article_category |          0 | PRIMARY                              |            1 | id          | A         |        9676 |     NULL | NULL   |      | BTREE      |         |               |
| article_category |          1 | article_category_category_id_foreign |            1 | category_id | A         |          90 |     NULL | NULL   |      | BTREE      |         |               |
| article_category |          1 | article_category_article_id_foreign  |            1 | article_id  | A         |        9676 |     NULL | NULL   |      | BTREE      |         |               |
+------------------+------------+--------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

예시를 실행하기 위한 데이터는 https://gist.github.com/tontonsb/b97bc33066a67e9d8bc3654f2c01c103 에서 찾을 수 있습니다.

이 동작은 빨라지지만 아직 2.8~0.07초이기 때문에 적어도 MariaDB 10.2.24에서는 문제를 명확하게 알 수 있습니다.아마 다른 열과 인덱스를 삭제했기 때문에 속도가 향상되었을 것입니다.

이것을 시험해 보세요.

• mpyw/elookent-has-by-non-dependent-subquery: has() 및 whereHas() 제약조건을 비의존 서브쿼리로 변환합니다.

$articles = Article::query()
    ->hasByNonDependentSubquery('categories', function ($category) {
        $category->where('link', 'foto');
    })
    ->active()
    ->recent()
    ->take(3)
    ->get();

언급URL : https://stackoverflow.com/questions/57230931/slow-wherehas-after-upgrade-from-laravel-5-1-to-laravel-5-8