起因
开发同学说一条SQL很慢,执行需要7分钟。。。
真残暴,直接上SQL吧(为方便阅读,这里简化并脱敏)
SELECT
product_id,
max( sales_2h ) AS sales_2h
FROM
`table_a` a
WHERE
serial_number = '2024030615'
AND ( SELECT count( 1 ) FROM table_b WHERE product_id = a.product_id AND promotion_end_time > now() AND activity_cos_ratio > cos_ratio ) > 0
GROUP BY
`product_id`
ORDER BY
sales_2h DESC
LIMIT 50
数据库、表的情况:
- MySQL8.0.18版本
- 服务器配置2C4G云服务器
- table_a\table_\b表引擎ENGINE=InnoDB
- table_a 共3w行数据、table_b共650w
- 数据库实例正常运行无死锁等异常
- table_a:product_id普通索引、serial_number普通索引
- table_b:product_id普通索引、promotion_end_time普通索引
- id自增主键
处理问题
1、因为数据库本身无异常,那么问题多半出在该SQL上
2、虽然数据库配置很低,但是数据量也不大。SQL能执行7分钟肯定哪里有猫腻
3、先看执行计划(三个重点)
table_a:
type:index
key:idx_product_id
Extra:Using where; Using temporary; Using filesort
table_b:
type:ALL
key:Null
Extra:Using where
4、如执行计划,问题多半出在table_b上,单拉table_b部分来看
SELECT count( 1 ) FROM table_b WHERE product_id = a.product_id AND promotion_end_time > now() AND activity_cos_ratio > cos_ratio
5、走索引,数据大概200w。
6、所以实际情况就应该是table_a过滤后1000条数据,关联了table_b表过滤后300w数据。
7、问题多半是出在关联上
8、分别查看table_a和table_b的product_id字段属性
product_id (table_a):utf8,utf8_bin
product_id (table_b):utf8mb4,utf8mb4_0900_ai_ci
9、跟开发确认字段属性,并确认一致性
10、最终修改table_a表product_id字段为utf8mb4,utf8mb4_0900_ai_ci
11、再次执行SQL,30ms,再次查看执行计划
table_a:
type:index
key:idx_product_id
Extra:Using where; Using temporary; Using filesort
table_b:
type:ref
key:idx_product_id
Extra:Using index condition; Using where
总结
在关联查询中,如果字段的字符集不同降走不到索引。
在MySQL官方文档中也明确提到了这点
For comparisons between nonbinary string columns, both columns should use the same character set. For example, comparing a utf8mb4 column with a latin1 column precludes use of an index.
对于非二进制字符串列之间的比较,两个列应该使用相同的字符集。例如,将utf8mb4列与latin1列进行比较可以避免使用索引。
彩蛋
字符集相同,排序规则不同依然会存在不走索引的情况