这篇是关于一个 MySQL query optimizer 问题的定位及解决。
公司系统中一个简单的 SQL 查询却花费了近 3 秒的时间，语句大致为：
SELECT slug FROM products
WHERE id IN (id1, id2, ...)
AND deleted_at IS NULL;
其中，id 是 PRIMARY KEY，deleted_at 记录删除时间，用于实现 soft delete，也加了索引。deleted_at 允许为空，为空代表是正常商品，不为空代表“已删除”的商品，同时记录下了删除时间。
并且在 Rails 的 ActiveRecord 中设置了 default scope 默认 deleted_at 为空。
这条 SQL 语句当然期待一直使用 PRIMARY index 实现查询效率最高，但是，结果却出人意料的成了 slow query。
使用 MySQL EXPLAIN 来查看查询的执行流程。结果如下：
MySQL EXPLAIN Output
发现当给定的 ID 条件为 20 左右时，是正常地使用 PRIMARY index；但是，当给定的 ID 条件为 50 左右时，竟然使用了 deleted_at 的索引，要知道 deleted_at 为空的有几千万行！
EXPLAIN output 中 Extra 列出给了 Using index condition，也就是 MySQL 只使用了 deleted_at index 的数据，而不用读取任何表数据；这是 MySQL 的 ICP 优化。这个过程相当于：
- 读取
deleted_at索引信息到内存； - 找出
deleted_at为空的行的 ID（几千万行）； - 在上面几千万行中找出 ID 在给定的 ID 列表中的行；
显然，这样效率是极其低下的，出现这种令人意外的行为，我猜测是由于 MySQL 最终获取数据不只一种方式，这就需要评估多种方式执行的复杂度。当指定 ID 比较少时，使用 PRIMARY index 这种方式的复杂度相较使用 deleted_at index 小；当指定 ID 比较多时，在 MySQL 评估算法中，前一种方式的复杂度竟然诡异地大于了后一种方式。而真实的执行语句时的情况并非如此，让我们先猜测一下该评估算法的一些行为。
- 当指定的 ID 比较多，而且比较离散时，MySQL 认为需要读取更多的 BTree nodes；从 PRIMARY index 中获取到 ID 后，还要离散地读取表数据，从而过滤
deleted_at为空的行； - 回头看看使用
deleted_atindex 这种方式呢，完全只需要使用 index 数据就可以完成；可以很好地利用 ICP 优化，简直完美。
等了解到更多 MySQL 查询优化后，再补充准确的原因吧。
解决方案
解决的办法当然是让 MySQL 尽量不要使用 deleted_at index 索引了，可以有如下几种方法：
- 在每次使用 ID 查询时，利用 Index Hints 明确告诉 MySQL 使用哪个索引，这里就是
Product.force_index(:PRIMARY)；其他条件查询时，还要 case by case 的分析是否使用 index hints； - 直接删除掉
deleted_at的索引；之前习惯性的给这类列加索引，其实仔细考虑下，几乎没什么用，也就能优化下Product.count这样的语句；其他查询几乎都不会用到该索引； - 不使用 soft delete；当然出现本文中的问题，怎么也怪不到 soft delete 的头上，不过多少也有 default scope 的问题。使用 soft delete 并不一定合适，我一直认为 soft delete is evil，就像我认为 ActiveRecord callback is evil；这个话题回头专门写吧；
网友评论