Mysql：常见的索引失效案例

数据准备

本文基于 mysql 5.7.34

1.建表

CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2.允许创建函数

set global log_bin_trust_function_creators=1; 

3.创建函数

随机产生字符串

DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

随机产生班级编号

DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;

4.创建存储过程

#创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, name ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;


#创建往class表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO class ( classname,address,monitor ) VALUES
(rand_string(8),rand_string(10),rand_num(1,100000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;

5.调用存储过程

#执行存储过程，往class表添加1万条数据
CALL insert_class(10000);

#执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);

6.创建删除表上的索引的存储过程

DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM
information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND
seq_in_index=1 AND index_name <>'PRIMARY' ;
#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
#若没有数据返回,程序继续,并将变量done设为2
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index " , _index , " on " , tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END //
DELIMITER ;

执行存储过程

#CALL proc_drop_index("dbname","tablename");

计算、函数、类型转换(自动或手动)导致索引失效

函数

创建索引：

CREATE INDEX idx_name ON student(NAME);

sql:

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

加上函数后：

 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

type为“ALL”，表示没有使用到索引，查询效率较之前低很多。

最终测试结果第二条sql比第一条执行时间长十倍多。

计算

创建索引：

CREATE INDEX idx_sno ON student(stuno);

在索引列上进行计算：

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

type = ALL，索引失效了。

优化为：

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

类型转换

#name=123发生类型转换，索引失效。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

详细可参考文章：MySQL中的隐式转换造成的索引失效

范围条件右边的列索引失效

删除已经存在的索引：

#study需要替换成你的数据库名称
CALL proc_drop_index('study', 'student')

创建索引：

create index idx_age_name_classid on student(age,name,classId);

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30  AND student.classId = 20 AND student.name = 'abc';

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30  AND student.classId > 20 AND student.name = 'abc';

创建联合索引时，务必把范围涉及到的字段写在最后

不等于(!= 或者<>)索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age <> 30

is not null索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age IS NULL

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age IS NOT NULL

like以通配符%开头索引失效

CALL proc_drop_index('study', 'student');
CREATE INDEX idx_name ON student(NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.NAME like 'A%'

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.NAME like '%A'

OR 前后存在非索引的列，索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel';

统一字符集

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行转换会造成索引失效。

order by 索引失效的情况

CALL proc_drop_index('study', 'student')
create index idx_age_classid_name on student(age,classId, NAME);

索引失效：

EXPLAIN SELECT * FROM student ORDER BY age, classId

这里因为返回数据过多，如果用上索引，会有大量回表操作，优化器这里就会优化成filesort。

修改：

EXPLAIN SELECT age, classId FROM student ORDER BY age, classId

这里因为不需要回表操作，所以会直接使用索引。

修改，加上limit：

EXPLAIN SELECT * FROM student ORDER BY age, classId limit 100

使用了limit，限制了返回的数量，优化器认为这里使用索引并回表的效率比filesort低，所以这里使用上了索引

索引失效：索引字段排序方向不一致

EXPLAIN SELECT * FROM student ORDER BY age desc, classId limit 100

修改：当两个索引字段排序方向一直，就又会使用索引。

EXPLAIN SELECT * FROM student ORDER BY age desc, classId desc limit 100

索引失效：索引字段顺序错误

EXPLAIN SELECT * FROM student ORDER BY classId desc, age desc limit 100

filesort算法：双路排序和单路排序

双路排序 （慢）:

MySQL 4.1之前是使用双路排序 ，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和 order by列 ，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出

从磁盘取排序字段，在buffer进行排序，再从 磁盘取其他字段 。

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种 改进的算法，就是单路排序。

单路排序 （快）:

从磁盘读取查询需要的所有列 ，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

单路排序的问题:

在sort_buffer中，单路比多路要占用更多的空间，因为单路是把所有字段都取出，所有可能去除的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序，排完再取sort_buffer容量大小，再排.....，从而多次I/O.

优化策略：

1.尝试提高 sort_buffer_size，mysql5.7默认是1MB

SHOW VARIABLES like '%sort_buffer_size%'

2.尝试提高max_length_for_sort_data

提高这个参数，会增加用改进算法的概率：

SHOW VARIABLES like '%max_length_for_sort_data%' #默认1024字节

3.Order by 时 select * 是大忌，最好查询需要的字段。