6. 索引优化注意事项1 - 详解

示例表

CREATE TABLE staffs 
(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
	age INT NOT NULL DEFAULT 0 COMMENT '年龄',
	pos VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
	add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表';


INSERT INTO staffs ( NAME, age, pos, add_time ) VALUES ( 'z3', 22, 'manager', NOW() ); 
INSERT INTO staffs ( NAME, age, pos, add_time ) VALUES ( 'July', 23, 'dev', NOW() ); 
INSERT INTO staffs ( NAME, age, pos, add_time ) VALUES ( '2000', 22, 'dev', NOW() ); 

-- 创建复合索引
CREATE INDEX idx_staffs_nap ON staffs ( NAME, age, pos );

1. 全值匹配

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

以下每条对应的执行计划



发现以上的查询顺序就是创建索引的顺序，根据 key_len 发现 3个列都使用了索引。只是出现了回表，在第4点再说明怎么解决

不理想状态
第1条
上面的 sql 可以说走索引状态都是理想中的，但是下面这个 sql 也是走了索引，只是只有 NAME = ‘July’ 走了索引。

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND pos = 'dev';

根据上面 SELECT * FROM staffs WHERE NAME = ‘July’; 的执行计划的 key_len 可以证明，只有 NAME = ‘July’ 走了索引。

第2条

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

发现根本就没有用到索引，走了全表扫描

为什么会这样？
这是因为创建索引的顺序为：NAME, age, pos，而你使用的顺序为：NAME、pos 。就像 1、2、3搂，你要上3搂，1搂上去了，但是2搂没楼梯了，所以后面索引就失效了。而第二条是因为1搂 2搂楼梯都没了，根本没法走索引。
所以在复合索引中尽量在常用的列上创建，并且尽量使用定义索引的顺序

2. 最作左前缀法则

如果索引了多列，要遵守最左前缀法则。意思就是查询时候从定义索引的最左列开始，并且不跳过索引。参看第一条。

3. 不再索引列上做任何操作（计算、函数、自动转换）

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' OR NAME = '222';
-- 内部会走一个隐式转换 case
EXPLAIN SELECT * FROM staffs WHERE NAME = 222;
EXPLAIN SELECT * FROM staffs WHERE LEFT(NAME, 4) = 'July';

可以发现上面 3 条都没走索引

4. 尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致）

减少 select *，使用覆盖索引能避免回表。根据这句话，抛出三个疑问。

什么是回表？
在认识回表前的前提，需要明白 聚集索引 与 普通索引。这里简单大白话一下

聚集索引：记录存储行数据的索引，比如 ：{ id : 1, rowid : Ox4646 }
普通索引：记录对应行id的指针引用。比如：{ name : ‘老王’ , {id : 1} }

查看下图以 name 为例使用索引查找那一行数据。需要搜索 2 个索引树，最后将数据给搂出来。这个时候你会发现明明走了索引，但是还是sql比较慢，因为回表会导致IO上升

怎么确定回表了？
查看执行计划，如果走索引了，但是 Extra 列，出现了 Using index condition

什么是覆盖索引？
覆盖索引就是，你查询的列都在你的复合索引中。直接在索引中获取了数据，并没有访问实际表。

-- 如下，select 的列都是在复合索引中的列
EXPLAIN SELECT NAME FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT age, pos FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT pos, age FROM staffs WHERE NAME = 'July' AND age = 25;

可以发现在索引中搜索获取到的数据。索引经常说的少用 select * 不是只是一个口号，尽量用啥数据就写啥

5. 在使用范围指定（!=、>、<）时候无法使用索引会导致全表扫描

-- 示例在正常走索引
-- 2个列走索引
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
-- 3个列走索引
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

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

显示类型为区间扫描，并且 NAME = ‘July’ AND age > 25 AND pos = ‘dev’ 中，只有 NAME = ‘July’ AND age > 25 使用到了索引，索引区间条件后的不会走索引了

6. is null、is not null 也无法使用索引

EXPLAIN SELECT * FROM staffs WHERE NAME IS NOT NULL

没走索引，索引不要让列为空，尽量给个默认值

7. 模糊查询时候怎么避免全表扫描

在大多数人认知中，大概是都认为模糊查询会不走使用吧。其实不然，看下下面几个例子

EXPLAIN SELECT * FROM staffs WHERE NAME LIKE '%July%'; 
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE '%July'; 	
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE 'July%'; 

没走索引

没走索引

走了索引。其实只要不在开头使用 % 模糊匹配，都会走索引。哪怕是使用 ‘J%uly%’

使用覆盖索引解决两端百分号，可以在 Extra 中看出，在索引中搜索获取数据

EXPLAIN SELECT NAME, age FROM staffs WHERE NAME LIKE '%July%'; 

8. 少用 or 导致连接失效

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July1' OR NAME = 'July2'; 

没走索引