Logo

菜单

liuxiaoshui
发布于 2024-01-17 / 11 阅读
0
0

mysql大表处理方案

#分库分表

场景

当数据库表中的数据越来越多，数据库操作越来越慢

首先，可以考虑对数据进行分区，以便更快速地进行查询。此外，还可以对查询和插入操作进行优化，以减少查询和插入时长。

其次，对于后续业务需求的扩展，可以通过使用设计模式来避免表中新增字段对系统的影响。例如，可以使用装饰器模式来动态添加功能，而不是直接在表中添加字段。装饰器模式demo见结尾。

最后，对于表中的无效数据，可以考虑使用数据清理工具来清理数据，以便只查询时间区间内的有效数据。这可以提高查询效率，并减少数据存储的空间占用。

评估表的数据量

可以从表容量/磁盘空间/实例容量三方面评估数据体量

表容量

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

查询行数据的方式：一般查询表数据有多少数据时用到的经典sql语句如下：

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

但是当数据量过大的时候，这样的查询就可能会超时,卡死，所以要换一种查询方式

use database_name;
show table status like '%table_a%';
show table status like '%table_a%' \G;  #mysql客户端命令行里使用

上述方法不仅可以查询表的数据，还可以输出表的详细信息 , 加 \G可以格式化输出。包括表名 存储引擎 版本 行数 每行的字节数等等

show table status统计信息可以刷新不及时，可以手动执行来触发刷新

-- 手动执行 ANALYZE TABLE your_table_name 来强制更新统计信息。
ANALYZE TABLE sche_gps_info;

磁盘空间

查看指定数据库容量大小

-- 磁盘空间
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 = 'traveldb' AND table_name = 'sche_gps_info'; 
ORDER BY data_length desc ,index_length desc;

建议数据量占磁盘使用率的70%以内。在一个存储系统中，数据库或数据占用的磁盘空间应该控制在总磁盘容量的70%以内。这是一种一般性的经验法则，旨在确保系统有足够的空间用于应对未来的增长、处理临时的数据波动，以及保障磁盘的正常运作。

同时，对于一些数据增长较快，可以考虑使用大的慢盘进行数据归档（归档可以参考方案三）

实例容量

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

出现性能问题的原因

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

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

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

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

image-20240117115737720.png

查看mysql文件页大小（16K）

SHOW GLOBAL STATUS like 'Innodb_page_size’;
为什么mysql页文件默认16K？
假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)
那么一颗高度为2的B+树能存储的数据为：1170*16=18720条，一颗高度为3的B+树能存储的数据为：1170*1170*16=21902400（千万级条）
​
Mysql不选B树？
1，因为B树非叶子节点页存储数据，16的多少次方等于2000w，树的高度比B+树大很多。2，B+树叶子节点有双向指针

因此，B+树高度一般为1-3层，已经满足千万级别的数据存储。

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

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

知道了根本原因之后，就需要考虑如何优化数据库来解决问题了

这里提供了三种解决方案，包括数据表分区，分库分表，冷热数据归档 了解完这些方案之后大家可以选取适合自己业务的方案

方案一：数据表分区

为什么要分区：表分区可以在区间内查询对应的数据，降低查询范围 并且索引分区 也可以进一步提高命中率，提升查询效率。分区是指将一个表的数据按照条件分布到不同的文件上面（未分区前都是存放在一个文件上面的），但是它还是指向的同一张表，只是把数据分散到了不同文件而已。

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

表分区有什么好处？

与单个磁盘或文件系统分区相比，可以存储更多的数据。

对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。

一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。

通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

表分区的限制因素

一个表最多只能有1024个分区。

MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

分区表中无法使用外键约束。

MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

在进行分区之前可以用如下方法 看下数据库表是否支持分区

mysql可以通过下面语句判断是否支持分区：
​
SHOW VARIABLES LIKE '%partition%';
​
如果输出：
​
have_partitioning   YES
​
表示支持分区。
​
或者通过：
​
SHOW PLUGINS;
显示所有插件，如果有partition ACTIVE STORAGE ENGINE GPL 插件则表明支持分区

mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

分区的二种方式

1，横向分区

什么是横向分区呢？就是横着来分区了，举例来说明一下，假如有100W条数据，分成十份，前10W条数据放到第一个分区，第二个10W条数据放到第二个分区，依此类推。也就是把表分成了十分，根用merge来分表，有点像哦。取出一条数据的时候，这条数据包含了表结构中的所有字段，也就是说横向分区，并没有改变表的结构。

2，纵向分区

什么是纵向分区呢？就是竖来分区了，举例来说明，在设计用户表的时候，开始的时候没有考虑好，而把个人的所有信息都放到了一张表里面去，这样这个表里面就会有比较大的字段，如个人简介，而这些简介呢，也许不会有好多人去看，所以等到有人要看的时候，在去查找，分表的时候，可以把这样的大字段，分开来。

方案二：数据库分表

为什么要分表：分表后，显而易见，单表数据量降低，树的高度变低，查询经历的磁盘io变少，则可以提高效率 mysql 分表分为两种 水平分表和垂直分表

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

水平分表

定义：数据表行的拆分，通俗点就是把数据按照某些规则拆分成多张表或者多个库来存放。分为库内分表和分库。

比如一个表有4000万数据，查询很慢，可以分到四个表，每个表有1000万数据

image-20240117130649912.png

垂直分表

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

image-20240117131351577.png

缺点：垂直分隔的缺点比较明显，数据不在一张表中，会增加join 或 union之类的操作

知道了两个知识后，我们来看一下分库分表的方案

1.取模方案：

拆分之前，先预估一下数据量。比如用户表有4000w数据，现在要把这些数据分到4个表user1 user2 uesr3 user4。

