[图片] 深入浅出索引(上) 什么是索引?,索引的出现其实就是为了提高数据查询效率,就像书的目录一样。 一本 500 页的书,如果你想快速找到其中的某一个知识点,在不借助目录的情况下,那我估计你可得找一会儿。同样,对于数据库的表而言,索引其实就是它的“目录” 。 索引的常见模型 哈希表(key-vlalue[value ..

mysql45 讲笔记 -- 深入浅出索引(上)

深入浅出索引(上)

什么是索引?,索引的出现其实就是为了提高数据查询效率,就像书的目录一样。 一本 500 页的书,如果你想快速找到其中的某一个知识点,在不借助目录的情况下,那我估计你可得找一会儿。同样,对于数据库的表而言,索引其实就是它的“目录” 。

索引的常见模型

树结构中的“二叉树”搜索效率是最高的,但是大多数的数据库存储是采用的是多(N)叉树,原因是索引不止在内存中,而且还要写到磁盘中。

假设有一颗 100 万节点的平衡二叉树,树高 20,查询一次可能需要访问 20 个数据快,假设一个数据需要 10ms 左右寻址,也就是说对于一个 100 万行的表,如果用二叉树来存储,单独访问一行,可能需要 20*10ms=0.2s,速度慢。

其中 InnoDB 一个整数字段的索引为例,这个 N 差不多为 1200,这样树高 4 的时候,数据就已经可以存到 17 亿了。

InnoDB 的索引模型

在 InnoDB 中,表都是根据主键顺序以索引的形式存放的,这种存放的方式称之为索引组织表,而且数据都是存储在 B+ 树中的。其中每一个索引在 InnoDB 里面对应一颗 B+ 树

下面我们以一个例子来分析一下 InnoDB 的索引模型

建表语句:

mysql> create table T(
		id int primary key,
		k int not null,
		name varchar(16),
		index(k)
	)engine=InnoDB;

索引模型:

表中 R1~R5 的(ID,k)值分别为(100,1)、(200,2),(300,3)、(500,5)、(600,6)如下表

从上图中我们可以索引类型分为主键索引和非主键索引。

其中主键索引的叶子节点村的是整行数据。在 InnoDB 里,主键索引也称之为聚簇索引(clustered index)

而非主键索引的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也称之为二级索引(secondary index)

主键索引和非主键索引的区别

我们以例子来说明它们之间的区别

也就是说,基于非主键索引的查询需要多扫描一颗索引树。因此我们在应用中应该尽量使用主键查询。

索引维护

B+ 树为了维护索引的有序性,在插入新值的时候需要做必要的维护。上图为例,如果插入的新行 ID 为 700,则只需要在 R5 的记录后面插入一个新记录。如果插入的 ID 是 400,这就相对比较麻烦了,需要逻辑上移动后面的数据,空出位置。

而更加糟糕的是如果所在的数据页已经满了,根据 B+ 树的算法,这个时候需要申请一个新的数据页,然后挪动部分数据过去,这个过程称之为页分列。在这种情况下,性能自然回受影响。

除了性能外,页分列操作还影响数据页利用率,原本放在一个页的数据,现在分到两个页中,整体上利用率降低了 50%。

有页分裂就有合并,当相邻的两个页由于数据的删除,利用率很低,会将页合并,这个过程是页分列的逆过程。

什么时候需要用自增索引

自增主键是指自增列上定义的主键,在建表语句中一般是这么定义的:NOT NULL PRIMARY KEY AUTO_INCREMENT。

也就是说,自增主键的插入数据模式,正符合了我们前面提到的递增插入的场景。每次插入一条新记录,都是追加操作,都不涉及到其他挪动其他记录,也不会触发叶子节点的分裂。

有业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本数据相对较高。

除了考虑性能之外,我们还可以从存储空间的角度看。假设你的表中的确有一个唯一字段,比如字符串类型的身份证号,那应该用身份证做主键,还是用自增字段做主键呢?

由于每个非主键索引需要在叶子节点存储主键的值,如果用身份证做主键,那么每一个二级索引的叶子节点占用 20 个字节,如果用整形做主键,则只要 4 个字节,如果是长整形(bingin)则需要 8 个字节。

显然主键的长度越小越好,这样普通索引占用的空间就越小。所以从存储空间的角度来看,自增主键就往往比较合理的选择。

那种情况适合业务字段字节直接做主键?

  1. 只有一个索引
  2. 该索引必须是唯一索引。

自问自答

  1. 什么是 B+ 树?
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    496 引用 • 500 回帖 • 777 关注
  • 笔记

    好记性不如烂笔头。

    206 引用 • 519 回帖
回帖
请输入回帖内容...