MySql性能优化二

什么是执行计划

        有了慢查询语句后，就要对语句进行分析。一条查询语句在经过 MySQL 查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执 行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，

　　对于每 个表采用什么访问方法来具体执行查询等等。EXPLAIN 语句来帮助我们查看某个 查询语句的具体执行计划，我们需要搞懂 EPLATNEXPLAIN 的各个输出项都是干嘛 使的，从而可以有针对性的提升我们查询语句的性能。

        通过使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析查询语句或是表结构的性能瓶颈，总的 来说通过 EXPLAIN 我们可以：

1.         表的读取顺序
2. 　　数据读取操作的操作类型
3. 　　哪些索引可以使用
4. 　　哪些索引被实际使用
5. 　　表之间的引用
6. 　　每张表有多少行被优化器查询

执行计划的语法

        执行计划的语法其实非常简单： 在 SQL 查询的前面加上 EXPLAIN 关键字就 行。比如：EXPLAIN select * from actor;

执行计划详解

         mysql> explain select * from actor;
        +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
        | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
        +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
        | 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 200 | 100.00 | NULL |
        +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+       

        id： 在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id

        select_type： SELECT 关键字对应的那个查询的类型

        table：表名

        partitions：匹配的分区信息

        type：针对单表的访问方法

        possible_keys：可能用到的索引

        key：实际上使用的索引

        key_len：实际使用到的索引长度

        ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息

        rows：预估的需要读取的记录条数 f

        iltered：某个表经过搜索条件过滤后剩余记录条数的百分比

        Extra：—些额外的信息

        下面详细分析上面字段的含义

• 　　table

　　　　不论我们的查询语句有多复杂，里边包含了多少个表，到最后也是需要对每 个表进行单表访问的，MySQL 规定 EXPLAIN 语句输出的每条记录都对应着某个单                 表的访问方法，该条记录的 table 列代表着该表的表名。

                mysql> explain select city_id, city, country from city inner join country on city.country_id=country.country_id;
                +----+-------------+---------+------------+------+-------------------+-------------------+---------+---------------------------+------+----------+-------+
                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                +----+-------------+---------+------------+------+-------------------+-------------------+---------+---------------------------+------+----------+-------+
                | 1 | SIMPLE | country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 109 | 100.00 | NULL |
                | 1 | SIMPLE | city | NULL | ref | idx_fk_country_id | idx_fk_country_id | 2 | sakila.country.country_id | 5 | 100.00 | NULL |
                +----+-------------+---------+------------+------+-------------------+-------------------+---------+---------------------------+------+----------+-------+

•         id

　　        比较简单的查询 语句里只有一个 SELECT 关键字，

　　        但是下边两种情况下在一条查询语句中会出现多个 SELECT 关键字:

　　　　        1、查询中包含子查询的情况 比如下边这个查询语句中就包含 2 个 SELECT 关键字:                    

                                  select * from film_actor where actor_id in(select actor_id from actor);　

　　　　        2、查询中包含 UNION 语句的情况 比如下边这个查询语句中也包含 2 个 SELECT

                            关键字:explain select * from film_actor where film_id=1 union select * from film_actor where film_id=2;

　　　　              查询语句中每出现一个 SELECT 关键字, MySQL 就会为它分配一个唯一的 id 值。

                            这个 id 值就是 EXPLAIN 语句的第一个列。　

           单 SELECT 关键字

　　　　EXPLAIN 的结果中也就 只有一条 id 列为 1 的记录∶

                mysql> explain select * from film_actor where film_id=1;
                +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
                | 1 | SIMPLE | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | const | 10 | 100.00 | NULL |
                +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+

        连接查询

　　        每个表都会对应一条记录，但是这些记录的 id 值都是相同的，

                mysql> explain select city_id, city, country from city inner join country on city.country_id=country.country_id;
                +----+-------------+---------+------------+------+-------------------+-------------------+---------+---------------------------+------+----------+-------+
                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                +----+-------------+---------+------------+------+-------------------+-------------------+---------+---------------------------+------+----------+-------+
                | 1 | SIMPLE | country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 109 | 100.00 | NULL |
                | 1 | SIMPLE | city | NULL | ref | idx_fk_country_id | idx_fk_country_id | 2 | sakila.country.country_id | 5 | 100.00 | NULL |
                +----+-------------+---------+------------+------+-------------------+-------------------+---------+---------------------------+------+----------+-------+

        包含子查询

