《Mysql查询优化实战》读书笔记总结

Mysql SQL执行过程

1. 客户端提交SQL语句：

• 客户端（如应用程序、命令行工具或图形界面工具）向MySQL服务器发送一条SQL查询请求。

2. 查询缓存检查：

• 服务器接收到SQL查询后，首先会检查查询缓存（Query Cache）。如果发现完全相同的查询之前被执行过且结果仍可复用，则直接从缓存中取出结果返回给客户端。然而，由于查询缓存的实际效益受限于多种因素（如缓存命中率低、并发环境下易失效、维护开销大），在MySQL 8.0版本中已移除查询缓存功能，之前的版本虽然提供了查询缓存但通常建议谨慎使用或关闭。

3. 解析器阶段：

• 如果查询未命中缓存，MySQL服务器的解析器开始工作，它会对SQL语句进行词法分析和语法分析，将其转换成一个抽象的解析树结构，以便进一步处理。

4. 预处理阶段：

• 预处理器接手解析树，对SQL语句做进一步的合法性验证及改写。这包括处理SQL语句中的注释、检查用户权限、展开视图定义等操作。

5. 优化器阶段：

• 经过预处理的解析树传递到查询优化器，优化器根据表的统计信息（如索引、记录数量、数据分布等）选择最佳的执行计划。这个阶段涉及选择合适的索引、决定表的连接顺序和方法等决策，其目标是最小化查询执行的成本。

6. 执行引擎与存储引擎交互：

• 一旦优化器确定了执行计划，执行引擎按照计划调用存储引擎提供的API进行实际的数据处理操作，例如读取数据、更新记录、执行索引查找等。MySQL的架构中，Server层负责SQL的解析和优化，而存储引擎层负责数据的存储和读取。

7. 执行与结果返回：

• 执行引擎执行完查询后，会将结果集返回给Server层，然后Server层将结果返回给客户端。如果查询结果满足缓存条件，在旧版本的MySQL中，查询结果会被放入查询缓存以供后续相同查询使用，但在MySQL 8.0以后不再适用。

8. 清理与结束：

• 结束查询执行过程，释放相关资源。在事务上下文中，还需要进行事务提交或回滚等操作。

优化目标

1. 减少磁盘I/O：

• 全表扫描：避免不必要的全表扫描操作，通过创建有效的索引并优化查询语句来利用索引进行快速检索，从而减少对大量数据块的读取。

• 磁盘临时表：尽量避免产生磁盘临时表，例如在执行复杂的JOIN或GROUP BY操作时，如果内存不足以容纳中间结果集，则可能导致临时表被写入磁盘，应适当增加内存或优化查询设计以减少此类情况。

• 日志和数据块fsync：合理配置日志同步策略，例如调整InnoDB的innodb_flush_log_at_trx_commit参数，以及合理安排批量写入，以减小频繁的日志刷盘带来的磁盘I/O压力。

2.减少网络带宽消耗：

• 返回太多数据：仅返回应用程序实际需要的数据，通过选择性地查询指定列、使用LIMIT限制结果数量、或者采用数据分页等方式来减少传输的数据量。

• 交互次数过多：通过批量插入、合并查询（如UNION ALL替代多个单次查询）、预编译和重用SQL语句（PreparedStatement）等方式减少与数据库服务器之间的网络交互次数。

3.降低CPU消耗：

• 排序分组操作（ORDER BY, GROUP BY）：优化分组和排序操作，确保有合适的索引支持，或者考虑是否可以在应用程序层面进行部分预处理，以减轻数据库端的计算负担。

• 聚合函数（MAX, MIN, SUM等）：针对聚合函数的计算，同样需确认是否有索引可用，并考虑是否可以通过汇总表或其他预先计算机制来提前完成复杂度较高的计算任务。

• 逻辑读：优化查询计划，让MySQL查询优化器选择最优的执行路径，减少不必要的数据读取和计算操作，例如避免在索引列上进行函数运算导致索引失效，进而引发全表扫描。同时，良好的索引设计有助于简化逻辑读操作，提高数据获取效率。

SQL优化和原则

原则

• 减少访问量：数据存取是数据库系统最核心功能，所以IO是数据库系统中最容易出现性能瓶颈，减少SQL访
  问IO量是SQL优化的第一步；数据块的逻辑读也是产生CPU开销的因素之一。
  • 减少访问量的方法：创建合适的索引、减少不必访问的列、使用索引覆盖、语句改写。
• 减少计算操作：计算操作进行优化也是SQL优化的重要方向。SQL中排序、分组、多表连接操作等计算操作
  都是CPU消耗的大户。
  • 减少SQL计算操作的方法：排序列加入索引、适当的列冗余、SQL拆分、计算功能拆分。

