MySQL中 “Explain”、“索引查询优化” 以及 “JOIN 原理”

迷迷的k

已于 2023-11-10 01:19:27 修改

阅读量695

点赞数 3

分类专栏： MySQL 文章标签：数据库 mysql

于 2023-05-29 10:22:03 首次发布

本文链接：https://blog.csdn.net/qq_66862911/article/details/130918322

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

1.2.1 关于 Explain 的最佳试题（doge）

2.1 Simple Nested-Loop Join 【简单】嵌套循环连接

2.2 Index Nested-Loop Join 【索引】嵌套循环连接

2.3 Block Nested-Loop Join 【块】嵌套循环连接

2.4 JOIN 原理总结

1、关于索引

1.1 谈谈索引

前文引入：

“索引” 一词相信大家已经耳濡目染已久，那么，索引到底是什么呢？

索引（INDEX）是帮助 MySQL 高效获取数据的【数据结构】，其存在的目的在于提高查找的效率，类似于字典

简单一句话就是：“排好序的快速查找的数据结构”

索引的分类：

单值索引：即索引只包含单个列，一张表可以有多个单列索引
唯一索引：其索引的值必须唯一，但允许存在空值
复合索引：即一个索引可以包含多个列

基本的语法：

创建：

                create (Unique唯一) index [ Index_name ] on [ My_table (列名...) ];

                alter [ My_table ] add (Unique唯一) index [ Index_name ] on [ (列名...) ];

删除：

                drop index [ Index_name ] on [ My_table ];

查看：

                show index from [ My_table ];

1.1.1 适合创建索引的情况

频繁使用的列：如果某个列经常用于查询的WHERE条件或JOIN操作中，那么为这个列创建索引可以加快查询速度

唯一性要求：对于要求具有唯一值的列，如主键列或唯一约束列，创建索引是必要的。索引可以确保列值的唯一性，并且还可以加速根据这些列进行查找和插入操作

经常进行排序或者分组的列：如果查询中包含了对某个列进行排序、分组或聚合操作（如COUNT、MAX、MIN等），为这个列创建索引可以提升这些操作的性能

连接操作的列：对于经常进行表连接操作的列，创建索引可以加快连接过程的速度。例如，在JOIN操作中经常使用的连接列，为其创建索引可以提高连接的效率

1.1.2 不适合创建索引的情况

小型表：对于非常小的表，全表扫描的代价可能比使用索引进行查找的开销更低；在这种情况下，创建索引可能没有明显的性能提升，并且会增加额外的存储和维护开销

插入更新频繁的表：对于频繁进行插入和更新操作的表，索引的维护开销可能超过了查询性能的提升；每次进行插入或更新操作时，数据库系统还需要更新索引结构，因此在这种情况下创建索引可能会导致性能下降

频繁变更的列：对于经常发生变更的列，如日志记录表中的时间戳列，创建索引可能会导致频繁的索引维护开销，并对性能产生负面影响

查询过滤结果较大的列：如果某个列的值分布广泛，那么使用索引进行查询的效果可能不好；例如，对于一个只有两个可能值（如性别列）的列，使用索引进行查询可能会导致查询优化器选择全表扫描而不是使用索引

1.2 关于 EXPLAIN （面试）

作用：

Explain 可以模拟 SQL 优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的，分析所查询的语句或者表结构的性能瓶颈

举个栗子🤔：

这里是一个简单的 explain 句子（explain + SQL）

EXPLAIN SELECT * from departments;

执行结果是对应的 SQL 性能信息

相关字段的解释：

id 执行顺序：

关于 id 相同与 id 不同同时存在，这里有一个例子🤔：

首先加载括号里面的 t3 表，之后会将这个 t3 表衍生为 s1 表；这里的 teble 中的 <derived2>就表示衍生， derived 后面的 2 表示 id 为 2 的 table表，即 t3；所以这里 table 执行的顺序为 t3 -> derived2（即 s1） -> t2

select_type 查询类型：

table 访问的表：

即显示这一行数据是关于哪一张表的

partitions 匹配的分区：

具体的分区名：如果查询仅涉及特定的分区或分区子集，partitions列将显示被查询命中的分区名称；这表示查询只在指定的分区上执行，而不需要扫描整个表

