Mysql进阶_查询优化

最近感觉自己数据库技术知识储备有点薄弱，很多东西只知其然，不知其所以然，对一些比较复杂的SQL查询也没好的查询方案。一咬牙，一跺脚，决定拿起在书架上落灰已久的《高性能Mysql》一书，为了避免走马观花式的阅读，给自己定一个读书任务，每看完一章，写一篇总结博客，用来记录当前章节的重点难点知识，供日后翻阅查看，权当读书笔记。

1. 为什么查询速度会慢

在一个查询中，最重要的是响应时间，如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行的更快，要对一个查询进行优化，首先要理解它为什么会慢。

对于低效的查询，可以从以下两个方面优化分析：

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

1.1 查询不需要的记录

1. 查询不需要的记录：比如说只需要10条数据，却将整个表的数据都查询出来。
2. 多表关联时返回全部列：多表关联查询的时候，只需要几列，却直接select *
3. 总是取出全部列：只需某几列的数据，直接select *
4. 重复查询相同的数据：不断地重复执行相同的查询，然后每次都返回相同数据。

1.2 MySQL是否扫描额外的记录

1.响应时间

响应时间是服务时间和排队时间之和。

服务时间：数据库处理这个查询真正花费的时间。

排队时间：服务器因为等待某些资源而没有真正执行查询的时间，可能等I/O操作完成，也可能等待行锁等。

2.扫描的行数和返回行数

3. 扫描行数和访问类型

扫描表、扫描索引、范围访问和单值访问的概念。

一般情况下mysql的where条件从好到坏依次是：

• 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎完成的。
• 使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。
• 从数据库表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。这在MySQL服务处完成，MySQL需要先从数据表读出记录然后过滤。

如果查询需要扫描大量的数据但只返回少数的行，可以采用以下方法进行优化：

• 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了。
• 改变库表结构。例如使用单独的汇总表。
• 重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询。

2 重构查询的方式

优化有问题的查询时，目标应该是找到一个更优的方法获得实际需要的结果，而不一定总是需要从MySQL获取一模一样的结果集。

2. 1 一个复杂查询还是多个简单查询

设计查询的时候有一个需要考虑的重要问题是，是否将一个复杂的查询分成多个简单的查询？

在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为更多的小查询是很有必要的。

2.2 切分查询

将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

比如说删除旧数据，定期清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的delete语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。

# 优化前
mysql>delete from messages where created < date_sum(now(), interval 3 month);
# 优化后
rows_affected = 0
do{
 rows_affected = do_query(
     "mysql>delete from messages where created < date_sum(now(), interval 3 month) 
     limit 10000")
} while rows_afffected > 0

2.3 分解关联查询

用分解关联查询的方式重构查询有如下的优势：

• 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身效率也可能会有所提升。
• 可以减少冗余记录的查询。

更容易对数据库进行拆分， 更容易做到高性能和可扩展

减少冗余记录的查询

3 查询执行的基础

当向MySQL发送一个请求时，MySQL执行如下图所示：

​ [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GD5W93wA-1640933725518)(file://C:/Users/Lenovo/AppData/Roaming/Typora/typora-user-images/image-20211031133439960.png?lastModify=1637802602)]

1. 客户端发送一条查询给服务器。
2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5. 将结果返回给客户端。

3.1 MySQL 客户端/服务器通信协议

MySQL客户端和服务器之间的通信协议是"半双工"，在任何一个时刻，要么服务器向客户端发送数据，要么客户端向服务器发送数据，这两个动作不可能同时发生。

服务器-> 客户端 或客户端->服务器

查询状态

对于一个MySQL链接，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。可以使用命令SHOW FULL PROCESSLIST命令查看（Command列表示当前的状态），

sleep: 线程正在等待客户端发送新的请求

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

locked: 等待锁

ayalyzing and statistics : 线程正在收集存储引擎的统计信息, 并生成查询的执行计划

copying to tmp table [on disk]: 线程正在执行查询, 并将结果都赋值到一张临时表，一般是在做group by操作、文件排序操作或union操作。

sorting result: 线程正在对结果集进行排序

Sending data: 线程在多个状态之间传送数据或生成结果集,或在向客户端返回数据

3.2 查询缓存

若查询缓存是打开的，则先查询缓存，没有就进入下一阶段。如果查询缓存命中，就检查用户权限，权限验证成功直接从缓存中拿取结果返回客户端。

注：缓存是大小写敏感的哈希查找，查询和缓存中的查询即时只有一个字节不同，也不会匹配缓存结果。

3.3 查询优化处理

语法解析器和预处理

MySQL通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。

查询优化器

一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

静态优化: 直接对解析树进行分析，并完成优化(编译时优化) 。比如，优化器可以通过一些简单的代数变换将WHERE条件转化为另一种等价形式。

动态优化:(运行时优化)

• 重新定义关联表的顺序:数据表的关联并不总是按照在查询中指定的顺序执行。
• **将外链接转化为内连接：**并不是所有的OUTER JOIN语句都必须以外连接的方式执行。比如WHERE条件、库表结构都可能会让外连接等价于一个内连接。
• 使用等价变化规则：MySQL可以使用一些等价变化来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。
• 优化count(), min() 和max()：索引和列是否可为空通常可以帮助MySQL优化这类表达式。
• 预估并转化为常数表达式：当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。
• 覆盖索引扫描
• 子查询优化
• 提前终止查询
• 等值传播
• 列表IN()的比较

数据和索引的统计信息

MySQL服务器层没有任何统计信息，所以查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。

MySQL如何执行关联查询

任何一次查询都是一次关联

MySQL对任何关联都执行嵌套循环关联操作, 即mysql先在一个表中循环取出单挑数据, 然后在嵌套循环到下一个表中寻找匹配的行

执行计划

关联查询优化器

排序优化

3.4 查询执行引擎

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。

3.5 返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，如该查询影响到的行数。

如果查询可以被缓存，那么MySQL在这个阶段也会将结果放到查询缓存中。

4 MySQL查询优化器的局限性

4.1 关联子查询

WHERE条件中包含IN（）的子查询语句非常影响查询效率。

4.2 UNION的限制**

4.3 索引合并优化

4.4 等值传递

4.5 并行执行

4.6 哈希关联

4.7 松散索引扫描

4.8 最大值和最小值优化

4.9 在同一个表上查询和更新

5 优化特定类型的查询

5.1 优化COUNT（）查询

5.2 优化关联查询

5.3 优化子查询

5.4 优化GOUP BY和DISTANCE

5.5 优化LIMIT分页

5.6 优化SQL_CALC_FOUND_ROWS

5.7 优化UNION查询

5.8 静态查询分析

5.9 使用用户自定义变量