MySQL 索引失效问题

索引失效的情况:

  1. 使用 like ‘%abc’或者like ‘%abc%’
  2. 查询列参与了函数计算(并没有使用函数索引)
  3. 数据不够离散,扫描的行数和加载索引的成本超过了全表扫描
  4. 联合索引没有使用最左匹配,或者在范围运算(>,<,<>)等运算的后面
  5. where中索引列有运算

除了上面的几个明显的问题外,还有索引的选择问题。MySQL 在执行一段 sql 的时候,会先决定使用哪一个索引,如果 选了一个性能比较差的索引,即使走了索引,也会带来性能问题。

对上面的第 4 条做一个例子说明:

  1. 定义 abcd 字段一个联合索引
  2. 如果使用 a>0 and b=1 .. 则 a 本身走索引,但 a 后面的字段都不走索引
  3. a=1 and b=1 and c>1 and d=1 这个例子 只有 d 不走索引,如果 索引顺序更改为 abdc 则都会走索引。

准备工作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
create database ITTest;
use ITTest;
CREATE TABLE tbl_user (
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
userName varchar(100) not null comment '姓名',
userAge int default 0 comment '年龄',
userStatus int default 0 comment '用户状态 0 有效,1 无效',
userSex int default 0 comment '性别 0 男 1 女',
birthDate DATE COMMENT '生日',
createTime DATETIME DEFAULT NOW() COMMENT '创建时间',
dataChangeLastTime DATETIME DEFAULT NOW() ON UPDATE NOW() COMMENT '最后更新时间'
);

alter table tbl_user add index idx_userName(userName);
alter table tbl_user add index idx_userSex(userSex);
alter table tbl_user add index idx_userStatus(userStatus);
alter table tbl_user add INDEX idx_union_userStatus_userSex_birthDate(userStatus,userSex,birthDate );

insert into tbl_user(userName,userAge,userStatus,userSex,birthDate)
values
('user001',rand()*100,rand(),rand(),curdate()),
('user002',rand()*100,rand(),rand(),curdate()),
('user003',rand()*100,rand(),rand(),curdate()),
('user004',rand()*100,rand(),rand(),curdate()),
('user005',rand()*100,rand(),rand(),curdate()),
('user006',rand()*100,rand(),rand(),curdate()),
('user007',rand()*100,rand(),rand(),curdate()),
('user008',rand()*100,rand(),rand(),curdate()),
('user009',rand()*100,rand(),rand(),curdate())

Explain 查看索引使用情况

image-20200909081934653

各个字段的含义

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2"; 
-- 结果:
id: 1

select_type: SIMPLE -- 查询类型(简单查询、联合查询、子查询)

table: user -- 显示这一行的数据是关于哪张表的 。

type: range -- 区间索引(在小于1990/2/2区间的数据),这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表扫描了全表才确定结果。一般来说,得保证查询至少达到range级别,最好能达到ref。

possible_keys: birthday -- 指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。

key: birthday -- 实际使用到的索引。如果为NULL,则没有使用索引。如果为primary的话,表示使用了主键。

key_len: 5 -- 最长的索引宽度。如果键是NULL,长度就是NULL。在不损失精确性的情况下,长度越短越好。

ref: const -- 显示哪个字段或常数与key一起被使用。

rows: 20 -- 这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。

Extra: Using where; Using index -- 执行状态说明,这里可以看到的坏的例子是Using temporary和Using

索引选择的决定的因素

  1. 如果在一个 sql 中,有使用了两个索引,是否会同时使用?如果不是,那如何决定使用哪一个?
  2. 如果有一个字段有单独的索引,又符合联合索引的最左匹配原则,索引会怎么选?

MySQL 索引的选取是基于成本计算的,影响查询成本的因素有 扫描行数、是否需要临时表以及是否需要排序**等。

成本的决定因素很多,就那扫描行数来说,影响扫描行数最大的因素是数据的离散度,但是数据又是动态变化的,所以在使用的离散度比较低的索引的时候需要注意后续的索引变化。例如:

1
2
select * from  user where userSex=0 and userStatus=0 and birthDate>'1897-01-01'
select * from user where userSex in(0,1) and userStatus=0 and birthDate>'1897-01-01'

分析下上面的两个sql 的执行速度那个快:

  1. 上面的sql 不同点是 userSex 的的过滤条件
  2. 根据上面建立的两个索引规则,有可能走到联合索引和 userStatus 单独的索引
  3. 在执行筛选的时候,如果这个时候,MySQL 发现 userSex 走索引的成本小于全表扫描,则可能会走索引下推
  4. 使用 in 的时候,userSex 的字段直接默认走全表扫描,不会计算成本