【高性能MySQL】第6章 读书笔记

第6章 查询性能优化

6.1 为什么查询速度会慢

6.2慢查询基础：优化数据访问

6.2.1 是否向数据库请求了不需要的数据

6.2.2 MySQL 是否在扫描额外的记录

6.3 重构查询方式

6.3.1 一个复杂的查询还是多个简单的查询

6.3.2 切分查询

6.3.3 分解关联查询

分解关联查询的优势：

1. 让缓存效率更高。
2. 减少锁竞争。
3. 可以更容易对数据库进行拆分，更容易做到高性能和可拓展。
4. 更高效。
5. 可以减少冗余记录的查询。
6. 某些哈希关联的效率高很多。

6.4 查询执行的基础

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

半双工

查询状态：

Sleep

Query

Locked：表锁，InnoDB 的行锁，并不会体现在线程状态中。

Analyzing and statistics

Copy to table [on disk]

Sorting result

Sending data

6.4.2 查询缓存

哈希查找

Percona 版本的 MySQL 中提供了一个新特性：在计算查询语句的哈希值时，先将注释移除再计算哈希值，这对于不同注释的相同查询可以命中相同的查询缓存结果。

6.4.3 查询优化处理

**语法解析器和预处理：**检查语法树，权限验证。

**查询优化器：**找到最好的执行计划。

以下原因会导致 MySQL 优化器会选择错误的执行计划：

• 统计信息不准确

• 执行计划的成本估算不等同于实际执行的成本。

• MySQL 的最优可能和你想的最优不一样。

• MySQL 从不考虑其他的并发执行查询。

• MySQL 并不是任何时候都基于成本优化。

  MATCH()

• MySQL 不会考虑不受其控制的操作，例如自定义函数

• 优化器无法估算所有可能的执行计划。

以下是 MySQL 可以处理的优化类型：

• 重新定义关联表的顺序。
• 将外连接转化为内连接。
• 使用等价变换规则。
• 优化 COUNT()，MIN()，MAX()。
• 预估并转化为常数表达式。
• 覆盖索引扫描。
• 子查询优化。
• 提前终止查询。
• 等值传播。
• 列表 IN() 的比较。

注意：优化器缺少哈希索引！

数据和索引的统计信息：

MySQL 如何执行关联查询：

执行计划：

关联查询优化器：

排序优化：

MySQL 两种排算法：

两次传输排序

单次传输排序

6.4.4 查询执行引擎

6.4.5 返回结果给客户端

MySQL 将结果返回客户端的过程是一个增量，逐步返回的过程。例如一旦服务器处理完最后一个关联表，开始生成第一个结果时，MySQL 就开始向客户端逐步返回结果集了。

以一个满足 MySQL 客户端/服务器通信协议的封发送，再通过 TCP 协议进行传输，在进行 TCP 传输的过程中，可能对 MySQL 封包进行缓存然后批量传输。

6.5 MySQL 查询优化的局限性

6.5.1 关联子查询

如何使用好关联子查询

6.5.2 UNION 限制

6.5.3 索引合并优化

6.5.4 等值传递

6.5.5 并行执行

6.5.6 哈希关联

MySQL 不支持哈希关联；

MySQL 8.0 新特性之哈希连接（Hash Join）

https://blog.csdn.net/weixin_35747785/article/details/113252508

MySQL 的所有关联都是嵌套循环关联；

可以建立一个哈希索引来曲线地实现哈希关联。

6.5.7 松散索引扫描

类似于Oracle的跳跃索引扫描。

MySQL 不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。

6.5.8 最大值最小值优化

6.5.9 在同一表上查询和更新

可以通过使用生成表的形式来解决。

6.6 查询优化器的提示（hint）

**HIGH_PRIORITY 和 LOW_PRIORITY：**优先级。

DELAYED：对 INSERT 和 REPLACE 有效，将插入的数据放到缓冲区，等表空闲时将数据写入。

**STRAIGHT_JOIN：**在SELECT 关键字或语句后，按顺序关联；固定前后两个表的关联顺序。

**SQL_SMALL_RESULT 和 SQL_BIG_RESULT：**只对SELECT 语句有效它们告诉优化器对 GROUP BY 或者 DISTINCT 查询如何使用临时表和排序。

**SQL_BUFFER_RESULT：**告诉优化器将查询结果放入一个临时表，然后尽可能快地释放锁。

**SQL_CACHE 和 SQL_NO_CACHE：**告诉 MySQL 这个结果集是否应该缓存存在查询缓存中。

**SQL_CALC_FOUND_ROWS：**查询中加上该提示 MySQL 会计算出去 LIMIT 子句后这个查询要返回结果集的总数，而实际上只返回 LIMIT 要求的结果集。

**FOR UPDATE 和 LOCK IN SHARE MOOD：**提示锁机制，只对实现了行级锁的存储引擎有效。

**USE INDEX，IGNORE INDEX 和 FORCE INDEX：**使用索引与否。

**optimizer_search_depth：**控制优化器在穷举执行计划的限度。

**optimizer_pru_level：**根据扫描行数来觉得是否跳过某些执行计划。

**optimizer_switch：**开启或关闭某些特定的标志位。

6.7 优化特定类型的查询

6.7.1 优化 COUNT() 查询

COUNT()：统计行数和列数。

在 MyISAM 中，没有任何 where 条件的 COUNT() 非常块。

6.7.2 优化关联查询

6.7.3 优化子查询

6.7.4 优化 GROUP BY 和 DISTINCT

MySQL 优化器会互相转化这两类查询。

6.7.5 优化 LIMIT 分页

6.7.6 优化SQL_CALC_FOUND_ROWS

6.7.7 优化 UNION 查询

6.7.8 静态查询分析

6.7.9 使用用户自定义变量

用户自定义变量是一个用来存储内容的临时容器，在连接 MySQL 的整个过程都存在。

用处：

​ 优化排名语句

​ 避免重复查询刚刚更新的数据

​ 统计和更新插入的数量

​ 确定取值的顺序

​ 编写偷懒的UNION

6.8 案例学习

6.8.1 使用 MySQL 建立一个队列表

6.8.2 计算两点之间的距离

6.8.3 使用用户自定义函数