MYSQL分区表测试

最新推荐文章于 2024-09-22 15:18:13 发布
最新推荐文章于 2024-09-22 15:18:13 发布
阅读量396 收藏
点赞数
分类专栏： Mysql 文章标签： mysql 数据库 测试

一、 mysql分区简介

数据库分区

数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于应用来说分区完全是透明的。

MYSQL的分区主要有两种形式：水平分区和垂直分区

水平分区（HorizontalPartitioning）

这种形式的分区是对根据表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。
所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。水平分区一定要通过某个属性列来分割。常见的比如年份，日期等。

垂直分区（VerticalPartitioning）

这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应所有行。
可以用 showvariables like ‘%partition%’;

命令查询当前的mysql数据库版本是否支持分区。

分区的作用：数据库性能的提升和简化数据管理

在扫描操作中，mysql优化器只扫描保护数据的那个分区以减少扫描范围获得性能的提高。

分区技术使得数据管理变得简单，删除某个分区不会对另外的分区造成影响，分区有系统直接管理不用手工干预。

mysql从5.1版本开始支持分区。每个分区的名称是不区分大小写。同个表中的分区表名称要唯一。

二、 mysql分区类型
根据所使用的不同分区规则可以分成几大分区类型。

RANGE 分区：

基于属于一个给定连续区间的列值，把多行分配给分区。

LIST 分区：

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区：

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。

KEY
分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

复合分区：

基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。

三、 mysql分区表常用操作示例
以部门员工表为例子：

1) 创建range分区



create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date,

salary int

)

partition by range(salary)

(

partition p1 values less than (1000),

partition p2 values less than (2000),

partition p3 values less than maxvalue

);

以员工工资为依据做范围分区。



create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by range(year(birthdate))

(

partition p1 values less than (1980),

partition p2 values less than (1990),

partition p3 values less than maxvalue

);

以year(birthdate)表达式（计算员工的出生日期）作为范围分区依据。这里最值得注意的是表达式必须有返回值。

2) 创建list分区

create table emp

(empno  varchar(20) not null ,

empname varchar(20),

deptno  int,

birthdate date not null,

salary int

)

partition by list(deptno)

(

partition p1 values in  (10),

partition p2 values in  (20),

partition p3 values  in  (30)

);

以部门作为分区依据，每个部门做一分区。

3) 创建hash分区

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by hash(year(birthdate))

partitions 4;

4) 创建key分区

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。“CREATE TABLE …PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by key(birthdate)

partitions 4;

5) 创建复合分区

range - hash(范围哈希)复合分区

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by range(salary)

subpartition by hash(year(birthdate))

subpartitions 3

(

partition p1 values less than (2000),

partition p2 values less than maxvalue

);

range- key复合分区

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by range(salary)

subpartition by key(birthdate)

subpartitions 3

(

partition p1 values less than (2000),

partition p2 values less than maxvalue

);

list - hash复合分区

CREATE TABLE emp (

empno varchar(20) NOT NULL,

empname varchar(20) ,

deptno int,

birthdate date NOT NULL,

salary int

)

PARTITION BY list (deptno)

subpartition by hash(year(birthdate))

subpartitions 3

(

PARTITION p1 VALUES in (10),

PARTITION p2 VALUES in (20)

)

;

list - key 复合分区

CREATE TABLE empk (

empno varchar(20) NOT NULL,

empname varchar(20) ,

deptno int,

birthdate date NOT NULL,

salary int

)

PARTITION BY list (deptno)

subpartition by key(birthdate)

subpartitions 3

(

PARTITION p1 VALUES in (10),

PARTITION p2 VALUES in (20)

)

;

6) 分区表的管理操作

删除分区:

alter table emp drop partition p1;

不可以删除hash或者key分区。

一次性删除多个分区，alter table emp drop partition p1,p2;

增加分区:

alter table emp add partition (partition p3 values less than (4000));

alter table empl add partition (partition p3 values in (40));

分解分区:

Reorganizepartition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。分解前后分区的整体范围应该一致。

alter table te

reorganize partition p1 into

(

partition p1 values less than (100),

partition p3 values less than (1000)

); —-不会丢失数据

合并分区:

Merge分区：把2个分区合并为一个。
alter table te

reorganize partition p1,p3 into

(partition p1 values less than (1000));

—-不会丢失数据

重新定义hash分区表:

Alter table emp partition by hash(salary)partitions 7;

—-不会丢失数据

重新定义range分区表:

Alter table emp partitionbyrange(salary)

(

partition p1 values less than (2000),

partition p2 values less than (4000)

); —-不会丢失数据

删除表的所有分区:

Alter table emp removepartitioning;–不会丢失数据

重建分区：

这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。

