如何在MySQL中使用分区表？

MySQL中的分区表可以将大量数据分布在多个文件中,提高查询效率。主要分区类型有:

1. RANGE分区:基于范围值进行分区,范围可以是日期、数字等。

sql
CREATE TABLE employees (
    id INT, 
    hire_date DATE
) 
PARTITION BY RANGE ( YEAR(hire_date) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001)
);

2. LIST分区:基于列表值进行分区。

sql
CREATE TABLE employees (
    id INT,
    depart_id INT 
)
PARTITION BY LIST(depart_id) (
    PARTITION p0 VALUES IN (1,3,5), 
    PARTITION p1 VALUES IN (2,4,6) 
);

3. HASH分区:基于用户定义的表达式进行分区,进行hash后分布到不同分区。

sql
CREATE TABLE employees (
    id INT 
) 
PARTITION BY HASH(id) 
PARTITIONS 4;

4. KEY分区:基于键值进行分区,主要用于联合主键的表分区。

sql 
CREATE TABLE tk (
    col1 INT, 
    col2 CHAR(5),
    added TIMESTAMP
) 
PARTITION BY KEY(col1, col2) 
PARTITIONS 8;

主要功能:

1. 减少IO和锁竞争,提高查询效率。
2. 方便数据维护(add/drop partition)。
3. 灵活的数据存放策略(按日期分区等)。

例:创建RANGE类型分区表,按年份分区。

sql
CREATE TABLE employees (
    id INT, 
    hire_date DATE 
)
PARTITION BY RANGE ( YEAR(hire_date) ) (
     PARTITION p0 VALUES LESS THAN (1991),
     PARTITION p1 VALUES LESS THAN (1996),
     PARTITION p2 VALUES LESS THAN (2001)
 );

INSERT INTO employees 
VALUES (1, '1990-01-01'),(2, '1995-12-31'), (3, '2000-06-01');

 SELECT * FROM employees;

输出:

+----+------------+
| id | hire_date  |
+----+------------+
|  1 | 1990-01-01 |   # 存储在p0分区
|  2 | 1995-12-31 |   # 存储在p1分区  
|  3 | 2000-06-01 |   # 存储在p2分区
+----+------------+