MySQL性能调优（四）：MySQL的执行原理（MySQL的查询重写规则）

最新推荐文章于 2025-04-24 17:21:13 发布

道友老李

最新推荐文章于 2025-04-24 17:21:13 发布

阅读量6.3k

点赞数 33

分类专栏：架构师进阶-性能调优文章标签： mysql 数据库

本文链接：https://blog.csdn.net/u014608435/article/details/147244660

版权

架构师进阶-性能调优专栏收录该内容

21 篇文章

订阅专栏

文章目录

MySQL性能调优
MySQL的执行原理
- 1.2.MySQL的查询重写规则

在这里插入图片描述
个人主页：道友老李
欢迎加入社区：道友老李的学习社区

MySQL性能调优

MySQL 性能调优是一个复杂且多维度的过程，下面从数据库设计、查询优化、配置参数调整、硬件优化几个方面为你介绍相关的调优方法。

数据库设计优化

合理设计表结构：确保表结构遵循数据库设计范式，减少数据冗余，同时要根据实际业务需求灵活调整，避免过度范式化导致的查询复杂度过高。
选择合适的数据类型：使用合适的数据类型可以减少存储空间，提高查询性能。例如，对于固定长度的字符串使用CHAR，对于可变长度的字符串使用VARCHAR；对于整数类型，根据取值范围选择合适的类型，如TINYINT、SMALLINT等。
建立适当的索引：索引可以加快数据的查找速度，但过多的索引会增加写操作的开销，因此需要根据查询需求建立适当的索引。例如，对于经常用于WHERE子句、JOIN条件和ORDER BY子句的列，可以考虑创建索引。

查询优化

避免全表扫描：尽量使用索引来避免全表扫描，例如在WHERE子句中使用索引列进行过滤。
优化子查询：子查询可能会导致性能问题，可以考虑使用JOIN来替代子查询。
减少不必要的列：在查询时只选择需要的列，避免使用SELECT *。

配置参数调整

调整内存分配：根据服务器的硬件资源和业务需求，调整innodb_buffer_pool_size、key_buffer_size等参数，以提高缓存命中率。
调整日志参数：根据业务需求调整log_bin、innodb_log_file_size等参数，以平衡数据安全性和性能。

硬件优化

使用高速存储设备：如 SSD 可以显著提高磁盘 I/O 性能。
增加内存：足够的内存可以减少磁盘 I/O，提高查询性能。

MySQL的执行原理

1.2.MySQL的查询重写规则

对于一些执行起来十分耗费性能的语句，MySQL还是依据一些规则，竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以被称作查询重写。

1.2.1.条件化简

我们编写的查询语句的搜索条件本质上是一个表达式，这些表达式可能比较繁杂，或者不能高效的执行，MySQL的查询优化器会为我们简化这些表达式。

1.2.1.1.移除不必要的括号

有时候表达式里有许多无用的括号，比如这样：

((a = 5 AND b =c) OR ((a > c) AND (c < 5)))

看着就很烦，优化器会把那些用不到的括号给干掉，就是这样：

(a = 5 and b =c) OR (a > c AND c < 5)

1.2.1.2.常量传递（constant_propagation）

有时候某个表达式是某个列和某个常量做等值匹配，比如这样：

a = 5

当这个表达式和其他涉及列a的表达式使用AND连接起来时，可以将其他表达式中的a的值替换为5，比如这样：

a = 5 AND b >a

就可以被转换为：

a = 5 AND b >5

等值传递（equality_propagation）

有时候多个列之间存在等值匹配的关系，比如这样：

a = b and b = c and c = 5

这个表达式可以被简化为：

a = 5 and b = 5 and c = 5

1.2.1.3.移除没用的条件（trivial_condition_removal）

对于一些明显永远为TRUE或者FALSE的表达式，优化器会移除掉它们，比如这个表达式：

(a < 1 and b= b) OR (a = 6 OR 5 != 5)

很明显，b = b这个表达式永远为TRUE，5 != 5这个表达式永远为FALSE，所以简化后的表达式就是这样的：