方法

• 创建索引减少扫描量
• 调整索引减少计算量
• 索引覆盖（减少不必访问的列，避免回表查询）
• SQL改写
• 干预执行计划

查询和干预执行计划

查看执行记录

explain [extended] SQL_Statement

优化器开关

show variables like 'optimizer_switch'

连接

show [full] processlist
information_schema.processlist

显示当前所有活动的连接，包括每个连接的ID、用户、主机、数据库、命令类型（如Sleep、Query、Connect等）、状态信息、当前SQL语句（如果正在执行查询）等内容。

优化策略

order by

order by查询的两种情况：

Using Index (索引排序):

• 当查询的ORDER BY子句引用的列已经存在于一个索引中，并且查询的执行计划选择了按照该索引顺序扫描记录时，MySQL可以直接通过索引返回已经排序好的记录，无需额外的排序操作。这种方式极大提高了查询性能，因为它减少了对内存和CPU的消耗。

Using filesort (文件排序):

• 当MySQL无法仅仅依赖索引获得有序数据时，它会执行一种称为“文件排序”（filesort）的操作。这意味着MySQL会先读取所有需要的行，然后在内存或者临时文件中进行排序。这样的操作通常会带来较大的性能开销，尤其是在处理大量数据时。

优化建议:

• 尽量保证WHERE条件中筛选的列和ORDER BY排序的列都使用同一个索引，并且索引列的顺序与ORDER BY子句中的列顺序一致。
• 确保ORDER BY中的所有字段都在同一个索引中，并且排序方向（升序或降序）与索引顺序一致。
• 若WHERE条件中有范围查询（如BETWEEN、>、<等）时，即使排序字段也在索引中，但如果索引不能连续遍历（因范围查询导致的不连续访问），MySQL往往无法避免使用文件排序。

特殊情况:

• 当WHERE条件中的过滤字段为覆盖索引的一部分（即索引足以覆盖查询所需的全部列），并且ORDER BY的字段位于同一索引的后面位置时，只有当WHERE条件过滤极为严格（例如常量等于某值，即const匹配），MySQL才有可能利用索引顺序直接返回有序结果，而避免文件排序。对于非const匹配的范围查询，即便索引包含了排序字段，MySQL也无法利用索引进行排序。

filesort优化
在无法避免filesort操作时，确实可以通过优化filesort本身的方式来提升性能，主要的思路包括：

1. 调整排序算法选择：

   • MySQL在进行filesort时会选择合适的排序算法。根据排序字段的大小，MySQL可能会采取“两次扫描算法”或“一次扫描算法”。若排序字段较小，MySQL会选择“一次扫描算法”，在排序缓冲区内完成排序，这样可以减少磁盘I/O，加快排序速度。

2. 优化参数max_length_for_sort_data：

   • max_length_for_sort_data参数用于确定MySQL是否采用“一次扫描排序算法”。当排序字段总长度小于这个参数值时，MySQL倾向于在内存中一次性取出所有记录并排序。增大这个参数值，可以让MySQL在更多情况下选择内存排序而非磁盘排序，从而提高排序效率。

3. 精简查询字段：

   • 避免使用SELECT *，只选择真正需要的字段，这样不仅可以减少排序时需要处理的数据量，还可以减少网络传输的数据量，间接提升排序性能。

4. 调整sort_buffer_size：

   • sort_buffer_size参数是MySQL为每个需要排序的线程分配的缓冲区大小。适当增大这个参数值，可以增加排序过程中能容纳的记录数量，降低需要写入临时文件的概率，从而避免磁盘排序。不过需要注意的是，这个参数不宜设置得过大，以免占用过多的内存资源，影响整个MySQL实例的稳定性。

Subquery

子查询在某些情况下会导致MySQL创建临时表来存储中间结果，这对于查询性能可能会造成一定的影响。为了优化这种情况，可以考虑以下策略：

1. 等价改写为JOIN查询：

   • 在许多情况下，子查询可以用JOIN操作替换，尤其是关联两个表的情况。JOIN操作可以利用索引来高效地联接表，比子查询创建临时表后再处理的方式更为高效。例如，原查询可能是从一个表中选取与另一个表中满足特定条件的记录相关的数据，这时可以尝试将子查询改写为INNER JOIN、LEFT JOIN、RIGHT JOIN或OUTER JOIN。

