面试官:你说说 Mysql 索引失效有哪些场景?
共 10270字,需浏览 21分钟
· 2022-11-22
阅读本文大概需要 11 分钟。
来自:https://juejin.cn/post/7161964571853815822
![](https://filescdn.proginn.com/b6408f44000333ab3e39dceb230adb74/3434735c899a408453753de9caec507f.webp)
前言
基础数据准备
联合索引
sname
,s_code
,address
主键索引
id
普通索引
height
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`s_code` int(100) NULL DEFAULT NULL,
`address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`height` double NULL DEFAULT NULL,
`classid` int(11) NULL DEFAULT NULL,
`create_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (`id`) USING BTREE,
INDEX `普通索引`(`height`) USING BTREE,
INDEX `联合索引`(`sname`, `s_code`, `address`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '学生1', 1, '上海', 170, 1, '2022-11-02 20:44:14');
INSERT INTO `student` VALUES (2, '学生2', 2, '北京', 180, 2, '2022-11-02 20:44:16');
INSERT INTO `student` VALUES (3, '变成派大星', 3, '京东', 185, 3, '2022-11-02 20:44:19');
INSERT INTO `student` VALUES (4, '学生4', 4, '联通', 190, 4, '2022-11-02 20:44:25');
正文
![](https://filescdn.proginn.com/cec574b72f675e4a30ab47d8a5506a91/3c69e15f8b0e2be96fb6521b4acef5ac.webp)
最左匹配原则
sname
, s_code
, address
-- 联合索引 sname,s_code,address
1、select create_time from student where sname = "变成派大星" -- 会走索引吗?
2、select create_time from student where s_code = 1 -- 会走索引吗?
3、select create_time from student where address = "上海" -- 会走索引吗?
4、select create_time from student where address = "上海" and s_code = 1 -- 会走索引吗?
5、select create_time from student where address = "上海" and sname = "变成派大星" -- 会走索引吗?
6、select create_time from student where sname = "变成派大星" and address = "上海" -- 会走索引吗?
7、select create_time from student where sname = "变成派大星" and s_code = 1 and address = "上海" -- 会走索引吗?
![](https://filescdn.proginn.com/2cccda5bff5cf47fd79f7dfaec9ad7d8/14fd0abd3c128c6deb806dec4440c153.webp)
EXPLAIN select create_time from student where sname = "变成派大星" -- 会走索引吗?
![](https://filescdn.proginn.com/f79105643b42a9cbdf3617d05034f809/a6a240b6996bbb6c7609aded3e4bd55f.webp)
EXPLAIN select create_time from student where address = "上海" and s_code = 1 -- 会走索引吗?
![](https://filescdn.proginn.com/506d43caf63a32c6b6441bacd3e6ce65/3504186ab04ef2365c9f3164b038b650.webp)
sname
, s_code
)顺序的索引,是匹配不到(sname
, s_code
)索引的;sname
, s_code
的顺序。address是用不到索引的
,因为s_code字段是一个范围查询,它之后的字段会停止匹配。![](https://filescdn.proginn.com/b9fb15621023dea8914cf083016ca182/a5041ad40f522c8f832511fea487cdd8.webp)
![](https://filescdn.proginn.com/ebc9b5ca0995c9be85141a92a9af03ac/617bf61ee172915462cf34983d726b4e.webp)
![](https://filescdn.proginn.com/5782fd491bf518b6ea5adce6848d371d/110de5961d755a56b668211d3f59b6da.webp)
思考
验证
你可以认为联合索引是闯关游戏的设计
例如你这个联合索引是state/city/zipCode
那么state就是第一关 city是第二关, zipCode就是第三关
你必须匹配了第一关,才能匹配第二关,匹配了第一关和第二关,才能匹配第三关
橙色代表字段 A
浅绿色 代表字段B
![](https://filescdn.proginn.com/66488f686690cd8767fb9c8cb1a9733e/2d99ee9202e2c5a70dfe89bc8c31e237.webp)
A 是有顺序的 1,1,2,2,3,4
B 是没有顺序的 1,2,1,4,1,2 这个是散列的
如果A是等值的时候 B是有序的 例如 (1,1),(1,2) 这里的B有序的 (2,1),(2,4) B 也是有序的
DROP TABLE IF EXISTS `leftaffix`;
CREATE TABLE `leftaffix` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) NULL DEFAULT NULL,
`c` int(11) NULL DEFAULT NULL,
`d` int(11) NULL DEFAULT NULL,
`e` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`a`) USING BTREE,
INDEX `联合索引`(`b`, `c`, `d`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of leftaffix
-- ----------------------------
INSERT INTO `leftaffix` VALUES (1, 1, 1, 1, '1');
INSERT INTO `leftaffix` VALUES (2, 2, 2, 2, '2');
INSERT INTO `leftaffix` VALUES (3, 3, 2, 2, '3');
INSERT INTO `leftaffix` VALUES (4, 3, 1, 1, '4');
INSERT INTO `leftaffix` VALUES (5, 2, 3, 5, '5');
INSERT INTO `leftaffix` VALUES (6, 6, 4, 4, '6');
INSERT INTO `leftaffix` VALUES (7, 8, 8, 8, '7');
SET FOREIGN_KEY_CHECKS = 1;
![](https://filescdn.proginn.com/59474ca27ef80c30c7ff53c3c397257a/53bc68c6f7f76ac4666a20af0cdea1b0.webp)
在创建索引树的时候会对数据进行排序 根据最左缀原则 会先通过 B 进行排序 也就是 如果出现值相同就 根据 C 排序 如果 C相同就根据D 排序 排好顺序之后就是如下图:
![](https://filescdn.proginn.com/0b9472d1770171358f3af9be49423f92/750ffa7c62b527222a41fdcedd21e67c.webp)
索引的生成就会根据图二的顺序进行生成 我们看一下 生成后的树状数据是什么样子
![](https://filescdn.proginn.com/bff0a82b9713f67793709d2a598d226b/a8cc2ca711a191a3d2683b7b264357d3.webp)
解释一些这个树状图 首先根据图二的排序 我们知道顺序 是 1111a 2222b 所以 在第三层 我们可以看到 1111a 在第一层 2222b在第二层 因为 111 < 222 所以 111 进入第二层 然后得出第一层
![](https://filescdn.proginn.com/a9240c25e76ef68dfe58462440c41f9e/6c37c23f919cea52b02148ed1f389ed5.webp)
第一个字段排序
,在第一个字段的排序基础上,然后在对第二个字段进行排序。所以B=2这种查询条件没有办法利用索引。![](https://filescdn.proginn.com/25e3e90a89eeae19ff0bb300578de028/d1599af19782f8c2c551c65701a8d5ff.webp)
补充
MySQL8.0版本
开始增加了索引跳跃扫描的功能,当第一列索引的唯一值较少时,即使where条件没有第一列索引,查询的时候也可以用到联合索引。小总结
如果 我where 后面的条件是
c = 1 and d = 1
为什么不能走索引呢 如果没有b的话 你查询的值相当于*11
我们都知道*
是所有的意思也就是我能匹配到所有的数据如果 我 where 后面是
b = 1 and d =1
为什么会走索引呢?你等于查询的数据是1*1
我可以通过前面 1 进行索引匹配 所以就可以走索引最左缀匹配原则的最重要的就是 第一个字段
select *
思考
解释
增加查询分析器解析成本。
增减字段容易与 resultMap 配置不一致。
无用字段增加网络 消耗,尤其是 text 类型的字段。
![](https://filescdn.proginn.com/14915c26aaf1d9351bcdef913c4d7c47/15338094d5056b90d6b0d31c306a749a.webp)
SELECT*
,获取了不需要的数据,则首先通过辅助索引过滤数据,然后再通过聚集索引获取所有的列,这就多了一次b+树查询,速度必然会慢很多,减少使用select * 就是降低回表带来的损耗。![](https://filescdn.proginn.com/5dad3498c034e4d78c79631e8836641b/e5a4636cec1c977c5fcc422fab41a623.webp)
![](https://filescdn.proginn.com/3642c78d938be2c70a11140fc0da13bd/546fcb1768610b1a0c7dcd15f3224c11.webp)
![](https://filescdn.proginn.com/d05bb7d6f4b8f51334eb29a4ce2b0195/df21368c65608521bcb519f8fb6ba481.webp)
![](https://filescdn.proginn.com/a8987a44299a354f669a08e6958ee35c/fafc60fd5eed24c252261e6d35319cfd.webp)
小总结
select * 会走索引
范围查找有概率索引失效但是在特定的情况下会生效 范围小就会使用 也可以理解为 返回结果集小就会使用索引
mysql中连接查询的原理是先对驱动表进行查询操作,然后再用从驱动表得到的数据作为条件,逐条的到被驱动表进行查询。
每次驱动表加载一条数据到内存中,然后被驱动表所有的数据都需要往内存中加载一遍进行比较。效率很低,所以mysql中可以指定一个缓冲池的大小,缓冲池大的话可以同时加载多条驱动表的数据进行比较,放的数据条数越多性能io操作就越少,性能也就越好。所以,如果此时使用
select *
放一些无用的列,只会白白的占用缓冲空间。浪费本可以提高性能的机会。按照评论区老哥的说法 select * 不是造成索引失效的直接原因 大部分原因是 where 后边条件的问题 但是还是尽量少去使用select * 多少还是会有影响的
使用函数
![](https://filescdn.proginn.com/052e19a1fb7d724dc5bfc215ba0e93b0/b5149c0cf791a2e912db499b5beb1f53.webp)
![](https://filescdn.proginn.com/4f9bb763254f7d95ca8780d3706a9d0f/2a43f43f6505007f25ae4a00a1ee164c.webp)
计算操作
![](https://filescdn.proginn.com/7a6bb77b72dcc34c43700c2c3497669f/40dc9dd086d4f13371a34955cbcb9276.webp)
![](https://filescdn.proginn.com/0e8d22ff43500d3dff94255792f99192/a5f8d0ec1f97b177b209a900eab17951.webp)
小总结
Like %
%百分号通配符: 表示任何字符出现任意次数(可以是0次).
_下划线通配符: 表示只能匹配单个字符,不能多也不能少,就是一个字符.
like操作符: LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较.
SELECT * FROM products WHERE products.prod_name like '1000';
SELECT* FROM products WHERE products.prod_name like '%Li%';
SELECT * FROM products WHERE products.prod_name like '%Li';
右:
虽然走 但是索引级别比较低主要是模糊查询 范围比较大 所以索引级别就比较低![](https://filescdn.proginn.com/e06f4616d2712b74e42a5a6d1d23c7d1/714e02cbb20a9f4ed20c753ad55293c6.webp)
左:
这个范围非常大 所以没有使用索引的必要了 这个可能不是很好优化 还好不是一直拼接上面的![](https://filescdn.proginn.com/6be24687068133b6b0559857a37f324b/1a807118daeae3e04f4ad04e48c81a3c.webp)
小总结
使用Or导致索引失效
![](https://filescdn.proginn.com/a77463182918eaa37b55a282a492f37c/61c90a1771afe8b45eec196293c76c18.webp)
优化
![](https://filescdn.proginn.com/57f027c666ac420527f742f9a02fbf91/5b41b3bc62667a1664c7db786470e441.webp)
in使用不当
![](https://filescdn.proginn.com/25e05b9b90a4f7366956a7430428b58f/cb2ef9e78128c173836b3ba52c11006c.webp)
![](https://filescdn.proginn.com/2491824ec28f3b4e76968e1ca2137f12/00b3f4bc60dd2bb57239fe1d26981ce0.webp)
order By
![](https://filescdn.proginn.com/5e3dcff26c8cd6be661f37f207b1c3d0/5836c4118bcdeb3c38c2bf82eb8861b3.webp)
走索引 + 回表
不走索引 直接全表扫描
子查询会走索引吗
答案是会 但是使用不好就不会
大总结
推荐阅读:
我代码就加了一行log日志,结果引发了P1的线上事故.....
Kubernetes 缺少的多租户功能,你可以通过这些方式实现
互联网初中高级大厂面试题(9个G) 内容包含Java基础、JavaWeb、MySQL性能优化、JVM、锁、百万并发、消息队列、高性能缓存、反射、Spring全家桶原理、微服务、Zookeeper......等技术栈!
⬇戳阅读原文领取! 朕已阅