Mysql分区

最新推荐文章于 2025-11-06 08:46:27 发布

原创最新推荐文章于 2025-11-06 08:46:27 发布 · 693 阅读

14 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #android #数据库

MySQL 分区功能全面介绍

1. 什么是分区（Partitioning）？

分区是 MySQL 用来对大表或大索引做的水平拆分技术。
它把一张大表的数据根据某种规则，拆分到多个逻辑分区（partition）中，每个分区独立存储和管理。

从逻辑上看是一个表，物理上数据分散存储，提高管理和查询效率。

2. 为什么要用分区？

提升查询效率
通过分区裁剪（Partition Pruning），查询时只扫描相关分区，避免全表扫描。
提高并发能力
多个分区可同时读写，降低锁竞争。
方便运维管理
可按分区进行备份、恢复、归档、删除老数据。
避免单表过大导致的性能瓶颈
拆分数据存储，降低索引和缓存压力。

3. MySQL 支持的分区类型

分区类型	说明	典型使用场景
RANGE	按字段值范围划分	时间区间分区，手机号号段分区
LIST	按字段取值列表划分	按地区编码、分类标识分区
HASH	按表达式哈希值划分，分区数固定	均匀分布数据，适合无规则数据分布
KEY	类似 HASH，由 MySQL 自动选字段哈希	简化 HASH 分区，用主键或唯一键做分区

# MySQL 各分区类型示例建表代码

---

## 1. RANGE 分区（按范围分区）

```sql
CREATE TABLE phone_range_partition (
    id BIGINT NOT NULL,
    phone VARCHAR(11) NOT NULL,
    phone_prefix INT NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY(id, phone, phone_prefix)
)
PARTITION BY RANGE (phone_prefix) (
    PARTITION p130 VALUES LESS THAN (131),
    PARTITION p131 VALUES LESS THAN (132),
    PARTITION p132 VALUES LESS THAN (133),
    PARTITION p_other VALUES LESS THAN MAXVALUE
);
```

---

## 2. LIST 分区（按值列表分区）

```sql
CREATE TABLE phone_list_partition (
    id BIGINT NOT NULL,
    phone VARCHAR(11) NOT NULL,
    province_code INT NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY(id, phone, province_code)
)
PARTITION BY LIST(province_code) (
    PARTITION p_bj VALUES IN (10),
    PARTITION p_sh VALUES IN (21),
    PARTITION p_sz VALUES IN (755),
    PARTITION p_other VALUES IN (0, 999)
);
```
给某个字段（如 province_code）指定几个具体值（比如 10、21、755 等）
这些指定的值对应一个分区

---

## 3. HASH 分区（按哈希值分区）

```sql
CREATE TABLE phone_hash_partition (
    id BIGINT NOT NULL,
    phone BIGINT NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY(id, phone)
)
PARTITION BY HASH(id)
PARTITIONS 8;
```
MySQL 会对 id 字段做哈希运算（本质就是 % 8 取模），然后把数据分配到 8 个分区之一中。
---

## 4. KEY 分区（MySQL 内置哈希分区）

```sql
CREATE TABLE phone_key_partition (
    id BIGINT NOT NULL,
    phone VARCHAR(11) NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY(id, phone)
)
PARTITION BY KEY(phone)
PARTITIONS 4;
```
对字段 phone 的值计算哈希值（由 MySQL 内部的哈希函数决定），然后将数据平均分配到 4 个分区中。
---

### 说明

* **RANGE** 和 **LIST**：适合有明确区间或枚举值划分的业务，如按手机号号段、地区编码等。
* **HASH** 和 **KEY**：适合无明显分组，追求均匀负载和并发性能。

---



KEY 分区 是一种 基于哈希值 的分区方式，区别在于：

HASH()：你自己定义表达式

KEY()：MySQL 使用自己的哈希函数（无法自定义）

🔹 比如：


PARTITION BY HASH(phone % 4) -- 你自己控制 hash 逻辑
PARTITION BY KEY(phone)     -- MySQL 自动决定 hash 算法

4. 分区的基本规则和限制

分区字段必须是主键或唯一索引的一部分
保证唯一性在分区上的可识别性。
不支持对 TEXT、BLOB、JSON 类型分区
分区字段应是整数、日期、字符串等简单类型。
不能使用非确定性函数
分区函数必须是确定性的，不能用 NOW()、UUID()、RAND()、SUBSTRING()（在某些场景）等。
不能跨分区创建外键约束
外键不支持跨分区表。
最大分区数限制
MySQL 最大支持 1024 个分区。

5. 分区裁剪（Partition Pruning）

查询时，MySQL 会根据 WHERE 条件中涉及的分区字段，自动判断只访问相关分区，称为分区裁剪。
举例：

SELECT * FROM orders WHERE order_date = '2024-06-26';

如果 orders 表按日期做 RANGE 分区，MySQL 只访问对应分区，极大提高性能。

6. MySQL 分区示例（手机号号段分区）

创建表，按手机号前三位分区（用整数字段 `phone_prefix`）

CREATE TABLE phone_data (
    id BIGINT NOT NULL,
    phone VARCHAR(11) NOT NULL,
    phone_prefix INT NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY(id, phone, phone_prefix)
)
PARTITION BY RANGE (phone_prefix) (
    PARTITION p130 VALUES LESS THAN (131),
    PARTITION p131 VALUES LESS THAN (132),
    PARTITION p132 VALUES LESS THAN (133),
    PARTITION p_other VALUES LESS THAN MAXVALUE
);

插入示例数据

INSERT INTO phone_data VALUES 
(1, '13012345678', 130, 'Alice'),
(2, '13187654321', 131, 'Bob'),
(3, '13200001111', 132, 'Charlie'),
(4, '13355556666', 133, 'David');

查询只访问相关分区

SELECT * FROM phone_data WHERE phone_prefix = 131;

7. 分区的应用场景举例

场景	适合的分区类型	说明
大量日志按时间查询	RANGE（按日期范围）	快速定位时间区间日志
按业务类别或地区查询	LIST	精准定位业务分组、地区分区
大量无规律数据均匀分布	HASH/KEY	减少热点，提高并发写入能力
大数据的冷热数据分离	结合分区与冷热表设计	热点数据分区缓存，冷数据归档处理