第26期:索引设计(索引下推)

爱可生云数据库

索引下推(INDEX CONDITION PUSHDOWN,简称 ICP)是 MySQL 5.6 发布后针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎,来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。ICP 适用于 MYISAM 和 INNODB,本篇的内容只基于 INNODB。

MySQL ICP 里涉及到的知识点如下:

1.MySQL 服务层:也就是 SERVER 层,用来解析 SQL 的语法、语义、生成查询计划、接管从 MySQL 存储引擎层上推的数据进行二次过滤等等。

2.MySQL 存储引擎层:按照 MySQL 服务层下发的请求,通过索引或者全表扫描等方式把数据上传到 MySQL 服务层。

3.MySQL 索引扫描:根据指定索引过滤条件(比如 where id = 1) ,遍历索引找到索引键对应的主键值后回表过滤剩余过滤条件。

4.MySQL 索引过滤:通过索引扫描并且基于索引进行二次条件过滤后再回表。

ICP 就是把以上索引扫描和索引过滤合并在一起处理,过滤后的记录数据下推到存储引擎后的一种索引优化策略。这样做的优点如下:

1.减少了回表的操作次数。

2.减少了上传到 MySQL SERVER 层的数据。

ICP 默认开启,可通过优化器开关参数关闭 ICP:optimizer_switch='index_condition_pushdown=off' 或者是在 SQL 层面通过 HINT 来关闭。

接下来,详细看下不适用 ICP、使用 ICP 的详细示例来理清 ICP 的概念。

在不使用 ICP 索引扫描的过程:

MySQL 存储引擎层只把满足索引键值对应的整行表记录一条一条取出,并且上传给 MySQL 服务层。

MySQL 服务层对接收到的数据,使用 SQL 语句后面的 where 条件过滤,直到处理完最后一行记录,再一起返回给客户端。

假设 SQL 语句为:

(localhost:mysqld.sock)|(ytt)>select * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G
*************************** 1. row ***************************
id: 28965
f1: 81
f2: 89
f3: 100
f4: 35
r1: 1
r2: 12844bda dog 11ea a051 08002753f58d
r3: 17
r4: 5
1 row in set (0.00 sec)

关闭 ICP 的处理流程大概如图 1:

使用 ICP 扫描的过程:

MySQL 存储引擎层,先根据过滤条件中包含的索引键确定索引记区间,再在这个区间的记录上使用包含索引键的其他过滤条件进行过滤,之后规避掉不满足的索引记录,只根据满足条件的索引记录回表取回数据上传到 MySQL 服务层。

MySQL 服务层对接收到的数据,使用 where 子句中不包含索引列的过滤条件做最后的过滤,然后返回数据给客户端。

如下图所示:

上面两张图很明显的对比出开启 ICP 比不开启 ICP 的效率。返回数据这一块虚线表示规避掉的记录,开启 ICP 很明显减少了上传到 MySQL 存储引擎层、MySQL 服务层的记录条数,节省了 IO。

查看语句是否用了 ICP,只需要对语句进行 EXPLAIN,在 EXTRA 信息里可以看到 ICP 相关信息。

以下为分别为关闭 ICP 与开启 ICP 的 EXPLAIN 结果:

(localhost:mysqld.sock)|(ytt)>explain select /*+ no_icp (t1)  */ * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_r4,idx_u1
          key: idx_u1
      key_len: 5
          ref: const
         rows: 325
     filtered: 0.12
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

(localhost:mysqld.sock)|(ytt)>explain select * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G  *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_r4,idx_u1
          key: idx_u1
      key_len: 5
          ref: const
         rows: 325
     filtered: 0.12
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)

其中 extra 里显示 “Using index condition” 就代表用了 ICP。不过这个信息有点过于简单了,除了 EXTRA 列结果显示不同外,其他的列结果都一样,没法从执行计划结果判断 ICP 的优略。

可以通过以下几种方法来查看 ICP 带来的直观性能提升。

1.show status like '%handler%'

