MySQL学习笔记--分区分表

mysql分区分表

概述
分区
分表

概述
数据库的数据量达到一定程度之后，为避免带来系统性能上得瓶颈。
采用的手段是分区，分片，分库，分表。

分表——把一张表分成多个小表

分区——把一张表的数据分成多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上

分区
MySQL数据库中的数据是以文件的形势存在磁盘上得，默认放在/mysql/data下面
一张表主要对应着三个文件
frm存放表结构
MYD存放表数据
MYI存表索引

分区的优点
分区可以分在多个磁盘，存储更大一点
根据查找条件，也就是where后面的条件，查找制查找相应的分区不用全部查找了
进行大数据搜索时可以进行并行处理。
跨多个磁盘来分散数据查询，来获得更大的查询吞吐量

分区的方式
横向分区
取出一条数据的时候，这条数据包含了表结构中的所有字段，也就是说横向分区，并没有改变表的结构

纵向分区
在设计用户表的时候，开始的时候没有考虑好，而把个人的所有信息都放到了一张表里面去，这样这个表里就会有比较大的字段，如个人简介，而这些简介，也不会有好多人去看，所以等到有人要看得时候，再去查找，分表的时候，可以把这样的大字段，分开来。

分区实现
1.range

create table info(
    id int primary key auto_increment,
    title varchar(32),
    content text,
    create_time date
)partition by range(year(create_time))(
    partition p2019 values less than (2020),
    partition p2020 values less than (2021),
    partition p2021 values less than maxvalue
    );

查看分区

select * from information_schema.PARTITIONS where TABLE_NAME = 'info';

insert into info (id,title,content,create_time) VALUES (1,'你有一条信息'，'xxx','2019-11-25');
insert into info (id,title,content,create_time) VALUES (1,'hello'，'xxx','2020-11-25');
insert into info (id,title,content,create_time) VALUES (1,'你好'，'xxx','2021-11-25');
insert into info (id,title,content,create_time) VALUES (1,'再见'，'xxx','2100-11-25');

#查询
select * from info;

#按分区查询
select * from info partition(p2019);
select * from info partition(p2020);
select * from info partition(p2021);

select * from info partition(p2110);#报错

list分区
按预定义的值来区分

create table number (
    num int,
    name varchar(32)
)partition by list (num)(
    partition p0 values in (1,3,5,7,9),
    partition p1 values in (2,4,6,8,0)
    );

#查看分区
select * from information_schema.PARTITIONS where TABLE_NAME = 'number';

#插入数据
insert into number (num,name) VALUES (1,'1');
insert into number (num,name) VALUES (2,'2');
insert into number (num,name) VALUES (3,'3');

insert into number (num,name) VALUES (10,'10');

#查询
select * from number;

#按分区查询
select * from number partition(p0);
select * from number partition(p1);
select * from number partition(p2);

hash分区
用类别建立分区

#分类表
create table kind(
    id int primary key auto_increment,
    name carchar(32)
);
#商品表
create table goods (
    id int,
    name varchar(32)
    kind int
)partition by hash (kind)
partition 5;

#查看分区
select * from information_schema.PATITIONS where TABLE_NAME = 'number';

#key 分区
create table key(
    id int,
    name varchar(32)
)partition by linear key (id)
partition 3;

#查看分区
select * from information_schema.PARTITIONS where TABLE_NAME = 'key_table';

子分区

create table sub_part(
    id int,
    content varchar(32),
    create_time date
)partition by range (year(create_time))
subpartition by hash (to_days(create_time))(
    partition p0  values less than (2019)(subpartition s0,subpartition s1,subpartition s3),
    partition p1  values less than (2020)(subpartition s4,subpartition s5,subpartition s6),
    partition p2  values less than (2021)(subpartition s7,subpartition s8,subpartition s9)
    );

分区管理

#删除
alter table info drop partition p2019

#新增
alter table info add partition (partittion p2022 values less then maxvalue);

分表
利用merge存储引擎来实现分表
merge分表，分为主表和子表，主表类似于一个壳子，逻辑上封装了子表，实际上数据都是存储在子表中的。

create table worker1(
    id int primary key auto_increment,
    name varchar(32)
    gender varchar(4)
)engine=MyISAM;

create table worker2(
    id int primary key auto_increment,
    name varchar(32)
    gender varchar(4)
)engine=MyISAM;

insert into worker1(name,gender) VALUES ('JACK','男')；
insert into worker2(name,gender) VALUES ('John','男')；

create table worker(
    id int primary key auto_increment,
    name varchar(32),
    gender varchar(4)
)engine=merge union = (worker1,worker2) insert_method=last auto_increment=1;

insert into worker (name,gender) VALUES ('haha','男')；

分区，分表对比——数据处理逻辑
分表
1，数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表里,
2，分表重点是存去数据时，如何提高Mysql并发能力上
3，用merge来分表，是最简单的一种方式。如果是用其他分表方式就比分区麻烦了

分区
1，不存在分表的概念，分区只不过是把存放数据的文件分成了许多小块，分区后的表，还是一张表。数据处理还是由自己来完成的
2，如何突破磁盘的读写能力，从而达到提高MySQL性能的目的
3，实现是比较简单的，建立分区表，跟建平常的表没什么区别，并且对于代码段来说是透明的。

如何突破磁盘的读写能力，从而达到提高MySQL性能的目的