MySQL分表技术&分区技术

MySQL分表技术&分区技术
一、分表技术
1、说明
说明：当一张表数据巨大时，不管如何优化，查询也会相当慢
解决：1-增加查询缓存，2-使用分表技术
2、实现分表的方法
水平分表：手动创建多张表，通过PHP算法判断实现读写
垂直分表：将表字段拆分到其他表中
3、水平分表
水平分表图示：

原理：PHP算法，用户ID%表个数，根据余数选择对应的数据表。
4、垂直分表
说明：当一个表有很多列，查询慢
解决：将表中字段分为常用字段和不常用字段，分别存入两个表中。
5、MySQL分表的弊端
弊端：水平分表或垂直分表，虽然可以加快查询速度，但却需要手动在应用层写逻辑代码，比较麻烦，增加了代码复杂度。
解决：通过分区可以规避，由MySQL底层实现水平分表，我们在应用层还是写原生SQL语句即可。
二、分区技术
1、分区语法
create table 表名名(

)engine=存储引擎 charset=编号
partition by 算法名(字段) partitions 数字;
2、MySQL四种分区算法
求余（主键字段）：key算法、hash算法
条件：list数据必须在指定集合中，range数据在执行范围中
①key分区
#创建数据库db
create database db;
#选择数据库
use db;
#创建表
create table articles(
id int unsigned primary key auto_increment,
title varchar(50) not null,
content text
) engine = myisam charset = utf8
partition by key(id) partitions 3;
#通过key算法求余id字段，分3个区

#插入测试数据
insert into articles values (null,‘aaa’,‘bbb’);

#刷新查看效果
flush table articles;

#查询数据（原生SQL语句查询）
select * from articles;
②hash 分区
#创建表
create table articles(
id int unsigned primary key auto_increment,
title varchar(50) not null,
content text
) engine = myisam charset = utf8
partition by hash (id) partitions 4;
#插入数据
insert into articles values (null,‘aaa’,‘bb’);
#查询数据
select * from articles;
③list分区
#创建表
create table articles(
id int unsigned auto_increment,
title varchar(50) not null,
content text,
cid int unsigned,
primary key (id,cid)
) engine = myisam charset = utf8
partition by list(cid) (
partition c1 values in (1,3),
partition c2 values in (2,4),
partition c3 values in (5,6,7)
);
#插入数据
insert into articles values (null,‘aaa’,‘bb’, 1);
insert into articles values (null,‘aaa’,‘bb’, 10); //报错，不存在10
④range分区
#创建数据表并实现range分区
create table user(
id int not null auto_increment,
name varchar(40) not null,
birthday date not null default ‘0000-00-00’,
primary key(id,birthday)
) engine = myisam default charset = utf8
partition by range(year(birthday)) (
partition 70hou values less than (1980),
partition 80hou values less than (1990),
partition 90hou values less than (2000),
partition 00hou values less than (2010)
);
#插入数据
insert into user values (null,‘c’,‘1990-01-01’);
insert into user values (null,‘d’,‘2011-01-01’); //报错，该分区不存在
3、分区管理
取余管理：增加分区-不影响原数据，删除分区-不影响原数据
条件管理：增加分区-不影响原数据，删除分区-数据丢失
①取余管理（key，hash）
增加分区数量：alter table 表名 add partition partitions 个数
减少分区数量：alter table 表名 coalesce partition 个数
②条件管理（list，range）
删除分区：alter table 表名 drop partition 分区名;
添加分区：
alter table tp_user add partition(
partition 10hou values less than (maxvalue)
);

alter table tp_goods add partition(
partition c4 values in (8,9,10)
);