MySQLはインデックスプッシュダウンを数秒で理解するのに役立ちます

MySQLはインデックスプッシュダウンを数秒で理解するのに役立ちます

Index Condition Pushdownプッシュダウン( ICP )はMySQL5.6の新機能です。テーブルクエリの数を減らし、クエリの効率を向上させることができます。

1. インデックスプッシュダウン最適化の原理

まず、MySQL の一般的なアーキテクチャを簡単に見てみましょう。

MySQL サービス層は、SQL 構文の解析、実行プランの生成などを担当し、ストレージ エンジン層を呼び出してデータの保存と取得を実行します。

索引下推のプッシュダウンは、実際には、上位層 (サービス層) が担当するタスクの一部が、処理のために下位層 (エンジン層) に引き渡されることを意味します。

ICP を使用せずに MySQL クエリを詳しく見てみましょう。

  • ストレージ エンジンはインデックス レコードを読み取ります。
  • インデックス内の主キー値に基づいて完全な行レコードを見つけて読み取ります。
  • ストレージ エンジンはレコードをサーバー レイヤーに渡し、レコードが WHERE 条件を満たしているかどうかを確認します。

ICP を使用する場合、クエリ プロセスは次のようになります。

  • ストレージ エンジンは、インデックス レコード (完全な行レコードではない) を読み取ります。
  • インデックス内の列を使用して WHERE 条件をチェックできるかどうかを判断します。条件が満たされない場合は、インデックス レコードの次の行を処理します。
  • 条件が満たされた場合、インデックス内の主キーを使用して完全な行レコードを検索して読み取ります (これをテーブル戻りと呼びます)。
  • ストレージ エンジンはレコードをサーバー レイヤーに渡し、サーバー レイヤーはレコードが WHERE 条件の残りの部分を満たしているかどうかを確認します。

2. インデックスプッシュダウンの具体的な実践

理論はかなり抽象的なので、実際に実践してみましょう。

ユーザー テーブルtuserを使用して、テーブル内に共同インデックス (名前、年齢) を作成します。

ここで要件がある場合:テーブル内の、名前が Zhang で、年齢が 10 歳のすべてのユーザーを取得します。次に、 SQL ステートメントは次のように記述されます。

select * from tuser where name like '张%' and age=10;


インデックスの左端一致の原則を理解していれば、このステートメントはインデックス ツリーを検索するときにのみ使用でき、条件を満たす最初のレコードの ID は 1 であることがわかります。

では次のステップは何でしょうか?

1. ICPを使用していない

MySQL 5.6より前では、ストレージ エンジンは結合インデックスを通じて '张%' name likeの主キー id (1, 4) を見つけ、テーブルを 1 つずつスキャンし、クラスター化インデックスを削除して完全な行レコードを見つけ、 serverレイヤーはage=10に従ってデータをフィルター処理します。

概略図を見てみましょう。

テーブルを 2 回返す必要があることがわかります。これにより、結合インデックスの他のフィールドage無駄になります。

2. ICPを使用する

MySQL 5.6以降では、ストレージ エンジンは ( nameage ) 結合インデックスに基づいてname like '張%を検索します。結合インデックスにはage列が含まれているため、ストレージ エンジンはage=10に従って結合インデックスを直接フィルターします。フィルタリングされたデータに従ってテーブルを 1 つずつスキャンします。

概略図を見てみましょう。

テーブルが 1 回だけ返されたことがわかります。

さらに、実行プランを確認すると、 Extra列にUsing index condition表示され、インデックス プッシュダウンが使用されていることがわかります。

+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+-------+---------+------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+-------+---------+------------------------+
| 1 | SIMPLE | tuser | NULL | 範囲 | na_index | na_index | 102 | NULL | 2 | 25.00 | インデックス条件を使用 |
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+-------+---------+------------------------+

3. インデックスプッシュダウンの使用条件

  • rangerefeq_refref_or_nullアクセス メソッドでのみ使用できます。
  • InnoDBおよびMyISAMストレージ エンジンとそれらのパーティション テーブルにのみ使用できます。
  • InnoDBストレージ エンジンの場合、インデックス プッシュダウンはセカンダリ インデックス (補助インデックスとも呼ばれます) にのみ適用されます。

