mysql索引和explain的详解

索引原理分析

索引存储结构
索引是在存储引擎中实现的，也就是说不同的存储引擎，会使使用不同的索引
MyISAM和InnoDB存储引擎：只支持B+ TREE索引， 也不能够更换
MEMORY/HEAP存储引擎：支持HASH和BTREE索引

B树图示

B树是为了磁盘或其它存储设备设计的一种多叉（下面你会看到，相对于二叉，B树每个内结点有多个分支，即多叉）平衡查找树。 多叉平衡。

B树和B+树的区别：
B树和B+树的最大区别在于非叶子节点是否存储数据的问题

在结构上：
（1） B树是非也只节点和叶子节点都会存储数据。
（2） B+树只有叶子节点才会存储数据，而且数据都是在一行上，而且这些数据都是指针指向的，也是有顺序的。

在性能上：
（1）对于B-树相对于B+数据，B-Tree因为非叶子结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。而B+Tree所有的数据都在叶子结点，每次查找都得到叶子结点。所以在同样高度的B-Tree和B+Tree中，B-Tree查找某个关键字的效率更高。B-Tree在单条数据读写有着更强的性能。
（2）但由于B+Tree所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于某个关键字的数据的时候，B+Tree只需要找到该关键字然后沿着链表遍历就可以了，而B-Tree还需要遍历该关键字结点的根结点去搜索。这个也决定当连表查询的时候mysql比起mongo有显著的优势。更重要的是由于B-Tree的每个结点（这里的结点可以理解为一个数据页）都存储主键+实际数据，而B+Tree非叶子结点只存储关键字信息，而每个页的大小有限是有限的，所以同一页能存储的B-Tree的数据会比B+Tree存储的更少。这样同样总量的数据，B-Tree的深度会更大，增大查询时的磁盘I/O次数，进而影响查询效率。

聚集索引（MyISAM）
B+树叶节点只会存储数据行（数据文件）的指针，简单来说数据和索引不在一起，就是聚集
索引。
聚集索引包含主键索引和辅助索引都会存储数据指针的值。

辅助索引（次要索引）
在 MyISAM 中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯一的,
而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引,则此索引的结构如下图所示

同样也是一颗 B+Tree,叶子节点中保存数据记录的地址。因此,MyISAM 中索引检索的算法为首先按照B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。

聚集索引（InnoDB）

主键索引（聚集索引）的叶子节点会存储数据行，也就是说数据和索引是在一起，这就是聚集索引。
辅助索引只会存储主键值
如果没有没有主键，则使用唯一索引建立聚集索引；如果没有唯一索引，MySQL会按照一定规则创建聚集索引。

主键索引
1.InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以
唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。

上图是 InnoDB 主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集。

mysql创建索引的时候和用法与索引息息相关，要建立合适的索引和理解一些索引的执行计划，就需要认识索引的结构。

explain的详解

参数说明：
explain后会出现十列数据，下面将介绍这下面的十列数据。

id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

先附上案例表：

