今天我聊聊索引的相关问题,因为索引是大家都比较关心的公共话题,确实有很多坑。
不知道你在实际工作中,有没有遇到过下面的这两种情况:
-
明明在某个字段上加了索引,但实际上并没有生效。
-
索引有时候生效了,有时候没有生效。
今天就跟大家一起聊聊,mysql数据库索引失效的10种场景,给曾经踩过坑,或者即将要踩坑的朋友们一个参考。
1. 准备工作
所谓空口无凭,如果我直接把索引失效的这些场景丢出来,可能没有任何说服力。
所以,我决定建表和造数据,给大家一步步演示效果,尽量做到有理有据。
我相信,如果大家耐心的看完这篇文章,一定会有很多收获的。
1.1 创建user表
创建一张user表,表中包含:id
、code
、age
、name
和height
字段。
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`age` int DEFAULT '0',
`name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
`height` int DEFAULT '0',
`address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_code_age_name` (`code`,`age`,`name`),
KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
此外,还创建了三个索引:
-
id
:数据库的主键 -
idx_code_age_name
:由code、age和name三个字段组成的联合索引。 -
idx_height
:普通索引
1.2 插入数据
为了方便给大家做演示,我特意向user表中插入了3条数据:
INSERT INTO sue.user (id, code, age, name, height) VALUES (1, '101', 21, '周星驰', 175,'香港');
INSERT INTO sue.user (id, code, age, name, height) VALUES (2, '102', 18, '周杰伦', 173,'台湾');
INSERT INTO sue.user (id, code, age, name, height) VALUES (3, '103', 23, '苏三', 174,'成都');
周星驰和周杰伦是我偶像,在这里自恋了一次,把他们和我放到一起了。哈哈哈。
1.3 查看数据库版本
为了防止以后出现不必要的误会,在这里有必要查一下当前数据库的版本。不说版本就直接给结论,是耍流氓,哈哈哈。
select version();
查出当前的mysql版本号为:8.0.21
1.4 查看执行计划
在mysql中,如果你想查看某条sql语句是否使用了索引,或者已建好的索引是否失效,可以通过explain
关键字,查看该sql语句的执行计划,来判断索引使用情况。
例如:
explain select * from user where id=1;
执行结果:
从图中可以看出,由于id字段是主键,该sql语句用到了主键索引
。
当然,如果你想更深入的了解explain关键字的用法,可以看看我的另一篇文章《explain | 索引优化的这把绝世好剑,你真的会用吗?》,里面更为详细的介绍。
2. 不满足最左匹配原则
之前我已经给code、age和name这3个字段建好联合索引
:idx_code_age_name。
该索引字段的顺序是:
-
code
-
age
-
name
如果在使用联合索引时,没注意最左前缀原则,很有可能导致索引失效喔,不信我们一起往下看。
2.1 哪些情况索引有效?
先看看哪些情况下,能走索引。
explain select * from user
where code='101';
explain select * from user
where code='101' and age=21
explain select * from user
where code='101' and age=21 and name='周星驰';
执行结果:
上面三种情况,sql都能正常走索引。
其实还有一种比较特殊的场景:
explain select * from user
where code = '101' and name='周星驰';
执行结果:
查询条件原本的顺序是:code、age、name,但这里只有code和name中间断层了,掉了age字段,这种情况也能走code字段上的索引。
看到这里,不知道聪明的你,有