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

文章插图

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

文章插图

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 请求，再合并数据，并将合并后的结果返回给上层调用者。