（五）mysql索引优化--explain详解

首先：索引优化和sql语句优化不是同一级别的概念，系统性能优化和sql语句优化也不是同一级别的概念。

SQL语句的优化主要包括三个问题，即如何发现有问题的SQL、如何分析SQL的执行计划以及如何优化SQL

     怎么发现有问题的SQL?  通过MySQL慢查询日志对有效率问题的SQL进行监控

     如何分析SQL的执行计划？通过explain分析SQL的执行计划 

     如何优化SQL语句？ 索引 + 避免使用一些降低性能的关键字。

补充：如何性能优化？sql优化 + 表结构优化 + 系统配置优化+硬件优化。 

系统性能优化和sql语句优化不是同一级别的概念。

上述是慢查询和expain语句的区别。

 

 explain详解（索引优化）

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。通过explain命令我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。

ID：

• id相同表示加载表的顺序是从上到下。
• id不同id值越大，优先级越高，越先被执行。
• 含有子查询中的子查询的id值会更大，会优先进行子查询。

select_type：

• SIMPLE(简单SELECT,不使用UNION或子查询等)
• PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
• UNION(UNION中的第二个或后面的SELECT语句)
• DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
• UNION RESULT(UNION的结果)
• SUBQUERY(子查询中的第一个SELECT)
• DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
• DERIVED(派生表的SELECT, FROM子句的子查询)
• UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

  table：显示这一行的数据是关于哪张表的

  type：

该列称为关联类型或者访问类型，它指明了MySQL决定如何查找表中符合条件的行，同时是我们判断查询是否高效的重要依据。结果值从最好到最坏依次是：(下列type都是在索引的基础上进行改良，没有索引就是all) 。

• ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行，效率最低，需要优化
• index: Full Index Scan，index与ALL区别为index类型遍历全部索引，all遍历全部数据，包括非索引的数据。
• range:只检索给定范围的行，使用一个索引来选择行。（in大于一半会失效，转化为无索引，进而原表找）
• ref: 非唯一性索引，返回的数据可以是0多或相同的多个 ，唯一性索引只能返回1个。
• eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
• const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
• NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

   possible_keys：这一列显示查询可能使用哪些索引来查找。

   Key：显示MySQL实际决定使用的键（索引）。

   key_len:表示索引中字节的大小，可以计算索引使用的内存长度，（key_len显示的值为索引字段的最大可能长度，并非实际     使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）。

   ref(和type里的ref不同）:显示了哪些字段或者常量被用来配合从表中查询。

   row:被索引优化查询到的个数（就是通过索引而查询到的个数）。

   Extra:

• using filesort:对结果使用一个外部索引排序，而不是按索引次序从表里读取行，一般有出现该值，都建议优化去掉，因为这样的查询 CPU 资源消耗大。一般出现在order by中。比如：查询2017届的学生名并将他们按年龄排序:select name from student where  time=2017 order by age. 就是会出现using filesort 。 因为已经通过name查询，又要通过age查询一次在排序
• using temporary:在查询结果排序时会使用一个临时表，一般出现于排序、分组group by和多表 join 的情况，查询效率不高，建议优化。
• using index:也叫覆盖索引，不需要回表查询，表示好的性能
• using where:需要回表查询