关于java:MySQL-处理大数据表的-3-种方案写的太好了建议收藏

作者：马佩 \
链接：https://juejin.cn/post/7146016771936354312

场景

当咱们业务数据库表中的数据越来越多，如果你也和我遇到了以下相似场景，那让咱们一起来解决这个问题

• 数据的插入, 查问时长较长
• 后续业务需要的扩大 在表中新增字段 影响较大
• 表中的数据并不是所有的都为无效数据 需要只查问工夫区间内的

评估表数据体量

咱们能够从表容量 / 磁盘空间 / 实例容量三方面评估数据体量，接下来让咱们别离开展来看看。

举荐一个开源收费的 Spring Boot 最全教程：

https://github.com/javastacks/spring-boot-best-practice

表容量：

表容量次要从表的记录数、均匀长度、增长量、读写量、总大小量进行评估。个别对于 OLTP 的表，倡议单表不要超过 2000W 行数据量，总大小 15G 以内。访问量：单表读写量在 1600/ s 以内

查问行数据的形式：咱们个别查问表数据有多少数据时用到的经典 sql 语句如下：

select count(*) from table;
select count(1) from table;

然而当数据量过大的时候，这样的查问就可能会超时，所以咱们要换一种查问形式：

use 库名;
show table status like '表名' ; 
show table status like '表名'\G ;

上述办法不仅能够查问表的数据，还能够输出表的详细信息 , 加 \G 能够格式化输入。包含表名 存储引擎 版本 行数 每行的字节数等等，大家能够自行试一下哈

磁盘空间

查看指定数据库容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

查问单个库中所有表磁盘占用大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

查问出的后果如下：

倡议数据量占磁盘使用率的 70% 以内。同时，对于一些数据增长较快，能够思考应用大的慢盘进行数据归档（归档能够参考计划三）

实例容量

MySQL 是基于线程的服务模型，因而在一些并发较高的场景下，单实例并不能充分利用服务器的 CPU 资源，吞吐量反而会卡在 mysql 层，能够依据业务思考本人的实例模式

呈现问题的起因

下面咱们曾经查到咱们数据表的体量了 那么为什么单表数据量越大 业务的执行效率就越慢 根本原因是什么呢？

一个表的数据量达到好几千万或者上亿时，加索引的成果没那么显著啦。性能之所以会变差，是因为保护索引的 B+ 树结构层级变得更高了，查问一条数据时，须要经验的磁盘 IO 变多，因而查问性能变慢。

大家是否还记得，一个 B + 树大略能够寄存多少数据量呢？

InnoDB 存储引擎最小贮存单元是页，一页大小就是16k。

B+ 树叶子存的是数据，外部节点存的是键值 + 指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到须要的数据；

假如 B + 树的高度为 2 的话，即有一个根结点和若干个叶子结点。这棵 B + 树的寄存总记录数为 = 根结点指针数 * 单个叶子节点记录行数。

• 如果一行记录的数据大小为 1k，那么单个叶子节点能够存的记录数 =16k/1k =16.
• 非叶子节点内寄存多少指针呢？咱们假如主键 ID 为 bigint 类型，长度为 8 字节( 面试官问你 int 类型，一个 int 就是 32 位，4 字节)，而指针大小在 InnoDB 源码中设置为 6 字节，所以就是 8 +6=14 字节，16k/14B =16*1024B/14B = 1170

因而，一棵高度为 2 的 B + 树，能寄存 1170 * 16=18720 条这样的数据记录。同理一棵高度为 3 的 B + 树，能寄存1170 *1170 *16 =21902400，也就是说，能够寄存两千万左右的记录。B+ 树高度个别为 1 - 3 层，曾经满足千万级别的数据存储。

如果 B + 树想存储更多的数据，那树结构层级就会更高，查问一条数据时，须要经验的磁盘 IO 变多，因而查问性能变慢。

如何解决单表数据量太大，查问变慢的问题

晓得了根本原因之后，咱们就须要思考如何优化数据库来解决问题了

这里提供了三种解决方案，包含数据表分区，分库分表，冷热数据归档 理解完这些计划之后大家能够选取适宜本人业务的计划

计划一：数据表分区

为什么要分区：表分区能够在区间内查问对应的数据，升高查问范畴 并且索引分区 也能够进一步提高命中率，晋升查问效率

