MySQL 索引何时失效

已注销
243 阅读3分钟

写在前面的话

索引存在的意义，在于快速的查询，找到想要查询的目标，进而获取或者更新。

本文假设你具备MySQL 的B+树索引的基础知识，只有如此，才能更好的阅读下文。

使用索引的必要不充分条件

如果某一列上创建了索引，在查询语句中，想要使用到该索引，一定是该索引与常量值进行比较。

即查询中使用某索引的必要不充分条件为：该索引列直接与常量值进行比较

现在进行举例

首先查看表的结构

SHOW CREATE TABLE tbl_user_wechat

CREATE TABLE `tbl_user_wechat` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, // 主键
`user_id` int(10) unsigned NOT NULL, // 唯一索引
`openid` char(32) DEFAULT NULL, // 唯一索引
`unionid` char(32) DEFAULT '', // 普通索引
`nickname` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ' ',
`headimgurl` varchar(256) DEFAULT NULL,
`sex` tinyint(1) DEFAULT '0',
`city` varchar(16) DEFAULT NULL,
`province` varchar(16) DEFAULT NULL,
`country` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_1` (`user_id`),
UNIQUE KEY `index_2` (`openid`),
KEY `index_3` (`unionid`)
) ENGINE=InnoDB AUTO_INCREMENT=28729623 DEFAULT CHARSET=utf8
  • 索引列直接与常量值进行比较
EXPLAIN SELECT * FROM tbl_user_wechat WHERE unionid = 'abc'

image.png 下面举例，索引列不与常量值进行直接比较

  • 索引列与其他列进行比较
EXPLAIN SELECT * FROM tbl_user_wechat WHERE unionid = openid

image.png

  • 索引列进行运算之后与常量值进行比较
EXPLAIN SELECT * FROM tbl_user_wechat WHERE user_id + 1 = 666

image.png

  • 索引列进行函数运算之后与常量进行比较
EXPLAIN SELECT * FROM tbl_user_wechat WHERE unionid = openid

image.png

下面讨论索引什么时候失效，当然前提是满足上面的必要条件——索引列直接与常量值进行比较

索引失效的典型案例

隐式转换

EXPLAIN SELECT * FROM tbl_user_wechat WHERE unionid = 123

image.png

居然是全表扫描，再看下面这个查询语句

EXPLAIN SELECT * FROM tbl_user_wechat WHERE cast(unionid as signed) = 123

image.png

其实就是进行了数据类型转换，进而使用全表扫描

单靠索引，不能得到所有结果

EXPLAIN SELECT * FROM tbl_user_wechat WHERE user_id = 123 or city = 'sss'

image.png

虽然搜索条件中有userId列与常量进行直接比较的部分，但是只利用userId索引并不能查询到所有结果，所以直接使用全表扫描

联合索引，等值查询没有按照顺序

如果你对索引结构足够了解，这一点其实不必说。

比如有联合索引union(a,b,c)，由a/b/c三个列组成，如果查询条件为

select * from table where b = 3

总结

  • 首先需要了解MySQL B+树本身的索引结构，明白什么是聚簇索引、什么是二级索引、什么是联合索引。有了这种基础知识，才能更深入探究什么时候索引失效。

  • 所谓使用索引的必要不充分条件，意思就是你要使用某索引，最起码查询条件中包含该索引列直接与常量值进行比较

当然这句话太绝对，还有一种情况，就算没有搜索条件，也可能使用某索引，那就是遍历索引，EXPLAIN中的查询方法为index，如果看到这里没有看懂，那你需要更深入学习一下索引基础，参考资料中的第一本书便是很好的学习资料

  • 在满足基础条件下，还会有一些索引会失效的情况，除了上一章讲到的情况，还有一种情况，会走全表扫描，那就是考虑所有索引的查询成本之后，发现还不如全表扫描，那么会走全表扫描。详见笔者的MySQL执行语句的逻辑

参考

从根儿上理解MySQL

avatar
文章
阅读
粉丝