【mysql】索引

MySql官方对索引的定义为：索引是帮助MySql高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引原理

不同的存储引擎支持的索引类型也不一样

存储引擎	事务支持	锁定支持	BTree	Full-text	hash
innoDb	√	行级锁定	√	√	×
MyISAM	×	表级锁定	√	√	×
Memory	×	表级锁定	√	×	√
NDB	√	表级锁定	√	×	×
Archive	×	行级锁定	×	×	×

---

hash索引

哈希索引是一种基于哈希表的索引结构，索引键经过hash运算之后得出hash值和对应的行指针存放于一个hash表中。因此他有很高的单条数据查询效率。

哈希索引一些特性：

• 检索效率非常高，可以一次定位；
• 仅能满足“=”、“in”、“>="、“<=”；
• 不支持索引排序，索引值和计算出来的hash值大小不一定一致；
• 无法使用部分组合索引，因为hash值是组合索引键合并后计算的值；
• 无法避免表扫描，无法从hash索引表中直接完成查询，需要访问表中的实际数据进行相应比较才得出相应结果；
• 遇到大量hash值一致的情况，性能不一定比Btree索引。大量记录指针与同一个hash值相关联，定位某条记录需要多次表数据访问而造成性能低下。

b+tree索引

二叉树

二叉树性质：左子树的键值小于根的键值，右子树的键值大于根的键值。
所谓查询效率：由树的深度决定，每深入一层，都需要进行寻址，而寻址的过程就是磁盘随机度，磁盘随机读的速度很慢。

二叉树结构：

二叉树的深度存在不可预测的情况，每深入一层，都需要进行寻址，而寻址的过程就是磁盘随机度，磁盘随机读的速度很慢。

平衡二叉树

平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1，就达到了所谓的平衡。需要通过一系列复杂的旋转达到平衡。

当二叉树出现此情况时，他的查询效率则会下降

判断「平衡二叉树」的 2 个条件：

• 是「二叉排序树」
• 任何一个节点的左子树或者右子树都是「平衡二叉树」（左右高度差小于等于 1）

非平衡二叉树通过旋转，转换为平衡二叉树。

• 右单旋(RR调整)
• 左单旋(LL调整)
• 左右双旋(LR双旋)
• 右左双旋(RL双旋)

B+Tree

B+Tree的定义
B+Tree是B树的变种，有着比B树更高的查询性能，来看下m阶B+Tree特征：
1、有m个子树的节点包含有m个元素（B-Tree中是m-1）；
2、根节点和分支节点中不保存数据，只用于索引，所有数据都保存在叶子节点中；
3、所有分支节点和根节点都同时存在于子节点中，在子节点元素中是最大或者最小的元素；
4、叶子节点会包含所有的关键字，以及指向数据记录的指针，并且叶子节点本身是根据关键字的大小从小到大顺序链接。

B+Tree结构

B+Tree特性
1、节点上只存储键值，不存储数据，这样一来，在有限的节点空间（页空间）内就可以存放更多的键值、指针；
2、所有数据都放在叶子节点中，所有叶子节点之间有链指针（双向循环列表），便于范围查找，也便于排序。

InnoDB中主键索引是聚集索引，所有数据都存在主键索引所在的聚集索引的B+Tree结构的叶子节点中。如果每次插入的主键是大小随机的话，每次数据进来找到的叶子节点的位置是随机的，这样的话，有些叶子节点所在页本来就排满了，结果又来了一条数据，就势必要引起页分裂，所以导致性能下降；但是如果主键是有序的话，每次进行都找到当前叶子前面的位置，一个一个叶子按顺序排满一个页再排一个页，就不会有页分裂的问题了。所以自增主键对于InnoDB这种使用B+Tree索引的存储引擎来说，性能更好。

索引类型

• 主键索引（PRIMARY KEY）

也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

• 全文索引（FULL TEXT）

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

• 常规索引（INDEX或KEY）

普通索引是最基本的索引，它没有任何限制，值可以为空；仅加速查询。

• 组合索引

组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

• 唯一索引（UNIQUE KEY）

唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。简单来说：唯一索引是加速查询 + 列值唯一（可以有null）。

• 索引未命中的几种常见情况

1. 如果条件中有 or ，除非所有的查询条件都建有索引，否则索引失效；
2. like查询是以%开头，如果是int型索引不会命中，字符型的命中’test%'百分号只有在右边才可以命中；
3. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引；
4. 没有查询条件，或者查询条件没有建立索引；
5. 查询条件中，在索引列上使用函数（+, - ,*,/）, 这种情况下需建立函数索引；
6. 采用 not in, not exist；
7. B-tree 索引 is null 不会走， is not null 会走；
8. 联合索引遵循左前缀原则，不满足的不会命中。

• 左前缀原则

以最左边的为起点任何连续的索引都能匹配上。
（1）如果第一个字段是范围查询需要单独建一个索引；
（2）在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边；

当创建(a,b,c)复合索引时，想要索引生效的话，只能使用 a和ab、ac和abc三种组合！