分区是指将一个表的数据依照条件散布到不同的文件下面，未分区前都是寄存在一个文件下面的，然而它还是指向的同一张表，只是把数据扩散到了不同文件而已。

咱们首先看一下分区有什么优缺点：

表分区有什么益处？

1. 与单个磁盘或文件系统分区相比，能够存储更多的数据。
2. 对于那些曾经失去保留意义的数据，通常能够通过删除与那些数据无关的分区，很容易地删除那些数据。相同地，在某些状况下，增加新数据的过程又能够通过为那些新数据专门减少一个新的分区，来很不便地实现。
3. 一些查问能够失去极大的优化，这次要是借助于满足一个给定 WHERE 语句的数据能够只保留在一个或多个分区内，这样在查找时就不必查找其余残余的分区。因为分区能够在创立了分区表后进行批改，所以在第一次配置分区计划时还未曾这么做时，能够从新组织数据，来进步那些罕用查问的效率。
4. 波及到例如 SUM()和 COUNT()这样聚合函数的查问，能够很容易地进行并行处理。这种查问的一个简略例子如“SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查问能够在每个分区上同时进行，最终后果只需通过总计所有分区失去的后果。
5. 通过跨多个磁盘来扩散数据查问，来取得更大的查问吞吐量。

表分区的限度因素

1. 一个表最多只能有 1024 个分区。
2. MySQL5.1 中，分区表达式必须是整数，或者返回整数的表达式。在 MySQL5.5 中提供了非整数表达式分区的反对。
3. 如果分区字段中有主键或者惟一索引的列，那么多有主键列和惟一索引列都必须蕴含进来。即：分区字段要么不蕴含主键或者索引列，要么蕴含全副主键和索引列。
4. 分区表中无奈应用外键束缚。
5. MySQL 的分区实用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

在进行分区之前能够用如下办法 看下数据库表是否反对分区哈

mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

计划二：数据库分表

为什么要分表：分表后，不言而喻，单表数据量升高，树的高度变低，查问经验的磁盘 io 变少，则能够提高效率

mysql 分表分为两种 程度分表和垂直分表

分库分表就是为了解决因为数据量过大而导致数据库性能升高的问题，将原来独立的数据库拆分成若干数据库组成，将数据大表拆分成若干数据表组成，使得繁多数据库、繁多数据表的数据质变小，从而达到晋升数据库性能的目标。

程度分表

定义：数据表行的拆分，艰深点就是把数据依照某些规定拆分成多张表或者多个库来寄存。分为库内分表和分库。比方一个表有 4000 万数据，查问很慢，能够分到四个表，每个表有 1000 万数据

垂直分表

定义：列的拆分，依据表之间的相关性进行拆分。常见的就是一个表把不罕用的字段和罕用的字段就行拆分，而后利用主键关联。或者一个数据库外面有订单表和用户表，数据量都很大，进行垂直拆分，用户库存用户表的数据，订单库存订单表的数据

毛病：垂直分隔的毛病比拟显著，数据不在一张表中，会减少 join 或 union 之类的操作

晓得了两个常识后，咱们来看一下分库分表的计划

1. 取模计划：

拆分之前，先预估一下数据量。比方用户表有 4000w 数据，当初要把这些数据分到 4 个表 user1 user2 uesr3 user4。比方 id = 17，17 对 4 取模为 1，加上，所以这条数据存到 user2 表。

留神：进行程度拆分后的表要去掉 auto_increment 自增长。这时候的 id 能够用一个 id 自增长长期表取得，或者应用 redis incr 的办法。

长处：数据平均的分到各个表中，呈现热点问题的概率很低。

毛病：当前的数据扩容迁徙比拟艰难难，当数据质变大之后，以前分到 4 个体现在要分到 8 个表，取模的值就变了，须要从新进行数据迁徙。

2.range 范畴计划

以范畴进行拆分数据，就是在某个范畴内的订单，寄存到某个表中。比方 id=12 寄存到 user1 表，id=1300 万的寄存到 user2 表。

长处：有利于未来对数据的扩容

毛病：如果热点数据都存在一个表中，则压力都在一个表中，其余表没有压力。

咱们看到以上两种计划 都存在毛病 然而却又是互补的，那么咱们将这两个计划联合会怎么呢？

3.hash 取模和 range 计划联合

