FantasticMao 技术笔记
BlogGitHub
  • README
  • C & Unix
    • C
      • 《C 程序设计语言》笔记
      • C 语言中的陷阱
      • CMake 示例
      • GNU make
      • LLVM Clang
      • Nginx 常用模块
      • Vim 常用命令
    • Unix-like
      • 《深入理解计算机系统》笔记
      • 《UNIX 环境高级编程》笔记 - UNIX 基础知识
      • 《UNIX 环境高级编程》笔记 - 文件 IO
      • 《UNIX 环境高级编程》笔记 - 标准 IO 库
      • 《鳥哥的 Linux 私房菜》笔记 - 目录配置
      • 《鳥哥的 Linux 私房菜》笔记 - 认识与学习 bash
      • 《鳥哥的 Linux 私房菜》笔记 - 任务管理
      • OpenWrt 中的陷阱
      • iptables 工作机制
  • Go
    • 《A Tour of Go》笔记
    • Go vs C vsJava
    • Go 常用命令
    • Go 语言中的陷阱
  • Java
    • JDK
      • 《Java 并发编程实战》笔记 - 线程池的使用
      • 设计模式概览
      • 集合概览
      • HashMap 内部算法
      • ThreadLocal 工作机制
      • Java Agent
    • JVM
      • 《深入理解 Java 虚拟机》笔记 - Java 内存模型与线程
      • JVM 运行时数据区
      • 类加载机制
      • 垃圾回收算法
      • 引用类型
      • 垃圾收集算法
      • 垃圾收集器
    • Spring
      • Spring IoC 容器扩展点
      • Spring Transaction 声明式事务管理
      • Spring Web MVC DispatcherServlet 工作机制
      • Spring Security Servlet 实现原理
    • 其它
      • 《Netty - One Framework to rule them all》演讲笔记
      • Hystrix 设计与实现
  • JavaScript
    • 《写给大家看的设计书》笔记 - 设计原则
    • 《JavaScript 权威指南》笔记 - jQuery 类库
  • 数据库
    • ElasticSearch
      • ElasticSearch 概览
    • HBase
      • HBase 数据模型
    • Prometheus
      • Prometheus 概览
      • Prometheus 数据模型和指标类型
      • Prometheus 查询语法
      • Prometheus 存储原理
      • Prometheus vs InfluxDB
    • Redis
      • 《Redis 设计与实现》笔记 - 简单动态字符串
      • 《Redis 设计与实现》笔记 - 链表
      • 《Redis 设计与实现》笔记 - 字典
      • 《Redis 设计与实现》笔记 - 跳跃表
      • 《Redis 设计与实现》笔记 - 整数集合
      • 《Redis 设计与实现》笔记 - 压缩列表
      • 《Redis 设计与实现》笔记 - 对象
      • Redis 内存回收策略
      • Redis 实现分布式锁
      • Redis 持久化机制
      • Redis 数据分片方案
      • 使用缓存的常见问题
    • MySQL
      • 《高性能 MySQL》笔记 - Schema 与数据类型优化
      • 《高性能 MySQL》笔记 - 创建高性能的索引
      • 《MySQL Reference Manual》笔记 - InnoDB 和 ACID 模型
      • 《MySQL Reference Manual》笔记 - InnoDB 多版本
      • 《MySQL Reference Manual》笔记 - InnoDB 锁
      • 《MySQL Reference Manual》笔记 - InnoDB 事务模型
      • B-Tree 简述
      • 理解查询执行计划
  • 中间件
    • gRPC
      • gRPC 负载均衡
    • ZooKeeper
      • ZooKeeper 数据模型
    • 消息队列
      • 消息积压解决策略
      • RocketMQ 架构设计
      • RocketMQ 功能特性
      • RocketMQ 消息存储
  • 分布式系统
    • 《凤凰架构》笔记
    • 系统设计思路
    • 系统优化思路
    • 分布式事务协议:二阶段提交和三阶段提交
    • 分布式系统的技术栈
    • 分布式系统的弹性设计
    • 单点登录解决方案
    • 容错,高可用和灾备
  • 数据结构和算法
    • 一致性哈希
    • 布隆过滤器
    • 散列表
  • 网络协议
    • 诊断工具
    • TCP 协议
      • TCP 报文结构
      • TCP 连接管理
由 GitBook 提供支持
在本页
  • 使用 EXPLAIN 来优化查询
  • EXPLAIN 输出格式
  • EXPLAIN 输出的列
  • EXPLAIN 关联类型
  • EXPLAIN 额外信息
  • 参考资料
  1. 数据库
  2. MySQL

理解查询执行计划

依据表、列、索引和 WHERE 子句中的条件,MySQL 优化器会选择使用多种技术来提高 SQL 语句的查询效率。例如,当对一张很大的表进行查询时,MySQL 不必查询所有的行。当对多张表进行联合查询时,MySQL 不必考虑所有的行组合情况。MySQL 优化器选择执行最有效的查询方案的一系列操作被称为「查询执行计划」,也被称为 EXPLAIN 计划。

使用 EXPLAIN 来优化查询

