本文共 12470 字,大约阅读时间需要 41 分钟。
欢迎扫码加入Java高知群交流
自5.1开始对分区(Partition)有支持
mysql> CREATE TABLE part_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=myisam 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 );注意最后一行,考虑到可能的最大值
mysql> create table no_part_tab (c1 int(11) default NULL,c2 varchar(30) default NULL,c3 date default NULL) engine=myisam;*通过存储过程灌入800万条测试数据
mysql> delimiter // /* 设定语句终结符为 //,因存储过程语句用;结束 */
mysql> CREATE PROCEDURE load_part_tab() begin declare v int default 0; while v < 8000000 do insert into part_tab values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652)); set v = v + 1; end while; end //mysql> delimiter ;mysql> call load_part_tab();Query OK, 1 row affected (8 min 17.75 sec)
mysql> insert into no_part_tab select * from part_tab;Query OK, 8000000 rows affected (51.59 sec)
* 测试SQL性能
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';+----------+
1 row in set (0.55 sec)
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';+----------+
mysql > explain select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G/* 结尾的\G使得mysql的输出改为列模式 */
mysql> explain select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G*************************** 1. row ***************************
mysql> create index idx_of_c3 on no_part_tab (c3);Query OK, 8000000 rows affected (1 min 18.08 sec)
mysql> create index idx_of_c3 on part_tab (c3);Query OK, 8000000 rows affected (1 min 19.19 sec)
* 再次测试SQL性能
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';+----------+
1 row in set (2.42 sec) /* 为原来4.69 sec 的51%*/
重启mysql ( net stop mysql, net start mysql)后,查询时间降为0.89 sec,几乎与分区表相同。
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';+----------+
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1997-12-31';+----------+
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1997-12-31';+----------+
1 row in set (2.63 sec)
** 增加未索引字段查询
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date'1996-12-31' and c2='hello';+----------+
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1996-12-31' and c2='hello';+----------+
CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY RANGE (uid) ( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 VALUES LESS THAN (9000000) DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');在这里,将用户表分成4个分区,以每300万条记录为界限,每个分区都有自己独立的数据、索引文件的存放目录,与此同时,这些目录所在的物理磁盘分区可能也都是完全独立的,可以提高磁盘IO吞吐量。
CREATE TABLE category ( cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY LIST (cid) ( PARTITION p0 VALUES IN (0,4,8,12) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES IN (1,5,9,13) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 VALUES IN (2,6,10,14) DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 VALUES IN (3,7,11,15) DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');分成4个区,数据文件和索引文件单独存放。
CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY HASH (uid) PARTITIONS 4 ( PARTITION p0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');分成4个区,数据文件和索引文件单独存放。
CREATE TABLE ti2 (id INT, amount DECIMAL(7,2), tr_date DATE) ENGINE=myisam PARTITION BY HASH( MONTH(tr_date) ) PARTITIONS 6;CREATE PROCEDURE load_ti2() begin declare v int default 0; while v < 80000 do insert into ti2 values (v,'3.14',adddate('1995-01-01',(rand(v)*3652) mod 365)); set v = v + 1; end while; end //* KEY 类型
CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY KEY (uid) PARTITIONS 4 ( PARTITION p0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx');分成4个区,数据文件和索引文件单独存放。
CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx');对 RANGE 分区再次进行子分区划分,子分区采用 HASH 类型。
CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '')PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx');对 RANGE 分区再次进行子分区划分,子分区采用 KEY 类型。
ALERT TABLE users DROP PARTITION p0;
删除分区 p0。
* 重建分区
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;用 REORGANIZE 方式重建分区的数量变成2,在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。
ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19) DATA DIRECTORY = '/data8/data' INDEX DIRECTORY = '/data9/idx');新增一个RANGE分区。
ALTER TABLE users ADD PARTITION PARTITIONS 8;将分区总数扩展到8个。
alter table results partition by RANGE (month(ttime)) (PARTITION p0 VALUES LESS THAN (1),PARTITION p1 VALUES LESS THAN (2) , PARTITION p2 VALUES LESS THAN (3) ,PARTITION p3 VALUES LESS THAN (4) , PARTITION p4 VALUES LESS THAN (5) ,PARTITION p5 VALUES LESS THAN (6) , PARTITION p6 VALUES LESS THAN (7) ,PARTITION p7 VALUES LESS THAN (8) , PARTITION p8 VALUES LESS THAN (9) ,PARTITION p9 VALUES LESS THAN (10) , PARTITION p10 VALUES LESS THAN (11),PARTITION p11 VALUES LESS THAN (12),PARTITION P12 VALUES LESS THAN (13) );
mysql> ALTER TABLE np_pk -> PARTITION BY HASH( TO_DAYS(added) ) -> PARTITIONS 4;ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> ALTER TABLE np_pk -> PARTITION BY HASH(id) -> PARTITIONS 4;Query OK, 0 rows affected (0.11 sec)
mysql> alter table results drop PRIMARY KEY;Query OK, 5374850 rows affected (7 min 4.05 sec)
mysql> alter table results add PRIMARY KEY(id, ttime);Query OK, 5374850 rows affected (6 min 14.86 sec)
Records: 5374850 Duplicates: 0 Warnings: 0
欢迎扫码加入Java高知群交流
转载地址:http://qoemi.baihongyu.com/