读《MySQL性能调优与架构设计》笔记之Query语句优化基本思路和原则

分手后的思念是犯贱 2022-08-06 11:14 106阅读 0赞

在分析如何优化MySQLQuery 之前，我们需要先了解一下Query 语句优化的基本思路和原则。一般来说，Query 语句的优化思路和原则主要提现在以下几个方面：

1. 优化更需要优化的Query；

2. 定位优化对象的性能瓶颈；

3. 明确的优化目标；

4. 从Explain 入手；

5. 多使用profile

6. 永远用小结果集驱动大的结果集；

7. 尽可能在索引中完成排序；

8. 只取出自己需要的Columns；

9. 仅仅使用最有效的过滤条件；

10. 尽可能避免复杂的Join 和子查询；

上面所列的几点信息，前面4 点可以理解为Query 优化的一个基本思路，后面部分则是我们优化中的基本原则。

下面我们先针对Query优化的基本思路做一些简单的分析，理解为什么我们的Query 优化到底该如何进行。

## 2.1. 优化更需要优化的Query ##

那什么样的Query是更需要优化呢？对于这个问题我们需要从对整个系统的影响来考虑。什么Query 的优化能给系统整体带来更大的收益，就更需要优化。一般来说，高并发低消耗（相对）的Query 对整个系统的影响远比低并发高消耗的Query 大。我们可以通过以下一个非常简单的案例分析来充分说明问题。

假设有一个Query每小时执行10000 次，每次需要20 个IO。另外一个Query 每小时执行10 次，每次需要20000 个IO。

我们先通过IO消耗方面来分析。可以看出，两个Query 每小时所消耗的IO 总数目是一样的，都是200000 IO/小时。假设我们优化第一个Query，从20 个IO 降低到18 个IO，也就是仅仅降低了2 个IO，则我们节省了2 \* 10000 = 20000 （IO/小时）。而如果希望通过优化第二个Query 达到相同的效果，

我们必须要让每个Query 减少20000 / 10 = 2000 IO。我想大家都会相信让第一个Query 节省2 个IO远比第二个Query 节省2000 个IO 来的容易。

其次，如果通过CPU 方面消耗的比较，原理和上面的完全一样。只要让第一个Query 稍微节省一小块资源，就可以让整个系统节省出一大块资源，尤其是在排序，分组这些对CPU 消耗比较多的操作中尤其突出。

## 2.2. 定位优化对象的性能瓶颈 ##

在拿到一条需要优化的Query之后，我们首先要判断出这个Query 的瓶颈到底是IO 还是CPU。到底是因为在数据访问消耗了太多的时间，还是在数据的运算（如分组排序等）方面花费了太多资源？

一般来说，在MySQL5.0 系列版本中，我们可以通过系统自带的PROFILING 功能很清楚的找出一个Query 的瓶颈所在。

## 2.3. 明确的优化目标 ##

一般来说，我们首先需要清楚的了解数据库目前的整体状态，同时也要清楚的知道数据库中与该Query 相关的数据库对象的各种信息，而且还要了解该Query 在整个应用系统中所实现的功能。了解了数据库整体状态，我们就能知道数据库所能承受的最大压力，也就清楚了我们能够接受的最悲观情况。把握了该Query 相关数据库对象的信息，我们就应该知道实现该Query 的消耗最理想情况下需要消耗多少资源，最糟糕又需要消耗多少资源。最后，通过该Query 所实现的功能点在整个应用系统中的重要地位，我们可以大概的分析出该Query 可以占用的系统资源比例，而且我们也能够知道该Query 的效率给客户带来的体验影响到底有多大。

当我们清楚了这些信息之后，我们基本可以得出该Query 应该满足的一个性能范围是怎样的，这也就是我们的优化目标范围，然后就是通过寻找相应的优化手段来解决问题了。如果该Query 实现的应用系统功能比较重要，我们就必须让目标更偏向于理想值一些，即使在其他某些方面作出一些让步与牺牲，比如调整schema 设计，调整索引组成等，可能都是需要的。而如果该Query 所实现的是一些并不是太关键的功能，那我们可以让目标更偏向悲观值一些，而尽量保证其他更重要的Query 的性能。这种时候，即使需要调整商业需求，减少功能实现，也不得不应该作出让步。

