はじめに
検索結果をページングしながら返すWeb APIを作るとき、検索にヒットした総件数も一緒に返すケースは多い。
通常、特定のページのデータを取得するクエリと、ヒットした件数を引くクエリは別々になる。
そこで、COUNT(*) OVER() を使うことで一発で両方とも引くテクニックがあるが、意図せずスロークエリに繋がりかねないので止めた方が良いと思っている話、である。
前提条件として MySQL8でInnoDBを使用している。
COUNT(*) OVER() を使うとどうなるか
下記のように、COUNT(*) OVER() を使うことでページングしつつ全件数も同時に取得できる。このクエリは、 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 で総件数が取得できている。
+--------+--------+------------+------------+-------------+
| 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)しかし、このクエリは手元のマシン上だと2秒程かかってしまい、非常に遅い。
そこで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 に時間がかかっており、85707行もJOINしていることがわかる。
下側のカウントを取らない方では1行しかJOINしていない。 結果行を見るとわかるように、emp_noは10001しか登場していないので、LIMITがきちんとかかているならば、JOINは1行で十分なはずである。
すなわちCOUNT OVERを使うクエリでは、カウントを取るために全行を調査する必要が出てきてしまい、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() の方と見比べてもらうと分かる通り、3行目の Extra が Using index に変わっている。すなわち、カバリングインデックスと判定されインデックスキャンで完了する。
すなわちここがポイントであり、データとカウントを同時に取ろうとする問題のクエリでは、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を使うとレコードが重複する でもページングの話題を取り上げている。ページングは意外に奥が深くて面白い！