(a < 1 and TRUE) OR (a = 6 OR FALSE)

可以继续被简化为

a < 1 OR a =6

1.2.1.4.表达式计算

在查询开始执行之前，如果表达式中只包含常量的话，它的值会被先计算出来，比如这个：

a = 5 + 1

因为5 + 1这个表达式只包含常量，所以就会被化简成：

a = 6

但是这里需要注意的是，如果某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中，出现在某个更复杂表达式中，就像这样：

ABS(a) > 5

或者：

-a < -8

优化器是不会尝试对这些表达式进行化简的。我们前边说过只有搜索条件中索引列和常数使用某些运算符连接起来才可能使用到索引，所以如果可以的话，最好让索引列以单独的形式出现在表达式中。

1.2.1.5.常量表检测

MySQL觉得下边这种查询运行的特别快：

使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表。

MySQL觉得这两种查询花费的时间特别少，少到可以忽略，所以也把通过这两种方式查询的表称之为常量表（英文名：constant tables）。优化器在分析一个查询语句时，先首先执行常量表查询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本，比方说这个查询语句：

SELECT
	*
FROM
	table1
INNER JOIN table2 ON table1.column1 = table2.column2
WHERE
	table1.primary_key = 1;

很明显，这个查询可以使用主键和常量值的等值匹配来查询table1表，也就是在这个查询中table1表相当于常量表，在分析对table2表的查询成本之前，就会执行对table1表的查询，并把查询中涉及table1表的条件都替换掉，也就是上边的语句会被转换成这样：

SELECT
	table1表记录的各个字段的常量值,
	table2.*
FROM
	table1
INNER JOIN table2 ON table1表column1列的常量值 = table2.column2;

1.2.2.外连接消除

我们前边说过，内连接的驱动表和被驱动表的位置可以相互转换，而左（外）连接和右（外）连接的驱动表和被驱动表是固定的。这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。

我们之前说过，外连接和内连接的本质区别就是：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充；而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃。查询效果就是这样：

SELECT * FROM e1 INNER JOIN e2 ON e1.m1 = e2.m2;

SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2;

对于上边例子中的（左）外连接来说，由于驱动表e1中m1=1, n1='a’的记录无法在被驱动表e2中找到符合ON子句条件e1.m1 = e2.m2的记录，所以就直接把这条记录加入到结果集，对应的e2表的m2和n2列的值都设置为NULL。

因为凡是不符合WHERE子句中条件的记录都不会参与连接。只要我们在搜索条件中指定关于被驱动表相关列的值不为NULL，那么外连接中在被驱动表中找不到符合ON子句条件的驱动表记录也就被排除出最后的结果集了，也就是说：在这种情况下：外连接和内连接也就没有什么区别了！

另外再说下这个查询：

SELECT* FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2 WHERE e2.n2 IS NOT NULL

由于指定了被驱动表e2的n2列不允许为NULL，所以上边的e1和e2表的左（外）连接查询和内连接查询是一样的。当然，我们也可以不用显式的指定被驱动表的某个列IS NOT NULL，只要隐含的有这个意思就行了，比方说这样：

SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2 WHERE e2.m2 = 2

在这个例子中，我们在WHERE子句中指定了被驱动表e2的m2列等于2，也就相当于间接的指定了m2列不为NULL值，所以上边的这个左（外）连接查询其实和下边这个内连接查询是等价的：

SELECT* FROM e1 INNER JOIN e2 ON e1.m1 = e2.m2 WHERE e2.m2 = 2

我们把这种在外连接查询中，指定的WHERE子句中包含被驱动表中的列不为NULL值的条件称之为空值拒绝（英文名：reject-NULL）。在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。

1.2.3.子查询优化

1.2.3.1.子查询语法

在一个查询语句A里的某个位置也可以有另一个查询语句B，这个出现在A语句的某个位置中的查询B就被称为子查询，A也被称之为外层查询。子查询可以在一个外层查询的各种位置出现，比如：

1）SELECT子句中

也就是我们平时说的查询列表中，比如这样：

