MySQL インデックス プッシュダウンを 5 分で理解する

MySQL インデックス プッシュダウンを 5 分で理解する

面接中に「MySQL 5.6」や「インデックス最適化」などの用語を聞いた場合、この質問は「インデックス プッシュダウン」に関するものであることがすぐにわかるはずです。

インデックス プッシュダウンとは何ですか?

Index Condition Pushdown (ICP) は、MySQL 5.6 の新機能です。テーブルクエリの数を減らし、クエリの効率を向上させることができます。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ICPは使用されていません

MySQL 5.6 より前では、ストレージ エンジンは結合インデックスを通じてname likelike '張%'の主キー ID (1, 4) を見つけ、テーブルを 1 つずつスキャンし、クラスター化インデックスを削除して完全な行レコードを見つけ、サーバー レイヤーはage=10進行篩選

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

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

ICPの使用

MySQL 5.6 以降では、ストレージ エンジンは (name, age) 結合インデックスに基づいてname likelike '張% ' を検索します。結合インデックスには 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 | インデックス条件を使用 |
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+-------+---------+------------------------+

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

  • 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" を設定します。

要約する

この記事はこれで終わりです。皆さんのお役に立てれば幸いです。また、123WORDPRESS.COM のその他のコンテンツにも注目していただければ幸いです。

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

<<:  ブラウザのキャッシュを防ぐために、js または css の後に ?v= バージョン番号を追加します。

>>:  Flexレイアウトを使用してヘッドの固定コンテンツ領域のスクロールを実現する方法

推薦する

mysql 8.0.16 winx64.zip インストールと設定方法のグラフィックチュートリアル

この記事では、MySQL 8.0.16 winx64.zipのインストールと設定方法の具体的なコード...

純粋な CSS 流星群の背景サンプルコード

GitHubアドレス、気に入ったらスターを付けてくださいプラグインのプレビューチュートリアルコード表...

MySQLはライブラリ内の主キーなしでテーブルインスタンスコードを素早く取得します

概要MySQL データベースで主キーのないテーブルを表示するための SQL ステートメントをいくつか...

linxu での Svn ワンクリック インストール シェル スクリプトの詳細な説明

#!/bin/bash #SVNをダウンロード yum -y サブバージョンをインストールします ...

知らないかもしれないmysqldumpパラメータ

前回の記事で、mysqldump バックアップ ファイルに記録されるタイムスタンプ データは UTC...

Linux/Mac に MySQL をインストールするときにパスワードを忘れた場合の解決策

序文この記事では主に、Linux/Mac に MySQL をインストールするときにパスワードを忘れた...

vite2.0+vue3 モバイルプロジェクトの詳細な説明

1. 関連する技術的なポイントバイト版ヴュー3 ts統合ルーティングvuexを統合するAxiosを統...

HTML 内の input type="reset" タグが無効 (機能しない) である理由として考えられるもの。

<html:reset> タグを使用すると、リセット ボタンが無効になり、ボタンをクリッ...

ページキャッシュを無効にするいくつかの方法を共有する

本日、開発中に、顧客からページをキャッシュしないように要求される方法に遭遇しました。調べたところ、ペ...

MySQL マルチバージョン同時実行制御 MVCC の実装

トランザクション分離レベルの設定 グローバルトランザクション分離レベルを読み取りコミット済みに設定;...

Vue コンポーネントはどのように解析され、レンダリングされるのでしょうか?

序文この記事では、Vue コンポーネントがどのように解析され、レンダリングされるかを説明します。 V...

MySQL の最初のインストールが成功した後にパスワードを初期化する手順

ファイルをディレクトリに解凍しますこれは解凍後のディレクトリですmy.iniファイルを入力しますダブ...

MySQL8インストーラーバージョングラフィックチュートリアル

インストール必要な書類は下部に記載されていますステップ1 mysql-installer-web-c...

ElementuiはデータをxlsxとExcelテーブルにエクスポートします

最近、Vue プロジェクトについて知り、ElementUI でデータを xlsx および Excel...

Linux コンパイル最適化で習得しなければならないいくつかの姿勢のまとめ

01. コンパイルオプションとカーネルコンパイルLinux カーネル (英語: linux kern...