Mysql分区、分表_mysql分区表创建语句

分区

一张表最多只能有1024个分区

1.range分区

 

使用场景：需要保存历史数据时候；某一数据表数据量过大时可以根据时间字段进行分区；需要根据时间字段查询的需求；

-- 当我们在对MySQL的数据表进行分区的时候,很容易碰到如下错误
-- A PRIMARY KEY must include all columns in the table’s partitioning function
-- 意思是说分区的字段必须是要包含在主键当中。
-- 所以当我们在使用日期进行分区的时候,就需要将日期字段包含到主键当中去,
-- ALTER TABLE `order` PARTITION BY RANGE (to_days(create_time))
-- (PARTITION p_Apr VALUES LESS THAN (to_days('2015-01-01')),
-- PARTITION p_May VALUES LESS THAN (to_days('2016-11-01')),
-- PARTITION p_Dec VALUES LESS THAN MAXVALUE );
-- 否则会报错（上例未报错是因为info表未设置主键），不过以时间作为主键是很少见的。
-- 【但是如果当数据库的表没有主键的时候,分区语句也能成功。】
-- ————————————————
-- 版权声明：本文为CSDN博主「搬砖男子汉」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
-- 原文链接：https://blog.csdn.net/u013803262/article/details/75108523

2.list分区

 

 不能插入到不存在的分区中。

 3.hash分区

