MySQL中的查询优化器怎么用

共计 6414 个字符，预计需要花费 17 分钟才能阅读完成。

本篇内容主要讲解“MySQL 中的查询优化器怎么用”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“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

二、优化器原则

1、常量传递（constant_propagation）
a = 1 AND b a
上面这个 sql 可以转换为：
a = 1 AND b 1

2、等值传递（equality_propagation）
a = b and b = c and c = 5
上面这个 sql 可以转换为：
a = 5 and b = 5 and c = 5

3、移除没用的条件（trivial_condition_removal）
a = 1 and 1 = 1
上面这个 sql 可以转换为：
a = 1

4、基于成本
一个查询可以有不同的执行方案，可以选择某个索引进行查询，也可以选择全表扫描，查询优化器会选择其中成本最低的方案去执行查询。
1）I/ O 成本
InnoDB 存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为 I / O 成本
2）CPU 成本
读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为 CPU 成本。
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。

综上所述，上边的查询语句可能用到的索引，也就是 possible keys 只有 PRIMARY 和 idx_titles_to_date。

2、计算全表扫描的代价

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

1）聚簇索引占用的页面数

2）该表中的记录数

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

SHOW TABLE STATUS LIKE  titles

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

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

Data_length =  聚簇索引的页面数量  x  每个页面的大小

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

聚簇索引的页面数量  = Data_length ÷ 16 ÷ 1024 = 20512768 ÷ 16 ÷ 1024 = 1252

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

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

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

总成本：1252+88414 = 89666。

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

我们前边说过表中的记录其实都存储在聚簇索引对应 B + 树的叶子节点中，所以只要我们通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组成的双向链表把所有记录都查看一遍。也就是说全表扫描这个过程其实有的 B + 树内节点是不需要访问的，但是 MySQL 在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算 I / O 成本的依据，是不区分内节点和叶子节点的。

3、计算 PRIMARY 需要成本

计算 PRIMARY 需要多少成本的关键问题是：需要预估出根据对应的 where 条件在主键索引 B + 树中存在多少条符合条件的记录。

范围区间数
当我们从索引中查询记录时，不管是 =、in、、这些操作都需要从索引中确定一个范围，不论这个范围区间的索引到底占用了多少页面，查询优化器粗暴的认为读取索引的一个范围区间的 I / O 成本和读取一个页面是相同的。
本例中使用 PRIMARY 的范围区间只有一个：(10101, 20000)，所以相当于访问这个范围区间的索引付出的 I / O 成本就是：1 x 1.0 = 1.0

预估范围内的记录数
优化器需要计算索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算 PRIMARY 在(10101, 20000) 这个范围区间中包含多少条数据记录，计算过程是这样的：

步骤 1：先根据 emp_no 10101 这个条件访问一下 PRIMARY 对应的 B + 树索引，找到满足 emp_no 10101 这个条件的第一条记录，我们把这条记录称之为区间最左记录。

步骤 2：然后再根据 emp_no 20000 这个条件继续从 PRIMARY 对应的 B + 树索引中找出第一条满足这个条件的记录，我们把这条记录称之为区间最右记录。

步骤 3：如果区间最左记录和区间最右记录相隔不太远（只要相隔不大于 10 个页面即可），那就可以精确统计出满足 emp_no 10101 and emp_no 20000 条件的记录条数。否则只沿着区间最左记录向右读 10 个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。那么问题又来了，怎么估计区间最左记录和区间最右记录之间有多少个页面呢？计算它们父节点中对应的目录项记录之间隔着几条记录就可以了。

根据上面的步骤可以算出来 PRIMARY 索引的记录条数，所以读取记录的 CPU 成本为：26808*0.2=5361.6，其中 26808 是预估的需要读取的数据记录条数，0.2 是读取一条记录成本常数。

PRIMARY 的总成本
确定访问的 IO 成本 + 过滤数据的 CPU 成本 =1+5361.6=5362.6

4、计算 idx_titles_to_date 需要成本

因为通过二级索引查询需要回表，所以在计算二级索引需要成本时还要加上回表的成本，而回表的成本就相当于下面这个 SQL 执行：

select * from employees.titles where  主键字段  in （主键值 1，主键值 2，。。。，主键值 3）;

所以 idx_titles_to_date 的成本 = 辅助索引的查询成本 + 回表查询的成本

5、比较各成本选出最优者

选择成本最小的索引

四、基于索引统计数据的成本计算

有时候使用索引执行查询时会有许多单点区间，比如使用 IN 语句就很容易产生非常多的单点区间，比如下边这个查询：

select * from employees.titles where to_date in (a , b , c , d , ...,  e

很显然，这个查询可能使用到的索引就是 idx_titles_to_date，由于这个索引并不是唯一二级索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多少，需要我们去计算。计算方式我们上边已经介绍过了，就是先获取索引对应的 B + 树的区间最左记录和区间最右记录，然后再计算这两条记录之间有多少记录（记录条数少的时候可以做到精确计算，多的时候只能估算）。这种通过直接访问索引对应的 B + 树来计算某个范围区间对应的索引记录条数的方式称之为 index pe。

如果只有几个单点区间的话，使用 index pe 的方式去计算这些单点区间对应的记录数也不是什么问题，可是如果很多呢，比如有 20000 次，MySQL 的查询优化器为了计算这些单点区间对应的索引记录条数，要进行 20000 次 index pe 操作，那么这种情况下是很耗性能的，所以 MySQL 提供了一个系统变量 eq_range_index_pe_limit，我们看一下这个系统变量的默认值：SHOW VARIABLES LIKE‘%pe%’; 为 200。

也就是说如果我们的 IN 语句中的参数个数小于 200 个的话，将使用 index pe 的方式计算各个单点区间对应的记录条数，如果大于或等于 200 个的话，可就不能使用 index pe 了，要使用所谓的索引统计数据来进行估算。像会为每个表维护一份统计数据一样，MySQL 也会为表中的每一个索引维护一份统计数据，查看某个表中索引的统计数据可以使用 SHOW INDEX FROM 表名的语法。

Cardinality 属性表示索引列中不重复值的个数。比如对于一个一万行记录的表来说，某个索引列的 Cardinality 属性是 10000，那意味着该列中没有重复的值，如果 Cardinality 属性是 1 的话，就意味着该列的值全部是重复的。不过需要注意的是，对于 InnoDB 存储引擎来说，使用 SHOW INDEX 语句展示出来的某个索引列的 Cardinality 属性是一个估计值，并不是精确的。可以根据这个属性来估算 IN 语句中的参数所对应的记录数：

1）使用 SHOW TABLE STATUS 展示出的 Rows 值，也就是一个表中有多少条记录。

2）使用 SHOW INDEX 语句展示出的 Cardinality 属性。

3）根据上面两个值可以算出 idx_key1 索引对于的 key1 列平均单个值的重复次数：Rows/Cardinality

4）所以总共需要回表的记录数就是：IN 语句中的参数个数 *Rows/Cardinality。

NULL 值处理
上面知道在统计列不重复值的时候，会影响到查询优化器。
对于 NULL，有三种理解方式：

NULL 值代表一个未确定的值，每一个 NULL 值都是独一无二的，在统计列不重复值的时候应该都当作独立的。

NULL 值在业务上就是代表没有，所有的 NULL 值代表的意义是一样的，所以所有的 NULL 值都一样，在统计列不重复值的时候应该只算一个。

NULL 完全没有意义，在统计列不重复值的时候应该忽略 NULL。
innodb 提供了一个系统变量：

show global variables like  %innodb_stats_method%

这个变量有三个值：

nulls_equal：认为所有 NULL 值都是相等的。这个值也是 innodb_stats_method 的默认值。如果某个索引列中 NULL 值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。

nulls_unequal：认为所有 NULL 值都是不相等的。如果某个索引列中 NULL 值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别少，所以倾向于使用索引进行访问。

nulls_ignored：直接把 NULL 值忽略掉。

最好不在索引列中存放 NULL 值才是正解

五、统计数据

InnoDB 提供了两种存储统计数据的方式：

• 统计数据存储在磁盘上。

• 统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了。

MySQL 给我们提供了系统变量 innodb_stats_persistent 来控制到底采用哪种方式去存储统计数据。在 MySQL 5.6.6 之前，innodb_stats_persistent 的值默认是 OFF，也就是说 InnoDB 的统计数据默认是存储到内存的，之后的版本中 innodb_stats_persistent 的值默认是 ON，也就是统计数据默认被存储到磁盘中。

不过 InnoDB 默认是以表为单位来收集和存储统计数据的，也就是说我们可以把某些表的统计数据（以及该表的索引统计数据）存储在磁盘上，把另一些表的统计数据存储在内存中。我们可以在创建和修改表的时候通过指定 STATS_PERSISTENT 属性来指明该表的统计数据存储方式。

1、基于磁盘的永久性统计数据
当我们选择把某个表以及该表索引的统计数据存放到磁盘上时，实际上是把这些统计数据存储到了两个表里：
• innodb_table_stats 存储了关于表的统计数据，每一条记录对应着一个表的统计数据
• innodb_index_stats 存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据

2、定期更新统计数据
• 系统变量 innodb_stats_auto_recalc 决定着服务器是否自动重新计算统计数据，它的默认值是 ON，也就是该功能默认是开启的。每个表都维护了一个变量，该变量记录着对该表进行增删改的记录条数，如果发生变动的记录数量超过了表大小的 10%，并且自动重新计算统计数据的功能是打开的，那么服务器会重新进行一次统计数据的计算，并且更新 innodb_table_stats 和 innodb_index_stats 表。不过自动重新计算统计数据的过程是异步发生的，也就是即使表中变动的记录数超过了 10%，自动重新计算统计数据也不会立即发生，可能会延迟几秒才会进行计算。
•如果 innodb_stats_auto_recalc 系统变量的值为 OFF 的话，我们也可以手动调用 ANALYZE TABLE 语句来重新计算统计数据。ANALYZE TABLE single_table;

3、控制执行计划
Index Hints
•USE INDEX：限制索引的使用范围，在数据表里建立了很多索引，当 MySQL 对索引进行选择时，这些索引都在考虑的范围内。但有时我们希望 MySQL 只考虑几个索引，而不是全部的索引，这就需要用到 USE INDEX 对查询语句进行设置。
•IGNORE INDEX：限制不使用索引的范围
•FORCE INDEX：我们希望 MySQL 必须要使用某一个索引(由于 MySQL 在查询时只能使用一个索引，因此只能强迫 MySQL 使用一个索引)。这就需要使用 FORCE INDEX 来完成这个功能。
基本语法格式：

SELECT * FROM table1 USE|IGNORE|FORCE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3

到此，相信大家对“MySQL 中的查询优化器怎么用”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！