技术分享 | SQL 优化：ICP 的缺陷

SQL语句
456
0
0
2023-01-08
SQL面试 100 问
上一篇
SQL中GROUP BY语句介绍
下一篇
标签   SQL优化
作者：胡呈清
爱可生 DBA 团队成员，擅长故障分析、性能优化，个人博客：https://www.jianshu.com/u/a95ec11f67a8，欢迎讨论。
本文来源：原创投稿
*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

什么是ICP（Index Condition Pushdown）

ICP全称 Index Condition Pushdown，也就是常说的索引条件下推，在之前的一篇文章中介绍过它：explain 执行计划详解2--Extra

使用二级索引查找数据时，where 子句中属于索引的一部分但又无法使用索引的条件，MySQL会把这部分条件下推到存储引擎层，筛选之后再进行回表，这样回表的次数就减少了。

比如有这样一个索引idx_test(birth_date,first_name,hire_date)

查询语句select * from employees where birth_date >= '1957-05-23' and birth_date <='1960-06-01' and hire_date>'1998-03-22';的执行过程：

1.根据 birth_date >= '1957-05-23' and birth_date <='1960-06-01' 这个条件从 idx_test 索引中查找数据，假设返回数据 10万行；

2.查找出来的10万行数据包含 hire_date 字段，MySQL会把 hire_date>'1998-03-22' 这个条件下推到存储引擎，进一步筛选数据，假设还剩1000行；

3.由于要查询所有字段的值，而前面查到的 1000 行数据只包含 birth_date,first_name,hire_date 三个字段，所以需要回表查出所有字段的值。回表的过程就是将这 1000 行数据的主键值拿出来，一个一个到主键索引上去查找（也可以开启 mrr，拿一批主键值回表），回表次数是 1000。如果没有ICP，则回表次数是 10 万。

很显然在执行阶段 ICP 可以减少回表的次数，在基于代价的优化器中，也就是能减少执行的成本。但是，优化器在优化阶段选择最优的执行计划时真的能考虑到 ICP 可以减少成本吗？下面我们通过一个实验来回答这个问题。

实验

先准备一些数据，下载 Employees Sample Database 并导入到 MySQL 中：https://dev.mysql.com/doc/employee/en/employees-installation.html

还是上面那个例子，创建一个组合索引：

alter table employees add index idx_test(birth_date,first_name,hire_date);

执行下面这个SQL：

SELECT *
FROM employees
WHERE birth_date >= '1957-05-23' 
    AND birth_date <= '1960-06-01' 
    AND hire_date > '1998-03-22';

执行计划如下：

mysql [localhost:5735] {msandbox} (employees) > explain select * from employees where birth_date >= '1957-05-23' and birth_date <='1960-06-01' and hire_date>'1998-03-22';
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_test      | NULL | NULL    | NULL | 298980 |    15.74 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+

可以看到并没有使用 idx_test 索引，但如果加 hint 强制走 idx_test 索引，我们知道可以使用 ICP，执行计划如下：

mysql [localhost:5735] {msandbox} (employees) > explain select * from employees force index(idx_test) where birth_date >= '1957-05-23' and birth_date <='1960-06-01' and hire_date>'1998-03-22';
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key      | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_test      | idx_test | 3       | NULL | 141192 |    33.33 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

再让我们打开 slow log 看下真实的执行效率：

  • 全表扫描需要扫描 300024 行，执行时间 0.15 秒
  • 走 idx_test 索引需要扫描 141192 行（Rows_examined: 1065 是个 bug，这显然不是扫描行数，扫描行数我们可以从执行计划看出，在这个例子中执行计划里的 rows 是真实的扫描行数，不是估算值，这个知识点不影响理解本文）。因为没有其他条件，从返回结果行数我们也能知道回表次数就是 1065，执行时间只要 0.037 秒
# Time: 2022-11-24T18:02:01.001734+08:00
# Query_time: 0.146939  Lock_time: 0.000850 Rows_sent: 1065  Rows_examined: 300024
SET timestamp=1669284095;
select * from employees where birth_date >= '1957-05-23' and birth_date <='1960-06-01' and hire_date>'1998-03-22';
# Time: 2022-11-24T18:01:09.001223+08:00
# Query_time: 0.037211  Lock_time: 0.001649 Rows_sent: 1065  Rows_examined: 1065
SET timestamp=1669284032;
select * from employees force index(idx_test) where birth_date >= '1957-05-23' and birth_date <='1960-06-01' and hire_date>'1998-03-22';

很显然走 idx_test 索引比全表扫描效率更高，那为什么优化器不选择走 idx_test 索引呢？一个不会犯错的说法是优化器有它的算法，并不以人类认为的时间快慢为标准来进行选择。这次我们打破砂锅问到底，优化器的算法是什么？

