Mysql之查询优化器

对于一个SQL语句，查询优化器先看是不是能转换成JOIN，再将JOIN进行优化

优化分为：

　　1. 条件优化

　　2.计算全表扫描成本

　　3. 找出所有能用到的索引

　　4. 针对每个索引计算不同的访问方式的成本

　　5. 选出成本最小的索引以及访问方式

开启查询优化器日志

-- 开启
set optimizer_trace="enabled=on";
-- 执行sql
-- 查看日志信息
select * from information_schema.OPTIMIZER_TRACE;
-- 关闭
set optimizer_trace="enabled=off";

常量传递（Constant_propagation）

a = 1 AND b > a

上面这个sql可以转换为：

a = 1 AND b > 1　　

等值传递（equality_propagation）

a = b and b = c and c = 5

上面这个sql可以转换为：

a = 5 and b = 5 and c = 5　

移除没用的条件（trivial_condition_removal）

a = 1 and 1 = 1

上面这个sql可以转换为：

a = 1

基于成本

一个查询可以有不同的执行方案，可以选择某个索引进行查询，也可以选择全表扫描，查询优化器会选择其中成本最低的方案去执行查询。

I/O成本

InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。

InnoDB存储引擎规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。

基于成本的优化步骤

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询

下边我们就以一个实例来分析一下这些步骤，单表查询语句如下：

select * from employees.titles where emp_no > '10101' and emp_no < '20000' and to_date = '1991-10-10';

1. 根据搜索条件，找出所有可能使用的索引

• • emp_no > '10101'，这个搜索条件可以使用主键索引PRIMARY。
  • to_date = '1991-10-10'，这个搜索条件可以使用二级索引idx_titles_to_date。

------------恢复内容开始------------

对于一个SQL语句，查询优化器先看是不是能转换成JOIN，再将JOIN进行优化

优化分为：

　　1. 条件优化

　　2.计算全表扫描成本

　　3. 找出所有能用到的索引

　　4. 针对每个索引计算不同的访问方式的成本

　　5. 选出成本最小的索引以及访问方式

开启查询优化器日志

-- 开启
set optimizer_trace="enabled=on";
-- 执行sql
-- 查看日志信息
select * from information_schema.OPTIMIZER_TRACE;
-- 关闭
set optimizer_trace="enabled=off";

常量传递（Constant_propagation）

a = 1 AND b > a

上面这个sql可以转换为：

a = 1 AND b > 1　　

等值传递（equality_propagation）

a = b and b = c and c = 5

上面这个sql可以转换为：

a = 5 and b = 5 and c = 5　

移除没用的条件（trivial_condition_removal）

a = 1 and 1 = 1

上面这个sql可以转换为：

a = 1

基于成本

一个查询可以有不同的执行方案，可以选择某个索引进行查询，也可以选择全表扫描，查询优化器会选择其中成本最低的方案去执行查询。

I/O成本

InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。

InnoDB存储引擎规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。

基于成本的优化步骤

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询

下边我们就以一个实例来分析一下这些步骤，单表查询语句如下：

select * from employees.titles where emp_no > '10101' and emp_no < '20000' and to_date = '1991-10-10';

1. 根据搜索条件，找出所有可能使用的索引

• • emp_no > '10101'，这个搜索条件可以使用主键索引PRIMARY。
  • to_date = '1991-10-10'，这个搜索条件可以使用二级索引idx_titles_to_date。

------------恢复内容结束------------