MySQL|explain 执行计划

为了知道 SQL 的效率执行的怎么样,经常使用 explain 查看 SQL 的具体执行过程,以加快 SQL 的执行效率。

explain 语句官方说明文档:https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

数据库版本 MySQL 8.0

explain 使用示例

使用 explain 对 SQL 进行分析,会得到一个或多个结果。

image.png

explain 字段含义

image.png

  • id

    • id 相同表示再同一组,同一组中的查询从上往下执行
    • id 不同,可能存在子查询,值越大优先级越高,则先执行
  • select_type 用来查看查询的类型,是普通查询、联合查询或子查询

image.png

  • table 当前查询正在访问的表,表名或别名

  • partitions 分区信息

  • type 指明查询访问的类型。

  • possible_keys 显示可能应用在这张表中的索引,一个或多个,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用

  • key 实际使用的索引,如果为 null,则没有使用索引,查询中若使用了覆盖索引,则该索引和查询的 select 字段重叠

  • key_len 表示索引中使用的字节数,可以通过 key_len 计算查询中使用的索引长度,在不损失精度的情况下长度越短越好

  • ref 显示索引的哪一列被使用了,如果可能的话,是一个常数

  • rows 根据表的统计信息及索引使用情况,大致估算出找出所需记录需要读取的行数,此参数很重要,直接反应的 sql 找了多少数据,在完成目的的情况下越少越好

  • filtered 过滤比例,使用 distinct 关键字,会进行过滤,filterd =(过滤后的记录数/过滤前的记录数)* 100

  • Extra 包含额外的信息,指明当前查询使用索引的情况

type 字段

type 显示查询访问的类型,它有许多取值,在开发中应该尽量使查询类型处在ref,否则至少保持在range

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • system 只有一条记录的表(等于系统表)
  • const 通过主键查询出一条记录
-- 通过主键查
SELECT * FROM tbl_name WHERE primary_key=1;

-- 通过联合主键查,须满足左匹配原则
SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;
  • eq_ref
    • 查询使用的所有列覆盖到 PRIMARY KEY 或 UNIQUE 非空字段
    • where 中索引列使用 = 进行比较
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;
  • ref 表示使用的是非主键和 unique 索引,一般是普通索引进行查询

  • fulltext 使用全文索引

  • ref_or_null 索引覆盖,普通索引做 IS NULL 判断(普通索引可以为 NULL)

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;
  • index_merge 在查询过程中多个索引组合使用,仍然满足索引覆盖
SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);
  • unique_subquery 该连接类型类似与 index_subquery,使用的是唯一索引

  • index_subquery 利用索引来关联子查询,不再扫描全表

-- student表中的s_id为主键,score中的索引为普通索引
select s_name from student WHERE s_id in (SELECT s_id FROM score);
  • range 表示利用索引查询的时候限制了范围,在指定范围内进行查询,这样避免了 index 的全索引扫描,适用的操作符: =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()
-- 使用以下
SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
  • index 索引覆盖且对整个索引进行扫描

    • 不满足左匹配原则
  • ALL 全表扫描

    • 索引不覆盖
SELECT * FROM table_name

总结

  • 通过了解 explain 可以知道 SQL 的执行情况,如果查询类型(select_type), 索引使用情况(key、type、possible_key),查询的记录数(rows)等

  • 知道了索引索引在什么时候被选择和使用对 SQL 优化和表设计有很多帮助

编写 SQL 总结:

  1. 尽量不适用子查询(subquery),含有子查询的查询多生成一次查询,下面是一个例子,它们相同的功能

image.png

image.png

  1. 查询是尽量保持索引覆盖

  2. 不用时 select *, 如果使用,一定会全部扫描

表设计总结:

  1. 两个表相关联的字段,一般都要加索引
  2. 表中常查询的字段和条件字段一般加索引
  3. 经常房在一块查询的字段可以考虑组合索引(combination index)

(完)