MySQL带clientid非空条件的count查询过慢 可加索引优化吗？

结论 #

可以通过添加索引大幅加快该查询的执行速度，优化后耗时可以降到和无筛选条件的count(*)相当甚至更低。 #

耗时差异原因 #

• 无筛选条件的select count(*) from items执行快，是因为MySQL优化器会自动选择体积最小的索引（通常是主键索引或其他短字段二级索引）进行全索引扫描，无需回表读取实际数据，200万行量级的扫描耗时9秒属于正常水平。
• 添加clientid is not null筛选后查询变慢，是因为clientid字段上没有索引时，数据库要么需要全表扫描逐行判断clientid是否非空，要么扫描其他索引后还要回表读取clientid字段做判断，额外产生了大量随机IO，因此耗时大幅上升到60秒。

具体优化操作 #

直接给clientid字段创建二级索引即可，执行语句：

CREATE INDEX idx_items_clientid ON items(clientid);

索引创建完成后，优化器会直接扫描idx_items_clientid二级索引完成筛选和计数：二级索引本身存储了clientid的所有值，无需回表即可直接判断是否非空，而且二级索引的体积远小于整表，扫描效率会大幅提升。 #

补充说明 #

• 如果clientid已经是某个现有联合索引的最左前缀字段，不需要重复创建单独索引，现有联合索引已经可以支持该查询的优化。
• 200万行量级的表创建二级索引速度很快，建议选择业务低峰期操作，不会对线上业务产生明显影响。

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