社区编辑申请
注册/登录
聊聊索引失效的经典场景
数据库 MySQL
因为在有多个索引的情况下,MySQL优化器一般会通过比较扫描行数、是否需要临时表以及是否需要排序等,来作为选择索引的判断依据。

前置条件

数据库版本为5.7.17。

创建一张user表,预置500w条数据,每个字段的值都是前缀加上从0开始计数的数字,累加到5000000,如图:

然后用explain来解析一下有没有走索引。

最左匹配原则

我们首先建一个组合索引,username,jobno,company三个字段:

首先执行以下几个sql:

EXPLAIN SELECT * FROM `user` WHERE username = 'cxj1000000' AND jobno = 'jn1000000' AND company = 'com1000000';
EXPLAIN SELECT * FROM `user` WHERE username = 'cxj1000000';
EXPLAIN SELECT * FROM `user` WHERE username = 'cxj1000000' AND jobno = 'jn1000000';
EXPLAIN SELECT * FROM `user` WHERE jobno = 'jn1000000' AND username = 'cxj1000000';
EXPLAIN SELECT * FROM `user` WHERE username = 'cxj1000000' AND company = 'com1000000';

发现都走了索引。

接下来再执行几个sql:

EXPLAIN SELECT * FROM `user` WHERE jobno = 'jn1000000' AND company = 'com1000000';
EXPLAIN SELECT * FROM `user` WHERE company = 'com1000000';
EXPLAIN SELECT * FROM `user` WHERE jobno = 'jn1000000' ;

发现没有走索引。

结论:最左匹配原则要求查询的sql语句中,必须包含最左边的字段,在username,jobno,company的组合索引中,username是最左边的字段,那么查询的sql语句中的where条件中,必须包含username字段,而与sql语句中username的使用顺序无关。

索引列上有计算

根据主键ID查询,毫无疑问会走主键索引,但如果像下面这种:

EXPLAIN SELECT * FROM `user` WHERE id + 1 = 2

可以看到没有走索引。

总结:如果索引列参与了计算,不会命中索引。像这种情况可以变换一下等式,把运算放到等号右边,就会命中索引。

EXPLAIN SELECT * FROM `user` WHERE id = 2 - 1

查询条件带or

上面所说的最左匹配原则中,只要查询语句中包含username就会走索引,但如果我们把and条件换成or,即:

SELECT * FROM `user` WHERE username = 'cxj13' OR jobno = 'jn13'

可以看到并没有走索引,而是全表扫描,所以在带有or的查询语句中,索引将失效,除非所有条件都带有索引。也就是说,username有索引,jobno也必须要建一个索引才会生效。

like查询

在username字段上新建索引user_idx_normal_username,不使用%模糊查询:

SELECT * FROM `user` WHERE username LIKE 'cxj'

使用前置%:

使用后置%:

前后都用%:

结论:模糊查询中,只要使用了%都不会走索引,不使用%号时可以走索引。

字段类型不同

还是使用username进行查询,username的字段类型是字符串类型,我们知道以下语句:

SELECT * FROM `user` WHERE username = 'cxj13'

肯定是会走索引的,但如果我们不用引号引用起来,而是:

SELECT * FROM `user` WHERE username = 100

会发现没有走索引:

结论:字符串的索引字段在查询时数据需要用引号引用,否则索引失效。

查询语句包含in

这种就比较特殊了,走不走索引不是绝对的,跟所查询的数据量跟总表数据量的比例有关。

我们新建一张表:

CREATE TABLE `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`dept` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

然后往这张表里插入10条数据:

首先执行以下语句:

SELECT * FROM test WHERE id IN (1)

执行结果:

可以看到走了索引,需要注意的是type,我们知道type表示所走索引的一个效率值,它的结果的好坏依次为:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

一般来说,至少要达到range级别,当type=index或者ALL时,表示效率低下,需要优化。我们看到只查询一条记录时,不但走了索引,而且type=const,效率较高。我们扩大下范围:

SELECT * FROM test WHERE id IN (1,2,3)

从执行结果上看,也走了索引,但此时type=range,效率降低了。再扩大下范围:

SELECT * FROM test WHERE id IN (1,2,3,4,5)

可以看到没有走索引了,type=ALL,全表扫描。

结论:mysql优化器会根据所查询的数据量决定是走索引还是全表扫描。

mysql选错索引

我们再建一个demo表来说明这种情况:

CREATE TABLE `demo` (
`id` int(11) NOT NULL,
`a` int(11) NOT NULL default 0,
`b` int(11) NOT NULL default 0,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;

然后插入100w条数据,执行以下sql:

select * from demo where (a between 1000 and 2000) and (b between 50000 and 100000) limit 1

看一下执行情况:

可以看到走了索引a,且只扫描了1001行,其实这正是我们需要的。

但如果我们加个排序,变成这样:

SELECT * FROM demo WHERE (a BETWEEN 1000 AND 2000) AND (b BETWEEN 50000 AND 100000) ORDER BY b LIMIT 1

再看下执行情况:

可以看到走了索引b,并且扫描了5w多行数据,这样效率显然会降低,为什么会走索引b呢?

因为在有多个索引的情况下,mysql优化器一般会通过比较扫描行数、是否需要临时表以及是否需要排序等,来作为选择索引的判断依据。在这个例子中,优化器看到根据b来进行排序,认为使用b效率更高,所以走了索引b。实际上,我们应该使用a索引。

这种情况可以使用force index来强制使用索引a。

SELECT * FROM demo FORCE INDEX(a) WHERE (a BETWEEN 1000 AND 2000) AND (b BETWEEN 50000 AND 100000) ORDER BY b LIMIT 1;

可以看到,查询走了索引a,并且只扫描了1001行。

责任编辑:姜华 来源: 今日头条
相关推荐

2022-06-12 06:48:34

2022-06-05 21:09:47

Python办公自动化

2022-06-15 11:02:40

网络安全运营

2022-06-13 07:36:06

2022-06-10 17:37:37

数据库

2022-07-01 06:03:08

WiFi 7WiFi 6

2022-06-27 23:44:37

云原生云存储云计算

2022-07-01 11:12:53

联想

2022-06-24 10:52:47

人工智能作业帮T前线

2022-06-29 09:43:14

SQL优化数据库

2022-06-10 07:45:09

CentOS国产操作系统

2022-05-23 11:03:53

云原生技术DockerIstio

2022-06-29 16:33:52

安全信息泄露信息安全

2022-06-30 11:03:27

DDoS攻击WAF

2022-06-24 07:08:24

OHOS自定义服务

2022-06-27 08:07:13

Go语言互斥锁

2022-05-24 08:21:16

数据安全API

2022-06-08 13:48:06

物联网卡智能设备SIM卡

2022-06-15 16:16:21

分布式数据库鸿蒙

2022-06-26 09:55:00

接口自动化项目

同话题下的热门内容

为什么MySQL的count()方法这么慢?解决MySQL幻读的终极指南MySQL 死锁了,怎么办?分库分表实战:小试牛刀—千万级数据之SQL优化面试突击:MySQL 中如何去重?Undo 日志用什么存储结构支持无锁并发写入?Group By 有哪些注意事项?你知道吗?MySQL主键自增值为什么有“空洞”?

编辑推荐

MySQL集群搭建详解如果对MySQL还停留在这个印象,就out了防止服务器宕机时MySQL数据丢失的几种方案MySQL innodb引擎备份工具XtraBackup之二(数据库全备)MySQL优化: Slave延迟很大的优化方法总结
我收藏的内容
点赞
收藏

51CTO技术栈公众号