# hash分区
create table kind(
	id int primary key auto_increment,
	name VARCHAR(32)
);
create table good(
	id int, -- primary key auto_increment,当我们在对MySQL的数据表进行分区的时候,很容易碰到如下错误
-- A PRIMARY KEY must include all columns in the table’s partitioning function
-- 意思是说分区的字段必须是要包含在主键当中。
-- 所以当我们在使用日期进行分区的时候,就需要将日期字段包含到主键当中去,
-- ALTER TABLE `order` PARTITION BY RANGE (to_days(create_time))
-- (PARTITION p_Apr VALUES LESS THAN (to_days('2015-01-01')),
-- PARTITION p_May VALUES LESS THAN (to_days('2016-11-01')),
-- PARTITION p_Dec VALUES LESS THAN MAXVALUE );
-- 否则会报错。不过以时间做为主键是比较少见的。
-- 【但是如果当数据库的表没有主键的时候,分区语句也能成功。】
-- ————————————————
-- 版权声明：本文为CSDN博主「搬砖男子汉」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
-- 原文链接：https://blog.csdn.net/u013803262/article/details/75108523
	name VARCHAR(32),
	kindId int
)partition by HASH(kindId) 
partitions 5;
-- [KEY分区和HASH分区相似，但是KEY分区支持除text和BLOB之外的所有数据类型的
-- 分区，而HASH分区只支持数字分区;Hash分区允许用户自定义表达式，但是key分区不支持用户自定义表达式。KEY分区不允许使用用户自定义的表达式进行分
-- 区，KEY分区使用系统提供的HASH函数进行分区。当表中存在主键或者唯一键时，如
-- 果创建key分区时没有指定字段系统默认会首选主键列作为分区字列,如果不存在主键
-- 列会选择非空唯一键列作为分区列,注意唯一列作为分区列唯一列不能为null。
-- 与hash分区不同的是，创建key分区表的时候，可以不用指定分区键，默认会选择使用主键或者唯一键作为分区键。如果没有主键或者唯一键则需要指定分区键。Key分区说白了就是按照主键分区。
-- ————————————————
-- 版权声明：本文为CSDN博主「景哈哈里有个景儿」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
-- 原文链接：https://blog.csdn.net/echo_hello_world/article/details/80693224]
 
# 查看分区
select * from information_schema.PARTITIONS where TABLE_NAME='good';
 
insert into good(id,name,kindId) values(1,'1',1);
insert into good(id,name,kindId) values(2,'2',2);
insert into good(id,name,kindId) values(3,'3',3);
insert into kind(id,name) values(1,'A');
insert into kind(id,name) values(2,'B');
insert into kind(id,name) values(3,'C');
 
select * from good;
select * from good PARTITION(p0);
select * from good PARTITION(p1);
select * from good PARTITION(p2);
select * from good PARTITION(p3);
select * from good PARTITION(p4);
select * from good PARTITION(p5); -- Unknown partition 'p5' in table 'good'

使用场景：good表的某外键字段是kind表的主键；

Hash分区只支持数字分区；

Hash分区允许用户自定义表达式；

 4.key分区（类似hash分区，区别见上面sql注释）

-- [KEY分区和HASH分区相似，但是KEY分区支持除TEXT和BLOB之外的所有数据类型的
-- 分区，而Hash分区只支持数字分区；Hash分区允许用户自定义表达式，但是key分区不支持用户自定义表达式。KEY分区不允许使用用户自定义的表达式进行分
-- 区，KEY分区使用系统提供的HASH函数进行分区。当表中存在主键或者唯一键时，如
-- 果创建key分区时没有指定字段系统默认会首选主键列作为分区字列,如果不存在主键
-- 列会选择非空唯一键列作为分区列,注意唯一列作为分区列唯一列不能为null。
-- 与hash分区不同的是，创建key分区表的时候，可以不用指定分区键，默认会选择使用主键或者唯一键作为分区键。如果没有主键或者唯一键则需要指定分区键。Key分区说白了就是按照主键分区。
-- ————————————————
-- 版权声明：本文为CSDN博主「景哈哈里有个景儿」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
-- 原文链接：https://blog.csdn.net/echo_hello_world/article/details/80693224]

 5.子分区（复合分区）

 子分区的分区名称不能重复。

6.分区管理

分表

1.merge分表(各子表的表结构要和总表保持一致）

缺点：
1.总表（MERGE表）必须使用MRG_MyISAM存储引擎，子表必须使用MyISAM存储引擎，不可避免会受到MyISAM存储引擎的限制，MyISAM不支持事务操作。
2.MERGE表不能使用某些MyISAM特性。例如，子表可创建全文索引，而MERGE表查询不能使用全文索引（MyISAM存储引擎可以直接支持全文索引，而InnoDB存储引擎则需要进行一些设置才能够支持全文索引）
3.一张表对应三个文件，1000个分表就是对应3000个文件，这样检索起来也会变的很慢
4.若使用ALTER TABLE语句修改MERGE表，那么会立即丢失总表和子表的映射关系，并且会将所有子表的数据拷贝至修改后的新总表。
5.子表之间不能保证唯一键约束，只能保证单个子表内部的唯一键约束。
6.子表不支持分区（Partition）。
7.不支持INSERT DELAYED插入延时语句。

使用merge（MRG_MyISAM）存储引擎来实现分表
1.子表必须使用MyISAM存储引擎，每个子表的表结构必须相同。

create table log_2001  
(  
  id int not null auto_increment,
  dt  datetime not null,  
  info varchar(100) not null,  
  primary key(id),
  index (dt)  
) ENGINE = MyISAM;  
create table log_2002 like log_2001;
2.建立merge表，merge表的表结构必须与各个子表结构相同，merge数据表也支持DELETE 、UPDATE、INSERT操作。MERGE数据表的定义里可以包括一个INSERT_METHOD选项，这个选项的可取值是NO、FIRST、LAST，他们的含义依次是禁止插入、插入到第一个子表、插入到最后一个子表。

create table log_merge  
(  
  id int not null auto_increment,
  dt  datetime not null,  
  info varchar(100) not null,  
  primary key(id),
  index (dt)  
) ENGINE = MERGE UNION = (log_2001, log_2002)  
INSERT_METHOD = FIRST;  
3.创建一个新的子表，并让他有同样的表结构，然后修改log_merge数据表把新子表包括进来，注意UNION表必须同属一个DATABASE

create table log_2003 like log_2001;
alter table log_merge  UNION = (log_2001, log_2002,log_2003); 
注意：
1.如果是通过修改.mrg文件的方式来修改MERGE表，那么一定要修改后要通过FLUSH TABLES刷新表缓存，否则修改不会生效。
2.查询结果及顺序与创建Merge表时联合表的顺序有关，例如：
INSERT INTO log_2001(id,dt,info)VALUES(1,'2017-01-01 11:00:00','测试01')
INSERT INTO log_2002(id,dt,info)VALUES(1,'2018-01-01 11:00:00','测试02')
SELECT * FROM log_merge WHERE id=1;
只会得到一条记录(1,'2017-01-01 11:00:00','测试01')，并不是两条记录，也不会是(1,'2018-01-01 11:00:00','测试02')。这是因为id是 primary key，如果在第一个表中查询到某记录，则不会在后面的表中再查此记录。如果id并没有定义唯一性约束，则这个查询会得到两条记录。
————————————————
版权声明：本文为CSDN博主「吾家陈小白」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/cw3225654/article/details/81335550

2.其它分表

3.总结

分区和分表的区别和各自的优缺点 - 百度文库