MySQL インデックス プッシュダウンを 1 つの記事で理解する

この記事では、mysql に関する関連知識を提供します。主にインデックス プッシュダウンに関する関連コンテンツを紹介します。インデックス条件プッシュダウンは、インデックス プッシュダウンとも呼ばれます。正式な英語名は、Index Condition Pushdown と呼ばれます。 ICP はデータ クエリを最適化するために使用されます。見てみましょう。皆さんのお役に立てれば幸いです。

推奨学習: mysql ビデオ チュートリアル

SELECT ステートメントの実行プロセス

MySQL データベースは、Server レイヤーと Engine レイヤーで構成されます。

• Server レイヤー: SQL アナライザー、SQL オプティマイザー、SQL エグゼキューターがあり、SQL ステートメントの特定の実行プロセスを担当します。 。
• エンジン レイヤー: 最も一般的に使用される InnoDB ストレージ エンジンなどの特定のデータの保存と、メモリへの一時データの保存を担当します。結果セット用の TempTable エンジン。

• クライアント/サーバー通信プロトコルを通じて MySQL への接続を確立します。

• クエリ キャッシュ:

  • クエリ キャッシュが有効で、クエリ キャッシュが完全にクエリされている場合同じ SQL ステートメントが使用されている場合、クエリ結果はクライアントに直接返されます。
  • クエリ キャッシュ がオンになっていない場合、またはまったく同じ SQL # クエリは実行されません ## ステートメントはパーサーによって構文的および意味的に解析され、解析ツリーが生成されます。
• パーサーは新しい解析ツリーを生成します。

• クエリ オプティマイザーは実行プランを生成します。

• クエリ実行エンジンは

  SQL ステートメントを実行します。このとき、クエリ実行エンジンは SQL 内のテーブルのストレージ エンジン タイプを決定します。 ステートメントと対応する API インターフェイスは、基礎となるストレージ エンジン キャッシュまたは物理ファイルと対話してクエリ結果を取得します。MySQL Server でフィルタリングした後、クエリ結果はキャッシュされて返されます。クライアントに。

  クエリ キャッシュが有効になっている場合、SQL ステートメントと結果は完全に クエリ キャッシュに保存されます。 #SQL ステートメントが今後実行されると、結果が直接返されます。

ヒント: MySQL 8.0 では、クエリ キャッシュ (クエリ キャッシュ モジュール) が削除されました。

クエリ キャッシュのヒット率が非常に低くなるためです。クエリ キャッシュの無効化は非常に頻繁に発生します。テーブルが更新されるたびに、そのテーブル上のすべてのクエリ キャッシュがクリアされます。

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

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

: ICP と呼ばれ、インデックス フィルタリング条件をストレージ エンジンにプッシュダウンすることで、# を削減します。 ##MySQL ストレージ エンジンがベース テーブルにアクセスする回数と MySQL サービス層がストレージ エンジンにアクセスする回数。 インデックス プッシュダウン VS カバリング インデックス: 実際には、どちらも

テーブルの戻り数を削減します

が、方法は異なります

カバーされたインデックス:

インデックスに必須フィールド (
• SELECT XXX

  ) が含まれている場合、クエリのためにテーブルにそれ以上のフィールドは返されません。

  インデックス プッシュダウン:
インデックスに含まれるフィールドを最初に判断します。
• 条件を満たさないレコードを直接除外します。

  、そして、テーブルが返す行数を減らします。

ICP

の仕組みを理解するには、クエリ

SQL: Give a Chestnut: 名前が la で始まり、経過時間が

18

<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:php;toolbar:false;'>SELECT * FROM user WHERE name LIKE &amp;#39;la%&amp;#39; AND age = 18;</pre><div class="contentsignin">ログイン後にコピー</div></div> であるレコードをクエリします。次のレコードがあります:

ICP

が有効になっていない場合のインデックス スキャンの実行方法:

対応するデータ行を見つけて読み取ります。 (実際にはテーブルを返すだけです)

WHERE
• のフィールドを判断して、条件を満たさない行を除外します。

ICP

を使用すると、インデックス スキャンは次のように実行されます:

インデックス要素グループを取得します。

WHERE
• のフィールドを判断し、インデックス列でフィルタリングします。
• 条件を満たすインデックスの場合、テーブルを返し、行全体をクエリします。
WHERE
• のフィールドを判断して、条件に当てはまらない行を除外します。

动手实验：

实验：使用 MySQL 版本 8.0.16

