> 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)，个人认为都是骚操作，假若在生产中使用，需事前阅读好文档。

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


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://mysql.gitbook.teaho.net/cha-xun-xing-neng-you-hua.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.