ALTER TABLE emp rebuild partitionp1,p2;

优化分区：

如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可以使用“ALTER TABLE … OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。

ALTER TABLE emp optimize partition p1,p2;

分析分区：

读取并保存分区的键分布。

ALTER TABLE emp analyze partition p1,p2;

修补分区：

修补被破坏的分区。

ALTER TABLE emp repairpartition p1,p2;

检查分区：

可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。

ALTER TABLE emp CHECK partition p1,p2;

这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTER TABLE … REPAIR PARTITION”来修补该分区。

【mysql分区表的局限性】

  1. 在5.1版本中分区表对唯一约束有明确的规定，每一个唯一约束必须包含在分区表的分区键（也包括主键约束）。


CREATE TABLE emptt (

empno varchar(20) NOT NULL  ,

empname varchar(20)，

deptno int,

birthdate date NOT NULL,

salary int ,

primary key (empno)

)

PARTITION BY range (salary)

(

PARTITION p1 VALUES less than (100),

PARTITION p2 VALUES less than (200)

);

这样的语句会报错。MySQL Database Error: A PRIMARY KEY must include allcolumns in the table’s partitioning function；

CREATE TABLE emptt (

empno varchar(20) NOT NULL  ,

empname varchar(20) ,

deptno int(11),

birthdate date NOT NULL,

salary int(11) ,

primary key (empno，salary)

)

PARTITION BY range (salary)

(

PARTITION p1 VALUES less than (100),

PARTITION p2 VALUES less than (200)

);

在主键中加入salary列就正常。

  1. MySQL分区处理NULL值的方式

如果分区键所在列没有notnull约束。

如果是range分区表，那么null行将被保存在范围最小的分区。

如果是list分区表，那么null行将被保存到list为0的分区。

在按HASH和KEY分区的情况下，任何产生NULL值的表达式mysql都视同它的返回值为0。

为了避免这种情况的产生，建议分区键设置成NOT NULL。

  1. 分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是当分

区类型为KEY分区的时候，可以使用其他类型的列作为分区键（ BLOB or TEXT 列除外）。

  1. 对分区表的分区键创建索引，那么这个索引也将被分区,分区键没有全局索引一说。

  2. 只有RANG和LIST分区能进行子分区，HASH和KEY分区不能进行子分区。

  3. 临时表不能被分区。

四、 获取mysql分区表信息的几种方法
1. show create table 表名
可以查看创建分区表的create语句

  1. show table status
    可以查看表是不是分区表

  2. 查看information_schema.partitions表 

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

可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息

  1. explain partitions select语句
    通过此语句来显示扫描哪些分区，及他们是如何使用的.

    五、 分区表性能比较

  2. 创建两张表: part_tab(分区表),no_part_tab(普通表)
CREATE TABLE part_tab

( c1 int default NULL, c2 varchar (30) default NULL, c3 date not null)

PARTITION BY RANGE(year(c3))

(PARTITION p0VALUES LESS THAN (1995),

PARTITION p1 VALUESLESS THAN (1996) ,

PARTITION p2 VALUESLESS THAN (1997) ,

PARTITION p11VALUES LESS THAN (MAXVALUE) );

CREATE TABLE no_part_tab

( c1 int defaultNULL, c2 varchar2(30) default NULL, c3 date not null)；
  1. 用存储过程插入800万条数据
CREATE PROCEDURE load_part_tab()

    begin

    declare v int default 0;

    while v < 8000000

    do

        insert into part_tab

        values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));

         set v = v + 1;

    end while;
    insert into no_part_tab  select * frompart_tab;
end;
  1. 测试sql性能

查询分区表：

select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

+———-+
| count(*) |
+———-+
| 795181 |
+———-+
1 row in set (2.62 sec)

查询普通表：

select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

+———-+
| count(*) |
+———-+
| 795181 |
+———-+
1 row in set (7.33 sec)

分区表的执行时间比普通表少70%