-- 表创建
CREATE TABLE IF NOT EXISTS `user` (
`id` VARCHAR(64) NOT NULL COMMENT '主键 id',
`name` VARCHAR(50) NOT NULL COMMENT '名字',
`age` TINYINT NOT NULL COMMENT '年龄',
`address` VARCHAR(100) NOT NULL COMMENT '地址',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '用户表';

-- 创建索引
CREATE INDEX idx_name_age ON user (name, age);

-- 新增数据
INSERT INTO user (id, name, age, address) VALUES (1, 'tt', 14, 'linhai');
INSERT INTO user (id, name, age, address) VALUES (2, 'lala', 18, 'linhai');
INSERT INTO user (id, name, age, address) VALUES (3, 'laxi', 30, 'linhai');
INSERT INTO user (id, name, age, address) VALUES (4, 'lawa', 40, 'linhai');

-- 查询语句
SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

新增数据如下：



• 关闭 ICP，再调用 EXPLAIN 查看语句：

-- 将 ICP 关闭
SET optimizer_switch = 'index_condition_pushdown=off';
-- 查看确认
show variables like 'optimizer_switch';

-- 用 EXPLAIN 查看
EXPLAIN SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;



• 开启 ICP，再调用 EXPLAIN 查看语句：

-- 将 ICP 打开
SET optimizer_switch = 'index_condition_pushdown=on';
-- 查看确认
show variables like 'optimizer_switch';

-- 用 EXPLAIN 查看
EXPLAIN SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;



由上实验可知，区别是否开启 ICP： Exira 字段中的 Using index condition



更进一步，来看下 ICP 带来的性能提升：

通过访问数据文件的次数

-- 1. 清空 status 状态
flush status;
-- 2. 查询
SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
-- 3. 查看 handler 状态
show status like '%handler%';

对比开启 ICP 和 关闭 ICP： 关注 Handler_read_next 的值

-- 开启 ICP
flush status;
SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
show status like '%handler%';
+----------------------------|-------+
| Variable_name              | Value |
+----------------------------|-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |  
| Handler_read_last          | 0     |
| Handler_read_next          | 1     |  <---重点
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------|-------+
18 rows in set (0.00 sec)


-- 关闭 ICP
flush status;
SELECT * FROM user WHERE name LIKE &#39;la%&#39; AND age = 18;
show status like '%handler%';
+----------------------------|-------+
| Variable_name              | Value |
+----------------------------|-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 3     |  <---重点
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------|-------+
18 rows in set (0.00 sec)

由上实验可知：

• 开启 ICP：Handler_read_next 等于 1，回表查 1 次。
• 关闭 ICP：Handler_read_next 等于 3，回表查 3 次。

这实验跟上面的栗子就对应上了。

索引下推限制

根据官网可知，索引下推 受以下条件限制：

• 当需要访问整个表行时，ICP 用于 range、 ref、 eq_ref 和 ref_or_null

• ICP可以用于 InnoDB 和 MyISAM 表，包括分区表 InnoDB 和 MyISAM 表。

• 对于 InnoDB 表，ICP 仅用于二级索引。ICP 的目标是减少全行读取次数，从而减少 I/O 操作。对于 InnoDB 聚集索引，完整的记录已经读入 InnoDB 缓冲区。在这种情况下使用 ICP 不会减少 I/O。

• 在虚拟生成列上创建的二级索引不支持 ICP。InnoDB 支持虚拟生成列的二级索引。

• 引用子查询的条件不能下推。

• 引用存储功能的条件不能被按下。存储引擎不能调用存储的函数。

• 触发条件不能下推。

• 不能将条件下推到包含对系统变量的引用的派生表。（MySQL 8.0.30 及更高版本)。

小结下：

• ICP 仅适用于 二级索引。
• ICP 目标是 减少回表查询。
• ICP 对联合索引的部分列模糊查询非常有效。

拓展：虚拟列

CREATE TABLE UserLogin (
userId BIGINT,
loginInfo JSON,
cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
PRIMARY KEY(userId),
UNIQUE KEY idx_cellphone(cellphone)
);

列 cellphone ：就是一个虚拟列，它是由后面的函数表达式计算而成，本身这个列不占用任何的存储空间，而索引 idx_cellphone 实质是一个函数索引。

好处： 在写 SQL 时可以直接使用这个虚拟列，而不用写冗长的函数。

举个栗子： 查询手机号

-- 不用虚拟列
SELECT * FROM UserLogin WHERE loginInfo->>"$.cellphone" = '13988888888'

-- 使用虚拟列
SELECT * FROM UserLogin WHERE cellphone = '13988888888'

推荐学习：mysql视频教程

以上がMySQL インデックス プッシュダウンを 1 つの記事で理解するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。