"ALL"：如果查询涉及所有分区，partitions列将显示为"ALL"；这表示查询将扫描表中的所有分区

"NULL"：如果查询不使用分区策略，partitions列将显示为"NULL"；这表示表不存在分区，查询将在整个表上执行

type 访问类型：

注意：一般来说，得保证查询至少达到range级别，最好能达到ref

访问类型具体含义如图所示：

possible_keys 可能使用的索引（SQL 自己推测的应该使用的索引）：

key 实际使用的索引（SQL 实际上使用到的索引）：

实际使用的索引；如果为NULL，则没有使用索引；查询中若使用了覆盖索引，则该索引仅出现在 key 列表中

key_len 索引中使用的字节数：

当然，在增加索引使用率的同时，该长度的增加是不可避免的，有得必有失 ( •̀ ω •́ )✧

ref 显示索引中被使用的列：

显示索引的哪一列被使用了，如果可能的话，是一个常数；哪些列或常量被用于查找索引列上的值

举个栗子🤔（如图所示）：

表 t1，t2 中的 col1 和 col2 为 Index 索引字段

rows 必须检查的行数：

举个栗子🤔：

这里是没有使用索引时， rows 需要检查的行数

这里是创建了索引之后，SQL 必须检查的 rows 行数

总之， rows 检查行数越少越好

filtered 过滤比例：

表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数

Extra：

EXTRA字段是EXPLAIN输出结果中的一列；它提供了有关查询执行过程中的额外信息和注释

具体的值所对应的含义如图所示：

这里举个栗子🤔：

前提说明：表 t1 中创建了三个索引，分别是 col1、col2 和 col3

由图可知，这里出现了 Using filesort ，SQL 内部会发生内排序，导致性能急剧下降，因此需要尽快的优化

原因：由于后面的 order by 直接到了 col3，而索引是按照最左原则进行查找，所以它排序的顺序应该为 col1 -> col2 -> col3；而这里由于没有 col2 ，从而在 col2 这里进行了全表的扫描，然后才到 col3

更改完善后：

这里是其余的字段所对应的含义（我就不具体写了）：

1.2.1 关于 Explain 的最佳试题（doge）

根据查询出来的 SQL 执行信息，找出对应 table 表的执行顺序

解答结果：

1.3 常见的索引失效以及相关的优化

计算、函数、类型转换(自动或手动) 都会导致索引失效

这里的 SELECT 查询字段中的 SQL_NO_CACHE 是一个指示性的关键字，用于告诉查询优化器在执行查询时不要缓存结果；它用于强制重新执行完整的查询，而不使用查询缓存；这对于执行频繁变动的查询或需要即时的最新数据的查询很有用 φ(*￣0￣)

#1、关于函数失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; #索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%'; #索引优化生效


------------------------------------------------------------------------------------


#2、关于计算失效

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001; #索引失效

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000; #索引优化生效


------------------------------------------------------------------------------------


#3、关于类型转换失效

# 其中 name 是 varchar 类型

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = 123;    #索引失效，存在隐式函数转换

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = '123';  #索引优化生效


------------------------------------------------------------------------------------


#4、关于 range 范围条件索引的右边索引失效

#最终还是要根据 SQL 中优化器调优后的执行顺序来进行判断

create index idx_age_name_classid on student(age,name,classid);    #添加索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age = 30 AND student.classId>20 AND student.name = 'abc';   # name 索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age = 30 AND student.name ='abc' AND student.classId>20; #索引优化生效


------------------------------------------------------------------------------------


#5、is null可能索引生效，is not null无法使用索引


#索引失效，因为 IS NOT NULL 相当于直接全表查询，遍历树 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL; 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;  #索引可能优化生效
 

------------------------------------------------------------------------------------


#6、like以通配符%开头索引失效

#索引失效，因为没有明确的开头查询条件，相当与遍历树，不满足最左原则
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';  

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';  #索引优化生效


## 【若要使用 %ab% 样式的 like 查询，该如何保证既查得出数据，也引用了索引】
## 这里我们就需要使用 “覆盖索引”，下面我们先创建索引

         ##假设 student 属性有,id(主键),age,name,grade,classId
         CREATE INDEX idx_age_name ON student(age,name);

#部分字段不在创建的索引内，所以会造成索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab%'; #索引失效

