003MySQL性能调优007分表和分区

最新推荐文章于 2025-05-30 22:29:21 发布

执笔未来

最新推荐文章于 2025-05-30 22:29:21 发布

阅读量253

点赞数

分类专栏： MySQL学习笔记文章标签： mysql

本文链接：https://blog.youkuaiyun.com/zhibiweilai/article/details/122192472

版权

MySQL学习笔记专栏收录该内容

33 篇文章

订阅专栏

1 简介

1.1 背景

对于InnoDB存储引擎来说，一般情况下一个表对应一个FRM文件，这个文件保存了表结构和索引。

当数据量较大时（一般千万条记录级别以上），数据库的性能就会开始下降，这时可以考虑对数据库进行分区或分表。

1.2 分表

分表是把数据量很大的一张表按照一定的规则分解成数据量较小的多张表，每张表都对应一个FRM文件。

使用时需要修改SQL语句，访问不同的表得到对应的数据。

分表按照方式分为垂直切分和水平切分。

1.3 分区

分区和分表类似，也是按照规则分解表。不同的是分区不会将一张表分解成多张表，而是将一张表里的数据分开存放，一张表对应多个FRM文件。

此外，对于客户端来说，数据库表的区分对于客户端是透明的，使用的时候也不需要修改SQL语句。

2 分表

2.1 垂直分表

垂直分表指的是将包含很多列的表拆分成多个子表，每个子表包含原表的几列，每个子表的条数和原表一致。

垂直分表的原则：

将常用的字段和不常用的字段拆分。

将大字段单独拆分。

2.2 水平分表

水平分表指的是将原表的条数拆分，每个子表的表结构和原表一致。

水平分表的方式：

1）按时间拆分。

如果业务系统对时效性要求较高，比如新闻发布系统的文章表，可以把数据库设计成时间结构，按时间水平拆分。

2）按模块拆分

如果原表包含多个模块，或者包含多个分类，可以按照模块进行拆分。

3）按哈希拆分

哈希结构常用语博客之类的业务场景，用户数量多，并且每个用户发布时间和次数不定，但总量大。

对于这种结构的原表，可以利用词条的MD5编码，取前几位进行拆分。

3 分区

3.1 说明

目前MySQL支持以下几种类型的分区，RANGE分区，LIST分区，HASH分区，KEY分区。实战十有八九都是用RANGE分区。

如果表存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。

3.2 通用操作

查看指定表的分区信息：

select
  partition_name part,
  partition_expression expr,
  partition_description descr,
  table_rows
from information_schema.partitions
where table_schema = schema() and table_name='test';

查看建表语句获取建表时的分区设置：

show create table test;

查看表状态判断是不是分区表：

show table status like 'test';

重建分区，可用于整理分区碎片：

alter table test rebuild partition part1, part2, part3;

优化分区，可以回收空间和碎片整理：

alter table test optimize partition part1, part2, part3;

分析分区，读取并保存分区的键分布，可以在对分区做了修改之后使用：

alter table test analyze partition part1, part2, part3;

修补分区，修补被破坏的分区：

alter table test repair partition part1, part2, part3;

删除分区，同时也会将分区内的数据删除：

alter table test drop partition part4, part3;

删除分区数据，保留分区：

alter table test truncate partition part4, part3;

3.3 RANGE分区

RANGE分区是实战最常用的一种分区类型，由分区键分隔的连续区间组成，这些区间要连续且不能相互重叠。

当插入的数据不在任何一个分区的时候，会抛异常。

分区键必须有主键索引或者唯一索引，并且分区键必须是Int类型，或者通过表达式返回Int类型，可以为Null。

分区键要求是整型值，但也可以是表达式，但要求表达式必须返回一个整型值。

建表时设置分区：

create table test (
  id int(11) not null comment '编号',
  name varchar(50) default null comment '姓名',
  address varchar(50) default null comment '地址',
  status int(2) default null comment '状态',
  createtime date default null comment '创建时间',
  updatetime date default null comment '修改时间',
  primary key (id)
) partition by range (id) (
  partition part0 values less than (50),
  partition part1 values less than (100),
  partition part2 values less than (150),
  partition part3 values less than maxvalue
);

修改表时设置分区：

alter table test partition by range (id) (
  partition part0 values less than (50),
  partition part1 values less than (100),
  partition part2 values less than (150),
  partition part3 values less than maxvalue
);

添加分区，要求只能添加大于分区键的分区：

alter table test add partition (partition part3 values less than (200));

分解分区：

alter table test reorganize partition part3 into (
  partition part3 values less than (200),
  partition part4 values less than maxvalue
);

合并分区：

alter table test reorganize partition part4, part3 into (
  partition part3 values less than maxvalue
);

3.4 LIST分区

类似于按RANGE分区，区别在于LIST分区是由多个散列值集合组成，这些集合要求里面的值不能重复。

当插入的数据不在任何一个分区的时候，会抛异常。

