MySQL hash索引及B+Tree索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。另一方面，索引会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE操作时，索引需同步更新维护。

由局部性原理可知，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，每一次IO读取的数据我们称之为一页(page)。

索引分类

普通索引

仅加速查询，Normal Key

唯一索引

加速查询 + 列值唯一（可以有null）Unique Key

联合索引

多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

全文索引

全文索引(FULL TEXT)。目前只有CHAR、VARCHAR，TEXT列上可以创建全文索引。目的为解决WHERE name LIKE '%word%'的模糊搜索。

阿里巴巴Java开发手册中推荐使用搜索引擎解决模糊搜索问题。

常见树结构

B树

B-树是一种多路搜索树。搜索从根结点开始，对结点内的关键字（有序）序列进行二分查找

B+树

B+树是在B树的基础上进行改造的，它的数据都是叶子节点，同时叶子节点之间还加了指针以形成链表。搜索时的区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中）

红黑树

红黑树（Red-Black Tree）是二叉搜索树（Binary Search Tree）的一种改进。为了避免在最坏的情况下退化为链表，红黑树在每一次插入或删除节点之后都会花O(log(n))的时间来对树的结构作修改，以保持树的平衡。

B+ Tree与Hash索引

• 查询时间。B+ Tree为 O(log(n))，与树的高度有关；Hash为O(1)
• 适用范围。在内存中，红黑树比B树效率更高；若是涉及磁盘操作，则B树更优

Q：索引的树型数据结构为什么使用B+树，而非B树或红黑树？
A：B树需要做局部的中序遍历，可能要跨层访问。而B+树由于所有数据都在叶子结点，不用跨层，同时由于有链表结构，只需要找到首尾，通过链表就能把所有数据取出。

Q：为什么常用B+ Tree而非Hash作为索引类型？
A：数据库中的索引一般是在磁盘上，数据量大的情况可能无法一次装入内存，B+树的设计可以允许数据分批加载，同时树的高度较低，提高查找效率。

注：
1.哈希索引数据并不是按照索引列的值顺序存储的，故无法用于排序
2.哈希索引只支持等值比较查询，如：=、in()、<=>(安全比较运算符，用来做 NULL 值的关系运算)，不支持任何范围查询

索引规约

1. 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
2. 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。count(distinct left(列名, 索引长度))/count(*)计算区分度
3. SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好。

详细索引规约见阿里巴巴Java开发手册(1.4.0)

---

参考资料：
1.MySQL 索引
2.数据库索引
3.MySQL索引原理以及查询优化
4.阿里巴巴Java开发手册(1.4.0)
5.MySQL B+树存储索引
6.B树、B-树、B+树与红黑树