Mysql优化3

  

10 索引失效

10.1 全值匹配

create index index_name_dept_age on employee(name,dep_id,age)

EXPLAIN select * from employee where name = '鲁班' 
EXPLAIN select * from employee where name = '鲁班' and dep_id = 1
EXPLAIN select * from employee where name = '鲁班' and dep_id = 1 and age = 10 

10.2 最佳左前缀法则

1、即使联合索引没有按照建立的字段顺序使用，索引也不会失效，因为预处理会将字段顺序进行调整
2、跳过中间的，后边的索引全部失效
3、没有使用最左边的，所有的索引失效

EXPLAIN select * from employee where name = '鲁班' and age = 10 and dep_id = 1 
EXPLAIN select * from employee where name = '鲁班' and age = 10 
EXPLAIN select * from employee where  dep_id = 1 and age = 10 

10.3 函数计算导致索引失效

EXPLAIN select * from employee where TRIM(name) = '鲁班'

10.4 范围条件导致索引失效

KEY_len变短只有name索引失效
EXPLAIN select * from employee where name = '鲁班' and dep_id > 1 and age = 10 

10.5 条件符号导致索引失效

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

EXPLAIN SELECT * FROM employee where name = '鲁班'
EXPLAIN SELECT * FROM employee where name != '鲁班'
EXPLAIN SELECT * FROM employee where name <> '鲁班'

EXPLAIN SELECT * FROM employee where name is  not null

少用or,使用or连接有可能导致索引失效
EXPLAIN SELECT * FROM employee where name = '鲁班' or NAME = '后裔'
EXPLAIN SELECT * FROM employee where name = '鲁班' or dep_id = 1

10.6 LIKE以通配符开头导致索引失效

EXPLAIN SELECT * FROM employee where name like '鲁%'
EXPLAIN SELECT * FROM employee where name like '%鲁'

10.7 字符类型不使用单引号导致索引失效

EXPLAIN SELECT * FROM employee where name = 200
EXPLAIN SELECT * FROM employee where name = '200'

10.8 尽量使用覆盖索引

查询的字段和建立的字段刚好吻合，这种成为覆盖索引,即不要使用*
使用覆盖索引，将

EXPLAIN select name,dep_id,age from employee where  dep_id = 1 and age = 10 
EXPLAIN select name,dep_id,age from employee where TRIM(name) = '鲁班'
EXPLAIN SELECT name,dep_id FROM employee where name <> '鲁班'

EXPLAIN SELECT name,dep_id FROM employee where name is  not null
EXPLAIN SELECT name,dep_id,age FROM employee where name like '%鲁'
EXPLAIN SELECT name,dep_id,age FROM employee where name = 200

12 排序与分组优化

12.1 排序优化如何避免UsingFileSort

没有按照索引顺序排序：
EXPLAIN select name,dep_id,age  from employee ORDER BY name,age,dep_id
EXPLAIN select name,dep_id,age  from employee ORDER BY age

按照索引排序，但是使用了*
EXPLAIN select *  from employee ORDER BY name,dep_id,age

当索引字段为常量时，可以当作是存在索引的
EXPLAIN select name,dep_id,age  from employee where name = '鲁班' 
ORDER BY dep_id
顺序会自动调节
EXPLAIN select name,dep_id,age  from employee where name = '鲁班' 
and age = 10 ORDER BY dep_id

索引字段为常量，出现UsingFileSort
EXPLAIN select name,dep_id,age  from employee where name = '鲁班' 
ORDER BY age

使用排序一升一降会造成filesort
EXPLAIN select name,dep_id,age  from employee 
ORDER BY name asc ,dep_id desc;

12.2 分组优化如何避免UsingTemporary

Group by 使用不当，会出现Using Temporary
还是和索引的使用顺序有关系

EXPLAIN select name,max(age)  from employee GROUP BY name,dep_id;
EXPLAIN select name,max(age)  from employee GROUP BY name,age;

EXPLAIN select name,max(age)  from employee where dep_id = 1 
GROUP BY name,age;

13 索引相关面试题

说出以下语法使用索引的情况：
假设建立了复合索引(a,b,c),以下条件是否使用了索引及使用情况

条件	索引使用情况
where a = 4	使用到了索引a
where a = 4 and b = 6	使用到了索引a,b
where a = 4 and c = 5 and b = 6	使用到了索引a,b,c 自动重新排序
where b = 4 or b = 5	没有使用到索引
where a = 4 and c = 6	使用到了索引a
where a = 4 and b > 5 and c =6	使用到了索引a,b,范围右侧失效
where a = 4 and b like ‘test%’ and c =6	使用到了索引a,b,test%相当于范围
where a = 4 order by b, c	使用到了a，不会有fileSort
where b = 5 order by a	没用到索引，会有fileSort
where b = 5 order by c	没用到索引，会有fileSort
where a = 4 group by c ,b	用到索引a，会造成Using Temporary

EXPLAIN SELECT age,dep_id  FROM employee
where name = 'a' group by age ,dep_id

EXPLAIN SELECT age,dep_id  FROM employee 
where name = 'a' group by dep_id,age