MySql--将大数据量的表进行拆分

发表于 2026-05-18 更新于 2026-05-19 分类于技术， mysql 置顶精品

阅读次数：本文字数： 3.1k 阅读时长 ≈ 11 分钟

摘要

MySql知识点介绍:将大数据量的表进行拆分
本文基于mysql-5.7.44

背景介绍

数据库中有一张大表，当前数据量超六千万条，占用空间超13G，每天新增2~3万条，大约每年新增一千万条记录
业务中日常查询主要集中在一年内的数据，偶尔需要查询一年前的数据
表结构

CREATE TABLE `ad_admob_network_by_day` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键，自增',
  `ad_date` date DEFAULT NULL COMMENT '统计日期',
  `app_value` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'app_id',
  `app_name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'app显示名称',
  `ad_unit_value` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '广告单元id',
  `ad_unit_name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '广告单元名称',
  `country_value` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '国家码',
  `estimated_earnings` int(20) DEFAULT NULL COMMENT '预估收入，microsValue',
  `impression_rpm` double(30,18) DEFAULT NULL COMMENT '千次展示预估收入，doubleValue',
  `ad_requests` int(10) DEFAULT NULL COMMENT '请求数，integerValue',
  `match_rate` double(22,18) DEFAULT NULL COMMENT '匹配率，doubleValue',
  `matched_requests` int(10) DEFAULT NULL COMMENT '匹配请求数，integerValue',
  `impressions` int(10) DEFAULT NULL COMMENT '展示次数，integerValue',
  `impression_ctr` double(22,18) DEFAULT NULL COMMENT '展示点击率，doubleValue',
  `clicks` int(10) DEFAULT NULL COMMENT '点击次数',
  `source_account` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '数据来源帐号,pub-xxxx',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_unique` (`ad_date`,`app_value`,`ad_unit_value`,`country_value`),
  KEY `index_app_id` (`app_value`),
  KEY `index_country` (`country_value`),
  KEY `index_account` (`source_account`)
) ENGINE=InnoDB AUTO_INCREMENT=62660570 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='admob数据统计-按天';

数据量日益增加，导致表空间和索引文件日益增大，影响查询效率

解决方案

方案1：水平拆分

计划对该表内的数据进行拆分存储，按年拆分到不同的表中
为了方便以后运行，数据拆分时可以编写存储过程实现，如下：

-- 按年拆分，按天迁移数据，新的表名称为 ad_admob_network_by_day_年份
CREATE DEFINER=`admin`@`%` PROCEDURE `archive_admob_by_year`(IN p_year INT)
BEGIN

    DECLARE v_start_date DATE;
    DECLARE v_end_date DATE;
    DECLARE v_table_name VARCHAR(100);
    DECLARE v_sql TEXT;

    -- 开始日期
    SET v_start_date = STR_TO_DATE(CONCAT(p_year, '-01-01'), '%Y-%m-%d');
		-- 下一年的第一天
    SET v_end_date = DATE_ADD(v_start_date, INTERVAL 1 YEAR);
    -- 年表名
    SET v_table_name = CONCAT('ad_admob_network_by_day_', p_year);

    -- 创建归档表
    SET v_sql = CONCAT(
        'CREATE TABLE IF NOT EXISTS ',
        v_table_name,
        ' LIKE ad_admob_network_by_day'
    );

    SET @sql = v_sql;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 按天迁移，小事务
    WHILE v_start_date < v_end_date DO

        SELECT CONCAT('ARCHIVE: ', v_start_date);

        SET v_sql = CONCAT(
            'INSERT IGNORE INTO ',
            v_table_name,
            ' SELECT * FROM ad_admob_network_by_day ',
            'WHERE ad_date = ''', v_start_date, ''''
        );

        SET @sql = v_sql;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 下一天
        SET v_start_date = DATE_ADD(v_start_date, INTERVAL 1 DAY);

    END WHILE;

END

调用方式：按年调用，将所有年份的数据迁移到新表中，包括本年的数据

# 每次运行时间大约 5 分钟
mysql> CALL archive_admob_by_year(2020);
mysql> CALL archive_admob_by_year(2021);
mysql> CALL archive_admob_by_year(2022);
…………………………
mysql> CALL archive_admob_by_year(2026);

调用后检查两边数据量是否一致

mysql> select count(*) from ad_admob_network_by_day t WHERE t.ad_date BETWEEN '2022-01-01' AND '2022-12-31';
+----------+
| count(*) |
+----------+
|  9977356 |
+----------+
1 row in set (6.32 sec)
mysql> select count(*) from ad_admob_network_by_day_2022 t WHERE t.ad_date BETWEEN '2022-01-01' AND '2022-12-31';

清空并删除原始表

mysql> TRUNCATE TABLE ad_admob_network_by_day;
Query OK, 0 rows affected (5.73 sec)

mysql> DROP TABLE ad_admob_network_by_day;
Query OK, 0 rows affected (0.40 sec)

将本年度的迁移表重命名为原始表

1 2	mysql> RENAME TABLE ad_admob_network_by_day_2026 TO ad_admob_network_by_day; Query OK, 0 rows affected (1.19 sec)

以后每年按上面的逻辑迁移一次
优缺点

优点	说明
易于维护	每年只需迁移当年数据，迁移范围小，操作简单
迁移效率高	单年数据量相对较小，迁移速度较快，不易造成锁表
减少影响	不影响历史归档分表，生产风险低
便于备份恢复	单年度表结构和数据清晰，易于独立备份和恢复
扩展灵活	可根据业务增长独立新增/扩展年度表
节省存储空间	可单独对历史分表压缩或归档，提升整体表空间利用率

缺点	说明
管理复杂度较高	多年度分表增多时，表数量增大，管理复杂
历史数据跨年分析麻烦	查询跨年度历史数据需 union 多张年度表，SQL复杂
存在数据一致性风险	迁移过程如有中断/异常，可能造成数据不一致
需要规划年度切换操作	每年需按时迁移数据和重命名新表，增加运维要求

方案2：对原始表进行分区重构

创建一个新表，按年进行分区

分区表简单理解就是将一张大表拆分为多张小表，每张小表是一个物理表，大表是逻辑表(MySql5.7)
这里创建新表而不是在原始表上直接构建分区，是因为当前表已经存在超六千万条记录，构建分区时会锁表，这个数据量预计会锁表30分钟
另外，分区表要求 PRIMARY KEY 和 UNIQUE KEY 中必须包含分区字段，本利中就是按年份分区，即分区字段是 ad_date

CREATE TABLE `ad_admob_network_by_day_partition` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键，自增',

  `ad_date` DATE NOT NULL COMMENT '统计日期',

  `app_value` VARCHAR(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'app_id',
  `app_name` VARCHAR(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'app显示名称',

  `ad_unit_value` VARCHAR(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '广告单元id',
  `ad_unit_name` VARCHAR(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '广告单元名称',

  `country_value` VARCHAR(10) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '国家码',

  `estimated_earnings` BIGINT DEFAULT NULL COMMENT '预估收入，microsValue',

  `impression_rpm` DOUBLE(30,18) DEFAULT NULL COMMENT '千次展示预估收入',

  `ad_requests` INT DEFAULT NULL COMMENT '请求数',

  `match_rate` DOUBLE(22,18) DEFAULT NULL COMMENT '匹配率',

  `matched_requests` INT DEFAULT NULL COMMENT '匹配请求数',

  `impressions` INT DEFAULT NULL COMMENT '展示次数',

  `impression_ctr` DOUBLE(22,18) DEFAULT NULL COMMENT '点击率',

  `clicks` INT DEFAULT NULL COMMENT '点击次数',

  `source_account` VARCHAR(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '数据来源帐号',

  -- =========================
  -- 主键（必须包含分区键）
  -- =========================
  PRIMARY KEY (`id`, `ad_date`),

  -- =========================
  -- 唯一约束（按你的业务维度）
  -- =========================
  UNIQUE KEY `uk_admob`
  (`ad_date`, `app_value`, `ad_unit_value`, `country_value`),

  -- =========================
  -- 业务索引（去掉冗余 index_date）
  -- =========================
  KEY `idx_app_value` (`app_value`),
  KEY `idx_country` (`country_value`),
  KEY `idx_source_account` (`source_account`)

) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_bin

-- =========================
-- 分区：按年
-- =========================
PARTITION BY RANGE (YEAR(ad_date)) (

  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),

  PARTITION pmax VALUES LESS THAN MAXVALUE
);

然后将原始表中的数据导入到上面创建的新表中，导入时可以参考上面方案1中的存储过程，按年迁移，按天导入的方式，过程类似，这里不再赘述
同样清空并删除原始表
重命名新表名称为原始表名称

1 2	mysql> RENAME TABLE ad_admob_network_by_day_partition TO ad_admob_network_by_day; Query OK, 0 rows affected (1.19 sec)

以后每年新增一个分区即可

ALTER TABLE ad_admob_network_by_day
REORGANIZE PARTITION pmax INTO (
  PARTITION p2026 VALUES LESS THAN (2027),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

创建分区表的好处如下：

1
2
3

❗ 不需要建新表
❗ 不需要迁移数据
❗ 不需要改代码结构

分区表删除分区数据极快，秒级删除

1	ALTER TABLE ad_admob_network_by_day DROP PARTITION p2025;

优缺点

优点	说明
删除分区数据速度极快，效率高	删除指定分区中的大量数据时只需元数据操作，几乎瞬间完成
不需要建新表，无需迁移原有数据	可以直接在原有表基础上分区，使用和维护方便
不需要改动现有代码结构	分区表与普通表使用相同，业务代码无需调整
未来每年仅需新增一个分区，维护简单	只需定期添加新分区即可，无需复杂操作
数据按年分区，管理更灵活	可针对不同年份单独管理、归档或清理
支持按分区归档和扩展，便于扩展容量	分区粒度支持扩容及归档，有利于大规模数据管理

缺点	说明
需手动维护分区，每年要新增/删除分区	运维时需定期手动添加或清理过期分区，自动化要求高
某些SQL特性（如外键关联）不支持分区表	分区表本身不支持外键等特性，设计受限
分区键选择不当会影响查询效率	错误的分区键可能造成跨分区查询，导致性能下降
分区数量过多可能影响数据库性能及元数据维护	分区太细粒度会影响DDL/DML效率和元数据开销
不是所有类型的查询都能利用分区优势	部分查询无法下推到分区级别，优化效果有限

适合采用分区表的典型场景

超大数据量按时间或范围型分布
例如按日期（年/月/日）、编号区间、地区ID等离散或有序字段进行分区。典型如：
- 日志、订单、账单、统计等大表，数据持续增量按天/月/年分布
- 业务分库分表不便时，用分区表高效管理历史数据
需要定期归档、清理历史数据
如业务要求定期删除早期（如一年前）数据，分区表可以通过DROP PARTITION一键快速清理，效率极高。
查询经常只关注某个区间/分区的数据
比如经常仅查询最近一年、最近一个月的数据，通过分区裁剪极大提升查询性能。
单表太大，超出数据量瓶颈
单表百万、千万、甚至上亿行，分区表可缓解单表大小带来的性能与管理压力。
不同数据周期有不同存储和管理需求
比如冷数据、热数据分区后，分布到不同磁盘或存储介质，方便归档和迁移。
运维操作需要高效的数据范围管理
如快速导入导出、统计、备份特定分区内的数据等，提升管理效率。

不适合分区的情况

小表、数据量较小的表，无需分区，分区反而带来运维复杂度
频繁跨分区联合查询，分区优势不明显甚至引入性能损耗
必须强依赖外键约束的业务（MySQL分区表不支持外键）

总之，当你遇到“大表、周期性、大区间清理/归档、基于字段分组查询”等需求时，可以优先考虑分区表方案。

后记

在MySQL 5.7中对一张大表进行首次创建分区的操作（例如执行ALTER TABLE … PARTITION BY …）会长时间锁住整张表，直到分区创建完成。
- 逻辑分区导致的全表锁：在5.7版本中，分区是“逻辑”的，InnoDB存储引擎层无法感知分区边界。执行ALTER TABLE时，Server层会向InnoDB层发送“锁定整表”的信号，导致在执行期间无法对表进行任何修改或查询。并且这个操作会重建整个表，持有MDL_EXCLUSIVE锁，完全阻塞业务写入。
- 分区操作期间，所有涉及该表的操作都将被阻塞，表现为“Waiting for table metadata lock”。
- 一旦分区建好，后续的增删查改等DML操作并非都会锁全表。利用“分区裁剪(Partition Pruning)”特性，多数操作可以只锁定需要访问的特定分区。

升级到MySQL 8.0后对表进行首次分区操作（ALTER TABLE … PARTITION BY …），并不是完全不锁表，但其影响会被降到最低，锁表时间将从分钟/小时级大幅缩短到秒/毫秒级。这背后的关键是MySQL 8.0的两项核心改进：分区级锁定和在线DDL（Online DDL）

对比项	MySQL 5.7	MySQL 8.0
锁粒度	逻辑分区，Server层与InnoDB层交互有限，对全表加MDL_EXCLUSIVE锁，无法分区级锁定	分区元数据集成InnoDB，锁管理可感知分区边界，支持分区级锁定与真正行级锁
首次建分区	复制全表数据并重建，锁表时间长	默认ALGORITHM=INPLACE, LOCK=NONE，支持并发读写，仅在切换瞬间锁表
增删分区	仅部分操作支持在线，部分情况仍需长时间锁表	仅元数据变更，锁表时间从分钟级降至毫秒级，无需重建表数据
重组/交换分区	长时间锁表	仍需短暂全表锁，作用范围仅限相关分区
总体影响	耗时操作需全表锁，业务阻塞明显	数据重建过程变为在线操作，仅元数据替换瞬间锁表，对业务影响极小