MySQl 专栏继续更新 不说不流畅难懂的东西 尽量输出简略了解 和 运用的SQL技巧 和 初中级开发不是很常用的但很有用的知识
欢迎检查SQL 专栏 查漏补缺 指导一二
每一次写博客对技能都会有更深化的了解 积少成多 百天方案我也想看看自己有多少成长 祝君好运 工作顺利
本文正在参与「技能专题19期 闲谈数据库技能」活动
前言
SQL 写欠好 加班少不了 日常工作中SQL 是必不可少的一项技能 可是许多人不会过多的去关注SQL问题 一是数据量小 二是没有意识到索引的重要性 本文首要是收拾 SQL失效场景 假如里边的细节你都知道 那你必定是学习才干比较好的人 膜拜 写完这篇文章 我感觉自己之前知道的真的是 “目录” 没有理解其中的内容 假如你能跟着节奏看完文章 必定会有收成 至少我写完感觉思想通透许多 以后百分之九十的 SQl索引问题 和 面试这方面问题都能拿捏两
文章 字数 六千余字 观看时长十分钟 操练时长两个半小时 继续弥补ing
文章概要
索引失效 收拾
基础数据预备
预备一个数据表作为 数据演示 这儿边一共 创立了三个索引
- 联合索引
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');
问题考虑
上面的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超详解
假如你内心的答案没有悉数说对就接着往下看
最左匹配准则望文生义：最左优先，以最左边的为起点任何接连的索引都能匹配上。一起遇到规模查询(>、<、between、like)就会中止匹配。
例如：s_code = 2 假如树立(sname, s_code)次序的索引，是匹配不到(sname, s_code)索引的;
可是假如查询条件是sname = “变成派大星” and s_code = 2或许a=1(又或许是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 是有次序的 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 也是有序的
这儿应该就能看出 假如没有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创立联合索引的规则是首要会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。所以B=2这种查询条件没有办法运用索引。
看到这儿还能够理解一个道理 为什么咱们树立索引的时分不引荐树立在经常改动的字段 由于这样的话咱们的索引结构就要跟着你的改动而改动 所以很耗费功能
弥补
谈论区老哥的提示 最左缀准则能够经过跳动扫描的方式打破 简略收拾一下这方面的知识
这个是在 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 * 索引运用正常
虽然走了索引可是 也不引荐这种写法 为什么呢？
首要咱们在上一个验证中创立了联合索引 咱们运用B=1 会走索引 可是 与直接查询索引字段不同 运用SELECT*,获取了不需求的数据，则首要经过辅助索引过滤数据，然后再经过集合索引获取一切的列，这就多了一次b+树查询，速度必然会慢许多，削减运用select * 便是降低回表带来的损耗。
也便是 Select * 在一些状况下是会走索引的 假如不走索引便是 where 查询规模过大 导致MySQL 最优选择全表扫描了 并不是Select * 的问题
上图便是索引失效的状况
规模查找也不是必定会索引失效 下面状况就会索引收效便是 等级低 收效的原因是由于缩小了规模
小总结
-
select * 会走索引
-
规模查找有概率索引失效可是在特定的状况下会收效 规模小就会运用 也能够了解为 返回成果集小就会运用索引
-
mysql中衔接查询的原理是先对驱动表进行查询操作，然后再用从驱动表得到的数据作为条件，逐条的到被驱动表进行查询。
-
每次驱动表加载一条数据到内存中，然后被驱动表一切的数据都需求往内存中加载一遍进行比较。功率很低，所以mysql中能够指定一个缓冲池的巨细，缓冲池大的话能够一起加载多条驱动表的数据进行比较，放的数据条数越多功能io操作就越少，功能也就越好。所以，假如此时运用
select *放一些无用的列，只会白白的占用缓冲空间。糟蹋本能够提高功能的时机。 -
依照谈论区老哥的说法 select * 不是形成索引失效的直接原因 大部分原因是 where 后边条件的问题 可是仍是尽量少去运用select * 多少仍是会有影响的
运用函数
运用在Select 后边运用函数能够运用索引 可是下面这种做法就不能
由于索引保存的是索引字段的原始值，而不是经过函数核算后的值，天然就没办法走索引了。
不过，从 MySQL 8.0 开端，索引特性添加了函数索引，即能够针对函数核算后的值树立一个索引，也便是说该索引的值是函数核算后的值，所以就能够经过扫描索引来查询数据。
这种写法我没运用过 感觉状况比较少 也比较简略注意到这种写法
核算操作
这个状况和上面一样 之所以会导致索引失效是由于改动了索引本来的值 在树中找不到对应的数据只能全表扫描
下面这种核算方式就会运用索引
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';
在左不走 在右走
右：
虽然走 可是索引等级比较低首要是模糊查询 规模比较大 所以索引等级就比较低
左：
这个规模十分大 所以没有运用索引的必要了 这个或许不是很好优化 还好不是一直拼接上面的
小总结
索引的时分和查询规模联系也很大 规模过大形成索引没有意义从而失效的状况也不少
运用Or导致索引失效
这个原因就更简略了
在 WHERE 子句中，假如在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效 举个比如，比如下面的查询句子，b 是主键，e 是一般列，从执行方案的成果看，是走了全表扫描。
优化
这个的优化方式便是 在Or的时分两头都加上索引
就会运用索引 避免全表扫描
in运用不当
首要运用In 不是必定会形成全表扫描的 IN肯定会走索引，可是当IN的取值规模较大时会导致索引失效，走全表扫描
in 在成果集 大于30%的时分索引失效
not in 和 In的失效场景相同
order By
- 走索引 + 回表
- 不走索引 直接全表扫描
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 = 3
5.6 之前
- 先经过 联合索引 查询到 最初为 3 的数据 然后拿到主键（上图中青色块为主键）
- 然后经过主键去主键索引里边去回表查询 二级索引里边查询出来几个 3 最初的就回表几次
5.6 之后
- 先经过 二级索引 查询到最初为 3 的数据 然后 再找到 c = 3 的数据进行过滤 之后拿到主键
- 经过主键进行回表查询
上面都会进行回表查询可是 5.6 之前没有完全去运用 二级缓存进行数据过滤 假如 3 最初的数据十分多 那就要一直回表 可是 5.6 之后去运用后续索引字段进行查询
怎么说呢 便是为什么索引下推要和联合索引进行运用 一般所以没有 索引下推便是充分运用 联合索引的字段进过滤 尽量削减需求回表的数据 来添加查询功率 感觉思路是很简略的
关于Innodb 引擎的ICP 只适合 二级索引
小细节：
索引下推除了依靠 联合索引之外 还不能在子查询下面进行运用 存储函数也不能运用
怎么检查是否运用索引下推
- 假如你是直接跳到这儿 看看文章有多长
主张收藏 - 假如你一步步看到这儿 感觉有点帮助
赞赞来一个 - 假如感觉文章有问题 主张谈论区指出
会修正
周五愉快 文章完结
继续更新SQL相联系列 可追更 不可催更
本文正在参与「技能专题19期 闲谈数据库技能」活动
