Mysql——》查询优化器_mysql优化器

推荐链接：
    总结——》【Java】
    总结——》【Mysql】
    总结——》【Redis】
    总结——》【Kafka】
    总结——》【Spring】
    总结——》【SpringBoot】
    总结——》【MyBatis、MyBatis-Plus】
    总结——》【Linux】
    总结——》【MongoDB】
    总结——》【Elasticsearch】

一、概念

Mysql自带：查询优化器（官网Tracing the Optimizer）

Q：一条 SQL 语句是不是只有一种执行方式？
A：并不是，可以有很多种执行方式的。

Q：如果有这么多种执行方式，这些执行方式怎么得到的？
A：根据查询优化器得到的
1）首先启用优化器的追踪（默认是关闭的）
2）接着执行一个 SQL 语句，优化器会生成执行计划，并且优化器分析的过程已经记录到系统表information_schema.optimizer_trace

Q：最终选择哪一种去执行？根据什么判断标准去选择？
A：Mysql使用 基于开销（cost）的优化器 ，哪个开销最小就用哪个执行计划。

二、作用/目的

1）对sql进行优化

优化类型：

1、对sql语句做重写：条件化简、常量传递、去除没用的条件
2、多表进行关联时，以哪个表的数据作为基准表
3、将一些外连接转换为内连接
4、对in子查询会进行物化、物化表转连接查询、转换为内连接查询
5、多个条件，优先执行哪个条件进行过滤（a=1有300条，b=2有200条，c=3有100条）
6、索引条件下推，从二级索引取得的索引记录，先做条件判断，如果条件不满足，则该二级索引记录不会去回表，这样可以大量的减少回表操作的随机IO成本。
7、在回表操作上，因为每次执行回表操作时都相当于要随机读取一个聚簇索引页面，而这些随机IO带来的性能开销比较大。MySQL中提出了一个名为Disk-Sweep Multi-Range Read (MRR，多范围读取)的优化措施，即先读取一部分二级索引记录，将它们的主键值排好序之后再统一执行回表操作。
8、MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但存在有特殊情况，也可能在一个查询中使用到多个二级索引，称之为：索引合并，比如Intersection交集合并、Union索引合并等等。
9、查询数据，是否直接从索引中取值
10、count()、min()、max，是否直接从索引中取值
11、其他
1
2
3
4
5
6
7
8
9
10
11
12
13

2）选择最优的执行计划

根据解析树生成不同的 执行计划 ，然后选择一种 最优（开销最小）的执行计划 。

-- 查询开销，代表需要随机读取几个4k的数据页才能完成查找
show status like 'Last_query_cost'; 
1
2

三、查询优化器的开启、关闭

1、是否开启

enable：是否开启
one_line：是否在一行显示

-- 默认：enabled=off,one_line=off
SHOW VARIABLES LIKE 'optimizer_trace';
1
2

2、开启

-- 方法1：开启
SET optimizer_trace="enabled=on";

-- 方法2：开启
SET optimizer_trace="enabled=on,one_line=on";

-- 方法3：开启
set @@session.optimizer_trace='enabled=on,one_line=on';

-- 结果：enabled=on,one_line=on
SHOW VARIABLES LIKE 'optimizer_trace';
1
2
3
4
5
6
7
8
9
10
11

注意：开启优化器消耗性能，因为它要把优化分析的结果写到表里面，所以不要轻易开启，或者查看完之后关闭它。

3、关闭

-- 方法1：关闭
SET optimizer_trace="enabled=off";

-- 方法2：关闭
SET optimizer_trace="enabled=off,one_line=off";

-- 结果：enabled=of,one_line=off
SHOW VARIABLES LIKE 'optimizer_trace';
1
2
3
4
5
6
7
8

四、查询优化器怎么得到执行计划？

1、开启查询优化器

SET optimizer_trace="enabled=on,one_line=on";
1

2、执行sql语句，生成优化信息

-- 这2条sql要一起执行，单独执行会导致没有trace信息
select * from student where age >=3 and age <=10 and name like '%a%';
select * from information_schema.optimizer_trace;
1
2
3

3、分析优化过程

（1）prepare阶段

（2）optimize阶段

（3）execute阶段

4、关闭查询优化器

SET optimizer_trace="enabled=off,one_line=off";
1

五、查询执行计划

优化器最终会把解析树变成一个查询执行计划（Mysql——》explain执行计划），查询执行计划是一个数据结构。这个执行计划，不一定是最优的执行计划（Mysql也有可能覆盖不到所有的执行计划）

-- 在sql语句前面加上explain关键字
explain
select * from house_asset.community t1 
left join house_asset.building t2 on t1.community_id = t2.community_id
limit 100;
1
2
3
4
5