　　        每个 SELECT 关键字都会对应一个唯一的 id 值。查询优化器可能对涉及子查询的查询语句进行 重写，从而转换为连接查询。所以如果我们想知道查询

        优化器对某个包含子查询 的语句是否进行了重写，如果发现两个记录id都是相同的，则是优化器把子查询转换成了连接查询。

                mysql> explain select * from film_actor where actor_id in(select actor_id from actor) OR film_id = 1;
                +----+-------------+------------+------------+-------+----------------+---------------------+---------+------+------+----------+-------------+
                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                +----+-------------+------------+------------+-------+----------------+---------------------+---------+------+------+----------+-------------+
                | 1 | PRIMARY | film_actor | NULL | ALL | idx_fk_film_id | NULL | NULL | NULL | 5462 | 100.00 | Using where |
                | 2 | SUBQUERY | actor | NULL | index | PRIMARY | idx_actor_last_name | 182 | NULL | 200 | 100.00 | Using index |
                +----+-------------+------------+------------+-------+----------------+---------------------+---------+------+------+----------+-------------+

        包含 UNION 子句

　　                对于包含 UNION 子句的查询语句来说，每个 SELECT 关键字对应一个 id 值也 是没错的，还有额外的东西，UNION 子句会把多个查询 的结果集合并起来

                并对结果集中的记录进行去重，怎么去重呢? MySQL 使用的是 内部的临时表。正如上边的查询计划中所示，UNION 子句是为了把 id 为 1 的查 询和id为

                2的查询的结果集合并起来并去重，所以在内部创建了一个名为<union1， 2="">的临时表（就是执行计划第三条记录的table 列的名称)，id 为 NULL 表明、

                这个 临时表是为了合并两个查询的结果集而创建的。 跟UNION 对比起来，UNION ALL 就不需要为最终的结果集进行去重，它只 是单纯的把多个查询的结果

                集中的记录合并成一个并返回给用户，所以也就不需 要使用临时表。所以在包含 UNION ALL 子句的查询的执行计划中，就没有那个 id 为 NULL 的记录，

                如下所示:

                mysql> explain select * from film_actor where film_id=1 union select * from film_actor where film_id=2;
                +----+--------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------+
                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                +----+--------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------+
                | 1 | PRIMARY | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | const | 10 | 100.00 | NULL |
                | 2 | UNION | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | const | 4 | 100.00 | NULL |
                | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
                +----+--------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------+        

                mysql> explain select * from film_actor where film_id=1 union all select * from film_actor where film_id=2;
                +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
                | 1 | PRIMARY | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | const | 10 | 100.00 | NULL |
                | 2 | UNION | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | const | 4 | 100.00 | NULL |
                +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+

•         select_type 列

　　                        一条大的查询语句里边可以包含若干个 SELECT 关键字，每个 SELECT 关键字代表着一个小的查询语句，而每个 SELECT 关键字的 From 子句中都

                        可以包含若干张表（这些表用来做连接查询)，每一张表都对应着 执行计划输出中的一条记录，

　　                        对于在同一个 SELECT 关键字中的表来说，它们的 id 值是相同的。 MySQL 为每一个 SELECT 关键字代表的小查询都定义了一个称之为 、

                        select_type 的属性，意思是我们只要知道了某个小查询的 select_type 属性，就 知道了这个小查询在整个大查询中扮演了一个什么角色，select_type

                         取值如下：

　　　　        SIMPLE：简单的 select 查询,不使用 union 及子查询

　　　　        PRIMARY：最外层的 select 查询

　　　　        UNION：UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果 集

　　　　        UNION RESULT：UNION 结果集

　　　　        SUBQUERY：子查询中的第一个 select 查询,不依赖于外 部查询的结果集

　　　　        DEPENDENT UNION：UNION 中的第二个或随后的 select 查询,依赖于外部查 询的结果集

　　　　        DEPENDENT SUBQUERY：子查询中的第一个 select 查询,依赖于外部查询的 结果集

　　　　        DERIVED： 用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些 子查询, 把结果放在临时表里。

　　　　        MATERIALIZED：物化子查询

　　　　        UNCACHEABLE SUBQUERY： 结果集不能被缓存的子查询,必须重新为外层查 询的每一行进行评估，出现极少。

