MySQL专题，第六期—sql查询分析与优化(二)

一、背景

    上期文章已经引入了expalin关键字分析sql，并且我们已经谈了关于id、select_type、table三列分析sql。本期我们继续分析剩余列。

二、继续讲述explain剩余字段

        ①   partitions：匹配的分区信息（对于非分区表值为NULL）。这个字段是分析mysql分区的。这里我们我们并没有分区，所以这里暂不讲述。

        ②   type：这一列代表了sql查询的访问类型。

    它的值为：all index range ref eq_ref const system null。

    执行效率从高到低依次为：system>const>eq_ref>ref>range>index>all。

    执行效率的高低顺序大家一定要牢记于心，这可是分析sql的关键。每个关键字分析如下：

        system：表中只有一行记录，相当于系统表。(实用基本不可能，忽略)

        const:表示通过一次索引就能找到。（主键索引、唯一索引）

        eq_ref:唯一性索引扫描，对于每个索引键，表中记录只有一条与之匹配。（常见的也是主键索引与唯一索引）

        ref:非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它是查找和扫描的混合体。

        range:只检查给定范围的行，使用索引来选择行。key字段的列显示了使用了哪个索引。这个一般就是where语句中出现<、>、in等操作符。这种查询的扫描比全索引扫描好，不用扫描全索引，也是一种优化。

        index：全索引扫描。比ALL快。这里虽然扫描了全索引，但是也是一种优化。因为索引是默认加载入内存的。

        all：全表扫描。可能多次读硬盘I/O。最差级别的查询。

        针对type这一列，我们可以直观看出sql查询的访问类型，对优化sql很有帮助。

        ③ possible keys key key_len三列：

            possible keys key这两列是紧密关联的。首先possible keys 显示可能应用在该查询的索引，可能多个。查找中涉及的字段若存在索引则该索引被引出。但是不一定在实际查询中使用。(索引失效问题)其次key 显示实际查询中使用的索引。如果为null则表示没有使用索引。

        注：如果查询中使用了覆盖索引，则该索引仅出现在key列表中。

关于覆盖索引：即select 后的字段与所建索引中字段一一对应(顺序、个数都得对应)。简单说覆盖索引就是不用再查数据库，直接从索引中就能获得想要的数据，所以，覆盖索引执行效率很高。

        对于key_len这一列，表示了索引中使用的字节数，可以通过该列计算查询中使用的索引的长度。自然，索引长度越短越好。但是，ken_len显示的值为索引字段的最大可能长度，并非实际长度(因为它是通过定义计算的)。

        ④ ref：显示了索引的哪一些列被使用了。

        它的值主要有两种：

                1.const：即，常量。举个例子：如sql查询的where条件中有a='5';假如a这个字段在索引中那么是不是一个常量呢。这就是const的含义。

                2.database.table.key：即，database数据库中table表的key字段(有可能是多个值，这个需要看索引是如何建立的。)

        ⑤row：根据表统计信息及索引使用情况，大致估算出找到需要的数据所需要读取的行数。(毋庸置疑：值越少越好)

三、分析几个例子

      例一：sql及其结果如下:

    select c.name,b.subject,a.score from score a left join subject b on         a.subject_id=b.id left join student c on a.student_id=c.id where c.name='zns';

    目的：查询zns学生的考试科目即成绩。

    查询结果如下：

    

sql分析结果如下：

    结果分析：

        1.原始sql被mysql分成了三个子查询。三个子查询都是simple查询，都没有用mysql分区。type列：a表查询为ALL，即：mysql进行了全表扫描，效率会很低吧。而 对c b两个表的查询是eq_ref级别的，回顾上面的内容。结合possible_key及key列。我们看到这两个查询用到了主键索引。而且我在这里根本没建立任何索引，所以说主键索引mysql自动建立。eq_ref级别的查询也就是说依次索引检索便找到了数据，效率很高吧。

    例二：sql及其结果如下：(未建立任何索引)

    select c.name,b.subject,a.score from score a left join subject b on a.subject_id=b.id left join student c on a.code_student=c.student_code where c.name='zns';

    查询结果如下：

    sql分析结果如下：

   例三：sql及其结果如下：

select b.name,(select c.subject from subject c where a.subject_id=c.id) subject,a.score from  student b left join  score a on a.code_student=b.student_code where b.name='zns';

    目的：查询名为zns的学生的全部科目及其成绩。

    建立索引情况如下：

1.student学生表

2.score分数表

3.科目表未建立索引

    

    查询结果如下：

    sql分析结果如下：

结果分析：

    例二与例三我们一起讲，大家很直白可以看到查询结果是一样的。而做例二查询的时候我完全没有建立任何索引。结果一目了然，从种种字段表明索引用在查询中。    

      ①  看id：说明例三存在子查询,而例二不存在子查询。(这里说明一下哈，我就是为了给大家做个比较，具体的查询sql的编写还得看具体情况而定)

      ②  看select_type：也充分表示例三存在子查询而例二不存在。

      ③  type：例二全是all，也就是最差查询。而例三就厉害了，用上索引之后一个ref查询,一个eq_ref查询。对应的列出了可能用到的索引及实际用到的索引，并且显示了用了索引的哪个列，长度是多少。

        注意：我们可以看到例三第二个查询索引没用上。这就是优化的重点啦。造成了索引失效

     ④  rows列：例二不用索引查询了1007行数据，而例三用了数据查询7行。