mysql explain 使用说明

explain 使用

        explain 查询计划，MySQL用来分析查询语句使用。可以通过explain来分析sql是否需要优化。使用方式毕竟简单：

        explain 查询sql语句   

        如： explain  select * from table

参数说明（常用）

        id 

                顺序(有可能重复，关联语句，如left join等)   越大越先执行,可以观察sql的执行顺序

        select_type

              simple         (简单查询,查询不包含子查询和union )         

              primary       (复杂查询中最外面的select）          

              subquery（包含在 select 中的子查询   不在 from 子句中）         

              union   在 union 中的第二个和随后的 select

              derived    包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表

               示例：

set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合

并优化 （如果不操作下面结果中不会有 derived）

explain select (select 1 from form where form_id = 1) from (select * from project where project_id = 1) der;

        table   表名

        partitions   分区

                如果查询是基于分区表的话，会显示查询将访问的分区。

        type

                sql访问类型，决定大概查找范围      一般要达到 range 最好达到ref

                最优到最差：  system(const的特例，结果集就一个，innerDB不可能达到) > const((主键)常量查询通过索引一次就找到数据) > eq_ref（主键，唯一等） > ref（使用普通索引，或者唯一索引前缀） >range（范围查找） > index（扫描全索引拿到结果，一般是二级索引） > all （全表扫描）

                

                1. system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

                2. const：表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

                3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

                4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

                5. range：只检索给定范围的行，使用一个索引来选择行（不论是是什么索引），key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

                6. index：Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（虽然all和Index都是读全表，但index是从索引中读取所有需要的数据（上一篇文章中的索引覆盖），而all是从硬盘读取的所有数据）

                7. all： 将遍历全表以找到匹配的行

        

        possible_keys

                可能会用到的索引，explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

        key

                实际使用的索引，如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。 一般不建议使用，mysql自己也会做优化，有时候走索引不一定比全表扫描快

        key_len   

                索引里使用的字节数

                 1.字符串，char(n)和varchar(n)，5.0.3以后版本中，n代表字符数，而不是字节数，如果是utf-8，一个数字 或字母占1个字节，一个汉字占3个字节

                        char(n)：如果存汉字长度就是 3n 字节

                        varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度， 因为 varchar是变长字符串

                 2.数值类型

                        tinyint：1字节

                        smallint：2字节

                        int：4字节

                        bigint：8字节

                 3.时间类型

                        date：3字节        timestamp：4字节        datetime：8字节

                 4.如果字段允许为 NULL，需要1字节记录是否为 NULL

        索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

         ref

                查询条件是什么?表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

        rows

                估计要读取并检测的行数

        filtered    

                 估算和其他表连接行数

        Extra

                额外信息（比较多，好几十种） 

                1. Using index：使用覆盖索引

                        mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

                2. Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖

                3. Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

                4. Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的

                5.Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的

                6. Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段

               总结:

        EXPLAIN，不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况。
        EXPLAIN，不考虑各种Cache(缓存)。
        EXPLAIN，不能显示MySQL在执行查询时所作的优化工作。
        EXPLAIN，部分统计信息(rows)是估算的，并非精确值。
        EXPALIN，只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

        通过explan来分析查询语句是否做优化，重点查看的字段是：

        type  最好能达到ref   key 是否使用了索引  rows 预计扫描的行数，extra  覆盖索引

当然也不是说所有查询都需要走索引才会有最高的效率，这都是根据实际情况来分析的，mysql 内

部也有计算方式，如果计算出来索引不如全表快（比如还要回表），那么索引就会失效。