MySQl索引基础

平衡树索引

平衡树基础

InnoDB引擎的平衡树采用B+Tree索引，先分析B-Tree和B+Tree的结构特点，以及为什么采用B+Tree结构。

• B-Tree结构：

  • 每个节点都有数据
  • 节点的值有自己的范围：$[\frac{m}{2},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;