起因

开发同学说一条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_atable_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官方文档中也明确提到了这点
2024-06-05T05:41:37.png

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列进行比较可以避免使用索引。

彩蛋

字符集相同，排序规则不同依然会存在不走索引的情况

二维码.png