分类导航

程序笔记发布时间：2022-07-15 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了mysql表分区的使用和底层原理，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

什么是分区表

@H_242_7@mySQL从5.1版本开始支持分区功能࿰c; 分区是将一个表的数据按照某种方式࿰c;比如按照时间上的月份࿰c;分成多个较小的࿰c;更容易管理的部分࿰c;但是逻辑上仍是一个表。

还没出现分区表的时候࿰c;所有的数据都是存放在一个文件里面的࿰c;如果数据量太大࿰c;查询数据时总是避免不了需要大量io操作；使用分区表后࿰c;每个分区存放不同的数据。这样不但可以减少io。还可以加快数据的访问；

为了保证MySQL的性能࿰c;我们都建议mysql单表不要太大࿰c;建议是：单表小于2G࿰c;记录数小于1千万࿰c;十库百表。如果但行记录数非常小࿰c;那么记录数可以再偏大些࿰c;反之࿰c;可能记录数到百万级别就开始变慢了。

那么࿰c;业务量在增长࿰c;数据到瓶颈了怎么办呢࿰c;除了使用分布式数据库࿰c;我们也可以自行分库分表࿰c;或者利用mysql的分区功能实现。分区表的出现是为了分而治之的概念࿰c;分区表的用处非常大࿰c;只是现在还有很多人都不知道；将一个表设置为分区表后࿰c;会在数据文件.idb的文件名加上#号࿰c;代表这是一个分区表；

分区表应用场景

@H_618_19@

表非常大以至于无法全部放在内存中࿰c;或者只在表的最后部分有热点数据࿰c;其他都是历史数据

分区表的数据更容易维护࿰c;࿰c;能批量删除大量数据

对一个独立分区进行优化、检查、修复等操作

分区表的数据可以分布在不同的设备上࿰c;从未高效的利用多个硬件设备

可以备份和恢复独立的分区；

分区表的限制

@H_618_19@

一个表最多能有1024个分区࿰c;在5.7版本及以上可以有8196个分区

在早期mysql中࿰c;分区表达式必须是整数或者整返回整数的表达式࿰c;在mysql5.5中࿰c;某些场景可以直接使用列来进行分区

分区表无法使用外检约束

最好不要去修改分区列

如果分区字段中有主键或者唯一索引的列࿰c;那么所有主键列和唯一索引列都必须包含进来；就像这样：

-- 创建分区必须包含所有主键
create table user_11(
  id bigint(20) not null ,
  name varchar(20) ,
  age int(3),
	PRIMary KEY (`id`,`age`)
)
-- 创建分区
partition by range columns(id,age)(
  partition p00 values less than(6,30), -- 小于6的值在P0分区
  partition p11 values less than(11,40), -- 小于11的值在p1分区
  partition p22 values less than(16,50), -- 小于16的值在p2分区
  partition p33 values less than (9999,9999) -- 大于21的值在p3分区࿰c;或者用一个更大的值
);

-- 创建分区必须包含所有唯一键
create table user_22(
  id bigint(20)  not null,
  name varchar(20) ,
  age int(3) not null ,
	unique key only_one_1(age,id )
)
-- 创建分区
partition by range columns(id,age)(
  partition p000 values less than(6,30), -- 小于6的值在P0分区
  partition p111 values less than(11,40), -- 小于11的值在p1分区
  partition p222 values less than(16,50), -- 小于16的值在p2分区
  partition p333 values less than (9999,9999) -- 大于21的值在p3分区࿰c;或者用一个更大的值
);

分区类型

@H_618_19@

范围分区

列表分区

列分区

hash分区

秘钥分区

子分区

分区表的使用

1、范围分区

下面示例中将年龄进行分区࿰c;

create table employees(
id bigint(20) not null,
age int(3) not null,
name varchar(20)
)
-- 创建分区
partition by range (age)(
  partition p0 values less than(6), -- 小于6的值在P0分区
  partition p1 values less than(11), -- 小于11的值在p1分区
  partition p2 values less than(16), -- 小于16的值在p2分区
  partition p3 values less than(21) -- 小于21的值在p3分区
);

创建好之后࿰c;就可以看到在数据文件夹中的分区文件了

[root@VM_0_5_centos test]# pwd
/var/lib/@H_419_48@mysql/test
[root@VM_0_5_centos test]# ll
总用量 @R_673_11263@1504
-rw-rw---- 1 mysql mysql         61 10月 31 2018 db.opt
-rw-rw---- 1 mysql mysql       8614 8月   1 21:30 employees.frm
-rw-rw---- 1 mysql mysql         32 8月   1 21:30 employees.par
-rw-rw---- 1 mysql mysql      98304 8月   1 21:30 employees#P#p0.ibd
-rw-rw---- 1 mysql mysql      98304 8月   1 21:30 employees#P#p1.ibd
-rw-rw---- 1 mysql mysql      98304 8月   1 21:30 employees#P#p2.ibd
-rw-rw---- 1 mysql mysql      98304 8月   1 21:30 employees#P#p3.ibd

