MYSQL5.7分区表

阿里云mysql基础信息
版本 ：5.7.32
配置：2核4G
数据来源：https://www.jianshu.com/p/d5d4fa4bd713
表结构：
分区表表结构

不分区表结构

数据量:1亿

参考阿飞的博客，网址：https://www.jianshu.com/p/d5d4fa4bd713，阿飞的博客没有给出分区前后的查询性能对比，自测下。
以下是测试主键索引
测试分区表sql

select * from employees_partition where job_no = ‘NO.8888891’;

连续执行五次用时：
0.288s
1.552s
1.367s
0.444s
0.461s

测试分区关闭缓存sql

select SQL_NO_CACHE * from employees_partition where job_no =
‘NO.8888891’;

连续执行五次用时：
0.290s
0.438s
0.566s
0.533s
0.908s

测试不分区sql

select * from employees_nopartition where job_no = ‘NO.8888891’;

连续执行五次用时：
0.355s
0.440s
0.523s
0.326s
0.567s

测试不分区关闭缓存sql

select SQL_NO_CACHE * from employees_nopartition where job_no =
‘NO.8888891’;

连续执行五次用时：
0.477s
0.630s
0.325s
0.495s
0.285s

以上并未发现分区表比不分区查询性能强。
感兴趣的可以自测下普通索引的性能对比。
考虑一个巨大的单表并且主键字段较大的最坏情形，我们来计算一下主表b+树的高度。
比如单表100亿行，每行数据平均占用1000字节的存储空间，16KB的page size，那么主表页节点就要占用约10TB空间，约7亿个页面。
假设主键索引取的非常蠢导致内节点每个索引行占据512字节，那么主表b+树的高度是

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

假设上述数据只有1千万行，那么用相同的方法计算可得主表b+树的高度分别是5，高度相差2。

假设例子中的单表100亿做了1000个分区，那么通过分区，可以让每次树搜索减少2次页面获取，这个差别确实会导致查询性能有所提升，但是区别其实并不大。

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

以上,并不建议用mysql分区表。