对MySQL DB,有如下的SQL语句:
SELECT pk FROM tbl_name WHERE cond = ... ORDER BY time DESC ;
这里pk是我们需要进行select出来的字段,它是主键,因此系统自动为其建立对应的索引结构,where子句下面的选择条件cond字段和order by 的time字段,从广义上可以理解为我们选择记录的过滤条件,为了提高查询的速度(本质就是过滤记录的速度),我们都知道会建立Index(cond, time)的联合索引来提高速度。
如果我们的数据库引擎是InnoDB,那么建立Index(cond,time)的复合索引是最佳答案,因为在InnoDB里,索引结构有一个特殊的地方:非主键索引在其B树的叶节点上会额外保存对应主键的值,这样不用再到数据文件里去取主键pk的值,而可以直接在索引里得到它。
如果我们的数据库引擎是MyISAM,那么建立Index(cond,time)复合索引就不是最佳答案。因为MyISAM的索引结构里,非主键索引并没有额外保存对应主键的值,应该建立Index(cond,time,pk)三级的复合索引。
附:
建立索引的时候,先show index from tbl_name查看Cardinality的值,Cardinality表示唯一值的个数,一般来说,如果唯一值个数在总行数中所占比例小于20%的话,则可以认为Cardinality太小,此时索引除了拖慢insert/update/delete的速度之外,不会对select产生太大作用;还有一个细节是建立索引的时候未考虑字符集的影响,比如说username字段,如果仅仅允许英文,下划线之类的符号,那么就不要用gbk,utf-8之类的字符集,而应该使用latin1或者ascii这种简单的字符集,索引文件会小很多,速度自然就会快很多。