EXPLAIN 语句提供有关于 MySQL 如何执行 SQL 语句的信息:

  • EXPLAIN 对 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句有效;

  • 当 EXPLAIN 被用于可解释的 SQL 语句时,MySQL 会展示来自优化器中的有关语句执行计划的信息。这也意味着,MySQL 会解释它将如何处理该语句,包括有关如何连接表和以何种顺序进行连接;

  • 当 EXPLAIN 被用于 FOR CONNECTION connection_id,而不是可解释的 SQL 语句时,MySQL 会展示该连接中正在执行的语句执行计划的信息;

  • 对于 SELECT 语句,EXPLAIN 会提供额外的执行计划信息,这些信息可以通过 SHOW WARNINGS 来显示;

  • EXPLAIN 对于涉及分区表的检查查询来说非常有用;

  • FORMAT 选项可以用于选择输出格式,值为 TRADITIONAL 时表示以表格的格式展示,值为 JSON 时表示以 JSON 的格式展示。

通过使用 EXPLAIN,开发者可以知道应该在表的何处添加索引,以便于 SQL 语句通过使用索引来更快地查找数据。开发者还可以使用 EXPLAIN 来检查 MySQL 优化器是否以最佳的顺序来连接表。

EXPLAIN 输出格式

EXPLAIN 会为 SELECT 语句中所使用的每个表返回一行信息。其中这些表的排列顺序与 MySQL 在处理 SQL 语句时所读取的顺序相同。这意味着 MySQL 会先读取第一张表中的行,然后在第二张表中查找匹配的行,然后在第三张表中查找,以此类推。

EXPLAIN 输出的列

EXPLAIN 输出的每行信息都对应了一张表,每行都会包含如下的信息:

  • id 表示 SELECT 的标识符,是一次查询当中的 SELECT 语句的序号。MySQL 将 SELECT 查询分为简单和复杂类型,复杂类型可以分成三类:简单子查询、派生查询(在 FROM 子句中的子查询)、UNION 查询;

  • select_type 表示 SELECT 的类型,部分值的含义如下表:

    • SIMPLE 表示简单 SELECT(没有使用 UNION 查询或者子查询);

    • PRIMARY 表示复杂查询中的最外层的 SELECT;

    • UNION 表示 UNION 查询中的第二个或者之后的 SELECT;

    • DEPENDENT UNION 表示 UNION 查询中的第二个或者之后的 SELECT,依赖于外部查询;

    • UNION RESULT 表示 UNION 查询的结果;

    • SUBQUERY 表示子查询中的第一个查询;

    • DEPENDENT SUBQUERY 表示子查询中的第一个查询,依赖于外部查询;

    • ......

  • table 表示该行查询所涉及的表名;

  • partitions 表示该行查询所涉及的分区;

  • type 表示 MySQL 关联该行数据的类型,更多内容请见 [关联类型](#EXPLAIN 关联类型);

  • possible_keys 表示该行查询可以使用的索引;

  • key 表示该行查询真实使用的索引;

  • key_len 表示该行查询真实使用的索引的字节数;

  • ref 表示之前的表在 key 列所记录的索引中查找值时使用的列或者常量;

  • rows 表示该行查询所涉及的行的总数估值;

  • filtered 表示表中符合某个条件(WHERE 子句或者关联条件)的行的百分比估值;

  • Extra 包含了 Mysql 解析该查询的额外信息,更多内容请见 [额外信息](#EXPLAIN 额外信息)。

EXPLAIN 关联类型

EXPLAIN 输出的 type 列描述了表是如何被关联的。下文列出了从最好到最差情况的关联类型:

  • system 是一种当表中只有一行数据时的特殊的 const 类型;

  • const 表示表中最多只有一行数据可以被匹配,并且该行需要在查询刚开始就被读取。由于查询结果只会有一行,所以在后续的优化过程中,优化器会将该行中列的值视为常量。当将 PRIMARY KEY 或者 UNIQUE Index 与常量进行比较的时候,MySQL 会使用 const 类型;

  • eq_ref 表示当前查询只会返回一行。当使用 PRIMARY KEY 或者 UNIQUE NOT NULL Index 进行查找时,MySQL 会使用 eq_ref 类型;

  • ref 表示当前查询会返回所有与索引值匹配的行。当仅使用索引的最左前缀或者索引不是 PRIMARY KEY 或者 UNIQUE Index 时,MySQL 会使用 ref 类型;

  • fulltext 表示当前查询使用了 FULLTEXT 索引;

  • ref_or_null 与 ref 类型相似,除了当前查询会额外包含 NULL 值以外;

  • index_merge 表示当前查询使用了索引合并优化;

  • unique_subquery 表示当前查询会在 IN 子句中,将 eq_ref 替换为某些形式的子查询;

  • index_subquery 与 unique_subquery 类型相似,除了它是用于处理非唯一索引以外;

  • range 表示当前查询使用了索引来选择行,并且只会返回检索范围内的行。当在索引上使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN() 条件时,MySQL 会使用 range 类型;

  • index 与 ALL 类型相似,除了当前查询会扫描索引树以外。

  • all 表示全表扫描。

EXPLAIN 额外信息

EXPLAIN 输出的 Extra 列描述了 MySQL 解析该查询的额外信息。如果开发者想要使查询更快地执行,则需要留意 Extra 列中出现 Using filesort 和 Using temporary 的情况。

Extra 列最重要的几个值如下:

  • Using index 表示 MySQL 将使用覆盖索引,以避免访问表;

  • Using Where 表示 MySQL 将在存储引擎检索后再进行过滤;

  • Using temporary 表示 MySQL 在对查询结果排序时会使用一个临时表;

  • Using filesort 表示 MySQL 会对查询结果使用一个外部索引排序,而不是按照索引次序从表中读取行。

参考资料

最后更新于1年前

EXPLAIN Output Format
《高性能MySQL》