SELECT(SELECT m1 FROM e1 LIMIT 1);

其中的(SELECT m1 FROM e1LIMIT 1)就是子查询。

2）FROM子句中

SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM e2 WHERE m2 > 2) AS t;

这个例子中的子查询是：(SELECT m2+1 AS m, n2 AS n FROM e2 WHERE m2 > 2)

这里可以把子查询的查询结果当作是一个表，子查询后边的AS t表明这个子查询的结果就相当于一个名称为t的表，这个名叫t的表的列就是子查询结果中的列，比如例子中表t就有两个列：m列和n列。

这个放在FROM子句中的子查询本质上相当于一个表，但又和我们平常使用的表有点儿不一样，MySQL把这种由子查询结果集组成的表称之为 派生表 。

3）WHERE或ON子句中

把子查询放在外层查询的WHERE子句或者ON子句中可能是我们最常用的一种使用子查询的方式了，比如这样：

SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2)

这个查询表明我们想要将(SELECT m2FROM e2)这个子查询的结果作为外层查询的IN语句参数，整个查询语句的意思就是我们想找e1表中的某些记录，这些记录的m1列的值能在e2表的m2列找到匹配的值。

4）ORDER BY子句、GROUP BY子句中

虽然语法支持，但没啥意义。

还有一些其他的子查询，这里不一一列举

1.2.3.2.子查询在MySQL中是怎么执行的

想象中子查询的执行方式是这样的：

如果该子查询是不相关子查询，比如下边这个查询：

SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2);

先单独执行(SELECTorder_note FROM s2)这个子查询。

然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询

最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。

但真的是这样吗？其实MySQL用了一系列的办法来优化子查询的执行，大部分情况下这些优化措施其实挺有效的，下边我们来看看各种不同类型的子查询具体是怎么执行的。

不同的子查询

1）按返回的结果集区分子查询

因为子查询本身也算是一个查询，所以可以按照它们返回的不同结果集类型而把这些子查询分为不同的类型：

1.1)标量子查询

那些只返回一个单一值的子查询称之为标量子查询，比如这样：

SELECT (SELECT m1 FROM e1 LIMIT 1);

或者这样：

SELECT * FROM e1 WHERE m1 = (SELECT MIN(m2) FROM e2);

SELECT * FROM e1 WHERE m1 < (SELECT MIN(m2) FROM e2);

这两个查询语句中的子查询都返回一个单一的值，也就是一个标量。这些标量子查询可以作为一个单一值或者表达式的一部分出现在查询语句的各个地方。

1.2)行子查询

顾名思义，就是返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）。比如这样：

SELECT * FROM e1 WHERE (m1, n1) = (SELECT m2, n2 FROM e2 LIMIT 1);

其中的(SELECT m2, n2 FROM e2 LIMIT 1)就是一个行子查询，整条语句的含义就是要从e1表中找一些记录，这些记录的m1和n1列分别等于子查询结果中的m2和n2列。

1.3)列子查询

列子查询自然就是查询出一个列的数据，不过这个列的数据需要包含多条记录（只包含一条记录就成了标量子查询了）。比如这样：

SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2);

其中的(SELECT m2 FROM e2)就是一个列子查询，表明查询出e2表的m2列的值作为外层查询IN语句的参数。

1.4)表子查询

顾名思义，就是子查询的结果既包含很多条记录，又包含很多个列，比如这样：

SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);

其中的(SELECT m2, n2 FROM e2)就是一个表子查询，

这里需要和行子查询对比一下，行子查询中我们用了LIMIT 1来保证子查询的结果只有一条记录，表子查询中不需要这个限制。

2)按与外层查询关系来区分子查询

2.1)不相关子查询

如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询。我们前边介绍的那些子查询全部都可以看作不相关子查询。

2.2)相关子查询

如果子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之为相关子查询。比如：

SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2 WHERE n1 = n2);

例子中的子查询是(SELECT m2 FROM e2 WHERE n1 = n2)，

可是这个查询中有一个搜索条件是n1 = n2，别忘了n1是表e1的列，也就是外层查询的列，也就是说子查询的执行需要依赖于外层查询的值，所以这个子查询就是一个相关子查询。

