MySQL调优之Explain工具最佳实践(二）

索引最佳实践

示例表：

 CREATE TABLE `employees` (
 `id` int(11) NOT NULL AUTO_INCREMENT, 
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`), 
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE 
 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());

INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW()); 
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

1.全值匹配

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

 EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manage r';

2.最左前缀法则

如果是复合索引，要遵守最左前缀法则。指的是从索引的最左前列开始并且不跳过中间的列；

 EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;

 EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';

跳过了首列，不会使用使用索引；

EXPLAIN SELECT * FROM employees WHERE position = 'manager';

跳过了第一列，第二索引列，不会使用复合索引，全表扫描；

3.不在索引列上做任何操作

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

EXPLAIN SELECT * FROM employees WHERE left(`name`,3)='LiLei'

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

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manage r'; 

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manage r';

范围查询后，索引失效；

5.尽量不使用Select *

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manage r';

尽量不使用不等于

mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描。
< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

7.is null,is not null 一般情况下也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is not null

8.like以通配符开头（‘$abc…’）mysql索引失效会变成全表扫描操作

 EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

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

EXPLAIN SELECT * FROM employees WHERE name = '1000';

EXPLAIN SELECT * FROM employees WHERE name = 1000;