MySql索引失效的十大杂症

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：

如果条件列不是联合索引中的第一个列，索引失效，如下：

分析与结论：

• 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

• 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的。

五、在索引列上使用mysql的内置函数，索引失效。

表结构：

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加了索引，但是因为它进行运算，索引直接迷路了。。。山重水复疑无路，算着算着脑瓜疼，索引就真的不认识路了。如图：

七、索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。

表结构：

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这些时，索引如同虚设。如下：

八、索引字段上使用is null， is not null，可能导致索引失效。

表结构:

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估计使用全表扫描要比使用索引快,则不使用索引。

• 当表的索引被查询，会使用最好的索引，除非优化器使用全表扫描更有效。优化器优化成全表扫描取决与使用最好索引查出来的数据是否超过表的30%的数据。

• 不要给‘性别‘等增加索引。如果某个数据列里包含了均是"0/1"或“Y/N”等值，即包含着许多重复的值，就算为它建立了索引，索引效果不会太好，还可能导致全表扫描。

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‘)

分析：

• 执行的sql，使用了 or条件，因为组合主键( user_id, device_id)，看起来像是每一列都加了索引，索引会生效。

• 但是出现 !=,可能导致索引失效。也就是 or+ !=两大综合症，导致了慢更新sql。

解决方案:

那么，怎么解决呢？我们是把 or条件拆掉，分成两条执行。同时给 device_id加一个普通索引。

最后，总结了索引失效的十大杂症，希望大家在工作学习中，参考这十大杂症，多点结合执行计划 expain和场景，具体分析，而不是按部就班，墨守成规，认定哪个情景一定索引失效等等。

来源: 程序员DD, 程序员小灰, macrozheng

MySql索引失效的十大杂症

标签：模拟   执行计划   nod   mfc   mac   xpl   连接   cpu   dex