Feb
22

mysql分区功能详细介绍，以及实例

技术安全
mysql,分区

3，list分区


LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分 区是从属于一个连续区间值的集合。


  1. //这种方式失败  

  2. mysql> CREATE TABLE IF NOT EXISTS `list_part` (  

  3.  ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',  

  4.  ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省',  

  5.  ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',  

  6.  ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女',  

  7.  ->   PRIMARY KEY (`id`)  

  8.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  

  9.  -> PARTITION BY LIST (province_id) (  

  10.  ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  

  11.  ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  

  12.  ->     PARTITION p2 VALUES IN (13,14,15,19),  

  13.  ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  

  14.  -> );  

  15. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function 

  16.  

  17. //这种方式成功 

  18. mysql> CREATE TABLE IF NOT EXISTS `list_part` ( 

  19.  ->   `id` int(11) NOT NULL  COMMENT '用户ID',  

  20.  ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', 

  21.  ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', 

  22.  ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女'  

  23.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  

  24.  -> PARTITION BY LIST (province_id) (  

  25.  ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  

  26.  ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  

  27.  ->     PARTITION p2 VALUES IN (13,14,15,19),  

  28.  ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  

  29.  -> );  

  30. Query OK, 0 rows affected (0.33 sec)  



上面的这个创建list分区时，如果有主銉的话，分区时主键必须在其中，不然就会报错。如果我不用主键，分区就创建成功了，一般情况下，一个张表肯定会有一个主键，这算是一个分区的局限性吧。


如果对数据进行测试，请参考range分区的测试来操作


4，hash分区


HASH分区主要用来确保数据在预先确定数目的分区中平均分布，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以 及指定被分区的表将要被分割成的分区数量。


  1. mysql> CREATE TABLE IF NOT EXISTS `hash_part` (  

  2.  ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID',  

  3.  ->   `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT '评论',  

  4.  ->   `ip` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  

  5.  ->   PRIMARY KEY (`id`)  

  6.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  

  7.  -> PARTITION BY HASH(id)  

  8.  -> PARTITIONS 3;  

  9. Query OK, 0 rows affected (0.06 sec)  



测试请参考range分区的操作


5，key分区


按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用 户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。


  1. mysql> CREATE TABLE IF NOT EXISTS `key_part` (  

  2.  ->   `news_id` int(11) NOT NULL  COMMENT '新闻ID',  

  3.  ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',  

  4.  ->   `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  

  5.  ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'  

  6.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  

  7.  -> PARTITION BY LINEAR HASH(YEAR(create_time))  

  8.  -> PARTITIONS 3;  

  9. Query OK, 0 rows affected (0.07 sec)  



测试请参考range分区的操作


6，子分区


子分区是分区表中每个分区的再次分割，子分区既可以使用HASH希分区，也可以使用KEY分区。这 也被称为复合分区（composite partitioning）。


1，如果一个分区中创建了子分区，其他分区也要有子分区


2，如果创建了了分区，每个分区中的子分区数必有相同


3，同一分区内的子分区，名字不相同，不同分区内的子分区名子可以相同（5.1.50不适用）


  1. mysql> CREATE TABLE IF NOT EXISTS `sub_part` (  

  2.  ->   `news_id` int(11) NOT NULL  COMMENT '新闻ID',  

  3.  ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',  

  4.  ->   `u_id`  int(11) NOT NULL DEFAULT 0s COMMENT '来源IP',  

  5.  ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'  

  6.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  

  7.  -> PARTITION BY RANGE(YEAR(create_time))  

  8.  -> SUBPARTITION BY HASH(TO_DAYS(create_time))(  

  9.  -> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2),  

  10.  -> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good),  

  11.  -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3)  

  12.  -> );  

  13. Query OK, 0 rows affected (0.07 sec)  



官方网站说不同分区内的子分区可以有相同的名字，但是mysql5.1.50却不行会提示以下错误


ERROR 1517 (HY000): Duplicate partition name s1


三，分区管理


1，删除分区


  1. mysql> alter table user drop partition p4;  



2，新增分区


  1. //range添加新分区  

  2. mysql> alter table user add partition(partition p4 values less than MAXVALUE);  

  3. Query OK, 0 rows affected (0.06 sec)  

  4. Records: 0  Duplicates: 0  Warnings: 0  

  5.   

  6. //list添加新分区  

  7. mysql> alter table list_part add partition(partition p4 values in (25,26,28));  

  8. Query OK, 0 rows affected (0.01 sec)  

  9. Records: 0  Duplicates: 0  Warnings: 0  

  10.   

  11. //hash重新分区  

  12. mysql> alter table hash_part add partition partitions 4;  

  13. Query OK, 0 rows affected (0.12 sec)  

  14. Records: 0  Duplicates: 0  Warnings: 0  

  15.   

  16. //key重新分区  

  17. mysql> alter table key_part add partition partitions 4;  

  18. Query OK, 1 row affected (0.06 sec)    //有数据也会被重新分配  

  19. Records: 1  Duplicates: 0  Warnings: 0  

  20.   

  21. //子分区添加新分区，虽然我没有指定子分区，但是系统会给子分区命名的  

  22. mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE);  

  23. Query OK, 0 rows affected (0.02 sec)  

  24. Records: 0  Duplicates: 0  Warnings: 0  

  25.   

  26. mysql> show create table sub1_part\G;  

  27. *************************** 1. row ***************************  

  28.  Table: sub1_part  

  29. Create Table: CREATE TABLE `sub1_part` (  

  30.  `news_id` int(11) NOT NULL COMMENT '新闻ID',  

  31.  `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',  

  32.  `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  

  33.  `create_time` date NOT NULL DEFAULT '0000-00-00' COMMENT '时间'  

  34. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  

  35. !50100 PARTITION BY RANGE (YEAR(create_time))  

  36. SUBPARTITION BY HASH (TO_DAYS(create_time))  

  37. (PARTITION p0 VALUES LESS THAN (1990)  

  38.  (SUBPARTITION s0 ENGINE = InnoDB,  

  39.  SUBPARTITION s1 ENGINE = InnoDB,  

  40.  SUBPARTITION s2 ENGINE = InnoDB),  

  41.  PARTITION p1 VALUES LESS THAN (2000)  

  42.  (SUBPARTITION s3 ENGINE = InnoDB,  

  43.  SUBPARTITION s4 ENGINE = InnoDB,  

  44.  SUBPARTITION good ENGINE = InnoDB),  

  45.  PARTITION p2 VALUES LESS THAN (3000)  

  46.  (SUBPARTITION tank0 ENGINE = InnoDB,  

  47.  SUBPARTITION tank1 ENGINE = InnoDB,  

  48.  SUBPARTITION tank3 ENGINE = InnoDB),  

  49.  PARTITION p3 VALUES LESS THAN MAXVALUE  

  50.  (SUBPARTITION p3sp0 ENGINE = InnoDB,    //子分区的名子是自动生成的  

  51.  SUBPARTITION p3sp1 ENGINE = InnoDB,  

  52.  SUBPARTITION p3sp2 ENGINE = InnoDB))  

  53. 1 row in set (0.00 sec)  



3，重新分区


  1. //range重新分区  

  2. mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);  

  3. Query OK, 11 rows affected (0.08 sec)  

  4. Records: 11  Duplicates: 0  Warnings: 0  

  5.   

  6. //list重新分区  

  7. mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));  

  8. Query OK, 0 rows affected (0.28 sec)  

  9. Records: 0  Duplicates: 0  Warnings: 0  

  10.   

  11. //hash和key分区不能用REORGANIZE，官方网站说的很清楚  

  12. mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9;  

  13. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION 9' at line 1  



四，分区优点


1，分区可以分在多个磁盘，存储更大一点


2，根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了


3，进行大数据搜索时可以进行并行处理。


4，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量

共计 0 条评论

NAME:

required

E-MAIL:

required, will not be published

HOMEPAGE:

CONTENT: