记MySQL2000万行数据性能下降问题

/ 数据库 / 1 条评论 / 150 浏览

不知什么时候开始,流行了一种说法:B+树存储2千万行以上的数据性能会急剧下降。这种说法实际上并不可靠。

这种方法是在特定情况下的理论设想

引擎:Inndb

索引:B+树,

主键类型:BigInt

行数据大小:1K

推理

先介绍一下Inndb下聚簇索引的特性

  1. 只有叶子节点存放数据
  2. 非叶子节点不存放数据,只有主键+指针。

image.png

由此可以推测得到公式

单表数据量 = 叶子节点数 * 页大小  / 行大小

MySQL对磁盘的操作最小单位是页。InnoDB下页大小默认是16K

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.03 sec)

主键BigInt = 8字节

指针 = 6字节 固定大小,不可修改

可以得出16K可以存放 16* 1024 / 14 = 1170个节点

索引高度叶子节点数数据量索引大小
2117018,72016K
31,368,90021,902,40018M
41,601,613,00025,625,808,0002GB

3层,可以将索引完全load到内存中,还可以缓存大部分的数据页。这样IO就会减少很多,平均IO次数< 1 。

4层,将索引全完load到内存,基本不太可能缓存进去,毕竟这只是一个表的索引。很难将索引缓存进去,如果索引没有在Cache,读取一次数据就需要 2次IO。效率大大降低。

总结

本质上的性能下降,跟多少条数据有相关性,但是2000万条是理论值,参考价值不是很大,主要还是参考索引是否能否load到内存中。每个SQL有几次IO。

**索引高度越高,性能越差,**同时可以得出为什么不用红黑树,因为红黑树的高度很高。

降低索引高度的方法

  1. 降低主键长度
  2. 修改页大小

减少IO的方法:

  1. 增加 innodb_buffer_pool_size
  2. 优化索引大小(包含整个mysql实例的表)

不过已经达到这个数据量了,已经要考虑是否需要分区或者分库分表的了。