前言：作为后端开发，Mysql必不可少的技术栈，因此对于其底层的学习也必不可少。在本文中，作者将通过项目中遇到的一个实际问题，来对mysql的底层原理进行剖析。

案例中涉及到很多mysql底层的优化知识

项目案例

项目是一个app，其中有一个弹窗功能，管理员可以在后台配置一些消息，包含title、contnent、image，当任意用户打开app后，会通过弹窗的形式进行展示。此业务有以下特点：

1. 消息可以配置多条，弹窗上有一个close按钮，若有多条消息，那么将依次展示（第一条close后继续弹出第二条）
2. 弹窗上还有一个“不再显示”的按钮，用户可以屏蔽当前这条消息
3. 消息只会展示当前生效的若干条，由于是弹窗，考虑到用户体验，消息不会过多

对于这个屏蔽功能，设计了一张关联表message_ignore，包含字段user_id、message_id，用户屏蔽消息后就插入一条数据。在给app返回弹窗数据前，先查表，过滤掉用户已经屏蔽的消息，即：select message_id from message_ignore where message_id in (?,?) and user_id = ?;

那么问题来了，message_ignore表需要一个联合索引，有两个方案：(message_id, user_id)和(user_id, message_id)，哪个方案更优？为什么？

初步分析

分析索引性能，第一步当然是explain。但这个案例非常简单，稍有点mysql经验的人都知道，两种方案，都满足最左前缀原则，实际结果就是完全没区别。因此，从explain上，是看不出区别的。

原理剖析

Mysql IO的基本特点

Mysql是基于磁盘进行存储的，因此需要有一定的规则来对磁盘进行管理Innodb存储结构。内容是挺多的，涉及到表空间、段、区、页。我们这里重点关注：页。

页是mysql磁盘管理的基本单位，一个页大小是16KB，进行IO的时候，最小单位也是页，即：程序要读某一个条数据，实际上的流程是，先将它所以在的页整个读到内存中，然后再去访问内存中的数据。

这里稍作思考便可得出结论：程序要访问两条数据，如果他们在同一页上，实际上只需要一次IO即可。

Buffer Pool

因为IO速度相对内存访问来说非常慢，因此使用内存来做缓存是提升性能的有效手段。 Mysql采用了一种优化版的LRU算法来对页进行缓存管理。Buffer pool详情。

我们这里关注的重点是，即使msql对LRU做了重大改进，但最终效果，仍然是一个LRU的效果，即：某一页被访问得越频繁，它在内存中存在的时间便会越长

结合前面页的知识，可以得出结论：若某一页的数据经常性的被访问，那么这页数据几乎不会存在IO（纯内存访问，快得飞起）

联合索引的数据组织形式

Mysql底层，对于联合索引的组织形式（例如索引：a, b, c），先按照a进行聚合，再按b聚合，再按c聚合。

以项目中的案例来说，假设用户1、2、3、4都屏蔽了两条消息1、2，两种索引方案，数据组织如下

第一次结论

假设，目前生效的message_id=2，那么所有的sql都是select message_id from message_ignore where message_id in (2) and user_id = ?;

1. 如果是(message_id, user_id)这种方案，那么用户1访问后，page2会被load到内存，且会在内存中停留一段时间。此时，2、3、4中的任意用户进行访问，都可以直接读取内存中的页，便无需IO，并且page2将很可能会成为热点数据从而常驻内存。

   实际情况，一页数据，最多可存在7000+条数据，那么采取message_id在前的方案，这7000+用户中任意用户访问后，其他人都能从中获得收益（如果此时访问流量很高，那么其实大多数用户都能享受到热点页带来的性能提升）。

2. 如果是(user_id, message_id)这种方案，用户1访问后，page1会被load到内存，此时只有用户2可以享受到page1的缓存效果，用户3、4访问，则需要重新IO。

   实际情况中，数据按照user_id聚合，因此message_id=2的数据，会分散到不同的页，那么这个索引的大部分页都可能被访问到（任何用户都可能打开app），因此不太可能形成热点数据，所以多数的sql都需要进行IO。

   通过以上分析，我们便有了足够的理由选择(message_id, user_id)这个索引了。但是作为技术研究，我们可以再继续深入分析一下。

B+Tree节点分裂

了解过B树都知道存在一个节点分裂的过程

简单演示下分裂过程，如下图：每个节点代表一个磁盘页，其中一个页上放了数据20、25、26，且该页已经放满。 现在需要插入数据23，本来应该放到20和25中间，但是因为该页没有额外空间，所以需要将该页分裂。 分裂结果就是将25、26放到一个新的页中，然后把23放到20后面

现在来看我们案例中的情况

首先是(message_id, user_id) 因为message_id相同的数据，都集中在少数的几个页上（比如5），那么再怎么分裂，也只能是从1分裂到5总共4次。

然后再来看(user_id, message_id) 数据按照user_id聚合，假如某一页上放了user_id=10和user_id=11的所有数据且已经沾满，当user_id=10的用户新屏蔽了一条数据后，该页就会分裂。这种情况在整张表的使用过程中，会相当频繁。

innodb四大特性

插入缓冲

最基本的插入数据流程：

1. 计算数据放到哪一页
2. 加载页到内存，并在内存中把数据插入到该页
3. 将整页数据刷新到磁盘

这里存在的一个问题就是，如果每次插入都这么做的话，那实际上会有非常多的加载页的动作，导致IO压力过大。

插入缓冲就是，第2步的时候，如果该页数据不在内存，就放到内存中的一个缓冲区，在一定时机后，统一刷入磁盘。好处就是，可以合并多次插入，减少IO压力。

对于插入缓冲，我们换个角度来看，它是一种手段，目的则是：减少插入数据时候，对页的加载次数（例如：插入100条数据，原本需要80次IO，有了插入缓冲后，只需要40次IO）

再来看我们这个案例，如果是(message_id, user_id)，因为存在热点数据，所以这些页大概率会一直在内存中，就无需IO，相对于(user_id, message_id)来说，加载页的次数页更少，因此也起到了相同的效果（例如：插入100条数据，原本需要80次IO，有了插入缓冲后，只需要40次IO）。

预读

innodb在load某一页的时候，会顺便把它旁边的页页load进来，如果后续访问到这些页，那么这次预读就很成功。

由于(message_id, user_id)数据按照message_id聚合，因此load某一页的时候，很可能旁边的，记录了相同message_id的页也会被加载。

而(user_id, message_id)就起不到这个效果。

实验

为了验证效果，做了一下实验，建了两张表

CREATE TABLE `msg_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `msg_id` int(11) NULL DEFAULT NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `msg_user_idx`(`msg_id`, `user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `user_msg`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `msg_id` int(11) NULL DEFAULT NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `msg_user_idx`(`user_id`, `msg_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

两张表插入完全相同的1000W条数据，且都执行过optimize table。Mysql的buffer pool大小为8M。

对两张表做完全相同的35000次查询，结果如下：

msg_user平均耗时：3.5秒

user_msg平均耗时：7.5秒

和预期的结果一致，两种索引虽然都满足了最左前缀原则，但是在这种特定的场景下，性能上还是有1倍的差距。