EXPLAIN SELECT age,name FROM student WHERE name LIKE '%ab%'; #索引数据查询使用成功

#这里的 id 为 PRIMARY主键，所以归为覆盖索引中的一类
EXPLAIN SELECT id,age,name FROM student WHERE name LIKE '%ab%'; #索引数据查询使用成功



------------------------------------------------------------------------------------



#7、最左前缀匹配原则，以下是 【索引失效】 的例子

## 索引创建时：
              CREATE INDEX idx_age_name_classId ON student(age,name,classId);

## 查询一：这个时候是存在索引的

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 1 AND name = 'zs' AND classId = 6;

## 查询二：这个时候索引已经失效了，因为创建索引时是以 age->name->classId 的顺序，现在 age 字段已经不存在了，即 age 后面索引也不会顺着下去了

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = 'zs' AND classId = 6;

## 查询三：与查询二同理，只用到了 age 字段的索引，因为 name 字段已经不存在了，classId 字段索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 1 AND classId = 6;

1.3.1 索引单表优化

举个栗子🤔：

题目：

对应的 SQL 语句：


explain select id,author_id 
from article 
where category_id = 1 and comments>1
order by views desc 
limit 1;

使用 Explain 输出的 SQL 结果如图所示：

很显然，这里的 type 不仅为 ALL ，而且，Extra 的字段包含了 Using filesort，这是不能忍受的

创建索引进行优化，下面是 SQL 语句：

#将题目中的三个字段分别加上索引
create index idx_article_ccv 
on article(category_id,comments,views);

使用 Explain 输出的 SQL 结果如图所示：

可见，虽然解决了全表查询，然而 Extra 依然没有发生改变，我们仍然需要优化

这里我们总结出来了一个结论：

这时，我们应该改变索引的现状，将中间的范围字段的索引删除，因为它会影响到之后的索引：

#先将之前的索引删除
DROP INDEX idx_article_ccv ON article;


create index idx_article_cv 
on article(category_id,views);

使用 Explain 输出的 SQL 结果如图所示：

既消除了全表扫描，也消除了文件内排序，优化完成！

结论：当多个索引中，存在范围性的索引，由于范围索引可能会导致索引失效的问题，所以最好将 range 类型的索引字段的索引删除

1.3.2 索引双表优化

举个栗子🤔，下面是一段 SQL 语句：

#这里是左表连接
explain select * from class 
left join book
on class.card = book.card;

这里我们先创建 class ，即左表的索引，下面是 SQL 语句：

alter table class 
add index Y (card);

使用 Explain 输出的 SQL 结果如图所示：

可见，将索引加在左表不起任何效果；原因是由于 left join 已经让左表进行了全表查询，所以加不加索引都无所谓，不会改变 rows 的列数

当索引加在右表时，type 从 all 变为了 ref ，可见效果明显

结论：当 SQL 进行 Left Join 左连接时，索引需加在右表处，因为左表 SQL 内部已经进行了“全表扫描”；当进行 Right Join 右连接时同理

1.3.3 索引三表优化

结论：三表优化其实跟双表大相径庭，其实就是保证 Join 语句中 "被驱动表" 中的 Join 后面的条件字段已经被索引

1.3.4 索引优化的总结口诀

全值匹配我最爱，最左前缀要遵守（全职匹配：最好使用完整的值来匹配索引列，而不是只使用部分值或模糊匹配）

带头大哥不能死，中间兄弟不能断（范围索引等）

索引列上少计算，范围之后全失效（少计算：指的是少用函数以及相关的数学计算）

LIKE百分写最右，覆盖索引不写星

不等空值还有or，索引失效要少用（不等：!=；空值：NOT IN）

1.4 关于覆盖索引

什么是覆盖索引？

索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行；毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了；一个索引包含了满足查询结果的数据就叫做覆盖索引

我们也可以这样理解；它是非聚集复合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列，也就是数据列只用从索引中就能取得，不必读取数据行（即被创建索引的字段，正好是覆盖查询条件中所涉及的字段；也即，索引包含了正在查找的数据）

注意事项：

之前举例的索引失效 SQL 语句，进行添加索引时，也并不一定会失效（要看你索引怎么加），而会使用覆盖索引；因为 SQL 的优化器会在 firesort 和使用索引之间进行权衡，估算成本