show status 语句可以查看对存储引擎的相关指标监控结果。从以下结果可以看出:指标 Handler_read_next(表示 MySQL 存储引擎按照索引键顺序读取下一行记录的请求数,也就是说这个值表示按照索引键值来访问基表的请求数)在没有开启 ICP 时,值为 325,也就是说对基表读取请求 325 次;而开启 ICP 后,这个值仅有 14 次。所以开启 ICP 效率提升很明显。

(localhost:mysqld.sock)|(ytt)>flush status;
Query OK, 0 rows affected (0.01 sec)

(localhost:mysqld.sock)|(ytt)> select /*+ no_icp (t1) */ * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G
*************************** 1. row ***************************
id: 28965
f1: 81
f2: 89
f3: 100
f4: 35
r1: 1
r2: 12844bda dog 11ea a051 08002753f58d
r3: 17
r4: 5
1 row in set (0.00 sec)


(localhost:mysqld.sock)|(ytt)>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          | 325   |
| 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)

(localhost:mysqld.sock)|(ytt)>flush status;
Query OK, 0 rows affected (0.01 sec)


(localhost:mysqld.sock)|(ytt)>select * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G
*************************** 1. row ***************************
id: 28965
f1: 81
f2: 89
f3: 100
f4: 35
r1: 1
r2: 12844bda dog 11ea a051 08002753f58d
r3: 17
r4: 5
1 row in set (0.00 sec)


(localhost:mysqld.sock)|(ytt)>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          | 14    |
| 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)

(localhost:mysqld.sock)|(ytt)>

2.开启 profiles

查看 profile 结果的总体时间,关闭 ICP 为:0.00101900,开启ICP为:0.00100325。时间上 ICP 占优势。

(localhost:mysqld.sock)|(ytt)>set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

(localhost:mysqld.sock)|(ytt)>show profiles;
Empty set, 1 warning (0.00 sec)

(localhost:mysqld.sock)|(ytt)> select /*+ no_icp (t1) */ * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G
*************************** 1. row ***************************
id: 28965
f1: 81
f2: 89
f3: 100
f4: 35
r1: 1
r2: 12844bda dog 11ea a051 08002753f58d
r3: 17
r4: 5
1 row in set (0.00 sec)

(localhost:mysqld.sock)|(ytt)> select  * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G        *************************** 1. row ***************************
id: 28965
f1: 81
f2: 89
f3: 100
f4: 35
r1: 1
r2: 12844bda dog 11ea a051 08002753f58d
r3: 17
r4: 5
1 row in set (0.00 sec)


(localhost:mysqld.sock)|(ytt)>show profiles\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.00101900
Query: select /*+ no_icp (t1) */ * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5
*************************** 2. row ***************************
Query_ID: 2
Duration: 0.00100325
Query: select  * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5
2 rows in set, 1 warning (0.00 sec)

任何需要下推到底层存储层的操作一般都有诸多限制,MySQL ICP 也不例外,ICP 限制如下:

1.ICP 仅用于需要访问基表所有记录时使用,适用的访问方法为:range、ref、eq_ref、ref_or_null。我上面举的例子即是 ref 类型,ICP 尤其是对联合索引的部分列模糊查找非常有效。

2.ICP 同样适用于分区表。

3.ICP 的目标是减少全行记录读取,从而减少 I/O 操作,仅用于二级索引。主键索引本身即是表数据,不存在下推操作。

4.ICP 不支持基于虚拟列上建立的索引,比如函数索引。

5.ICP 不支持引用子查询的条件。


关于 MySQL 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!

阅读 479

中国领先的企业数据处理技术整体解决方案提供商,开源数据库领域优秀企业。为大型行业用户的特定场景提供深度挖掘数据价值的解决方案,覆盖分布式数据库集群、云数据库平台、数据库大体量运管平台、海量数据集成与存储、清洗与治理、人工智能分析挖掘、可视化展现、安全与隐私保护等多个领域。

180 声望
124 粉丝
0 条评论
你知道吗?

中国领先的企业数据处理技术整体解决方案提供商,开源数据库领域优秀企业。为大型行业用户的特定场景提供深度挖掘数据价值的解决方案,覆盖分布式数据库集群、云数据库平台、数据库大体量运管平台、海量数据集成与存储、清洗与治理、人工智能分析挖掘、可视化展现、安全与隐私保护等多个领域。

180 声望
124 粉丝
宣传栏