索引失效的11种情况

数据准备

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

CREATE DATABASE dbtest01;set global log_bin_trust_function_creators&＃61;TRUE;DELIMITER //
CREATE FUNCTION &＃96;rand_num&＃96;(from_num INT, to_num INT)
RETURNS INT
BEGINDECLARE i INT DEFAULT 0;SET i &＃61; FLOOR(from_num &＃43; RAND() * (to_num - from_num &＃43; 1));RETURN i;
END //
DELIMITER;DELIMITER //
CREATE FUNCTION &＃96;rand_string&＃96;(n INT)
RETURNS VARCHAR(255) CHARSET utf8mb4
BEGINDECLARE chars_str VARCHAR(100) DEFAULT &＃39;abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ&＃39;;DECLARE ret_str VARCHAR(255) DEFAULT &＃39;&＃39;;DECLARE i INT DEFAULT 0;WHILE i END //
DELIMITER ;CREATE TABLE &＃96;class&＃96;(
&＃96;id&＃96; INT(11) NOT NULL AUTO_INCREMENT,
&＃96;className&＃96; VARCHAR(30) DEFAULT NULL,
&＃96;address&＃96; VARCHAR(40) DEFAULT NULL,
&＃96;monitor&＃96; INT NULL,
PRIMARY KEY(&＃96;id&＃96;)
) ENGINE&＃61;INNODB AUTO_INCREMENT&＃61;1 DEFAULT CHARSET&＃61;utf8;CREATE TABLE &＃96;student&＃96;(
&＃96;id&＃96; INT(11) NOT NULL AUTO_INCREMENT,
&＃96;stuno&＃96; INT NOT NULL,
&＃96;name&＃96; VARCHAR(20) DEFAULT NULL,
&＃96;age&＃96; INT(3) DEFAULT NULL,
&＃96;classId&＃96; INT(11) DEFAULT NULL,
PRIMARY KEY(&＃96;id&＃96;)
) ENGINE&＃61;INNODB AUTO_INCREMENT&＃61;1 DEFAULT CHARSET&＃61;utf8;DELIMITER //
CREATE PROCEDURE &＃96;insert_stu&＃96;(&＃96;start&＃96; INT, &＃96;max_num&＃96; INT)
BEGINDECLARE i INT DEFAULT 0;SET autocommit &＃61; 0;REPEATSET i &＃61; i &＃43; 1;INSERT INTO student(stuno, name, age, classId)VALUES((&＃96;start&＃96; &＃43; i), rand_string(6), rand_num(16, 20), rand_num(1000000, 9999999));UNTIL i &＃61; max_numEND REPEAT;COMMIT;
END //
DELIMITER;DELIMITER //
CREATE PROCEDURE &＃96;insert_class&＃96;(&＃96;max_num&＃96; INT)
BEGINDECLARE i INT DEFAULT 0;SET autocommit &＃61; 0;REPEATSET i &＃61; i &＃43; 1;INSERT INTO &＃96;class&＃96;(className, address, monitor)VALUES(rand_string(8), rand_string(10), rand_num(1, 100));UNTIL i &＃61; max_numEND REPEAT;COMMIT;
END //
DELIMITERE;CALL insert_class(10000);CALL insert_stu(100000, 500000);

索引失效的11种情况

• 全值索引

在没有索引的情况下&＃xff0c; 做如下查询语句耗时0.179s

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

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

再添加如下所以&＃xff1a;CREATE INDEX idx_age_classid_name ON student(age, classId, &＃96;name&＃96;); 

 查看上面查询语句的执行计划&＃xff1a;

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

• 最佳左前缀法则

 此时索引 idx_age_classId_name 将失效&＃xff0c;无法使用。

注&＃xff1a;若查询字段为age和name&＃xff0c;idx_age_classId_name 可以使用&＃xff0c;但只能使用关于 age的索引&＃xff0c;即Key_len&＃61;5;

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

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

• 类型转换导致索引失效

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

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

• 不等于&＃xff08;!&＃61; 或 <>&＃xff09;索引失效

• IS NULL可以使用索引&＃xff0c;IS NOT NULL 不能使用索引

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

页面搜索严禁左模糊或者全模糊&＃xff01;&＃xff01;&＃xff01;

•  OR 前后存在非所以的列&＃xff0c; 索引失效

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

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