背景
最近生产爆出一条慢 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 字段编码一致, 还是会走索引.
十, 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 和场景, 具体分析 , 而不是按部就班, 墨守成规, 认定哪个情景一定索引失效.
个人公众号
如果你是个爱学习的好孩子, 可以关注我公众号, 一起学习讨论.
如果你觉得本文有哪些不正确的地方, 可以评论, 也可以关注我公众号, 私聊我, 大家一起学习进步哈.
来源: https://www.cnblogs.com/jay-huaxiao/p/12114590.html