CREATE TABLE `taddr` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country` varchar(100) DEFAULT '',
  `province` varchar(100) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `addr_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `addr_id`(`addr_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;


CREATE TABLE `type_time` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` varchar(255) DEFAULT '[]',
  `name` varchar(100) DEFAULT '',
  PRIMARY KEY (`id`)，
  INDEX `name_time_index`(`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

一、id
每个 SELECT语句都会自动分配的一个唯一标识符.
表示查询中操作表的顺序，有三种情况：
id相同：执行顺序由上到下
id不同：如果是子查询，id号会自增，id越大，优先级越高。
id相同的不同的同时存在
id列为null的就表示这是一个结果集，不需要使用它来进行查询。

二、select_type

查询类型，主要用于区别普通查询、联合查询(union、union all)、子查询等复杂查询

2.1、simple
表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple

EXPLAIN select * from user

EXPLAIN select u.id,u.addr_id,a.* from user u inner join taddr a on u.addr_id=a.id

2.2 primary
一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type为primary。

explain select * from taddr t inner join (
select addr_id from user ) u on t.id=u.addr_id

explain select * from user u where u.addr_id =1q
union all
select * from user u where u.addr_id =2

2.3 subquery
除了from字句中包含的一查询外，其他地方出现的子查询都可能是subquery

2.4 dependent subquery

与dependent union类似，表示这个subquery的查询要受到外部表查询的影响

explain select u.name,(select t.province from taddr t where u.addr_id=t.id) from user u

2.5 union
union连接的两个select查询，第⼀个查询是PRIMARY，除了第一个表外，第二个以后的表select_type都是union

三、table
显示的查询表名，如果查询使用了别名，那么这里显示的是别名
如果不涉及对数据表的操作，那么这显示为null
如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。
如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

四、type

依次从好到差：
system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，
index_subquery，range，index_merge，index，ALL

除了all之外，其他的type都可以使⽤到索引，除了index_merge之外，其他的type只可以用到一个索引

4、1 system
表中只有一行数据或者是空表。

4、2const
使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描。

4、3 eq_ref
关键字:连接字段主键或者唯一性索引。
此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较较操作通常是 ‘=’, 查询效率较高.

EXPLAIN select u.id,u.addr_id,a.* from user u inner join taddr a on u.addr_id=a.id

4、4 ref
针对非唯一性索引，使用等值（=）查询非主键。或者是使用了最左前缀规则索引的查询。

EXPLAIN select u.id,u.addr_id,a.* from taddr a left join user u on u.addr_id=a.id

4.5 fulltext
全文索引检索，要注意，全文索引的优先级很高，若全高索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

4、6 unique_subquery
用于where中的in形式子查询，子查询返回不重复值唯一值

4、7 index_subquery
用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

4、8 range
索引范围扫描，常用于使用>,<,is null,between ,in ,like等运算符的查询中。

explain select * from type_time a inner join (
select id from type_time where name =‘2’ and time in (‘2’,‘3’,‘4’) ) b on a.id=b.id

4、9 index
键字：条件是出现在索引树中的节点的。可能没有完全匹配索引。
索引全表扫描，把索引从头到尾扫一遍，常用于使用索引列就可以处理不需要读取数据文件的查询、可以使使用索引排序或者分组的查询。

explain select * from user group by addr_id

explain select addr_id from user

4、10 all
这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

五、possible_keys

此次查询中可能选用的索引，一个或多个

六、key
查询真正使使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

七、key_len

用于处理查询的索引长度度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查
询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的，这里不会计算进去。留意下这个列的值，算下你的多列索引总长度就知道有没有使用到所有的列了。
另外，key_len只计算where条件用到的索引长度，而排序和分组就算使用到了索引，也不会计算到key_len中。

explain select id from type_time where name =‘2’ 用到长度303

explain select id from type_time where name =‘2’ and time in (‘2’,‘3’,‘4’) 用到长度 1071

八、ref
如果是使用的常数等值查询，这里会显示const
如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

九、rows
这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原因是InnoDB使用了MVCC并发机制）

十、extra
这个列包含不适合在其他列中显示但十分重要的额外的信息，其中比较常见有一些：

10、1 using temporary
表示使用了临时表存储中间结果。
MySQL在对查询结果order by和group by时使用临时表
临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，
used_tmp_table，used_tmp_disk_table才能看出来。

explain select * from user u inner join taddr t on u.addr_id=t.id GROUP BY t.id

10、2 using filesort
排序时无法使用到索引时，就会出现这个。常用于order by和group by语句中

说明MySQL会使用个外部的索引排序，而不是按照索引顺序进行读取。
MySQL中无法利索引索引完成的排序操作称为“文件排序“

10、3 using index
查询时不需要回表查询，直接通过索引就可以获取查询的数据。
表示相应的SELECT查询中使用到了覆盖索引（Covering Index），避免回表访问数据行，效率不
错。
如果同时出现Using Where ，说明索引被用来执行查找索引键值
如果没有同时出现Using Where ，表明索引用来读取数据来执行查找动作。

这里对索引的原理和explain做了一些介绍，需要索引需要建立之后对其改变查询方式可能会更能深刻理解 InnoDB 使用覆盖索引和非覆盖索引造成区别。这也是建立索引和使用sql需要特别考虑的问题。