答案是成本，优化器在选择最优的执行计划时会计算所有可用的执行计划的成本，然后选择成本最小的那个。而成本有明确的计算方法，也能通过 explain format=json 展示执行计划的成本，因此我们用这一点来证明 ICP 能否影响执行计划的成本。关于 explain format=json 的详细输出解释可以参考：explain format=json 详解，本文不过多展开。

成本计算

1.I/O成本

表的数据和索引都存储到磁盘上，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。

2.CPU成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。

3.成本常数

对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL5.7 中规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数（不同版本可能不一样，可以通过 mysql.server_cost、mysql.engine_cost 查看）。

不加干涉时，优化器选择全表扫描，总成本为 "query_cost": "60725.00"，计算公式：

  • IO成本：929*1 = 929 （929 是主键索引的页数，通过表的统计信息中的 Data_length/pagesize 得到）
  • CPU 成本：298980*0.2 = 59796（298980是扫描行数，全表扫描时这是一个估算值，也就是表的统计信息中的 Rows）
  • 总成本 = IO成本 + CPU 成本 = 929 + 59796 = 60725
mysql [localhost:5735] {msandbox} (employees) > explain format=json select * from employees  where birth_date >= '1957-05-23' and birth_date <='1960-06-01' and hire_date>'1998-03-22'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "60725.00"
    },
    "table": {
      "table_name": "employees",
      "access_type": "ALL",
      "possible_keys": [
        "idx_test"
      ],
      "rows_examined_per_scan": 298980,
      "rows_produced_per_join": 47059,
      "filtered": "15.74",
      "cost_info": {
        "read_cost": "51313.14",
        "eval_cost": "9411.86",
        "prefix_cost": "60725.00",
        "data_read_per_join": "6M"
      },
      "used_columns": [
        "emp_no",
        "birth_date",
        "first_name",
        "last_name",
        "gender",
        "hire_date"
      ],
      "attached_condition": "((`employees`.`employees`.`birth_date` >= '1957-05-23') and (`employees`.`employees`.`birth_date` <= '1960-06-01') and (`employees`.`employees`.`hire_date` > '1998-03-22'))"
    }
  }
}
1 row in set, 1 warning (0.00 sec)

hint 走 idx_test 索引时，总成本为 "query_cost": "197669.81"，计算公式：

  • 访问 idx_test 索引的成本：
  • IO 成本=1*1=1（优化器认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的，而条件中只有 birth_date >= '1957-05-23' and birth_date <='1960-06-01' 这一个范围）
  • CPU 成本 = 141192*0.2 = 28238.4（扫描行数 "rows_examined_per_scan": 141192）
  • 回表的成本（不会考虑索引条件下推的作用，因此回表次数等于索引扫描行数）：
  • 回表 IO 成本 = 141192*1 = 141192
  • 回表 CPU 成本 = 141192*0.2 = 28238.4
  • 总成本：1+28238.4+141192+28238.4=197669.8
mysql [localhost:5735] {msandbox} (employees) > explain format=json select * from employees force index(idx_test) where birth_date >= '1957-05-23' and birth_date <='1960-06-01' and hire_date>'1998-03-22'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "197669.81"
    },
    "table": {
      "table_name": "employees",
      "access_type": "range",
      "possible_keys": [
        "idx_test"
      ],
      "key": "idx_test",
      "used_key_parts": [
        "birth_date"
      ],
      "key_length": "3",
      "rows_examined_per_scan": 141192,
      "rows_produced_per_join": 47059,
      "filtered": "33.33",
      "index_condition": "((`employees`.`employees`.`birth_date` >= '1957-05-23') and (`employees`.`employees`.`birth_date` <= '1960-06-01') and (`employees`.`employees`.`hire_date` > '1998-03-22'))",
      "cost_info": {
        "read_cost": "188257.95",
        "eval_cost": "9411.86",
        "prefix_cost": "197669.81",
        "data_read_per_join": "6M"
      },
      "used_columns": [
        "emp_no",
        "birth_date",
        "first_name",
        "last_name",
        "gender",
        "hire_date"
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

结论

从上一步的成本结果来看，全表扫描的成本是 60725，而走 idx_test 索引的成本是 197669.81，因此优化器选择全表扫描。

实际上 ICP 可以减少回表次数，走 idx_test 索引时的真实回表次数是 1065，成本应该是：

  • IO成本：1065*1 = 1065
  • CPU成本：1065*0.2 = 213

但是优化器在计算回表成本时，显然没有考虑 ICP，直接将扫描索引的行数 141192 当作了回表的次数，所以得到的回表成本巨大，总成本远远大于全表扫描的成本。

因此，我们可以得到的结论是：ICP可以在执行阶段提高执行效率，但是在优化阶段并不能改善执行计划。

SQL面试 100 问
上一篇
SQL中GROUP BY语句介绍
下一篇