MySQL 分区表使用指南

执行上面的 SQL，创建表，并插入记录。

查询年份大于 2016 的记录，语句如下：

这个查询，如果想优化，首先想到的就是在年份字段上添加索引，因为年份字段作为查询条件的一个字段。

但实际操作就会发现，添加了索引，最终查询并没有使用这个索引，因为 MySQL 执行器会推断，当结果集的数量占总记录数的比例较大时，不会使用索引，因为无论是否使用索引，扫描的记录总数差不多。

解决方法就是在磁盘的检索范围上进行优化，那就是创建分区表来解决。

分区表的创建

执行下面的语句，可以删除上面创建的表，重新创建带有分区的表。

drop table test_partition;
CREATE TABLE test_partition ( id int(11) NOT NULL, create_time datetime NOT NULL, cyear int, PRIMARY KEY (id,create_time , cyear) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE (cyear) (
 PARTITION y14before VALUES LESS THAN (2014) ,
 PARTITION y15 VALUES LESS THAN (2015) ,
 PARTITION y16 VALUES LESS THAN (2016) ,
 PARTITION y17 VALUES LESS THAN (2017) ,
 PARTITION y18 VALUES LESS THAN (2018) ,
 PARTITION y19 VALUES LESS THAN (2019) ,
 PARTITION y20 VALUES LESS THAN (2020) ,
 PARTITION y20after VALUES LESS THAN maxvalue
);

PARTITION BY RANGE 表示根据字段进行范围分区。

PARTITION 就是分区表的关键字，y15 代表分区的名称，LESS THAN 条件。

当进行数据插入时，年份为 2015 年的数据，就会存储在 y15 这个分区中。年份为 2016 的数据就会存储在 y16 这个分区中。以此类推。

分区表的使用

插入数据

INSERT INTO test_partition VALUES (1,"20130722000000",2013);
INSERT INTO test_partition VALUES (2,"20140722000000",2014);
INSERT INTO test_partition VALUES (3,"20150722000000",2015);
INSERT INTO test_partition VALUES (4,"20160722000000",2016);
INSERT INTO test_partition VALUES (5,"20170722000000",2017);
INSERT INTO test_partition VALUES (6,"20180722000000",2018);
INSERT INTO test_partition VALUES (7,"20190722000000",2019);
INSERT INTO test_partition VALUES (8,"20200722000000",2020);
INSERT INTO test_partition VALUES (9,"20210722000000",2021);
INSERT INTO test_partition VALUES (10,"20220722000000",2022);

可以看到在数据库安装目录的 data 目录中，test 这个文件夹下面有这样一些文件，这就是 test 数据库中 test_partition 表的不同分区数据。文件名称对应的就是表名称 + 分区的名称。

在进行查询时，MySQL 会根据查询条件，从指定的分区表中获取数据。这样就缩小了数据的检索范围。

查询这个执行计划，可以看到 partitions 字段值就是数据涉及到的分区名称。MySQL 只去查找涉及到的分区，然后从中获取数据，并不会把所有表数据全部去扫描，从物理层面减少扫描范围。

因为查询条件是年份大于 2016，所以只查询 2017 及其之后年份的数据，2017 年的数据存储在 y18 的分区中，所有就可以看到 y18,y19,y20after，这三个分区。

分区表数据统计

通过下面的查询，可以看到表的每个分区中数据分布情况：

SELECT PARTITION_NAME AS "分区", TABLE_ROWS AS "行数" FROM information_schema.partitions WHERE table_schema="test" -- 数据库名称 AND table_name="test_partition"; -- 表名

分区表的使用限制

1. 查询必须包含分区列（上面例子中的 cyear 列），不允许对分区列进行计算。
2. 分区列必须是数字类型。
3. 分区表不支持建立外键索引。
4. 建表时主键必须包含所有的列（上面例子中，PRIMARY KEY (id,create_time , cyear)）。
5. 最多 1024 个分区。