> For the complete documentation index, see [llms.txt](https://mysql.gitbook.teaho.net/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mysql.gitbook.teaho.net/cha-xun-xing-neng-you-hua.md).

# 查询性能优化

衡量查询真正重要的是响应时间。可把查询看成是一个任务，那么它由一系列的子任务组成，每个子任务都会消耗一定的时间。（ps. 可参考mysql的SHOW PROFILE命令）

在完成任务时，查询会在包括网络，CPU，生成统计信息和执行计划，锁等等消耗掉时间。

查询的生命周期大致可以按照顺序来看：从客户端，到服务端，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。

## 慢查询基础

查询性能低下最基本的原因是访问的数据太多。

对于低效查询，我们发现通过下面两个步骤来分析总是有效：

1. 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时也可能访问了太多的列。
2. 确认MySQL服务器层是否在分析大量超过需要的数据行。

对于MySQL，最简单的衡量查询开销的三个指标如下：

1. 响应时间
2. 扫描的行数
3. 返回的行数

没有那个指标能完美的衡量查询的开销，他们只能大致的反映。这三个指标都会记录到MySQL的**慢查询日志** 。

简单点点一些点：

响应时间是两部分之和：服务时间，排队时间。

在不同类型的应用压力和环境下，响应时间**没有什么一致**的规律和公式。既可能是响应时间慢造成了CPU等问题，也可能是CPU、资源竞争等造成的响应时间慢。具体问题具体分析。

书中有一个“快速上限估计”法来估算查询的响应时间，该查询时间是否合理。请参考：Tapio Lahdenmaki和Mike Leach编写的Relational Database Index Design and the Optimizers(Wiley出版社)一书中提到的技术。

## 重构查询的方式

### 一个复杂查询还是多个简单查询

在传统的实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总是认为网络通信、查询分析和优化是一件代价很高的事情。但是这样的想法对于MySQL并不适用：

1. MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。
2. 现代的网络速度比以前要快很多，无论是带宽还是延迟。在某些版本的MySQL上即使在一个通用的服务器上，也能够运行每秒超过10万的查询。

话虽如此，相比起来，MySQL每秒能处理内存成百万的数据，而响应数据到客户端要慢多了。当然，有时候是存在分解大查询的必要性的。

### 切分查询

![有效减缓数据库的压力](/files/-LIuRg1XRa9Xuu0hjrGM)

### 分解关联查询

![](/files/-LIuSwOKWUb7QucIxPi7)

1. 对于MySQL的查询缓存来说，如果关联中的某个表发生了改变，那么久无法使用查询缓存。
2. 减少锁竞争。
3. 应用层做关联，这更有利于数据库拆分（sharding等等），做到可扩展。
4. 某些时候，分解查询对于数据库和应用来说，只查询了一次表或数据块。而一个或多个关联查询**可能**会重复多次查询了某些表或数据。

## 查询执行的基础

![一个查询的完整过程](/files/-LIuUeaRHQeqFqj5odPo)

### MySQL客户端/服务端通信协议

MySQL客户端和服务端之间的通信协议是“半双工”的。这意味着，某一时刻，只能是由一方传数据到另一方。所以，我们无法将数据切分成小块独立发送。一个明显的限制是，意味着无法进行流量控制。（ps.曾听说我朋友因为这个原因出过一个不小的bug）。注意参数 max\_allowed\_packet。

相应地，客户端也不能贸然只接受前几条就终止（当然也不要暴力终止）。

#### 查询状态

可通过SHOW FULL PROCESSLIST命令查看当前各个MySQL查询连接的状态。各状态值请看考MySQL文档。

### 查询优化处理

书中该节谈查询优化器的。平时用EXPLAIN调优可结合该节和附录看着调。

#### MySQL如何执行关联查询

MySQL中“关联”是一个广泛的词。总的来说，MySQL认为任何一个查询都是一次“关联” （包括子查询，甚至基于单表的select）都可能是关联查询。

MySQL关联执行的策略：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行。

![](/files/-LJAKix7gQmQHpNJcp-I)

![](/files/-LJALRjU4hgozHEkA87f)

#### 关联查询优化器

有时候，MySQL查询优化器会更改我们写的SQL的join的顺序，这一点，可对比执行的SQL和EXPLAIN SQL的结果，这是由于查询优化器重定义了关联的顺序，这是查询优化器一个重要的功能。我们可使用STRAINGHT\_JOIN关键字重写查询，使查询按照我们SQL的JOIN顺序来执行。不过，绝大多数时候，优化器的优化都比常人的判断准确。

#### 排序优化

无论如何，排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。

当不能使用索引生成排序结果，MySQL则会再自行排序，MySQL将这一过程统称为文件排序。

![](/files/-LJAPaggKiI5XAPGAvpY)

#### HINTS

可通过HINTS控制最终执行计划。

具体看文档：[8.9.2 Optimizer Hints](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html)

控制查询优化器的方式不止以上一种，具体可查阅：[8.9 Controlling the Query Optimizer](https://dev.mysql.com/doc/refman/8.0/en/controlling-optimizer.html)，个人认为都是骚操作，假若在生产中使用，需事前阅读好文档。

书中，关于查询的章节，案例篇的**计算两点之间的距离，**它用实例告诉了我们，在实际应用中如何取舍，什么时候该冗余计算值，如何避免矫枉过正，值得细细阅读。