특정 테이블에서 왼쪽 조인 속도가 매우 느립니다.

특정 테이블에서 왼쪽 조인 속도가 매우 느립니다.

48GB DDR3를 탑재한 듀얼 Xeon L5630에서 PHP 7.0-FPM 및 MariaDB 10.0.27을 탑재한 Ubuntu 16.04를 실행하고 있는 경우 60초 동안 쿼리가 매우 느려지는 이유를 찾을 수 없습니다.

SELECT v.video_id, v.user_id, v.title, v.slug, v.rating, v.rated_by,
                          v.duration, v.thumb, v.total_views, v.total_comments, v.add_time,
                          v.view_time, v.status, v.source_id, v.orientation, v.thumbs,
                          v.featured, v.flagged, 
                          u.username, 
                          s.name, 
                          f.reason,
                          GROUP_CONCAT(c.name) AS categories
                   FROM video AS v
                   LEFT JOIN video_flags AS f ON (f.video_id = v.video_id)
                   LEFT JOIN video_sources AS s ON (s.source_id = v.source_id)
                   LEFT JOIN user AS u ON (u.user_id = v.user_id)
                   LEFT JOIN video_category AS vc ON (vc.video_id = v.video_id)
                   LEFT JOIN video_categories AS c ON (c.category_id = vc.category_id) GROUP BY v.video_id ORDER BY v.video_id DESC LIMIT 10

video_flags의 f.reason 필드와 왼쪽 Join을 코멘트할 때 쿼리는 152ms밖에 걸리지 않기 때문에 video_flags 테이블에 문제가 있다고 특정했습니다.video_flags 테이블에는 video_id에 대한 인덱스가 있으며 두 테이블 INT(11)의 필드 유형은 동일합니다.

설명 선택 실행 시 다음 정보가 반환됩니다.

+------+-------------+-------+--------+---------------+----------+---------+----------------------------+---------+-------------------------------------------------+
| id   | select_type | table | type   | possible_keys | key      | key_len | ref                        | rows    | Extra                                           |
+------+-------------+-------+--------+---------------+----------+---------+----------------------------+---------+-------------------------------------------------+
|    1 | SIMPLE      | v     | ALL    | NULL          | NULL     | NULL    | NULL                       | 1219933 | Using temporary; Using filesort                 |
|    1 | SIMPLE      | f     | ALL    | video_id      | NULL     | NULL    | NULL                       |       1 | Using where; Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | s     | eq_ref | PRIMARY       | PRIMARY  | 4       | adb_network.v.source_id    |       1 |                                                 |
|    1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY  | 4       | adb_network.v.user_id      |       1 |                                                 |
|    1 | SIMPLE      | vc    | ref    | video_id      | video_id | 4       | adb_network.v.video_id     |       2 | Using index                                     |
|    1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY  | 4       | adb_network.vc.category_id |       1 | Using where                                     |
+------+-------------+-------+--------+---------------+----------+---------+----------------------------+---------+-------------------------------------------------+

여기서 놓치고 있는 것이 무엇인지 모르겠습니다.처음에는 video_flags 테이블이 비어 있어야 한다고 생각했습니다.그 후 레코드를 추가했는데 쿼리가 빠르게(200ms) 되었습니다만, 문제가 재발해, 쿼리가 다시 완료되기까지 오랜 시간이 걸립니다.

어떤 도움이라도 감사합니다.

---

업데이트: @somnium의 f.reason 컬럼 없이 설명 선택 추가:

+------+-------------+-------+--------+---------------+----------+---------+----------------------------+------+-------------+
| id   | select_type | table | type   | possible_keys | key      | key_len | ref                        | rows | Extra       |
+------+-------------+-------+--------+---------------+----------+---------+----------------------------+------+-------------+
|    1 | SIMPLE      | v     | index  | NULL          | PRIMARY  | 4       | NULL                       |    5 |             |
|    1 | SIMPLE      | f     | ref    | video_id      | video_id | 4       | adb_network.v.video_id     |    1 | Using index |
|    1 | SIMPLE      | s     | eq_ref | PRIMARY       | PRIMARY  | 4       | adb_network.v.source_id    |    1 |             |
|    1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY  | 4       | adb_network.v.user_id      |    1 |             |
|    1 | SIMPLE      | vc    | ref    | video_id      | video_id | 4       | adb_network.v.video_id     |    2 | Using index |
|    1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY  | 4       | adb_network.vc.category_id |    1 | Using where |
+------+-------------+-------+--------+---------------+----------+---------+----------------------------+------+-------------+

