Mysql索引结构&页&聚集索引&非聚集索

2020-06-07  本文已影响0人  机器学习架构

[toc]




摘要

第一部分主要从数据结构及算法理论层面讨论MySQL数据库索引的数理基础。
第二部分结合MySQL数据库中MyISAM和InnoDB数据存储引擎中索引的架构实现讨论聚集索引、非聚集索引及覆盖索引等话题。

索引

索引概述

MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护者满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。

索引优势劣势

索引结构

索引是在MySQL的存储引擎层中实现的,而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引:

聚集索引 复合索引区别:
聚簇索引:将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据
非聚簇索引:将数据与索引分开存储,索引结构的叶子节点指向了数据对应的位置

BTREE 结构

BTree又叫多路平衡搜索树,一颗m叉的BTree特性如下:

以5叉BTree为例,key的数量:公式推导[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4 。当n>4时,中间节点分裂到
父节点,两边节点分裂。
插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。
动画演示
演变过程如下,可以通过上面的网站走一下这个过程:
1). 插入前4个字母 C N G A
2). 插入H,n>4,中间元素G字母向上分裂到新的节点
3). 插入E,K,Q不需要分裂
4). 插入M,中间元素M字母向上分裂到父节点G
5). 插入F,W,L,T不需要分裂
6). 插入Z,中间元素T向上分裂到父节点中
7). 插入D,中间元素D向上分裂到父节点中。然后插入P,R,X,Y不需要分裂
8). 最后插入S,NPQR节点n>5,中间节点Q向上分裂,但分裂后父节点DGMT的n>5,中间节点M向上分裂

在这里插入图片描述

B+TREE 结构

B+Tree为BTree的变种,B+Tree与BTree的区别为:
1). n叉B+Tree最多含有n个key,而BTree最多含有n-1个key。
2). B+Tree的叶子节点保存所有的key信息,依key大小顺序排列。
3). 所有的非叶子节点都可以看作是key的索引部分
通过下面的图,可以非常直观的看出来二者的差异:
[图片上传失败...(image-6caab0-1591539893523)]


B+树
MySQL中的B+Tree

它是InnoDB管理存储空间的基本单位,一个页的大小一般是16KB。InnoDB为了不同的目的而设计了许多种不同类型的页,比如存放表空间头部信息的页,存放Insert Buffer信息的页,存放INODE信息的页,存放undo日志信息的页等等等等。


页结构

插入数据过程

索引分类

  1. 单值索引 :即一个索引只包含单个列,一个表可以有多个单列索引
  2. 唯一索引 :索引列的值必须唯一,但允许有空值
  3. 复合索引 :即一个索引包含多个列

索引语法

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name(index_col_name,...)
示例 :
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
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)

show index from table_name;

DROP INDEX index_name ON tbl_name;

索引设计原则

索引的设计可以遵循一些已有的原则,创建索引的时候请尽量考虑符合这些原则,便于提升索引的使用效率,更高
效的使用索引。

  1. 对查询频次较高,且数据量比较大的表建立索引
  2. 索引字段的选择,最佳候选列应当从where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合
  3. 使用唯一索引,区分度越高,使用索引的效率越高。
  4. 索引可以有效的提升查询数据的效率,但索引数量不是多多益善,索引越多,维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低DML操作的效率,增加相应操作的时间消耗。另外索引过多的话MySQL也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但无疑提高了选择的代价。
  5. 使用短索引,索引创建之后也是使用硬盘来存储的,因此提升索引访问的I/O效率,也可以提升总体的访问效率。假如构成索引的字段总长度比较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升MySQL访问索引的I/O效率。
  6. 利用最左前缀,N个列组合而成的组合索引,那么相当于是创建了N个索引,如果查询时where子句中使用了组成该索引的前几个字段,那么这条查询SQL可以利用组合索引来提升查询效率。

聚触索引 & 非聚触索引

聚集索引与非聚集索引的区别是:叶节点是否存放一整行记录
InnoDB 主键使用的是聚簇索引和辅助索引,MyISAM 不管是主键索引,还是二级索引使用的都是非聚簇索引。
下图形象说明了聚簇索引表(InnoDB)和非聚簇索引(MyISAM)的区别:


索引区别

参考:


你的鼓励也是我创作的动力

打赏地址

上一篇 下一篇

猜你喜欢

热点阅读