散列值必须有主键索引或者唯一索引，并且散列值必须是Int类型，或者通过表达式返回Int类型，可以为Null。

散列值要求是整型值，但也可以是表达式，但要求表达式必须返回一个整型值。

建表时设置分区：

create table test (
  id int(11) not null comment '编号',
  name varchar(50) default null comment '姓名',
  address varchar(50) default null comment '地址',
  status int(2) default null comment '状态',
  createtime date default null comment '创建时间',
  updatetime date default null comment '修改时间',
  primary key (id, status)
) partition by list (status) (
  partition part0 values in (0, 1, 5),
  partition part1 values in (2),
  partition part2 values in (3, 4)
);

修改表时设置分区：

alter table test partition by list (status) (
  partition part0 values in (0, 1, 5),
  partition part1 values in (2),
  partition part2 values in (3, 4)
);

添加分区，要求只能添加未被定义的分区：

alter table test add partition (partition part4 values in (6, 7));

分解分区：

alter table test reorganize partition part2 into (
  partition part2 values in (3),
  partition part3 values in (4)
);

合并分区：

alter table test reorganize partition part3, part2 into (
  partition part3 values in (3, 4)
);

3.5 HASH分区

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。

和RANGE分区，LIST分区不同的是，HASH分区无需定义分区的条件，只需要指明分区数即可。

设置分区时只需要指定分区数量，如果没有指定分区数量，那么分区的数量将默认为1。

散列值必须有主键索引或者唯一索引，并且散列值必须是Int类型，或者通过表达式返回Int类型，可以为Null。

散列值要求是整型值，但也可以是表达式，但要求表达式必须返回一个整型值。

建表时设置分区：

create table test (
  id int(11) not null comment '编号',
  name varchar(50) default null comment '姓名',
  address varchar(50) default null comment '地址',
  status int(2) default null comment '状态',
  createtime date default null comment '创建时间',
  updatetime date default null comment '修改时间',
  primary key (id, status)
) partition by hash (id) partitions 3;

修改表时设置分区：

alter table test partition by hash (id) partitions 3;

添加分区，在原有分区的基础上添加分区：

alter table test add partition partitions 2;

减少分区，在原有分区的基础上减少分区：

alter table test coalesce partition 2;

3.6 KEY分区

KEY分区其实跟HASH分区差不多，同样用来确保数据在预先确定数目的分区中平均分布，而且KEY分区也无需定义分区的条件，只需要指明分区数即可。

和HASH分区不同的是，KEY分区允许多列但不允许使用表达式，而HASH分区只允许一列但允许使用表达式。

如果在有主键或者唯一键的情况下，KEY分区中分区键可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。

KEY分区和HASH分区的算法不一样，HASH分区使用用户定义的函数进行分区，KEY分区使用数据库提供的函数进行分区。

建表时设置分区：

create table test (
  id int(11) not null comment '编号',
  name varchar(50) default null comment '姓名',
  address varchar(50) default null comment '地址',
  status int(2) default null comment '状态',
  createtime date default null comment '创建时间',
  updatetime date default null comment '修改时间',
  primary key (id)
) partition by key (id) partitions 3;

修改表时设置分区：

alter table test partition by key (id) partitions 3;

添加分区，在原有分区的基础上添加分区：

alter table test add partition partitions 2;

减少分区，在原有分区的基础上减少分区：

alter table test coalesce partition 2;

3.7 子分区

子分区是针对RANGE类型和LIST类型的分区表中每个分区的再次分割，再次分割可以是HASH类型和KEY类型。

需要注意的是，每个分区必须有相同数量的子分区，并且如果在一个分区表上的任何分区上明确定义了任何子分区，那么就必须定义所有的子分区。

将每个RANGE分区细分为两个HASH分区：

create table test (
  id int(11) not null comment '编号',
  name varchar(50) default null comment '姓名',
  address varchar(50) default null comment '地址',
  status int(2) default null comment '状态',
  createtime date default null comment '创建时间',
  updatetime date default null comment '修改时间',
  primary key (id)
) partition by range (id) subpartition by hash (id) subpartitions 2 (
  partition part0 values less than (50),
  partition part1 values less than (100),
  partition part2 values less than (150),
  partition part3 values less than maxvalue
);

明确定义子分区：

create table test (
  id int(11) not null comment '编号',
  name varchar(50) default null comment '姓名',
  address varchar(50) default null comment '地址',
  status int(2) default null comment '状态',
  createtime date default null comment '创建时间',
  updatetime date default null comment '修改时间',
  primary key (id)
) partition by range (id) subpartition by hash (id) (
  partition part0 values less than (50) (
        subpartition part0sub0,
        subpartition part0sub1
    ),
  partition part1 values less than (100) (
        subpartition part1sub0,
        subpartition part1sub1
    ),
  partition part2 values less than (150) (
        subpartition part2sub0,
        subpartition part2sub1
    ),
  partition part3 values less than maxvalue (
        subpartition part3sub0,
        subpartition part3sub1
    )
);