mysql 聚集索引和非聚集索引区别 和explian extra的各种类型含义解释

简单概况一下：

聚集索引就是以主键创建的索引非聚集索引就是除了主键以外的索引。非聚集索引也叫做二级索引，不用纠结那么多名词，将其等价就行了。非聚集索引在建立的时候也未必是单列的，可以多个列来创建索引。本质区别：

表记录的排列顺序和索引的排列顺序是否一致。

聚集索引（clustered）也叫聚簇索引

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

聚集索引表记录的排列顺序和索引的排列顺序保持一致，所以查询效率相当快。只要找到第一个索引记录的值，其余的连续性的记录也一定是连续存放的。聚集索引的缺点就是修改起来比较慢，因为它需要保持表中记录和索引的顺序需要一致，在插入新记录的时候就会对数据也重新做一次排序。在数据表创建上INNODB聚集索引存储的是一个文件，后缀为.frmInnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。非聚集索引（unclustered）

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

非聚集索引定义了表中记录的一些逻辑顺序，但记录的物理和索引不一定保持一致，两种索引都采用B+树的结构，非聚集索引的叶子层并不和数据叶相互重叠，而是采用叶子层包含一个指向表中的记录指针。非聚集索引的缺点就是索引的层次比较多，但是不会造成数据的重排。在数据表创建上Myisam存储为三个文件.frm、.MYD、.MYI，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取

非聚簇索引查询数据需要先查到聚簇索引的key，然后用这个key去查询真正的数据(这个过程称为回表)。
也就是说非聚簇索引是需要查询两次

gap锁的基本作用就是保证可重复读的情况下不出现幻读

gap锁的关键就是锁住索引树的叶子节点之间的间隙，不让新的记录插入到间隙之中

explan 后的Extra为Using index condition说明，确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。

Extra为Using where说明，SQL使用了where条件过滤数据。但type属性是ALL，表示需要扫描全部数据，仍有优化空间。
Extra为Using index说明，SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。这类SQL语句往往性能较好。
 Extra为Using filesort说明，得到所需结果集，需要对所有记录进行文件排序。这类SQL语句性能极差，需要进行优化。
 Extra为Using temporary说明，需要建立临时表(temporary table)来暂存中间结果。 这类SQL语句性能较低，往往也需要进行优化。

B+Tree相对于B-Tree有几点不同：
非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。

为什么B-tree 不适合mysql 查询？

第一：B-树的每个节点都有data域（指针），这无疑增大了节点大小，说白了增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时啊！），而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。这是优点之一。

第二：B+树所有的Data域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问啦