MySQL怎样使用Explain分析查询 执行计划解读与优化切入点

mysql的explain语句是优化查询性能的关键工具，通过在select语句前添加explain关键字，可获取查询执行计划并分析性能瓶颈。1. id列显示查询标识符，数值越大优先级越高；2. select_type表示查询类型，如simple、primary、subquery等；3. type列反映访问类型，从最优到最差依次为system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、all，应尽量避免all；4. possible_keys和key分别表示可能使用和实际使用的索引；5. rows列显示mysql估计扫描的行数，值越小越好；6. extra列包含额外信息，如using index（覆盖索引）、using where、using temporary、using filesort等，应避免filesort和temporary；7. 优化策略包括避免全表扫描、减少filesort、利用覆盖索引、优化连接操作、避免临时表、合理使用索引等。此外，慢查询日志可通过配置开启，并使用mysqldumpslow或pt-query-digest工具分析，结合explain进一步优化查询性能。

MySQL的EXPLAIN语句是优化查询性能的关键工具。它能让你洞悉MySQL如何执行你的SQL查询，从而找出潜在的瓶颈并进行优化。简单来说，EXPLAIN就是SQL的X光机，帮你透视查询内部。

解决方案

要使用EXPLAIN，只需在你的SELECT语句前加上EXPLAIN关键字即可。例如：

EXPLAIN SELECT * FROM users WHERE age > 25 AND city = 'New York';

执行这条语句后，MySQL会返回一个结果集，其中包含了关于查询执行计划的各种信息。接下来，我们需要解读这些信息，找到优化的切入点。

理解EXPLAIN结果的关键列

• id: 查询的标识符。如果查询包含子查询或UNION，则会有多个id。数值越大，优先级越高，越先执行。
• select_type: 查询的类型。常见的类型包括：
  • SIMPLE: 简单查询，不包含子查询或UNION。
  • PRIMARY: 最外层的SELECT查询。
  • SUBQUERY: 子查询。
  • DERIVED: 在FROM子句中的子查询。
  • UNION: UNION语句中的第二个或后面的SELECT查询。
  • UNION RESULT: UNION的结果。
• table: 查询访问的表名。
• partitions: 查询涉及到的分区。
• type: 访问类型，表示MySQL如何查找表中的行。这是EXPLAIN结果中最重要的一列，因为它反映了查询的效率。常见的类型包括（从最佳到最差）：
  • system: 表中只有一行记录，通常是系统表。
  • const: 使用唯一索引或主键，只返回一行记录。
  • eq_ref: 使用唯一索引或主键，关联查询时只返回一行记录。
  • ref: 使用非唯一索引，返回匹配某个单独值的所有行。
  • fulltext: 使用全文索引。
  • ref_or_null: 类似于ref，但是MySQL必须在初次查找的结果里找出null条目。
  • index_merge: 使用多个索引合并来查找行。
  • unique_subquery: 在IN子查询中使用唯一索引。
  • index_subquery: 在IN子查询中使用非唯一索引。
  • range: 在索引上进行范围查找，例如BETWEEN、>、。
  • index: 全索引扫描，扫描整个索引树。
  • ALL: 全表扫描，扫描整个表。应该尽量避免ALL类型。
• possible_keys: MySQL可能使用的索引。
• key: MySQL实际使用的索引。如果为NULL，表示没有使用索引。
• key_len: 索引的长度，表示MySQL使用的索引的字节数。
• ref: 显示索引的哪一列被使用了，通常是一个常量值。
• rows: MySQL估计需要扫描的行数。这个值越小越好。
• filtered: 表示经过WHERE条件过滤后剩余的百分比。
• Extra: 包含关于MySQL如何执行查询的额外信息。一些常见的Extra值包括：
  • Using index: 使用覆盖索引，不需要回表查询。
  • Using where: 使用WHERE子句过滤结果。
  • Using temporary: 使用临时表来存储中间结果，通常发生在ORDER BY或GROUP BY语句中。
  • Using filesort: 使用文件排序，而不是索引排序，通常性能较差。
  • Using join buffer (Block Nested Loop): 使用连接缓冲区，通常发生在没有索引的连接操作中。
  • Impossible WHERE noticed after reading const tables: WHERE子句总是false，导致没有查到数据。
  • Select tables optimized away: 使用某些聚合函数（例如MIN或MAX）来访问仅仅使用索引的表时能被优化。

如何根据EXPLAIN结果进行优化

