Range checked for each record(index map: 0×2)
MYSQL版本:8.0.25-upsql-3.0.2
背景:
重构了一个报表功能,重构SQL的时候发现查询卡住了。之前的SQL也是我写的改动量并不大。
-- 索引
KEY `BANK_CODE` (`BANK_CODE`,`BANK_ID`,`EMPLOYEE_ID`,`appdec_day`) USING BTREE;
explain SELECT
USER .BANK_CODE,
USER .ID USER_SID,
USER .TEAM_SID,
USER .BANK_SID,
appdec_day,
IFNULL(SUM(swipe), 0) swipe,
IFNULL(SUM(swipe_com), 0) swipe_com,
IFNULL(SUM(swipe_gold), 0) swipe_gold,
IFNULL(SUM(swipe_pla), 0) swipe_pla,
IFNULL(SUM(new_customer), 0) new_customer,
IFNULL(SUM(new_customer_com), 0) new_customer_com,
IFNULL(SUM(new_customer_gold), 0) new_customer_gold,
IFNULL(SUM(new_customer_pla), 0) new_customer_pla,
IFNULL(SUM(assessment), 0) assessment,
IFNULL(SUM(addition), 0) addition,
IFNULL(SUM(card_performance), 0) card_performance,
IFNULL(SUM(final_performance), 0) final_performance
FROM
sys_user USER
INNER JOIN report_person_month report
ON USER .BANK_ID = report.BANK_ID
AND USER .BANK_CODE = convert(report.BANK_CODE,SIGNED)
AND USER .EMPLOYEE_ID = report.EMPLOYEE_ID
WHERE
USER .USER_TYPE = '0'
AND report.appdec_day = SUBSTRING(20221231, 1, 6)
AND USER .BANK_CODE = 6100
GROUP BY user.ID;
- 查看表结构也是有索引的。
- 使用explain时候,发现走了全表,没有走索引,extra:Range checked for each record(index map: 0×2)。
- 不会是字段类型不一致的问题吧。
排查的时候发现了user表和data表中,BANK_CODE字段类型不一致,一个是varchar,还有一个是bigint。
然后试着执行:ALTER TABLE report_person_month MODIFY `BANK_CODE` BIGINT DEFAULT NULL;
完毕之后呢就再使用explain。发现正常了。走了索引。 - 这个时候,一个是想知道原因,还一个是解决方案是啥呢。
// 首先,看一下BANK_CODE这个字段多少个表在使用。
// 发现有252个。
SELECT * FROM information_schema.COUMNS where TABLE_SCHEMA = 'dsmp' AND COLUMN_NAME = 'BANK_CODE';
// 14个,其中有varchar(不同的数量)、bigint、int
SELECT TABLE_NAME,COLUMN_TYPE,count(1) FROM information_schema.COUMNS where TABLE_SCHEMA = 'dsmp' AND COLUMN_NAME = 'BANK_CODE' GROUP BY COLUMN_TYPE;
// 然后我tm心凉了半截。这要是字段统一,我没累死,也怕生产出问题被砍死。
-
发现几个主表是bigint。和之前的同事沟通,当初为啥这么设计。他说有的银行确实是字符串,但那时候需求紧张改动量太大了。就和银行沟通,银行妥协了使用整数。
但后来决定,项目中将BANK_CODE统一改为varchar;但是人手不够,需求就搁置了。问清楚了就好。妥协了一家就都这么妥协吧。我这个是数据表,属于扩展功能依赖性没有这么强,没有其他的功能引用。
我就跟着改成了bigint。 -
还有打补丁的解决方案就是在执行查询的时候,临时改变字段类型。
1.CAST(report.BANK_CODE = SIGNED) 2.CONVERT(report.BANK_CODE = SIGNED) 执行exlain,extra:Using where;Using join buffer(hash join)。
Using join buffer(hash join)。为mysql8的新特性,功能是在使用关联查询的时候使用连接缓冲区,此处不做叙述。(参考:https://www.jianshu.com/p/a13dce90b02d)
总结
- 以上问题就解决完毕了。现在我想知道原因。
隐式转换。参考:
1.https://blog.csdn.net/weixin_64940494/article/details/126115774
2.https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html
3.https://www.jb51.net/article/266809.htm
个人的理解就是:
BANK_CODE字段在user表是bigint,在data表中是varchar(4)。关联时data.BANK_CODE做隐式转换,无法命中索引,只能走全表扫描,性能下降崩溃。
我们在平时的开发过程中,尽量要避免隐式转换,因为一旦发生隐式转换除了会降低性能外, 还有很大可能会出现不期望的结果,就像我最开始遇到的那个问题一样。