はじめに
検索結果をページングしながら返すWeb APIを実装するとき、総件数も一緒に返すケースは多い。
そこで、COUNT(*) OVER() を使ってページングされたデータと総件数を一発のクエリで引くテクニックがあるが、意図せずスロークエリに繋がりかねないので止めた方が良いと思っている話、である。
前提条件として MySQL8でInnoDBを使用している。
COUNT(*) OVER() を使うとどうなるか
COUNT(*) OVER() を使うことで、下記のようにLIMITでページングしつつ全件数も同時に取得できる。このクエリは、 employees データベース を使って開発部の社員の給与履歴を検索するものである。
-- salaries(社員の給与履歴)をdepartmentsまでJOINして開発部に絞って検索する
SELECT
salaries.*
, COUNT(*) OVER() AS total_count
FROM salaries
INNER JOIN dept_emp USING(emp_no)
INNER JOIN departments USING(dept_no)
WHERE dept_name = 'Development'
LIMIT 100結果は次のようになり、total_count で総件数が取得できている。
しかし、このクエリは手元のマシン上で2秒程かかってしまっている。
+--------+--------+------------+------------+-------------+
| emp_no | salary | from_date | to_date | total_count |
+--------+--------+------------+------------+-------------+
| 10001 | 60117 | 1986-06-26 | 1987-06-26 | 810026 |
| 10001 | 62102 | 1987-06-26 | 1988-06-25 | 810026 |
| 10001 | 66074 | 1988-06-25 | 1989-06-25 | 810026 |
| 10001 | 66596 | 1989-06-25 | 1990-06-25 | 810026 |
| 10001 | 66961 | 1990-06-25 | 1991-06-25 | 810026 |
| 10001 | 71046 | 1991-06-25 | 1992-06-24 | 810026 |
| 10001 | 74333 | 1992-06-24 | 1993-06-24 | 810026 |
| 10001 | 75286 | 1993-06-24 | 1994-06-24 | 810026 |
| 10001 | 75994 | 1994-06-24 | 1995-06-24 | 810026 |
| 10001 | 76884 | 1995-06-24 | 1996-06-23 | 810026 |
+--------+--------+------------+------------+-------------+
10 rows in set (2.15 sec)ところが、実はCOUNT OVERを消すとこのクエリは即時に終了する。
SELECT
salaries.*
-- , COUNT(*) OVER() AS total_count
FROM salaries
INNER JOIN dept_emp USING(emp_no)
INNER JOIN departments USING(dept_no)
WHERE dept_name = 'Development'
LIMIT 100+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 60117 | 1986-06-26 | 1987-06-26 |
| 10001 | 62102 | 1987-06-26 | 1988-06-25 |
| 10001 | 66074 | 1988-06-25 | 1989-06-25 |
| 10001 | 66596 | 1989-06-25 | 1990-06-25 |
| 10001 | 66961 | 1990-06-25 | 1991-06-25 |
| 10001 | 71046 | 1991-06-25 | 1992-06-24 |
| 10001 | 74333 | 1992-06-24 | 1993-06-24 |
| 10001 | 75286 | 1993-06-24 | 1994-06-24 |
| 10001 | 75994 | 1994-06-24 | 1995-06-24 |
| 10001 | 76884 | 1995-06-24 | 1996-06-23 |
+--------+--------+------------+------------+
10 rows in set (0.00 sec)いやいや、COUNTが重いんでしょう？と思いきや、こちらも単独で実行すれば400msec程度で終わる。
SELECT COUNT(*)
FROM salaries
INNER JOIN dept_emp USING(emp_no)
INNER JOIN departments USING(dept_no)
WHERE dept_no = 'd005';+----------+
| COUNT(*) |
+----------+
| 810026 |
+----------+
1 row in set (0.38 sec)よって、まとめずに実行したほうがパフォーマンスが良いということになる。
なぜ遅くなるのか？
実行計画を確認してみると、COUNT(*) OVER() で件数を取るクエリと取らないクエリは、全く同じ実行計画になっている。
mysql> EXPLAIN SELECT salaries.* , COUNT(*) OVER() total_count FROM salaries INNER JOIN dept_emp USING(emp_no) INNER JOIN departments USING(dept_no) WHERE dept_name = 'Development' LIMIT 10;
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+---------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+---------------------------+--------+----------+-------------+
| 1 | SIMPLE | departments | NULL | const | PRIMARY,dept_name | dept_name | 162 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | dept_emp | NULL | ref | PRIMARY,dept_no | dept_no | 16 | const | 165571 | 100.00 | Using index |
| 1 | SIMPLE | salaries | NULL | ref | PRIMARY | PRIMARY | 4 | employees.dept_emp.emp_no | 9 | 100.00 | NULL |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+---------------------------+--------+----------+-------------+mysql> EXPLAIN SELECT salaries.* FROM salaries INNER JOIN dept_emp USING(emp_no) INNER JOIN departments USING(dept_no) WHERE dept_name = 'Development' LIMIT 10;
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+---------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+---------------------------+--------+----------+-------------+
| 1 | SIMPLE | departments | NULL | const | PRIMARY,dept_name | dept_name | 162 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | dept_emp | NULL | ref | PRIMARY,dept_no | dept_no | 16 | const | 165571 | 100.00 | Using index |
| 1 | SIMPLE | salaries | NULL | ref | PRIMARY | PRIMARY | 4 | employees.dept_emp.emp_no | 9 | 100.00 | NULL |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+---------------------------+--------+----------+-------------+すぐにわかる問題点などもないと思う。厄介なことに、実行計画からは COUNT OVER でクエリが遅くなることがわからない のである。（詳しい人であればわかるのかもしれないが…）
そこでさらに、EXPLAIN ANALYZE を見てみる。
mysql> EXPLAIN ANALYZE SELECT salaries.* , COUNT(*) OVER() total_count FROM salaries INNER JOIN dept_emp USING(emp_no) INNER JOIN departments USING(dept_no) WHERE dept_name = 'Development' LIMIT 10\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s) (actual time=1366.977..1366.987 rows=10 loops=1)
-> Window aggregate with buffering: count(0) OVER () (actual time=1366.973..1366.982 rows=10 loops=1)
-> Nested loop inner join (cost=343306.77 rows=1587717) (actual time=0.102..323.638 rows=810026 loops=1)
-> Covering index lookup on dept_emp using dept_no (dept_no='d005') (cost=17284.28 rows=165571) (actual time=0.037..15.233 rows=85707 loops=1)
-> Index lookup on salaries using PRIMARY (emp_no=dept_emp.emp_no) (cost=1.01 rows=10) (actual time=0.002..0.003 rows=9 loops=85707)mysql> EXPLAIN ANALYZE SELECT salaries.* FROM salaries INNER JOIN dept_emp USING(emp_no) INNER JOIN departments USING(dept_no) WHERE dept_name = 'Development' LIMIT 10\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s) (cost=343306.77 rows=10) (actual time=0.122..0.126 rows=10 loops=1)
-> Nested loop inner join (cost=343306.77 rows=1587717) (actual time=0.120..0.123 rows=10 loops=1)
-> Covering index lookup on dept_emp using dept_no (dept_no='d005') (cost=17284.28 rows=165571) (actual time=0.046..0.046 rows=1 loops=1)
-> Index lookup on salaries using PRIMARY (emp_no=dept_emp.emp_no) (cost=1.01 rows=10) (actual time=0.072..0.074 rows=10 loops=1)
これを見ると、カウントを取るクエリ（上側）では Nested loop inner join に時間がかかっていることがわかる。またその下の Covering index lookup on dept_emp using dept_no において、カウントを取らないクエリではJOINの行数が1行で済んでいるのに対し、85707行もJOINしている。
これは、カウントを取らないクエリではLIMIT 10が効いており、最終的な10行に必要な emp_no = 10001 しかJOINしていないということである。
逆にカウントを取る場合には最終的には不要な行であっても、全数を調査すためにJOINを一度全てやる必要があり、時間がかかっているという訳だ。
少し考えれば当たり前のことだが、カウントを取るためにLIMITの効果が消える ということが言えそうだ。
なぜ単独の COUNT(*) よりも遅いのか？
次に思うことは、ではなぜ COUNT(*) よりも遅くなるのかということである。COUNT(*) も件数を数えるためにLIMITをかけられない点では COUNT(*) OVER() と同じである。
これは実行計画を見るとわかる。
mysql> EXPLAIN SELECT COUNT(*) FROM salaries INNER JOIN dept_emp USING(emp_no) INNER JOIN departments USING(dept_no) WHERE dept_name = 'Development' LIMIT 10;
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+---------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+---------------------------+--------+----------+-------------+
| 1 | SIMPLE | departments | NULL | const | PRIMARY,dept_name | dept_name | 162 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | dept_emp | NULL | ref | PRIMARY,dept_no | dept_no | 16 | const | 165571 | 100.00 | Using index |
| 1 | SIMPLE | salaries | NULL | ref | PRIMARY | PRIMARY | 4 | employees.dept_emp.emp_no | 9 | 100.00 | Using index |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+---------------------------+--------+----------+-------------+先ほど記載した COUNT(*) OVER() の方と見比べてもらうと分かる通り、Extra 列が全て Using index に変わっている。すなわち、カバリングインデックスと判定されインデックスキャンのみで完了しているのである。
問題のクエリの最も重い部分はNested Loop Joinであり、ひいてはsalariesテーブルの行への大量アクセスである。
データとカウントを同時に取ろうとするそのクエリでは、salaries.* を必要としているため、行数をカウントする際にもテーブルデータへのアクセスが発生しているのである。
SELECT
salaries.* -- <- すべての列が必要
, COUNT(*) OVER() AS total_count -- <- よって件数調べるときにも行アクセスが必要とMySQLが判断する
FROM salaries
...一方、単独の COUNT(*) クエリであれば、行数がわかれば良いのでインデックススキャンだけで済んでいる のである。これが速度差につながっていると考えられる。
参考までに EXPLAIN ANALYZE の結果も載せておく。
mysql> EXPLAIN ANALYZE SELECT COUNT(*) FROM salaries INNER JOIN dept_emp USING(emp_no) INNER JOIN departments USING(dept_no) WHERE dept_name = 'Development' LIMIT 10\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s) (cost=502825.55 rows=1) (actual time=316.573..316.573 rows=1 loops=1)
-> Aggregate: count(0) (cost=502825.55 rows=1) (actual time=316.572..316.572 rows=1 loops=1)
-> Nested loop inner join (cost=344053.85 rows=1587717) (actual time=0.062..286.118 rows=810026 loops=1)
-> Covering index lookup on dept_emp using dept_no (dept_no='d005') (cost=17284.28 rows=165571) (actual time=0.043..15.702 rows=85707 loops=1)
-> Covering index lookup on salaries using PRIMARY (emp_no=dept_emp.emp_no) (cost=1.01 rows=10) (actual time=0.002..0.003 rows=9 loops=85707)結論
COUNT(*) OVER() を使ってデータと件数を同時取得することによって、
LIMITが効かなくなる- カバリングインデックスと判定されず、フルテーブルスキャンとなる
と言えるのではないかと思う。
オフセット法を使ってページネーションを実装するときは、データと件数の取得は分けてやろう。 （WHERE句が複雑になるとクエリが分かれるのは少々面倒くさいけど…）
余談: そもそもInnoDBのCOUNT()は速くない
ここまで書きつつ、今回のクエリの検索にヒットしている行数が80万件程と、決して小さくない点には注意が必要である。
これが1000万、1億となってくると、そもそも件数を取ること自体が難しくなる。（これについては、InnoDBでCOUNT()を扱う際の注意事項あれこれ がとても参考になった。）
そのため、検索にヒットする行数が大きいAPIで応答速度も必要となるケースでは、次のような工夫が必要になってくると思う。
- 件数を別テーブルやRedis等でキャッシュする
- 総件数を取得するWeb APIを独立させ、
- フロント側で1ページ目を先に表示しつつ、件数取得やナビゲーション表示を遅延させる
- 同セッション内で件数は1度だけ、または定期的にポーリングで取得する
ちなみに未だそのような要件には出会っていないので、これはあくまでパッと思いついたもの。
おわりに
2月に書いた、SQLのページネーションでOFFSETを使うとレコードが重複する でもページングの話題を取り上げている。ページングは意外に奥が深くて面白い！