乐趣区

关于索引:索引设计高并发场景微服务实战六

jiezi

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

你好，我是程序员 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 例

如果对您有帮忙，欢送关注我的微信公众号和我交换：ProgrammerAlan

退出移动版