比如id = 1，1对4取模为1，加上 ，所以这条数据存到t_order_1表。

注意：进行水平拆分后的表要去掉auto_increment自增长。这时候的id可以用一个id 自增长临时表获得，或者使用 redis incr的方法或者分布式id方案。

image-20240117133218532.png

优点：数据均匀的分到各个表中，出现热点问题的概率很低。

缺点：以后的数据扩容迁移比较困难难，当数据量变大之后，以前分到4个表现在要分到8个表，取模的值就变了，需要重新进行数据迁移。

2.range 范围方案

以范围进行拆分数据，就是在某个范围内的订单，存放到某个表中。比如`id=1存放到t_order_1表，id=1200w的存放到t_order_2 表。

image-20240117133615590.png

优点：有利于将来对数据的扩容

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

❝

我们看到以上两种方案 都存在缺点 但是却又是互补的，那么我们将这两个方案结合会怎样呢？

❞

3.hash取模和range方案结合

如下图 我们可以看到 group 组存放id 为0~4000万的数据，然后有三个数据库 DB0 DB1 DB2，DB0里面有四个数据库，DB1 和DB2 有三个数据库

假如id为15000 然后对10取模（为啥对10 取模 因为有10个表），取0 然后 落在DB_0,然后在根据range 范围，落在Table_0 里面。

image-20240117133835181.png

总结：采用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. 归档表数据的初始化

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

  4. 数据的获取过程

以上三种方案我们如何选型

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

首先，我们可以考虑对数据进行分区，以便更快速地进行查询。此外，我们还可以对查询和插入操作进行优化，以减少查询和插入时长。

其次，对于后续业务需求的扩展，我们可以通过使用设计模式来避免表中新增字段对系统的影响。例如，我们可以使用装饰器模式来动态添加功能，而不是直接在表中添加字段。

最后，对于表中的无效数据，我们可以考虑使用数据清理工具来清理数据，以便只查询时间区间内的有效数据。这可以提高查询效率，并减少数据存储的空间占用。

综上所述，通过以上措施，我们可以更好地处理业务数据库表中数据量增加的问题，并确保系统的高效性和可扩展性。

补充

为什么mysql很少采用分区方案

虽然 MySQL 支持表分区，但在实际应用中，并不是所有场景都适合使用分区方案。以下是一些可能解释为什么在某些情况下很少使用分区的原因：

  1. 应用场景不适合： 分区通常在大型数据集或者需要高并发查询的场景下更为有用。对于小型数据集或者轻量级应用，引入分区可能并不划算，而且维护起来可能会增加复杂性。

  2. 查询优化不一定明显： 分区表的性能优势在某些特定的查询和数据维护操作上可能非常明显，但并不是所有类型的查询都会受益于分区。在一些场景下，MySQL 的优化器已经足够智能，能够对非分区表执行的查询进行有效优化。

  3. 维护和管理成本： 引入分区后，表的维护和管理可能变得更加复杂。例如，需要考虑分区键的选择、定期分区维护、备份和恢复等问题。在一些情况下，这增加的管理成本可能超过了性能收益。

  4. 不同存储引擎支持不一致： 并非所有的存储引擎都对分区提供相同的支持。例如，InnoDB 支持分区，但某些其他存储引擎可能并不支持。这可能会限制了在某些场景下使用分区的选择。

  5. 应用层处理： 在某些情况下，应用层更容易处理数据的分割和查询优化，而不是依赖于数据库层面的分区。

总体而言，是否选择使用分区应该根据具体的应用需求、数据量、查询模式以及性能和维护成本等多方面因素来进行综合考虑。在一些特定的场景下，使用分区可以带来显著的性能提升，但并不是在所有情况下都是必要或者最佳选择。

装饰器模式demo

装饰器模式通常用于动态地给一个对象添加一些额外的职责，而不需要修改其代码。在数据库设计中，这可以用于处理后续业务需求的扩展，而不是直接在表中添加字段。在下面的示例中，我们将使用一个简化的场景说明装饰器模式的概念。

假设我们有一个表示用户的表 user_table，最初只包含基本信息：

sqlCopy codeCREATE TABLE user_table (
    user_id INT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255)
);

现在，我们希望在不直接修改 user_table 的情况下，为用户添加一些额外的功能，比如记录用户的登录次数。我们可以使用装饰器模式来实现这个目标。

首先，我们可以创建一个装饰器表 user_login_decorator，用于记录用户的登录次数：

sqlCopy codeCREATE TABLE user_login_decorator (
    user_id INT PRIMARY KEY,
    login_count INT,
    FOREIGN KEY (user_id) REFERENCES user_table(user_id)
);

然后，我们创建一个视图 user_with_login_info，将 user_table 和 user_login_decorator 结合起来：

sqlCopy codeCREATE VIEW user_with_login_info AS
SELECT
    u.user_id,
    u.username,
    u.email,
    d.login_count
FROM
    user_table u
LEFT JOIN
    user_login_decorator d ON u.user_id = d.user_id;

现在，我们可以通过查询 user_with_login_info 视图来获取包含用户登录次数的信息，而不需要直接修改 user_table：

sqlCopy code
SELECT * FROM user_with_login_info;

在这个例子中，user_with_login_info 视图充当了一个装饰器，将用户表的基本信息与登录次数信息结合起来。当我们有其他需要添加的功能时，可以创建类似的装饰器表和视图，从而实现系统的动态扩展，而不必直接修改原始表的结构。这种方法使得我们可以更灵活地应对后续业务需求的变化。请注意，实际使用时需要根据具体情况进行设计和调整。


linux命令
验证相同生产者组内，生产者实例挂掉...

评论