最近生产爆出一条慢sql,原因是用了or和!=,导致索引失效。于是,总结了索引失效的十大杂症,希望对大家有帮助,加油。
新建一个user表,它有一个普通索引userId,结构如下:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`age` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
分析&结论:
注意: 如果or条件的列都加了索引,索引可能会走的,大家可以自己试一试。
假设demo表结构如下:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` varchar(32) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_userId` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
userId为字符串类型,是B+树的普通索引,如果查询条件传了一个数字过去,它是不走索引的,如图所示:
如果给数字加上'',也就是传一个字符串呢,当然是走索引,如下图:
分析与结论:
为什么第一条语句未加单引号就不走索引了呢?这是因为不加单引号时,是字符串跟数字的比较,它们类型不匹配,MySQL会做隐式的类型转换,把它们转换为浮点数再做比较。
并不是用了like通配符,索引一定失效,而是like查询是以%开头,才会导致索引失效。
表结构:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` varchar(32) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_userId` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
like查询以%开头,索引失效,如图:
把%放后面,发现索引还是正常走的,如下:
把%加回来,改为只查索引的字段(覆盖索引),发现还是走索引,惊不惊喜,意不意外
结论:
like查询以%开头,会导致索引失效。可以有两种方式优化:
附: 索引包含所有满足查询需要的数据的索引,称为覆盖索引(Covering Index)。
表结构:(有一个联合索引 idx_userid_age
, userId
在前, age
在后)
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`age` int(11) DEFAULT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
在联合索引中,查询条件满足最左匹配原则时,索引是正常生效的。请看demo:
如果条件列不是联合索引中的第一个列,索引失效,如下:
分析与结论:
表结构:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` varchar(32) NOT NULL,
`loginTime` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_userId` (`userId`) USING BTREE,
KEY `idx_login_time` (`loginTime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
虽然loginTime加了索引,但是因为使用了mysql的内置函数Date_ADD(),索引直接GG,如图:
表结构:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` varchar(32) NOT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
虽然age加了索引,但是因为它进行运算,索引直接迷路了。。。山重水复疑无路,算着算着脑瓜疼,索引就真的不认识路了。如图:
表结构:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`age` int(11) DEFAULT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
虽然age加了索引,但是使用了!= 或者 < >,not in这些时,索引如同虚设。如下:
表结构:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`card` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE,
KEY `idx_card` (`card`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
单个name字段加上索引,并查询name为非空的语句,其实会走索引的,如下:
单个card字段加上索引,并查询name为非空的语句,其实也会走索引的,如下:
但是它们用or连接起来,索引就失效了,如下:
新建两个表,一个user,一个user_job
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE `user_job` (
`id` int(11) NOT NULL,
`userId` int(11) NOT NULL,
`job` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
user 表的name字段编码是utf8mb4,而user_job表的name字段编码为utf8。
执行左外连接查询,user_job表还是走全表扫描,如下:
如果把它们改为name字段编码一致,还是会一路高歌,雄赳赳,气昂昂,走向索引。
Mysql出于效率与成本考虑,估算全表扫描与使用索引,哪个执行快。这跟它的优化器有关,来看一下它的逻辑架构图吧(图片来源网上)
总结了索引失效的十大杂症,在这里来个首尾呼应吧,分析一下我们生产的那条慢sql。模拟的表结构与肇事sql如下:
CREATE TABLE `user_session` (
`user_id` varchar(32) CHARACTER SET utf8mb4 NOT NULL,
`device_id` varchar(64) NOT NULL,
`status` varchar(2) NOT NULL,
`create_time` datetime NOT NULL,
`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`,`device_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
explain
update user_session set status =1
where (`user_id` = '1' and `device_id`!='2')
or (`user_id` != '1' and `device_id`='2')
分析:
or
条件,因为组合主键( user_id
, device_id
),看起来像是每一列都加了索引,索引会生效。!=
,可能导致索引失效。也就是 or
+ !=
两大综合症,导致了慢更新sql。解决方案:
那么,怎么解决呢?我们是把 or
条件拆掉,分成两条执行。同时给 device_id
加一个普通索引。
最后,总结了索引失效的十大杂症,希望大家在工作学习中,参考这十大杂症,多点结合执行计划 expain
和场景,具体分析,而不是按部就班,墨守成规,认定哪个情景一定索引失效等等。