mysql表数据量 innodb_Mysql-InnoDB分表真的有意义吗?

可以看一下我的这个回答，恰好回答了这个问题。为什么mysql表分区不是很常用？​www.zhihu.com

在单机mysql实例(不是分布式数据库 的情况下)使用分区表的原因，主要是因为单表数据量太大导致索引过大，从而降低了查询性能。

考虑一个巨大的单表并且主键字段较大的最坏情形，我们来计算一下主表b+树的高度。

例子1。 比如单表100亿行，每行数据平均占用1000字节的存储空间，16KB的page size，那么主表页节点就要占用约10TB空间，约7亿个页面。假设主键占用空间较大导致内节点每个索引行平均占据256字节，于是每个内节点页面存放64个索引行。那么主表b+树的高度就是

1 + ceiling(lg(64, 700000000)) = 6，

假设主键索引取的非常蠢导致内节点每个索引行占据512字节，那么主表b+树的高度是

1 + ceiling(lg(32, 700000000)) = 7.

例子2。 假设上述数据只有1千万行，那么用相同的方法计算可得主表b+树的高度分别是4 (256字节的主键索引)和5(512字节的主键索引) --- 高度只相差2。

假设例子1的主表真的做了1000个分区那么每个分区表就是例子2的主表，那么通过分区，可以让每次树搜索减少2次页面获取(极大概率从buffer pool获取，否则系统性能无法实用)。 这个差别确实会导致例子2查询性能有所提升，但是区别其实并不大。

另外，分区后另一个性能优势是分散了根节点的访问，从而提升并发性能。不过要知道b+树做遍历并不会持有内节点页面的事务锁，只需要短暂持有根结点页面的read latch，所以除了页面分裂(低频操作)以外的遍历，是可以并发执行的。

综上，我认为这单机做表分区获得的性能提升的理论上限很有限，估计也就10%以内，随数据和查询特征略有波动，随数据库系统的实现也有不同。感兴趣的同学可以使用postgresql或者mysql做一个实验对比一下。在不同的数据库系统实现中，分区表的实际性能与单表相比，提升各不相同，甚至未必能提升，甚至会降低。

mysql分区表的详情如下。

首先，如果很多表都做分区，会导致mysql innodb数据目录下文件数目非常多(比如1000个表分区会产生2000个文件)，从而使操作系统的文件系统工作效率降低。并且由于mysql打开表文件的数目限制(该限制虽然可以手动修改但是也受限与操作系统可用资源量)从而导致打开的表反复被淘汰和重新打开，从而降低了所有查询的性能。

在mysql5.7的早期版本中，分区表的实现性能较差，与相同数据量的单表相比性能下降约10%。后来在mysql5.7.19才做了优化，可以去http://bugs.mysql.com上面看一下这个bug。但是即使这个bug修复之后，分区表仍然比相同数据量的单表有大约5%的性能下降(8个分区，100GB数据量，sysbench oltp测例)。

在上例中如果单表占据10TB空间那么单个服务器节点的计算资源(内存，cpu，存储)恐怕已经无法支持业务运行了，所以大概率还是要做分库分表才行。而如果单表只有1TB以内的空间那么完全没必要做表分区。也就是说，在单节点mysql实例中做表分区并没有什么必要，也不会有明显的性能优势。

如果是做分库分表的话，那么通过分区表来实现分库分表是一些简单的分表中间件常用的方法，也比较有效。在昆仑分布式数据库中，我们在计算节点中实现分库分表，在存储节点中永远使用单表做存储，不使用分区表，就是基于上述mysql分区表性能降低的原因。