MySQL的分区（一）

mysql支持范围分区（range）、列表分区（list）、column分区、哈希分区（hash）、key分区、字段列表分区等

以timestamp类型字段作为分区键进行范围分区，有两种方式：
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)
);
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
在mysql5.7中timestamp范围分区表只能使用上面两种格式，使用to_days可能触发bug。
date类型的分区：
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
PARTITION p0 VALUES LESS THAN (‘1960-01-01’),
PARTITION p1 VALUES LESS THAN (‘1970-01-01’),
PARTITION p2 VALUES LESS THAN (‘1980-01-01’),
PARTITION p3 VALUES LESS THAN (‘1990-01-01’),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
使用列表分区的实例：
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)
);
使用ignore关键字，可以在插入多条数据时忽略没有匹配分区的数据，不报错：
mysql> CREATE TABLE h3 (
-> c1 INT,
-> c2 INT
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (1, 4, 7),
-> PARTITION p1 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO h3 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3
mysql> INSERT IGNORE INTO h3 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected (0.00 sec)
Records: 5 Duplicates: 2 Warnings: 0
mysql> SELECT * FROM h3;
+——+——+
| c1 | c2 |
+——+——+
| 7 | 5 |
| 1 | 9 |
| 2 | 5 |
+——+——+
3 rows in set (0.00 sec)
可以在定义表时指定分区属性，也可以使用alter table进行修改：
ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname) (
PARTITION p0 VALUES LESS THAN (‘g’),
PARTITION p1 VALUES LESS THAN (‘m’),
PARTITION p2 VALUES LESS THAN (‘t’),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
range columns不支持表达式，仅支持一或多个列名。
由于字符集character sets和collations的排列顺序不同，当进行数据迁移或者修改库、表、列的字符集时，
有可能因此而出现报错。比如对于大小写不敏感的collation，and排列顺序在Andersen之前，
但对于大小写敏感的collation就不是。
使用多个字段分区时，是按照字段顺序进行比较的，以下语句正确：
CREATE TABLE rc4 (
a INT,
b INT,
c INT
)
PARTITION BY RANGE COLUMNS(a,b,c) (
PARTITION p0 VALUES LESS THAN (0,25,50),
PARTITION p1 VALUES LESS THAN (10,20,100),
PARTITION p2 VALUES LESS THAN (10,30,50)
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);
但不建议使用此分区方式。
哈希分区实例：
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;
限制：
分区键必须是数字类型（integer)
分区键必须是主键和所有唯一键的一部分
需要指定分区数，否则默认是1
数据放入哪个分区是固定且可以提前计算的。比如：
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;
如果col3的值为’2005-09-15′,数据放入哪个分区的计算公式为：
MOD(YEAR(‘2005-09-01’),4)
= MOD(2005,4)
= 1
即放入第一个分区
线性分区（LINER HASH PARTITION），与普通hash分区的区别是其采用线性二次幂算法，公式为：
V = POWER(2, CEILING(LOG(2, num)))
语句举例：
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 LINEAR HASH( YEAR(hired) )
PARTITIONS 4;
key分区
与hash分区类似，只是算法不同。对于NDB cluster，使用md5()函数，其他引擎使用类似password（）函数进行分区。
举例：
CREATE TABLE tm1 (
s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;
key键必须是主键的一部分。当存在主键或非空唯一键时，也可以为空。
复合分区/子分区
mysql5.7中分区类型为range或list，子分区可以使用hash或key分区。
有以下限制：
每个分区中的子分区数量必须一样；
子分区名称不能重复；
################################
分区表中NULL的处理
在range分区表中，NULL被认为小于所有值，被存放在第一个分区中；
LIST分区表中，必须指定某个分区包含NULL值；
在hash或key分区表中，NULL被当作0处理。
#############################
分区管理
range和list分区表可以进行分区的增、删、合并、拆分操作
增删分区的逻辑和写法与oracle基本一致。拆分/分裂分区的语法：
ALTER TABLE members
REORGANIZE PARTITION p0 INTO (
PARTITION n0 VALUES LESS THAN (1970),
PARTITION n1 VALUES LESS THAN (1980)
);
hash和key分区表不能进行删除，但可以合并，如：
ALTER TABLE clients COALESCE PARTITION 4;
其中4是待删除的分区数量。
添加6个分区分区，如：
ALTER TABLE clients ADD PARTITION PARTITIONS 6;
分区交换（用于range分区或子分区）
类似oracle，对分区表的某个分区与普通表进行交换。例如：
ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt;
限制：
表结构一致，包含索引一致
普通表不包含外键，也不被其他表做外键引用
普通表数据的范围没有超过分区表定义
如果是innodb引擎，要求row格式一致
未使用data direct开发云主机域名ory选项
需要表的增删改查权限
该过程不会触发触发器
交换时自增值会被重置
ignore选项无效
使用without validation选项时，不再逐条校验数据
分区重建，相当于删除所有数据再重新插入：
ALTER TABLE t1 REBUILD PARTITION p0, p1;
##############################################
分区的维护
优化分区，用于对大量数据进行修改或删除操作后，可以回收空间并整理碎片
相当于运行了check partition、analyze partition、repair partition。
可以对多个分区一次性执行：
alter table t1 optimize partition p0,p1;
注意：innodb不支持单个分区的optimize操作，会升级为对全表的重建，如：
mysql> alter table t4 optimize partition p1;
+——-+———-+———-+———————————————————————————————+
| Table | Op | Msg_type | Msg_text |
+——-+———-+———-+———————————————————————————————+
| tl.t4 | optimize | note | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. |
| tl.t4 | optimize | status | OK |
+——-+———-+———-+———————————————————————————————+
2 rows in set (4.00 sec)
可以使用ALTER TABLE … REBUILD PARTITION和ALTER TABLE … ANALYZE PARTITION 进行替代，避免此问题。
分析分区，读取和存储分区的关键属性信息：
alter table t1 analyze partition p3;
修复分区
alter table t1 repair partition p0,p1;
正常执行时如果有重复键值会报错；
从5.7.2开始，可以使用alter ignore table选项，出现重复值时自动删除
检查分区
alter table trb3 check partition p1;
检查p1分区的数据和索引是否有中断。如果有重复值，则check操作会报错。
从5.7.2开始，可以使用alter ignore table选项，出现重复值时报告出来。
########################################################
获取分区信息
show create table
show table status =>是否分区
information_schema.partitions
explain select
举例：
mysql> show table status from tl like ‘t%’
-> ;
+——+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+——+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+
| t1 | InnoDB | 10 | Dynamic | 0 | 0 | 65536 | 0 | 65536 | 0 | 1 | 2020-06-18 15:35:43 | NULL | NULL | utf8_general_ci | NULL | partitioned | |
| t2 | InnoDB | 10 | Dynamic | 12 | 6826 | 81920 | 0 | 81920 | 0 | 30 | 2020-06-18 15:57:08 | 2020-06-18 16:01:59 | NULL | utf8_general_ci | NULL | partitioned | |
| t3 | InnoDB | 10 | Dynamic | 9 | 1820 | 16384 | 0 | 16384 | 0 | 1 | 2020-06-18 15:55:24 | 2020-06-18 16:01:59 | NULL | utf8_general_ci | NULL | | |
| t4 | InnoDB | 10 | Dynamic | 21 | 3900 | 81920 | 0 | 81920 | 0 | 30 | 2020-06-19 18:23:20 | NULL | NULL | utf8_general_ci | NULL | partitioned | |
| t5 | InnoDB | 10 | Dynamic | 0 | 0 | 49152 | 0 | 49152 | 0 | 30 | 2020-06-18 16:20:24 | 2020-06-18 16:27:30 | NULL | utf8_general_ci | NULL | partitioned | |
| tt | InnoDB | 10 | Dynamic | 3 | 5461 | 16384 | 0 | 0 | 0 | NULL | 2020-06-17 23:23:00 | 2020-06-17 23:28:35 | NULL | utf8_general_ci | NULL | | |
+——+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+———————+————+—————–+———-+—————-+———+

相关推荐: 【Mysql】从binlog中找出单个表的binlog信息

点击(此开发云主机域名处)折叠或打开相关推荐: Zabbix 3.2 监控部署一、准备工作1.系统环境系统：CentOS 6.8 X86_64MySQL：5.7.12Apache：2.4.20PHP：7.1.4Zabbix：3.2.62.安装所需环境[root…