索引为啥会错选？

索引为啥会错选？

前言：本文由一次线上慢查案例，剖析出 mysql 优化器为啥会错选索引，mysql 5.7.28 版本

1. 环境准备

1.1 线上慢查 SQL 如下：

简化 SQL 如下

SELECT * FROM fund_operation.t_fund_lend_quota_data
WHERE (FuiDate = 20220810) ORDER BY FuiTimeNode DESC limit 1;

  FuiDate 里面的值都是今天日期（请记住这个日期，都是只查当天的才会导致慢查）

1.2 线上表结构如下：

CREATE TABLE `t_fund_lend_quota_data` (
  `FuiId` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `FuiFundSource` int(11) NOT NULL DEFAULT '-1' COMMENT '资金通道',
  `FuiProductId` bigint(20) NOT NULL DEFAULT '-1' COMMENT '产品id',
  `FuiFundProjectId` bigint(20) NOT NULL DEFAULT '-1' COMMENT '资金项目id',
  `FuiAmountUpperLimit` bigint(20) NOT NULL DEFAULT '-2' COMMENT '放款额度上限|不可无限制|-2初始值',
  `FuiDate` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '当前日期',
  `FuiTimeNode` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '时间节点',
  `FuiCreateTime` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
  `FuiUpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`FuiId`),
  KEY `idx_fundSource` (`FuiFundSource`),
  KEY `idx_fundProjectId` (`FuiFundProjectId`),
  KEY `idx_fundProductId` (`FuiProductId`),
  KEY `idx_date` (`FuiDate`),
  KEY `idx_timeNode` (`FuiTimeNode`),
  KEY `idx_timeNode_date` (`FuiTimeNode`,`FuiDate`)
) ENGINE=InnoDB AUTO_INCREMENT=4027463 DEFAULT CHARSET=utf8 COMMENT='放款额度配置监控统计表'

  线上表数据目前在 4027800

2. Explain 常规分析

前置：由于先创建组合索引 idx_date_timeNode 来解决问题，写分析文档在后，所以后面的语句都会带上 ignore index(idx_date_timeNode)（当前 SQL 忽略使用该索引）

2.1 explain 分析结果

  explain 分析，该 SQL 走 idx_timeNode 索引，不用排序且预估只要扫描 62 行就可以拿到结果。explain 结果看起来这条 SQL 执行很快，实际呢却很慢（到了这里，猜想预估扫描行数和实际扫描行数不符，导致慢查）。

这里你会想，为啥它不走 idx_date 索引？

  我们强制使用 idx_date 来看下。

  explain 分析，该 SQL 走 idx_date 索引，预估扫描行 = 28672，其中因为 order by，需要在内存中排序。从 explain 结果来看，走 idx_date 索引效果远差于 idx_timeNode 索引。

  至于预估rows = 28672，这个有偏差，实际 FuiDate = 20220810 的记录数才 19860 条

具体可以看下这篇文章：mysql explain预估剖析

2.1.1 实际扫描行数（rows）

  慢SQL采用 idx_timeNode 索引，我们来分析它实际扫描的行数。

这里说下该 SQL 的执行计划，以便不清楚这块的同学更好分析。

首先 SQLService 给 InnoDB引擎发起执行语句，InnoDB 基于 idx_timeNode 二级索引倒叙查到第一个主键 ID，再通过回表操作，根据主键 ID 查询到一条记录返回给 SQLService，SQLService 对该记录进行 where FuiDate = 20220810 判断，如果符合且 limit 1，则直接返回；如果不符合，则调用引擎接口取“下一行记录”，InnDB 重复倒叙找下一个记录直至 SQLService 层条件判断成功。所以这里 InnoDB 重复查找多少次就是真实的扫描行数

(1) 通过 SQL 来直观的查看数据分布

  limit 差不多 2000 条，都没有 FuiDate = 20220810 的数据，说明此时此刻该 SQL 扫描至少超过 2000 行。

(2) 通过业务来看数据分布

字段含义说明：

FuiTimeNode 字段：时间节点，数据分布是 1、2、3、4、5…96（每隔15分钟生成一个节点）

FuiDate 字段：日前，格式-年月日，数据分布 20220316、20220317…20220810

  两个字段的共同含义：代表当前日期的某个时间节点。举例：FuiTimeNode = 1 AND FuiDate = 20220810，代表 20220810-00:15分的记录；FuiTimeNode =3 AND FuiDate = 20220810，代表 20220810-00:45分的记录；

(3) 结合真实数据，再来看慢 SQL

  我是20220810-11.58 执行的，FuiDate = 20220810 的最大 FuiTimeNode = 48。

  查询SQL如上：最大 FuiTimeNode = 48，48 以后都数据对于当前时间来说，还没有生成。

慢SQL如下：

SELECT * FROM fund_operation.t_fund_lend_quota_data
WHERE (FuiDate = 20220810) ORDER BY FuiTimeNode DESC limit 1;

  上面我们分析过慢 SQL 的执行计划，结合实际数据分布在来看。InnDB 引擎基于idx_timeNode索引倒叙查找下一个记录，需要从 FuiTimeNode = 96 查找到 FuiTimeNode = 48。中间 FuiTimeNode = 96、97、96......49 的节点在此刻是没有 FuiDate = 20220810 数据的，中间FuiTimeNode = 96、97、96......49的数据都在SQLService 层过滤了。

  上面用伪代SQL统计中间至少需要过滤 1542478 行数据，真实扫描行数大于 1542478行，而不是预估的 62行。

3. 猜测、寻找答案

  从上面 explain 分析可以看出，由于预估的 rows 远小于真实扫描的 rows，导致优化器认为走 idx_timeNode 索引很高效，其实执行效果还不如走 idx_date 索引。

  问题发现了，我们接下来看为啥会预估 rows 偏差这么大，从而导致索引错选！以及 rows = 62 是怎么来的。

4. 索引错选原因

慢SQL如下：

SELECT * FROM fund_operation.t_fund_lend_quota_data
WHERE (FuiDate = 20220810) ORDER BY FuiTimeNode DESC limit 1;

4.1 优化器分析计划

1. 第一步：在 idx_date (FuiDate) 索引和 idx_timeNode (FuiTimeNode) 索引都存在情况下，mysql 优化器会先用 idx_date (FuiDate) 索引估算出需要扫描的 rows = 28676
   

2. 第二步，预估走 idx_timeNode (FuiTimeNode) 索引需要扫描 rows。

   其采用的算法是：预估rows = （表中总条数 / FuiDate = 20220810 的总记录数） limit_nums*

   • 分母：表中总条数，其实也是拿的统计预估的 rows，而不是真实 rows，如果在优化器阶段都要执行SQL获取真实 rows，那mysql将会很慢。预估的 rows 怎么看？
     

     通过该 SQL 我们可以看出，预估的表总条数 = 1780101，也就是分母 = 1780101。

   • 分子：预估的 FuiDate = 20220810 的总记录数，其实第一步的预估 rows 就是分子 = 28676
   • limit_nums：limit = 1，也就是 1。

   分母、分子都算出来了，预估rows = (1780101 / 28676) * 1 = 62.07（是不是跟 explain rows结果一致）。
   

4.2 预估算法真实逻辑

  第二步为啥会采用如此算法来做预估？mysql 会假设 FuiDate = 20220810 的记录数是均匀分布的，表中有 1780101 条记录，其中 28676 条满足查询条件，所以每隔 62 条就有一条满足where条件的。终究估算太过于理想化了（贴上 mysql 官方解答案例：https://bugs.mysql.com/bug.php?id=78325）

5. 解决方案

5.1 解决方案一

  强制走 idx_date 索引，实际执行效果尚可，但是它不是最佳的，需要几万条数据内存排序，如果 sort_buffer 大小不够，竞争激烈，会退化成文件排序

5.2 解决方案二

  创建 idx_date_timeNode (FuiDate,FuiTimeNode) 组合索引，执行计划不用排序且实际扫描行数基本上只有1行

  有 limit 存在，这个 rows = 40342 不准确，代码的 FuiDate = 20220810 预估行数。从filtered = 100 可以看出，读取一行即可得到所需数据。

filtered：表示返回结果的行数占需读取行数的百分比，越大越好