MySQL索引原理之索引分析

　　在实际生产中经常会遇到关于数据库性能优化方面的问题，怎么入手呢？首先得分析，MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，供开发人员有针对性的优化。
　　　　

　　下面从explain命令开始介绍索引分析需要的相关术语：

　　1、explain

　　　　select_type：表示查询的类型。常用的值如下：

　　　　　　SIMPLE ： 表示查询语句不包含子查询或union

　　　　　　PRIMARY：表示此查询是最外层的查询

　　　　　　UNION：表示此查询是UNION的第二个或后续的查询

　　　　　　DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果

　　　　　　UNION RESULT：UNION的结果

　　　　　　SUBQUERY：SELECT子查询语句

　　　　　　DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

　　　　最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没用到UNION查询。

　　　　type：表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。

　　　　　　ALL：表示全表扫描，性能最差。

　　　　　　index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。

　　　　　　range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
　　　　　　ref：表示使用非唯一索引进行单值查询。
　　　　　　eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
　　　　　　const、system：表示使用主键或唯一索引做等值查询，常量查询。
　　　　　　NULL：表示不用访问表，速度最快。
　　　　possible_keys：表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。
　　　　key：表示查询时真正使用到的索引，显示的是索引名称。
　　　　rows：MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。
　　　　key_len：表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
　　　　　　key_len的计算规则如下：
　　　　　　字符串类型
　　　　　　　　字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4
　　　　　　　　char(n)：n*字符集长度
　　　　　　　　varchar(n)：n * 字符集长度 + 2字节
　　　　　　数值类型
　　　　　　　　TINYINT：1个字节
　　　　　　　　SMALLINT：2个字节
　　　　　　　　MEDIUMINT：3个字节
　　　　　　　　INT、FLOAT：4个字节
　　　　　　　　BIGINT、DOUBLE：8个字节
　　　　　　时间类型
　　　　　　　　DATE：3个字节
　　　　　　　　TIMESTAMP：4个字节
　　　　　　　　DATETIME：8个字节
　　　　　　字段属性：NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。
　　　　Extra：Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：
　　　　　　Using where：表示查询需要通过索引回表查询数据。
　　　　　　Using index：表示查询需要通过索引，索引就可以满足所需数据。
　　　　　　Using filesort：表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化。
　　　　　　Using temprorary：查询使用到了临时表，一般出现于去重、分组等操作。
　　2、回表查询
　　　　在MySQL索引原理之索引类型 - 池塘里洗澡的鸭子 - 博客园 (cnblogs.com)介绍过，InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。
　　　　即：通过索引查询主键值，然后再去聚簇索引查询记录信息。
　　3、最左前缀原则
　　　　复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。
　　　　　　　　

 　　4、explain的输出结果Extra字段为Using index时，能够触发索引覆盖。其本质是：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖——即如果一个索引包含了（或者说覆盖了）所有满足查询所需要的数据，那么就成这类索引为覆盖索引。从定义就可知索引覆盖查询不需要回表操作。

　　　　通过将被查询的字段，建立到组合索引，这也是实现索引覆盖最常见的方法。

 

　　

　　

 

未完……待续