MySQL索引失效的11种情况

数据准备

按照如下代码创建生成范围内随机整数函数、固定长度随机字符串、创建 student 表和 class 表、创建随机过程自动向表中插入数据，数据由随机函数随机生成。最后调用随机过程得到数据供学习使用。

CREATE DATABASE dbtest01;

set global log_bin_trust_function_creators=TRUE;


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

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

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;


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(16, 20), rand_num(1000000, 9999999));
				UNTIL i = max_num
				END REPEAT;
				COMMIT;
END //
DELIMITER;

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, 100));
				UNTIL i = max_num
				END REPEAT;
				COMMIT;
END //
DELIMITERE;


CALL insert_class(10000);

CALL insert_stu(100000, 500000);

索引失效的11种情况

• 全值索引

在没有索引的情况下， 做如下查询语句耗时0.179s

 添加索引：CREATE INDEX idx_age ON student(age);  后再次执行此查询语句：

可见查询时间减少，但是减少的并没有想象中的多，分析原因应该是生成数据时age限定在16-20岁之间了，导致大面积的重复age数据，所以对age创建索引效果并不好，这也和之前文章中提到的不适合创建索引的情况相吻合。

再添加如下所以：CREATE INDEX idx_age_classid_name ON student(age, classId, `name`); 

 查看上面查询语句的执行计划：

发现此时只会使用新创建的联合索引，单索引成为冗余索引，此时单索引 idx_age 已失效，应当删除 。

• 最佳左前缀法则

 此时索引 idx_age_classId_name 将失效，无法使用。

注：若查询字段为age和name，idx_age_classId_name 可以使用，但只能使用关于 age的索引，即Key_len=5;

• 主键插入顺序
• 计算、函数、导致索引失效

查看以上俩查询语句的执行计划，发现第一个使用了索引，第二个没有使用索引。 

• 类型转换导致索引失效

•  范围条件的右边的列的失效

key_len = 10 说明联合索引只使用了前两个，关于name的所有并没有使用到。这是因为关于classId的查询是范围性的，导致其右侧的其余联合索引列失效。 故创建联合索引时，务必将涉及到范围查询的字段写在最后。

• 不等于（!= 或 <>）索引失效

• IS NULL可以使用索引，IS NOT NULL 不能使用索引

•  LIKE 以 通配符 % 开头索引失效

页面搜索严禁左模糊或者全模糊！！！

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

• 数据库和表的字符集统一使用 utf8mb4 

统一字符集可避免由于字符集转换导致的索引失效！！！不同的字符集在比较前需要进行 转换 或造成索引失效！！！