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 システムコマンドセットの使用

推薦する

js でクラスセレクターと名前属性セレクターを実装する手順の例

jQuery の登場により、DOM の操作効率が大幅に向上し、開発がより高いレベルに引き上げられまし...

Vue+Openlayerはグラフィックスのドラッグと回転変形効果を実現します

目次序文関連資料成果を達成する実装手順序文Openlayer には独自の拡張プラグイン ol-ext...

Vue で Alibaba のアイコンフォント ベクター アイコンを使用する方法について

インターネット上には多くのインポート方法があり、公式も3つのインポート方法を提供していますが、インポ...

Pythonの関数知識についての簡単な説明

目次関数パラメータの2つの主要なカテゴリ位置パラメータ可変長パラメータ名前空間要約する関数パラメータ...

JavaScript の useRef と useState の紹介

目次1. useStateフック2. useRefフック3. useRef と useState 4...

Linuxでディスク使用量を確認する方法

1. dfコマンドを使用してディスク全体の使用量を表示します。 df コマンドは、ハードディスクのマ...

vue cli で env を使用するガイド

目次序文紹介-公式設定例序文vueCli を使用してプロジェクトを開発したことのある方は、少しがっか...

CSSでスペースを処理する方法

1. 宇宙のルールHTML コード内の空白は通常、ブラウザによって無視されます。 <p>...

Vueでクラススタイルを使用する方法の詳細

目次1. ブール2. 表現3. マルチクラスパッケージ4. v-bind でクラス class を直...

「いいね!」機能では MySQL と Redis のどちらを使用すればよいでしょうか?

目次1. 初心者が陥りがちな間違い2. Iteratorのremove()メソッドを使用する3. f...

Linux CentOS 6.5 ifconfig が IP を照会できない問題の解決方法

最近、何人かの友人から、仮想マシンに CentOS をインストールした後、ifconfig コマンド...

CSSにスティッキー効果を追加する方法

前面に書かれたfilter:blur と filter:contrast がこのような素晴らしい化学...

MySQL の暗号化と復号化の例

MySQL の暗号化と復号化の例データの暗号化と復号化はセキュリティ分野で非常に重要です。プログラマ...

GaussDB for MySQL パフォーマンス最適化の詳細な説明

目次背景インスピレーションは人生から生まれる速達配送の最適化原則GaussDB の最適化 (MySQ...

docker公式mysqlイメージのカスタム構成の詳細な説明

インストール時間を節約するために、公式の mysql docker イメージを使用して mysql ...