平衡树索引
平衡树基础
InnoDB引擎的平衡树采用B+Tree索引,先分析B-Tree和B+Tree的结构特点,以及为什么采用B+Tree结构。
-
B-Tree结构:
- 每个节点都有数据
- 节点的值有自己的范围: [ m 2 , m ] [\frac{m}{2}, m] [2m,m]
- 中序遍历是递增的
-
B+Tree结构:
- 节点不存储数据,只存储索引
- 父节点的值,在所有的子节点中一定被包含;且父节点的值在子节点中一定是第一个或者最后一个
- 子节点存储数据,而且子节点互相连接,形成一个顺序的链式结构
B-/+Tree作为索引的特点:
- 优:树的高度低,一次读取多个节点,减少磁盘的IO次数;
- 缺:是平衡树结构复杂,需要调整结构耗费时间,但是这相对磁盘IO时间可以忽略。
B+Tree比B-Tree的优势:
- B+Tree的非叶子节点没有数据,因此可以使得叶子节点包含更多的键值,减低树的高度,优化查询次数
- B+Tree获取一定范围数据时,B+Tree不需要回溯等的操作,而是直接根据索引定位到叶子节点的数据范围的两端,然后利用叶子的链式结构读取
- 插入或删除数据时,需要调整的仅仅时索引排列方式,底层的数据部分不变
缺点:B+Tree使用了多余的节点,因为所有的孩子节点都要保存父节点的数据。
推荐文章:https://blog.csdn.net/qq_26222859/article/details/80631121
InnoDB的B+Tree索引
匹配特点:
- 索引执行最左匹配的原则,从最左侧向右侧匹配
- 索引匹配无法跳过中间的列,向后继续匹配
- 无法匹配表达式中的值,比如
where id +1 < 6
中的id
就无法执行匹配
设计索引的基本原则:
- 最左侧匹配尽量区分度较大
- 使用适中长度的可区分匹配,太短区分效果不好,太长速度慢
索引交互:
- 相交操作(AND)出现了多个索引时,考虑合并这多个索引
- 联合操作(OR)出现时,会耗费CPU进行排序等操作,耗费时间
哈希索引
- 只能精确匹配
- 索引列必须一次性全部使用,不能单独使用
- 无法排序,查询区间等。使用时,只能进行
=
、!=
或IN()
等操作
聚簇索引
说明:聚簇索引是一种存储形式,叶子节点是存储数据的;而普通索引的叶子存储的是指针,指向实际的数据地址
特点:
- 数据全部位于叶子节点中
- 数据行与相邻的键位数据紧密相连
- 叶子中包含全部数据,节点中只是索引
- 一个表中只能有一个聚簇索引
数据存储模式:
以数据页的形式存储,即一个批次的数据存放到一个数据页中,加速IO
优势:
- 相关的数据紧密排列在一起,一次IO操作可以批量读取大量的数据。
- 针对主键的访问速度
劣势
- 插入和删除的效率依赖主键,对主键执行过多的插入删除,会影响存储页的排列,进而引发效率问题
- 更新和移动数据,同样也可能会造成类似的问题
- 稀疏的数据会使得数据页利用率低,使得全表扫描效率差
索引的一些总结
使用索引有3个主要的优势:
- 减少磁盘IO的次数
- 减少了排序和临时表
- 把随机IO变成了顺序IO
索引所在的列不能在表达式中,否则无法使用索引的特性。
索引的基本操作
创建索引:
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) # 主键索引
# create的方式
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
删除索引
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
查看索引
show index from tblname;