如何使用具有where子句和order的mysql查询的索引

Question

我有张桌子

CREATE TABLE activity ( 
 id INT(10) UNSIGNED AUTO_INCREMENT, 
 subscriberid INT(10) UNSIGNED NOT NULL DEFAULT '0', 
 action VARCHAR(250) NOT NULL DEFAULT '', 
 ip INT(10) UNSIGNED NOT NULL DEFAULT '0', 
 cdate DATETIME NULL DEFAULT NULL, 
 PRIMARY KEY (id), 
 KEY cdate (cdate), 
 KEY subscriberid (subscriberid) 
) ENGINE = InnoDB DEFAULT CHARSET = utf8 DEFAULT COLLATE = utf8_general_ci; 

如何使用cdate字段上的索引返回按cdate降序排序的单个下标的所有行。

如果我做了

explain select * from activity where subscriberid = 2 ORDER BY cdate DESC;

它似乎没有使用索引。如果我添加一个group子句，它不会像您在链接中看到的那样执行一个文件，但是它仍然扫描所有行http://take.ms/8z3Po

Answer 1

尝试一个复合索引(下标，日期)。即

ALTER TABLE activity ADD INDEX subid_date(subscriberid, cdate);

Answer 2

MySQL查询优化器将永远不会使用任何索引。为什么？

您的键分布和索引的基数太低了。

根据我提供的查询的输出和您自己的解释计划，您只有4行。

• 3行订阅者id 2
• 1行订阅者id 4

MySQL查询优化器发现在如此小的数据集中使用索引非常繁琐。

如果表中有100行和其他订阅者id值，那么查询优化器将查看更近一些，并查看哪个订阅者id占行数的5%或更少。任何高于5%的内容都会使查询优化器放弃一个索引，然后尝试另一个索引。如果所有索引都不能提供良好的密钥分布和高度基数，则需要进行全表扫描和一些潜在的文件排序。

在表中只有4行时，MySQL查询优化器做出了正确的选择，因为它所需的工作量最少，因为它不喜欢显示的键的分布和基数。即使是复合指数也会遭受同样的后果。

我还有其他文章讨论和演示这种查询优化器行为

• Apr 11, 2014：为什么MySQL不使用基数较高的索引？
• Nov 13, 2012：索引必须覆盖所有选定的列才能用于订单？ (这篇文章包含了来自我的7篇文章，这些文章一直延伸到2011年7月关于这个主题的文章)
• Mar 05, 2012：数据库同步和软删除场景中的墓碑表和已删除标志
• Oct 20, 2011：在MySQL中，即使扫描表的查询每月只运行一次，我也应该添加索引吗？

Q.E.D.