如下图 咱们能够看到 group 组寄存 id 为 0~4000 万的数据，而后有三个数据库 DB0 DB1 DB2，DB0 外面有四个数据库，DB1 和 DB2 有三个数据库

如果 id 为 15000 而后对 10 取模（为啥对 10 取模 因为有 10 个表），取 0 而后 落在 DB_0, 而后在依据 range 范畴，落在 Table_0 外面。

总结：采纳 hash 取模和 range 计划联合 既能够防止热点数据的问题，也有利于未来对数据的扩容

咱们曾经理解了 mysql 分区和分表的常识 那咱们看一下这两个技术有何不同以及实用场景

分辨别表的区别：

1、实现形式上

• mysql 的分表是真正的分表，一张表分成很多表后，每一个小表都是残缺的一张表，都对应三个文件，一个.MYD 数据文件，.MYI 索引文件，.frm 表构造
• 分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，然而他存放数据的区块变多了。

2、进步性能上

• 分表重点是存取数据时，如何进步 mysql 并发能力上；
• 而分区呢，如何冲破磁盘的读写能力，从而达到进步 mysql 性能的目标。

3、实现的难易度上

1、分表的办法有很多，用 merge 来分表，是最简略的一种形式。这种形式根分区难易度差不多，并且对程序代码来说能够做到通明的。如果是用其余分表形式就比分区麻烦了。2、分区实现是比较简单的，建设分区表，根建平时的表没什么区别，并且对开代码端来说是通明的

分辨别表的分割

1、都能进步 mysql 的性高，在高并发状态下都有一个良好的体现。

2、分表和分区不矛盾，能够相互配合的，对于那些大访问量，并且表数据比拟多的表，咱们能够采取分表和分区联合的形式，访问量不大，然而表数据很多的表，咱们能够采取分区的形式等。

分库分表存在的问题

1、事务问题

在执行分库分表之后，因为数据存储到了不同的库上，数据库事务管理呈现了艰难。如果依赖数据库自身的分布式事务管理性能去执行事务，将付出昂扬的性能代价；如果由应用程序去帮助管制，造成程序逻辑上的事务，又会造成编程方面的累赘。

2、跨库跨表的 join 问题

在执行了分库分表之后，难以避免会将本来逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，咱们无奈 join 位于不同分库的表，也无奈 join 分表粒度不同的表，后果本来一次查问可能实现的业务，可能须要屡次查问能力实现。

3、额定的数据管理累赘和数据运算压力

额定的数据管理累赘，最不言而喻的就是数据的定位问题和数据的增删改查的反复执行问题，这些都能够通过应用程序解决，但必然引起额定的逻辑运算，例如，对于一个记录用户问题的用户数据表 userTable，业务要求查出问题最好的 100 位，在进行分表之前，只需一个 order by 语句就能够搞定，然而在进行分表之后，将须要 n 个 order by 语句，别离查出每一个分表的前 100 名用户数据，而后再对这些数据进行合并计算，能力得出后果。

计划三：冷热归档

为什么要冷热归档：其实起因和计划二相似，都是升高单表数据量，树的高度变低，查问经验的磁盘 io 变少，则能够提高效率 如果大家的业务数据，有显著的冷热辨别，比方：只须要展现近一周或一个月的数据。那么这种状况这一周喝一个月的数据咱们称之为热数据，其余数据为冷数据。那么咱们能够将冷数据归档在其余的库表中，进步咱们热数据的操作效率。

接下来讲一下归档的过程

1. 创立归档表 创立的归档表 原则上要与原表保持一致
2. 归档表数据的初始化

1. 业务增量数据处理过程

1. 数据的获取过程

以上三种计划咱们如何选型

大家能够依据本人的业务场景，去抉择适合本人业务的计划，我这边就给大家提供一下思路～

那么到了这里，我要讲的内容就差不多完结了，如果有什么不对的，或者有什么纳闷，欢送大家指导！

参考文章：https://blog.csdn.net/qq_36770474/article/details/119537186

近期热文举荐：

1.1,000+ 道 Java 面试题及答案整顿(2022 最新版)

2. 劲爆！Java 协程要来了。。。

3.Spring Boot 2.x 教程，太全了！

4. 别再写满屏的爆爆爆炸类了，试试装璜器模式，这才是优雅的形式！！

5.《Java 开发手册（嵩山版）》最新公布，速速下载！

感觉不错，别忘了顺手点赞 + 转发哦！