MySQL~慢日志基础

MySQL慢查询日志分析与优化指南

一、慢日志EXPLAIN关注点

在分析慢查询时，EXPLAIN命令是您的得力助手。您需要关注以下几点：

1. 索引情况：

   • 是否走索引：检查查询是否利用了索引。
   • 索引是否存在/失效/完全覆盖：确认索引是否有效，是否完全覆盖了查询所需的列。

2. 扫描的行数：

   • 对比扫描的行数与实际预期结果行数，判断是否存在大量无效扫描。

3. 额外信息：

   • 是否用到临时表、文件排序：了解查询过程中是否产生了额外的开销。
   • 联合查询字段是否走索引：确认联合查询中的字段是否都利用了索引。

二、慢日志查看字段

慢查询日志记录了查询的详细信息，以下是一些关键字段：

1. Query_time：查询执行时间，判断查询是否慢的主要依据。
2. Lock_time：查询等待锁的时间，反映并发处理的锁竞争情况。
3. Rows_sent：查询返回给客户端的行数，评估查询效率和输出规模。
4. Rows_examined：查询过程中扫描的行数，判断是否需要优化查询或添加索引。
5. Db：执行慢查询的数据库名。
6. User@Host：执行慢查询的用户和主机信息。
7. Timestamp：查询执行的时间点（在某些版本的MySQL中）。
8. Query：具体的慢查询语句。

三、慢日志优化手段

针对慢查询，可以从数据库层和应用层两个方面进行优化：

1. 数据库层优化：

   • 添加合理索引：根据查询模式创建适当的索引。
   • 进行读写分离：减轻主数据库的压力。
   • 冷热数据归档：减少查询过程中的数据量。
   • 提升硬件水平：增加内存、使用更快的存储系统等。

2. 应用层优化：

   • 增加Redis缓存层：减少对数据库的访问次数。
   • 优化业务逻辑：减少不必要的数据库查询。
   • 降低并发或改写SQL：降低查询的复杂度和执行时间。

四、驱动表与多表联查算法

在SQL执行过程中，驱动表会先被访问，而被驱动表则会在每次驱动表的一行数据被处理后访问。优化的一个常见目标是让小表作为驱动表来驱动大表。多表联查算法主要包括以下几种：

1. 嵌套循环连接（Nested Loop Join）：

   • 扫描驱动表的每一行，然后根据此行数据扫描被驱动表的数据进行匹配查询，可能会形成笛卡尔积。

2. 块嵌套循环连接（Block Nested Loop Join）：

   • 将驱动表的一部分行数据放在内存中，然后与被驱动表进行匹配查询，依赖join_buffer_size参数。

3. 索引嵌套循环连接（Index Nested Loop Join）：

   • 利用索引过滤驱动表的一行数据，然后根据此行数据在被驱动表进行索引过滤匹配。

4. 哈希连接（Hash Join）：

   • 将驱动表进行哈希，然后根据哈希对被驱动表进行匹配查询。

了解这些算法有助于更好地优化查询，例如，通过调整join_buffer_size参数来优化块嵌套循环连接的性能。

五、联合索引最左匹配原则与索引失效场景

1. 联合索引最左匹配原则：

   • MySQL会一直向右进行匹配直到遇到范围查询（如>,<,BETWEEN,LIKE等）导致索引失效。
   • 联合索引的构建顺序是按照索引列的顺序构建的。

2. 索引失效场景：

   • 查询条件没有使用到联合索引的最左边的列。
   • LIKE查询以%开头。
   • 查询条件存在隐式转换。
   • 查询条件存在函数运算。
   • 多表连接的字段存在字符集类型等不一致的情况。
   • 查询结果的数据分部占据了表的很大一部分。
   • 统计信息不准确时。

六、EXPLAIN的extra列说明

1. Using index：表示查询只需要访问索引，不需要回表查询数据。
2. Using where：表示查询需要通过where条件来过滤数据。
3. Using temporary：表示查询需要使用临时表来存储中间结果，可能发生在内存或者磁盘中通常发生在排序和分组操作中。
4. Using filesort：表示查询需要使用文件排序，可能发生在内存或者磁盘中，通常发生在包含ORDER BY或GROUP BY子句的查询中。
5. Impossible WHERE：表示查询的WHERE条件永远为假，无法返回任何结果。
6. Select tables optimized away：表示查询中没有FROM子句，或者查询只涉及常量表。

七、ICP特性
1. 未使用ICP的查询过程

• 解析查询：MySQL服务器接收SQL查询，解析确定需访问的表和索引。
• 索引查找：服务器通过存储引擎接口，在索引中查找满足条件的索引项，此过程仅基于索引键值。
• 数据行检索：根据索引项中的指针或主键值，从数据表中检索出完整的行数据。
• 过滤行数据：在服务层，根据WHERE子句中的其他条件对检索出的行进行过滤。
• 返回结果：将过滤后的结果返回给客户端。
  2. 使用ICP的查询过程
• 解析查询：MySQL服务器解析查询，确定需访问的表和索引。
• 索引查找与部分过滤：服务器将WHERE子句中的部分条件（索引条件）下推到存储引擎层。存储引擎在查找索引项时，同时根据这些下推的条件进行过滤。
• 数据行检索与最终过滤：根据过滤后的索引项检索数据行，然后在服务层根据WHERE子句中的剩余条件进行最终过滤。
• 返回结果：将最终过滤后的结果返回给客户端。
  优化结果
• 减少基表IO操作：存储引擎层在读取完整数据前进行初步过滤，降低IO成本。
• 减少Server层数据量：存储引擎层和MySQL服务器层间传输的数据量减少。
  适用场景
• ref：非唯一索引的等值查询。
• ref_or_null：类似ref，但包含null值判断。
• eq_ref：多表联查场景。
• range：范围查询（如between、like、>、<）。
  限制
• 只能作用于辅助索引（非聚集索引）。