MySQL 索引失效问题

发表于 2020-09-13

索引失效的情况：

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

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

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

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

准备工作

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 查看索引使用情况

各个字段的含义

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

索引选择的决定的因素

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

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 的执行速度那个快：

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