MySql索引失效了！讲讲这是什么情况？

2023-06-2916:23:41数据库教程Comments1,045 views字数 3152阅读模式

索引失效

准备数据：

CREATE TABLE `dept` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`deptName` VARCHAR(30) DEFAULT NULL,
	`address` VARCHAR(40) DEFAULT NULL,
	ceo INT NULL ,
	PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

CREATE TABLE `emp` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`empno` INT NOT NULL ,
	`name` VARCHAR(20) DEFAULT NULL,
	`age` INT(3) DEFAULT NULL,
	`deptId` INT(11) DEFAULT NULL,
	PRIMARY KEY (`id`)
	#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

1、计算、函数导致索引失效

-- 显示查询分析
EXPLAIN SELECT * FROM emp WHERE emp.name  LIKE 'abc%';
EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; --索引失效

MySql索引失效了！讲讲这是什么情况？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

2、LIKE以%开头索引失效

EXPLAIN SELECT * FROM emp WHERE name LIKE '%ab%'; --索引失效

MySql索引失效了！讲讲这是什么情况？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

拓展：Alibaba《Java开发手册》文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

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

EXPLAIN SELECT * FROM emp WHERE emp.name = 'abc' ;
EXPLAIN SELECT * FROM emp WHERE emp.name <> 'abc' ; --索引失效

MySql索引失效了！讲讲这是什么情况？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

4、IS NOT NULL 和 IS NULL

EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;
EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效

MySql索引失效了！讲讲这是什么情况？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

注意：当数据库中的数据的索引列的NULL值达到比较高的比例的时候，即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引，此时type的值是range（范围查询）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

-- 将 id>20000 的数据的 name 值改为 NULL
UPDATE emp SET `name` = NULL WHERE `id` > 20000;

-- 执行查询分析，可以发现 IS NOT NULL 使用了索引
-- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效，由查询优化器的算法决定
EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL;

MySql索引失效了！讲讲这是什么情况？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

测试完将name的值改回来文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

UPDATE emp SET `name` = rand_string(6) WHERE `id` > 20000;

5、类型转换导致索引失效

EXPLAIN SELECT * FROM emp WHERE name='123'; 
EXPLAIN SELECT * FROM emp WHERE name= 123; --索引失效

MySql索引失效了！讲讲这是什么情况？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

6、全值匹配我最爱

准备：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

-- 首先删除之前创建的索引
CALL proc_drop_index("atguigudb","emp");

问题：为以下查询语句创建哪种索引效率最高文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

-- 查询分析
EXPLAIN SELECT * FROM emp WHERE emp.age = 30 and deptid = 4 AND emp.name = 'abcd';
-- 执行SQL
SELECT * FROM emp WHERE emp.age = 30 and deptid = 4 AND emp.name = 'abcd';
-- 查看执行时间
SHOW PROFILES;

创建索引并重新执行以上测试：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

-- 创建索引：分别创建以下三种索引的一种，并分别进行以上查询分析
CREATE INDEX idx_age ON emp(age);
CREATE INDEX idx_age_deptid ON emp(age,deptid);
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

结论：可以发现最高效的查询应用了联合索引 idx_age_deptid_name文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

MySql索引失效了！讲讲这是什么情况？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

7、最佳左前缀法则

准备：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

-- 首先删除之前创建的索引
CALL proc_drop_index("atguigudb","emp");
-- 创建索引
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

问题：以下这些SQL语句能否命中 idx_age_deptid_name 索引，可以匹配多少个索引字段文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

测试：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

  • 如果索引了多列，要遵守最左前缀法则。即查询从索引的最左前列开始并且不跳过索引中的列。
  • 过滤条件要使用索引，必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.name = 'abcd' ;
-- EXPLAIN结果：
-- key_len：5 只使用了age索引
-- 索引查找的顺序为 age、deptid、name，查询条件中不包含deptid，无法使用deptid和name索引

EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd';
-- EXPLAIN结果：
-- type： ALL， 执行了全表扫描
-- key_len： NULL， 索引失效
-- 索引查找的顺序为 age、deptid、name，查询条件中不包含age，无法使用整个索引

EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND emp.deptid=1 AND emp.name = 'abcd';
-- EXPLAIN结果：
-- 索引查找的顺序为 age、deptid、name，匹配所有索引字段

EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd' AND emp.age = 30;
-- EXPLAIN结果：
-- 索引查找的顺序为 age、deptid、name，匹配所有索引字段

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

准备：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

-- 首先删除之前创建的索引
CALL proc_drop_index("atguigudb","emp");

问题：为以下查询语句创建哪种索引效率最高文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.deptId>1000 AND emp.name = 'abc';

测试1：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

-- 创建索引并执行以上SQL语句的EXPLAIN
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);
-- key_len：10， 只是用了 age 和 deptid索引，name失效

注意：当我们修改deptId的范围条件的时候，例如deptId>100，那么整个索引失效，MySQL的优化器基于成本计算后认为没必要使用索引了，所以就进行了全表扫描。（注意：因为表中的数据是随机生成的，因此实际测试中根据具体数据的不同测试的结果也会不一样，最终是否使用索引由优化器决定）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

MySql索引失效了！讲讲这是什么情况？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

测试2：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

-- 创建索引并执行以上SQL语句的EXPLAIN（将deptid索引的放在最后）
CREATE INDEX idx_age_name_deptid ON emp(age,`name`,deptid);
-- 使用了完整的索引

MySql索引失效了！讲讲这是什么情况？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

补充：以上两个索引都存在的时候，MySQL优化器会自动选择最好的方案文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

作者：Esofar文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/48756.html
  • 本站内容整理自互联网，仅提供信息存储空间服务，以方便学习之用。如对文章、图片、字体等版权有疑问，请在下方留言，管理员看到后，将第一时间进行处理。
  • 转载请务必保留本文链接：https://www.cainiaoxueyuan.com/sjk/48756.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定