MySQL高级学习（7）索引优化

mysql 索引优化

1. 最左前缀：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

 and 忽略左右关系。既即使没有按顺序 由于优化器的存在，会自动优化。经过试验结论  建立了 idx_nameAge 索引  id 为主键

    （1）.当使用覆盖索引的方式时，(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以 name 开头，也会使用 idx_nameAge 索引。

    既 select 后的字段 有索引，where 后的字段也有索引，则无关执行顺序。

    （2）除开上述条件 才满足最左前缀法则。

        EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';

        EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

2.不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

3.存储引擎不能使用索引中范围条件右边的列

      where条件查找范围条件，type回事range 并且后面的where条件 索引失效

4.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

       会用到index extra中 会using index 性能更好

5.mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

     索引  idx_nameAgeJob

             idx_name

      使用 != 和 <> 的字段索引失效( != 针对数值类型。 <> 针对字符类型

          前提 where and 后的字段在混合索引中的位置比当前字段靠后  where age != 10 and name='xxx'  ,这种情况下，mysql自动优化，将 name='xxx' 放在 age ！=10 之前，name 依然能使用索引。只是 age 的索引失效)

6.is not null 也无法使用索引,但是is null是可以使用索引的

7.like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

        like ‘%abc%’ 或者 "%abc" type 类型会变成 all

         like ‘abc%’ type 类型为 range ，算是范围，可以使用索引

问题：解决like '%字符串%'时索引不被使用的方法？？

     利用覆盖索引去解决

       select name,age,email from tables ... type会是index using index

       select id from tables type也是index using index id是主键索引 也可以使用到

8.字符串不加单引号索引失效

       2000和‘2000’是不一样的，字符串类型的不可以不加单引号，mysql会底层进行转换导致索引失 效，使用了函数造成索引失效

9.少用or,用它来连接时会索引失效

总结

顺口溜

带头大哥不能死（复合索引的最左前缀原则，最左边的索引不能没有，没有会导致索引失效）

中间兄弟不能断（复合索引中间的索引不能没有，否则索引只会生效第一个，后面的索引失效）

索引列上不计算（计算、函数、(自动or手动)类型转换）

like百分加右边

范围后面全失效（范围查询后面的索引会失效）

字符串里有引号（mysql会底层进行转换导致索引失效）

 假设index(a,b,c) 复合索引Where语句索引是否被使用

1.where a = 3     使用到a

2.where a = 3 and b = 5      使用到a，b

3.where a = 3 and b = 5 and c = 4    使用到a,b,c

4.where b = 3 或者 where b = 3 and c = 4  或者 where c = 4 没用到

5.where a = 3 and c = 5   使用到a， 但是c不可以，b中间断了

6.where a = 3 and b > 4 and c = 5      使用到a和b， c不能用在范围之后，b后断了

7.where a = 3 and b like 'kk%' and c = 4    使用到a,b, c 但是type为range

8.where a = 3 and b like '%kk' and c = 4     只用到a

9.where a = 3 and b like '%kk%' and c = 4     只用到a

10.where a = 3 and b like 'k%kk%' and c = 4   使用到a,b,c 但是type为range 同7

面试题举例

【建表语句】

create table test03(

 id int primary key not null auto_increment,

 c1 char(10),

 c2 char(10),

 c3 char(10),

 c4 char(10),

 c5 char(10)

);

insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');

insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');

insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');

insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');

insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

【建索引】

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

问题：我们创建了复合索引idx_test03_c1234 ,根据以下SQL分析下索引使用情况？

explain select * from test03 where c1='a1'; 用到了c1

explain select * from test03 where c1='a1' and c2='a2'; 用到了c1 c2

explain select * from test03 where c1='a1' and c2='a2' and c3='a3';用到了c1 c2 c3

explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';用到了c1 c2 c3 c4

1）explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4'; 

用到了c1 c2 c3 c4 mysql优化器自动优化where后面的and顺序

2） explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3'; 

用到了c1 c2 c3 c4 mysql优化器自动优化where后面的and顺序

3） explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';

用到了c1 c2 c3用到了排序 c4没用到

4） explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';

用到了c1 c2 c3 c4用来排序

5） explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;

 用到了c1 c2 c3作用在排序而不是查找 c4没用到

6） explain select * from test03 where c1='a1' and c2='a2' order by c3;

用到了c1 c2 c3作用在排序而不是查找

7） explain select * from test03 where c1='a1' and c2='a2' order by c4; 

用到了c1 c2 c4在排序 出现了using filesort 需要优化

8） 

8.1explain select * from test03 where c1='a1' and c5='a5' order by c2,c3; 

 只用c1一个字段索引，但是c2、c3用于排序,无filesort

8.2explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;

 出现了filesort，我们建的索引是1234，它没有按照顺序来，3 2 颠倒了

9） explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;

用到了c1 c2 索引 c2 c3 用来排序

10）explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;       

 同上用c1、c2两个字段索引，但是c2、c3用于排序,无filesort

 explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;             

 本例有常量c2的情况，和8.2对比 因为where条件有了c2的查询 所以c2已经变成了常量，在order by的时候 c2也就成了常量， 也就只有c3进行了order by 所以没有了filesort

 explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;       

使用到了c1索引 出现了filesort排序，需要优化                        

11）explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;

使用到了一个c1 分组之前必排序，c2 c3 进行了排序 分组

12）explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;

使用到了一个c1 分组之前必排序，出现了 Using where; Using temporary; Using filesort 

 

一般性建议

1.对于单键索引，尽量选择针对当前query过滤性更好的索引

2.在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。(避免索引过滤性好的索引失效)

3.在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引

4.尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

 

sql优化步骤

1.观察，至少跑一天，然后看看生产的慢sql的情况

2.开启慢查询日志，超过设定慢查询时间的，抓取出来

3.explain+慢sql分析 （会解决80%）

4.show profile 查询sql在mysql服务器中的执行细节和生命周期情况

5.运维 or DBA 进行sql数据库的数据库服务器参数调优