3) [NOT] IN/ANY/SOME/ALL子查询

对于列子查询和表子查询来说，它们的结果集中包含很多条记录，这些记录相当于是一个集合，所以就不能单纯的和另外一个操作数使用操作符来组成布尔表达式了，MySQL通过下面的语法来支持某个操作数和一个集合组成一个布尔表达式：

3.1)IN或者NOT IN

具体的语法形式如下：

操作数 [NOT] IN (子查询)

这个布尔表达式的意思是用来判断某个操作数在不在由子查询结果集组成的集合中，比如下边的查询的意思是找出e1表中的某些记录，这些记录存在于子查询的结果集中：

SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);

3.2) ANY/SOME（ANY和SOME是同义词）

具体的语法形式如下：

操作数比较符 ANY/SOME(子查询)

这个布尔表达式的意思是只要子查询结果集中存在某个值和给定的操作数做比较操作，比较结果为TRUE，那么整个表达式的结果就为TRUE，否则整个表达式的结果就为FALSE。比方说下边这个查询：

SELECT * FROM e1 WHERE m1 > ANY(SELECT m2 FROM e2);

这个查询的意思就是对于e1表的某条记录的m1列的值来说

如果子查询(SELECTm2 FROM e2)的结果集中存在一个小于m1列的值，那么整个布尔表达式的值就是TRUE，

否则为FALSE，也就是说只要m1列的值大于子查询结果集中最小的值，整个表达式的结果就是TRUE，所以上边的查询本质上等价于这个查询：

SELECT * FROM e1 WHERE m1 > (SELECT MIN(m2) FROM e2);

另外，=ANY相当于判断子查询结果集中是否存在某个值和给定的操作数相等，它的含义和IN是相同的。

3.3)ALL具体的语法形式如下：

操作数比较操作 ALL(子查询)

这个布尔表达式的意思是子查询结果集中所有的值和给定的操作数做比较操作比较结果为TRUE，那么整个表达式的结果就为TRUE，否则整个表达式的结果就为FALSE。比方说下边这个查询：

SELECT * FROM e1 WHERE m1 > ALL(SELECT m2 FROM e2);

这个查询的意思就是对于e1表的某条记录的m1列的值来说

如果子查询(SELECT m2 FROM e2)的结果集中的所有值都小于m1列的值，那么整个布尔表达式的值就是TRUE，否则为FALSE，也就是说只要m1列的值大于子查询结果集中最大的值，整个表达式的结果就是TRUE，所以上边的查询本质上等价于这个查询：

SELECT * FROM e1 WHERE m1 > (SELECT MAX(m2) FROM e2);

3.4)EXISTS子查询

有的时候我们仅仅需要判断子查询的结果集中是否有记录，而不在乎它的记录具体是个啥，可以使用把EXISTS或者NOT EXISTS放在子查询语句前边，就像这样：

SELECT * FROM e1 WHERE EXISTS (SELECT 1 FROM e2);

对于子查询(SELECT 1 FROM e2)来说，我们并不关心这个子查询最后到底查询出的结果是什么，所以查询列表里填*、某个列名，或者其他啥东西都无所谓，我们真正关心的是子查询的结果集中是否存在记录。也就是说只要(SELECT 1 FROM e2)这个查询中有记录，那么整个EXISTS表达式的结果就为TRUE。

1.2.3.3.MySQL对IN子查询的优化

1）标量子查询、行子查询的执行方式

对于不相关标量子查询或者行子查询来说，它们的执行方式很简单，比方说下边这个查询语句：

SELECT * FROM s1 WHERE order_note = (SELECT order_note FROM s2 WHERE key3 = 'a' LIMIT 1);

它的执行方式和我们前面想象的一样：

先单独执行(SELECT order_note FROM s2 WHERE key3 = ‘a’ LIMIT 1)这个子查询。

然后在将上一步子查询得到的结果当作外层查询的参数，

再执行外层查询SELECT * FROM s1 WHERE order_note= …。

