【MYSQL】分区表

对于MYSQL的态度一直都是会基本SQL和简单命令就行，最近处理一个数据量很大的项目，为了提高效率，在数据库方面的瓶颈上，选择了使用分区表来提高查询效率。至此和大家一起分享一下。 1.引言 本文初略的讲述了mysql数据库如何分区表。 2.环境要求 在5.1版本

对于MYSQL的态度一直都是会基本SQL和简单命令就行，最近处理一个数据量很大的项目，为了提高效率，在数据库方面的瓶颈上，选择了使用分区表来提高查询效率。至此和大家一起分享一下。

1.引言

本文初略的讲述了mysql数据库如何分区表。

2.环境要求

在5.1版本中不是默认就安装了，而在之后版本中一般默认选择了安装分区表支持。可以通过如下方式查看当前数据库是否支持分区表操作：

650) this.width=650;” src=”https://img.gaodaima.com/d/file/2021/11/10/a752972d6c6537fc67a27ca8e4d72d22.jpg” title=”partitions.png” alt=”wKioL1R6ym7A0CplAABfWiOHn-I850.jpg” />

使用show variables like ‘%partition%’;如果不支持分区，那么value字段值为No。

3.重要概念描述

3.1 分区字段

1）当仅存在单一主键时，不存在唯一键，那么分区字段必须是主键字段；

2）当存在复合主键时，不存在唯一键，那么分区字段必须是主键组合的一部分字段，一个或多个。

3）当主键和唯一键都存在时，那么分区字段必须同时包括主键字段和唯一键字段。

4.分区表类型

4.1 range分区

1）语法展示：

# 语法# 在创建表单的最后，添加partitions by range(分区字段)(#   partition 分区名 values less than(阀值1),#   partition 分区名 values less than(阀值2),#   ...#   partition 分区名 values less than(阀值n),# )

示例展示：

create table test_range(	id int auto_increment,	description varchar(50),	primary key(id)) ENGINE=InnoDB auto_increment=1 default charset=utf8partition by range(id)(	partition p1 values less than(6), #id<6的存放在p1分区	partition p2 values less than(11) #6 <= id < 11 存放在p2分区);

查看分区情况：

   show create table test_range;

650) this.width=650;” src=”https://img.gaodaima.com/d/file/2021/11/10/d09c28444a76ef104dcb3d97a2eb340b.jpg” title=”p1png.png” alt=”wKioL1R6z4qC137DAADITZNS8H8149.jpg” />

注意到，在显示的表结构添加了分区表的信息。

数据测试：

    insert into test_range values(null, "test1");        insert into test_range values(null, "test2");    insert into test_range values(null, "test3");    insert into test_range values(null, "test4");    insert into test_range values(null, "test5");    insert into test_range values(null, "test6");    insert into test_range values(null, "test7");    insert into test_range values(null, "test8");    insert into test_range values(null, "test9");    insert into test_range values(null, "test10");

插入10条数据，此时我们来查看其查询执行过程：

650) this.width=650;” src=”https://img.gaodaima.com/d/file/2021/11/10/9051c89ba499ad702b387b583b929199.jpg” title=”p2.png” alt=”wKioL1R60LDBET1bAADRpczcpSo931.jpg” />

从结果可以发现，其只是在p1分区执行的查询，那么此时就减少了查询扫描的数据量，从而提高了查询效率。

如果此时，我们插入第11条数据会发生什么情况呢？

   insert into test_range values(null, "test11");   会发错：insert into test_range values(null, "test11")	Error Code: 1526. Table has no partition for value 11	0.015 sec

原因很简单，因为在我们创建表单时，仅仅指定了1 – 10的id数值分区，当插入id=11时的分区时，此时没有分区提供，那么就引发错误，那么如果解决这样的问题呢，采取如下方式，修改表的分区方式：

alter table test_range add partition(	partition p3 values less than(MAXVALUE));# 添加一个分区，也就是p3是id从11到maxValue的存放区域

此时插入id=11的数据，并执行查询解析：

