共计 2778 个字符，预计需要花费 7 分钟才能阅读完成。
索引设计—高并发场景微服务实战（六）
你好，我是程序员 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
