1、索引使用原则
1、在经常进行连接,但是没有指定为外键的列上建立索引,而不经常连接的字段则由优化器自动生成索引。
2、在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。
3、在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就无必要建立索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度。
4、如果待排序的列有多个,可以在这些列上建立复合索引(compound index)。
2、实例讲解
(1)在下面两条select语句中:
SELECT * FROM table1 WHERE field1<=10000 AND field1>=0;
SELECT * FROM table1 WHERE field1>=0 AND field1<=10000;
如果数据表中的数据field1都>=0,则第一条select语句要比第二条select语句效率高的多,因为第二条select语句的第一个条件耗费了大量的系统资源。
第一个原则:在where子句中应把最具限制性的条件放在最前面。!!!!
(2)在下面的select语句中:
SELECT * FROM tab WHERE a=… AND b=… AND c=…;
若有索引index(a,b,c),则where子句中字段的顺序应和索引中字段顺序一致。
第二个原则:where子句中字段的顺序应和索引中字段顺序一致。!!!!
(3)如果对多列进行索引(组合索引),列的顺序非常重要,MySQL仅能对索引最左边的前缀进行有效查找。例如:存在组合索引it1c1c2(c1,c2)。
查询语句select * from t1 where c1=1 and c2=2能够使用该索引。
查询语句select * from t1 where c1=1也能够使用该索引。
但是,查询语句select * from t1 where c2=2不能够使用该索引,因为没有组合索引的引导列,即,要想使用c2列进行查找,必需出现c1等于某值。
(4)当order by 中的字段出现在where条件中时,才会利用索引而不排序,更准确的说,order by 中的字段在执行计划中利用了索引时,不用排序操作。
这个结论不仅对order by有效,对其他需要排序的操作也有效。比如group by 、union 、distinct等。
3、Mysql索引会失效的几种情况
1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。
注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引。
2.对于多列索引,不是使用的第一部分,则不会使用索引。
3.like查询是以%开头。(以%结尾,索引可以使用)
4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引。
4、例题:
假设MySQL数据库表:
create table T{
k int unsigned not null auto_increment,
a date,
b varchar(24),
c int,d varchar(24),
primary key(k),unique key a_index (a DESC,b DESC),
key k1(b),key k2(c),key k3(d));
如下哪些sql语句查询能较好的利用索引?()
正确答案: A D
A、select b from WHERE b like 'aaa%';
B、select a,b from T WHERE a='2015-10-25' ORDER BY b ASC,c ASC;
C、select a,b,c from T WHERE a='2015-10-25' ORDER BY b ASC;
D、select a,b,c from T WHERE a='2015-10-25' ORDER BY a,b;
由unique key a_index (a DESC,b DESC)可知,该表按a 降序,b降序建立了唯一索引。
当order by 字段出现在where条件中时,才会利用索引而无需排序操作。!!!!!!