mysql索引使用

一、order by

注意最左前缀原则

select * from test where name = 'xiaoming' order by age

name和 age都走索引

select * from test where name = 'xiaoming' order by mobile

name走索引，mobile不走

select * from test where name = 'xiaoming' order by age,mobile

都走索引

select * from test where name = 'xiaoming' order by mobile,age

order by 不走索引，索引顺序颠倒

select * from test where name in ('xiaoming','wangli') order by age,mobile

order by 不走索引，查询出in范围中的内容后，age和mobile无法再走索引排序

排序分为：文件排序（filesort）、索引排序（index）

原则：遵循最左前缀原则，尽量在索引列上排序，尽量使用覆盖索引

 二、索引设置原则

1.最左前缀原则

    范围后面不走索引，一定要有带头大哥，中间索引不能断

2.索引列上不要任何操作

3.尽量使用覆盖索引（没有回表操作）

4.!=，is null，is not null不走索引

5.不要在小基数字段上加索引

   例如性别

6.字符串可以采用前缀索引

    例：varchar(255)，可以针对前20个字符创建索引 name(20)，20个字符基本上能到精准匹配

7.where与order by冲突，优先where

 三、in会不会走索引

主要看数据量及in后面的范围

1.如果数据量比较大，in后面的范围较少，走索引

2.如果数据量比较小，in后面的范围又将对较多，mysql认为走全表扫描花费的成本较少，则不走索引

总结：查询条件范围内的数据量相对总量较大，可能不走索引，较小的话大概率会走索引

四、关联sql的优化原则

1.小表驱动大表

2.关联字段要加索引

五、in和exsits优化

原则：小表驱动大表

select * from A where id in (select id from B)

先查询B，再根据B的查询结果查询A，当B的数据量小于A时，in优于exsits

select * from A where exists (select 1 from B where B.id = A.id)

先查询A，再根据B中是否有A结果过滤，当A的数据量小于B时，exsits优于in

 六、count的优化

count的执行效率对比

1.当字段有索引

count(*)count(1)>count(字段)>count(id)

count(字段)走的是二级索引，存储的数据比主键索引要少

2.当字段没有索引

count(*)count(1)>count(id)>count(字段)

总结：对于count(*)，mysql并不是把所有字段都取出来，而是专门做了优化，不取值，按行累加，效率很高