MySQL のクエリパフォーマンスに対する制限の影響

I. はじめに

まず、MySQL のバージョンについて説明します。

mysql> バージョンを選択します();
+-----------+
| バージョン() |
+-----------+
| 5.7.17 |
+-----------+
セット内の1行（0.00秒）

テーブル構造:

mysql> desc テスト;
+--------+----------------------+------+-----+---------+----------------+
| フィールド | タイプ | Null | キー | デフォルト | 追加 |
+--------+----------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| val | int(10) 符号なし | NO | MUL | | |
| ソース | int(10) 符号なし | NO | | | |
+--------+----------------------+------+-----+---------+----------------+
セット内の 3 行 (.00 秒)

id は自動インクリメントの主キーであり、val は一意でないインデックスです。

合計500万件の大量のデータを投入します。

mysql> テストから count(*) を選択します。
+----------+
| カウント(*) |
+----------+
|5242882|
+----------+
セット1列（4.25秒）

制限オフセット行のオフセットが大きい場合、効率の問題が発生することが分かっています。

mysql> select * from test where val=4 limit 300000,5;
+---------+-----+--------+
| id | 値 | ソース |
+---------+-----+--------+
| 3327622 | 4 | 4 |
| 3327632 | 4 | 4 |
| 3327642 | 4 | 4 |
| 3327652 | 4 | 4 |
| 3327662 | 4 | 4 |
+---------+-----+--------+
5 列セット (15.98 秒)

同じ目的を達成するために、通常は次のように書き直します。

mysql> select * from test a 内部結合 (select id from test where val=4 limit 300000,5) b on a.id=b.id;
+---------+-----+--------+---------+
| id | val | ソース | id |
+---------+-----+--------+---------+
| 3327622 | 4 | 4 | 3327622 |
| 3327632 | 4 | 4 | 3327632 |
| 3327642 | 4 | 4 | 3327642 |
| 3327652 | 4 | 4 | 3327652 |
| 3327662 | 4 | 4 | 3327662 |
+---------+-----+--------+---------+
セット5行（0.38秒）

時間の違いは明らかです。

なぜ上記のような結果が表示されるのでしょうか? select * from test where val=4 limit 300000,5; のクエリプロセスを見てみましょう。

インデックスリーフノードデータが照会されます。

リーフノードの主キー値に基づいて、クラスター化インデックス上のすべての必須フィールド値をクエリします。

次の図のようになります。

上記のように、インデックスノードを 300,005 回クエリし、クラスター化インデックスデータを 300,005 回クエリし、最後に最初の 300,000 件の結果をフィルターして最後の 5 件を取り出す必要があります。 MySQL は、クラスター化インデックスのデータをクエリするために大量のランダム I/O を費やし、300,000 回のランダム I/O によってクエリされたデータは結果セットに表示されません。

誰かが必ずこう尋ねるでしょう: インデックスは最初に使用されるので、最初にインデックスリーフノードに沿って必要な最後の 5 つのノードまでクエリを実行し、次にクラスター化インデックス内の実際のデータをクエリするのはなぜですか。これには、次の図のプロセスと同様に、5 つのランダム I/O のみが必要です。

実は私もこの質問をしたいんです。

確認

上記の推論を確認するために実際にいくつかの操作を実行してみましょう。

select * from test where val=4 limit 300000,5を証明するには、MySQL に 1 つの SQL でインデックスノードを介してデータノードがクエリされる回数をカウントする方法があるかどうかを知る必要があります。まずHandler_read_*シリーズを試してみましたが、残念ながらどの変数も条件を満たしませんでした。

私はこれを間接的にしか確認できません:

InnoDB にはバッファプールがあります。データページやインデックスページなど、最近アクセスされたデータページが含まれます。したがって、バッファープール内のデータページの数を比較するには、2 つの SQL ステートメントを実行する必要があります。予測結果では、 select * from test a inner join (select id from test where val=4 limit 300000,5)実行した後、バッファープール内のデータページ数はselect * from test where val=4 limit 300000,5の対応する数よりもはるかに少なくなります。これは、前者の SQL はデータページに 5 回しかアクセスしないのに対し、後者の SQL はデータページに 300005 回アクセスするためです。

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
空セット (0.04 秒)

現在、バッファープール内にテストテーブルに関するデータページが存在しないことがわかります。

mysql> select * from test where val=4 limit 300000,5;
+---------+-----+--------+
| id | 値 | ソース |
+---------+-----+--------+
| 3327622 | 4 | 4 |
| 3327632 | 4 | 4 |
| 3327642 | 4 | 4 |
| 3327652 | 4 | 4 |
| 3327662 | 4 | 4 |
+---------+-----+--------+
セット5列（26.19秒）

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
+------------+-----------+
| インデックス名 | カウント(*) |
+------------+-----------+
| プライマリ | 4098 |
| 値 | 208 |
+------------+-----------+
セット2列（0.04秒）

この時点で、バッファープールにはテストテーブルのデータページが 4098 ページ、インデックスページが 208 ページあることがわかります。

select * from test a inner join (select id from test where val=4 limit 300000,5) 、バッファプールをクリアしてMySQLを再起動する必要があります。

mysqladmin シャットダウン
/usr/local/bin/mysqld_safe &
mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
空セット (0.03 秒)

SQL を実行します:

mysql> select * from test a 内部結合 (select id from test where val=4 limit 300000,5) b on a.id=b.id;
+---------+-----+--------+---------+
| id | val | ソース | id |
+---------+-----+--------+---------+
| 3327622 | 4 | 4 | 3327622 |
| 3327632 | 4 | 4 | 3327632 |
| 3327642 | 4 | 4 | 3327642 |
| 3327652 | 4 | 4 | 3327652 |
| 3327662 | 4 | 4 | 3327662 |
+---------+-----+--------+---------+
セットに5行（0.09秒）

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
+------------+-----------+
| インデックス名 | カウント(*) |
+------------+-----------+
| プライマリ | 5 |
| 値 | 390 |
+------------+-----------+
セットに2行（0.03秒）

2 つの違いは明らかです。最初の SQL は 4098 のデータページをバッファープールにロードしますが、2 番目の SQL は 5 つのデータページのみをバッファープールにロードします。私たちの予測通りです。これにより、最初の SQL ステートメントが遅い理由も確認できます。大量の役に立たないデータ行 (300,000) を読み取ってから破棄します。

そして、これは問題を引き起こします。あまりホットではないデータページを大量にバッファープールにロードすると、バッファープールの汚染が発生し、バッファープールのスペースが占有されます。

発生した問題

再起動のたびにバッファープールがクリアされるようにするには、innodb_buffer_pool_dump_at_shutdown と innodb_buffer_pool_load_at_startup をオフにする必要があります。これら 2 つのオプションは、データベースのシャットダウン時にバッファープールデータをダンプすることと、データベースの起動時にディスクにバックアップバッファープールデータをロードすることを制御します。

参考文献:

1. https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/

2. https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-buffer-pool-tables.html

SQL実行効率の詳細については、以下の関連記事をご覧ください。

以下もご興味があるかもしれません: