MySQL查询优化

MySQL解决方案工程师 2019-09-02

1274

上周在MySQL团队的网络研讨会上，分享了MySQL查询优化相关内容，现在把内容整理一下，供各位参考。

内容分为如下几个方面：

基于成本的MySQL查询优化。
监视、分析和查询优化的工具有哪些？
数据访问方法与如何选择索引。
如何对连接进行优化。
如何影响优化器。

接下来，简明介绍一下相关内容，网络研讨会的视频附在后面。

基于成本的MySQL查询优化：

MySQL的优化器将SQL查询作为输入，并产生一个计划来执行该查询。
例如，应该按什么顺序连接表以及应该使用哪些索引。目标是能够从众多可能的执行计划中找到最佳的执行计划。
MySQL使用基于成本的优化器。计划的成本大致反映了查询所需的资源，其中主要因素是计算查询时将要访问的行数。在优化过程中，优化器会选择连接顺序，决定使用哪个索引等等。同时，优化器还会根据从存储引擎获取的数据的统计信息作出决定。并且还依赖于数据字典中的元数据信息。

因此，查询优化的总体思路如上图所示。

基于成本的查询优化器将为不同的操作分配成本，如表扫描和索引查找。
成本单位是从磁盘读取随机数据页的成本。所有其他成本数字都与此成本单位有关。基于这些成本，优化器将计算可供选择的计划的成本。最终它将选择成本最低的计划。

这是MySQL中成本模型的一个非常简单的视图。
作为输入，它需要基本操作，例如从表格读取数据或连接两个表格。作为输出，它产生对执行此操作的成本的估计。除了成本估算之外，它还会估算该操作产生的行数。成本模型由计算不同操作的成本和估计行数的公式组成。除了成本公式之外，成本模型还包含一组“成本常数”。这些是MySQL服务器在执行查询时执行的基本操作的成本。
成本模型使用来自数据字典的信息和来自存储引擎的统计信息来进行计算。主要的统计信息是表中的行数，对于索引，也获得基数，即有多少个不同的列值，以及索引范围中的行数。从数据字典中，我们使用关于行和索引的信息：如行和键的长度，唯一性以及列是否可以为空。
在MySQL 5.7中，已经配置了成本模型。成本常数存储在数据库表中，可以更改以更好地表示系统的特征。
使用这个模型，优化器在大多数情况下会选择一个最好的计划。但是，有时候优化器不能成功找到最佳计划。这可能是由于其决策所依据的数据不准确或由于成本模型本身的不准确性。

监视分析工具

了解MySQL的优化器原理之后，我们来通过一些工具来对MySQL的性能进行监视分析。工具包括如下：

MySQL企业版监控器的查询分析器提供了一个概览，使用户能够快速识别开销大的查询。用户还可以对单个查询进行深入研究，以查看关于查询的细粒度统计信息。

Performance Schema有大量的表与性能相关的数据。

events_statements_history表提供最近执行的查询的统计信息。

events_statements_summary_by_digest表中汇总类似查询的数据。

file_summary_by_event_name表包含获取文件I/ O的统计信息。

table_io_waits_summary表提供有关表和索引使用情况的统计信息。

语句事件表提供有关最近运行的查询的信息。
events_statements_current表将显示每个连接或线程的当前语句。
历史表默认给出每个线程最近的10条语句，
history_long表给出了全部10000条最新的语句。
历史记录表的大小是可配置的。
已经用红色标出了这些表格中的最有用的信息。
例如，TIMER_WAIT给出了执行查询需要多长时间。单位是皮秒。
ROWS_SENT是结果中的行数，而ROWS_EXAMINED是执行查询时必须访问的行数。
ROWS_EXAMINED比ROWS_SENT大得多，通常表明查询需要优化。
CREATED_TMP_DISK_TABLES告诉查询是否创建了磁盘上的临时表。

语句摘要是将类似查询分组在一起的一种方法。

在这里你可以看到两个查询的例子，这两个查询的区别，仅在于使用的常量。这样的查询将得到相同的摘要。摘要计算也会忽略空白和数值。

events_statements_summary_by_digest表包含每个摘要汇总的统计信息。

DIGEST_TEXT提供规范化的查询文本，COUNT_STAR是执行此类型查询的次数。

SUM_TIMER_WAIT是执行此查询所用的总时间，AVG_TIMER_WAIT是每次执行的平均时间。大多数其他列是各个查询的值的总和。

在调查最近的性能退化时，FIRST_SEEN列对于查找可能导致问题的新查询非常有用。

Performance schema非常庞大，有很多细节。因此，MySQL团队开发了sys_schema，这是一个视图，存储过程和函数的集合，旨在使访问Performanceschema数据变得更加容易。

EXPLAIN有三种格式，表格，JSON及可视化。通过使用EXPLAIN可以获取查询的执行计划。
结构化的EXPLAIN以JSON格式提供查询计划，可以通过在EXPLAIN之后指定FORMAT= JSON来获得此信息。使用JSON可以更容易地显示查询的结构。

MySQL Workbench使用结构化EXPLAIN的输出来优化查询计划。

上图按从左到右的连接顺序显示表。红色框表示全表扫描，而绿色框表示使用索引查找。对于每个表，显示使用的索引。还要注意的是，每个表格的框上方是每个表访问所发现的行数的估计值以及访问该表的成本。

如果您想更深入地了解为什么选择某个查询计划，那么优化器跟踪非常有用。虽然EXPLAIN显示选定的计划，但优化程序跟踪显示为什么选择计划：

您将能够看到替代计划，估计成本以及做出的决策。跟踪采用JSON格式。

要打开optimizer_trace，可以在optimizer_trace会话变量中设置enabled标志。解释或运行查询后，可以从informationschema中的optimizer_trace表中获取跟踪。通常会将跟踪转存到文件以供进一步检查。

optimizer_trace表有3个其他列，查询的文本，

Missing_bytes_beyond_max_mem_size。如果这不是零，则需要增加用于捕获跟踪的内存缓冲区的大小。

insufficient_privileges。如果不为零，则表示用户缺乏查看有关查询或查询中使用的存储例程的信息的权限。

数据访问方法

选择访问方法的目标是为查询中的每个表找到读取所需数据的最佳方式。

对于查询的每个表，我们执行以下操作：

检查访问方法是否有用，估计使用该访问方法的成本，以及选择成本最低的访问方法。

关于访问方法的详细内容请参看视频。

连接的优化

连接优化器的作用是找到连接表的最佳顺序。

我们的连接优化器使用“贪婪搜索策略”来计算所有可能的连接顺序。

这是以深度优先的方式完成的。为了缩小范围，将会删去比迄今为止发现的最佳计划花费更多的部分计划。为了使这种删去更高效，首先会对大小和键依赖关系进行排序。这样增加了尽早找到一个好计划的机会，以便更多的计划将被删去。连接优化器默认也会删去一些不那么有希望的计划，而这些计划不一定比目前的最佳计划花费更多。在某些情况下，可能会因此而错过最佳计划。探索式删去可以通过将optimizer_prune_level设置为0来关闭。