高性能MySql进化论(十二):Mysql中分区表的使用总结。当数据量非常大时(表的容量到达GB或者是TB)，如果仍然采用索引的方式来优化查询，由于索引本生的消耗以及大量的索引碎片的产生，查询的过程会导致大量的随机I/O的产生，在这种场景下除非可以很好的利用覆盖索引，否则由于在查询的过程中需要根据索引回数据表查询，会导致性能受到很大的影响，这时可以考虑通过分区表的策略来提高查询的性能。
不同的数据库管理系统对分区的实现可能有所区别，本文主要以MySQL为基础
1 分区的类型
1.1RANGE分区
按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义
假定你想基于每个雇员离开公司的年份来分割表，也就是说，YEAR(separated)的值。实现这种分区模式的CREATE TABLE 语句的一个例子如下所示。
例如，你可能决定通过添加一个PARTITION BY RANGE子句把这个表分割成4个区间
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY RANGE (YEAR(separated)) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
插入一些测试数据后发现P1的数据文件明显增大 [sql] view plain copy print?
mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS SampleProc$$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE SampleProc()
-> BEGIN
-> DECLARE x INT;
-> SET x = 1000;
-> WHILE x<= 2000 DO
-> insert into employees(id,fname,lname,hired,separated,job_code,store_id) values(x,concat('firstname',x),concat('ai',x),'1994-01-01','1995-01-01',10,20);
-> SET x = x + 1;
-> END WHILE;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> call SampleProc() $$
Query OK, 1 row affected (22.55 sec)
mysql> delimiter ;
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id)
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;