MySQL Index Pushdown (ICP) とは何かを理解するための記事

MySQL Index Pushdown (ICP) とは何かを理解するための記事

1. はじめに

ICP (Index Condition Pushdown) は、MySQL 5.6 で導入されたクエリ最適化戦略です。これは、元々サーバー層で実行されていたインデックス条件チェックをストレージ エンジン層にプッシュダウンすることで、テーブル戻りとストレージ エンジン アクセスの数を減らし、クエリ効率を向上させます。

2. 原則

ICP の仕組みを理解するには、まず ICP なしで MySQL がどのようにクエリを実行するかを理解しましょう。

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

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

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

III. 実践

まずテーブルを作成し、レコードを挿入します

テーブルユーザーの作成 (
id int(11) NOT NULL AUTO_INCREMENT COMMENT "主キー",
name varchar(32) COMMENT "名前",
city ​​varchar(32) COMMENT "city",
age int(11) コメント "age",
主キー(ID)、
キー idx_name_city(名前、都市)
)engine=InnoDB デフォルト文字セット=utf8;

ユーザーに挿入(名前、都市、年齢) 値("ZhaoDa", "北京", 20),("QianEr", "上海", 21),("SunSan", "広州", 22), ("LiSi", "深圳", 24), ("ZhouWu", "寧波", 25), ("WuLiu", "杭州", 26), ("ZhengQi", "南寧", 27), ("WangBa", "銀川", 28), ("LiSi", "天津", 29), ("ZhangSan", "南京", 30), ("CuiShi", "鄭州", 65), ("LiSi", "昆明", 29), ("LiSi", "鄭州", 30);

テーブルレコードを確認する

mysql> ユーザーから * を選択します。
+----+----------+----------+------+
| ID | 名前 | 都市 | 年齢 |
+----+----------+----------+------+
| 1 | 趙大 | 北京 | 20 |
| 2 | 千二 | 上海 | 21 |
| 3 | サンサン | 広州 | 22 |
| 4 | LiSi | 深圳 | 24 |
| 5 | 周武 | 寧波 | 25 |
| 6 | 烏柳 | 杭州 | 26 |
| 7 | 鄭斉 | 南寧 | 27 |
| 8 | 王巴 | 銀川 | 28 |
| 9 | リーシ | 天津 | 29 |
| 10 | 張三 | 南京 | 30 |
| 11 | 崔市 | 鄭州 | 65 |
| 12 | リーシ | クンミン | 29 |
| 13 | リーシ | 鄭州 | 30 |
+----+----------+----------+------+
セット内の行数は 13 です (0.00 秒)

このテーブルには結合インデックス (name, city) が作成されていることに注意してください。次のステートメントをクエリするとします。

name="LiSi" かつ city like "%Z%" かつ age > 25 であるユーザーから * を選択します。

3.1 インデックスプッシュダウンを使用しない

インデックス プッシュダウンを使用しない場合、ジョイント インデックスの「左端一致」の原則に従って、name 列のみがインデックスを使用できます。city 列はあいまい一致であるため、インデックスを使用できません。このときの実行プロセスは次のとおりです。

  1. ストレージ エンジンは、(名前、都市) 結合インデックスに基づいて、名前値が LiSi のレコード (合計 4 件のレコード) を検索します。
  2. 次に、これら 4 つのレコードの ID 値に基づいてテーブルが 1 つずつスキャンされ、クラスター化インデックスから完全な行レコードが取得され、これらのレコードがサーバー層に返されます。
  3. サーバー層はこれらのレコードを受信し、条件 name="LiSi"、city like "%Z%"、age > 25 に従ってフィルタリングし、最終的にレコード ("LiSi", "ZhengZhou", 30) を残します。

絵を描いてみましょう:

インデックス条件プッシュダウンは使用されません

3.2 インデックスプッシュダウンの使用

インデックス プッシュダウンを使用する場合、実行プロセスは次のようになります。

  • ストレージ エンジンは、(名前、都市) 結合インデックスに基づいて、name='LiSi' の 4 つのレコードを検索します。
  • 結合インデックスには city 列が含まれているため、ストレージ エンジンは "%Z%" のように結合インデックスを city で直接フィルターします。フィルター処理後、2 つのレコードが残ります。
  • フィルタリングされたレコードの ID 値に従って、テーブルが 1 つずつスキャンされ、クラスター化インデックスから完全な行レコードが取得され、これらのレコードがサーバー層に返されます。
  • サーバー レイヤーは、WHERE ステートメントのその他の条件 (年齢 > 25) に基づいて行を再度フィルターし、最終的にレコード ("LiSi"、"ZhengZhou"、30) のみを残します。

