mysql索引失效的⼗⼤问题⼩结
⽬录
背景
⼀、查询条件包含or，可能导致索引失效
⼆、如何字段类型是字符串，where时⼀定⽤引号括起来，否则索引失效
三、like通配符可能导致索引失效。
四、联合索引，查询时的条件列不是联合索引中的第⼀个列，索引失效。
五、在索引列上使⽤mysql的内置函数，索引失效。
六、对索引列运算（如，+、-、*、/），索引失效。
七、索引字段上使⽤（！= 或者 < >，not in）时，可能会导致索引失效。
⼋、索引字段上使⽤is null， is not null，可能导致索引失效。
九、左连接查询或者右连接查询查询关联的字段编码格式不⼀样，可能导致索引失效。
⼗、mysql估计使⽤全表扫描要⽐使⽤索引快，则不使⽤索引。
总结
背景
最近⽣产爆出⼀条慢sql，原因是⽤了or和!=，导致索引失效。于是，总结了索引失效的⼗⼤杂症，希望对⼤家有帮助，加油。
⼀、查询条件包含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;
执⾏⼀条查询sql，它是会⾛索引的，如下图所⽰：
把or条件+没有索引的age加上，并不会⾛索引，如图：
分析&结论：
对于or+没有索引的age这种情况，假设它⾛了userId的索引，但是⾛到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并如果它⼀开始就⾛全表扫描，直接⼀遍扫描就完事。
mysql是有优化器的，处于效率与成本，遇到or条件，索引可能失效，看起来也合情合理。
注意: 如果or条件的列都加了索引，索引可能会⾛的，⼤家可以⾃⼰试⼀试。
⼆、如何字段类型是字符串，where时⼀定⽤引号括起来，否则索引失效
假设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通配符，索引⼀定失效，⽽是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：
如果条件列不是联合索引中的第⼀个列，索引失效，如下：
分析与结论：
当我们创建⼀个联合索引的时候，如(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字段编码⼀致，还是会⾛索引。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论