组合索引该怎么玩？

罗志宏

已于 2022-12-07 22:08:13 修改

阅读量297

点赞数

分类专栏： java mysql 文章标签：数据库大数据 java

于 2022-10-15 10:33:52 首次发布

本文链接：https://blog.csdn.net/weixin_44981707/article/details/127332111

版权

java 同时被 2 个专栏收录

23 篇文章 2 订阅

订阅专栏

mysql

8 篇文章 0 订阅

订阅专栏

组合索引该怎么玩？

一、环境准备

1.1 线上慢查情况

慢查情况如下图
在这里插入图片描述
简化 SQL 如下

select FuiProductId as 'key',CAST(ifnull(sum(FuiLoanAmount),0) as SIGNED) as 'value' 
from funds_raise.t_funds_raise_task 
where Fuistatus = 4  and FuiFundsProjectId = 50 and FuiFinishLendTime BETWEEN 1661961600 and 1664553599 and FuiInnerFailCode = 0
group by FuiProductId

1.2 线上表结构

CREATE TABLE `t_funds_raise_task` (
  `FuiLoanId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '标的id',
  `FuiLoanAmount` bigint(20) NOT NULL DEFAULT '0' COMMENT '借款金额',
  `FstrLoanTitle` varchar(128) NOT NULL DEFAULT '' COMMENT 'loan title',
  `FstrLoanOrderId` varchar(64) NOT NULL DEFAULT '' COMMENT '借款订单ID',
  `FuiLoanUserId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '借款人uid',
  `FuiProductId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '产品id',
  `FuiPeriodsDays` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '期数天数',
  `FuiFundsProjectId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '资金项目ID',
  `FuiStatus` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '状态',
  `FuiInvestId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '投资ID',
  `FstrOpQueueEmail` varchar(64) NOT NULL DEFAULT '' COMMENT '手动放款操作人邮箱',
  `FuiQueueRetryTimes` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '队列重试次数',
  `FuiApplyMatchTime` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '申请借款时间',
  `FuiMatchSuccTime` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '匹配成功时间',
  `FuiConfirmLoanTime` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '确认要款时间',
  `FuiApplyLendTime` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '申请放款时间',
  `FuiApplyRaiseTime` bigint(20) NOT NULL DEFAULT '0' COMMENT '请求募集时间',
  `FuiLoanIdentityFlag` int(3) unsigned NOT NULL DEFAULT '0' COMMENT '网贷标识',
  `FuiFinishRaiseTime` bigint(20) NOT NULL DEFAULT '0' COMMENT '完成募集的时间',
  `FuiCreditLendTime` bigint(20) NOT NULL DEFAULT '0' COMMENT '信贷通知放款时间',
  `FuiFinishLendTime` bigint(20) NOT NULL DEFAULT '0' COMMENT '放款完成时间',
  `FuiFundSource` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '资金方渠道',
  `FuiCasVersion` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'cas',
  `FuiCreateTime` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
  `FuiUpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `FuiInvestUid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '投资uid',
  `FuiIsUAT` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否为UAT环境，1是，0不是',
  `FstrNotice` varchar(512) NOT NULL DEFAULT '' COMMENT '放款结果回调参数',
  `FstrReconciliationFailMsg` varchar(128) NOT NULL DEFAULT '' COMMENT '资方对账失败原因',
  `FuiInnerFailCode` bigint(10) unsigned NOT NULL DEFAULT '0' COMMENT '内部失败编码',
  `FuiGuaranteeType` int(11) NOT NULL DEFAULT '-1' COMMENT '保险类型 0-无意义, 1-有保险, 2-无保险, 3-无保障, 默认值 -1',
  `FstrChannelName` varchar(64) NOT NULL DEFAULT '' COMMENT '进件渠道名称 Convert值',
  PRIMARY KEY (`FuiLoanId`),
  KEY `idx_update_time` (`FuiUpdateTime`),
  KEY `idx_status_retryTimes` (`FuiStatus`,`FuiQueueRetryTimes`),
  KEY `idx_createTime_productId_fundsProjectId_status` (`FuiCreateTime`,`FuiProductId`,`FuiFundsProjectId`,`FuiStatus`),
  KEY `idx_FstrLoanOrderId` (`FstrLoanOrderId`),
  KEY `idx_FuiApplyMatchTime` (`FuiApplyMatchTime`),
  KEY `idx_FuiCreditLendTime` (`FuiCreditLendTime`),
  KEY `idx_loanUserId` (`FuiLoanUserId`),
  KEY `idx_product_status` (`FuiProductId`,`FuiStatus`),
  KEY `idx_fundProject_status` (`FuiFundsProjectId`,`FuiStatus`),
  KEY `idx_finishLendTime` (`FuiFinishLendTime`),
  KEY `idx_FuiApplyRaiseTime` (`FuiApplyRaiseTime`),
  KEY `idx_FundsProjectId_InnerFailCode_FuiStatus` (`FuiFundsProjectId`,`FuiInnerFailCode`,`FuiStatus`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='募集任务表'

线上表数据目前在 18935752

二、Explain 常规分析

2.1 explain 分析结果

在这里插入图片描述
explain 分析，该 SQL 走 idx_FundsProjectId_InnerFailCode_FuiStatus 索引，预估 rows 显示1483788条，预估得到结果行 = 1483788 * 0.01 * 6.87(filtered) = 101936 条。

从这里可以看出什么？

预估rows = 1483788，是通过索引扫描后得到的行数，预估结果行 = 101936 条是 rows = 1483788 通过非索引 where 条件过滤出的行数，两者相差 10倍，足以说明选择的索引不合理，并没有有效过滤数据；而非索引字段却过滤了大部分数据，这里的非索引字段只剩下 FuiFinishLendTime 字段了。

有必要说明，上面条数都是基于mysql 采样统计预估，真实条数我们可以通过 select count(1) 去查

三、组合索引重建

上面通过 explain 分析，我们知道重新建立的组合索引带上 FuiFinishLendTime 能过滤大部分数据。那新组合索引该选择哪些字段呢，字段之前的顺序需不需要考虑？

3.1 组合索引字段选择

能过滤大部分数据的字段，都应该考虑放在组合索引字段中；过滤的数据小的字段，即便放在组合索引字段中，也起不来太大的作用。

3.1.1 构造伪 SQL 显示真实条数

构建伪 SQL 显示结果真实条数，伪 SQL 如下：

select count(1) 
from funds_raise.t_funds_raise_task 
where Fuistatus = 4  and FuiFundsProjectId = 50 and FuiFinishLendTime BETWEEN 1661961600 and 1664553599 and FuiInnerFailCode = 0

真实条数 = 31543

构建伪 SQL 显示各个字段过滤后的真实条数

Fuistatus 字段伪 SQL 显示真实条数

select count(1) 
from funds_raise.t_funds_raise_task 
where Fuistatus = 4

真实条数 = 18752668，线上表数据目前在 18935752，可想而知 Fuistatus 字段过滤效果是奇差，不该考虑放在组合索引字段中。

FuiInnerFailCode 字段伪 SQL 显示真实条数

select count(1) 
from funds_raise.t_funds_raise_task 
where FuiInnerFailCode = 0

真实条数 = 18925805，线上表数据目前在 18935752，几乎所有记录 FuiInnerFailCode = 0，该字段不应该放在组合索引字段中。

FuiFundsProjectId 字段伪 SQL 显示真实条数

select count(1) 
from funds_raise.t_funds_raise_task 
where FuiFundsProjectId = 50

真实条数 = 719360，线上表数据目前在 18935752，过滤了极大部分数据，该字段可以考虑在组合索引中。

FuiFinishLendTime 字段伪 SQL 显示真实条数

select count(1) 
from funds_raise.t_funds_raise_task 
where FuiFinishLendTime BETWEEN 1661961600 and 1664553599

真实条数 = 544172，线上表数据目前在 18935752，过滤了极大部分数据，该字段可以考虑在组合索引中。

FuiFundsProjectId 字段和 FuiFinishLendTime 字段组合伪 SQL 显示真实条数

select count(1) 
from funds_raise.t_funds_raise_task 
where FuiFinishLendTime BETWEEN 1661961600 and 1664553599 and FuiFundsProjectId = 50

真实条数 = 31965，进一步过滤了大部分数据。从这可以得出，我们应该选择 FuiFundsProjectId 字段和 FuiFinishLendTime 字段作为组合索引字段

3.2 组合索引字段顺序选择

从 3.1 分析，我们知道选择 FuiFundsProjectId 字段和 FuiFinishLendTime 作为组合索引字段，那么它们之前的顺序该怎么放呢，谁在前？这都是有讲究的。

这里给大家多演示几个选择，并分析它们的执行计划，以便看透本质。

3.2.1 FuiFinishLendTime 字段放在最左列

3.2.1.1 创建索引

直接创建组合索引

create index idx_finishLendTime_fundsProjectId on funds_raise.t_funds_raise_task(FuiFinishLendTime,FuiFundsProjectId)

3.2.1.2 explain 分析

explain 常规分析
在这里插入图片描述
咦，它为啥还是选择了老索引 idx_FundsProjectId_InnerFailCode_FuiStatus，没有选择新索引 idx_finishLendTime_fundsProjectId。我们强制看看新索引的执行计划，看看有啥猫腻。

explain 强制新索引分析
在这里插入图片描述

老索引执行计划的 Extra 和新索引执行计划的 Extra 是一样的，老索引执行计划的 rows（索引扫描行数）比新索引执行计划的 rows 高不少，老索引执行计划的 type = ref，新索引执行计划的 type=range，rows 的差距不足弥补 type 带来的差距，优化器最终选择老索引执行计划。

实际情况，真的是老索引执行更快吗？我们真实执行下看下耗时。

3.2.1.3 耗时对比

老索引 idx_FundsProjectId_InnerFailCode_FuiStatus 执行耗时
在这里插入图片描述

新索引 idx_finishLendTime_fundsProjectId 耗时
在这里插入图片描述

从两者耗时来看，新索引 idx_finishLendTime_fundsProjectId 耗时远小于老索引 idx_FundsProjectId_InnerFailCode_FuiStatus，这里分析下为什么。

老索引预估扫描行数 rows = 1483788 稍大于新索引预估扫描行数 rows = 1103990，从这一方面看新索引从磁盘加载的数据要少，耗时肯定也更低；另一方面，新索引会触发索引下推，通过条件 FuiFinishLendTime BETWEEN 1661961600 and 1664553599 走索引从磁盘加载到数据到内存后，触发索引下推，然后再根据条件 FuiFundsProjectId = 50 过滤不符合的数据，再回表，我们上文知道通过这2个字端过滤后的数据有 31965，也就是新索引有 31965条数据回表，而老索引没有索引下推，是基于预估扫描行数 rows = 1483788 进行回表的，从这一方面可以看出，新索引比老索引耗时更低。

我们分析，为什么新索引比老索引耗时低。但为啥优化器还是选择了老索引？

一方面两者的预估 rows 相差不大，且老索引 type = ref 等级比新索引 type = range 高；另一方面，索引下推过滤的行数并没有在优化器检查的范围内，导致预估 rows 差别不大。最终优化器选择了老索引

3.2.1.4 解决方案

我们业务 SQL 可以使用 force index(idx_finishLendTime_fundsProjectId) 强制走新索引，但是这种硬编码行为不是那么妥当，包括以后索引整合，迁移表都需要考虑这部分行为。

这里笔者提一句，如果需要使用 force index，那么说明索引创建的不是那么完美。也就是 idx_finishLendTime_fundsProjectId 不是最佳选择。

3.2.2 FuiFundsProjectId 字段放在最左列

3.2.2.1 创建索引

直接创建组合索引

create index idx_fundsProjectId_finishLendTime on funds_raise.t_funds_raise_task(FuiFundsProjectId,FuiFinishLendTime)

3.2.2.2 explain 分析

在这里插入图片描述

与 idx_FundsProjectId_InnerFailCode_FuiStatus 对比，它的预估扫描 rows = 58258 远小于 1483788，即便它的 type =range，优化器最终选择了它。

与 idx_finishLendTime_fundsProjectId 对比，它们 type = range 一样，预估扫描 rows = 58258 远小于 1103990，因为它能充分利用组合索引 FuiFundsProjectId 和 FuiFinishLendTime 字段，只需要从磁盘加载更少的数据夜到内存，而 idx_finishLendTime_fundsProjectId 索引只能使用 FuiFinishLendTime 字段。

3.2.2.3 执行耗时

在这里插入图片描述

从这里看出，耗时比之前两个索引耗时都低

3.2.3 最优选择

最优选择，就是 FuiFundsProjectId 字段放在最左列，创建 idx_fundsProjectId_finishLendTime 组合索引。

四、总结

上一章节我们介绍了分析组合索引字段和顺序选择方法，但最为一个 MySql 优化老司机，顺序这一环节是有固定模版的

一般情况下：

字段为 a、b

如果 b 是等值查询，而 a 是范围查询，则创建（b, a）组合索引
如果 a 是等值查询，b 是范围查询，则创建（a, b）组合索引
如果 a 和 b 均为范围查询，那个字段区分度更高，哪个字段放在前面
如果 a 和 b 都是等值查询呢？a、b 哪个字段放在前面都一样

引用：https://zhuanlan.zhihu.com/p/528854865

等值查询（A = ? and B = ?）和索引下推（A > ? and B = ?）

等值查询和索引下推还是有区别的，区别在于需要加载的数据页不一样

等值查询（A = ? and B = ?）：等值查询是通过 A = ? 和 B = ？一起确定查询位置，再循环通过查询下一条机制把符合条件的都从磁盘加载到内存，所以加载到内存的数据页几乎都符合 A = ? and B = ?

索引下推（A > ? and B = ?）：索引下推不一样，它只通过 A > ? 确定查询位置，然后把 A > ? 从磁盘加载到内存中，再在内存中走索引下推逻辑，也就是判断是否符合 B = ？，然后再循环通过查询下一条机制把符合 A > ? 的从磁盘加载到内存，所以加载到内存的数据页几乎都符合 A > ?

这就是两者区别，等值是所有条件都可以确定位置，而索引下推的条件不可以确定位置，需要加载到内存后再过滤。