Understanding MySQL index pushdown in one article

This article brings you relevant knowledge about mysql, which mainly introduces the relevant content about index pushdown. Index condition pushdown is also called index pushdown. The full English name is Index Condition Pushdown. , referred to as ICP, is used to optimize data queries. Let’s take a look at it. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

SELECT statement execution process

MySQL The database consists of the Server layer and the Engine layer:

• Server Layer: There are SQL analyzer, SQL optimizer, SQL executor, which are responsible for the specific execution process of SQL statements.
• Engine Layer: Responsible for storing specific data, such as the most commonly used InnoDB storage engine, and for storing temporary data in memory The TempTable engine for result sets.

• Establishes a connection to MySQL through the client/server communication protocol.

• Query Cache:

  • If Query Cache is enabled and the query cache is fully queried If the same SQL statement is used, the query results will be returned directly to the client;
  • If Query Cache is not turned on or the exact same SQL# is not queried ## The statement will be parsed syntactically and semantically by the parser and a parse tree will be generated.
• The parser generates a new parse tree.

• The query optimizer generates an execution plan.

• The query execution engine executes the

  SQL statement. At this time, the query execution engine will determine the storage engine type of the table in the SQL statement and the corresponding API The interface interacts with the underlying storage engine cache or physical files to obtain query results. After filtering by MySQL Server, the query results are cached and returned to the client.

  If

  Query Cache is enabled, SQL statements and results will be completely saved to Query Cache. If the same SQL statement is executed in the future, the result will be returned directly.

Tips: MySQL 8.0 has removed query cache (query cache module).

Because the hit rate of the query cache will be very low. Query cache invalidations are very frequent: whenever there is an update to a table, all query caches on that table are cleared.

What is index pushdown?

Index Condition Pushdown: Referred to as ICP, it reduces # by pushing down the index filtering conditions to the storage engine. ##MySQL The number of times the storage engine accesses the base table and MySQL The number of times the service layer accesses the storage engine. Index pushdown VS covering index:

In fact, they both

reduce the number of table returns, but in different ways

Covered index:
• When the index contains the required fields (

  SELECT XXX), no more fields will be returned to the table to query.

Index pushdown:
• Make a judgment first on the fields included in the index,

  Directly filter out records that do not meet the conditions , and reduce table returns number of rows.

To understand how

ICP

works, start with a query SQL: Give a chestnut: Query the records whose name starts with la

and whose age is

18

SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

has these records:

How the index scan is performed when

ICP

is not enabled: Locate and read the corresponding data row. (Actually: just return the table)

Make judgments on the fields in
• WHERE
and filter out rows that do not meet the conditions.

Using

ICP

, the index scan is performed as follows: Get the index element Group.

Make judgment on the fields in
• WHERE
and filter in the index column.
• For indexes that meet the conditions, return the table to query the entire row.
Make judgments on the fields in
• WHERE
and filter out rows that do not meet the conditions.

动手实验：

实验：使用 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视频教程

The above is the detailed content of Understanding MySQL index pushdown in one article. For more information, please follow other related articles on the PHP Chinese website!