where ,group, order by, join on
我们知道这些情况,适合建立索引。
如果没有索引,那么会遍历整个表,是不希望的。
如何理解为什么索引会加快速度?
假如一个表是这样的
// 分组 分数
//----id----name----sex----group—score-- |
//----1-----bruce-----1--------A--------98 ----|
//----2------jay--------0--------B--------80 ----|
//…
group by A 在A上建立索引(innodb)
索引结构是
1----A
2----B
group by A 会从索引中查找到属于A分组的id,然后拿着id扫描表。 好像检索书本目录一样 。
下面介绍两个实用方案。
-
select count(*) from table
这条sql应该在索引中执行,而不能使之遍历表,当其他字段没有建立索引时,通常为主键id,而索引中的运算有较好的算法。
但如果主键庞大时,查询效率可能并不理想。利用较简单的字段容易出奇效,比如我们在sex(0或1)上建立索引,mysql查询优化器选择最简单的字段进行运算,查询速度明显增快。 -
select avg( score ) from table where group = A;
一般方案:在group和score分别建立索引。 然而事实是速度并未有提升,仍然很慢,为什么?
mysql先利用索引A,区分出A分组的记录,然后再次进行score运算。
最优方案:建立联合索引group_score,索引结构是这样的
id----group----score
1-------A---------98
2-------B---------80注意group_score,group在前,score在后,这样针对这条sql优化后,执行时,先根据group在索引中找到符合的数据,然后在索引上运算avg( score ),而无需扫描表。执行效率有巨大的提升。
总结:索引即本来在表中扫描和运算的操作,放在索引中执行,由于索引有特殊的算法,所以加快速度。明白了这个道理,如何使用索引就很明确了。