索引优化之explain后的详细分析

索引优化之explain后的详细分析

熟悉索引优化的大佬都知道，在explain关键字的作用下，可以清楚索引是否被执行，索引是否被完全执行，索引执行的方式，引入索引后是否使查询更快等等
在进入正题之前，我来先介绍一下explain作用下的表的内容吧，如下：

我来介绍几个重点

1.select_type（额，其实我感觉这个用处不大吧，应该？）

select_type数据列指明各“单位select 查询”的查询类型，select_type数据列的列值如下所示
①simple:表明进行的是进行不需要Union操作或不含子查询的简单select查询
②primary：表明是一个需要Union操作或含子查询的select查询
③union：表明是一个由union操作联合而成的单位select查询，除第一个外，第二个以后的所有单位select查询
④dependent union：与union 一样，dependent union出现在union或union all 形成的集合查询中。此处的dependent表示union或union all联合而成的单位查询受外部影响
⑤union result：union result为包含union结果的数据表。

2.type(这个挺重要)

type
意味着类型，这里的type官方全称是“join type”，意思是“连接类型”,这样很容易给人一种错觉，觉得必须需要俩个表以上才有连接类型。事实上这里的连接类型并非字面那样的狭隘，它更确切的说是一种数据库引擎查找表的一种方式。
mysql5.7中type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是all, index, range, ref, eq_ref，const。从左到右，它们的效率依次是增强的。撇开sql的具体应用环境以及其他因素，你应当尽量优化你的sql语句，使它的type尽量靠右，但实际运用中还是要综合考虑各个方面的。
all
这便是所谓的“全表扫描”，如果是展示一个数据表中的全部数据项，倒是觉得也没什么，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。
index
这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据
range
range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>’,’<'外，in和or也是索引范围扫描。
ref
出现该连接类型的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。
ref_eq
ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。
const
通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。

3.key(这个相当重要)

这个可以很重要，但是能说的不多，就一句话可以概况：通过key，我们可以知道在该次sql查询中使用了那些索引。

4.key_len(这个也挺重要)

通过key_len我们可以知道索引是否被完全使用，至于如何判断是否完全使用，就涉及到key_len的计算了，下面我们来玩一玩
假如我们有如下建表语句

Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '',
  `name1` char(20) DEFAULT NULL,
  `name3` varchar(20) NOT NULL DEFAULT '',
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx1` (`name`,`name1`),
  KEY `idx3` (`add_time`,`name3`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

重点看一下name, name1, name3，以及两个索引就行
执行1：

explain select * from t1 where name1='yuanchangliang' and name3='chenwenjiao';

结果：
±—±------------±------±-----±------------±-------±--------±-----------------------| id | select_type | table | type |possible_keys| key | key_len | ref | rows | Extra ±—±------------±------±-----±------------±-------±--------±--------±-------
| 1 | SIMPLE | t1 | ref |idx3 | idx3 | 123 |const,const| 1 | Using index ±—±------------±------±-----±--------------------±--------------------±--------±----
我们可以看到，key_len为123，怎么算的呢，先来个公式

key_len的长度计算公式： varchr(10)变长字段且允许NULL = 10 * ( character
set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段) varchr(10)变长字段且不允许NULL =
10 *( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL = 10 * ( character
set：utf8=3,gbk=2,latin1=1)+1(NULL) char(10)固定字段且不允许NULL = 10 *
( character set：utf8=3,gbk=2,latin1=1)

我来解释一波，看看建表语句，name1为char(20), name3为varchar(20)，如果字符编码为utf-8，则每个长度占3个字节；另外如果是default null，则null会额外占一个字节，如果是not null，则不占字节。，如果是varchar这种可变类型，需要两个字节来存储长度，所以我们来看看123的由来：name1(203+1)+name3(203+2)=123;
name1加1是因为它是default null name3加2是因为它是可变类型

5.rows

这个可就是不要太好用了，通过看rows,可以直接看到索引是否对查询有优化作用，与未添加索引的sql语句的rows相比较，使用索引后rows减小了，说明起到了优化作用，rows越小说明优化效果越好

6.extra(这个其实也很重要，但是情况太多了，这里就不再赘述了，可以通过百度详细了解一下)

不说了，告辞

我是“道祖且长”，一个在互联网苟且偷生的Java程序员