　　　　        UNCACHEABLE UNION：UNION 中的第二个或随后的 select 查询,属于不可缓 存的子查询，出现极少。

　　        SIMPLE

                        mysql> explain select * from film_actor where film_id=1;
                        +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
                        | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                        +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
                        | 1 | SIMPLE | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | const | 10 | 100.00 | NULL |
                        +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+

                        mysql> explain select city_id, city, country from city inner join country on city.country_id=country.country_id;
                        +----+-------------+---------+------------+------+-------------------+-------------------+---------+---------------------------+------+----------+-------+
                        | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                        +----+-------------+---------+------------+------+-------------------+-------------------+---------+---------------------------+------+----------+-------+
                        | 1 | SIMPLE | country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 109 | 100.00 | NULL |
                        | 1 | SIMPLE | city | NULL | ref | idx_fk_country_id | idx_fk_country_id | 2 | sakila.country.country_id | 5 | 100.00 | NULL |
                        +----+-------------+---------+------------+------+-------------------+-------------------+---------+---------------------------+------+----------+-------+

                PRIMARY

　　                对于包含 UNION、UNION ALL 或者子查询的大查询来说，它是由几个小查询 组成的，其中最左边的那个查询的 select_type 值就是 PRIMARY　　

                        mysql> explain select * from film_actor where film_id=1 union select * from film_actor where film_id=2;
                        +----+--------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------+
                        | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                        +----+--------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------+
                        | 1 | PRIMARY | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | const | 10 | 100.00 | NULL |
                        | 2 | UNION | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | const | 4 | 100.00 | NULL |
                        | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
                        +----+--------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------+

                UNION

                                对于包含 UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成的， 其中除了最左边的那个小查询以外，其余的查询的 select_type

                         值就是 UNION，可以对比上一个例子的效果。

                UNION RESULT

                        MySQL 选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查 询的 select_type 就是 UNION RESULT。

                SUBQUERY

　　                        如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子 查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案

                        来执行该 子查询时，该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是SUBQUERY，比如下边这个查询:

                        mysql> explain select * from film_actor where actor_id in(select actor_id from actor) OR film_id = 1;
                        +----+-------------+------------+------------+-------+----------------+---------------------+---------+------+------+----------+-------------+
                        | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                        +----+-------------+------------+------------+-------+----------------+---------------------+---------+------+------+----------+-------------+
                        | 1 | PRIMARY | film_actor | NULL | ALL | idx_fk_film_id | NULL | NULL | NULL | 5462 | 100.00 | Using where |
                        | 2 | SUBQUERY | actor | NULL | index | PRIMARY | idx_actor_last_name | 182 | NULL | 200 | 100.00 | Using index |
                        +----+-------------+------------+------------+-------+----------------+---------------------+---------+------+------+----------+-------------+

                        由于 select_type 为 SUBQUERY 的子查询由于 会被物化，所以只需要执行一遍。

                        TIPS

　　                        semi-join：半连接优化技术，本质上是把子查询上拉到父查询中，与父查询 的表做 join 操作。关键词是“上拉”。对于子查询，其子查询部分相对于

                        父表的 每个符 合条件的元组，都要把子查询执行一轮。效率低下。用半连接操作优化子 查询，是把子查询上拉到父查询中，这样子查询的表和父查询

                        中的表是并列关系， 父表的 每个符合条件的元组，只需要在子表中找符合条件的元组即可。简单来说， 就是通过将子查询上拉对父查询中的数据进行

                        筛选，以使获取到最少量的足以对 父查询记录进行筛选的信息就足够了。 子查询物化：子查询的结果通常缓存在内存或临时表中。 关联/相关子查询：

                        子查询的执行依赖于外部查询。多数情况下是子查询的 WHERE 子句中引用了外部查询的表。自然“非关联/相关子查询”的执行则不依 赖与外部的查询。

                DEPENDENT UNION、DEPENDENT SUBQUERY

