本文深入探讨MySQL的分表和分区技术，包括其实现方式、数据处理、性能提升及难易度对比，同时提供了实际操作案例，帮助读者理解如何有效运用这两种方法优化数据库性能。
mysql数据库文件拓展名
格式文件.frm 描述表的结构（列、列类型、索引，等等）
数据文件.ISD(ISAM) 或 .MYD (MyISAM) 包含表的数据─即它的行
索引文件.ISD(ISAM) 或 .MYI (MyISAM) 包含数据文件中任何索引的索引树。无论该表有无索引，索引文件都存在
分表
什么是分表？
- 分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三个文件，MYD数据 文件，.MYI索引文件，.frm表结构文件。这些表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的 时候根据事先定义好的规则得到对应的表名，然后去操作它。 将单个数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，根据一定的算法（如用hash的方式，也 可以用求余（取模）的方式），让用户访问不同的表，这样数据分散到多个数据表中，减少了单个数据表的访 问压力。提升了数据库访问性能。分表的目的就在于此，减小数据库的负担，缩短查询时间。
Mysql分表分为垂直切分和水平切分
- 垂直切分是指数据表列的拆分，把一张列比较多的表拆分为多张表 通常我们按以下原则进行垂直拆分: 把不常 用的字段单独放在一张表; 把text，blob（binary large object，二进制大对象）等大字段拆分出来放在附表中; 经常组合查询的列放在一张表中; 垂直拆分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用 join关键起来即可。
- 水平拆分是指数据表行的拆分，把一张的表的数据拆成多张表来存放。 水平拆分原则 通常情况下，我们使用 hash、取模等方式来进行表的拆分 比如一张有400W的用户表users，为提高其查询效率我们把其分成4张表 users1，users2，users3，users4 通过用ID取模的方法把数据分散到四张表内Id%4= [0,1,2,3] 然后查询,更新,删 除也是通过取模的方法来查询 部分业务逻辑也可以通过地区，年份等字段来进行归档拆分; 进行拆分后的表， 这时我们就要约束用户查询行为。比如我们是按年来进行拆分的,这个时候在页面设计上就约束用户必须要先选 择年,然后才能进行查询。
分表的几种方式：
mysql集群
- 它并不是分表，但起到了和分表相同的作用。集群可分担数据库的操作次数，将任务分担到多台数据库上。集 群可以读写分离，减少读写压力。从而提升数据库性能。
预先估计会出现大数据量并且访问频繁的表，将其分为若干个表
- 根据一定的算法（如用hash的方式，也可以用求余（取模）的方式）让用户访问不同的表。 例如论坛里面发表 帖子的表，时间长了这张表肯定很大，几十万，几百万都有可能。聊天室里面信息表，几十个人在一起一聊一 个晚上，时间长了，这张表的数据肯定很大。像这样的情况很多。所以这种能预估出来的大数据量表，我们就 事先分出个N个表，这个N是多少，根据实际情况而定。以聊天信息表为例：我们事先建100个这样的表， message_00,message_01,message_02…message_98,message_99.然后根据用户的ID来判断这个用户的聊天 信息放到哪张表里面，可以用hash的方式来获得，也可以用求余的方式来获得，方法很多。 或者可以设计每张 表容纳的数据量是N条，那么如何判断某张表的数据是否容量已满呢？可以在程序段对于要新增数据的表，在 插入前先做统计表记录数量的操作
利用merge存储引擎来实现分表
- 如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码，因为程序里面的sql语句已经写好了，用 merge存储引擎来实现分表, 这种方法比较适合。 merge分表，分为主表和子表，主表类似于一个壳子，逻辑上 封装了子表，实际上数据都是存储在子表中的。 我们可以通过主表插入和查询数据，如果清楚分表规律，也可 以直接操作子表。
分表案例
创建表
drop database IF EXISTS testdb;
create database testdb;
use testdb;
create table member(
id bigint auto_increment primary key,
name varchar(20),
sex tinyint not null default '0'
)engine=myisam default charset=utf8 auto_increment=1;
insert into member(name,sex) values('tom1',1);
insert into member(name,sex) select name,sex from member;
创建两个分表
use testdb;
DROP table IF EXISTS tb_member1;
create table tb_member1(
id bigint primary key ,
name varchar(20),
sex tinyint not null default '0'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
DROP table IF EXISTS tb_member2;
create table tb_member2(
id bigint primary key,
name varchar(20),
sex tinyint not null default '0'
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
创建主表tb_member
DROP table IF EXISTS tb_member;
create table tb_member(
id bigint primary key ,
name varchar(20),
sex tinyint not null default '0'
) ENGINE=MERGE UNION=(tb_member1,tb_member2) INSERT_METHOD=LAST CHARSET=utf8 ;
insert into member(name,sex) values('tom2',2);
insert into member(name,sex) select name,sex from member;
insert into member(name,sex) values('tom3',3);
insert into member(name,sex) select name,sex from member;
insert into member(name,sex) values('tom4',4);
insert into member(name,sex) select name,sex from member;
按照已经有行插入
insert into tb_member1(id,name,sex) select id,name,sex from member where
id%2=0;
insert into tb_member2(id,name,sex) select id,name,sex from member where
id%2=1;
检索
select * from tb_member1;
select * from tb_member2;
select * from tb_member;
select * from tb_member where id=3;
注意：总表只是一个外壳，存取数据发生在一个一个的子表里面。
注意：每个子表都有自已独立的相关表文 件，而主表只是一个壳，并没有完整的相关表文件
分区
什么是分区
- 分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，分区后，表还是一张表，但数据散列到多个位置了。app读写的时候操作的还是表名 字，db自动去组织分区的数据。
分区主要有两种形式：
- 水平分区（Horizontal Partitioning）这种形式分区是对表的行进行分区，所有在表中定义的列在每个数据集中 都能找到，所以表的特性依然得以保持。
- 举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记 录。
- 垂直分区（Vertical Partitioning）这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些 特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。
- 举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经 常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。
查看是否支持分区
mysql> show plugins;
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
或者
mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+---------------+
|Variable_name | Value |
+-----------------------+---------------+
| have_partition_engine | YES |
+-----------------------+------------------+
创建range分区表
use testdb;
create table if not exists user (
id int not null auto_increment,
name varchar(30) not null default '',
sex int(1) not null default '0',
primary key(id)
)default charset=utf8 auto_increment=1
partition by range(id) (
partition p0 values less than (3),
partition p1 values less than (6),
partition p2 values less than (9),
partition p3 values less than (12),
partition p4 values less than maxvalue
);
insert into testdb.user(name,sex)values ('tom1','0');
insert into testdb.user(name,sex)values ('tom2','1');
insert into testdb.user(name,sex)values ('tom3','1');
insert into testdb.user(name,sex)values ('tom4','0');
insert into testdb.user(name,sex)values ('tom5','0');
insert into testdb.user(name,sex)values ('tom6','1');
insert into testdb.user(name,sex)values ('tom7','1');
insert into testdb.user(name,sex)values ('tom8','1');
insert into testdb.user(name,sex)values ('tom9','1');
insert into testdb.user(name,sex)values ('tom10','1');
insert into testdb.user(name,sex)values ('tom11','1');
insert into testdb.user(name,sex)values ('tom12','1');
insert into testdb.user(name,sex)values ('tom13','1');
insert into testdb.user(name,sex)values ('tom14','1');
从某个分区中查询数据
select * from testdb.user partition(p0);
select * from testdb.user partition(p1);
select * from testdb.user partition(p2);
select * from testdb.user partition(p3);
select * from testdb.user partition(p4);
删除分区 当删除了一个分区，也同时删除了该分区中所有的数据。
alter table testdb.user drop partition p4;
新增分区
alter table testdb.user add partition(
partition m5 values less than (13)
);
select * from testdb.user partition(m5);
查看分区
select PARTITION_NAME,PARTITION_METHOD,PARTITION_DESCRIPTION,TABLE_ROWS from information_schema. `PARTITIONS`
where table_schema='testdb'
and table_name='user';
分区合并
alter table testdb.user
REORGANIZE PARTITION p1,p2,p3 into(
PARTITION t1 VALUES less than (8),
PARTITION t2 VALUES less than (12),
)
分区时，将不同分区放到不同存储位置
create table t_user(
id int not null auto_increment,
name varchar(30) not null default '',
primary key(id)
) engine=innodb default charset=utf8 auto_increment=1
partition by range(id)(
partition p1 values less than (3) data directory 'E:\1',
partition p2 values less than (6) data directory 'E:\2',
partition p3 values less than (9) data directory 'E:\3');
然后查看E:\1文件夹
给t_user表中插入内容
hash分区
create table t_hash( a int(11), b datetime) partition by hash(year(b))
partitions 4;
insert into t_hash values(1,'2020-06-23');
select PARTITION_NAME,PARTITION_METHOD,PARTITION_DESCRIPTION,TABLE_ROWS
from information_schema. `PARTITIONS`
where table_schema='testdb'
and table_name='t_hash';
mysql分表和分区有什么区别
- 实现方式上
- mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完整的一张表，都对 应三个文件，一个.MYD数据文件，.MYI索引文件，.frm表结构文件。
- 分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据的区块变多了
- 数据处理上
- 分表后，数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表 里面。
- 分区呢，不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表呢，还是一 张表，数据处理还是由自己来完成。
- 提高性能上
- 分表后，单表的并发能力提高了，磁盘I/O性能也提高了。并发能力为什么提高了呢，因 为查寻一次所花的时间变短了，如果出现高并发的话，总表可以根据不同的查询，将并发压力分到不同 的小表里面。
- mysql提出了分区的概念，主要是想突破磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。 在这一点上，分区和分表的测重点不同，分表重点是存取数据时，如何提高mysql并发能力上；而分区 呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。
- 实现的难易度上
- 分表的方法有很多，用merge来分表，是最简单的一种方式。这种方式跟分区难易度差不多，并且对 程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。
- 分区实现是比较简单的，建立分区表，根建平常的表没什么区别，并且对开代码端来说是透明的。
mysql分表和分区有什么联系
- 都能提高mysql的性高，在高并发状态下都有一个良好的表现。
- 分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表 和分区结合的方式，访问量不大，但是表数据很多的表，我们可以采取分区的方式等。
- 分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一 些，但也要创建子表和配置子表间的union关系。
的。如果是用其他分表方式就比分区麻烦了。 - 分区实现是比较简单的，建立分区表，根建平常的表没什么区别，并且对开代码端来说是透明的。
mysql分表和分区有什么联系
- 都能提高mysql的性高，在高并发状态下都有一个良好的表现。
- 分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表 和分区结合的方式，访问量不大，但是表数据很多的表，我们可以采取分区的方式等。
- 分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一 些，但也要创建子表和配置子表间的union关系。
- 表分区相对于分表，操作方便，不需要创建子表。
扫一扫
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
