第26期:索引设计(索引下推) - 技术分享 - 新闻资讯 - 爱可生

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

发布时间:2021-04-27 浏览次数:399

关键字:分布式数据库架构分布式数据库软件分库分表中间件

索引下推(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 不支持引用子查询的条件。

关键字:分布式数据库架构分布式数据库软件分库分表中间件

相关推荐

产品试用 产品试用
400-820-6580 免费电话