絵を描いてみましょう:


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

また、実行プランからインデックスプッシュダウンが使用されていることもわかります(Extraにインデックス条件の使用が表示されます)

mysql> explain select * from user where name="LiSi" and city like "%Z%" and age > 25;
+----+-------------+--------+-----------+--------+---------------+---------------+---------+-------+---------+---------+------------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+--------+---------------+---------------+---------+-------+---------+---------+------------------------------------+
| 1 | SIMPLE | user | NULL | ref | idx_name_city | idx_name_city | 99 | const | 4 | 7.69 | インデックス条件の使用; where の使用 |
+----+-------------+--------+-----------+--------+---------------+---------------+---------+-------+---------+---------+------------------------------------+
セットに 1 行、警告 1 件 (0.00 秒)

IV. 利用条件

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

ヒント: インデックス プッシュダウンの目的は、テーブル戻り数を減らすこと、つまり IO 操作を減らすことです。 InnoDB のクラスター化インデックスの場合、完全な行レコードがキャッシュにロードされているため、インデックスのプッシュダウンは意味がありません。

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

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

インデックス条件プッシュダウンはデフォルトで有効になっており、システム パラメータ 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 Index Pushdown (ICP) についての説明はこれで終わりです。MySQL Index Pushdown (ICP) の詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL インデックス データ構造の詳細な分析
  • MySQLデータベースのトランザクションとインデックスの詳細な説明
  • MySQL インデックス プッシュダウンの詳細
  • MySQLはインデックスプッシュダウンを数秒で理解するのに役立ちます
  • MySQL インデックス プッシュダウンを 5 分で理解する
  • MySQL 面接の質問: ハッシュ インデックスの設定方法

<<:  フレックスレイアウトにおける画像変形の解決策の詳細な説明

>>:  docker システムコマンドセットの使用

推薦する

Centos 用の rpm パッケージのカスタマイズと yum リポジトリの構築に関するチュートリアル

1 yumでソフトウェアをインストールしたときにダウンロードしたrpmパッケージを保存しますyum ...

Docker+Nginx を使ってシングルページアプリケーションをデプロイする

開発から導入まで自分で行うシングルページアプリケーションを開発する場合、ビルドを実行した後 npm ...

MySQL は重複データを削除して最小の ID ソリューションを維持します

オンラインで検索して重複データを削除し、ID が最小のデータだけを残します。方法は次のとおりです。 ...

背景とリンクとして画像を書き込む方法(背景画像+リンク)

写真を背景にしてリンクを記載します。たとえば、ウェブサイトのロゴ画像などです。例: ポテトのロゴ画像...

検索ボックスと検索ボタンの境界線が重なり合わない問題を解決

今日、Baiduのページで練習していたところ、検索ボックスとボタンの余白とパディングの値が0に設定さ...

JDカルーセル効果を実現するための純粋なHTMLとCSS

JD カルーセルは、動的な効果を追加せず、主に位置決めの知識を使用して、純粋な HTML と CS...

MySQLでSQLクエリ文がどのように実行されるかを分析する

目次1. MySQL論理アーキテクチャの概要2. コネクタクエリキャッシュ4. パーサー5. オプテ...

nginxコンテナ設定ファイルの独立した実装

コンテナを作成する [root@server1 ~]# docker run -it --name ...

CSS で画像アダプティブ コンテナを実装するためのサンプル コード

多くの場合、画像をコンテナのサイズに合わせて調整する必要があります。 1. imgタグ方式幅と高さを...

MySQL における datetime と timestamp の違いと選択

目次1 違い1.1 スペース占有1.2 表現範囲1.3 タイムゾーン2 テスト3つの選択肢MySQL...

MySQL の datetime フィールドの丸め操作

目次序文1. 背景2. シミュレーションテスト3. 結論要約する序文もし私が罠に陥っていなかったら、...

el-table カプセル化に基づくドラッグ可能な行と列、および選択列コンポーネントの実装

効果環境が必要ビュー要素UIドラッグアンドドロッププラグインSortable.js必要な構成プロパテ...

Mysql 5.7.19 無料インストール バージョンで遭遇した落とし穴 (コレクション)

1. 公式ウェブサイトから 64 ビットの zip ファイルをダウンロードします。 2. インスト...

Centos7.3は起動時に自動的に起動または指定されたコマンドを実行します

Centos7では、/etc/rc.d/rc.localファイルの権限が削減されており、実行権限があ...

Vue 手書き読み込みアニメーション プロジェクト

ページが応答しない場合、白い画面が表示されないように、読み込みアニメーションを表示するのがユーザーフ...