Mysql 单表适合的最大数据量是多少?如何优化其性能?


Mysql 单表适合的最大数据量是多少?如何优化其性能?

文章插图
 
我们说 MySQL 单表适合存储的最大数据量,自然不是说能够存储的最大数据量,如果是说能够存储的最大量,那么,如果你使用自增 ID,最大就可以存储 2^32 或 2^64 条记录了,这是按自增 ID 的数据类型 int 或 bigint 来计算的;如果你不使用自增 id,且没有 id 最大值的限制,如使用足够长度的随机字符串,那么能够限制单表最大数据量的就只剩磁盘空间了 。显然我们不是在讨论这个问题 。
影响 Mysql 单表的最优最大数量的一个重要因素其实是索引 。
我们知道 Mysql 的主要存储引擎 InnoDB 采用 B+树结构索引 。(至于为什么 Mysql 选择 b+树而不是其他数据结构来组织索引,不是本文讨论的话题,之后的文章会讲到 。)那么 B+树索引是如何影响 Mysql 单表数据量的呢?
B+树一棵 B+树如下所示:
Mysql 单表适合的最大数据量是多少?如何优化其性能?

文章插图
 
Mysql 的 B+树索引存储在磁盘上,Mysql 每次读取磁盘 Page 的大小是 16KB,为了保证每次查询的效率,需要保证每次查询访问磁盘的次数,一般设计为 2-3 次磁盘访问,再多性能将严重不足 。Mysql B+树索引的每个节点需要存储一个指针(8Byte)和一个键值(8Byte) 。因此计算16KB/(8B+8B)=1K 16KB 可以存储 1K 个节点,3 次磁盘访问(即 B+树 3 的深度)可以存储 1K _ 1K _ 1K 即 10 亿数据 。
如果查询依赖非主键索引,那么还涉及二级索引 。这样数据量将更小 。
拆分分而治之——没有什么问题不能通过拆分一次来解决,不行就拆多次 。
Mysql 单表存储的数据量有限 。一个解决大数据量存储的办法就是分库分表 。说白了就是一个数据库一张表放不下那么多数据,那就分多个数据库多张表存储 。
拆分可分为垂直拆分和水平拆分 。
垂直拆分是按照不同的表(或者 Schema)来切分到不同的数据库(主机)之上,水平拆分则是根据表中的数据的逻辑关系,将同一个表中的数据按照某种条件拆分到多台数据库(主机)上面或多张相同 Schema 的不同表中 。
垂直拆分的最大特点就是规则简单,实施也更为方便,尤其适合各业务之间的耦合度非常低,相互影响很小,业务逻辑非常清晰的系统 。在这种系统中,可以很容易做到将不同业务模块所使用的表分拆到不同的数据库中 。根据不同的表来进行拆分,对应用程序的影响也更小,拆分规则也会比较简单清晰 。
水平拆分与垂直切分相比,相对来说稍微复杂一些 。因为要将同一个表中的不同数据拆分到不同的数据库中,对于应用程序来说,拆分规则本身就较根据表名来拆分更为复杂,后期的数据维护也会更为复杂一些 。
垂直拆分最直接的就是按领域拆分服务,隔离领域数据库 。如此每个库所承担的数据压力就减少了 。
水平拆分就是将同一个 Schema 的数据拆分到不同的库或不同的表中,这样每个表的数据量也将减小,查询效率将更高效 。水平拆分就涉及到表的分片规则问题 。
几种典型的分片规则包括:
  • 按照用户 ID 求模,将数据分散到不同的数据库,具有相同数据用户的数据都被分散到一个库中 。
  • 按照日期,将不同月甚至日的数据分散到不同的库中 。
  • 按照某个特定的字段求摸,或者根据特定范围段分散到不同的库中 。
实现门面模式——没有什么问题不能通过添加一个中间层来解决 。
垂直拆分的一个方案就是在应用层使用多个数据源,按业务访问不同的数据源 。另外更好方案其实就是微服务化 。按不同的业务领域来拆分微服务,明确领域边界,隔离领域数据库 。这样将对数据的存取内聚到独立的服务之中,对外提供统一的接口 。在需要同时依赖多个服务时,我们可以通过添加门面应用来组合底层服务的数据,以提供更符合上层业务需求的接口,这些服务往往更接近真实的业务 。而底层的服务则是更加内聚的资源服务 。
代理模式——没有什么问题不能通过添加一个中间层来解决 。
对于水平拆分应该尽量屏蔽拆分带来的数据访问困恼,为了让上层业务无需关心下层数据组织方式 。水平拆分往往通过添加一个代理层来做这些事情,代理层对上提供虚拟表,这些虚拟表就像我们在单库上设计的单表一样;代理层对下解析和拆分执行 sql,然后按相应规则在不同的库和表执行相应的 sql 请求,再合并数据,并将合并后的结果返回给上层调用者 。


推荐阅读