今天与大家分享一个在项目中实际使用的10亿级单表数据，做到如何分区，如何优化，提高检索，快速访问的实际案例。这么大量的单表数据，一般是不推荐的。但有时业务是没有办法，或者说实际上是可以的（比如，就这个案例，它数据量虽然大，但存储的字段不多，且都是一些日期和数值类型的数据）。这个表是个金融类实时交易订单数据表，在每交易日交易时段，炒股的人，都知道，整个市场交易订单每时每刻都是大量的。这里，这个表假设一下核心字段，一个是当前交易日，一个是订单时间，一个是成交量，其它字段就不列了，反正需要的数字，都会有，但不会太多字段。这个表如果在某个市场，暂且每天记录的量假如是800百万，那一年下来8,000,000*200（交易日）=1,600,000,000 (约16亿)，那这是一个巨量的数据表。假如表：
-- 创建表
CREATE TABLE example_table (
id INT NOT NULL,
current_date DATE NOT NULL,
user_name VARCHAR(50),
amount DECIMAL(10,2),
PRIMARY KEY (id, current_date)
)
此时，我们在服务器各个方面保持不变的情况下，对这个表大量读写，就要做好优化了。我们想到的是，那得分区处理。依据分区的方式，mysql是有range和hash二种方式。第一种是以日期作为范围，通常按 current_date 进行范围分区，例如按年份或月份，进行分区：
-- 1. 先移除现有分区（转为普通表）
ALTER TABLE example_table REMOVE PARTITIONING;
-- 2. 再添加新的分区
ALTER TABLE example_table
PARTITION BY RANGE (YEAR(current_date) * 100 + MONTH(current_date)) (
PARTITION p202201 VALUES LESS THAN (202202),
PARTITION p202202 VALUES LESS THAN (202203),
PARTITION p202203 VALUES LESS THAN (202204),
PARTITION p202204 VALUES LESS THAN (202205),
PARTITION p202205 VALUES LESS THAN (202206),
PARTITION p202206 VALUES LESS THAN (202207),
PARTITION p202207 VALUES LESS THAN (202208),
PARTITION p202208 VALUES LESS THAN (202209),
PARTITION p202209 VALUES LESS THAN (202210),
PARTITION p202210 VALUES LESS THAN (202211),
PARTITION p202211 VALUES LESS THAN (202212),
PARTITION p202212 VALUES LESS THAN (202301),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
我们可以在 p_future 之前添加新月份分区
-- 例如添加 2023年1月 的分区
ALTER TABLE example_table REORGANIZE PARTITION p_future INTO (
PARTITION p202301 VALUES LESS THAN (202302),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
我们也可以删除分区，（但这个要小心，数据也会清掉的）如：
-- 删除 2022年1月 的分区（数据也会被删除）
ALTER TABLE example_table DROP PARTITION p202201;
我们也可以合并分区，如：
-- 合并 2022年1-3月 为一个季度分区
ALTER TABLE example_table REORGANIZE PARTITION p202201, p202202, p202203 INTO (
PARTITION p2022_q1 VALUES LESS THAN (202204)
);
-- 反向:将季度分区拆分为月度分区
ALTER TABLE example_table REORGANIZE PARTITION p2022_q1 INTO (
PARTITION p202201 VALUES LESS THAN (202202),
PARTITION p202202 VALUES LESS THAN (202203),
PARTITION p202203 VALUES LESS THAN (202204)
);
我们也可以查看分区
SELECT
PARTITION_NAME,
PARTITION_DESCRIPTION,
TABLE_ROWS,
DATA_LENGTH,
CREATE_TIME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'example_table'
ORDER BY PARTITION_ORDINAL_POSITION;
另外一种是HASH分区：
-- 按表数据最小日期时间（指定）：使用取模确保在0-49范围内
ALTERTABLE example_table PARTITIONBYHASH(((YEAR(current_date)-2022)*12+MONTH(current_date)-5)%50)
PARTITIONS 50;
以上是分区的二个方式介绍，注意，分区的字段值必须是唯一。如果在现有的大表中进行分区，则不能直接使用脚本去构建，可能需要新建一个表（结构带分区的）先建好，再把数据同步过去，自动实现分区，要不然，在现有的表上进行直接分区，会锁表，以及大量IO操作，服务器有可能会自动重启。
以上就是大表的分区常用方法举例，关注深兴旺,一起学习，一起进步!