1. 避免全表扫描 (type = ALL): 这是最常见的优化目标。通过添加合适的索引，可以避免全表扫描。
2. 优化filesort: filesort通常表示性能瓶颈。可以尝试添加索引来避免文件排序。确保ORDER BY子句中的列包含在索引中。
3. 减少扫描的行数 (rows): 扫描的行数越多，查询效率越低。可以通过优化查询条件、使用更合适的索引来减少扫描的行数。
4. 利用覆盖索引 (Using index): 覆盖索引是指索引包含了查询所需的所有列，不需要回表查询。可以显著提高查询性能。
5. 优化连接操作: 确保连接操作使用的列有索引。避免使用没有索引的连接操作，这会导致Using join buffer。
6. 避免使用临时表 (Using temporary): 临时表会增加查询的开销。可以尝试优化查询语句或添加索引来避免使用临时表。
7. 分析WHERE子句: 确保WHERE子句中的条件能够充分利用索引。避免在WHERE子句中使用函数或表达式，这可能会导致索引失效。

MySQL的索引类型有哪些？它们有什么区别？

摄图AI

摄图网旗下AI视觉创作平台

下载

MySQL支持多种索引类型，不同的索引类型适用于不同的场景。常见的索引类型包括：

• B-Tree 索引: 这是MySQL中最常用的索引类型。B-Tree 索引适用于全值匹配、范围查询、前缀匹配等场景。它可以用于=、>、、BETWEEN、LIKE等操作符。
• Hash 索引: Hash 索引使用哈希函数将索引列的值映射到一个哈希码，然后将哈希码存储在索引中。Hash 索引只适用于精确匹配 (=)，不支持范围查询。Memory 存储引擎默认使用 Hash 索引。
• Fulltext 索引: Fulltext 索引用于全文搜索，可以查找包含指定关键词的文本。Fulltext 索引适用于MATCH AGAINST操作符。
• 空间索引 (R-Tree): 空间索引用于存储空间数据，例如地理位置信息。空间索引适用于空间查询，例如查找附近的地点。
• 聚簇索引 (Clustered Index): 聚簇索引决定了表中数据的物理存储顺序。InnoDB 存储引擎使用聚簇索引。如果没有显式定义主键，InnoDB会选择一个唯一的非空索引作为聚簇索引，如果不存在这样的索引，InnoDB会隐式创建一个自增的聚簇索引。
• 二级索引 (Secondary Index): 也称为非聚簇索引。二级索引存储索引列的值以及指向聚簇索引的指针。当查询使用二级索引时，MySQL首先在二级索引中查找匹配的行，然后根据指针回表查询聚簇索引中的数据。

B-Tree 索引是最通用的索引类型，适用于大多数场景。Hash 索引适用于精确匹配，但不支持范围查询。Fulltext 索引适用于全文搜索。空间索引适用于空间数据。聚簇索引决定了数据的物理存储顺序，二级索引用于辅助查询。选择合适的索引类型可以显著提高查询性能。

怎样处理慢查询日志？

MySQL的慢查询日志记录了执行时间超过long_query_time秒的SQL查询。分析慢查询日志可以帮助你找到需要优化的查询。

1. 开启慢查询日志: 在MySQL配置文件 (例如 my.cnf 或 my.ini) 中，设置以下参数：

   slow_query_log = 1
   slow_query_log_file = /path/to/slow_query.log
   long_query_time = 2
   log_output = FILE

   • slow_query_log: 开启慢查询日志。
   • slow_query_log_file: 指定慢查询日志文件的路径。
   • long_query_time: 设置慢查询的阈值，单位为秒。
   • log_output: 指定日志输出方式，可以设置为 FILE 或 TABLE。

   重启MySQL服务使配置生效。

2. 分析慢查询日志: 可以使用 mysqldumpslow 工具来分析慢查询日志。例如：

   mysqldumpslow -s t -t 10 /path/to/slow_query.log

   • -s t: 按照查询时间排序。
   • -t 10: 显示前10个慢查询。

   mysqldumpslow 可以帮助你找到执行时间最长的查询、出现频率最高的查询等。

3. 使用 pt-query-digest: pt-query-digest 是 Percona Toolkit 中的一个工具，可以更详细地分析慢查询日志。它可以提供更全面的查询统计信息，例如查询的平均执行时间、最大执行时间、查询次数、查询使用的索引等。

   pt-query-digest /path/to/slow_query.log

4. 优化慢查询: 根据慢查询日志的分析结果，找到需要优化的查询。使用 EXPLAIN 分析查询的执行计划，找出性能瓶颈，并进行优化。可以尝试添加索引、优化查询语句、重写SQL等方式来提高查询性能。

处理慢查询日志是一个持续的过程。定期分析慢查询日志，并对慢查询进行优化，可以有效地提高MySQL数据库的性能。