登陆 注册
本网站(662p.com)打包出售,且带程序代码数据，662p.com域名，程序内核采用TP框架开发，需要联系扣扣：2360248666 /wx:lianweikj
精品域名一口价出售：1y1m.com(350元) ，6b7b.com(400元) ， 5k5j.com(380元) ， yayj.com(1800元)， jiongzhun.com(1000元) ， niuzen.com(2800元) ， zennei.com(5000元)
需要联系扣扣：2360248666 /wx:lianweikj
mysql表分区的使用与底层原理详解
沙雕mars · 249浏览 · 发布于2021-08-04 +关注

 

通俗地讲表分区是将一大表,根据条件分割成若干个小表,mysql5.1开始支持数据表分区了,这篇文章主要给大家介绍了关于mysql表分区的使用与底层原理的相关资料,需要的朋友可以参考下


什么是分区表

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

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

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

那么，业务量在增长，数据到瓶颈了怎么办呢，除了使用分布式数据库，我们也可以自行分库分表，或者利用mysql的分区功能实现。

分区表的出现是为了分而治之的概念，分区表的用处非常大，只是现在还有很多人都不知道；
将一个表设置为分区表后，会在数据文件.idb的文件名加上#号，代表这是一个分区表；

分区表应用场景

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

  2. 分区表的数据更容易维护，，能批量删除大量数据

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

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

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

分区表的限制

  1. 一个表最多能有1024个分区，在5.7版本及以上可以有8196个分区

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

  3. 分区表无法使用外检约束

  4. 最好不要去修改分区列

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

-- 创建分区必须包含所有主键

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分区，或者用一个更大的值

);


-- 创建分区必须包含所有唯一键

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分区，或者用一个更大的值

);

分区类型

  1. 范围分区

  2. 列表分区

  3. 列分区

  4. hash分区

  5. 秘钥分区

  6. 子分区

分区表的使用

1、范围分区

下面示例中将年龄进行分区，

1

2

3

4

5

6

7

8

9

10

11

12

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分区

);

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

1

2

3

4

5

6

7

8

9

10

11

[root@VM_0_5_centos test]# pwd

/var/lib/mysql/test

[root@VM_0_5_centos test]# ll

总用量 8741504

-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以下的数字，如果插入21的数字则会报错，

1

2

mysql> insert employees (id,name,age) values(1,'yexindong',21);

ERROR 1526 (HY000): Table has no partition for value 21

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

1

2

3

4

5

6

7

8

9

10

11

12

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分区，或者用一个更大的值

);

时间范围分区

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

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分区）

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

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、列分区

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

4、hash分区

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

-- 普通字段的分区

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个分区，分别是0，1，2，3，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; -- 创建四个分区，分别为0，1，2，3

5、秘钥分区（key分区）

key分区用的比较少

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

-- 以主键进行分区

CREATE TABLE k1 (

    id INT NOT NULL PRIMARY KEY,

    name VARCHAR(20)

)

PARTITION BY KEY()

PARTITIONS 2; -- 创建2个分区分别为P0和P1，这里是hash分区的变种，存储方式和hash分区一样



-- 以唯一键进行分区

CREATE TABLE k1 (

    id INT NOT NULL,

    name VARCHAR(20),

    UNIQUE KEY (id)

)

PARTITION BY KEY()

PARTITIONS 3;-- 创建三个分区，分别是p0，p1，p2


-- 指定主键字段进行分区

CREATE TABLE tm1 (

    s1 CHAR(32) PRIMARY KEY

)

PARTITION BY KEY(s1)

PARTITIONS 10; -- 创建10个分区

6、子分区

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

1

2

3

4

5

6

7

8

9

-- 表中有3个分区，每个分区上有2个子分区，所以加起来一共有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个文件,顾名思义，生成了6个分区

1

2

3

4

5

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

添加分区

1

2

-- 添加列表分区

alter table titles add partition(partition p7 values in('CEO'));

分区表原理

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

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

select查询

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

insert操作

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

delete操作

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

update操作

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

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

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

如何使用分区表

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

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

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

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

注意事项

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

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

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

  4. 维护分区的时候，成本可能很高，特别是需要修改分区的时候，成本是最高的，


数据库 mysql
0 人收藏
分享至：

相关推荐

使用SELECT语句检索数据

奔跑的男人 · 803浏览 · 2019-06-03 09:33:43
部署MySQL延迟从库的几个好处

吴振华 · 662浏览 · 2019-05-14 21:57:51
MongoDB凭什么跻身数据库排行前五？

iamitnan · 722浏览 · 2019-06-18 10:04:56
一个快速上手、轻量级 Golang 公共类库 (golang_common)

qq2360248666 · 894浏览 · 2019-05-29 09:48:45
详解基于linux环境MySQL搭建与卸载

追忆似水年华 · 868浏览 · 2019-05-27 09:54:23
Oracle开启和关闭的几种模式

qq2360248666 · 750浏览 · 2019-06-04 10:18:47
加载中

0评论

评论
沙雕mars
做自己，让别人去说，欢迎各位关注！
分类专栏
最新发布
小鸟云服务器
最热排行
扫码进入手机网页
Copyright © 2013-2023 662P. All rights reserved. 粤ICP备2023044799号