MYSQL 索引机制-B+TREE

1. 索引简介

  索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构，SQL的主流索引结构有B+树以及Hash结构。
  1. 数据库的索引是一种为了加速数据库表中行记录检索等的数据结构。
  2. 数据库的索引存储于磁盘。
  3. 正确地创建合适的索引是数据库性能优化的基础。

1.1 索引的工作机制

  建立索引后，索引列会在磁盘中单独的存储起来，索引的本质是一种数据结构，通过这种空间换时间的方式可以快速找到需要查询的数据。
  索引数据结构的存储结构分成两种：
(1). 哈希表：底层通过哈希表存储；
(2). 树形索引：底层通过树形存储，在树的结构调整过程中会带来巨大的性能损耗。（InnorDB存储引擎）

1.2 B-树 结构

  (1). B树是一种完全平衡的2-3树的结构，即一个节点可以派生2或3个节点，通过树的合并和分裂，最终达到绝对平衡性。
  (2). 树的每个节点代表索引的行记录的数据。
  (3). 关键字匹配后，会根据递归的二分法查询子节点。
  (4). 数据区有两种存储方案：存储行记录的位置、存储行记录的索引数据。
  (5).树形结构在数据的增加、删除过程中需要进行重新调整，使其达到平衡。因此，对于使用较少的字段、或者多变的字段，一般不建议使用索引。

1.4 B+树 -> Mysql

   B+树结构是基于B-树结构的加强版变种结构。在关系型数据库当中，基本上都是基于B-树或其变种结构，作为索引的底层结构，oracle采用B*树，mysql采用B+树。
   mysql中的B+树结构：
 1、为满足自增序列作为主键索引，B+树采用左闭合的比较规则。
 2、mysql中的B+树结构： 在根节点与支节点不保存数据，数据搜索一定会到达叶子节点，叶子节点中保存数据区。且叶子节点关键字形成有序的双向链式结构。

 3、与B-树的区别：
  (1).B+树的IO效率高于B-树。
  (2).基于索引的表扫描性能高于B-树。
  (3).排序能力强于B-树。
  (4).基于索引的查询B+树更趋于稳定。

1.3 索引落地

  MySql的 Pluggable Storage Engines 插件式设计，支持根据提供的标准去引用第三方的存储引擎，例如：InnoDB(MySQL默认)、MyISAM、Archive、Merge、Mermory、Cluster等。MySql中的索引是通过不同的存储引擎去实现的，比如InnoDB实现的B+树索引（叶子节点数据区存储的是行数据所有内容），Mermory实现的是哈希索引，MyISAM采用的数据与索引隔离存储的模式（即B+树的叶子节点数据区存储的是磁盘指针）。
  InnoDB落地示例：
 聚集索引 - clustered Index：
  聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。
注：在InnoDB引擎中，只有主键才是聚集索引，其他索引都是非聚集索引，因为InnoDB的B+树索引中叶子节点的数据区存储的是真实的行数据纪录。

• 详细内容可参考:聚集索引与非聚集索引的总结.

2. 索引列

2.1 索引列的离散性

索引列的离散性指的是索引的重复率，计算公式为：

count(distinct col):count(col)

• 比值越高，重复率越低，离散性越好：
  （1）若列的离散性越好，列的选择性就越好；
  （2）选择性好的列作为索引更适合；
  （3）离散性很差的列作为索引可能适得其反。

2.2 索引项的比较规则

  对索引项中关键字的对比，一定是从左往右依次进行。
 （1）索引项中的关键字可以是联合索引（即多个列字符串按照索引顺序拼接而成）
  （2） 字符串的对比规则：按照字符串中字符顺序从左往右依次对比字符的ASCII码，直到出现大小不同为止。

2.3 联合索引

如：

经排查发现最常用的sql语句：
select * from users where name = ?;
select * from users where name = ? and phoneNum = ?;
解决方案有：
方案一：
create index index_name on users(name); 
方案二：
create index index_name_phone on users(name,phoneNum);

1、单列索引是一种特殊的联合索引；
2、联合索引列的顺序要遵循最左前置原则：
    最常用列>离散度高>最少空间
(因为索引项的关键字组成按照联合索引列的从左到右顺序拼接而成，索引项的比较规则是从左往右依次进行)
3、联合索引是一个包含了多个功效的索引
（在上面的方案二中已经包含了方案一的索引项，包含方案一索引的功效，所以方案一的为冗余索引）
联合索引就像火车：
    火车头 - 火车身 - 火车尾巴
要先有火车头，然后再加火车身，最后才是火车尾巴。

2.4 关于索引的总结

     全值匹配我最爱，最左前缀要遵守；- 列的排列顺序
     带头大哥不能死，中间兄弟不能断；- 联合索引多功效
     索引列上少计算，范围之后全失效；- 执行计划不调用业务逻辑
     Like百分写最右，覆盖索引不写星；
     不等空值还有or，索引失效要少用。

3. MySQL查询where条件的顺序对查询效率的影响

  数据库引擎在执行SQL语句时，都是要经过查询优化器的，查询优化器会将SQL进行优化，选择最优的查询计划来执行，查询优化器会选择最优的查询计划。

1. 如果where子句中的列全都包含在索引列中，那么where字句中的字段顺序和索引顺序无关，但如果是部分包括的话就要看是否满足最左前缀匹配原则了。
2. MySQL遵循最左前缀原理，当查询条件匹配联合索引的前面几列时，可以使用联合索引；否则，不会使用联合索引。
3. where子句中有范围条件（如> < >=）时，索引失效

<下一章： 聚集索引与非聚集索引的总结.>