MySQL分表技术&分区技术

时间：2024-04-06 22:50:27

一、分表技术

1、说明

说明：当一张表数据巨大时，不管如何优化，查询也会相当慢
解决：1-增加查询缓存，2-使用分表技术

2、实现分表的方法

水平分表：手动创建多张表，通过PHP算法判断实现读写
垂直分表：将表字段拆分到其他表中

3、水平分表

水平分表图示：MySQL分表技术&分区技术
原理：PHP算法，用户ID%表个数，根据余数选择对应的数据表。

4、垂直分表

说明：当一个表有很多列，查询慢
解决：将表中字段分为常用字段和不常用字段，分别存入两个表中。

5、MySQL分表的弊端

弊端：水平分表或垂直分表，虽然可以加快查询速度，但却需要手动在应用层写逻辑代码，比较麻烦，增加了代码复杂度。
解决：通过分区可以规避，由MySQL底层实现水平分表，我们在应用层还是写原生SQL语句即可。

二、分区技术

1、分区语法

 1. create table 表名名(
 2. List item
 3. )engine=存储引擎 charset=编号
 4. partition by  算法名(字段)  partitions 数字;

2、MySQL四种分区算法

求余（主键字段）：key算法、hash算法
条件：list数据必须在指定集合中，range数据在执行范围中
①key分区

 
1  #创建数据库db
2  create database db;
3  #选择数据库
4  use db;
5  #创建表
6  create table articles(
7    id int unsigned primary key auto_increment,
8    title varchar(50) not null,
9    content text
10 ) engine = myisam charset = utf8
11 partition by key(id) partitions 3;
12 #通过key算法求余id字段，分3个区
13
14 #插入测试数据
15 insert into articles values (null,'aaa','bbb');
16
17 #刷新查看效果
18 flush table articles;

MySQL分表技术&分区技术

1  #查询数据（原生SQL语句查询）
2  select * from articles;

②hash 分区

1  #创建表
2  create table articles(
3     id int unsigned primary key auto_increment,
4     title varchar(50) not null,
5     content text
6  ) engine = myisam charset = utf8
7  partition by hash (id) partitions 4;
8  #插入数据
9  insert into articles values (null,'aaa','bb');
10 #查询数据
11 select * from articles;

③list分区

1 #创建表
2 create table articles(
3  id int unsigned auto_increment,
4  title varchar(50) not null,
5  content text,
6 cid int unsigned,
7 primary key (id,cid)
8 ) engine = myisam charset = utf8
9 partition by list(cid) (
10 partition c1 values in (1,3),
11 partition c2 values in (2,4),
12 partition c3 values in (5,6,7)
13 );
14 #插入数据
15 insert into articles values (null,'aaa','bb', 1);
16 insert into articles values (null,'aaa','bb', 10); //报错，不存在10

④range分区

1 #创建数据表并实现range分区
2 create table user(
3   id int not null auto_increment,
4   name varchar(40) not null,
5  birthday date not null default '0000-00-00',
6  primary key(id,birthday)
7 ) engine = myisam default charset = utf8
8 partition by range(year(birthday)) (
9   partition 70hou values less than (1980),
10  partition 80hou values less than (1990),
11  partition 90hou values less than (2000),
12  partition 00hou values less than (2010)
13 );
14 #插入数据
15 insert into user values (null,'c','1990-01-01');
16 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 分区名;
添加分区：

1 alter table tp_user add partition(
2  partition 10hou values less than (maxvalue)
3 );
4
5 alter table tp_goods add partition(
6  partition c4 values in (8,9,10)
7 );

相关文章