UpUpUpUpUpUpUp
关注
专栏目录
mysql分区表测试_MYSQL分区表功能测试简析
weixin_29887959的博客
01-21 222
1.查看Mysql版本是否支持分区 SHOW VARIABLES LIKE %partition%;+-------------------+-------+| Variable_name | Value |+-------------------+-------+| have_partitioning | YES |+-------------------+-------+如果VALUE 为YE...
MySQL 分区表介绍与测试
For-的博客
05-26 697
一、基本介绍： 分区表是一个独立的逻辑表，底层由多个物理的文件组成，分区对于 SQL 层来说是透明的； 下图可以看出，表 em_elec_meter_data 的多个分区是使用了 # 进行标识。 分区表的使用场景： 表非常大以至于无法全部都放在内存中，或者热点数据只有在表的最后部分，其他都是历史数据。 分区表的数据作用在不同的设备上，高效地利用多个硬件设备；例如有 1TB 的数据，服务器的硬盘容量只有 500 GB，那么可以使用其他设备平摊未能放下的数据。 需要独立备份和恢复分区，这在非常大数据集的场景
查看sqlserver表分区数据分布
12-13
查看sqlserver表分区数据分布，需要的同学下载看看吧
MySQL分区表（一）
最新发布
小何的博客
09-22 1779
MySQL分区表
mysql分区
果果的博客
09-28 2713
mysql分区
mysql数据库分区
weixin_49114503的博客
08-06 4912
所谓分区，就是将一个表分成多个区块进行操作和保存，从而降低每次操作的数据，提高性能。而对于应用来说则是透明的，从逻辑上看只有一张表，但在物理上这个表可能是由多个物理分区组成的，每个分区都是独立的对象，可以进行独立处理。未分区的表所有数据物理层面存储在同一个文件中，分区的表会按分区数量存储到不同的文件中，提高查询效率以下为分区和不分区物理层面的区别。分区是一种表的设计模式，正确的分区可以极大地提升数据库的查询效率，完成更高质量的SQL编程。但是如果错误地使用分区，那么分区可能带来毁灭性的的结果。
MySQL数据库分区
Hello World
08-08 1875
数据库分区是一种有效的管理大型数据集的策略，可以提高查询性能和管理效率。在 MySQL 中实现分区需要根据具体的业务需求选择合适的分区类型，并设计合理的分区方案。
Mysql分区表的管理与维护
09-10
MySQL分区表是一种优化大型数据库性能的技术，它将一个大表分成多个较小的部分，每个部分称为一个分区，从而提高查询效率和管理能力。分区通常应用于处理大量数据的表，特别是那些有明确范围或时间属性的数据，如...
MySQL 5.5.8 分区表性能测试
03-20
压缩包中的“count.xls”文件可能是测试结果的记录，可能包含了在不同场景下对MySQL 5.5.8分区表性能的具体测试数据，例如查询时间、CPU使用率、内存消耗等指标。通过分析这些数据，我们可以深入了解分区表在实际...
Mysql分区
a2534725767的博客
01-05 575
Mysql分区 1、什么是mysql分区      使用myisam引擎的一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部
mysql 分区功能介绍及实例
翻过这座山，就到菩提洞了
04-01 468
一，什么是数据库分区 前段时间写过一篇关于mysql分表的的文章，下面来说一下什么是数据库分区，以mysql为例。mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi...
mysql的分区
qq_25072517的博客
05-15 315
https://www.cnblogs.com/phpshen/p/6198375.html https://blog.csdn.net/u010320108/article/details/79460748 https://www.cnblogs.com/mliudong/p/3625522.html 一:分区 分区就是把一个数据表的文件和索引分散存储在不同的物理文件中。 mysql支...
MySQL的分区
abcd1594433168的专栏
01-15 428
MySQL的分区MySQL的分区 什么是分区 分区能干什么 分区能支持的引擎 分区管理概述什么是分区所谓分区就是将一个表分解成多个区块进行操作和保存，从而降低每次操作的数据，提高性能。而对应用来说是透明的，从逻辑上看是只有一个表(这里跟分库分表的访问不一样)，但在物理上这个表可能是由多个物理分区组成的，每个分区都是一个独立的对象，可以进行独立处理。分区能干什么 进行逻辑数据分割，分割数据能够有多个不
mysql 分区 partition
wnull的博客
08-23 395
mysql 分区 .partition 一、分区 将某张表的数据，分别存储到不同的去区域中。 *其实每个分区，就是独立的表，都要存储该分区数据的数据，索引等信息。 二、分区的优点 和单个磁盘或文件系统分区相比，可以存储更多的数据。 优化查询。 1 . where 子句中包含分区条件时，可以只扫描必要的分区。 2. 涉及聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需汇总得到结果。 对...
MySQL分区
yozzs的博客
10-22 2521
分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。分区有利于管理非常大的表。
MySQL表分区
热门推荐
atwdy的博客
06-13 1万+
主要介绍mysql几种表分区的基本使用，包括range分区、list分区、hash分区、key分区、子分区和columns分区。
Mysql——分区
如不來的博客
08-19 343
1、分区的概念：如果一张表的数据量太大的话，那么myd(表数据)、myi(索引数据)就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块。这样我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。 2、分区...
MySQL分区表详解与测试实践
"MYSQL分区表测试" 在MySQL数据库中，分区表是一种高级的物理数据库设计技术，旨在提升查询性能和简化数据管理。分区主要是通过将一个大的表逻辑上划分为更小、更易管理的部分，而对应用程序来说，这些分区是透明的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值