MySQL分区

1、为什么要分区

为了加快查询，但分区不一定就会加快查询，看情况而定

2、表分区的优缺点和限制

MySQL分区有优点也有一些缺点，如下：

优点

• 查询性能提升：分区可以将大表划分为更小的部分，查询时只需扫描特定的分区，而不是整个表，从而提高查询性能。特别是在处理大量数据或高并发负载时，分区可以显著减少查询的响应时间。
• 管理和维护的简化：使用分区可以更轻松地管理和维护数据。可以针对特定的分区执行维护操作，如备份、恢复、优化和数据清理，而不必处理整个表。这简化了维护任务并减少了操作的复杂性。
• 数据管理灵活性：通过分区，可以根据业务需求轻松地添加或删除分区，而无需影响整个表。这使得数据的增长和变化更具弹性，可以根据需求进行动态调整。
• 改善数据安全性和可用性：可以将不同分区的数据分布在不同的存储设备上，从而提高数据的安全性和可用性。例如，可以将热数据放在高速存储设备上，而将冷数据放在廉价存储设备上，以实现更高的性能和成本效益。

缺点

• 复杂性增加：分区引入了额外的复杂性，包括分区策略的选择、表结构的设计和维护、查询逻辑的调整等。正确地设置和管理分区需要一定的经验和专业知识。
• 索引效率下降：对于某些查询，特别是涉及跨分区的查询，可能会导致索引效率下降。由于查询需要在多个分区之间进行扫描，可能无法充分利用索引优势，从而影响查询性能。
• 存储空间需求增加：使用分区会导致一定程度的存储空间浪费。每个分区都需要占用一定的存储空间，包括分区元数据和一些额外的开销。因此，对于分区键的选择和分区粒度的设置需要权衡存储空间和性能之间的关系。
• 功能限制：在某些情况下，分区可能会限制某些MySQL的功能和特性的使用。例如，某些类型的索引可能无法在分区表上使用，或者某些DDL操作可能需要更复杂的处理。 在考虑使用分区时，需要综合考虑业务需求、查询模式、数据规模和硬件资源等因素，并权衡分区带来的优势和缺点。对于特定的应用和数据场景，分区可能是一个有效的解决方案，但并不适用于所有情况。

限制

• 在mysql5.6.7之前的版本，一个表最多有1024个分区；从5.6.7开始，一个表最多可以有8192个分区。
• 分区表无法使用外键约束。
• NULL值会使分区过滤无效。
• 所有分区必须使用相同的存储引擎。

3、分区操作

查看是否支持分区

show variables like '%have_partitioning%'

3.1、创建分区表

CREATE TABLE sales (
    id INT,
    sales_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sales_date)) (
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN (2021),
    PARTITION p3 VALUES LESS THAN (2022),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

3.2、向分区表添加新的分区

ALTER TABLE sales ADD PARTITION (
    PARTITION p5 VALUES LESS THAN (2023)
);

3.3、删除指定的分区

ALTER TABLE sales DROP PARTITION p3;

3.4、重新组织分区

ALTER TABLE sales REORGANIZE PARTITION p1, p2, p5 INTO (
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

3.5、合并相邻的分区

ALTER TABLE sales COALESCE PARTITION p1, p2;

3.6、分析指定分区的统计信息

ALTER TABLE sales ANALYZE PARTITION p1;

3.7、分区类型

RANGE分区：根据某一列的范围值将数据分布到不同的分区。

CREATE TABLE sales (
    id INT,
    sales_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sales_date)) (
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN (2021),
    PARTITION p3 VALUES LESS THAN (2022),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

LIST分区：根据某一列的离散值将数据分布到不同的分区。每个分区包含特定的列值列表。

CREATE TABLE users (
    id INT,
    username VARCHAR(50),
    region VARCHAR(50)
)
PARTITION BY LIST (region) (
    PARTITION p_east VALUES IN ('New York', 'Boston'),
    PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco'),
    PARTITION p_other VALUES IN (DEFAULT)
);

HASH分区：使用哈希算法将数据均匀地分布到多个分区中。

CREATE TABLE sensor_data (
    id INT,
    sensor_name VARCHAR(50),
    value INT
)
PARTITION BY HASH (id)
PARTITIONS 4;

KEY分区：根据某一列的哈希值将数据分布到不同的分区。不同于HASH分区，KEY分区使用的是列值的哈希值而不是哈希函数。

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE
)
PARTITION BY KEY (customer_id)
PARTITIONS 5;

COLUMNS 分区：MySQL在5.5版本引入了COLUMNS分区类型，其中包括RANGE COLUMNS分区和LIST COLUMNS分区。

CREATE TABLE sales (
    id INT,
    sales_date DATE,
    region VARCHAR(50),
    amount DECIMAL(10,2)
)
PARTITION BY RANGE COLUMNS(region, sales_date) (
    PARTITION p1 VALUES LESS THAN ('East', '2022-01-01'),
    PARTITION p2 VALUES LESS THAN ('West', '2022-01-01'),
    PARTITION p3 VALUES LESS THAN ('East', MAXVALUE),
    PARTITION p4 VALUES LESS THAN ('West', MAXVALUE)
);

参考：MySQL 主从复制与读写分离-阿里云开发者社区 (opens new window)