2. 反嵌套（Dependent Subquery Unnesting）：

   • 反嵌套是一种特殊的优化技术，MySQL优化器在某些条件下能够自动将嵌套子查询转化为JOIN形式，或者应用其他优化策略，以减少临时表的使用。但是，不是所有子查询都可以通过反嵌套进行优化，这取决于查询的具体逻辑和MySQL优化器的能力。

limit

分页查询在数据库查询优化中是一项重要任务，特别是在处理大数据量时，传统的分页查询方式可能存在性能瓶颈。优化目标主要包括：

1. 消除不必要的排序：

   • 如果应用中的分页查询需要对结果进行排序，而排序字段并没有合适的索引支持，那么每次分页查询都会触发全表扫描并进行排序，这是很大的性能开销。优化时应尽量确保排序字段上有索引，或者在业务允许的情况下，去掉不必要的排序操作。

   • 如果必须排序，可以尝试通过覆盖索引（索引中包含了查询和排序所需的全部列）来避免排序时的回表操作，或者调整查询语句，使ORDER BY和LIMIT操作能在索引上完成。

2. 避免扫描大量不需要的记录：

   • 在进行分页查询时，传统的做法是从第一页开始逐页往后查，每一页都要扫描大量的无关记录。为优化这一点，可以采用所谓的"offset + limit"优化技巧，即通过组合主键或唯一索引的范围查询来定位分页位置，减少无谓的扫描。

   • 对于MySQL，可尝试使用LIMIT offset, row_count方式进行分页，但要注意随着offset值增大，查询效率会显著降低。这时，可以结合WHERE条件使用一个自增的唯一索引列进行范围查询，以更精确地定位每页数据的位置，减少扫描记录数。

   • 对于非常大的分页需求，可以采用基于游标的分页方法，即记录上次分页查询的最大主键值，下次查询时以此作为起点继续查询下一页数据，这种方法可以有效减少跳跃式扫描带来的性能损耗。

or/and condition

在数据库查询优化中，AND和OR操作符的使用会影响查询性能和索引的选择：

1. AND操作符：

   • 当查询语句中使用AND连接多个条件时，结果集是由各个条件过滤后的交集。如果有其中一个条件可以利用索引进行高效过滤，即使其它条件不能利用索引，也能在一定程度上减少需要进一步处理的数据量。在这种情况下，如果有一个条件具有很高的过滤性（即能够排除大部分数据），创建对此条件的索引就非常关键。

2. OR操作符：

   • OR操作符的结果集是由各个条件分别查询的并集。MySQL通常会将OR条件转化为多个查询的UNION操作。对于OR条件，除非所有字段都有独立的索引，否则很难充分利用索引。理想的做法是，OR两边的字段都应当创建索引，以提高查询效率。

3. 复合索引：

   • 对于多个字段同时出现在WHERE子句中，并且这些字段的过滤性和查询模式相对固定的情况，可以考虑创建复合索引。复合索引是针对多个字段组合而成的一个索引，它特别适用于多个字段同时参与AND条件过滤的场景。对于OR条件，如果两侧的字段都在同一个复合索引中，理论上可以提高查询性能，但实际上MySQL通常不会像对待AND条件那样有效地利用复合索引。

处理策略：

• 对于AND子句，重点在于找到过滤性最强的字段，为其创建索引，或者为多个常用字段组合创建复合索引。
• 对于OR子句，务必保证两边的字段都有各自独立的索引，如果条件组合较为固定，也可以尝试创建覆盖这些条件的复合索引，尽管OR条件下复合索引的利用并不一定如AND条件那样直接有效。

join优化

在MySQL中，JOIN操作是连接两张或多张表以获取所需数据的关键操作。对于JOIN优化，可以从以下几种策略着手：

1. Nested-Loop Join算法优化：

   • Nested-Loop Join是最基本的JOIN算法，它的执行过程类似于两层嵌套循环，外层循环取一张表的每一行，内层循环通过外层行的值去另一张表中寻找匹配的行。

2. 关联字段索引优化：

   • 对于JOIN操作涉及的关联字段（JOIN条件中的字段），确保这些字段上都有合适的索引，以便MySQL能够快速定位到相关数据。在Nested-Loop Join中，每层内部循环只需获取通过索引能够快速定位的那部分数据，而不是全表扫描，这样可以极大地提高JOIN效率。

3. 小表驱动原则：

   • 在执行JOIN操作时，遵循“小表驱动大表”的原则。也就是优先选择数据量较小的表作为驱动表，这是因为内层循环的次数将直接影响JOIN的整体效率。通过观察和分析数据表的实际大小和数据分布情况，选择合适的小表作为JOIN操作的驱动表，可以显著降低JOIN的执行成本。

来源

藏经阁《Mysql查询优化实战》