索引设计—高并发场景微服务实战（六）

Hello，牛油们好，我是程序员Alan.

我在上一篇文章《 表结构设计—高并发场景微服务实战（五）》中，详细的写了如何选择合适的类型创建一张表，但表结构设计只是设计数据库最初的环节之一，我们还缺少数据库设计中最为重要的一个环节——索引设计，只有正确设计索引，业务才能达到上线的初步标准。

索引如果展开来讲有很多需要关注的地方，例如索引设计、业务应用与调优等等，本篇文章我会重点讲一下索引设计相关知识。

索引是什么？

索引是一门排序的艺术，索引是提升查询速度的一种数据结构。有效的设计并创建索引，会提升数据库系统的整体性能。索引之所以能提升查询速度，在于它在插入时对数据进行了排序（显而易见，它的缺点是影响插入或者更新的性能）。索引是对记录进行排序。

在目前的 MySQL 8.0 版本中，InnoDB 存储引擎支持的索引有 B+ 树索引、全文索引、R 树索引。这里我们先关注使用最为广泛的 B+ 树索引。

B+树索引结构

B+ 树索引是数据库系统中最为常见的一种索引数据结构，几乎所有的关系型数据库都支持它。

那你知道为什么关系型数据库都热衷支持 B+树索引吗？因为B+数是目前为止排序最有效率的数据结构。

B+树索引的特点是： 基于磁盘的平衡树，但树非常矮，通常为 3~4 层，能存放千万到上亿的排序数据。树矮意味着访问效率高，从千万或上亿数据里查询一条数据，只用 3、4 次 I/O。

又因为现在的固态硬盘每秒能执行至少 10000 次 I/O ，所以查询一条数据，哪怕全部在磁盘上，也只需要 0.003 ~ 0.004 秒。另外，因为 B+ 树矮，在做排序时，也只需要比较 3~4 次就能定位数据需要插入的位置，排序效率非常不错。

优化 B+ 树索引的插入性能

B+ 树在插入时就对要对数据进行排序，但排序的开销其实并没有你想象得那么大，因为排序是 CPU 操作（当前一个时钟周期 CPU 能处理上亿指令）。

真正的开销在于 B+ 树索引的维护，保证数据排序，这里存在两种不同数据类型的插入情况。

• 数据顺序（或逆序）插入： B+ 树索引的维护代价非常小，叶子节点都是从左往右进行插入，比较典型的是自增 ID 的插入、时间的插入（若在自增 ID 上创建索引，时间列上创建索引，则 B+ 树插入通常是比较快的）。
• 数据无序插入： B+ 树为了维护排序，需要对页进行分裂、旋转等开销较大的操作，另外，即便对于固态硬盘，随机写的性能也不如顺序写，所以磁盘性能也会收到较大影响。

你不可能要求所有插入的数据都是有序的，因为索引的本身就是用于数据的排序，插入数据都已经是排序的，那么你就不需要 B+ 树索引进行数据查询了。

所以对于 B+ 树索引，在 MySQL 数据库设计中，仅要求主键的索引设计为顺序，比如使用自增，或使用排序的 UUID，而不用无序值做主键。

二级索引

InnoDB 存储引擎的数据是根据主键索引排序存储的，除了主键索引外，其他的索引都称之为二级索引（Secondeary Index）， 或非聚集索引（None Clustered Index）。 二级索引也是一颗 B+ 树索引，但它和主键索引不同的是叶子节点存放的是索引键值、主键值。 下面的表User 中的 idx_name 就是二级索引。

CREATE TABLE User (
    id BIGINT AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    sex CHAR(6) NOT NULL,
    registerDate DATETIME NOT NULL,
    ...
    PRIMARY KEY(id), -- 主键索引
    KEY idx_name(name) -- 二级索引
)

如果用户通过列 name 进行查询，比如下面的 SQL：

SELECT * FROM User WHERE name = 'Alan'，

通过二级索引 idx_name 只能定位主键值，需要额外再通过主键索引进行查询，才能得到最终的结果。这种“二级索引通过主键索引进行再一次查询”的操作叫作“回表”。

你知道二级索引这样设计的一大好处是什么吗？如果记录发生了修改，那么其他索引无需进行维护，除非记录的主键发生了修改。

考虑额外创二级索引的开销

二级索引虽好，但不可以忽略了使用它带来的开销。创建二级索引的开销，主要表现在二级索引的维护、空间开销和回表开销三个方面。接下来，我们详细分析一下。

首先是二级索引的维护开销。创建 N 个二级索引，就需要再创建 N 棵 B+ 树，新增数据时不仅要修改聚簇索引，还需要修改这 N 个二级索引。

其次是空间开销。虽然二级索引不保存原始数据，但需要保存索引列的数据，所以会占用更多的空间。这样除了数据存储本身空间的开销，还额外增加了索引数据存储的开销。

最后是回表的代价。二级索引不保存原始数据，通过索引找到主键后需要再查询聚簇索引，才能得到我们要的数据。

函数索引

从 MySQL 5.7 版本开始，MySQL 就开始支持创建函数索引 （即索引键是一个函数表达式）。 函数索引有两大用处：

• 优化业务 SQL 性能；
• 配合虚拟列（Generated Column）。

先来看第一个, 优化业务 SQL 性能。

假设last_date建立了二级索引，下面这条SQL语句里仍有一个常见的错误，你知道是什么吗？

SELECT * FROM user  WHERE DATE_FORMAT(last_date,'%Y-%m') = '2022-10' 

DATE_FORMAT(last_date)不是索引，因此上述 SQL 无法使用二级索引last_date，会导致全表扫描。数据库规范要求查询条件中函数写在等式右边，而不能写在左边，就是这个原因。

要尽快解决这个问题，可以使用函数索引， 创建一个DATE_FORMAT(register_date) 的索引

ALTER TABLE user 
ADD INDEX 
idx_func_last_date((DATE_FORMAT(last_date,'%Y-%m')));

覆盖索引

创建一个user表。

create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
index k(k))
engine=InnoDB;

我们执行一条SQL语句 select ID from user where k between 66 and 99,

此时只需要查询ID的值， 而ID的值已经在K索引树上了，因此可以直接提供查询结果，不需要回表。也就是说在这个查询里面，索引K已经“覆盖了”我们的查询需求，我们称之为覆盖索引。

从上面的例子我们可以看到，覆盖索引可以显著提升查询性能，索引使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

以最左边的为起点任何连续的索引都能匹配上。

当创建(a,b,c)复合索引时，想要索引生效的话，只能使用 a和ab、ac和abc三种组合！

站在巨人的肩膀上：

• 姜承尧——MySQL实战宝典
• 林晓斌——MySQL实战45讲
• Java业务开发常见错误100例

**************************************