B-Tree与Hash索引

B-tree:（数据区+指针区+二分查找）

B-tree索引能够加快访问数据的速度，因为存储引擎不再需要经行全表扫描来获取需要的数据，取而代之的是从根节点开始搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下查找。通常比较节点页的值和要查找的值可以找到合适的指针进入下层子节点。

B-tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。

如上图，是一颗B-tree，关于B-tree的定义可以参见B-tree，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

一、 B-tree的查找过程

• 如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的B-tree可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

二、 B-tree性质

1. 通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么B-tree要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2. 当B-tree的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，B-tree会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，B-tree就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，B-tree可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

三、 B-tree索引建立原则

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2. = 和 in 可以乱序，比如a = 1 and b = 2and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
3. 尽量选择区分度高的列作为索引，区分度的公式： count(distinct col)/count(*) ，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
4. 索引列不能参与计算，保持列“干净”，比如 from_unixtime(create_time) = ’2014-05-29’ 就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 create_time = unix_timestamp(’2014-05-29’);
5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

注意：

• B-tree的高度一般都在2-4层，这也就是说查找某一键值的行记录最多只要2到4次IO，花费0.02-0.04秒左右。
• B-tree索引适用于全值匹配、匹配最左前缀、匹配列前缀、匹配范围值

Hash索引：（哈希码+指针）

       哈希索引基于哈希表实现，只有精确匹配索引所有列的列才有效。对于每一行数据，存储引擎都会对所有索引计算一个哈希码，哈希码是一个较小的值并且不同键值计算出来的哈希码都不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据的指针。

哈希索引限制：

1. 哈希索引只包含哈希值和行指针，而不知存储字段值，所以不能使用索引中的值来避免读取行。若读取行，则必须经行一次IO操作。
2. 哈希索引并不是按照哈希值顺序存储的，所以也就无法用于排序。
3. 哈希值也不支持部分索引，因为哈希值始终是使用索引列中所有的内容计算哈希值的。例如，在数据（A,B）上建立哈希索引，如果只查询A则不能使用哈希索引。（对比B-Tree索引中符合索引的最左匹配规则）
4. 哈希索引只支持等值查询。
5. 哈希索引查询速度非常快，除非出现出现冲突。

注意：

• Mysql：只有在Memory引擎显示支持哈希索引。（MyISAM、InnoDB、Memory）
• Innodb引擎有一个特殊的功能叫做“自适应哈希索引”。当Innodb注意到某些索引值使用非常频繁时，它会在内存中基于B-tree索引之上再建立一个哈希索引。这是一个完全自动、内部的行为，用户无法配置或者设置，不过有必要可以关闭此功能。

mysql常见索引：

普通索引：最基本的索引，没有任何限制

• 创建索引：CREATE INDEX indexName ON tableName(tableColumns(length));如果是CHAR,VARCHAR类型，length可以小于字段实际长度;如果是BLOB 和 TEXT 类型，必须指定length，下同。
• 修改表结构：ALTER tableName ADD INDEX [indexName] ON (tableColumns(length)) 
• 创建表的时候直接指定：CREATE TABLE tableName ( [...], INDEX [indexName] (tableColumns(length)) ;

唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

• 创建索引：CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))
• 修改表结构：ALTER tableName ADD UNIQUE [indexName] ON (tableColumns(length))
• 创建表的时候直接指定：CREATE TABLE tableName ( [...], UNIQUE [indexName] (tableColumns(length));

全文索引：针对较大的数据，生成全文索引很耗时耗空间。(MyIsam和InnoDb支持：优秀博主)

• ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
• create fulltext index ik_1 on table_1(col_1);
• SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串');

组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

• ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

主键索引：它是一种特殊的唯一索引，不允许有空值。