はじめに
本記事では MySQL で JOIN を行うクエリをインデックスで高速化する方法について、調べた内容をまとめます。
Where狙いのキー、order by狙いのキーを大いに参考にしました。ありがとうございます。
検証には MySQL が公式で用意してくれているデータを使用します。
環境
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.30 |
+-----------+
1 row in set (0.01 sec)検証
以下のクエリを高速化します。
テーブルを JOIN し、ORDER BY した結果の上位5件を取得するクエリです。
EXPLAIN ANALYZE
SELECT
*
FROM
employees emp
INNER JOIN
salaries sal
ON emp.emp_no = sal.emp_no
INNER JOIN
dept_emp demp
ON demp.emp_no = emp.emp_no
WHERE
demp.dept_no = 'd005'
ORDER BY
salary DESC
LIMIT 5
;インデックスを張らない場合
まずはインデックスを張らない場合の実行計画を確認します。
EXPLAIN ANALYZE
SELECT
*
FROM
employees emp
INNER JOIN
salaries sal
ON emp.emp_no = sal.emp_no
INNER JOIN
dept_emp demp
ON demp.emp_no = emp.emp_no
WHERE
demp.dept_no = 'd005'
ORDER BY
salary DESC
LIMIT 5
;
-- -> Limit: 5 row(s) (actual time=1467.729..1467.730 rows=5 loops=1)
-- -> Sort: sal.salary DESC, limit input to 5 row(s) per chunk (actual time=1467.718..1467.719 rows=5 loops=1)
-- -> Stream results (cost=286932.14 rows=1395387) (actual time=3.773..1333.169 rows=810026 loops=1)
-- -> Nested loop inner join (cost=286932.14 rows=1395387) (actual time=3.750..945.108 rows=810026 loops=1)
-- -> Nested loop inner join (cost=87695.67 rows=148054) (actual time=3.221..496.062 rows=85707 loops=1)
-- -> Index lookup on demp using dept_no (dept_no='d005'), with index condition: (demp.dept_no = 'd005') (cost=15573.65 rows=148054) (actual time=2.870..209.743 rows=85707 loops=1)
-- -> Single-row index lookup on emp using PRIMARY (emp_no=demp.emp_no) (cost=0.39 rows=1) (actual time=0.003..0.003 rows=1 loops=85707)
-- -> Index lookup on sal using PRIMARY (emp_no=demp.emp_no) (cost=0.40 rows=9) (actual time=0.004..0.005 rows=9 loops=85707)インデックスを張った場合
salaries テーブルの salary に対してインデックスを貼ります。
ALTER TABLE salaries ADD INDEX salary_idx(salary);
EXPLAIN ANALYZE
SELECT
*
FROM
employees emp
INNER JOIN
salaries sal
ON emp.emp_no = sal.emp_no
INNER JOIN
dept_emp demp
ON demp.emp_no = emp.emp_no
WHERE
demp.dept_no = 'd005'
ORDER BY
salary DESC
LIMIT 5
;
-- -> Limit: 5 row(s) (actual time=1473.754..1473.755 rows=5 loops=1)
-- -> Sort: sal.salary DESC, limit input to 5 row(s) per chunk (actual time=1473.752..1473.753 rows=5 loops=1)
-- -> Stream results (cost=273073.22 rows=1395387) (actual time=8.863..1345.495 rows=810026 loops=1)
-- -> Nested loop inner join (cost=273073.22 rows=1395387) (actual time=8.844..959.352 rows=810026 loops=1)
-- -> Nested loop inner join (cost=87401.91 rows=148054) (actual time=7.143..553.846 rows=85707 loops=1)
-- -> Index lookup on demp using dept_no (dept_no='d005'), with index condition: (demp.dept_no = 'd005') (cost=15530.55 rows=148054) (actual time=7.014..202.380 rows=85707 loops=1)
-- -> Single-row index lookup on emp using PRIMARY (emp_no=demp.emp_no) (cost=0.39 rows=1) (actual time=0.004..0.004 rows=1 loops=85707)
-- -> Index lookup on sal using PRIMARY (emp_no=demp.emp_no) (cost=0.31 rows=9) (actual time=0.003..0.004 rows=9 loops=85707)インデックスが使用されていない。。
駆動表の位置を変えてみる
MySQL の JOIN はNested Loop Join アルゴリズムを使用しているため、インデックスを ORDER BY で使用する場合は、そのテーブルが外部表である必要があるようです。
なので salaries テーブルを外部表にして再度実行計画を確認します。
EXPLAIN ANALYZE
SELECT
*
FROM
salaries sal
INNER JOIN
employees emp
ON emp.emp_no = sal.emp_no
INNER JOIN
dept_emp demp
ON demp.emp_no = emp.emp_no
WHERE
demp.dept_no = 'd005'
ORDER BY
salary DESC
LIMIT 5
;
-- -> Limit: 5 row(s) (actual time=1430.325..1430.326 rows=5 loops=1)
-- -> Sort: sal.salary DESC, limit input to 5 row(s) per chunk (actual time=1430.324..1430.325 rows=5 loops=1)
-- -> Stream results (cost=267469.77 rows=1395387) (actual time=3.617..1304.464 rows=810026 loops=1)
-- -> Nested loop inner join (cost=267469.77 rows=1395387) (actual time=3.593..915.731 rows=810026 loops=1)
-- -> Nested loop inner join (cost=81392.98 rows=148054) (actual time=1.774..495.060 rows=85707 loops=1)
-- -> Index lookup on demp using dept_no (dept_no='d005'), with index condition: (demp.dept_no = 'd005') (cost=15537.36 rows=148054) (actual time=1.535..206.419 rows=85707 loops=1)
-- -> Single-row index lookup on emp using PRIMARY (emp_no=demp.emp_no) (cost=0.34 rows=1) (actual time=0.003..0.003 rows=1 loops=85707)
-- -> Index lookup on sal using PRIMARY (emp_no=demp.emp_no) (cost=0.31 rows=9) (actual time=0.003..0.004 rows=9 loops=85707)先ほどと変わらず、インデックスが使用されていない。。
SQL で JOIN の順序を変更しても、実際に JOIN される順序はオプティマイザ側で判断して決定するようです。
なので STRAIGHT_JOIN を使用して salaries テーブル外部表に指定します。
EXPLAIN ANALYZE
SELECT
*
FROM
salaries sal
STRAIGHT_JOIN
employees emp
ON emp.emp_no = sal.emp_no
STRAIGHT_JOIN
dept_emp demp
ON demp.emp_no = emp.emp_no
WHERE
demp.dept_no = 'd005'
ORDER BY
salary DESC
LIMIT 5
;
-- -> Limit: 5 row(s) (cost=1943361.02 rows=5) (actual time=47.477..67.759 rows=5 loops=1)
-- -> Nested loop inner join (cost=1943361.02 rows=5) (actual time=47.462..67.743 rows=5 loops=1)
-- -> Nested loop inner join (cost=1053197.51 rows=5) (actual time=42.257..61.928 rows=524 loops=1)
-- -> Index scan on sal using salary_idx (reverse) (cost=0.00 rows=5) (actual time=42.179..54.838 rows=524 loops=1)
-- -> Single-row index lookup on emp using PRIMARY (emp_no=sal.emp_no) (cost=0.37 rows=1) (actual time=0.013..0.013 rows=1 loops=524)
-- -> Filter: (demp.dept_no = 'd005') (cost=0.31 rows=1) (actual time=0.011..0.011 rows=0 loops=524)
-- -> Single-row index lookup on demp using PRIMARY (emp_no=sal.emp_no, dept_no='d005') (cost=0.31 rows=1) (actual time=0.010..0.010 rows=0 loops=524)インデックスが使用され、実行時間が短縮されたことを確認できました。
オプティマイザヒントを使用する
上記では STRAIGHT_JOIN を使用して外部表を指定しましたが、MySQL8.0 以降ではオプティマイザヒント)という機能を利用することでオプティマイザを制御できるようです。
以下では JOIN_PREFIX を使用し結合順序が最初のテーブルを指定しています。なお JOIN_PREFIX はあくまでもヒントなので、オプティマイザが不要と判断した場合は指定した順序にならないようです。
(JOIN_FIXED_ORDER を使用すれば結合順序を強制することもできます。)
EXPLAIN ANALYZE
SELECT /*+ JOIN_PREFIX(sal) */
*
FROM
employees emp
INNER JOIN
salaries sal
ON emp.emp_no = sal.emp_no
INNER JOIN
dept_emp demp
ON demp.emp_no = emp.emp_no
WHERE
demp.dept_no = 'd005'
ORDER BY
salary DESC
LIMIT 5
;
-- -> Limit: 5 row(s) (cost=1943361.02 rows=5) (actual time=19.674..23.996 rows=5 loops=1)
-- -> Nested loop inner join (cost=1943361.02 rows=5) (actual time=19.673..23.993 rows=5 loops=1)
-- -> Nested loop inner join (cost=1053197.51 rows=5) (actual time=0.119..22.433 rows=524 loops=1)
-- -> Index scan on sal using salary_idx (reverse) (cost=0.00 rows=5) (actual time=0.097..20.852 rows=524 loops=1)
-- -> Single-row index lookup on emp using PRIMARY (emp_no=sal.emp_no) (cost=0.37 rows=1) (actual time=0.003..0.003 rows=1 loops=524)
-- -> Filter: (demp.dept_no = 'd005') (cost=0.31 rows=1) (actual time=0.003..0.003 rows=0 loops=524)
-- -> Single-row index lookup on demp using PRIMARY (emp_no=sal.emp_no, dept_no='d005') (cost=0.31 rows=1) (actual time=0.003..0.003 rows=0 loops=524)おわりに
本記事では MySQL で JOIN を行うクエリをインデックスで高速化する方法について、調べた内容をまとめました。
テーブルを JOIN し ORDER BY した結果の上位~件を取得するクエリの場合、インデックスを使用するかどうかでパフォーマンスが大きく変わることが分かりました。
またこの時どのテーブルが外部表がなのかを意識することで、インデックスを使用できるクエリを作成できました。
この記事がどなたかの参考になれば幸いです。
コメント