mysql 索引相关总结

1.单表查询情况:

单表使用复合索引时,最好不要带上会使用范围查询的字段,例如

select * from table where column_a = 1 and column_b in [1,2] roder by column_c;

这条语句我们使用了column_a,column_b,column_c这3列作为查询条件和排序条件,假如我们将索引建立为包含column_a,column_b,column_c这3列的联合索引,执行上面的语句时,查询过程会用到范围索引(range),导致范围索引的列是column_b,这样会导致它后面的列(column_c)使用不了索引,所以在进行排序操作时会进行文件内排序,效率低下.

解决方案就是把范围查询的列(使用>,<,between,in等等)不要建立到联合索引中,直接建立column_a,column_c这2列的联合索引,这样既能使用索引查询也能使用索引排序.

2.多表关联查询

select * from table_A left join table_B on table_A.a_id = table_B.b_id;

这种情况我们都知道要在关联条件的列上做索引,由于table_A是主表,table_B是副表,所以这种情况下table_A一定会全表扫描,扫描table_A一行数据,就去关联table_B的0行,1行或多行数据,就相当于给定了table_B.b_id 去查找table_B中的数据,所以这个时候对tableB的b_id这行数据做索引最有效.

3.哪些情况会造成索引丢失:

4.需要注意的几个点:

执行引擎在执行查询任务时会先对sql进行优化,所以索引被使用或者不被使用是会根据优化后的sql来判断的,举两个例子:

1)select * from table where  column_a="a" order by column_b,column_a;(建立复合索引 column_a,column_b),这种情况下排序会使用索引吗?

答案是排序会使用索引,咋一看看上去排序的字段顺序和索引的顺序是反的,但是where条件column_a="a"表明column_a是一个产量,对常量进行排序没有任何意义,所以sql优化器直接自动把sql 优化成了 select  from table where  column_a="a" order by column_b;而这个sql是可以使用索引进行排序的.

2)select * from table where  column_a=100(column_a是varchar类型,对column_a建立索引),这种情况下查询会使用索引吗?

答案是查询不会使用索引,咋一看这么都想不明白明明很简单的使用索引列作为查询条件,并且索引上也没有做运算,为什么会使用不上索引呢?原因还是在于sql优化器,column_a是varchar类型数据,而等号后的值是int型,两边数据类型不匹配,所有sql优化器就自动把sql优化成了select  from table where  int(column_a)=100;这样就相当于索引条件处进行了函数运算,所以就用不了索引了.

3)写sql一定不要select * ,要在可能的情况下尽量少的查询数据列,这样才能尽可能的使用到覆盖索引(不用回表查询).例如 select column_a,column_b from table where column_a like '%aa%'(对column_a,column_b建立复合索引),这样其实在索引文件中就包含了主键列,column_a,column_b,这3列的信息.虽然like '%aa%'这种方式不符合最左匹配原则,不能使用索引进行查询,但是查询的列正好在索引覆盖范围内,引擎可以直接对这个索引文件进行全表扫描,而不用对数据库文件进行全表扫描,一样可以节约不少时间.

尽量少查字段的好处还有,使用orderby时,占用的sort_buffer缓冲区的容量更少,避免超出缓冲区容量而在硬盘中生成排序临时文件,并且少查字段还能减少网络的IO.

4)排序使用复合索引时需要满足最左原则,可以全部字段升序,也可以全部字段降序,group by 在执行的时候其实也会在内部执行一次order by,所以索引对group by查询语句也有作用.

5)说明mysql排序的一个坑(也不算坑,就是执行出来的结果可能和你想象的不一致),当一个sql语句中同时出现了order by column_a和 limit 条件时,mysql会先执行order by 条件再执行limit条件如果order by 后面的字段column_a有重复值时,加上limit的结果可能和不limit的结果的顺序并不相同,mysql会在column_a相等的行上随机选择行来参与排序,反应在实际工作中就是:

select * from (select * from table_A order by column_a) limit 0,5 

和

select * from table_A order by column_a limit 0,5 

返回的结果并不一致(前提是有结果中有多行column_a相等的行),如果这种不一致对你的业务不会产生影响那就没有关系,如果产生了影响那么就要在排序条件中再加入一个字段,能保证order by 后面的条件能唯一对应到一行数据(最好就是加上主键行,这样肯定能确保唯一性).

这种情况具体产生的可以参考:https://zhuanlan.zhihu.com/p/347118306