650) this.width=650;” src=”https://img.gaodaima.com/d/file/2021/11/10/9cf1347eee7bbd9a1e4bf7bea7d92005.jpg” title=”p3.png” alt=”wKiom1R60nnwSlUUAADL0geAo20618.jpg” /> 发现，已经将其分配到p3分区中了。

还需要特别注意的时，使用partition by range(分区字段)，其中的分区字段可以是分区字段的表单式，但是必须是返回的整数，在5.5版本中，可以使用partition by range column/columns语法，指定某个字段。这里不做介绍。大家可以自己尝试一下。

4.2 list分区

list分区可以理解为集合分区方式，意思就是指定某个集合来分区。

语法展示：

   partition by list(分区字段表达式)(     partition 分区名 values in(value1, value2,<div>本文来源gaodai^.ma#com搞#代!码网</div>...,valuen)#分区集合   );

示例展示：

create table test_list(	id int auto_increment,	description varchar(50),	primary key(id))ENGINE=InnoDB auto_increment=1 default charset=utf8partition by list(id)(	partition p1 values in (1, 3, 5, 7, 9),#id=1,3,5,7,9分配至p1区	partition p2 values in (2, 4, 6, 8, 10)#id=2,4,6,8,10分配至p2区);#可以如4.1中使用show create table test_list查看表创建结构。

数据测试：使用4.1中数据测试sql，插入10条数据。

650) this.width=650;” src=”https://img.gaodaima.com/d/file/2021/11/10/caf1b3e0731982a8fa449a902ac1f32b.jpg” title=”p4.png” alt=”wKioL1R61Y-RjP7aAADWGviW2FI351.jpg” /> 可以发现其查询的仅仅是p1区。如果需要添加分区，可以使用4.1中使用的add partition来添加分区。

4.3 hash分区

使用hash函数得到取模，分配到不同的分区中。分区表达式必须返回整数。

语法展示：

   partition by hash(分区表达式) partitions 表数量（模数）.

示例展示：

create table test_hash(	id int auto_increment,	description varchar(50),	primary key(id)) ENGINE=InnoDB auto_increment=1 default charset=utf8partition by hash(id) partitions 3; #以id分区，分配到3张表中

数据测试：插入4.1类同10条数据

650) this.width=650;” src=”https://img.gaodaima.com/d/file/2021/11/10/b5f747af85b2698af2a8a5741c272c38.jpg” title=”p5.png” alt=”wKioL1R615mA8FC8AADLcG2rGPs147.jpg” />

你也可以尝试修改id值，查看其分配的分区。hash分区还有一种叫做linear hash线性分区，这里不做介绍，

4.4 key分区

在本次开发中，我选择的是key分区，因为其是针对一个或多个字段作为分区字段，不要求是正整数，其内部调用的是自己的hash函数，计算出hash整数值，然后取模分表。

语法展示：

   partition by key(分区字段组合) partitions 表数（模数）。

操作和Hash分区一致，这里就不做累赘的展示了。

5.额外扩展

5.1 在实际开发中，经常出现的情况是表已经上线使用，那么必须动态添加分区类型。

   alter table 表名 partition by hash/key （分区字段表达式） [partitions 表数]#如果不加partitions那么默认为1.      alter table 表名 partition by range/list(分区字段表达式)（具体分区设置）。

5.2 当发现之前的分区需要添加新的分区时，采取如下方式：

   list/range : alter table 表名 add partition (partition 分区名 [values in|values less than] [集合|数值])；   hash/key : alter table 表名 add partition partitions 表数；

例如：修改上述test_hash的分区数量

   alter table test_hash add partition partitions 6;

5.3 删除某个分区/删除所有分区

   # 删除某个分区   list/range : alter table 表名 drop partition 分区名1, 分区名2，...；   #例如：   alter table test_list drop partition p1;   hash/key : 上述语法不成立      # 删除整个分区   alter table test_hash remove partitioning;

还有诸如合并分区，以及5.5的一些新特性，list/range 增加column,columns支持。本文不做过多阐述。

喜欢 (0)赏

[搞代码]

分享 (0)