也就是说，对于包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别独立的执行外层查询和子查询，就当作两个单表查询就好了。

对于相关的标量子查询或者行子查询来说，比如下边这个查询：

SELECT * FROM s1 WHERE
order_note = (SELECT order_note FROM s2 WHERE s1.order_no= s2.order_no LIMIT 1);

事情也和我们前面想象的一样，它的执行方式就是这样的：

先从外层查询中获取一条记录，本例中也就是先从s1表中获取一条记录。

然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从s1表中获取的那条记录中找出s1.order_no列的值，然后执行子查询。

最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。

再次执行第一步，获取第二条外层查询中的记录，依次类推。

也就是说对于两种使用标量子查询以及行子查询的场景中，MySQL优化器的执行方式并没有什么新鲜的。

2)物化表

对于不相关的IN子查询，比如这样：

SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE order_no = 'a');

我们最开始的感觉就是这种不相关的IN子查询和不相关的标量子查询或者行子查询是一样一样的，都是把外层查询和子查询当作两个独立的单表查询来对待。但是MySQL为了优化IN子查询下了很大力气，所以整个执行过程并不像我们想象的那么简单。

对于不相关的IN子查询来说，如果子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单独的单表查询效率很高，但是如果单独执行子查询后的结果集太多的话，就会导致这些问题：

1、结果集太多，可能内存中都放不下。

2、对于外层查询来说，如果子查询的结果集太多，那就意味着IN子句中的参数特别多，这就导致：无法有效的使用索引，只能对外层查询进行全表扫描。

在对外层查询执行全表扫描时，由于IN子句中的参数太多，这会导致检测一条记录是否符合和IN子句中的参数匹配花费的时间太长。

比如说IN子句中的参数只有两个：

SELECT * FROM tbl_name WHERE column IN (a, b);

这样相当于需要对tbl_name表中的每条记录判断一下它的column列是否符合column = a OR column = b。

在IN子句中的参数比较少时这并不是什么问题，如果IN子句中的参数比较多时，比如这样：

SELECT * FROM tbl_name WHERE column IN (a, b, c ..., ...);

那么这样每条记录需要判断一下它的column列是否符合column =a OR column = b OR column = c OR …，这样性能耗费可就多了。

MySQL的改进是不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。写入临时表的过程是这样的：

1、该临时表的列就是子查询结果集中的列。

2、写入临时表的记录会被去重，临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引。

一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引。

如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。

MySQL把这个将子查询结果集中的记录保存到临时表的过程称之为物化（英文名：Materialize）。为了方便起见，我们就把那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

3)物化表转连接

事情到这就完了？我们还得重新审视一下最开始的那个查询语句：

SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE order_no = 'a');

当我们把子查询进行物化之后，假设子查询物化表的名称为materialized_table，该物化表存储的子查询结果集的列为m_val，那么这个查询

就相当于表s1和子查询物化表materialized_table进行内连接：

SELECT s1.* FROM s1 INNER JOIN materialized_table ON order_note = m_val;

转化成内连接之后就有意思了，查询优化器可以评估不同连接顺序需要的成本是多少，选取成本最低的那种查询方式执行查询。

我们分析一下上述查询中使用外层查询的表s1和物化表materialized_table进行内连接的成本都是由哪几部分组成的：

1、如果使用s1表作为驱动表的话，总查询成本由下边几个部分组成：

物化子查询时需要的成本
扫描s1表时的成本

s1表中的记录数量 × 通过m_val = xxx对materialized_table表进行单表访问的成本（我们前边说过物化表中的记录是不重复的，并且为物化表中的列建立了索引，所以这个步骤显然是非常快的）。

2、如果使用materialized_table表作为驱动表的话，总查询成本由下边几个部分组成：

物化子查询时需要的成本
扫描物化表时的成本

物化表中的记录数量 × 通过order_note= xxx对s1表进行单表访问的成本（如果order_note列上建立了索引，这个步骤还是非常快的）。

MySQL查询优化器会通过运算来选择上述成本更低的方案来执行查询。