MySQL大数据表处理策略全解析_powerbi mysql数据量过大-CSDN博客

本文链接：https://blog.csdn.net/qq_36936192/article/details/145012379

在业务不断发展的过程中，我们常常会面临业务数据库表中数据日益增多的困扰，随之而来的便是插入、查询时长变长，后续业务扩展困难等问题。

一、评估表数据体量

表容量
- 对于表容量，需要综合考量表的记录数、平均长度、增长量、读写量以及总大小。一般而言，面向联机事务处理（OLTP）的表，建议单表数据量不要超过 2000 万行，总大小控制在 15G 以内，且单表读写量每秒在 1600 次以内。
- 当查询表数据量时，传统的 select count(*) from table 或 select count(1) from table 在数据量过大时容易超时。此时，可以改用 use 库名; show table status like '表名'; 或 show table status like '表名'\G;，不仅能查询数据量，还能获取表的详细信息，如存储引擎、版本、行数、每行字节数等。
磁盘空间
- 通过以下 SQL 语句可查看指定数据库容量大小：

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;

查询单个库中所有表磁盘占用大小，只需在 where 子句中指定库名：

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 + 树结构层级增多，查询一条数据时，磁盘 I/O 操作变多。以 InnoDB 存储引擎为例，其最小存储单元是页，一页大小为 16k。B + 树叶子存数据，内部节点存键值 + 指针。假设一行记录数据大小为 1k，主键 ID 为 bigint 类型（8 字节），指针大小在 InnoDB 源码中为 6 字节，那么高度为 2 的 B + 树能存放约 18720 条记录，高度为 3 的 B + 树能存放约两千万条记录。B + 树一般 1 - 3 层，可满足千万级数据存储，但层级越高，查询性能越差。

三、解决单表数据量太大、查询变慢的方案

方案一：数据表分区
- 为何分区：表分区能在指定区间查询数据，缩小查询范围，索引分区还可提高命中率，提升查询效率。它将表数据按条件分布到不同文件，实则仍指向同一张表，只是分散存储。
- 优点：
  - 可比单个磁盘或文件系统分区存储更多数据。
  - 便于删除无意义数据（通过删除相关分区），也易添加新数据（新增分区）。
  - 一些查询借助分区优化，只需查找特定分区，提升效率。
  - 涉及聚合函数的查询，如 SUM()、COUNT()，可并行处理，最后汇总结果。
  - 能跨多个磁盘分散数据查询，增大吞吐量。
- 限制因素：
  - 一个表最多 1024 个分区。
  - MySQL 5.1 中，分区表达式须为整数或返回整数的表达式，5.5 版起支持非整数表达式分区。
  - 若分区字段含主键或唯一索引列，须包含全部主键和索引列。
  - 分区表不能用外键约束，且表数据和索引必须同时分区。
- 分区前，可用 show variables like '%partition%'; 查看数据库表是否支持分区。
方案二：数据库分表
- 为何分表：分表可降低单表数据量，使 B + 树高度降低，减少查询磁盘 I/O，提高效率。分表分为水平分表和垂直分表。
- 水平分表：按行拆分数据，通俗讲就是依规则将数据分到多张表或多个库。例如，4000 万数据的表查询慢，可分到四个表，各存 1000 万数据。但要注意，水平拆分后的表需去掉 auto_increment 自增长，可通过临时表或 Redis incr 方法获取 ID。
- 垂直分表：依据列的相关性拆分，常见将不常用字段与常用字段分开，利用主键关联，或拆分数据库中的订单表和用户表。不过，垂直分表会增加 join 或 union 操作。
- 分库分表方案：
  - 取模方案：预估数据量后拆分，如 4000w 用户表数据分到 4 个表 user1、user2、uesr3、user4，数据 id = 17 对 4 取模为 1，存到 user2 表。优点是数据均匀分布，热点问题少；缺点是数据扩容迁移困难。
  - range 范围方案：按范围拆分数据，如 id = 12 存 user1 表，id = 1300 万 存 user2 表。优点是利于扩容；缺点是若热点数据集中，压力全在一张表。
  - hash 取模和 range 方案结合：综合两者优势，先对数据总量取模分配到不同数据库组，再依据 range 范围将数据存到具体表，既能避免热点数据，又便于扩容。
- 分区分表的区别与联系：
  - 区别：
    - 实现方式上，分表是真正将一张表分成多个完整小表，各有数据、索引、表结构文件；分区则是一张大表分区后仍为一张表，只是存储区块增多。
    - 性能提升方面，分表侧重提高并发存取能力，分区着重突破磁盘读写瓶颈。
    - 实现难易度，简单分表方式（如 merge）与分区难易相近且对代码透明，复杂分表方式则较麻烦。
  - 联系：都能提升 MySQL 性能，高并发下表现良好，可相互配合，如大访问量且表数据多，可分表和分区结合；访问量不大但表数据多，可仅分区。
- 分库分表存在的问题：
  - 事务问题：数据分布在不同库，依赖数据库分布式事务管理性能代价高，由应用程序控制又增加编程负担。
  - 跨库跨表的 join 问题：逻辑关联性强的数据拆分后，表关联操作受限，无法直接 join 不同分库、分表粒度的表，业务查询可能需多次操作。
  - 额外的数据管理负担和数据运算压力：如查询成绩最好的 100 位用户，分表前一个 order by 语句搞定，分表后需多个 order by 分别查各分表，再合并计算。
方案三：冷热归档
- 为何归档：和分表类似，归档可降低单表数据量，减少磁盘 I/O，提高热数据操作效率。若业务数据有明显冷热区分，如近一周或一月数据为热数据，其余为冷数据，可将冷数据归档到其他库表。
- 归档过程：
  - 创建归档表，原则上与原表结构一致。
  - 初始化归档表数据。
  - 处理业务增量数据，包括获取、迁移等过程。

四、三种方案的选型建议

数据表分区：适用于数据量较大、查询场景限定在某个区且无联合查询的情况。优点是基本不影响原有业务逻辑 SQL；缺点是分表后业务有一定开销，分区数据聚合耗时，且不适用于千万级以上数据量。
数据表分表：适合数据量较大、冷热区不明显且数据能按区间完整划分的场景。优势在于能提高查询、插入效率；不足是若大数据表增多，数据库表数量飙升，区间划分固定，后续单表数据量增大仍会影响性能，实现复杂度较高。
冷热归档：在数据量较大、冷热分区明显且冷数据使用频率极低时首选。好处是数据迁移对业务影响小、开发量少、成本低；缺点是需确认分表规则。

总之，面对 MySQL 大数据表问题，要综合评估数据体量，深入了解各方案优劣，结合业务实际需求，选取最合适的优化策略，让数据库高效稳定运行。

欢迎关注公众号《小周的数据库进阶之路》，更多精彩知识和干货尽在其中。