前期准备

drop table if EXISTS `employees` ;

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());

表中含有五个字段,其中id为主键,idx_name_age_position 为 name、age、position 三个字段的联合索引。

实战开始

全值匹配

1. EXPLAIN SELECT * FROM employees WHERE name= ‘LiLei’;
   

2. EXPLAIN SELECT * FROM employees WHERE name= ‘LiLei’ AND age = 22;
   

3. EXPLAIN SELECT * FROM employees WHERE name= ‘LiLei’ AND age = 22 AND position =’manager’;
   

上面三个查询语句通过执行计划 通过看引用参数的个数和key_len 可以分析出第一个语句只用到 索引idx_name_age_position中name字段,第二个语句使用到name_age ,第三个语句使用三个字段。

返回结果参数含义 以及 key_len 计算方式 见 explain工具

最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。(需要保证B+tree 的有序性)

1. EXPLAIN SELECT * FROM employees WHERE name = ‘Bill’ and age = 31
   

2. EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = ‘dev’;
   

3. EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = ‘dev’;
   

上面三个结果中,第一个满足最左前缀法则 走了索引检索，2，3测试中不满足法则,走的全表扫描。

计算|函数|类型转换

在索引列上做这些操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描。存在计算时最好使用java等语言进行处理。

索引中存在范围查询

存储引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT * FROM employees WHERE name= ‘LiLei’ AND age > 22 AND position =’manager’;

索引长度中可以看出来只用到了索引字段 name和age ,字段position 并没有使用到。

使用覆盖索引

尽量使用覆盖索引(只访问二级索引的查询(索引列包含查询列))，减少 select * 语句
EXPLAIN SELECT name,age FROM employees WHERE name= ‘LiLei’ AND age = 23 AND position =’manager’;

使用覆盖索引在扫描二级索引的时候就能返回查询结果，不会进行回表操作,效率高。

比较操作

mysql在使用不等于(!=或者<>)，not in ，not exists, < 、 > 、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
EXPLAIN SELECT * FROM employees WHERE name not In ‘LiLei’;

在mysql5.6 以下版本使用不等于(!=或者<>)，not in ，not exists 的时候无法使用索引会导致全表扫描，可以自己explain 试一下

isnull isnotnull

EXPLAIN SELECT * FROM employees WHERE name is null
is null,is not null 一般情况下 也无法使用索引

like

like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like ‘%Lei’

EXPLAIN SELECT * FROM employees WHERE name like ‘Lei%’

or 和 in

少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引，详见范围查询优化
EXPLAIN SELECT * FROM employees WHERE name = ‘LiLei’ or name = ‘HanMeimei’;

范围查询优化

给年龄添加单值索引：ALTER TABLE employees ADD INDEX idx_age (age) USING BTREE ;
explain select * from employees where age >=1 and age <=2000;

没走索引原因:mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是 由于单次数据量查询过大导致优化器最终选择不走索引

• 优化方法:可以将大的范围拆分成多个小范围

  explain select * from employees where age >=1 and age <=1000;
  explain select * from employees where age >=1001 and age <=2000;

总结

like KK%相当于=常量，%KK和%KK% 相当于范围

回到之前讲过的B+索引是一个有序的数据结构 在它的保证有序的情况下才能高效的检索数据。如果不是使用覆盖索引，在使用二级索引后还需要进行回表操作。

mysql在执行语句之前会进行成本分析，如果回表成本很高，它可能会放弃走索引而进行全表扫描。