MySQL跨表记录计数及左连接性能异常问题咨询

为啥反向左连接会用那个完全无关的tinyblob索引？ #

MySQL的查询优化器是靠成本估算选执行计划的，出现这种离谱的选择，大概率是这两个原因：

1. 统计信息过时了：如果你的表数据有过大量增删改，但没更新统计信息，优化器拿到的行数、索引基数这些数据都是错的。比如它可能误以为那个tinyblob索引的行数更少，关联成本更低，就瞎选了。
2. 优化器对blob类型的成本评估偏差：tinyblob是二进制类型，优化器对这类列的索引成本计算本来就容易出问题，尤其是当它没法准确判断关联逻辑的时候，可能就随便挑了个看起来“能用”的索引。

为啥这个无关索引反而比主键索引快？ #

这里的“更快”要么是特殊场景下的巧合，要么是假象：

• 如果那个“无关索引”其实是覆盖索引：比如它刚好包含了user_id和你查询需要的其他列，那MySQL不用回表（不用去主键索引里拿完整行数据），直接从这个索引就能拿到所有需要的信息，所以速度看起来更快。哪怕它带了tinyblob列，但只要查询没用到这个列，优化器可能误判它是覆盖索引。
• 主键索引碎片化严重：主键是聚集索引，如果表的数据删改多了，主键索引的碎片会很多，IO成本反而更高；而那个无关索引是非聚集索引，数据更紧凑，临时看起来快，但逻辑上它是无关的，这肯定是优化器的判断出问题了。

怎么解决？ #

• 先更新统计信息：跑一下ANALYZE TABLE your_tinyblob_table;，让优化器拿到准确的数据分布，很多时候这就能解决问题。
• 强制指定索引：如果优化器还是瞎选，就用FORCE INDEX逼着它用主键索引，比如：

  SELECT *
  FROM other_table ot
  LEFT JOIN tinyblob_table tt FORCE INDEX(PRIMARY) ON ot.user_id = tt.user_id;
  

• 检查索引是否真的无关：看看那个索引的定义，是不是不小心包含了user_id或者其他关联列？如果确实没用，直接删掉它，省得优化器再乱选。

内容的提问来源于stack exchange，提问作者shantanuo