MySQL 表格分区什么情况下，考虑 表格分区？

项目中，经常会遇到数据量比较大的表格（百万、千万、亿），这个时候就要根据业务，考虑表格分区的设计思维了。 通过将表格的行分成多个逻辑分区，可以更快地执行涉及这些分区的查询，并降低对大量数据的访问需求。

MySQL 分区步骤

MySQL 表格分区是一种将一个表格分成多个较小的、独立的区域，以提高查询性能和管理方便性的技术。下面是操作 MySQL 表格分区的一般步骤：

确保你的 MySQL 版本支持表格分区。MySQL 5.6 及以上版本都支持表格分区。创建表格时指定分区。在 CREATE TABLE 语句中，使用 PARTITION BY 子句指定分区方法。常见的分区方法包括： RANGE：根据列的值的范围进行分区。 LIST：根据列的值的列表进行分区。 HASH：根据列的哈希值进行分区。 KEY：根据列的索引进行分区。

设计表格时，考虑好分区一般为：RANGE + LIST 、 HASH + KEY 例如，创建一个按年份分区的顾客表格：

-- 第一种：RANGE分区 根据年份将数据进行分区处理。CREATE TABLE customers (   id INT,   name VARCHAR(50),   year INT,   partitioned_column VARCHAR(50)) PARTITION BY RANGE (year) (    PARTITION p0 VALUES LESS THAN (2000),    PARTITION p1 VALUES LESS THAN (2005),    PARTITION p2 VALUES LESS THAN (2010),    PARTITION p3 VALUES LESS THAN MAXVALUE);-- 第二种：HASH分区 根据月份将数据进行分区处理-- 此处，是划分了12个分区。CREATE TABLE customers (   id INT,   name VARCHAR(50),   crt_date date,   partitioned_column VARCHAR(50)) PARTITION BY HASH(MONTH(crt_date)) PARTITIONS 12;-- 第三种：LIST分区 根据顾客状态进行分区处理CREATE TABLE customers (   id INT,   name VARCHAR(50),   status char(1),   crt_date date,   partitioned_column VARCHAR(50)) PARTITION BY list(status)PARTITIONS 12;-- 第四种：KEY分区 根据相同KEY写入同一个分区CREATE TABLE customers (   id INT,   name VARCHAR(50),   status char(1),   crt_date date,   partitioned_column VARCHAR(50)) PARTITION BY key(id)PARTITIONS 12-- 通过下面的命令，可以将原来的12个分区改为6个分区ALTER TABLE customers COALESCE PARTITION 6；

插入数据时，MySQL 会自动将数据分配到相应的分区。你可以像插入普通表格一样插入数据。例如：

INSERT INTO customers (id, name, year, partitioned_column) VALUES (1, 'John Doe', 2008, 'some value');

查询数据时，MySQL 会自动从相应的分区中获取数据。你可以像查询普通表格一样查询数据。例如：

SELECT * FROM customers WHERE year = 2008;

调整分区。你可以根据需要添加、删除或合并分区。例如，为顾客表格添加一个新的分区：

ALTER TABLE customers ADD PARTITION (PARTITION p4 VALUES LESS THAN (2015));

注意监控和维护分区。

使用SHOW TABLE STATUS 命令：查看表格的状态和分区信息。

# 查看所有表格的状态和分区信息show table status;

使用** OPTIMIZE TABLE命令：是优化表格的命令。也可以优化和修复分区。 它的作用主要是重建表索引，并压缩表碎片，使得数据物理排列更加紧密，从而加快查询速度。此外，它还可以更新统计信息和缓存，从而减少数据读取次数和IO操作，大大提高数据库效率。 场景：优化删除大量数据后的表，或者对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行很多更改后的优化。一般针对特定的表格，进行周期运行。

optimize table customers;

使用 ALTER TABLE ... REORGANIZE PARTITION命令：可以合并相邻的分区。

-- 分解分区  将p0拆分为s0和s1ALTER TABLE customers REORGANIZE PARTITION p0 INTO (    PARTITION s0 VALUES LESS THAN (3),    PARTITION s1 VALUES LESS THAN (5));-- 合并分区  将s0和s1合并为p0ALTER TABLE customers REORGANIZE PARTITION s0,s1 INTO (    PARTITION p0 VALUES LESS THAN (5));-- 合并分区  将s0和s1合并为 r0 r1ALTER TABLE customers REORGANIZE PARTITION s0,s1 INTO (    PARTITION s0 VALUES LESS THAN (2),    PARTITION p0 VALUES LESS THAN (5));

使用 ALTER TABLE ... ADD PARTITION命令：可以添加新的分区。

alter table customers add partition (partition p5 VALUES LESS THAN (2020))

使用 ALTER TABLE ... COALESCE PARTITION命令：修改分区数量。

-- 通过下面的命令，将原来的12个分区改为6个分区ALTER TABLE customers COALESCE PARTITION 6；

什么场景下，具体使用什么分区？Key分区：这种分区方式主要用于将具有相同Key的消息写入同一个分区，以保证消息的顺序性。例如，在订单系统中，每个订单都有一个唯一的订单号作为Key，那么具有相同订单号的消息将被写入同一个分区，保证了订单的顺序。Hash分区：Hash分区主要用于数据结构中，主要是为了提高查询效率。在密码学中，hash算法的作用主要是用于消息摘要和签名，对整个消息的完整性进行校验。List分区：List分区特别适合于枚举值列的分区，例如根据性别分区。这种分区方式能够快速定位到特定的数据分区，从而提高查询效率。Range分区：Range分区最适合的两种场景是 1.当我们需要删除过期或某些一类数据时，可以通过ALTER TABLE命令直接删除特定分区的数；2.当我们查询数据时，通过合理设计可以减少全表扫描，从而提高查询效率。例如，SELECT * from t_user_main where f_id > 12可以直接扫描p2区扫描，查询数据。RANGE COLUMNS 用法

RANGE COLUMNS是RANGE分区的一种特殊类型，它与RANGE分区的区别如下：

RANGE COLUMNS不接受表达式，只能是列名。而RANGE分区则要求分区的对象是整数。RANGE COLUMNS允许多个列，在底层实现上，它比较的是元祖（多个列值组成的列表），而RANGE比较的是标量，即数值的大小。RANGE COLUMNS不限于整数对象，date，datetime，string都可作为分区列。

CREATE TABLE rcx (    a INT,    b INT,    c CHAR(3),    d INT)PARTITION BY RANGE COLUMNS(a,d,c) (    PARTITION p0 VALUES LESS THAN (5,10,'ggg'),    PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),    PARTITION p2 VALUES LESS THAN (15,30,'sss'),    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE));

作者：喵不拉几

链接：