开启查询优化器

  1. -- 开启查询优化器
  2. set optimizer_trace="enabled=on";
  4. -- 执行 SQL
  5. SELECT * from t1 where a=1 and b=1;
  7. -- 查询查询优化器执行步骤
  8. select * from information_schema.OPTIMIZER_TRACE;

equality_propagation：等值传递
constant_propagation：常量传递
trivial_condition_removal：无效值移除

基于成本

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

I/O成本

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

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

基于成本的优化步骤

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

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

  1. select * from employees.titles 
  2. 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。
上边的查询语句可能用到的索引，也就是 possible keys 只有 PRIMARY 和 idx_titles_to_date。

2、计算全表扫描的代价

对于 InnoDB 存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本=I/O成本+CPU成本，所以计算全表扫描的代价需要两个信息：

  • 聚簇索引占用的页面数
  • 该表中的记录数

MySQL 为每个表维护了一系列的统计信息， SHOW TABLE STATUS LIKE ‘tableName’ 语句来查看表的统计信息

Rows
表示表中的记录条数。对于使用 MyISAM 存储引擎的表来说，该值是准确的，对于使用 InnoDB 存储引擎的表来说，该值是一个估计值

Data_length（单位：字节）

表示表占用的存储空间字节数。使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：Data_length = 聚簇索引的页面数量 x 每个页面的大小

我们的 titles 表使用默认 16KB 的页面大小，而上边查询结果显示 Data_length 的值是 20512768，所以我们可以反向来推导出聚簇索引的页面数量：

聚簇索引的页面数量 = Data_length ÷ 1024 ÷ 16 = 20512768 ÷ 16 ÷ 1024 = 1252
**
我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，所以就可以计算全表扫描成本了。但是MySQL在真实计算成本时会进行一些微调。

I/O成本：12521 = 1252。1252指的是聚簇索引占用的页面数，1.0指的是加载一个页面的成本常数。

CPU成本：442070 * 0.2=88414。442070 指的是统计数据中表的记录数，对于 InnoDB 存储引擎来说是一个估计值，0.2 指的是访问一条记录所需的成本常数

总成本 = I/O成本 + CPU成本 = 1252+88414 = 89666

综上所述，对于titles的全表扫描所需的总成本就是89666