优点：

避免 InnoDB 进行二次查询，也就是避免回表
可以把随机的 IO 变成顺序的 IO ，以提高查询的效率

缺点：

索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作

索引使用建议（补充）：

2、谈谈 JOIN 原理

JOIN 方式连接多个表，本质就是各个表之间的数据的循环匹配；在 MySQL5.5 之前，用的就是嵌套循环（Nested Loop Join）；若表中的数据非常大，则 join 所关联的执行时间会很长。在 MySQL5.5 之后，用的是 BNLJ 算法来进行优化嵌套执行

2.1 Simple Nested-Loop Join 【简单】嵌套循环连接

这里是将 A表 中取出一条数据，然后与 B表 进行遍历，将匹配的结果放到 result 中，以此类推

驱动表A 中的每一条记录都与 驱动表B 中的数据进行判断

显然，这种方式进行嵌套循环的效率是非常低的

2.2 Index Nested-Loop Join 【索引】嵌套循环连接

这里，索引嵌套的优化思路主要的为了 减少内层表数据的匹配次数（B + 树）

将外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样就极大的减少了对内层表的匹配次数

这里需要注意的是：

如果索引不是主键索引，则需要进行一次回表查询
若是主键索引，则效率 yyds

若表中没有进行添加索引或者因为 SQL 语句导致索引失效，则这种方式就属于摆设了，就又回到简单嵌套查询连接那里了；不过不要慌，还提供了接下来的一种方式 φ(*￣0￣)

2.3 Block Nested-Loop Join 【块】嵌套循环连接

虽然说跟 Simple Join 一样，没有进行添加索引，但是，Block 不再像 Simple 是逐条获取驱动表的数据，而是 分成一块一块的获取；
同时，引入了 JOIN BUFFER 缓冲区，将驱动表 JOIN 相关的部分数据列缓存到 JOIN BUFFER 中
在全表扫描时，被驱动表中的每一条数据一次性的和 JOIN BUFFER 中所有的驱动表记录进行匹配，这里将 Simple Join 中多次比较合并成了一次，降低了 IO 的次数，从而提高了访问效率

注意事项：

这里缓存的不只是关联表的列，SELECT 后面的列也会被缓存起来
在一个有 N 个 JOIN 关联的 SQL 中，会分配 N-1 个 JOIN BUFFER
查询的时候尽量减少不必要的字段，以让 JOIN BUFFER 中可以存放更多的列

2.4 JOIN 原理总结

以上的几种嵌套循环连接的整体效率比较：INLJ > BNLJ > SNLJ

用小结果集驱动大结果集，以减少 IO 次数

#推荐，t1 作为驱动表，t2 作为被驱动表
select  t1.b,t2.* from  t1  straight_join   t2  on  t1.b=t2.b  where  t2.id <= 100;  

#不推荐，t2 作为驱动表，t1 作为被驱动表
select  t1.b,t2.* from  t2  straight_join   t1  on  t1.b=t2.b  where  t2.id <= 100;

为被驱动表匹配的条件增加索引（减少内层表中循环匹配的次数）
增大 JOIN BUFFER SIZE 的大小（一次缓存的越多，扫表的次数就越少）
减少驱动表不必要的字段查询（字段越少，JOIN BUFFER 所缓存的数据就越多）

这里需要知道的是，8.0版本的 MySQL 废弃了 BNJL ，用 Hash Join 进行替代

Nested Loop 与 Hash Join 对比图：

迷迷的k

关注

3
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
MySQL中 “Explain”、“索引查询优化” 以及 “JOIN 原理”

用小结果集驱动大结果集，以减少 IO 次数#推荐，t1 作为驱动表，t2 作为被驱动表#不推荐，t2 作为驱动表，t1 作为被驱动表为被驱动表匹配的条件增加索引（减少内层表中循环匹配的次数）增大 JOIN BUFFER SIZE 的大小（一次缓存的越多，扫表的次数就越少）减少驱动表不必要的字段查询（字段越少，JOIN BUFFER 所缓存的数据就越多）这里需要知道的是，8.0版本的 MySQL 废弃了 BNJL ，用 Hash Join 进行替代。
复制链接

扫一扫