Author:Jason豪
前言：此次出现问题的慢SQL共涉及到三张表，其中一张表是之前已经优化过SQL并上线过索引的一张表cm_settle_partner_day_data，同一张表再次抛出慢SQL，需要更深入的探究和检查之前SQL优化的结果和已创建的索引，并解决明晰上次SQL优化遗留的技术盲点，下文为本次的研究优化成果，在文末会总结参考过的文献与资料。
一、SQL业务背景与线上表数据环境
再次出现慢SQL的表为“日级数据上传表”，cm_settle_partner_day_data，该表目前的生产库数据量是2946064，每日新增数据量在4000左右，目前除主键外已有四个单列索引和上次新建的五列联合索引idx_status_dim_date_agent_channel。
建表语句如下：
CREATE TABLE `cm_settle_partner_day_data` (
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`created` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT '创建时间',
`modifier` varchar(64) NOT NULL DEFAULT '' COMMENT '更新人',
`creator` varchar(64) NOT NULL DEFAULT '' COMMENT '创建人',
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`settle_partner_day_id` bigint(20) NOT NULL COMMENT '业务唯一id',
`channel_id` bigint(20) NOT NULL COMMENT '渠道id',
`agent_id` bigint(20) NOT NULL COMMENT '企业id',
`dimension_value` tinyint(4) NOT NULL COMMENT '维度值：1-Fr维度，2-账户维度，3-非标维度',
`status` tinyint(4) NOT NULL COMMENT '数据状态，1-有效，2-覆盖',
`business_date` date NOT NULL DEFAULT '1971-01-01' COMMENT '日期',
`account` varchar(32) NOT NULL DEFAULT '' COMMENT '前台账号',
`fr_code` varchar(32) NOT NULL DEFAULT '' COMMENT 'fr',
`show_value` bigint(20) NOT NULL DEFAULT '0' COMMENT '展示数据',
`click_value` bigint(20) NOT NULL DEFAULT '0' COMMENT '点击数据',
`consume_value` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '消耗数据（元）',
`cash_value` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '现金（元）',
`file_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '上传的文件ID',
PRIMARY KEY (`id`),
KEY `idx_channel_id` (`channel_id`),
KEY `idx_settle_partner_day_id` (`settle_partner_day_id`),
KEY `idx_business_date` (`business_date`),
KEY `idx_fr_code` (`fr_code`),
KEY `idx_status_dim_date_agent_channel` (`status`,`dimension_value`,`business_date`,`agent_id`,`channel_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1041177 DEFAULT CHARSET=utf8 COMMENT='日级数据上传表'二、“日级数据上传表”问题sql的具体优化思路与方案
1. 上次“日级数据上传表”SQL优化的瑕疵剖析和技术盲点扫除
上次优化最终版本的SQL如下（和本次优化的SQL不是同一个）：
SELECT
p.channel_id channelId,
sum( p.show_value ) showValue,
sum( p.click_value ) clickValue,
sum( p.consume_value ) consumeValue,
sum( p.cash_value ) cashValue,
four.channel_name channel_name
from cm_settle_partner_day_data p FORCE INDEX (idx_status_dim_date_agent_channel)
,
(SELECT a.channel_id,a.agent_id,ch.channel_name,a.deleted,a.biz_type,ca.category_id from cm_channel_admittance_form a
INNER JOIN cm_channel ch ON a.channel_id = ch.channel_id
INNER JOIN cm_sub_category sc ON ch.sub_category_id = sc.sub_category_id
INNER JOIN cm_category ca ON sc.category_id = ca.category_id
WHERE a.deleted = 0
AND a.biz_type = 1
AND ca.category_id = 2) four
WHERE
p.STATUS = 1
AND p.dimension_value IN ( 1, 3 )
AND '2020-07' = date_format( business_date, '%Y-%m' )
AND p.agent_id = 296
AND p.agent_id = four.agent_id
AND p.channel_id = four.channel_id
GROUP BY
p.channel_id,
four.channel_name;上次涉及cm_settle_partner_day_data 表的SQL优化完成后，原来的级联慢SQL速度提升近百倍，线上执行0.03秒左右。但在建立联合索引时，出现将business_date字段放在联合索引首列后，SQL无法顺利使用索引的情况，即便使用了force index语法后，也无济于事。因为当时SQL已经优化成功，导致对于此情况的分析不够深入。
再次回头看之前SQL优化后的执行计划，如下图P1，发现key_len为2，代表五列联合索引idx_status_dim_date_agent_channel只使用到了前两列，第三列business_date字段开始，就没有在走联合索引，即使如此，SQL仍然相比未优化前快了近百倍。
关于执行计划中key_len的含义参考文献：
MySQL优化篇：执行计划explain中key_len计算方式_mysql explain length 如何计算-CSDN博客
P1
通过测试，分析与查找资料，发现在SQL中的where条件里的'2020-07' = date_format( business_date, '%Y-%m' )为导致联合索引未完全使用的原因，一个常识，在表字段上加函数会导致索引失效。
date_format()函数导致索引失效文献如下：
https://blog.csdn.net/weixin_44160916/article/details/107789396
确定问题后，结合慢SQL业务传值情况制定的解决方案为，除去business_date字段上的date_format()函数，因为代码中传值为String类型的月份数据，例如：'2020-07'，而business_date为'%Y-%m-%d'的日期格式，所以决定将SQL中#{month}= date_format( business_date, '%Y-%m' )改写为
AND business_date < DATE_ADD(concat(#{month},'-01'),INTERVAL 1 MONTH )
AND business_date >= concat(#{month},'-01')改写SQL where条件后，再次查看SQL的执行计划，如下
此次原五列联合索引使用到了status , dimension_value , business_date 三个字段，但仍未使用后续的agent_id和channel_id两个字段。原因是，此时business_date使用了范围查询（>= <= ），范围查询后的字段不会再使用索引（具体数据结构算法解释在文末备注comment 1）。
所以，在测试环境新建立了idx_status_dim_agent_date四列索引，将business_date字段放在了联合索引最后进行范围查询，在测试环境改写SQL测试使用该索引后，索引字段全部使用，并且查询行数rows从41364行降低到328行，dev执行速度从0.02秒降低到0.008秒左右，线上降低速度量级会更明显（因为线上的数据量更大）
CREATE INDEX idx_status_dim_agent_date ON cm_settle_partner_day_data (`status`,`dimension_value`,`agent_id`,`business_date`);SQL语法改写并使用新四列索引的执行计划如下图P2
P2
SQL语法改写后的SQL如下(除了使用索引的变更和date_format()函数在表字段business_date使用的去除改写外，还将主表p与四个参数表级联的临时表four改为inner join on 将级联条件从where中移到on后，使SQL逻辑更加清晰易读)：
SELECT
p.channel_id channelId,
sum( p.show_value ) showValue,
sum( p.click_value ) clickValue,
sum( p.consume_value ) consumeValue,
sum( p.cash_value ) cashValue,
four.channel_name channel_name
from cm_settle_partner_day_data p FORCE INDEX (idx_status_dim_agent_date)
inner join
(SELECT a.channel_id,a.agent_id,ch.channel_name,a.deleted,a.biz_type,ca.category_id from cm_channel_admittance_form a
INNER JOIN cm_channel ch ON a.channel_id = ch.channel_id
INNER JOIN cm_sub_category sc ON ch.sub_category_id = sc.sub_category_id
INNER JOIN cm_category ca ON sc.category_id = ca.category_id
WHERE a.deleted = 0
AND a.biz_type = 1
AND ca.category_id = 2) four on p.agent_id = four.agent_id AND p.channel_id = four.channel_id
WHERE
p.STATUS = 1
AND p.dimension_value IN ( 1, 3 )
AND business_date < DATE_ADD(concat('2020-01','-01'),INTERVAL 1 MONTH)
AND business_date >= concat('2020-01','-01')
AND p.agent_id = 296
GROUP BY
p.channel_id,
four.channel_name;2. 本次“日级数据上传表”SQL优化方案与解决思路
本次优化的慢SQL如下：
SELECT
a.modified,
a.created,
a.modifier,
a.creator,
a.id,
a.settle_partner_day_id,
a.channel_id,
a.agent_id,
a.dimension_value,
a.status,
a.business_date,
a.account,
a.fr_code,
a.show_value,
a.click_value,
a.consume_value,
a.cash_value,
ch.channel_name
FROM
cm_settle_partner_day_data a
LEFT JOIN
cm_channel ch ON a.channel_id = ch.channel_id
WHERE
a.agent_id = 4499319196452005901
AND a.dimension_value = 3
AND a.business_date >= '2020-12-01 00:00:00'
AND a.business_date <= '2020-12-31 00:00:00'
ORDER BY created DESC
LIMIT 2147483647;本次cm_settle_partner_day_data表的慢SQL，供前台front系统用户查询日级数据列表使用，其中该SQL的where条件中agent_id与business_date字段为必传字段，where条件中还有三个字段是用户选择添加用来查询筛选的。
线上第一次查看该慢SQL 的执行计划时，没有走索引，直接全表扫描，扫描了206 w+行数据才出结果，线上执行该SQL的时间为2秒左右，执行计划如图P3。
P3
cm_settle_partner_day_data表原本已有idx_business_date单列索引，但是CBO判断走了全表扫描，通过尝试force index强制原慢SQL使用idx_business_date单列索引后，线上执行该慢SQL只需要扫描372494行数据，执行速度降低稳定在0.6秒左右，达到1秒以下的效果。执行计划如图P4
P4
SQL语法改写后的SQL如下：
SELECT
a.modified,
a.created,
a.modifier,
a.creator,
a.id,
a.settle_partner_day_id,
a.channel_id,
a.agent_id,
a.dimension_value,
a.status,
a.business_date,
a.account,
a.fr_code,
a.show_value,
a.click_value,
a.consume_value,
a.cash_value,
ch.channel_name
FROM
cm_settle_partner_day_data a force index (idx_business_date)
LEFT JOIN
cm_channel ch ON a.channel_id = ch.channel_id
WHERE
a.agent_id = 4499319196452005901
AND a.dimension_value = 3
AND a.business_date >= '2020-12-01 00:00:00'
AND a.business_date <= '2020-12-31 00:00:00'
ORDER BY created DESC
LIMIT 2147483647;最终的优化方案，我决定“填坑”满足联合索引的最左匹配原则，覆用之前新建的四列联合索引
idx_status_dim_agent_date，
在Java代码中做改动，在传参到MyBatis层SQL前，判断status和dimension_value字段是否为空，如果为空则填写全量枚举值，并改写SQL语法中status和dimension_value字段处均使用in语法，多值精确匹配。（此处两个字段的枚举值不多，数量分别为2和3，可以“填坑”从而满足索引的最左匹配原则）。
“填坑”优化后的SQL和其执行计划如下，如图P5：
SELECT
a.modified,
a.created,
a.modifier,
a.creator,
a.id,
a.settle_partner_day_id,
a.channel_id,
a.agent_id,
a.dimension_value,
a.status,
a.business_date,
a.account,
a.fr_code,
a.show_value,
a.click_value,
a.consume_value,
a.cash_value,
ch.channel_name
FROM
cm_settle_partner_day_data a force index(idx_status_dim_agent_date)
LEFT JOIN
cm_channel ch ON a.channel_id = ch.channel_id
WHERE
a.agent_id = 4499319196452005901
AND a.dimension_value in (1,2,3)
AND a.business_date >= '2020-01-01 00:00:00'
AND a.business_date <= '2020-01-31 00:00:00'
AND a.STATUS IN (1,2)
ORDER BY created DESC
LIMIT 2147483647;
P5
三、上线结果
索引和代码上线后，上次优化过的级联SQL从0.03秒降低到0.00秒以下，截图如P6
P6
本次cm_settle_partner_day_data涉及到的慢SQL也从线上2秒左右，降低到0.32秒左右，最坏情况两个“填坑”字段均使用了全列枚举，也稳定在0.5秒左右。
四、本次优化SQL时候的疑问与思考
进一步对本次慢SQL优化解析时，曾考虑，因为agent_id与business_date两个字段为必传字段，新建该两字段的两列联合索引，能进一步优化该sql的速度。此处有一个技术疑问点，两列联合索引business_date字段放在首位还是agent_id字段放在首位 ? business_date字段在本条SQL的where条件中使用了>= 和 <=，那CBO会判断将business字段执行为“范围查询”还是“多值精确匹配”?
在dev测试环境新建了idx_date_agent与idx_agent_date两条索引
CREATE INDEX idx_date_agent ON cm_settle_partner_day_data (`business_date`,`agent_id`);
CREATE INDEX idx_agent_date ON cm_settle_partner_day_data (`agent_id`,`business_date`);测试SQL慢SQL如下，暂时去除了原where条件中的dimension_value：
SELECT
a.modified,
a.created,
a.modifier,
a.creator,
a.id,
a.settle_partner_day_id,
a.channel_id,
a.agent_id,
a.dimension_value,
a.status,
a.business_date,
a.account,
a.fr_code,
a.show_value,
a.click_value,
a.consume_value,
a.cash_value,
ch.channel_name
FROM
cm_settle_partner_day_data a force index(idx_agent_date)
LEFT JOIN
cm_channel ch ON a.channel_id = ch.channel_id
WHERE
a.agent_id = 296
AND a.business_date >= '2020-01-01 00:00:00'
AND a.business_date <= '2020-01-31 00:00:00'
ORDER BY created DESC
LIMIT 2147483647;用上述问题SQL分别force index使用后，发现，business_date字段不论在联合索引的首位还是二位，SQL全部使用到了联合索引的所有字段，但是，在business_date日期字段保持在月跨度的数据情况下(例如1月份cm_settle_partner_day_data表数据量为65309，agent_id为296并日期在1月份的数据量为4066)，使用idx_agent_date索引CBO预估只扫描了4066行数据即可查询得到结果，而idx_date_agent索引足足扫描了12W+行数据才得出结果，前者明显更快，使用执行计划如图P7 P8
P7
P8
之所以使用两个索引查询扫描行数差别如此之大，是因为CBO对索引idx_date_agent中的bussiness_date字段判断使用了“多值精确匹配IN”，需要对bussiness_date字段跨度范围内的所有数据进行扫描（1月份cm_settle_partner_day_data总数据量为65309），并进一步根据agent_id字段进行筛选才能拿到预期数据。
（如何从B+Tree数据结构角度解释rows 128040，CBO预估的扫描行数是从哪来的？
The rows column indicates the number of rows MySQL believes it must examine to execute the query.
For InnoDB tables, this number is an estimate, and may not always be exact.
--MySQL 5.7 Reference Manual ）（也有可能是key_len的值CBO统计错误，当使用idx_date_agent索引时，key_len应该只为3，之所以出现rows 128040，是因为使用了bussiness_date字段的范围扫描，扫了索引65309行数据然后回表又扫主表65309行）
而对idx_agent_date中business_date字段使用了“范围查询”，当双列二级索引拿到索引第一位字段agent_id为296的叶子结点后，找到二位business_date字段的第一个范围值，即刻使用business_date字段叶子节点的指针，顺序遍历到最后一个范围值，拿出相应数据的所有主键id，4066个主键id，并回表拿出相应主键id的所有数据，所以只扫描4066行数据（所有agent_id字段为296，对应的business_date日期范围为1月份的数据量）即可拿出所有预期数据（详细解释见comment1）
两个思考：
- 如何从多列联合索引的底层B+Tree数据结构的角度，解释“多列字段联合索引”中“范围查询匹配”和“多值精确匹配”的查询方式和算法执行？
- 多列联合索引B+Tree数据结构叶结点的“顺序访问指针”是只有首字段有实现吗？还是“联合索引”中的每个字段在叶结点都有“顺序访问指针”？（依照目前的测试情况，是第二种情况，这是目前技术界的一个空白没有找到任何相关资料，不过MySQL索引开发团队的朋友应该很清楚这的数据结构实现，但官方文档没有介绍）
对前面两个疑问的解答，如果单从执行效率来看，将agent_id放在首列的索引最快。第二个疑点，在本次数据环境执行中，当business_date字段放在联合索引首位时候，CBO会判断business_date 字段执行为“多值精确匹配IN”，而当business_date字段放在二位时，CBO会判断business_date 字段执行为“范围查询”。
具体什么时候当一个字段使用了 <= >= 或者between and，CBO会判断为“范围查询”或者“多值精确匹配IN”？目前没有查找到相应的技术文献包括官方文档，不过MySQL设计开发该模块的团队应该清楚这的算法逻辑，我们也只能根据实际使用时的测试结果去推断。（CBO会根据两种方式哪种能更大程度减少扫描行数，并更快执行SQL去做出选择）
参考文献与注释：
联合索引在B+树上的存储结构及数据查找方式
联合索引在B+树上的存储结构及数据查找方式_联合索引的b+树结构-CSDN博客
mysql explain rows理解
https://www.cnblogs.com/shamo89/p/8335633.html
MySQL官方文档 Optimization And Index
MySQL :: MySQL 5.7 Reference Manual :: 8.3 Optimization and Indexes
comment1：
范围列可以用到索引（必须满足最左前缀），但是范围列后面的列无法用到索引（解释：多列联合索引中的某个字段可以使用范围索引，多列联合索引B+Tree实现和查询机制是，在B+Tree索引的叶结点带有顺序访问指针，目的是为了提高区间访问的性能，如果要查询key为从18到49的所有数据记录，当在叶节点找到第一个key 18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，无需因为“多值精确匹配”而多次扫描主键索引拿目标数据，极大提高了区间查询效率。同理，当多列联合索引根据索引的首字段到达叶子结点后，当多列索引中的某个字段（可以是首字段也可以是2345…位字段）要范围查询，一旦到达该范围查询的字段后，找到第一个查找值后，使用叶结点该字段的“顺序访问指针”，直接将到达最后范围值的所有数据从索引中取出，不再使用范围查找字段的后续索引字段去筛选查找数据）。
扫一扫
1325
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