因为age字段最大只能插入21以下的数字࿰c;如果插入21的数字则会报错࿰c;

@H_768_14@mysql> insert employees (id,name,age) values(1,'yexindong',21);
ERROR 1526 (HY000): Table has no partition for value 21

所以࿰c;为了解决这个问题࿰c;在建表的时候可以这么干࿰c;将最大的值使用@H_768_14@maxvalue࿰c;据说@H_768_14@maxvalue的值为28个9࿰c;也就是9999999999999999999999999999

create table employees(
id bigint(20) not null,
age int(3) not null,
name varchar(20)
)
-- 创建分区
partition by range (age)(
  partition p0 values less than(6), -- 小于6的值在P0分区
  partition p1 values less than(11), -- 小于11的值在p1分区
  partition p2 values less than(16), -- 小于16的值在p2分区
  partition p3 values less than maxvalue -- 大于16的值在p3分区࿰c;或者用一个更大的值
);

时间范围分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991), -- 1991年之前的数据在P0分区
    PARTITION p1 VALUES LESS THAN (1996),-- 1996年之前的数据在P1分区
    PARTITION p2 VALUES LESS THAN (2001),-- 2001年之前的数据在P2分区
    PARTITION p3 VALUES LESS THAN MAXVALUE -- 2001年制后的数据在P3分区
);

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE columNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

2、列表分区（list分区）

列表分区和范围分区最大的区别就是列表是等值的࿰c;而范围分区是在某个范围内的；

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17), -- 3,5,6,9,17的值放在pNorth分区
    PARTITION pEast VALUES IN (1,2,10,11,19,20),-- 1,2,10,11,19,20的值放在pEast分区
    PARTITION pWest VALUES IN (4,12,13,14,18),-- 4,12,13,14,18的值放在pWest分区
    PARTITION pCentral VALUES IN (7,8,15,16)-- 7,8,15,16的值放在pCentral分区
);

3、列分区

列分区是范围分区和列表分区的变体࿰c;也就是说列分区就是由范围分区和列表分区封装得来的࿰c;唯一的不同的是࿰c;列分区没有数据类型的限制࿰c;换句话说࿰c;范围分区和列表分区就是列分区；

4、hash分区

hash分区不需要指定范围或者列表࿰c;而是根据插入的值动态分配来决定插入到哪个分区࿰c;和hashMap的原理很像࿰c;不同的是hashMap会通过扰动函数来解决hash碰撞问题࿰c;但是mysql的hash分区是直接取模运算得出结果；然后插入指定位置的分区；

-- 普通字段的分区
CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 5;--创建5个分区࿰c;分别是0࿰c;1࿰c;2࿰c;3࿰c;4

-- 创建时间类型的分区
CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4; -- 创建四个分区࿰c;分别为0࿰c;1࿰c;2࿰c;3

5、秘钥分区（key分区）

key分区用的比较少