　　                在包含 UNION 或者 UNION ALL 的大查询中，如果各个小查询都依赖于外层 查询的话，那除了最左边的那个小查询之外，其余的小查询的 select_type

                         的 就是 DEPENDENT UNION

                        mysql> explain select film_id from film_actor where film_id in(select film_id from film_actor where film_id=3 union select film_id from film_actor where film_id=2);
                        +----+--------------------+------------+------------+-------+------------------------+----------------+---------+-------+------+----------+--------------------------+
                        | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                        +----+--------------------+------------+------------+-------+------------------------+----------------+---------+-------+------+----------+--------------------------+
                        | 1 | PRIMARY | film_actor | NULL | index | NULL | idx_fk_film_id | 2 | NULL | 5462 | 100.00 | Using where; Using index |
                        | 2 | DEPENDENT SUBQUERY | film_actor | NULL | ref | PRIMARY,idx_fk_film_id | idx_fk_film_id | 2 | const | 5 | 100.00 | Using where; Using index |
                        | 3 | DEPENDENT UNION | film_actor | NULL | ref | PRIMARY,idx_fk_film_id | idx_fk_film_id | 2 | const | 4 | 100.00 | Using where; Using index |
                        | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
                        +----+--------------------+------------+------------+-------+------------------------+----------------+---------+-------+------+----------+--------------------------+

                DERIVED

　　                对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的 select_type 就是 DERIVED。

                        mysql> explain select * from (select actor_id,count(*) as c from film_actor group by actor_id)as a where c>1;
                        +----+-------------+------------+------------+-------+------------------------+---------+---------+------+------+----------+-------------+
                        | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                        +----+-------------+------------+------------+-------+------------------------+---------+---------+------+------+----------+-------------+
                        | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 5462 | 100.00 | NULL |
                        | 2 | DERIVED | film_actor | NULL | index | PRIMARY,idx_fk_film_id | PRIMARY | 4 | NULL | 5462 | 100.00 | Using index |
                        +----+-------------+------------+------------+-------+------------------------+---------+---------+------+------+----------+-------------+

                        从执行计划中可以看出， id 为 2 的记录就代表子查询的执行方式，它的 select_type 是 DERIVED ，说明该子查询是以物化的方式执行的。

                        id 为 1 的记录 代表外层查询，大家注意看它的 table 列显示的是，表示该查询是针 对将派生表物化之后的表进行查询的。

                MATERIALIZED

                        当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查 询进行连接查询时，该子查询对应的 select_type 属性就是 MATERIALIZED。

                        mysql> explain select * from inventory where film_id in(select film_id from film_actor);
                        +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------------+------+----------+-------------+
                        | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                        +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------------+------+----------+-------------+
                        | 1 | SIMPLE | inventory | NULL | ALL | idx_fk_film_id | NULL | NULL | NULL | 4581 | 100.00 | NULL |
                        | 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 2 | sakila.inventory.film_id | 1 | 100.00 | NULL |
                        | 2 | MATERIALIZED | film_actor | NULL | index | idx_fk_film_id | idx_fk_film_id | 2 | NULL | 5462 | 100.00 | Using index |
                        +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------------+------+----------+-------------+

                UNCACHEABLE SUBQUERY、UNCACHEABLE UNION　　

　　                很少见到。

•         partitions

　　                和分区表有关，一般情况下我们的查询语句的执行计划的 partitions 列的值 都是 NULL。

•         type

　　                type 列就表明了这个访问方法/访问类型是个什么 东西，是较为重要的一个指标，结果值从最好到最坏依次是：

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

　　                出现比较多的是 system>const>eq_ref>ref>range>index>ALL 一般来说，得保证查询至少达到 range 级别，最好能达到 ref

　　                system

　　　　                当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory，那么对该表的访问方法就是 system。

　　                const

　　　　                就是当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的 访问方法就是 const。因为只匹配一行数据，所以很快。

                                mysql> explain select actor_id from actor where actor_id=1;
                                +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
                                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                                +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
                                | 1 | SIMPLE | actor | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | Using index |
                                +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

                        eq_ref

　　                        在连接查询中，如果被驱动表时通过主键或者唯一耳机索引累等值匹配的方式访问的则该别驱动表访问方法就是eq_ref

                                mysql> explain select * from staff inner join store on manager_staff_id=staff_id;
                                +----+-------------+-------+------------+--------+--------------------+---------+---------+-------------------------------+------+----------+-------+
                                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                                +----+-------------+-------+------------+--------+--------------------+---------+---------+-------------------------------+------+----------+-------+
                                | 1 | SIMPLE | store | NULL | ALL | idx_unique_manager | NULL | NULL | NULL | 2 | 100.00 | NULL |
                                | 1 | SIMPLE | staff | NULL | eq_ref | PRIMARY | PRIMARY | 1 | sakila.store.manager_staff_id | 1 | 100.00 | NULL |
                                +----+-------------+-------+------------+--------+--------------------+---------+---------+-------------------------------+------+----------+-------+

                        ref

