文章目录

  • 基础操作
  • 常见的索引模型
  • 哈希表
  • 有序数组
  • 二叉搜索树
  • InnoDB 的索引模型
  • 基于主键索引和普通索引的查询有什么区别？
  • 索引维护
  • 覆盖索引
  • 普通索引和唯一索引
  • 从性能的角度考虑，你选择唯一索引还是普通索引呢？选择的依据是什么呢？
  • 查询过程
  • 更新过程
  • change buffer
  • 索引选择和实践



MySQL索引可以大大提高MySQL的检索速度。

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。对于数据库的表而言，索引其实就是它的“目录”。

基础操作

创建索引：

create index index_name on table_name(column_name);

修改表结构（添加索引）：

alter table tablename add index index_name(column_name);

创建表时指定索引：

create table mytable(
	id int not null,
	username varchar(16) not null,
	index[index_name] (username(length))
);

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

删除索引：

drop index [index_name] on mytable;

常见的索引模型

哈希表、有序数组和搜索树。

哈希表

哈希表是一种以 键-值（key-value）存储数据的结构。

用哈希函数将key转换成一个确定的位置，多个key值经过哈希函数的运算，会出现同一个值的情况，解决办法是拉出一个链表：

联合索引唯一性_搜索


如图，User2和User4根据身份证号算出的值都是N，所以后面还跟着一个链表。

假设，这时要查ID_card_n2对应的名字：首先将ID_card_n2通过哈希函数计算出N，然后，按顺序遍历，找到User2。

注意，ID_card_n并不是递增的，这样增加User的速度会很快，单做区间查询时就会不好。所以哈希索引做区间查询的速度是很慢的。

哈希表这种结构适用于只有等值查询的场景。

有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀。

联合索引唯一性_联合索引唯一性_02


假设身份证号没有重复，这个数组按身份证号递增顺序保存。如果要查ID_card_n2 对应的名字，就可以用二分法快速得到，时间复杂度O(log(N))。

同理，可以用二分法进行范围查询。

如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。

所以，有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据。

二叉搜索树

联合索引唯一性_主键_03


二叉搜索树的特点是：父节点左子树所有节点的值小于父节点的值，右子树所有节点的值大于父节点的值。

这样如果你要查 ID_card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是 O(log(N))。

为了维持查询复杂度O(log(N))，就需要保持这个树是平衡二叉树。所以更新的时间复杂度也是O(log(N))。

二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

你可以想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

数据库底层存储的核心就是基于这些数据模型的。每碰到一个新数据库，我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库的适用场景。

在 MySQL 中，索引是在存储引擎层实现的，即不同存储引擎的索引的工作方式并不一样。

InnoDB 的索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

每一个索引在 InnoDB 里面对应一棵 B+ 树。

假设，一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。

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)。

ID

k

R1

100

1

R2

200

2

R3

300

3

R4

400

4

R5

500

5

两棵树的示例示意图如下。

联合索引唯一性_主键_04


根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。（也就是存的是R1、R2…）

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。（也就是存的是对应的主键的值100、200…）

基于主键索引和普通索引的查询有什么区别？

如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

基于非主键索引的查询需要多扫描一棵索引树。因此，在应用中应该尽量使用主键查询。

索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。
而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。
当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

哪些场景下应该使用自增主键，而哪些场景下不应该？
自增主键：NOT NULL PRIMARY KEY AUTO_INCREMENT

插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。

自增主键的插入数据模式，复合递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到移动记录，也不会触发叶子节点的分裂。

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

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

从性能和存储空间方面考量，自增主键往往是更合理的选择。

每一张表是好几棵B+树，树结点的key值就是某一行的主键，value是该行的其他数据。新建索引就是新增一个B+树，查询不走索引就是遍历主B+树。

问题：在表T中，执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

表T的初始化语句：

create table T (
	ID int primary key,
	k int NOT NULL DEFAULT 0, 
	s varchar(16) NOT NULL DEFAULT '',
	index k(k)
)engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

联合索引唯一性_联合索引唯一性_05


这条SQL 查询语句的执行流程：

  1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
  2. 再到 ID 索引树查到 ID=300 对应的 R3；
  3. 在 k 索引树取下一个值 k=5，取得 ID=500；
  4. 再回到 ID 索引树查到 ID=500 对应的 R4；
  5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表。这个查询过程，读了k索引树3条记录，回表了2次。

在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？

覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

基于上面覆盖索引的说明，我们来讨论一个问题：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？

市民表的定义：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

身份证号是市民的唯一标识，即有根据身份证号查询市民信息的需求，只要在身份证号字段上建立索引就够了。

但如果现在有一个高频请求，根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

普通索引和唯一索引

唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
创建方式：

create unique index index_name on table_name(username(length));

修改表结构：

alter table table_name add unique index_name(username(length));

在不同的业务场景下，应该选择普通索引，还是唯一索引？

假设你在维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，就会执行类似这样的
SQL 语句：

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

逻辑正确：要么给 id_card 字段创建唯一索引，要么创建一个普通索引。

从性能的角度考虑，你选择唯一索引还是普通索引呢？选择的依据是什么呢？

联合索引唯一性_联合索引唯一性_06

从这两种索引对查询语句和更新语句的性能影响来进行分析。

查询过程

查询语句：

select id from T where k=5;

这个查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

  • 对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
  • 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

这个性能差距微乎其微。

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。
因为引擎是按页读写的，所以说，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。
当然，如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

更新过程

change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

change buffer，是可以持久化的数据。change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

索引选择和实践

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。

如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。