摘要
最近遇到一个慢 sql, 在排查过程中发现和分库分表后的索引设置有关系, 总结了下问题.
问题
在进行应用健康度盘点时, 发现有个慢 sql 如下
表结构, 按照 userid 进行的分表 .
explain 一下发现走的是 userid 这个索引, 一个用户下面有很多商品, 也就有了很多 brandgoodid, 所以有可能会很慢, 因为要扫描很多的索引键去过滤 brandgoodid 值. 而写这个 SQL 的人期望走的主键索引, 而不是'userid'的索引. 因为用主键索引, 就是 N 次主键扫描(N 表示 in 中的数量).
分析
直接原因很明显
IN 这个查询误导了 MySQL 的优化器, 选错了索引 IN 查询常常会影响 MySQL server 的判断. 主要是 IN 里面的值数量不同, 会影响扫描行数的不同, 所以常常会出现索引选择不一致. 之前也总结过一篇 SQL IN 一定走索引吗
解决
因为用户查询的 brandgoodlid 是限定在某个 group 维度下的, 一个 group 对应的 brandgood 是有限的, 在这个业务中, 通常小于 10. 所以这个地方使用主键索引, 效率更高. 解决方法也就是这地方需要 force index 强制走 PRIMARY index.
扩展
分库分表后的索引
为什么题目叫分库分表后的索引问题的, 直接原因和分库分表并没有什么关系啊? 因为在排查问题时, 犯了一个错误. 以为路由到具体的 brandgood_0020 表后, 可以直接根据 brandgoodid 主键索引来查询了. 认为和一些分布式数据库 (cassandra) 一样, 是 clustering key+partition key 这种索引数据. 可以根据 clustering key 到数据的节点的 partition 块, 然后根据 local index 找到对应的数据.
但其实 MySQL 的分库分表不一样, 分表键不是索引, 只是客户端路由. 只负责找到对应的表. 到表以后, 就是和单表一样查询逻辑.
因为分表键不是索引, 但是查询语句是必须要带着分表键, 那意味着我们的分库分表以后的表索引大部分要建成联合索引了, 分表键 + 索引键 .
要不然我们的查询语句 select xx from table where 分表键 = xxx AND a =xxx, 是走不了联合索引的. 只能走单索引. 单索引 MySQL server 要面临着索引选择的问题.
当然并不是绝对的, 比如上面我举的那个案例. 按照这个思路查看了下其他的分表索引. 果然表上的大部分索引都是非联合索引, 还是直接从单表 copy 过来的索引. 这些索引基本上都是无用的, 因为都的是 userid 索引.
索引选择的问题
MySQL 为什么会选错索引呢, 详细的请看 10 | MySQL 为什么有时候会选错索引
我们这个案例是因为判断扫描行数的时候出问题了.
来源: http://www.tuicool.com/articles/fQ3QBf3