Mysql为什么要分表

为什么要分表

昨天面试，面试官我了一些Mysql分库分表的问题，我发现很多我都不知道。我仅仅知道最基础的分表机制。至于到底为什么分库分表，分库分表之后的事务我都不知道。

问到为什么分表，我说因为数据库单表数据量最合适两千万，再多可能会导致索引的层数增加。但是经过面试官追问后，我发现似乎索引的层数并不会增加。
面试结束后，我又研究了一下这个问题，发现我忘了一个关键的点，索引的结点是按页组织的，就是说根节点就是一个页的大小。这就是关键点，之前学习这块的时候我是记得的，太久不复习导致忘了这点。正所谓学而时习之。下面具体说说这个问题。

Mysql默认引擎InnoDB中，页的默认大小是16k。也就是说我们的根节点是一个页，按照b+树结构，他还要存储子结点的指针，能存的子节点指针是有限的。同时我们的叶子结点也是一个页，叶子结点能存放的记录行数等于一个页能存的记录行数。知道了这些前提条件我们就可以通过简单计算来做一个验证。
这里借用一下小林coding画的索引结构的图，如果对索引不熟悉的可以去看他的文章，我这里主要还是剖析一下我上面说的那个单表数据存储数量的问题。https://xiaolincoding.com/mysql/index/page.html

我们可以看到非叶结点存放的结构是多个 索引项+指针。我们讨论主键索引，对于主键索引，就是 主键值+指针。设主键类型为bigint，占8字节，innodb中一个指针6字节。6+8=14字节。同时页的默认大小是16k。我们就可以计算，一个页中可以存放多少个这种 主键值+指针的组合。16384/14=1170.2（这里16k我差点按16000来算了，低级错误，实属不该）。相当于一个页能存放1142个这样的组合。他们每个中都含有一个指针，指向子结点。对于一个三层的树，则为1170117016k=21,913,098k。这就是最终第三层数据页所能存放的数据量，一般单条记录大小为1k。则对应三层b+树大约2000万条记录。当然根据平均记录大小的不同，三层索引能容纳的记录数也不同。这个计算是相当粗略的，因为页本身还有头部。不过在数据量级上不会有差别。同时也能计算出四层索引约能存放300亿条数据。
写到这里我突然有个疑问，即使超过2000万，最后变成四层索引，也仅是多加了一次磁盘io，难道真的会因为这个导致所谓的查询性能大幅下降吗？具体多一次io的性能损耗就要落到磁盘性能上了，就有些跑题。在网上看到很多人做了实验，确实在超过2000万行时，查询性能会下降。倘若不是因为磁盘io，那么还可能是哪些原因呢？

我找到了一篇英文文章说了这个问题。
https://yishenggong.com/2023/05/22/is-20m-of-rows-still-a-valid-soft-limit-of-mysql-table-in-2023/
文章观点是所谓2000万的数据是多年前在hdd（传统硬盘）上做出的判断，当下多使用SSD（固态硬盘）这个结论已经立不住了。因为对于传统磁盘，一次磁盘io要很久，多一次都是很大的性能损失。而对于SSD来说，磁盘IO的速度已经是可以接受的了。
具体的实验可以看一下这篇文章，作者写的很清楚，我觉得没必要做重复性工作再把作者的实验拿出来在复述一遍。
我简单说一下这篇文章的结论：

1. Innodb_buffer_pool大小/表大小决定了性能是否会降级。
2. 对于是否分表来说，更可靠的指标是查询所需时间和缓冲池命中率，如果查询始终命中缓冲区，不会出现任何性能问题。
3. 除了分表外，增加Innodb_buffer_pool的大小或者数据库内存也是一种对抗性能下降的选择。
4. 如果可能，请避免在生产环境中select *，因为在最坏的情况下，这会导致 2 次索引树查找。
5. 考虑到SSD现在很流行，20M行并不是MySQL表的一个非常有效的软限制。

感悟

看完这篇文章我突然明白过来，分表只是手段，目的是为了提高查询性能，它并不是实现目的的唯一途径。提高Innodb_buffer_pool大小也是手段。我对Mysql的查询优化策略并不了解，仅仅了解一些简单的查询原则。网上略微搜了一下，感觉是一门很大的学问，这部分是比较重要的，我觉得可以是一个Mysql进阶的学习方向。

回到最开始的问题，为什么要分表？

1. 首先表数据量过大，查询的数据量也很大时，你的Innodb_buffer_pool大小必然是无法容纳那么大量的数据的，所以命中率低，很影响性能。
2. 其次索引高度增加到第四层，确实是会增加一次磁盘io，确确实实是有性能损耗的。
   暂时只能想到这两个原因，欢迎大伙补充。
   重要的还是性能问题。如果没有性能问题，你一个表一亿数据不分表我觉得也没问题。

后续

对于分库分表，会有很多的问题：
分布式事务问题
跨节点关联查询
主键重复
分片算法
使用什么中间件
等等

本来打算一篇文章写完，结果仅仅写完分表的原因就花了好久。标题也从Mysql分库分表变成了Mysql为什么要分表。后面在写这些问题吧。

昨天面试官就问了分库分表会有哪些问题，我发现自己确实不知道，只知道数据迁移这个问题。面试官人很好，教我想把分库分表写在简历上作为亮点，应该深入的研究一下这些问题，中间件是怎么解决的。我发觉自己项目中仅仅简单对数据库表做了分表，对于更深层次的东西确实缺少了解。这块应该是接下来的学习重点。
对于一些技术来说还是不要不求甚解，明白更深层次的东西才是真正属于你的知识。感谢昨天的面试官。

文章欢迎大伙批评指正。