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

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 rowsoffsetが大きい場合、効率の問題が発生することが分かっています。

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_shutdowninnodb_buffer_pool_load_at_startupオフにする必要があります。これら 2 つのオプションは、データベースのシャットダウン時にバッファー プール データをダンプすることと、データベースの起動時にディスクにバックアップbuffer poolデータをロードすることを制御します。

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

以下もご興味があるかもしれません:
  • MySql ページングで limit+order by を使用する場合のデータ重複の解決策
  • 制限を使用すると、MySQL のページングがどんどん遅くなるのはなぜですか?
  • MySQL 最適化 query_cache_limit パラメータの説明
  • MySQLのorder byとlimitを混在させる際の落とし穴の詳細な説明
  • 大きなオフセットによる MySQL 制限ページングが遅い理由と最適化ソリューション
  • MySQL のソートとページング (order by と limit) と既存の落とし穴
  • MySQL は制限を使用してページング例メソッドを実装します
  • MySQLの制限を使用して大規模なページングの問題を解決する方法
  • MySQLのページング制限のパフォーマンス問題についての簡単な説明
  • MySQL の制限パフォーマンス分析と最適化
  • MySQL クエリにおける LIMIT の大きなオフセットによって引き起こされるパフォーマンス低下の分析

<<:  Zabbix カスタム監視 nginx ステータス実装プロセス

>>:  ブラウザが登録できるイベントの概要

推薦する

HTML テーブルタグチュートリアル (46): テーブルフッタータグ

<tfoot> タグは、テーブル フッターのスタイルを定義するために使用されます。基本構...

Linux または Android でファイル システムを追加する属性インターフェイスを解析する方法

最初のもの: 1. 主要なヘッダーファイルを追加します。 #include <linux/of...

Docker で MySQL マスターとスレーブをデプロイする方法

画像をダウンロードMySQLイメージの選択 docker 検索 mysql MySQL 5.7 イメ...

Axiosは繰り返しのリクエストをキャンセル

目次序文1. リクエストをキャンセルする方法2. 重複リクエストの判定方法3. 繰り返しリクエストを...

Docker コンテナは実行後に終了します (実行を継続する方法)

現象Dockerコンテナを起動する docker run –name [コンテナ名] [コンテナID...

開発者がデータベースロックを詳細に理解する必要がある理由

1.ロックしますか? 1.1 ロックとは何ですか?ロックの本当の意味は、鍵またはコードで開くことがで...

element-ui 写真をアップロードした後、座標点をマークします

要素UIとはelement-ui は、Ele.me のフロントエンド チームが開発者、デザイナー、製...

シンプルなウェブ計算機を実装するJavaScript

背景私は新しいプロジェクト チームに配属されたので、プロジェクトでは js を使用する必要があります...

Linux自動ログイン例の説明

インターネット上には、expect を使用して自動ログインを実現するスクリプトが多数存在しますが、明...

XHTML 入門チュートリアル: よく使われる XHTML タグ

<br />記事と同様に、Web ページにも明確な段落と重要度の異なるタイトルが必要です...

Windows での MySQL の詳細なインストール手順と基本的な使用方法

目次1. MySQLをダウンロードする2. MySQLをインストールする3. MySQL の基本的な...

MySQLクエリ速度を最適化する方法

前の章では、高性能な MySQL に不可欠な、最適化されたデータ型の選択方法とインデックスの効率的な...

Mysql の 2 つのテーブル間の結合クエリの 4 つの状況の概要

一般的に言えば、より完全な結果を得るためには、2 つ以上のテーブルから結果を取得する必要があります。...

JavaScript をスリープまたは待機させる方法

目次概要setTimeout() の確認スリープ関数の書き方シンプルな選択ループで実行されますか?要...

CentOS 6.9 で glibc ダイナミック ライブラリをアップグレードする詳細なプロセス

glibc は、gnu によってリリースされた libc ライブラリ、つまり c ランタイム ライブ...