MySQL道普請便り

第200回LIMIT OFFSETに関するMySQLクエリチューニング

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.27 secかかっています。

読み取り行数を減らすアプローチ

読み取り行数を減らすクエリのチューニング方法は、第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_INCREMENTが歯抜けになっている場合などは、書き換えが難しいこともあります。今回はAUTO_INCREMENT値が連番ではないため、この方法でのクエリ書き換えはできませんでした。

読み取りページ数を減らすアプローチ

読み取りページ数を減らすチューニング方法は、カバリングインデックスを利用して読み取るページ数を抑えるアプローチです。前述の方法で実現できなかった際に検討します。クエリが読み取る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_buffer_pool_read_requestsを比較してみます。このステータスは読み取りページ数を表します。注意として、このステータスはグローバルステータスです。よって、すべての接続にわたって値を集計するため、他セッションからのクエリがあればこのステータス値のクエリ実行前と実行後の差分を比較しても意味がありません。他に実行されるクエリがないテスト環境などを利用して確認してください。

クエリ Innodb_buffer_pool_read_requests 実行時間
SELECT * FROM test ORDER BY 1 LIMIT 350000,10; 353416 0.27 sec
SELECT * FROM test JOIN (SELECT id FROM test ORDER BY 1 LIMIT 350000,10 ) a USING(id); 42330 0.08 sec

このように、Innodb_buffer_pool_read_requestsの値は大きく減り、実行時間も速くなりました。

なぜ、このように書き換えるだけで実行が速くなるのでしょうか?

問題のクエリSELECT * FROM test ORDER BY 1 limit 350000,10LIMIT 350000,10では、実際には350010行を読み取ることと同等になります。そして、不必要な350010行分のテーブルデータのページや、オーバフローページ(text型のcカラム)を読むことになります。オーバフローページについては、マニュアルの15.10 InnoDB の行フォーマットをご参照ください。

書き換えたクエリ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.217 secondsかかりましたが、書き換え後のクエリでは9.852 secondsで完了しています。

$ 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にて問題になるクエリのチューニング方法を紹介しました。読み取り行数を減らすチューニングと読み取りページ数を減らすチューニングを説明しました。読み取り行数を減らすチューニングは一般的な方法であるので、最初に検討することをおすすめします。それで難しい場合は、読み取りページを減らすチューニングを検討するといいでしょう。読み取りページを減らすチューニングは、今回はプライマリーキーを利用しましたが、カバリングインデックスはセカンダリインデックスでも有効です。

おすすめ記事

記事・ニュース一覧