SQL 写不好 加班少不了 日常工作中SQL 是必不可少的一项技术 但是很多人不会过多的去关注SQL问题 一是数据量小 二是没有意识到索引的重要性 本文主要是整理 SQL失效场景 如果里面的细节你都知道 那你一定是学习能力比较好的人 膜拜写完这篇文章 我感觉自己之前知道的真的是 “目录” 没有明白其中的内容 如果你能跟着节奏看完文章 一定会有收获 至少我写完感觉思维通透很多 以后百分之九十的 SQl索引问题 和 面试这方面问题都能拿索引失效 整理基础数据准备准备一个数据表作为 数据演示 这里面一共 创建了三个索引联合索引sname,s_code,address主键索引id普通索引heightSET 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);问题思考上面的SQL 我们已经创建好基本的数据 在验证之前 先带着几个问题我们先从上往下进行验证最左匹配原则写在前面我很早之前就听说过数据库的最左匹配原则当时是通过各大博客论坛了解的但是这些博客的局限性在于它们对最左匹配原则的描述就像一些数学定义一样往往都是列出123点满足这123点就能匹配上索引否则就不能。最左匹配原则就是指在联合索引中如果你的 SQL 语句中用到了联合索引中的最左边的索引那么这条 SQL 语句就可以利用这个联合索引去进行匹配我们上面建立了联合索引 可以用来测试最左匹配原则sname,s_code,address请看下面SQL语句 进行思考 是否会走索引-- 联合索引 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 上海 -- 会走索引吗凭你的经验 哪些会使用到索引呢 可以先思考一下 在心中记下数字走索引例子EXPLAIN select create_time from student where sname 变成派大星 -- 会走索引吗未走索引例子EXPLAIN select create_time from student where address 上海 and s_code 1 -- 会走索引吗走的全表扫描 rows 4如果不知道EXPLAIN是什么的 或者看不懂分析出来的数据的话 建议去看看另一篇文章分析命令EXPLAIN超详解[1]如果你内心的答案没有全部说对就接着往下看最左匹配原则顾名思义最左优先以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(、、between、like)就会停止匹配。例如s_code 2 如果建立(sname,s_code)顺序的索引是匹配不到(sname,s_code)索引的;但是如果查询条件是sname 变成派大星 and s_code 2或者a1(又或者是s_code 2 and sname 变成派大星 )就可以因为优化器会自动调整sname,s_code的顺序。再比如sname 变成派大星 and s_code 1 and address 上海address是用不到索引的因为s_code字段是一个范围查询它之后的字段会停止匹配。不带范围查询 索引使用类型带范围使用类型根据上一篇文章的讲解 可以明白 ref 和range的含义 级别还是相差很多的思考为什么左链接一定要遵循最左缀原则呢验证看过一个比较好玩的回答你可以认为联合索引是闯关游戏的设计例如你这个联合索引是state/city/zipCode那么state就是第一关 city是第二关 zipCode就是第三关你必须匹配了第一关才能匹配第二关匹配了第一关和第二关才能匹配第三关这样描述不算完全准确 但是确实是这种思想要想理解联合索引的最左匹配原则先来理解下索引的底层原理。索引的底层是一颗B树那么联合索引的底层也就是一颗B树只不过联合索引的B树节点中存储的是键值。由于构建一棵B树只能根据一个值来确定索引关系所以数据库依赖联合索引最左的字段来构建 文字比较抽象 我们看一下加入我们建立 A,B 联合索引 他们在底层储存是什么样子呢橙色代表字段 A浅绿色 代表字段B图解我们可以看出几个特点A 是有顺序的 112234B 是没有顺序的 121412 这个是散列的如果A是等值的时候 B是有序的 例如 1112 这里的B有序的 21,(2,4) B 也是有序的这里应该就能看出 如果没有A的支持 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;在创建索引树的时候会对数据进行排序 根据最左缀原则 会先通过 B 进行排序 也就是 如果出现值相同就 根据 C 排序 如果 C相同就根据D 排序 排好顺序之后就是如下图索引的生成就会根据图二的顺序进行生成 我们看一下 生成后的树状数据是什么样子解释一些这个树状图 首先根据图二的排序 我们知道顺序 是 1111a 2222b 所以 在第三层 我们可以看到 1111a 在第一层 2222b在第二层 因为 111 222 所以 111 进入第二层 然后得出第一层简化一下就是这个样子但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序在第一个字段的排序基础上然后在对第二个字段进行排序。所以B2这种查询条件没有办法利用索引。看到这里还可以明白一个道理 为什么我们建立索引的时候不推荐建立在经常改变的字段 因为这样的话我们的索引结构就要跟着你的改变而改动 所以很消耗性能补充评论区老哥的提示 最左缀原则可以通过跳跃扫描的方式打破 简单整理一下这方面的知识这个是在 8.0 进行的优化MySQL8.0版本开始增加了索引跳跃扫描的功能当第一列索引的唯一值较少时即使where条件没有第一列索引查询的时候也可以用到联合索引。比如我们使用的联合索引是 bcd 但是b中字段比较少 我们在使用联合索引的时候没有 使用 b 但是依然可以使用联合索引MySQL联合索引有时候遵循最左前缀匹配原则有时候不遵循。小总结前提 如果创建 b,c,d 联合索引面如果 我where 后面的条件是c 1 and d 1为什么不能走索引呢 如果没有b的话 你查询的值相当于*11我们都知道*是所有的意思也就是我能匹配到所有的数据如果 我 where 后面是b 1 and d 1为什么会走索引呢你等于查询的数据是1*1我可以通过前面 1 进行索引匹配 所以就可以走索引最左缀匹配原则的最重要的就是 第一个字段我们接着看下一个失效场景select *思考这里是我之前的一个思维误区select *不会导致索引失效 之前测试发现失效是因为where 后面的查询范围过大 导致索引失效 并不是Select *引起的 但是为什么不推荐使用select *解释增加查询分析器解析成本。增减字段容易与 resultMap 配置不一致。无用字段增加网络 消耗尤其是 text 类型的字段。在阿里的开发手册中大面的概括了上面几点。在使用Select *索引使用正常虽然走了索引但是 也不推荐这种写法 为什么呢首先我们在上一个验证中创建了联合索引 我们使用B1 会走索引 但是 与直接查询索引字段不同 使用SELECT*,获取了不需要的数据则首先通过辅助索引过滤数据然后再通过聚集索引获取所有的列这就多了一次b树查询速度必然会慢很多减少使用select *就是降低回表带来的损耗。也就是Select *在一些情况下是会走索引的 如果不走索引就是 where 查询范围过大 导致MySQL 最优选择全表扫描了 并不是Select *的问题上图就是索引失效的情况范围查找也不是一定会索引失效 下面情况就会索引生效就是 级别低 生效的原因是因为缩小了范围小总结select *会走索引范围查找有概率索引失效但是在特定的情况下会生效 范围小就会使用 也可以理解为 返回结果集小就会使用索引mysql中连接查询的原理是先对驱动表进行查询操作然后再用从驱动表得到的数据作为条件逐条的到被驱动表进行查询。每次驱动表加载一条数据到内存中然后被驱动表所有的数据都需要往内存中加载一遍进行比较。效率很低所以mysql中可以指定一个缓冲池的大小缓冲池大的话可以同时加载多条驱动表的数据进行比较放的数据条数越多性能io操作就越少性能也就越好。所以如果此时使用select *放一些无用的列只会白白的占用缓冲空间。浪费本可以提高性能的机会。按照评论区老哥的说法select *不是造成索引失效的直接原因 大部分原因是 where 后边条件的问题 但是还是尽量少去使用select *多少还是会有影响的使用函数使用在Select 后面使用函数可以使用索引 但是下面这种做法就不能因为索引保存的是索引字段的原始值而不是经过函数计算后的值自然就没办法走索引了。不过从 MySQL 8.0 开始索引特性增加了函数索引即可以针对函数计算后的值建立一个索引也就是说该索引的值是函数计算后的值所以就可以通过扫描索引来查询数据。这种写法我没使用过 感觉情况比较少 也比较容易注意到这种写法计算操作这个情况和上面一样 之所以会导致索引失效是因为改变了索引原来的值 在树中找不到对应的数据只能全表扫描因为索引保存的是索引字段的原始值而不是b - 1表达式计算后的值所以无法走索引只能通过把索引字段的取值都取出来然后依次进行表达式的计算来进行条件判断因此采用的就是全表扫描的方式。下面这种计算方式就会使用索引Java比较熟悉的可能会有点疑问这种对索引进行简单的表达式计算在代码特殊处理下应该是可以做到索引扫描的比方将b - 1 6变成b 6 - 1。是的是能够实现但是 MySQL 还是偷了这个懒没有实现。小总结总而言之 言而总之 只要是影响到索引列的值 索引就是失效Like %这个真的是难受哦 因为经常使用这个 所以还是要小心点 在看为什么失效之前 我们先看一下 Like % 的解释%百分号通配符:表示任何字符出现任意次数(可以是0次)._下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符.like操作符:LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较.注意:如果在使用like操作符时,后面的没有使用通用匹配符效果是和一致的,SELECT * FROM products WHERE products.prod_name like 1000;2.匹配包含Li的记录(包括记录Li) :SELECT* FROM products WHERE products.prod_name like %Li%;3.匹配以Li结尾的记录(包括记录Li,不包括记录Li ,也就是Li后面有空格的记录,这里需要注意)SELECT * FROM products WHERE products.prod_name like %Li;在左不走 在右走右虽然走 但是索引级别比较低主要是模糊查询 范围比较大 所以索引级别就比较低左这个范围非常大 所以没有使用索引的必要了 这个可能不是很好优化 还好不是一直拼接上面的。公众 号Java精选回复java面试获取面试资料支持在线刷题。小总结定期回顾-审视自己、查缺补漏索引的时候和查询范围关系也很大 范围过大造成索引没有意义从而失效的情况也不少使用Or导致索引失效这个原因就更简单了在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效 举个例子比如下面的查询语句b 是主键e 是普通列从执行计划的结果看是走了全表扫描。优化这个的优化方式就是 在Or的时候两边都加上索引就会使用索引 避免全表扫描in使用不当首先使用In 不是一定会造成全表扫描的IN肯定会走索引但是当IN的取值范围较大时会导致索引失效走全表扫描in 在结果集 大于30%的时候索引失效not in 和 In的失效场景相同order By这一个主要是Mysql 自身优化的问题 我们都知道OrderBy 是排序 那就代表我需要对数据进行排序 如果我走索引 索引是排好序的 但是我需要回表 消耗时间 另一种 我直接全表扫描排序 不用回表 也就是走索引 回表不走索引 直接全表扫描Mysql 认为直接全表扫面的速度比 回表的速度快所以就直接走索引了 在Order By的情况下 走全表扫描反而是更好的选择子查询会走索引吗答案是会 但是使用不好就不会大总结减少回表优化思路这个对于SQL有研究的人可能是比较了解的但是对于工作时长不久的会比较陌生的词语 但是这个是非常有意思 且重要的在这个索引问题上面还有一个细节的东西 其中印象比较深刻的是回表会造成效率下降 但是在我们日常工作中是比较常用单列索引 联合索引对于新手来说不是很常用 但是单列索引在一些情况下肯定不是最优解 例如 like % 问题 会造成索引问题 近期了解到一个ICP知识 我之前都没有关注过 不知道大家对这个了解多少 我这里就进行一些整理首先我们ICP 全称是Index Condition Pushdown中文可以说成是索引下推 主要的作用解决数据查询回表的问题 但是前提是和联合索引进行使用 才能发挥出来功效 接下来不了解的小伙伴可以认真看一下这一点 个人感觉还是比较有意思的东西回表问题上面其实对于回表查询没有过多的解释 就再提一什么是回表查询回表查询一般发生在非主键索引上面 需要进行两次树查询 所以效率会有所折扣 我们要想解决这个行为就可以使用 联合索引去优化ICP 索引下推这个是在MySQL 5.6 之后提供的特性 这个如果面试中问到 我们平常面试的时候 面试官都有喜欢问什么版本 增加了什么 如果问你 MySQL 5.6 之后增加什么优化 不知道大家都能说出什么 这个就是一个很加分点 你能说明白什么是索引下推 面试官会对你增加好感 至少说明你还是有点东西在身上的 不啰嗦了 开始研究我们先看一下 5.6 之前 和 5.6 之后 查询流程会有什么变化假设 我我们需要查询select * from table1 where b like 3% and c 35.6 之前先通过 联合索引 查询到 开头为 3 的数据 然后拿到主键上图中青色块为主键然后通过主键去主键索引里面去回表查询 二级索引里面查询出来几个 3 开头的就回表几次5.6 之后先通过 二级索引 查询到开头为 3 的数据 然后 再找到 c 3 的数据进行过滤 之后拿到主键通过主键进行回表查询上面都会进行回表查询但是 5.6 之前没有完全去利用 二级缓存进行数据过滤 如果 3 开头的数据非常多 那就要一直回表 但是 5.6 之后去利用后续索引字段进行查询怎么说呢 就是为什么索引下推要和联合索引进行使用 普通所以没有 索引下推就是充分利用 联合索引的字段进过滤 尽量减少需要回表的数据 来增加查询效率 感觉思路是很简单的对于Innodb 引擎的ICP 只适合 二级索引小细节索引下推除了依赖 联合索引之外 还不能在子查询下面进行使用 存储函数也不能使用怎么查看是否使用索引下推