一、下面所有的测试基于mysql 8.0.17版本。 elcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 34 Server version: 8.0.17 Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. Server version: 8.0.17 Source distribution
CREATE TABLE t1 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2) ) PARTITION BY HASH(col3) PARTITIONS 4; CREATE TABLE t2 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1), UNIQUE KEY (col3) ) PARTITION BY HASH(col1 + col3) PARTITIONS 4; 这样会大大限制mysql分区使用范围。
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) ); 2. list 分区,比较少使用。类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
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( YEAR(hired) ) PARTITIONS 4; 4.KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) ) PARTITION BY KEY() PARTITIONS 2; CREATE TABLE k1 ( id INT NOT NULL, name VARCHAR(20), UNIQUE KEY (id) )PARTITION BY KEY() PARTITIONS 2; 上面两个例子你会觉得很奇怪,都没有指定分区列。mysql默认就使用了唯一键来做了分区。
CREATE TABLE part_tab (c1 int default NULL, c2 varchar(30) default NULL, c3 date not null) PARTITION BY RANGE(year(c3)) (PARTITION p0 VALUES LESS THAN (1995), PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), PARTITION p11 VALUES LESS THAN (MAXVALUE) ); CREATE TABLE no_part_tab(c1 int default NULL, c2 varchar(30) default NULL, c3 date not null); drop procedure load_part_tab; delimiter $$ CREATE PROCEDURE load_part_tab() begin declare v int default 0; while v < 8000000 do insert into part_tab values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652)); set v = v + 1; end while; end;$$ delimiter ; call load_part_tab(); //从 part_tab 导入数据到 no_part_tab insert into no_part_tab select * from part_tab; 创建了2个表,数据都是800万。
一个表no_part_tab的大小是428M。在这里另外
mysql> explain select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31'; +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | part_tab | p1 | ALL | NULL | NULL | NULL | NULL | 796215 | 11.11 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31'; +----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | no_part_tab | NULL | ALL | NULL | NULL | NULL | NULL | 7773613 | 11.11 | Using where | +----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31'; +----------+ | count(*) | +----------+ | 795181 | +----------+ 1 row in set (0.39 sec) mysql> select count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31'; +----------+ | count(*) | +----------+ | 795181 | +----------+ 1 row in set (2.94 sec) 查询时间和扫描的行数,高下可判。
六、mysql分区操作,在mysql8测试通过 序号 常见操作 举例 备注 1 删除分区 1) aher table emp drop partition p1; 2) 一次性删除各个区:alter table emp drop partition p1,p2; 3) 删除表的所有分区:Alter table emp remove partitioning; 1)不可以删除hash或者kev分区。 2)删除分区会删除数据,但是删除表的所有分区--不会丢失数据(验证ok) 2 增加分区 alter table emp add partition (partition p1 values less than (24)); alter table emp add partition partition p3 values in (40)); 1)增加分区的值只能增加,不能比现在所拥有的分区值低 3 分解分区 alter table emp reorganize partition p2 into (partition p1 values less than (6), partition p2 values less than (16)); reorganize partition关键字可以对表的部分分区或全部分区进行修 改,并且不会丢失数据。分解前后分区的整体范围应该一致。 4 合并分区 alter table emp reorganize partition p1,p3 into (partition p1 values less than (1000)); 不会丢失数据 5 重新定义分区 重新定义Hash分区:Alter table emp partition by hash(salary) partitions 7; 重新定义Range分区: Alter table emp partition by range(id) (partition p1 values less than (2000), partition p2 values less than (4000)); 相当于删除重建。