聊聊索引失效的经典场景

前置条件

数据库版本为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行。

名称栏目：聊聊索引失效的经典场景
网站地址：http://www.shufengxianlan.com/qtweb/news11/358961.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联