示例表
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';
没走索引