MySQL 分区

目录

分区场景

如何使用分区表

分区命令

---

MySQL 5.1引入分区表。在MySQL5.5 中，分区表又做了很多改进，这才使得分区表逐步用在生产环境。

分区是解决大数据量情况下，根据分区筛选掉大量数据的操作。

分区场景

1. 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。
2. 分区表的数据更容易维护。例如，想批量删除大量数据可以使用清除整个分区的方式。
3. 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
4. 可以使用分区表来避免某些特殊的瓶颈，例如InnoDB 的单个索引的互斥访问。
5. 如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

如何使用分区表

假设我们希望从一个非常大的表中查询出一段时间的记录，而这个表中包含了很多年的历史数据，数据是按照时间排序的，例如，希望查询最近几个月的数据，这大约有 10 亿条记录。

你打算如何查询这个表？如何才能更高效？

分析：因为数据量巨大，肯定不能再每次查询的时候都扫描全表。考虑到索引在空间和维护上的消耗，也不希望使用索引。即使真的使用索引，你会发现数据并不是按照想要的方式聚集的，而且会有大量的碎片产生，最终会导致一个查询产生成千上万的随机 I/O，应用程序也随之僵死。加一两个索引也许可以解决这个问题，不过多数情况下，索引不会有任何作用。这时候只有两条路可选：让所有的查询都只在数据表上做顺序扫描，或者将数据表和索引全部都缓存在内存里。

在数据量超大的时候，B-Tree索引就无法起作用了。除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，如果数据量巨大，则非常慢。

分区以代价非常小的方式定位到需要的数据在哪一片区域，这片区域可以做顺序搜索，可以建索引，还可以将数据都缓存到内存，等等。因为分区无须额外的数据结构记录每个分区有哪些数据——分区不需要精确定位每条数据的位置，也就无须额外的数据结构——所以其代价非常低。只需要一个简单的表达式就可以表达每个分区存放的是什么数据。

分区命令

CREATE TABLE IF NOT EXISTS TableName
(
 STATIS_DATE      INTEGER         NOT NULL COMMENT '日期',
 PAGE_ID          VARCHAR(32)     NOT NULL COMMENT '页面编码',
 PAGE_NM          VARCHAR(128)             COMMENT '页面名称',
 PRIMARY KEY (STATIS_DATE,PAGE_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='表注释'
PARTITION BY RANGE COLUMNS(STATIS_DATE)
(
 PARTITION P_201812 VALUES LESS THAN (20190101) ENGINE = InnoDB,
 PARTITION P_201901 VALUES LESS THAN (20190201) ENGINE = InnoDB,
 PARTITION P_201902 VALUES LESS THAN (20190301) ENGINE = InnoDB,
 PARTITION P_201903 VALUES LESS THAN (20190401) ENGINE = InnoDB,
 PARTITION P_201904 VALUES LESS THAN (20190501) ENGINE = InnoDB,
 PARTITION P_201905 VALUES LESS THAN (20190601) ENGINE = InnoDB,
 PARTITION P_201906 VALUES LESS THAN (20190701) ENGINE = InnoDB,
 PARTITION P_201907 VALUES LESS THAN (20190801) ENGINE = InnoDB,
 PARTITION P_201908 VALUES LESS THAN (20190901) ENGINE = InnoDB,
 PARTITION P_201909 VALUES LESS THAN (20191001) ENGINE = InnoDB,
 PARTITION P_201910 VALUES LESS THAN (20191101) ENGINE = InnoDB,
 PARTITION P_201911 VALUES LESS THAN (20191201) ENGINE = InnoDB,
 PARTITION P_201912 VALUES LESS THAN (20200101) ENGINE = InnoDB,
 PARTITION P_202001 VALUES LESS THAN (20200201) ENGINE = InnoDB,
 PARTITION P_202002 VALUES LESS THAN (20200301) ENGINE = InnoDB,
 PARTITION P_202003 VALUES LESS THAN (20200401) ENGINE = InnoDB,
 PARTITION P_202004 VALUES LESS THAN (20200501) ENGINE = InnoDB,
 PARTITION P_202005 VALUES LESS THAN (20200601) ENGINE = InnoDB,
 PARTITION P_202006 VALUES LESS THAN (20200701) ENGINE = InnoDB,
 PARTITION P_202007 VALUES LESS THAN (20200801) ENGINE = InnoDB,
 PARTITION P_202008 VALUES LESS THAN (20200901) ENGINE = InnoDB,
 PARTITION P_202009 VALUES LESS THAN (20201001) ENGINE = InnoDB,
 PARTITION P_202010 VALUES LESS THAN (20201101) ENGINE = InnoDB,
 PARTITION P_202011 VALUES LESS THAN (20201201) ENGINE = InnoDB,
 PARTITION P_202012 VALUES LESS THAN (20210101) ENGINE = InnoDB
);