インデックス プッシュダウンの目的は、テーブル戻り数を減らすこと、つまり IO 操作を減らすことです。 InnoDB のクラスター化インデックスの場合、數據和索引は一緒になり、テーブルを返すことはありません。

  • サブクエリを参照する条件はプッシュダウンできません。
  • ストレージ エンジンはストアド関数を呼び出せないため、ストアド関数を参照する条件はプッシュダウンできません。

関連するシステムパラメータ:

インデックス条件プッシュダウンはデフォルトで有効になっており、システム パラメータ optimizer_switch を使用して有効かどうかを制御できます。

デフォルトのステータスを表示します。

mysql> @@optimizer_switch\G を選択します。
************************** 1. 行 ****************************
@@optimizer_switch: index_merge=on、index_merge_union=on、index_merge_sort_union=on、index_merge_intersection=on、engine_condition_pushdown=on、index_condition_pushdown=on、mrr=on、mrr_cost_based=on、block_nested_loop=on、batched_key_access=off、materialization=on、semijoin=on、loosescan=on、firstmatch=on、duplicateweedout=on、subquery_materialization_cost_based=on、use_index_extensions=on、condition_fanout_filter=on、derived_merge=on
セット内の 1 行 (0.00 秒)

状態を切り替える:

optimizer_switch="index_condition_pushdown=off" を設定します。
optimizer_switch="index_condition_pushdown=on" を設定します。

これで、数分で MySQL インデックス プッシュダウンを理解する方法に関するこの記事は終了です。MySQL インデックス プッシュダウンに関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM を応援していただければ幸いです。

以下もご興味があるかもしれません:
  • MySQL インデックス データ構造の詳細な分析
  • MySQLデータベースのトランザクションとインデックスの詳細な説明
  • MySQL インデックス プッシュダウンの詳細
  • MySQL インデックス プッシュダウンを 5 分で理解する
  • MySQL Index Pushdown (ICP) とは何かを理解するための記事
  • MySQL 面接の質問: ハッシュ インデックスの設定方法

<<:  レスポンシブWebデザイン学習(1) - 画面サイズと使用率の決定

>>:  CSS スタイルをプログラムで処理するためのサンプル コード

推薦する

ウェブデザイン実践者必読のキャリアプラン

原文記事、転載の際は著者と出典を明記してください! Web デザインは間違いなくテクノロジーであり、...

JS配列重複排除の詳細

目次1 テストケース2 JS配列重複排除4種類2.1 要素の比較2.1.1 二重層 for ループ比...

プロトタイプとプロトタイプチェーン プロトタイプとプロトタイプの詳細

目次1. プロトタイプ2. プロトタイプチェーン2.1 コンストラクタ2.2 電話をかける/申し込む...

Nginx proxy_redirect の使用方法の詳細な説明

今日、Apache の nginx リバース プロキシを実行していたときに、ちょっとした問題に遭遇し...

MySQL がユーザー名とパスワードの漏洩を引き起こす可能性のある Riddle の脆弱性を公開

MySQL バージョン 5.5 および 5.6 を標的とする Riddle 脆弱性により、中間者攻撃...

Dockerコンテナの構築と実行のプロセスの詳細な説明

イメージをプルし、コンテナを作成してコンテナを実行するだけです。 docker run -d --r...

Zabbixは複数のmysqlプロセスの監視を実装します

1 つのサーバー上で 3 つの MySQL インスタンス プロセスが開始され、それぞれ異なるポート ...

in() の ID の順序に従った Mysql クエリ結果の順序の詳細な説明

in() の ID の順序に従った Mysql クエリ結果の順序の詳細な説明コード例: <se...

MySQL ストアド プロシージャのクエリ コマンドの概要

以下のように表示されます。 mysql.proc から名前を選択します (db='データベー...

Nginx 502 Bad Gateway エラーの原因と解決策

Nginx 502 Bad Gateway エラーに何度か遭遇しました。ここでメモしておこうと思いま...

クラスタrpmを使用してMySQLをインストールするための詳細な手順

MySQLデータベースをインストールするa) MySQL ソースインストールパッケージをダウンロード...

MySQL の完全バックアップとクイックリカバリ方法

過去 15 日間のデータをバックアップするシンプルな MySQL 完全バックアップ スクリプト。バッ...

GoogleとFacebookがDockerを使わない理由

この記事を書いた理由は、修正した分散 PyTorch プログラムを Facebook のクラスター上...

React スキャフォールディングのパスエイリアスを設定する方法

この記事を書いている時点でのReactのバージョンは16.13.1です1 npm run eject...