读高性能MySQL（第4版）笔记11_查询性能优化（中）

1. MySQL的客户端/服务器通信协议

1.1. MySQL的客户端和服务器之间的通信协议是“半双工”的

1.2. 在任何时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生

1.3. 当查询的语句很长的时候，参数max_allowed_packet就特别重要了

1.4. 一般的服务器响应给用户的数据通常很多，由多个数据包组成

1.5. 当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据

1.5.1. 在必要的时候一定要在查询中加上LIMIT限制

1.6. 当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是MySQL在向客户端推送数据的过程

1.7. MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放相应的资源

1.8. 当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，而实际上都是从这个库函数的缓存获取数据

1.9. 如果能够尽早开始处理这些结果集，就能大大减少内存的消耗，在这种情况下可以不使用缓存来记录结果而是直接处理

1.9.1. 对于服务器来说，需要查询完成后才能释放资源，所以在和客户端交互的整个过程中，服务器的资源都是被这个查询所占用的

1.9.2. 用mysql_unbuffered_query()代替mysql_query()，PHP则不会缓存结果

2. SHOW FULL PROCESSLIST命令

2.1. 该命令返回结果中的Command列，其就表示当前的状态

2.2. Sleep

2.2.1. 线程正在等待客户端发送新的请求

2.3. Query

2.3.1. 线程正在执行查询或者正在将结果发送给客户端

2.4. Locked

2.4.1. 在MySQL服务器层，该线程正在等待表锁

2.5. Analyzing and statistics

2.5.1. 线程正在检查存储引擎的统计信息，并优化查询

2.6. Copying to tmp table [on disk]

2.6.1. 线程正在执行查询，并且将其结果集复制到一个临时表中

2.6.2. 么是在做GROUP BY操作

2.6.3. 要么是在进行文件排序操作

2.6.4. 或者是在进行UNION操作

2.6.5. “on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上

2.7. Sorting result

2.7.1. 线程正在对结果集进行排序

3. 导致MySQL优化器选择错误的执行计划

3.1. 统计信息不准确

3.1.1. MySQL服务器依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大

3.2. 成本指标并不完全等同于运行查询的实际成本

3.3. MySQL的最优可能和你想的最优不一样

3.3.1. MySQL只是基于其成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式

3.4. MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度

3.5. MySQL也并不是任何时候都是基于成本的优化

3.5.1. 如果存在全文搜索的MATCH()子句，则在存在FULLTEXT索引的时候就使用全文索引

3.5.2. 即使有时候使用其他索引和WHERE条件可以远比这种方式要快，MySQL也仍然会使用对应的全文索引

3.6. MySQL不会考虑不受其控制的操作的成本

3.6.1. 执行存储函数或者用户自定义函数的成本

4. 优化策略

4.1. 静态优化

4.1.1. 不依赖于特别的数值

4.1.2. 在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化

4.1.3. 编译时优化

4.2. 动态优化

4.2.1. 和查询的上下文有关

4.2.2. 在每次查询的时候都重新评估

4.2.3. 运行时优化

5. 优化类型

5.1. 重新定义联接表的顺序

5.1.1. 数据表的联接并不总是按照在查询中指定的顺序进行

5.2. 将外联接转化成内联接

5.2.1. 并不是所有的OUTER JOIN语句都必须以外联接的方式执行

5.3. 使用代数等价变换规则

5.4. 优化COUNT()、MIN()和MAX()

5.4.1. 索引和列是否可为空通常可以帮助MySQL优化这类表达式

5.5. 预估并转化为常数表达式

5.5.1. 当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理

5.6. 覆盖索引扫描

5.6.1. 当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行

5.7. 子查询优化

5.7.1. 将子查询转换为一种效率更高的形式，从而减少多个查询多次对数据进行访问

5.8. 提前终止查询

5.8.1. 在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询

5.8.2. 一个典型的例子就是当使用了LIMIT子句的时候

5.9. 类似这种“不同值/不存在”的优化一般可用于DISTINCT、NOT EXIST()或者LEFT JOIN类型的查询

5.10. ⑩等值传播

5.11. ⑾列表IN()的比较

5.11.1. IN()完全等同于多个OR条件的子句，因为这两者是完全等价的

5.11.2. 在MySQL中这点是不成立的，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O（logn）复杂度的操作，等价地转换成OR查询的复杂度为O（n），对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快

6. 表和索引的统计信息

6.1. 存储引擎则给优化器提供对应的统计信息，包括：每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引的长度是多少、索引的分布信息等

7. 联接查询

7.1. MySQL对任何联接都执行嵌套循环联接操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止

7.2. 在MySQL 8.0.20版本之后，已经不再使用基于块的嵌套循环联接操作，取而代之的是哈希联接

7.2.1. 这让联接操作性能变得更好，特别是当数据集可以全部存储在内存时

7.3. 通常多表联接的时候，可以有多种不同的联接顺序来获得相同的执行结果

7.4. 执行计划

7.4.1. 如果你对某个查询执行EXPLAIN EXTENDED后，再执行SHOWWARNINGS，就可以看到重构出的查询

7.5. 让查询进行更少的回溯和重读操作

7.5.1. 可以使用STRAIGHT_JOIN关键字重写查询，让优化器按照你认为的最优的联接顺序执行

7.6. n个表的联接可能有n的阶乘种联接顺序

7.6.1. 优化器选择使用“贪婪”搜索的方式查找“最优”的联接顺序

7.6.2. 当需要联接的表超过optimizer_search_depth的限制的时候，就会选择“贪婪”搜索模式了

7.7. 查询不能重新排序，联接优化器可以利用这一点通过消除选择来减小搜索空间