MYSQL分区索引

MYSQL中，数据量大的情况下，可以增加索引加快检索速度。但是数据量特别大的情况下，只能分库分表，但是在分库分表后数据量依然很大，已经做不了分表的情况下，索引依旧查询不动的情况下，可以使用分区来解决。
从MySQL5.1开始MySQL支持的绝大部分引擎都开启了表分区功能。

分区VS分库分表

image.png

基本概念

表分区是 MySQL 提供的一种物理数据组织技术，它将逻辑上的单个表拆分为多个物理存储单元，但对应用程序来说仍然是一个单一的表。分区后的表数据会按照指定的规则分布在不同的存储位置（如不同的文件或磁盘），但不需要修改 SQL 查询语句，数据库会自动定位数据所在的分区。

适用场景

1、按时间范围查询频繁；
2、历史数据归档需求，不需要的数据可以直接删除分区，不需要逐行删除数据；
3、单表数据超过 10GB 或 1 亿行，此时全表扫描耗时显著，分区可将数据分散到多个物理设备。

创建分区例子

1、按时间范围分区；

CREATE TABLE orders (
    id INT,
    order_no VARCHAR(50),
    end_time DATETIME NOT NULL,
    PRIMARY KEY (id, end_time)
)
PARTITION BY RANGE (to_days(`end_time`)) (
    PARTITION p20241220 VALUES LESS THAN (739605) ENGINE = InnoDB,
    PARTITION p20241227 VALUES LESS THAN (739612) ENGINE = InnoDB
);

2、按照地域分区；

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    region_id TINYINT NOT NULL,
    PRIMARY KEY (id, region_id)   
)
PARTITION BY LIST (region_id) (
    PARTITION p_north VALUES IN (1, 2, 3),   
    PARTITION p_south VALUES IN (4, 5, 6)
);

3、按照HASH 分区（按哈希值）；

CREATE TABLE logs (
    id INT,
    user_id INT NOT NULL,
    action VARCHAR(50),
    PRIMARY KEY (id, user_id)  
)
PARTITION BY HASH (user_id)
PARTITIONS 8;  -- 自动创建 p0~p7 共 8 个分区

注意事项

1、分区键必须包含在主键中（MySQL 强制要求）；
2、RANGE/LIST 分区中，NULL 值会被分到第一个分区（VALUES LESS THAN 最小的分区）；