MySQLでは、さまざまなアプローチでのクエリチューニングが存在します。WHERE条件のカラムにインデックスが存在しない場合にインデックスを追加するなど、簡単なチューニングでも大きな改善は見込めます。
今回は、LIMIT OFFSETにて問題になるクエリについて、以下の2つのチューニング方法を紹介します。
- 読み取り行数を減らすアプローチ
- 読み取りページ数を減らすアプローチ
サンプルのテーブルとデータ、クエリを元に、具体例にて説明します。
前準備
以下のようなテーブルを用意します。
mysql> SHOW CREATE TABLE test\G Table: test Create Table: CREATE TABLE `test` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4652971 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
データにはランダムな値が入っており、text型カラムには大きめのデータが格納されています。データとレコード件数は以下の通りです。
mysql> SELECT * FROM test LIMIT 10; +----+----------+-------------------------------------------------------------------------------------------------------------------------+ | id | k | c | +----+----------+-------------------------------------------------------------------------------------------------------------------------+ | 10 | 12987071 | 05677017559-47107518969-97509137401-28934334557-14497052050-61906823704-44077628507-24840441785-05187301456-27797851637 | | 11 | 14178622 | 69916792160-36263343577-60040896157-93749675091-85349848150-51324584648-99894376828-35106327881-24322876464-85736660000 | | 12 | 12584170 | 06636928111-91412549319-87017689961-79500497523-29051692073-64380774874-78643769852-73373361096-34215043106-34370178281 | | 13 | 11614512 | 66692787527-64669315176-31954384866-40167063199-20714065413-78429761915-37156169484-48536455204-68184091915-53509501438 | | 14 | 12886167 | 03057970397-76844598219-57919615042-40446066254-53006176529-07928677880-76521527831-33188464243-14751964794-48299542958 | | 15 | 12837625 | 46727919633-45146044920-86415428832-97681745441-41127226689-79274197453-43262911115-03302568793-24793907254-41889251160 | | 16 | 16091645 | 66674934079-61932070973-59260913966-95805894865-93643781508-42542760387-65244531372-94908882784-73015194138-09444593269 | | 17 | 12845435 | 42863854528-01081829164-15763552981-12403743290-73250276677-11227646213-34508938444-96091116410-30361051807-02401305678 | | 18 | 12839521 | 82991834540-27107960725-55255797929-15631102224-93218976341-02587259718-39218801679-60059023868-06269782489-88220383890 | | 19 | 12896536 | 50775217476-73338282083-95609959533-46458517058-43224921897-04845035863-09371954537-79862351894-56643259241-35384026860 | +----+----------+-------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.00 sec) mysql> SELECT COUNT(*) FROM test; +----------+ | COUNT(*) | +----------+ | 400000 | +----------+ 1 row in set (0.21 sec)
問題のクエリは？
問題となるクエリはLIMIT OFFSETを利用して、ページャー処理を実現するクエリです。このクエリはOFFSET値が大きくなると、途端に実行時間が長くなる問題があります。詳しくは、第141回 LIMIT句の利用についての記事で紹介しているのでご確認ください。
mysql> EXPLAIN SELECT * FROM test ORDER BY 1 LIMIT 350000,10 ; +----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------+ | 1 | SIMPLE | test | NULL | index | NULL | PRIMARY | 4 | NULL | 350010 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM test ORDER BY 1 LIMIT 350000,10 ; +--------+----------+-------------------------------------------------------------------------------------------------------------------------+ | id | k | c | +--------+----------+-------------------------------------------------------------------------------------------------------------------------+ | 475987 | 12934825 | 32225328067-96685552614-47007847146-67264797604-25477060252-13538483042-06880236554-05933221444-42283024165-16540610834 | | 475988 | 7729792 | 24051952945-28978441303-07347623194-06535745208-23810853782-58992432675-21781788789-00402390133-72601281070-23200242273 | | 475989 | 13017076 | 00321616808-61180753455-64698206562-88895960185-47040348398-81633581408-67992793936-17703909005-10705500265-33826006029 | | 475990 | 13009695 | 25116371725-23715806713-13390628479-50696955169-85124527757-10385304649-97573706129-19959650611-26942392893-12747043333 | | 475991 | 13000401 | 33960513743-55602597226-70850171898-08210004872-35173618710-19184002059-52632691737-07993606484-84168735981-50340940620 | | 475992 | 14746186 | 72495415174-31951688572-71127113829-20326929865-67031603304-41365056487-10961464465-70488943959-73717865098-13989846372 | | 475993 | 13015244 | 91687809739-17068042086-55777438908-56233268836-95321313537-24764281179-02073508743-99920683932-84364151822-60549895090 | | 475994 | 9892285 | 37963177767-99941235261-59777690726-61189362235-86130576292-28288039956-12930351819-14372176876-56799892202-58341634301 | | 475995 | 13948638 | 26009839178-40116193662-36260500617-48739229367-06945483100-76110815089-50785201219-09781972571-90757122664-45259260863 | | 475996 | 11297453 | 78102864899-02942683029-78673717597-32383654429-74735333751-46797411055-62094276606-90836232585-48135200409-66716992525 | +--------+----------+-------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.27 sec)
上記のように SELECT * FROM test ORDER BY 1 LIMIT 350000,10 ; このクエリを実行すると、0.かかっています。
読み取り行数を減らすアプローチ
読み取り行数を減らすクエリのチューニング方法は、第141回 LIMIT句の利用についての"LIMIT句のよくある勘違い"でも述べられています。まず、このクエリがRANGE検索になるように書き換えが可能か、検討することをおすすめします。
以下のように、プライマリーキーを利用してRANGE検索になるようにクエリを書き換えます。
mysql> EXPLAIN SELECT * FROM test WHERE id BETWEEN 350000 and 350010; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 11 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
この方法は、単純に書き換えることができるのあればかなり有効です。読み取り行数を必要なデータのみになるため、クエリ速度が大幅に改善します。ただし、複雑なWHERE句条件がついていたり、AUTO_
読み取りページ数を減らすアプローチ
読み取りページ数を減らすチューニング方法は、カバリングインデックスを利用して読み取るページ数を抑えるアプローチです。前述の方法で実現できなかった際に検討します。クエリが読み取るInnoDBのページ数を減らすことが目的になります。カバリングインデックスとはインデックスにWHERE条件と結果セットに必要なすべてのカラムが含まれている場合、クエリはそのインデックスのみで解決し、テーブルデータを読み取らなくても済むようになります。
書き換え後のクエリは以下のようになります。
mysql> EXPLAIN SELECT * FROM test JOIN (SELECT id FROM test ORDER BY 1 LIMIT 350000,10 ) a USING(id) ; +----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 350010 | 100.00 | NULL | | 1 | PRIMARY | test | NULL | eq_ref | PRIMARY | PRIMARY | 4 | a.id | 1 | 100.00 | NULL | | 2 | DERIVED | test | NULL | index | NULL | PRIMARY | 4 | NULL | 350010 | 100.00 | Using index | +----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+ 3 rows in set, 1 warning (0.01 sec) mysql> SELECT * FROM test JOIN (SELECT id FROM test ORDER BY 1 LIMIT 350000,10 ) a USING(id) ; +--------+----------+-------------------------------------------------------------------------------------------------------------------------+ | id | k | c | +--------+----------+-------------------------------------------------------------------------------------------------------------------------+ | 475987 | 12934825 | 32225328067-96685552614-47007847146-67264797604-25477060252-13538483042-06880236554-05933221444-42283024165-16540610834 | | 475988 | 7729792 | 24051952945-28978441303-07347623194-06535745208-23810853782-58992432675-21781788789-00402390133-72601281070-23200242273 | | 475989 | 13017076 | 00321616808-61180753455-64698206562-88895960185-47040348398-81633581408-67992793936-17703909005-10705500265-33826006029 | | 475990 | 13009695 | 25116371725-23715806713-13390628479-50696955169-85124527757-10385304649-97573706129-19959650611-26942392893-12747043333 | | 475991 | 13000401 | 33960513743-55602597226-70850171898-08210004872-35173618710-19184002059-52632691737-07993606484-84168735981-50340940620 | | 475992 | 14746186 | 72495415174-31951688572-71127113829-20326929865-67031603304-41365056487-10961464465-70488943959-73717865098-13989846372 | | 475993 | 13015244 | 91687809739-17068042086-55777438908-56233268836-95321313537-24764281179-02073508743-99920683932-84364151822-60549895090 | | 475994 | 9892285 | 37963177767-99941235261-59777690726-61189362235-86130576292-28288039956-12930351819-14372176876-56799892202-58341634301 | | 475995 | 13948638 | 26009839178-40116193662-36260500617-48739229367-06945483100-76110815089-50785201219-09781972571-90757122664-45259260863 | | 475996 | 11297453 | 78102864899-02942683029-78673717597-32383654429-74735333751-46797411055-62094276606-90836232585-48135200409-66716992525 | +--------+----------+-------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.08 sec)
ステータスInnodb_を比較してみます。このステータスは読み取りページ数を表します。注意として、このステータスはグローバルステータスです。よって、すべての接続にわたって値を集計するため、他セッションからのクエリがあればこのステータス値のクエリ実行前と実行後の差分を比較しても意味がありません。他に実行されるクエリがないテスト環境などを利用して確認してください。
| クエリ | Innodb_ |
実行時間 |
|---|---|---|
| SELECT * FROM test ORDER BY 1 LIMIT 350000,10; | 353416 | 0. |
| SELECT * FROM test JOIN (SELECT id FROM test ORDER BY 1 LIMIT 350000,10 ) a USING(id); | 42330 | 0. |
このように、Innodb_
なぜ、このように書き換えるだけで実行が速くなるのでしょうか？
問題のクエリSELECT * FROM test ORDER BY 1 limit 350000,10）LIMIT 350000,10では、実際には350010行を読み取ることと同等になります。そして、不必要な350010行分のテーブルデータのページや、オーバフローページcカラム）
書き換えたクエリSELECT * FROM test JOIN (SELECT id FROM test ORDER BY 1 limit 350000,10 ) a USING(id);）LIMIT 350000,10においても、問題のクエリと同じく350010行を読み取ります。
しかし、SELECTリストにidのみを指定することで、プライマリキーのカバリングインデックスとなり、不必要なページを読むことなく完結できます。実行計画のExtra列のUsing indexから、カバリングインデックスとなっているのがわかります。それを内部テンポラリーテーブルにして基テーブルとJOINすることで、実際に結果が必要なテーブルデータだけを読みにいくようにチューニングしています。
クエリ単体の速度が大幅に改善しているわけではありませんが、同時実行や呼ばれる回数が多いとパフォーマンスに差が出てきます。たとえば、mysqlslapにて同時実行数100で500件のクエリを処理する時間を比較すると、大きく差がでます。問題のクエリでは500件のクエリが完了するまで30.かかりましたが、書き換え後のクエリでは9.で完了しています。
$ mysqlslap --host=127.0.0.1 -p --port=3306 --user=root --query="SELECT * FROM test ORDER BY 1 LIMIT 350000,10 ;" --concurrency=100 --number-of-queries=500 --create-schema=test
Benchmark
Average number of seconds to run all queries: 30.217 seconds
Minimum number of seconds to run all queries: 30.217 seconds
Maximum number of seconds to run all queries: 30.217 seconds
Number of clients running queries: 100
Average number of queries per client: 5
$ mysqlslap --host=127.0.0.1 -p --port=3306 --user=root --query="SELECT * FROM test JOIN (SELECT id FROM test ORDER BY 1 LIMIT 350000,10 ) a USING(id) ;" --concurrency=100 --number-of-queries=500 --create-schema=test
Benchmark
Average number of seconds to run all queries: 9.852 seconds
Minimum number of seconds to run all queries: 9.852 seconds
Maximum number of seconds to run all queries: 9.852 seconds
Number of clients running queries: 100
Average number of queries per client: 5
まとめ
今回はLIMIT OFFSETにて問題になるクエリのチューニング方法を紹介しました。読み取り行数を減らすチューニングと読み取りページ数を減らすチューニングを説明しました。読み取り行数を減らすチューニングは一般的な方法であるので、最初に検討することをおすすめします。それで難しい場合は、読み取りページを減らすチューニングを検討するといいでしょう。読み取りページを減らすチューニングは、今回はプライマリーキーを利用しましたが、カバリングインデックスはセカンダリインデックスでも有効です。