| mysql> show create table sub1_partG; *************************** 1. row *************************** Table: sub1_part Create Table: CREATE TABLE `sub1_part` ( `news_id` int(11) NOT NULL COMMENT '新闻ID', `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容', `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP', `create_time` date NOT NULL DEFAULT '0000-00-00' COMMENT '时间' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 !50100 PARTITION BY RANGE (YEAR(create_time)) SUBPARTITION BY HASH (TO_DAYS(create_time)) (PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0 ENGINE = InnoDB, SUBPARTITION s1 ENGINE = InnoDB, SUBPARTITION s2 ENGINE = InnoDB), PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s3 ENGINE = InnoDB, SUBPARTITION s4 ENGINE = InnoDB, SUBPARTITION good ENGINE = InnoDB), PARTITION p2 VALUES LESS THAN (3000) (SUBPARTITION tank0 ENGINE = InnoDB, SUBPARTITION tank1 ENGINE = InnoDB, SUBPARTITION tank3 ENGINE = InnoDB), PARTITION p3 VALUES LESS THAN MAXVALUE (SUBPARTITION p3sp0 ENGINE = InnoDB, //子分区的名子是自动生成的 SUBPARTITION p3sp1 ENGINE = InnoDB, SUBPARTITION p3sp2 ENGINE = InnoDB)) 1 row in set (0.00 sec) |
3，重新分区
range重新分区
| mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE); Query OK, 11 rows affected (0.08 sec) Records: 11 Duplicates: 0 Warnings: 0 |
list重新分区
| mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5)); Query OK, 0 rows affected (0.28 sec) Records: 0 Duplicates: 0 Warnings: 0 |
hash和key分区不能用REORGANIZE，官方网站说的很清楚
| mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9; 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，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量
MySQL分表、分区的区别和联系
一，什么是MySQL分表，分区
什么是分表，从表面意思上看呢，就是把一张表分成N多个小表
什么是分区，分区呢就是把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上，具体请参考mysql分区功能详细介绍，以及实例
二，mysql分表和分区有什么区别呢
1，实现方式上