　　                        当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该 表的访问方法就可能是 ref。

                                mysql> explain select city_id,country from city inner join country on city.country_id=country.country_id;
                                +----+-------------+---------+------------+------+-------------------+-------------------+---------+---------------------------+------+----------+-------------+
                                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                                +----+-------------+---------+------------+------+-------------------+-------------------+---------+---------------------------+------+----------+-------------+
                                | 1 | SIMPLE | country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 109 | 100.00 | NULL |
                                | 1 | SIMPLE | city | NULL | ref | idx_fk_country_id | idx_fk_country_id | 2 | sakila.country.country_id | 5 | 100.00 | Using index |
                                +----+-------------+---------+------------+------+-------------------+-------------------+---------+---------------------------+------+----------+-------------+

                        fulltext

　　                        跳过

                        ref_or_null

　　                        有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该 列的值为 NULL 的记录也找出来。

                        index_merge 

　　                        一般情况下对于某个表的查询只能使用到一个索引，在某些场景下可以使用 索引合并的方式来执行查询。

                                mysql> explain select * from rental where inventory_id=1 or customer_id=2;
                                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                                | 1 | SIMPLE | rental | NULL | index_merge | idx_fk_inventory_id,idx_fk_customer_id | idx_fk_inventory_id,idx_fk_customer_id

                                 | 3,2 | NULL | 30 | 100.00 | Using union(idx_fk_inventory_id,idx_fk_customer_id); Using where |

                        unique_subquery

　　                        类似于两表连接中被驱动表的 eg_ref 访问方法，unique _subquery 是针对在 一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询

                                转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划 的type 列的值就是 unique_subquery

                                mysql> explain select * from staff s where s.staff_id in (select manager_staff_id from store) or s.store_id=3;
                                +----+--------------------+-------+------------+-----------------+--------------------+--------------------+---------+------+------+----------+-------------+
                                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                                +----+--------------------+-------+------------+-----------------+--------------------+--------------------+---------+------+------+----------+-------------+
                                | 1 | PRIMARY | s | NULL | ALL | idx_fk_store_id | NULL | NULL | NULL | 2 | 100.00 | Using where |
                                | 2 | DEPENDENT SUBQUERY | store | NULL | unique_subquery | idx_unique_manager | idx_unique_manager | 1 | func | 1 | 100.00

                                | Using index |
                                +----+--------------------+-------+------------+-----------------+--------------------+--------------------+---------+------+------+----------+-------------+

                        index_subquery

                                index_subquery 与 unique_subquery 类似，只不过访问⼦查询中的表时使⽤ 的是普通的索引：

                        range

                                如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法， 一般就是在你的 where 语句中出现了 between、<、>、in 等的查询。

                                这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

                                mysql> explain select * from customer where store_id between 3 and 10;
                                +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
                                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                                +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
                                | 1 | SIMPLE | customer | NULL | range | idx_fk_store_id | idx_fk_store_id | 1 | NULL | 1 | 100.00 | Using index condition |
                                +----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+

                        index

                                当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法 就是 index。

                                mysql> explain select film_id, store_id from inventory;
                                +----+-------------+-----------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
                                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                                +----+-------------+-----------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
                                | 1 | SIMPLE | inventory | NULL | index | NULL | idx_store_id_film_id | 3 | NULL | 4581 | 100.00 | Using index |
                                +----+-------------+-----------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+

                        all

                                最熟悉的全表扫描，将遍历全表以找到匹配的行

                                mysql> explain select * from inventory;
                                +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
                                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                                +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
                                | 1 | SIMPLE | inventory | NULL | ALL | NULL | NULL | NULL | NULL | 4581 | 100.00 | NULL |        
                                +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+

•         possible_keys 与 key

　　　　                在EXPLAIN 语句输出的执行计划中,possible_keys 列表示在某个查询语句中， 对某个表执行单表查询时可能用到的索引有哪些，key 列表示实际

                        用到的索引有 哪些，如果为 NULL，则没有使用索引possible keys 列中的值并不是越多越好，可能使用 的索引越多，查询优化器计算查询成本时就得

                        花费更长时间，所以如果可以的话， 尽量删除那些用不到的索引。

•         key_len

　　                        key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大 长度，

　　                计算方式是这样的： 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就 是该固定值，对于指定字符集的变长类型的索引

                        列来说，比如某个索引列的类型 是 VARCHAR(100)，使用的字符集是 utf8，那么该列实际占用的最大存储空间就 是 100 x 3 = 300 个字节。 如果

                        该索引列可以存储 NULL 值，则 key_len比不可以存储 NULL 值时多 1 个 字节。utf8mb4 是4个字节。对于变长字段来说，都会有 2 个字节的空间

                        来存储该变长列的实际长度。

                        mysql> explain select * from film where film_id=1;
                        +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
                        | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                        +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
                        | 1 | SIMPLE | film | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | NULL |
                        +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

                        mysql> desc film;
                        +----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
                        | Field | Type | Null | Key | Default | Extra |
                        +----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
                        | film_id | smallint unsigned | NO | PRI | NULL | auto_increment |
                        | title | varchar(128) | NO | MUL | NULL | |
                        | description | text | YES | | NULL | |
                        | release_year | year | YES | | NULL | |
                        | language_id | tinyint unsigned | NO | MUL | NULL | |
                        | original_language_id | tinyint unsigned | YES | MUL | NULL | |
                        | rental_duration | tinyint unsigned | NO | | 3 | |
                        | rental_rate | decimal(4,2) | NO | | 4.99 | |
                        | length | smallint unsigned | YES | | NULL | |
                        | replacement_cost | decimal(5,2) | NO | | 19.99 | |
                        | rating | enum('G','PG','PG-13','R','NC-17') | YES | | G | |
                        | special_features | set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') | YES | | NULL | |
                        | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
                        +----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+

                        mysql> explain select film_id,store_id from inventory;
                        +----+-------------+-----------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
                        | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                        +----+-------------+-----------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
                        | 1 | SIMPLE | inventory | NULL | index | NULL | idx_store_id_film_id | 3 | NULL | 4581 | 100.00 | Using index |
                        +----+-------------+-----------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
                        1 row in set, 1 warning (0.00 sec)

                        mysql> desc inventory;
                        +--------------+--------------------+------+-----+-------------------+-----------------------------------------------+
                        | Field | Type | Null | Key | Default | Extra |
                        +--------------+--------------------+------+-----+-------------------+-----------------------------------------------+
                        | inventory_id | mediumint unsigned | NO | PRI | NULL | auto_increment |
                        | film_id | smallint unsigned | NO | MUL | NULL | |
                        | store_id | tinyint unsigned | NO | MUL | NULL | |
                        | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
                        +--------------+--------------------+------+-----+-------------------+-----------------------------------------------+

                        mysql> desc film;
                        +----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
                        | Field | Type | Null | Key | Default | Extra |
                        +----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
                        | film_id | smallint unsigned | NO | PRI | NULL | auto_increment |
                        | title | varchar(128) | NO | MUL | NULL | |
                        | description | text | YES | | NULL | |
                        | release_year | year | YES | | NULL | |
                        | language_id | tinyint unsigned | NO | MUL | NULL | |
                        | original_language_id | tinyint unsigned | YES | MUL | NULL | |
                        | rental_duration | tinyint unsigned | NO | | 3 | |
                        | rental_rate | decimal(4,2) | NO | | 4.99 | |
                        | length | smallint unsigned | YES | | NULL | |
                        | replacement_cost | decimal(5,2) | NO | | 19.99 | |
                        | rating | enum('G','PG','PG-13','R','NC-17') | YES | | G | |
                        | special_features | set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') | YES | | NULL | |
                        | last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
                        +----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
                        13 rows in set (0.00 sec)

                        mysql> explain select * from film where title='test'; //128 * 4 + 2  utf8mb4 是4个字节
                        +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
                        | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                        +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
                        | 1 | SIMPLE | film | NULL | ref | idx_title | idx_title | 514 | const | 1 | 100.00 | NULL |
                        +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

                                执行计划的生成是在 MySQL server 层中的功能，并不是针对具体某个存储 引擎的功能，MySQL 在执行计划中输出 key_len 列主要是

                        为了让我们区分某个使 用联合索引的查询具体用了几个索引列(复合索引有最左前缀的特性，如果复合 索引能全部使用上，则是复合索引字段

                        的索引长度之和，这也可以用来判定复合 索引是否部分使用，还是全部使用)，而不是为了准确的说明针对某个具体存储 引擎存储变长字段的

                        实际长度占用的空间到底是占用 1 个字节还是 2 个字节。

                                Key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长 度。在不损失精确性的情况下，长度越短越好 key_len显示的

                        值为索引字段的最大可能长度，并非实际使用长度，即key_len 是根据表定义计算而得，不是通过表内检索出的。

