SQL优化——MySQL中执行计划的使用

目录

1 执行计划是什么

2  在mysql中查看执行计划

2.1 准备数据

2.2 查看执行计划

3 详细说明执行计划结果各个字段的含义

3.1 id

3.2 select_type

3.3 table

3.4 type

3.5 possible_keys

3.6 key

3.7 key_len

3.8 ref

3.9 rows

4.0 Extra

---

1 执行计划是什么

        我们将一个查询的SQL在数据库中执行后，会得到我们想要的结果数据，但我们并不知道数据库是如何去解析这个SQL，也不知道我们预想中应该起作用的索引有没有生效。此时，有一个可视化界面展示給用户这方面的信息就很重要了，而执行计划就以一个表格的形式展示了这些我们想知道的信息。

 

2  在mysql中查看执行计划

2.1 准备数据

       这里创建两张表，user_info与role_info，以user_info的role_id字段与role_info表的id字段关联，建表语句如下。

CREATE TABLE `user_info` (
  `id` bigint(32) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `address` varchar(32) DEFAULT NULL,
  `nick_name` varchar(32) DEFAULT NULL,
  `role_id` bigint(32) DEFAULT NULL,
  `create_time` date DEFAULT NULL,
  `update_time` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age_nick` (`name`,`age`,`nick_name`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE,
  KEY `idx_nick_name` (`nick_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

CREATE TABLE `role_info` (
  `id` bigint(32) NOT NULL DEFAULT '0',
  `name` varchar(32) DEFAULT NULL,
  `create_time` date DEFAULT NULL,
  `update_time` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.2 查看执行计划

在mysql中只要在任意查询语句前面加上explain或者desc命令就可以查看该sql的执行计划，如下图所示。

 

3 详细说明执行计划结果各个字段的含义

        查询SQL执行计划后，显示的是一个类似数据表的结果集，包含10列，下面详细介绍这10个字段的意义。

3.1 id

        涉及多表查询的SQL，各个表的执行会有顺序，这里的id决定了执行的顺序。使用explain得多多行数据，在这里id值大的先执行；id值相同时，表示执行的顺序是由上至下。下面两张图展示的，一为内连接、二为包含子查询的sql。

3.2 select_type

表示查询的类型，就是说这个SQL是单表查询或者子查询、连接查询等。select_type取值为一下几种，查询的效率由上至下是越来越慢的。

SIMPLE：查询中不包含子查询或者UNION，通常是单表查询，为查询速度最快的查询；

PRIMARY：查询中包含子查询的sql，在最外层查询的select_type会为此，且只有一个；

SUBQUERY：子查询语句；

DERIVED：from子句中出现的子查询，该结果存在于临时表中；

UNION:：union连接两个select查询，第二个SELECT出现在UNION之后，标记为UNION；

UNION RESULT： 从UNION结果中进行SELECT，因其不参与对数据表的操作，只是临时表，所以id为null。

3.3 table

        显而易见，table列表示的是查询的是哪张表。

3.4 type

       type是执行计划中的一个重要指标，表示访问类型，跟索引有很大的关系，简单说就是一个查询是扫描了全表还是索引起到了它该有的作用。大致有以下几个值，从上至下表示效率由快至慢。一般来说在进行优化时，需保证至少达到range级别，最好达到ref级别。

NULL：不访问表，直接返回结果，比如SELECT 2 FROM DUAL;

system：访问的表只有一行数据或是空表；

const：通过索引一次就查到结果，只返回一条记录，通常是指通过主键查找或者唯一索引；

eq_ref：多表关联查询，且查出的记录只有一条，常出现在主键查询或者唯一索引；

ref：扫描非唯一性索引，返回的结果可能会有多行。

range：走索引，在WHERE之后使用BETWEEN, <,>,in等操作；

index：遍历了整个索引树，性能不高；

all：遍历全表。

3.5 possible_keys

        显示可能应用在该表的索引。

3.6 key

        显示实际走的索引，若为null则表示没有走索引。

3.7 key_len

        显示索引使用的字节数，在不损失精度的条件下，该字段值越小越好。

3.8 ref

        通常在索引生效时，它的值是const。

3.9 rows

        显示查询扫描行的数量。

4.0 Extra

        显示以上字段没有展示出来的信息，需关注的值有以下几种。

      （1）using filesort：按“文件排序”；

      （2）using temporary： 用到了临时表存储结果；

      （3）using index：用到了索引，效率较好。

        当出现using filesort或者using temporay时效率时这个sql的效率是比较低的，如果数据量大，需要优化。

        在前面我创建了一张user_info表，这张表的主键是id，age字段上有索引，address字段上没建索引。

      （1）首先我以id进行排序，查看它的执行计划。

explain SELECT * FROM user_info ORDER BY id;

       Extra字段的值为null，这是正常的。

      （2）我用没有建立索引的字段address字段进行排序。

explain SELECT * FROM user_info ORDER BY address;

        此时Extra字段的值为Using filesort，可以考虑对其优化。

      （3）使用建立了索引的字段age进行排序。

explain SELECT * FROM user_info ORDER BY age;

        此时Extra字段值依然为Using filesort

      （4）只查询索引字段。

explain SELECT age FROM user_info ORDER BY age;

        此时Extra字段值为Using index，用到了索引，是高效的。

        using temporary在使用group by语句的时候会出现，解决的办法也类似，给字段加索引。