## 2.4. 从Explain 入手 ##

只有Explain才能告诉你，这个Query 在数据库中是以一个什么样的执行计划来实现的。但是，有一点我们必须清楚，Explain 只是用来获取一个Query 在当前状态的数据库中的执行计划，在优化动手之前，我们比需要根据优化目标在自己头脑中有一个清晰的目标执行计划。再借助Explain 来验证调整的结果是否满足自己预定的执行计划。对于不符合预期的执行计划需要不断分析Query 的写法和数据库对象的信息，继续调整尝试，直至得到预期的结果。

在了解了上面这些优化的基本思路之后，我们再来看看优化的几个基本原则。

## 2.5. 永远用小结果集驱动大的结果集 ##

很多人喜欢在优化SQL的时候说用小表驱动大表，个人认为这样的说法不太严谨。为什么？因为大表经过WHERE 条件过滤之后所返回的结果集并不一定就比小表所返回的结果集大，可能反而更小。在这种情况下如果仍然采用小表驱动大表，就会得到相反的性能效果。其实这样的结果也非常容易理解，在MySQL 中的Join，只有Nested Loop 一种Join 方式，也就是MySQL 的Join 都是通过嵌套循环来实现的。驱动结果集越大，所需要循环的此时就越多，那么被驱动表的访问次数自然也就越多，而每次访问被驱动表，即使需要的逻辑IO 很少，循环次数多了，总量自然也不可能很小，而且每次循环都不能避免的需要消耗CPU ，所以CPU 运算量也会跟着增加。所以，如果我们仅仅以表的大小来作为驱动表的判断依据，假若小表过滤后所剩下的结果集比大表多很多，结果就是需要的嵌套循环中带来更多的循环次数，反之，所需要的循环次数就会更少，总体IO 量和CPU 运算量也会少。

所以，在优化JoinQuery 的时候，最基本的原则就是“小结果集驱动大结果集”，通过这个原则来减少嵌套循环中的循环次数，达到减少IO 总量以及CPU 运算的次数。

## 2.6. 尽可能在索引中完成排序 ##

** **

## 2.7. 只取出自己需要的Columns ##

任何时候在Query 中都只取出自己需要的Columns，尤其是在需要排序的Query 中。为什么？对于任何Query，返回的数据都是需要通过网络数据包传回给客户端，如果取出的 Column 越多，需要传输的数据量自然会越大，不论是从网络带宽方面考虑还是从网络传输的缓冲区来看，都是一个浪费。

如果是需要排序的Query来说，影响就更大了。在MySQL 中存在两种排序算法，一种是在MySQL4.1 之前的老算法，实现方式是先将需要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在我们所设定的排序区（通过参数sort\_buffer\_size 设定）中进行排序，完成排序之后再次通过行指针信息取出所需要的Columns，也就是说这种算法需要访问两次数据。

第二种排序算法是从MySQL4.1版本开始使用的改进算法，一次性将所需要的Columns 全部取出，在排序区中进行排序后直接将数据返回给请求客户端。改进算法只需要访问一次数据，减少了大量的随机IO，极大的提高了带有

排序的Query 语句的效率。但是，这种改进后的排序算法需要一次性取出并缓存的数据比第一种算法要多很多，如果我们将并不需要的Columns 也取出来，就会极大的浪费排序过程所需要的内存。在MySQL4.1 之后的版本中，我们可以通过设置max\_length\_for\_sort\_data 参数大小来控制MySQL选择第一种排序算法还是第二种排序算法。当所取出的Columns 的单条记录总大于max\_length\_for\_sort\_data设置的大小的时候，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种优化后的算法。为了尽可能提高排序性能，我们自然是更希望使用第二种排序算法，所以在Query 中仅仅取出我们所需要的Columns 是非常有必要的。