-- 以主键进行分区
CREATE TABLE k1 (
    id INT NOT NULL PRIMary KEY,
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2; -- 创建2个分区分别为P0和P1࿰c;这里是hash分区的变种࿰c;存储方式和hash分区一样


-- 以唯一键进行分区
CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 3;-- 创建三个分区࿰c;分别是p0࿰c;p1࿰c;p2

-- 指定主键字段进行分区
CREATE TABLE tm1 (
    s1 CHAR(32) PRIMary KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10; -- 创建10个分区

6、子分区

子分区这么理解就行了：在分区的基础上在分区；举个例子吧࿰c;如果一张表分成三个分区࿰c;而每个分区又有三个子分区࿰c;所以一共有3 * 3 = 9个分区；

 -- 表中有3个分区࿰c;每个分区上有2个子分区࿰c;所以加起来一共有6个分区
CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

进入mysql的数据文件中就可以看到有6个文件,顾名思义࿰c;生成了6个分区

-rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p0#SP#p0sp0.ibd
-rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p0#SP#p0sp1.ibd
-rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p1#SP#p1sp0.ibd
-rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p1#SP#p1sp1.ibd
-rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p2#SP#p2sp0.ibd
-rw-rw---- 1 mysql mysql      98304 8月   2 22:37 ts#P#p2#SP#p2sp1.ibd

添加分区

-- 添加列表分区
alter table titles add partition(partition P7 values in('CEO'));

分区表原理

分区表由多个相关的底层表实现࿰c;这个底层表也是由句柄对象标识࿰c;我们可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎）࿰c;分区表的索引知识在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看࿰c;底层表和普通表没有任何不同࿰c;存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

分区表的操作按照以下的操作逻辑进行：

SELEct查询

当查询一个分区表的时候࿰c;分区层先打开并锁住所有的底层表࿰c;优化器先判断是否可以过滤部分分区࿰c;然后再调用对应的存储引擎接口访问各个分区的数据

insert操作

当写入一条记录的时候࿰c;分区层先打开并锁住所有的底层表࿰c;然后确定哪个分区接受这条记录࿰c;再将记录写入对应底层表

delete操作

当删除一条记录时࿰c;分区层先打开并锁住所有的底层表࿰c;然后确定数据对应的分区࿰c;最后对相应底层表进行删除操作

update操作

当更新一条记录时࿰c;分区层先打开并锁住所有的底层表࿰c;R_175_11845@ysql先确定需要更新的记录再哪个分区࿰c;然后取出数据并更新࿰c;再判断更新后的数据应该再哪个分区࿰c;最后对底层表进行写入操作࿰c;并对源数据所在的底层表进行删除操作

有些操作时支持过滤的࿰c;例如࿰c;当删除一条记录时࿰c;R_175_11845@ySQL需要先找到这条记录࿰c;如果where条件恰好和分区表达式匹配࿰c;就可以将所有不包含这条记录的分区都过滤掉࿰c;这对update同样有效。如果是insert操作࿰c;则本身就是只命中一个分区࿰c;其他分区都会被过滤掉。mysql先确定这条记录属于哪个分区࿰c;再将记录写入对应得曾分区表࿰c;无须对任何其他分区进行操作

虽然每个操作都会“先打开并锁住所有的底层表”࿰c;但这并不是说分区表在处理过程中是锁住全表的࿰c;如果存储引擎能够自己实现行级锁࿰c;例如InnoDB࿰c;则会在分区层释放对应表锁。

如何使用分区表

@H_618_19@

日志系统可以用分区࿰c;一般日志数量都是比较多的࿰c;按年或者月份来分区࿰c;一般来说都需要在日志系统中查询出某一段时间的历史记录࿰c;因为数据量巨大࿰c;肯定不能走全表扫描࿰c;全表扫描会引发大量的随机io࿰c;当数据量超大的时候࿰c;索引也无法起作用；此时应该考虑用分区进行解决；

并不是数据量大才需要用分区࿰c;数据量小的时候也可以用分区࿰c;怎样的场景下数据量小呢？答案是你每次查询的数据都是某一个批次的时候就可以用分区࿰c;比如说字典࿰c;业务的字典和用户类型的字典一般都是存放在同一张表里面的࿰c;且你每次查询的时候不是差一个业务或者一个用户类型࿰c;而是查询整个业务或者用户类型࿰c;这就是一个批次࿰c;此时也可以用分区来实现；

使用分区后࿰c;就可以不用索引了࿰c;因为一般使用分区的话都是范围查询࿰c;范围查询也就没必要使用索引了；已经将数据分布在不同的分区中了；

要使用索引的话࿰c;也可以࿰c;但是要分离热数据和冷数据࿰c;热数据就是经常要查询的数据࿰c;在热数据的表上加索引来加快访问速度；

注意事项

@H_618_19@

null值会使分区过滤无效；分区是需要制定列名的࿰c;需要确保这个列名不会出现null值；

如果分区列和索引列不是同一列的话࿰c;会导致查询无法进行分区过滤࿰c;比如说你的id和age字段都加了索引࿰c;那么分区的时候最好把这2个列设为分区列：干PARTITION BY RANGE columNS(id,agE)

对分区表增删改的成本很高࿰c;每次对表进行次增删改的时候会打开并锁住所有的底层表࿰c;只要有一个锁住了࿰c;其他的操作就无法进行；

维护分区的时候࿰c;成本可能很高࿰c;特别是需要修改分区的时候࿰c;成本是最高的࿰c;

大佬总结

以上是大佬教程为你收集整理的mysql表分区的使用和底层原理全部内容，希望文章能够帮你解决mysql表分区的使用和底层原理所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。

标签：cap column InnoDB list map mysql partition php sql 优化应用场景数据库程序员索引

上一篇: 【MySQL学习】数据库问题及着重点... 下一篇:大学毕业设计 C#实现医院药品信息...

猜你在找的程序笔记相关文章

You can't specify target table 'xxx' for update in FROM clause的解决 2022-07-21
【UNIAPP】上传视频，进度条的前台与后端 2022-07-21
十款代码表白特效，一个比一个浪漫！ 2022-07-04
作业3 2022-07-06
linux系统下部署项目访问报404错误的解决方法 2022-05-30
C++带有指针成员的类处理方式详解 2022-06-07
Linux——（1）基本命令 2022-07-21
JavaScript之正则表达式学习笔记 2019-11-07
Redux源码学习笔记 2019-11-07
Webpack学习笔记 2019-11-07