•         ref

　　                当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const、 eg_ref、ref、ref_or_null、unique_sutbquery、index_subopery

                其中之一时,ref 列 展示的就是与索引列作等值匹配的是谁，比如只是一个常数或者是某个列

•         rows

　　                如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行数，如果使用索引来执行查询时，

                执行计划的 rows 列就代表预计扫描的索引记录行数。

•         filtered

　　                查询优化器预测有多少条记录满⾜其余的搜索条件，对于单表查询来说，这个 filtered 列的值没什么意义，我们更关注在连接查 询中驱动表对应的

                执行计划记录的 filtered 值，

                        mysql> explain select * from staff s where s.staff_id in (select manager_staff_id from store);
                        +----+-------------+-------+------------+--------+--------------------+--------------------+---------+-------------------------------+------+----------+-------------+
                        | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                        +----+-------------+-------+------------+--------+--------------------+--------------------+---------+-------------------------------+------+----------+-------------+
                        | 1 | SIMPLE | store | NULL | index | idx_unique_manager | idx_unique_manager | 1 | NULL | 2 | 100.00 | Using index |
                        | 1 | SIMPLE | s | NULL | eq_ref | PRIMARY | PRIMARY | 1 | sakila.store.manager_staff_id | 1 | 100.00 | NULL |
                        +----+-------------+-------+------------+--------+--------------------+--------------------+---------+-------------------------------+------+----------+-------------+

•         Extra

　　                Extra 列是用来说明一些额外信息的，我们可以通过这些额外信 息来更准确的理解 MySQL 到底将如何执行给定的查询语句。

　　　　        No tables used

　　　　　　        当查询语句的没有 FROM 子句时将会提示该额外信息。

　　　　        Impossible WHERE

　　　　　　        查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息

　　　　        No matching min/max row

　　　　　　        当查询列表处有 MIN 或者 MAX 聚集函数，但是并没有符合 WHERE 子句中 的搜索条件的记录时，将会提示该额外信息。

　　　　        Using index

　　　　　　        当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以 使用索引覆盖的情况下,在 Extra 列将会提示该额外信息。

                                mysql> explain select last_name from actor where last_name like '%a';
                                +----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+--------------------------+
                                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                                +----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+--------------------------+
                                | 1 | SIMPLE | actor | NULL | index | NULL | idx_actor_last_name | 182 | NULL | 200 | 11.11 | Using where; Using index |
                                +----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+--------------------------+

　　　　        Using index condition

　　　　　　        有些搜索条件中虽然出现了索引列，但却不能使用到索引，

                                mysql> explain select * from actor where last_name>'z' and last_name like '%a';
                                +----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
                                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                                +----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
                                | 1 | SIMPLE | actor | NULL | range | idx_actor_last_name | idx_actor_last_name | 182 | NULL | 3 | 100.00 | Using index condition |
                                +----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+

                                索引条件下推

　　                                1、先根据 last_name> 'z'这个条件，定位到二级索引 idx_actor_last_name 中对应的二 级索引记录。

　                                    2、对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否 满足 last_name '%a'这个条件，如果这个条件不满足，

                                                则该二级索引记录压根 儿就没必要回表。

　　                                3、对于满足 last_name LIKE '%a'这个条件的二级索引记录执行回表操作。 我们说回表操作其实是一个随机 IO，比较耗时，所以

                                                上述修改可以省去很 多回表操作的成本。

                        Using where

                                        当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中 有针对该表的搜索条件时，在 Extra 列中会提示上述额外

                                信息。出现了 Using where，只是表示在 server 层根据 where 条件 进行了过滤，和是否全表扫描或读取了索引文件没有关系。

                                Using where 只是表示 MySQL 使用 where 子句中的条件对记录进行 了过滤。

                        Using join buf er (Block Nested Loop)

