Mysql 存储大数据量常规知识浅析介绍

数据库  /  管理员 发布于 1年前   617

Mysql 存储大数据量常规知识浅析介绍。

Mysql 单表适合的最大数据量是多少？

我们说 Mysql 单表适合存储的最大数据量，自然不是说能够存储的最大数据量，如果是说能够存储的最大量，那么，如果你使用自增 ID，最大就可以存储 2^32 或 2^64 条记录了，这是按自增 ID 的数据类型 int 或 bigint 来计算的；如果你不使用自增 id，且没有 id 最大值的限制，如使用足够长度的随机字符串，那么能够限制单表最大数据量的就只剩磁盘空间了。显然我们不是在讨论这个问题。影响 Mysql 单表的最优最大数量的一个重要因素其实是索引。

我们知道 Mysql 的主要存储引擎 InnoDB 采用 B+树结构索引。那么什么是B+tree？那么 B+树索引是如何影响 Mysql 单表数据量的呢？

什么是B+tree？

1.B+Tree定义
B+树可以看作是B树的一种变形，在实现文件索引结构方面比B树使用得更普遍。
一棵 m 阶B+树可以定义如下：
树中每个非叶结点最多有 m 棵子树；
根结点 (非叶结点) 至少有 2 棵子树。除根结点外, 其它的非叶结点至少有 ém/2ù 棵子树；有 n 棵子树的非叶结点有 n-1 个关键码。
所有叶结点都处于同一层次上，包含了全部关键码及指向相应数据对象存放地址的指针，且叶结点本身按关键码从小到大顺序链接；
每个叶结点中的子树棵数 n 可以多于 m，可以少于 m，视关键码字节数及对象地址指针字节数而定。
若设结点可容纳最大关键码数为 m1，则指向对象的地址指针也有 m1 个。
结点中的子树棵数 n 应满足 n 属于[m1/2, m1]
若根结点同时又是叶结点，则结点格式同叶结点。
所有的非叶结点可以看成是索引部分，结点中关键码 Ki 与指向子树的指针 Pi 构成对子树 (即下一层索引块) 的索引项 ( Ki, Pi )，Ki 是子树中最小的关键码。
特别地，子树指针 P0 所指子树上所有关键码均小于 K1。结点格式同B树。
叶结点中存放的是对实际数据对象的索引。
在B+树中有两个头指针：一个指向B+树的根结点，一个指向关键码最小的叶结点。

2.B+Tree特性
B+Tree的搜索与B-Tree也基本相同，区别是B+Tree只有达到叶子结点才命中（B-Tree可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；
B+Tree的特性
所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
不可能在非叶子结点命中；
非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
更适合文件索引系统

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 求模，将数据分散到不同的数据库，具有相同数据用户的数据都被分散到一个库中。 按照日期，将不同月甚至日的数据分散到不同的库中。按照某个特定的字段求摸，或者根据特定范围段分散到不同的库中。