MySQL查询优化

数据库的核心技术之一就是优化器，利用优化器，Oracle和MySQL都能对用户提交给它的SQL语句进行查询优化的处理，目的就是为了能让语句执行的效率更高。

技术社群的这篇文章《技术分享 | MySQL 查询优化》介绍了徐老师曾经分享过的MySQL查询优化的相关内容，值得学习借鉴。

内容分为如下几个方面：

MySQL 的优化器将 SQL 查询作为输入，并产生一个计划来执行该查询。
例如，应该按什么顺序连接表以及应该使用哪些索引。目标是能够从众多可能的执行计划中找到最佳的执行计划。

MySQL 使用基于成本的优化器。计划的成本大致反映了查询所需的资源，其中主要因素是计算查询时将要访问的行数。在优化过程中，优化器会选择连接顺序，决定使用哪个索引等等。同时，优化器还会根据从存储引擎获取的数据的统计信息作出决定。并且还依赖于数据字典中的元数据信息。

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

基于成本的查询优化器将为不同的操作分配成本，如表扫描和索引查找。

成本单位是从磁盘读取随机数据页的成本。所有其他成本数字都与此成本单位有关。基于这些成本，优化器将计算可供选择的计划的成本。最终它将选择成本最低的计划。

这是 MySQL 中成本模型的一个非常简单的视图。

作为输入，它需要基本操作，例如从表格读取数据或连接两个表格。作为输出，它产生对执行此操作的成本的估计。除了成本估算之外，它还会估算该操作产生的行数。成本模型由计算不同操作的成本和估计行数的公式组成。除了成本公式之外，成本模型还包含一组“成本常数”。这些是 MySQL 服务器在执行查询时执行的基本操作的成本。

成本模型使用来自数据字典的信息和来自存储引擎的统计信息来进行计算。主要的统计信息是表中的行数，对于索引，也获得基数，即有多少个不同的列值，以及索引范围中的行数。从数据字典中，我们使用关于行和索引的信息：如行和键的长度，唯一性以及列是否可以为空。
在 MySQL 5.7 中，已经配置了成本模型。成本常数存储在数据库表中，可以更改以更好地表示系统的特征。

使用这个模型，优化器在大多数情况下会选择一个最好的计划。但是，有时候优化器不能成功找到最佳计划。这可能是由于其决策所依据的数据不准确或由于成本模型本身的不准确性。

2. 监视、分析和查询优化的工具有哪些？

监视分析工具

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

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

5. 如何影响优化器

影响优化器

可以通过上图的方法对优化器进行影响，以达到预期效果。

MySQL 5.7 引入了优化器提示的新语法。

在 SELECT 之后，新的提示应该放在一个特殊的注释中。

这些提示中的许多提示都对应于 optimizer_switch 会话变量可以控制的功能。这些提示允许打开或关闭单个查询的功能，或者仅用于查询的某些表或索引。

MySQL 8.0，已经添加了提示来控制派生表和视图是否合并。

还添加了几个连接顺序提示。这些新的连接提示比现有的 STRAIGHT_JOIN 提示更加灵活，它要求 FROM 子句中的表按特定顺序列出。

MySQL 8.0 还可以在查询期间临时设置会话变量。

从 MySQL 5.7 开始，提供了一个插件，可用于在不更改应用程序的情况下重写有问题的查询。

查询可以在到达 MySQL 服务器时重写。

用户可以使用这个插件添加提示，修改连接顺序等等。

在这张幻灯片的例子中，插入一条规则来将 FORCEINDEX 提示添加到匹配模式的查询中。与 performance schema 中使用的相同的查询摘要用于传入查询与重写规则的高效匹配。

这样，对于不符合任何重写规则的查询，应该几乎没有开销。

如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，

近期更新的文章：《mysqldump导出的SQL事务大小可以控制么？》
《MySQL远程登录提示Access denied的场景》《JDBC连接参数useCursorFetch的使用场景》《MySQL中索引创建错误的场景》
《MySQL的字符集转换操作场景》
近期的热文：《推荐一篇Oracle RAC Cache Fusion的经典论文》
《"红警"游戏开源代码带给我们的震撼》
文章分类和索引：《公众号1300篇文章分类和索引》