MySQL で制限を使用するとパフォーマンスに影響するのはなぜですか?

まず、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 | 0 | |
| ソース | int(10) 符号なし | NO | | 0 | |
+--------+----------------------+------+-----+---------+----------------+
セット内の 3 行 (0.00 秒)

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

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

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

limit offset rowsのoffsetが大きい場合、効率の問題が発生することが分かっています。

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;のクエリプロセスを見てみましょう。

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

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

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

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

確認済み：

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

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

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

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

val=4 の制限 300000,5 のテストから * を選択

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 秒)

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

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秒）

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

select * from test a inner join (select id from test where val=4 limit 300000,5) b> 、 buffer poolをクリアして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 のデータページをbuffer poolにロードしますが、2 番目の SQL は 5 つのデータページのみをbuffer pool。私たちの予測通りです。これにより、最初の SQL ステートメントが遅い理由も確認できます。大量の役に立たないデータ行 (300,000) を読み取ってから破棄します。

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

発生した問題:

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

これで、MySQL で limit を使用するとパフォーマンスに影響する理由に関するこの記事は終了です。MySQL で limit を使用するとパフォーマンスにどのような影響があるかの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

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