MySQL索引

Mysql索引

索引是帮助mysql高效获取数据的排好序的数据结构 每新建一个索引 就会在对应的文件中 新生成一个树结构

• 索引数据结构

  • 二叉树

  • 红黑树

  • hash表

  • B Tree

二叉树

 

如果顺序存储数据

 

会形成一个链表 同全表扫描没有什么区别 所以没有选择二叉树

红黑树

 

红黑树存在的问题: 树的高度太高 如果数据量过大 从根节点找到叶子节点需要多次寻找

B树

 

键值 - 索引数据

value值 -data 索引所在行的文件地址

叶节点具有相同的深度，叶节点的指针为空

所有索引元素不重复

节点中的数据索引从左到右递增排列

范围查找很不友好

B+树

 

非叶子节点不存储data 只存储索引(冗余) 可以放更多的索引

叶子节点包含所有的索引字段

叶子节点用双向指针连接 提高区间访问的性能 很好的支持了范围查找

B+树 节点中的数据索引从左到右递增排列(有序)

一个节点的大小为16KB

Hash

 

存放索引 和索引所在行的磁盘文件地址

对索引的key进行一次hash()计算就可以定位出数据存储的位置

很多时候Hash索引要比B+ 树索引更高效

不使用hash索引的原因 :

能满足 “=”，“IN”，但是 不支持范围查询

hash冲突问题

MyISAM存储引擎索引实现索引

 

myisam引擎的数据表对应的文件 为三个

.frm 表结构

.myd data 数据

.myi index 索引

如果有sql语句 select * from table where cold1 = 30;

则 找到30这个叶子节点 从其data中拿到这一行数据在磁盘文件中的地址 定位到这一行数据

InnoDB存储引擎索引实现

 

innodb 存储的表在文件中 只有两个文件

.ibd 所有数据

.frm 表结构

表数据文件本身就是按B+Tree组织的一个索引结构文件

聚集索引-叶节点包含了完整的数据记录

innodb只有一个聚集索引 如果有主键 即是主键生成的索引

其他的索引都是 非聚簇索引/二级索引

非聚簇索引/二级索引的叶子节点的data存放的是该行数据所对应的主键值

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

必须建主键:

.ibd文件必须要用一颗b+树来组织

如果有自增的整形主键 那么就可以直接使用主键生成的聚簇索引

如果没有生成这种主键 会从第一列开始选择一列所有元素都不相等的列 作为索引 如果选不到 就会生成隐藏列 _rowid

使用整型: 在查询过程中会经过多次比较 整型的比较效率高

占用空间小

使用自增:

 

非自增的插入 8 7 的过程

插入8

 

插入7

 

最终结果

 

原来的节点会分裂 并且b+树做了一次平衡

插入自增的节点

可以直接开一个新节点

聚集索引和非聚集索引查找效率

聚集索引叶子节点包含所有信息 所以效率更高

为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)

1 节约存储空间

2 保持一致性

在插入一条数据之后 如果非聚簇索引存储的是具体数据 则每个索引的叶子节点都需要更新

联合索引的底层存储结构

多个字段共同组织成一个索引

 

最左前缀原理

会根据索引创建时的字段顺序

先根据name排序 相等 再根据age 相等 再根据position

当使用联合索引时 需要 从左边按顺序开始 不能跳过

原因 :

只有在前面的字段生效时 后面的字段才是有序的 否则时无序的

后面的字段是局部有序的