MySQL 的B+树索引.
一、B+树索引概述
索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响（需维护索引的结构和数据）；而索引太少，对查询性能又会产生影响。
二叉树，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。
平衡二叉树（AVL树），任何节点的两个子树的高度最大差为 1。平衡二叉树的查询速度很快，但是维护一棵平衡二叉树的代价是非常大的。通常来说，需要 1 次或多次左旋和右旋来得到插入或更新后树的平衡性。
B+ 树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，B+ 树中的 B 不是代表二叉（binary），而是代表平衡（balance）。在 B+ 树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接，叶子节点之间组成一个双向链表。
B+ 树索引的本质就是 B+ 树在数据库中的实现，但是 B+ 索引在数据库中有一个特点是高扇出性（数据库分区），因此在数据库中，B+ 树的高度一般都在 2-4 层，这也就是说查找某一键值的行记录时最多只需要 2 到 4 次IO。
数据库中的 B+ 树索引可以分为 聚集索引和辅助索引。
B+ 树索引并不能找到一个给定键值的具体行。B+ 树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中查找，最后得到要查找的数据。
至于为什么选择 B+Tree而不是B-Tree，可以参考： 以B-tree和B+Tree的区别来分析 mysql 索引实现
- B+ 树的层级更少：相较于 B 树 B+ 每个非叶子节点存储的关键字数更多，树的层级更少，所以查询数据更快；
- B+ 树查询速度更稳定：B+ 所有关键字数据地址都存在叶子节点上，即每一个索引实际上距离根节点距离相同，所以每次查找的次数都相同所以查询速度要比 B 树更稳定;
- B+ 树天然具备排序功能：B+ 树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比 B 树高。
- B+ 树全节点遍历更快：B+ 树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像 B 树一样需要对每一层进行遍历，这有利于数据库做全表扫描。
- B+ 树的范围查找非常方便，这是因为 B+ 树的叶子节点之间依靠单向指针相连。比如查找范围为 10<index<16 的节点，我们仅仅需要先找到索引为 10 的节点在哪个叶子子节点上，特别需要指出的是，即使比如 14 节点在另一个叶子节点上，也能通过叶子节点之间的指针快速找到索引。
二、索引创建和查看
索引的创建和删除可以通过两种方法，一种是 ALTER TABLE，另一种是 CREATE/DROP INDEX。用户可以设置对整个列的数据进行索引，也可以只索引一个列的开头部分数据。
## 添加索引
alter table <table_name> add index <index_name>(column_list);
alter table <table_name> add unique(column_list);
alter table <table_name> add primary key(column_list);
## 删除索引
alter table <table_name> drop index <index_name>;
alter table <table_name> drop primary key;
## 添加索引
create index <index_name> on <table_name>(column_list);
create unique index <index_name> on <table_name>(column_list);
## 删除索引
drop index <index_name> on <table_name>;
索引的查看，可以使用命令 SHOW INDEX。
SHOW INDEX FROM <table_name>
Non_unique 表示是否非唯一的索引；Column_name 表示索引列的名称；Seq_in_index 表示索引中该列的位置；Collation 表示列以什么方式存储在索引中，可以是 A 或 NULL，B+ 树索引总是 A，即排序的；Sub_part 是否是列的部分被索引，如果是整个列，则该字段显示为 NULL；Packed 关键字如何被压缩；Null 是否索引中的列含有 NULL 值；Index_type 索引的类型。
Cardinality 非常关键的值，表示索引中唯一值的数目的估计值，优化器会根据这个值来判断是否使用这个索引。这个值并不是实时更新的，如果需要实时更新 Cardinality 的信息，可以使用 ANALYZE TABLE 命令。建议在非高峰时间，对应用程序下的几张核心表做 ANALYZE TABLE 操作，这能使优化器和索引更好的工作（除了 ANALYZE TABLE 外，还有 SHOW TABLE STATUS、SHOW INDEX 以及访问 INFORMATION SCHEMA 架构下的表 TABLES 和 STATISTICS 都会去重新计算 Cardinality 值）。
MySQL 对于主键索引的创建会采用临时表的方式，首先会创建一张带有主键索引的临时表，然后把原表中数据导入到临时表，接着删除原表，最好把临时表重命名为原表名，这部分操作会导致数据库不可用，因此建议在创建表的时候就定义好主键！
MySQL 对于辅助索引的创建支持 FIC —— Fast Index Creation（快速索引创建）方式，其会对创建索引的表加上一个 S 锁，不需要建立临时表。
MySQL 5.6 版本开始支持 Online DDL(在线数据定义)操作，其允许辅助索引创建的同时，还允许其他诸如 INSERT、UPDATE、DELETE 这类 DML 操作，其原理是将 DML 操作日志写入到一个缓存中，待完成索引创建后再将缓存应用到表上，以此达到数据的一致性，这个缓存的大小由参数 innodb_online_alter_log_max_size 控制，默认的大小为 128MB。
AlTER TABLE <tbl_name> [index_type](index_col_name)
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
LOCK [=] { DEFAULT| NONE| SHARED| EXCLUSIVE }
ALGORITHM 制定了创建和删除索引的算法，COPY 选择创建临时表的方式；INPLACE 表示创建和删除索引不需要创建临时表；DEFAULT 会根据参数 old_alter_table 来判断是使用 INPLACE 算法还是 COPY 算法，该参数的默认值为 OFF，表示采用 INPLACE 方式。
LOCK 指定了创建和删除索引的时候添加锁的情况，NONE 表示不添加任何的锁；SHARE 表示添加 S 锁；EXCLUSIVE 表示添加 X 锁；DEFAULT 会根据并发性执行一个锁升级的过程，先判断是否可以使用 NONE 模式，若不能，再判断是否可以使用 SHARED 模式，否则将使用 EXCLUSIVE 模式。
三、联合索引
联合索引是指对表上的多个列进行索引。从本质上来说，联合索引也是一棵B+ 树。那么什么时候会使用到联合索引呢？"WHERE a= xxx and b=xxx" 和 "WHERE a= xxx" 都能使用到联合索引，但是"WHERE b= xxx"则使用不到这个索引，因为叶子节点上的 b 值是无序的，这也是人们常说的 —— 最左前缀匹配。除此之外，因为联合索引已经对键值进行了排序处理，因此对于索引列的排序操作也能使用到索引。
四、覆盖索引
覆盖索引是指从索引中就可以得到查询的记录，而不需要查询聚集索引中的整行记录的所有信息，因此可以减少大量的 IO 操作。比如只查询索引列的信息。
对于统计问题而言，在同时存在辅助索引和聚集索引的情况下，InnoDB 存储引擎会优先使用辅助索引来进行统计，因为辅助索引远小于聚集索引（辅助索引不需要维护整行记录的全部信息）。
此外，在通常情况下，诸如（a，b）的联合索引，一般是不可以选择列 b 作为查询条件。但是如果是统计操作，并且是覆盖索引的，则优化器会进行选择。
五、其他
当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右），即使存在可以使用的辅助索引，优化器仍然会选择通过聚集索引来查找数据，因为顺序读要远大于离散读。这是由当前传统机械硬盘的特性所决定的，即利用顺序读来替换随机读的查找。可以使用关键字 FORCE INDEX 来强制使用某个索引。
Multi-Range Read 优化是 MySQL 5.6 开始支持的一种索引优化方式，目的是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于 IO-bound 类型的 SQL 查询语句可带来性能极大的提升，适用于 range、ref、eq_ref 类型的查询。
Index Condition Pushdown（索引下推) 优化是 MySQL 5.6 开始支持的一种索引优化方式，默认开启，使用 SET optimizer_switch = 'index_condition_pushdown=off'; 可以将其关闭。ICP 优化可以有效的提高查询效率，适用于 range、ref、eq_ref、ref_or_null 类型的查询。
顺便提一下 MyISAM 的存储引擎索引实现，与 InnoDB 不同的是，无论是一级索引，还是二级索引，MyISAM 的索引叶节点的 data 域存放的直接是数据记录的地址，没有另外构建聚簇索引。
