MySQL RANGE分区表
1,860 total views, 1 views today
一、开启分区表功能
MySQL想要使用分区表，必须安装partition插件。
查看是否安装了partition插件。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
mysql> show plugins; +----------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | ........... | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | ........... | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +----------------------------+----------+--------------------+---------+---------+ 42 rows in set (0.00 sec) SELECT PLUGIN_NAME as Name, PLUGIN_VERSION as Version, PLUGIN_STATUS as Status FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_TYPE='STORAGE ENGINE'; +--------------------+---------+----------+ | Name | Version | Status | +--------------------+---------+----------+ | binlog | 1.0 | ACTIVE | | MRG_MYISAM | 1.0 | ACTIVE | | MEMORY | 1.0 | ACTIVE | | CSV | 1.0 | ACTIVE | | MyISAM | 1.0 | ACTIVE | | PERFORMANCE_SCHEMA | 0.1 | ACTIVE | | InnoDB | 5.6 | ACTIVE | | BLACKHOLE | 1.0 | ACTIVE | | FEDERATED | 1.0 | DISABLED | | ARCHIVE | 3.0 | ACTIVE | | partition | 1.0 | ACTIVE | +--------------------+---------+----------+ 11 rows in set (0.00 sec) |
MySQL 5.6二进制版本默认提供分区功能；如果是编译安装，那么编译时请加上 -DWITH_PARTITION_STORAGE_ENGINE 选项。
如果不想使用分区，启动MySQL服务请加上–skip-partition 选项。
分区表中的分区必须为同一种存储引擎。
分区不能使用 MERGE, CSV, or FEDERATED 存储引擎。
创建hash分区表ti，并分配6个分区。
|
1 2 3 4 |
CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE) ENGINE=INNODB PARTITION BY HASH( MONTH(tr_date) ) PARTITIONS 6; |
可以看到，每个分区都有单独的.idb文件
|
1 2 3 4 5 6 7 8 9 10 11 |
# ll -th 总用量 596K -rw-rw---- 1 mysql mysql 96K 2月 6 14:05 ti#P#p1.ibd -rw-rw---- 1 mysql mysql 96K 2月 6 14:05 ti#P#p3.ibd -rw-rw---- 1 mysql mysql 96K 2月 6 14:05 ti#P#p5.ibd -rw-rw---- 1 mysql mysql 96K 2月 6 14:05 ti#P#p0.ibd -rw-rw---- 1 mysql mysql 96K 2月 6 14:05 ti#P#p2.ibd -rw-rw---- 1 mysql mysql 96K 2月 6 14:05 ti#P#p4.ibd -rw-rw---- 1 mysql mysql 44 2月 6 14:05 ti.par -rw-rw---- 1 mysql mysql 8.5K 2月 6 14:05 ti.frm -rw-rw---- 1 mysql mysql 61 2月 6 14:05 db.opt |
分区操作针对一张表的数据和所有索引，你不能单独对数据分区，而不对索引分区。
二、分区表的优点
●分区表可以存储更多数据，不同的分区可以存放在单独磁盘或者系统分区上。
●便于清理不需要的数据，使用drop partition即可。当然，也能为新数据创建分区。
●优化SQL查询，自适应where条件，只扫描where条件过滤后数据对应的分区，排除了其他不需要的分区。另外，可以根据日常的SQL，将热数据重组到一个分区，能够提高不少性能。从5.6开始，查询支持指定分区。如 SELECT * FROM t PARTITION (p0,p1) WHERE c < 5，该SQL只扫描p0和p1分区，然后使用where条件过滤。大大提升了SQL查询速度。分区表支持 DELETE, INSERT, REPLACE, UPDATE, LOAD DATA, LOAD XML等命令。
●聚合函数默认并行化执行，例如 SUM() 和 COUNT()。 SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id; 这条SQL，在每个分区同时并行执行，最后将结果合并得出最终结果。
●如果将数据分布到不同磁盘上，可以大大提高吞吐量。
三、分区表类型
1、范围分区（RANGE partitioning）
顾名思义，每个分区中存放的数据都有指定的范围，数据符合这个范围就存放到对应的分区中。范围界定使用 VALUES LESS THAN 关键字。
##1、普通数值类型的分区，如INT类型
创建range分区表，以store_id列作为分区列，store_id<6落在p0分区，6<store_id<11落在p1分区，11<store_id<16落在p2分区，16<store_id<21落在p3分区。
这里没有指定存储引起，它以MySQL默认为准。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21) ); |
分区表的详细信息，查询 information_schema.PARTITIONS 可以得到。
字段解释
TABLE_SCHEMA：表属于的database名字。
PARTITION_NAME：分区名。
PARTITION_ORDINAL_POSITION：分区序号。
PARTITION_METHOD：分区类型，如RANGE、LIST、HASH等。
PARTITION_DESCRIPTION：用于RANGE和LIST分区，分区范围的值。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
select TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWS, PARTITION_ORDINAL_POSITION, CREATE_TIME, UPDATE_TIME, TABLESPACE_NAME from information_schema.PARTITIONS where TABLE_NAME = 'employees' order by PARTITION_ORDINAL_POSITION; +--------------+------------+----------------+------------------+----------------------+-----------------------+------------+----------------------------+---------------------+-------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | PARTITION_ORDINAL_POSITION | CREATE_TIME | UPDATE_TIME | TABLESPACE_NAME | +--------------+------------+----------------+------------------+----------------------+-----------------------+------------+----------------------------+---------------------+-------------+-----------------+ | szlsd | employees | p0 | RANGE | store_id | 6 | 0 | 1 | 2017-02-07 11:05:19 | NULL | NULL | | szlsd | employees | p1 | RANGE | store_id | 11 | 0 | 2 | 2017-02-07 11:05:19 | NULL | NULL | | szlsd | employees | p2 | RANGE | store_id | 16 | 0 | 3 | 2017-02-07 11:05:19 | NULL | NULL | | szlsd | employees | p3 | RANGE | store_id | 21 | 0 | 4 | 2017-02-07 11:05:19 | NULL | NULL | +--------------+------------+----------------+------------------+----------------------+-----------------------+------------+----------------------------+---------------------+-------------+-----------------+ 4 rows in set (0.00 sec) |
竖排版更利于阅读
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
select TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWS, PARTITION_ORDINAL_POSITION, CREATE_TIME, UPDATE_TIME, TABLESPACE_NAME from information_schema.PARTITIONS where TABLE_NAME = 'employees' order by PARTITION_ORDINAL_POSITION\G *************************** 1. row *************************** TABLE_SCHEMA: szlsd TABLE_NAME: employees PARTITION_NAME: p0 PARTITION_METHOD: RANGE PARTITION_EXPRESSION: store_id PARTITION_DESCRIPTION: 6 TABLE_ROWS: 0 PARTITION_ORDINAL_POSITION: 1 CREATE_TIME: 2017-02-07 11:05:19 UPDATE_TIME: NULL TABLESPACE_NAME: NULL *************************** 2. row *************************** TABLE_SCHEMA: szlsd TABLE_NAME: employees PARTITION_NAME: p1 PARTITION_METHOD: RANGE PARTITION_EXPRESSION: store_id PARTITION_DESCRIPTION: 11 TABLE_ROWS: 0 PARTITION_ORDINAL_POSITION: 2 CREATE_TIME: 2017-02-07 11:05:19 UPDATE_TIME: NULL TABLESPACE_NAME: NULL *************************** 3. row *************************** TABLE_SCHEMA: szlsd TABLE_NAME: employees PARTITION_NAME: p2 PARTITION_METHOD: RANGE PARTITION_EXPRESSION: store_id PARTITION_DESCRIPTION: 16 TABLE_ROWS: 0 PARTITION_ORDINAL_POSITION: 3 CREATE_TIME: 2017-02-07 11:05:19 UPDATE_TIME: NULL TABLESPACE_NAME: NULL *************************** 4. row *************************** TABLE_SCHEMA: szlsd TABLE_NAME: employees PARTITION_NAME: p3 PARTITION_METHOD: RANGE PARTITION_EXPRESSION: store_id PARTITION_DESCRIPTION: 21 TABLE_ROWS: 0 PARTITION_ORDINAL_POSITION: 4 CREATE_TIME: 2017-02-07 11:05:19 UPDATE_TIME: NULL TABLESPACE_NAME: NULL 4 rows in set (0.00 sec) |
熟悉Oracle分区表的朋友，应该会发现上面分区表创建的有问题。没错，就是没有MAXVALUE分区，MAXVALUE 分区用于存放大于现有分区范围的数据。一是避免忘记分配分区，造成数据无法插入；二是插入的数据存在很大不确定性，用于存储这些不确定数据。
RANGE分区表必须含有MAXVALUE分区，语法 LESS THAN MAXVALUE 。大于16的数据都会存储在p3分区红。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN MAXVALUE ); |
MySQL官方文档有一个很有意思的例子，按照工号（job_code）位数来分区。
2位工号代表普通工人；3位工号代表办公室人员和供应人员；4位工号代表管理人员。
这样工人的数据存放在p0分区，办公室人员和供应人员的数据存放在p1分区，而管理人员的数据存放在p2分区。
由于工号的位数是固定的，所以无需考虑创建MAXVALUE分区。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (job_code) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (10000) ); |
2、date类型的列做RANGE分区
例子中，separated列是DATE类型。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 ); |
3、TIMESTAMP类型的列做RANGE分区
report_updated列为TIMESTAMP时间戳类型。如果我们直接使用时间戳分区，还要把时间计算成时间戳，然后分区，麻烦而且容易出错。
而UNIX_TIMESTAMP()函数可以把TIMESTAMP类型转换成时间，就容易很多，不容易出错。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE quarterly_report_status ( report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ), PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ), PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ), PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ), PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ), PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ), PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ), PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ), PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ), PARTITION p9 VALUES LESS THAN (MAXVALUE) ); |
4、RANGE分区适用场景
●可以按照分区删除老数据，ALTER TABLE employees DROP PARTITION p0。比delete高效的多，而且影响小。
●表中有日期或者时间类型的列
●经常基于分区列查询，MySQL能够快速根据where条件扫描数据所在的分区。EXPLAIN PARTITIONS SELECT COUNT(*) FROM employees WHERE separated BETWEEN ‘2000-01-01’ AND ‘2000-12-31’ GROUP BY store_id;