　　                                在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度， MySQL 一般会为其分配一块名叫 join buffer 的内存块来加快

                                查询速度。

                        Not exists

　　                                当我们使用左（外）连接时，如果 WHERE 子句中包含要求被驱动表的某个 列等于 NULL 值的搜索条件，而且那个列又是不允许存储

                                 NULL 值的，那么在该 表的执行计划的 Extra 列就会提示 Not exists 额外信息。

                        Using intersect(...)、Using union(...)和 Using sort_union(...)

　　                                如果执行计划的 Extra 列出现了 Using intersect(...)提示，说明准备使用 Intersect 索引合并的方式执行查询，括号中的...表示需要进行索引

                                合并的索引名 称；如果出现了 Using union(...)提示，说明准备使用 Union 索引合并的方式执行 查询；出现了 Using sort_union(...)提示，说明

                                准备使用 Sort-Union 索引合并的方 式执行查询。

                        Zero limit

　　                        当我们的 LIMIT 子句的参数为 0 时，表示压根儿不打算从表中读出任何记录， 将会提示该额外信息。

                        Using filesort

　　                        有一些情况下对结果集中的记录进行排序是可以使用到索引的，

　　                        MySQL 把这种在内存中或者磁盘上进行排序的方式统称为文件排序。如果某个查询需要 使用文件排序的方式执行查询，就会在执行计划的

                                Extra 列中显示 Using filesort

                                mysql> explain select * from film order by last_update desc;
                                +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
                                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                                +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
                                | 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using filesort |
                                +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

                                如果查询中需要使用 filesort 的方式进行排序的记录非常多， 那么这个过程是很耗费性能的，我们最好想办法将使用文件排序的执行方式改为

                                 使用索引进行排序。

                        Using temporary

　　                                在许多查询的执行过程中，MySQL 可能会借助临时表来完成一些功能，比如 去重、排序之类的，比如我们在执行许多包含 DISTINCT、

                                GROUP BY、UNION 等 子句的查询过程中，如果不能有效利用索引来完成查询，MySQL 很有可能寻求通 过建立内部的临时表来执行查询。

                                如果查询中使用到了内部的临时表，在执行计 划的 Extra 列将会显示 Using temporary 提示：　　

                                mysql> explain select release_year,count(*) from film group by release_year;
                                +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
                                | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                                +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
                                | 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using temporary |
                                +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+

                                执行计划中出现 Using temporary 并不是一个好的征兆，因为建立 与维护临时表要付出很大成本的，所以我们最好能使用索引来替代掉使用

                                临时表，总的来说，发现在执行计划里面有using filesort或者Using temporary的时候， 特别需要注意，这往往存在着很大优化的余地，最好

                                进行改进，变为使用 Using index 会更好。

                        Start temporary, End temporary

　　                                有子查询时，查询优化器会优先尝试将 IN 子查询转换成 semi-join(半连接优 化技术，本质上是把子查询上拉到父查询中，与父查询的表做

                                 join 操作)，而 semi-join 又有好多种执行策略，当执行策略为 DuplicateWeedout 时，也就是通 过建立临时表来实现为外层查询中的记录进行

                                去重操作时，驱动表查询执行计划 的 Extra 列将显示 Start temporary 提示，被驱动表查询执行计划的 Extra 列将显 示 End temporary 提示。

                        LooseScan

　　                        在将 In 子查询转为 semi-join 时，如果采用的是 LooseScan 执行策略，则在 驱动表执行计划的 Extra 列就是显示 LooseScan 提示。

                        FirstMatch(tbl_name)

　　                        在将 In 子查询转为 semi-join 时，如果采用的是 FirstMatch 执行策略，则在 被驱动表执行计划的 Extra 列就是显示 FirstMatch(tbl_name)提示。

总结

　　学习了Mysql执行计划各个字段含义，在以后的工作中，能分析出sql哪里出问题了，该如何优化。

技术参考

　　mysql官方脚本: https://downloads.mysql.com/docs/sakila-db.zip

　　C/C++Linux服务器开发/后台架构师【零声教育】-学习视频教程-腾讯课堂