---

솔루션:@somnium의 제안대로 저는 a를 추가해 보았습니다.FORCE INDEX에서video_id컬럼 및 이로 인해 쿼리 시간이 60초에서 272ms로 단축되었습니다.조인 중에 인덱스가 손실되는 이유는 알 수 없지만 문제는 해결되었습니다.감사합니다.

SELECT v.video_id, v.user_id, v.title, v.slug, v.rating, v.rated_by,
                              v.duration, v.thumb, v.total_views, v.total_comments, v.add_time,
                              v.view_time, v.status, v.source_id, v.orientation, v.thumbs,
                              v.featured, v.flagged, 
                              u.username, 
                              s.name, 
                              f.reason,
                              GROUP_CONCAT(c.name) AS categories
                       FROM video v
                       LEFT JOIN video_flags f FORCE INDEX FOR JOIN (video_id) ON (f.video_id = v.video_id)
                       LEFT JOIN video_sources s ON (s.source_id = v.source_id) 
                       LEFT JOIN user u ON (u.user_id = v.user_id)
                       LEFT JOIN video_category vc ON (vc.video_id = v.video_id)
                       LEFT JOIN video_categories c ON (c.category_id = vc.category_id) GROUP BY v.video_id ORDER BY v.video_id DESC LIMIT 10

실수로 큰 테이블에서 전체 테이블 스캔이 발생했습니다.videos잠재적인 문제 목록은 MySQL 문서에서 확인할 수 있습니다.

잠재적인 문제

키가 없습니다.

f.reason이 없는 설명을 보면 옵티마이저는 다음 명령을 무시합니다.video_flags테이블. MySQL/MariaDB는 모든 인덱스를 완전히 활용할 수 있습니다.

추가할 때f.reason, MySQL이 일치해야 합니다.v.video_id = f.video_id.~하듯이video_flags1개의 행이 있으며 MySQL은 취득을 시도합니다.v.video_id모든 엔트리에 대해video에 대한 인덱스가 없는 것 같습니다.v.video_id따라서 MySQL은 풀스캔을 해야 합니다.videos얻기 위해 디스크/메모리의 테이블video_id그 결과 1219933 행의 취득이 가능하게 됩니다(의 5 행에 비해).explain select없이.video_flags).

낮은 카디널리티

또 다른 잠재적인 문제는 낮은 카디널리티입니다만, 옵티마이저가 이것을 망치는 원인이 무엇인지 잘 모르겠습니다.

MySQL 문서:

다른 열을 통해 카디널리티가 낮은 키(많은 행이 키 값과 일치)를 사용하고 있습니다.이 경우 MySQL은 키를 사용하여 많은 키 검색을 수행하고 테이블 스캔이 더 빠를 것으로 가정합니다.

로는 '1-2'는 '의 카디널리티가 낮기 입니다.video_flags이 MySQL 검색에서 전체 하게 될 수 .videos좌파 Join(당신은 항상 왼쪽에서 모든 값이 필요할 것이다)때문에.이 시점에서 완전한 테이블 스캔이 더 잘 결정한다..video_id왜냐하면 기수 높다.할 수 있습니다.FORCE INDEX★★★★★★ 。

잠재적인 해결책

이 때 인덱스를 해 보세요.v.video_id을 사용하다을 꼼꼼히 .explain selects는 지수 갑자기 익숙하지 않다 찾기 위해서.: ★NULL★★★★★★에possible_keysv츠키노

try 를 사용해보겠습니다.FORCE INDEX.

도움이 됐으면 좋겠다.

계획 A: 이 방법이 더 잘 작동하는지 확인합니다.(필요한 10개의 video_ids를 얻기 위해 JOINING 또는 GROUPING을 모두 수행할 필요는 없을 것 같습니다.)

SELECT ...  -- as before
    FROM (
        SELECT video_id
            FROM video
            ORDER BY video_id DESC
            LIMIT 10 ) AS v1
    JOIN video AS v USING (video_id)
    LEFT JOIN ...  -- as before
    ...
    ORDER BY video_id DESC;  -- no GROUP BY or LIMIT here

플랜 B: 왼쪽 조인을 서브쿼리로 변경

s.name,
LEFT JOIN video_sources AS s ON (s.source_id = v.source_id)

-->

( SELECT name FROM video_sources WHERE source_id = v.source_id ) AS name,

다른 한 줄 값과 왼쪽 결합에 대해서도 마찬가지입니다.

언급URL : https://stackoverflow.com/questions/39745267/extremely-slow-left-join-on-specific-table