MySQL深度学习2（内部组件、explain分析、sql语句优化）

hui1203li

已于 2022-06-22 10:19:15 修改

阅读量173

点赞数

文章标签： mysql 数据库 database

于 2022-06-14 13:42:37 首次发布

本文链接：https://blog.csdn.net/hui1203li/article/details/125244045

版权

1.MySQL的内部组件结构

词法分析为以下阶段：

2.SQL语句优化

（1）explain分析

explain extended

会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）

id列

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。

id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

select_type列

select_type 表示对应行是简单还是复杂的查询。

1）simple：简单查询。查询不包含子查询和union

1 mysql> explain select * from film where id = 2;

2）primary：复杂查询中最外层的 select

3）subquery：包含在 select 中的子查询（不在 from 子句中）

4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表

5）union：在 union 中的第二个和随后的 select

1 mysql> explain select 1 union all select 1;

table列

这一列表示 explain 的一行正在访问哪个表。

当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

type列

（2）查询条件优化

最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

改变索引列会破坏在B+树聚合索引的查找方式，结果集改变了，要保证索引树有序

存储引擎不能使用索引中范围条件右边的列

第二个索引范围查找，第三个索引无序。

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

MySQL只需要通过索引就可以查找和返回查询所需要的数据，而不必在使用索引处理数据之后再进行回表操作。覆盖索引可以一次性完成查询工作，有效减少IO，提高查询效率。

.mysql在使用不等于（！=或者<>）， not in ， not exists 的时候无法使用索引会导致全表扫描

< 小于、 > 大于、 <= 、 >= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

is null,is not null 一般情况下也无法使用索引

like以通配符开头（ '$abc...'）mysql索引失效会变成全表扫描操作

解决like'%字符串%'索引不被使用的方法？

a）使用覆盖索引，查询字段必须是建立覆盖索引字段

b）如果不能使用覆盖索引则可能需要借助搜索引擎

字符串不加单引号索引失效

少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引

基数小的字段可以使用in(所有值)。in在数据量大的时候走索引，数据量小的时候不走索引。

详见范围查询优化

（3）分页查询优化

根据自增且连续的主键排序的分页查询

可以添加条件让查询的数据从想要的页数开始。

得满足条件：主键自增且连续；结果是按照主键排序的

根据非主键字段排序的分页查询

将排序和分页操作找到主键，根据主键查到对应的记录。

2.order by和group by优化

1、MySQL支持两种方式的排序 filesort 和 index ，Using index是指MySQL 扫描索引本身完成排序。index效率高，filesort效率低。

2、order by满足两种情况会使用Using index。

1) order by语句使用索引最左前列。

2) 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序 。注意，where高于having，能写在where中的限定条件就不要去having限定了。

Using filesort文件排序原理详解

filesort文件排序方式

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可

以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >

双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的 排序字段 和 可以直接定位行

数据的行 ID ，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >

MySQL 通过比较系统变量 max_length_for_sort_data( 默认1024字节 ) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

如果字段的总长度小于max_length_for_sort_data ，那么使用单路排序模式；

如果字段的总长度大于max_length_for_sort_data ，那么使用双路排序模∙式。

3.join关联查询优化

1.关联字段加索引 ，让mysql做join操作时尽量选择NLJ算法

2.小表驱动大表 ，写多表连接sql时如果 明确知道 哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

straight_join解释：straight_join 功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。

对于小表定义的明确

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤， 过滤完成之后 ，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表” ，应该作为驱动表。

嵌套循环连接 Nested-Loop Join(NLJ) 算法

一次一行循环地从第一张表（称为 驱动表 ）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（ 被驱动 表）里取出满足条件的行，然后取出两张表的结果合集。

基于块的嵌套循环连接 Block Nested-Loop Join( BNL )算法

把 驱动表 的数据读入到 join_buffer 中，然后扫描 被驱动表 ，把 被驱动表 每一行取出来跟join_buffer 中的数据做对比。

4.in和exsits优化

原则： 小表驱动大表 ，即小的数据集驱动大的数据集

in： 当B表的数据集小于A表的数据集时，in优于exists

1 select * from A where id in (select id from B)
2 #等价于：
3 for(select id from B){
4 select * from A where A.id = B.id
5 }

exists： 当A表的数据集小于B表的数据集时，exists优于in 将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

1 select * from A where exists (select 1 from B where B.id = A.id)
2 #等价于:
3 for(select * from A){
4 select * from B where B.id = A.id
5 }
6
7 #A表与B表的ID字段应建立索引

1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会

忽略SELECT清单,因此没有区别

2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比

3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析