## 2.8. 仅仅使用最有效的过滤条件 ##

很多人在优化Query语句的时候很容易进入一个误区，那就是觉得WHERE 子句中的过滤条件越多越好，实际上这并不是一个非常正确的选择。其实我们分析Query 语句的性能优劣最关键的就是要让他选择一条最佳的数据访问路径，如何做到通过访问最少的数据量完成自己的任务。

为什么说过滤条件多不一定是好事呢？请看下面示例：

需求： 查找某个用户在所有group中所发的讨论message 基本信息。

场景：    1、知道用户ID和用户nick\_name

2、信息所在表为group\_message

3、group\_message 中存在用户ID(user\_id)和nick\_name(author)两个索引

方案一：将用户ID和用户nick\_name 两者都作为过滤条件放在WHERE 子句中来查询，Query 的执行计划如下：

> **EXPLAIN SELECT\* FROM** group\_message

\-> **WHERE** user\_id = 1 **AND**author='1111111111'\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type:SIMPLE

table:group\_message

type: ref

possible\_keys:group\_message\_author\_ind,group\_message\_uid\_ind

key:group\_message\_author\_ind

key\_len: 98

ref: const

rows: 1

Extra: Usingwhere

1 row in set(0.00 sec)

方案二：仅仅将用户ID作为过滤条件放在WHERE 子句中来查询，Query 的执行计划如下：

> **EXPLAIN SELECT\* FROM** group\_message

\-> **WHERE** user\_id= 1\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type:SIMPLE

table:group\_message

type: ref

possible\_keys:group\_message\_uid\_ind

key:group\_message\_uid\_ind

key\_len: 4

ref: const

rows: 1

Extra:

1 row in set(0.00 sec)

方案三：仅将用户nick\_name作为过滤条件放在WHERE 子句中来查询，Query 的执行计划如下：

> **EXPLAIN SELECT\* FROM** group\_message

\-> **WHERE**author = '1111111111'\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type:SIMPLE

table:group\_message

type: ref

possible\_keys:group\_message\_author\_ind

key:group\_message\_author\_ind

key\_len: 98

ref: const

rows: 1

Extra: Usingwhere

1 row in set (0.00 sec)

初略一看三个执行计划好像都挺好的啊，每一个Query 的执行类型都利用到了索引，而且都是“ref”类型。可是仔细一分析，就会发现，group\_message\_uid\_ind 索引的索引键长度为4（key\_len:4），由于user\_id字段类型为int，所以我们可以判定出Query Optimizer 给出的这个索引键长度是完全准确的。而group\_message\_author\_ind 索引的索引键长度为98（key\_len:98），因为author 字段定义为varchar(32) ，而所使用的字符集是utf8，32 \* 3 + 2 = 98。而且，由于user\_id 与author（来源于nick\_name）全部都是一一对应的，所以同一个user\_id 有哪些记录，那么所对应的author 也会有完全相同的记录。所以，同样的数据在group\_message\_author\_ind 索引中所占用的存储空间要远远大于group\_message\_uid\_ind 索引所占用的空间。占用空间更大，代表我们访问该索引所需要读取的数据量就会更多。所以，选择group\_message\_uid\_ind 的执行计划才是最优的执行计划。也就是说，上面的方案二才是最优方案，而使用了更多的WHERE 条件的方案一反而没有仅仅使用user\_id一个过滤条件的方案一优。

可能有些人会说，那如果将user\_id 和author 两者建立联合索引呢？告诉你，效果可能比没有这个索引的时候更差，因为这个联合索引的索引键更长，索引占用的空间将会更大。

这个示例并不一定能代表所有场景，仅仅是希望让大家明白，并不是任何时候都是使用的过滤条件越多性能会越好。在实际应用场景中，肯定会存在更多更复杂的情形，怎样使我们的Query 有一个更优化的执行计划，更高效的性能，还需要靠大家仔细分析各种执行计划的具体差别，才能选择出更优化的Query。

## 2.9. 尽可能避免复杂的Join 和子查询 ##