记一次 mysql 左连接导致索引失效的原因
起因
生产环境一条查询 3 张表的 sql(简化版) :
1
2
3
SELECT * FROM TABLE1 T1 -- 大概 1500 条数据
LEFT JOIN TABLE2 T2 ON T1.T2_ID = T2.OBJ_ID -- 大概 1000 条数据
LEFT JOIN TABLE2 T3 ON T1.T3_ID = T3.OBJ_ID -- 大概 5000 条数据
查询时长: 4S 左右
T2 T3 表是我们常用的业务表(平时使用 inner join 连接, 极少数使用 left join, 但是使用 left join 时也并不慢), OBJ_ID 为主键 T1 是新建的表
分析
像这种数据量小, 并且诡异的慢查询, 一般来说, 只能上 explain 分析了, 分析得知:
T1 与 T2 之间的查询type: eq_ref(性能较优, 使用了索引) T1 与 T3 之间的查询type: all (性能最烂, 未使用索引)
此刻陷入疑惑, 明明都是使用索引作为关联条件, 为什么 T2 可以, T3 不可以, 慢查询也是该原因导致的全表扫描形成笛卡尔积
翻看三张表的表结构, 除了排序规则(collate)不一致, 其他的都一致
解决
查询相关资料, 得知, 当两张表排序规则不一致时, 可能会使索引失效(我使用 inner join T3 是走索引的)
因为 T3 表为主要业务表, 也是整体数据结构的通用排序规则(utf8_general_ci), 所以修改 T1 与 T2 的排序规则, 使用表设计, 修改表的排序规则为: utf8_bin - utf8_general_ci
发现索引还是未生效
使用:
1
2
show full columns from TABLE1;
查看得知: 字段的排序规则并未跟随表的排序规则变化, 查询资料得知, 修改后的表排序规则只对新增字段生效
使用下述sql 解决问题:
1
2
3
ALTER TABLE TABLE1 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
原因
因为 T1 表是同事在自己机器数据库建的表, 他本地数据库的排序规则为: utf8_bin, 导致创建的表排序规则与测试库, 生产库不一致