MySQL分区表

标签: mysql  

分区功能并不是在存储引擎层完成的,因此不是只有innodb存储引擎支持分区。
分区的过程是将一个表或索引分解为多个更小、更可管理的部分。每个分区都是独立的对象,可以独自处理,也可以作为一个更大的对象的一部分进行处理。
MySQL数据库支持的分区类型为水平分区,并不支持垂直分区。此外,MySQL数据库的分区是局部分区索引,一个分区中既存放了数据又存放了索引。而全局分区是指,数据存放在各个分区中,但是所有数据的索引存放在一个对象中。
分区可能会给某些SQL语句性能带来提高,但是分区主要用于数据库高可用性的管理。
当前MySQL支持以下几种类型的分区:

  • RANGE分区
  • LIST分区
  • HASH分区
  • KEY分区

不论创建何种类型的分区,如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分。唯一索引可以是允许NULL值的,并且分区列只要是唯一索引的一个组成部分,不需要整个唯一索引列都是分区列。如果建表时没有指定主键,唯一索引,可以指定任何一个列为分区列。

RANGE分区

行数据基于属于一个给定连续区间的列值被放入分区。
示例:

create table t(
id int
)engine=innodb
partition by range (id) (
partition p0 values less than (10),
partition p1 values less than (20)
);

查看表在磁盘上的物理文件,启用分区之后,表不再由一个ibd文件组成了,而是由建立分区时的各个分区ibd文件组成如t#p#p0.ibd、t#p#p1.ibd
接着插入数据:

insert into t values(1);
insert into t values(9);
insert into t values(10);
insert into t values(15);

可以通过查询information_schema数据库中的partitions表来查看每个分区的具体信息:

select * from information_schema.partitions
where table_schema='test_partition'and table_name='t'\G;

在这里插入图片描述
TABLE_ROWS列反映了每个分区中记录的数量PARTITION_METHOD表示分区的类型,这里显示的是RANGE。

对于表t,由于定义了分区,因此对于插入的值应该严格遵守分区的定义,当插入一个不在分区中定义的值时,MySQL数据库会抛出了一个异常。
为解决上述问题,可以对分区添加一个MAXVALUE值的分区,MAXVALUE可以理解为正无穷:

alter table t add partition(partition p2 values less than maxvalue);

使用explain查看SQL语句的执行计划:

explain
select * from t
where id<10 and id>0\G;

在这里插入图片描述
可以看出该SQL语句只查询了p0分区。

LIST分区

LIST分区和RANGE分区非常相似,只是分区列的值是离散的。
示例:

create table r (
a int,
b int)engine=innodb
partition by list (b) (
partition p0 values in (1,3,5,7,9),
partition p1 values in (0,2,4,6,8));

插入一些数据:

insert into r select 1,1;
insert into r select 1,2;
insert into r select 1,3;
insert into r select 1,4;

查看分区情况:

select table_name,partition_name,table_rows 
from information_schema.partitions 
where table_name='r' and table_schema='test_partition'\G;

在这里插入图片描述

HASH分区

HASH分区的目的是将数据均匀地分布到预先定义的各个分区中,保证各分区的数据量大致都是一样的。
在RANGE分区和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;而在HASH分区中,MySQL自动完成这些工作,用户要做的只是基于将要进行的哈希分区的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。
示例:

create table t_hash(
a int,
b datetime
)engine=innodb
partition by hash (year(b))
partitions 4;

插入一条记录:

 insert into t_hash values(1,'2010-04-01');

分区算法如下:

MOD(YEAR('2010-04-01'),4)
=MOD(2010,4)
=2

查看分区情况:

select table_name,partition_name,table_rows 
from information_schema.partitions 
where table_name='t_hash' and table_schema='test_partition'\G;

在这里插入图片描述
MySQL还支持一种称为LINEAR HASH的分区,它使用一个更复杂的算法来确定新行插入到已分区的表中的位置。它的语法和HASH分区的语法相似,只是将HASH改为LINEAR HASH。
示例:

create table t_l_hash(
a int,
b datetime
)engine=innodb
partition by linear hash (year(b))
partitions 4;

插入一条记录:

insert into t_l_hash values(1,'2010-04-01');

MySQL根据以下方法进行分区判断:

  • 取大于分区数量4的下一个2的幂值V,V=POWER(2,CEILING(LOG(2,num)))=4
  • 所在分区N=YEAR(‘2010-04-01’)&(V-1)=2

LINEAR HASH分区的优点在于,增加、删除、合并和拆分分区将变得更加快捷,这有利于处理含有大量数据的表。它的缺点在于,与使用HASH分区得到的数据分布相比,各个分区间数据可能不大均衡。

KEY分区

KEY分区和HASH分区相似,不同之处在于HASH分区使用用户定义的函数进行分区,KEY分区使用MySQL提供的函数进行分区。

create table t_key (
a int,
b datetime
)engine=innodb
partition by key(b)
partitions 4;

COLUMNS分区

前面介绍的RANGE、LIST、HASH和KEY这四种分区中,分区的条件是:数据必须是整型,如果不是整型,那应该需要通过函数将其转换为整型,如year()等函数。COLUMNS分区可以直接使用非整型的数据进行分区,分区根据类型直接比较而得,不需要转换为整型。此外RANGE COLUMNS分区可以对多个列的值进行分区。
COLUMNS分区支持以下的数据类型:

  • 所有的整型类型
  • 日期类型,如date和datetime。其余日期类型不予支持。
  • 字符串类型。blob和text类型不予支持。
    示例:
-- 对日期类型的分区,不再需要year()函数了,而直接使用columns
create table t_columns(
a int,
b datetime
)engine=innodb
partition by range columns (b) (
partition p0 values less than ('2009-01-01'),
partition p1 values less than ('2010-01-01')
);

-- 可以直接使用字符串的分区
create table t_columns2(
a int,
b datetime,
city varchar(15)
)engine=innodb
partition by list columns(city) (
partition p1 values in ('a','b','c'),
partition p2 values in ('d','e','f'),
partition p3 values in ('g','h','k'),
);

-- 可以使用多个列进行分区:
create table rcx (
a int,
b int,
c char(3)
)engine=innodb
partition by range columns(a,b,c) (
partition p0 values 
);

子分区

子分区是在分区的基础上再进行分区,也叫复合分区。MySQL允许在RANGE和LIST的分区上再进行HASH或KEY的子分区。

create table ts (a int,b date)engine=innodb
partition by range(year(b))
subpartition by hash(to_days(b))
subpartitions 2 (
partition p0 values less than (1990),
partition p1 values less than (2000),
partition p2 values less than MAXVALUE);

表ts先根据b列进行了RANGE分区,然后又进行了一次HASH分区,所以分区的数量应该为6个。
在这里插入图片描述
我们也可以通过使用subpartition语法来显式地指出各个子分区的名字:

create table ts(
a int,
b date
)engine=innodb
partition by range(year(b))
subpartition by hash(to_days(b)) (
partition p0 values less than (1990) (
subpartition s0,
subpartition s1
),
partition p1 values less than (2000) (
subpartition s2,
subpartition s3
),
partition p0 values less than MAXVALUE (
subpartition s4,
subpartition s5
));

子分区的建立需要注意以下几个问题:

  • 每个子分区的数量必须相同
  • 要在一个分区表的任何分区上使用subpartition来明确定义任何子分区,就必须定义所有子分区。
  • 每个subpartition子句必须包括子分区的一个名字。
  • 子分区的名字必须是唯一的。
版权声明:本文为qq_41242680原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_41242680/article/details/108050026

智能推荐

javaScript第一天(1)

01-JavaScript基础 核心知识点 javaScript书写位置 javaScript变量 javaScript数据类型 javaScript数据类型转换 javaScript运算符 今日学习目标 能够定义一个变量并完成变量的赋值 能够说出每一种具体的数据类型 能够数据类型之间的相互转化 能够掌握各种运算符的作用 序言 JavaScript发展历史(js) JavaScript是什么? J...

VS2015错误—严重性代码说明项目文件行 禁止显示状态错误 C4996 fopen(‘fscanf‘、strcmp):This function or variable may be unsafe.

在运行时碰到下列错误: 看错误输出,需要将fopen改为fopen_s; 1.最普通的解决方法,就是使用fopen_s替代, fopen_s()函数的用法:fopen_s(_Outptr_result_maybenull_ FILE ** _File, _In_z_ const char * _Filename, _In_z_ const char * _Mode); fopen()函数的用法:f...

Vuejs——前端学习日记(二)

Vuejs——前端学习日记(二) Vue列表显示 新的指令和属性 后续 通过之前对Vuejs进行简单项目的学习,让我对Vuejs有了初步的认识,接下来是进一步的了解。 Vue列表显示 与之前看到的声明的简单变量message,name相比,数据列表是一个更加复杂的数据,所以在显示方面也会有所不同。在HTML代码中,我们会用v-for指令来显示列表。 如果用之前那样的方法来显...

设计模式之适配器模式

一、适配器模式的背景 在现实生活中,经常出现两个对象因接口不兼容而不能在一起工作的实例,这时需要第三者进行适配。例如,讲中文的人同讲英文的人对话时需要一个翻译,用直流电的笔记本电脑接交流电源时需要一个电源适配器,用计算机访问照相机的 SD 内存卡时需要一个读卡器等。 在软件设计中也可能出现:需要开发的具有某种业务功能的组件在现有的组件库中已经存在,但它们与当前系统的接口规范不兼容,如果重新开发这些...

Spring 4.x遇到的连接数据库拒绝访问

使用了Spring JdbcTemplate连接数据库, 提示 ‘Access denied for user ‘lil’@’192.168.3.104’ (using password: YES)’ , 问题记录与解决 使用了两种方法连接数据库 使用Java读取properties配置文件读取数据库配置(没有使用spring...

猜你喜欢

Day80.html的基本内容 -HTML和CSS

Html和CSS Javase → C/S模式 → Client / Server Javaweb→B/S模式 → Browser / Server 1. 前端BS软件结构 2. 前端的开发流程 3. 网页的组成部分 页面组成: 内容(结构)、表现、行为。 4. HTML简介 5… 创建HTML文件 创建一个web工程(静态的web工程) 在工...

排序算法

(1)冒泡排序 (2)快速排序参考博客(快排原理) 参考博客(形象化过程) 快排原理: 在要排的数(比如数组A)中选择一个中心值key(比如A[0]),通过一趟排序将数组A分成两部分,其中以key为中心,key右边都比key大,key左边的都key小,然后对这两部分分别重复这个过程,直到整个有序。 整个快排的过程就简化为了一趟排序的过程,然后递归调用就行了。 一趟排序的方法: 1,定义i=0,j=...

如何在Windows中获取Mac地址?

Mac Address is a network address used to layer 2 network traffic. The communication is done between network nodes with the mac address. It is important part of computer networking. In this tutorial, w...

使用stm32cubemx快速生成fatfs例程

使用stm32cubemx快速生成fatfs例程 前言 1. cubemx生成过程 1.1 sdio相关配置 1.2 系统时钟树配置 1.3 fatfs配置 1.4 修改工程的栈空间 2. 修改工程代码 2.1 fatfs sd卡读写文件的流程 2.2 具体代码的实现 3.实验现象 前言 本文将介绍如何使用stm32cubemx快速生成一个stm32 sdio 接口的fatfs例程,并实现对sd卡...

史上最简单maven配置与使用

什么是maven maven是用来帮助开发者管理项目所依赖的jar包的一款工具,创建maven工程使得开发不必再自己下载jar包,放入项目文件作为依赖,开发者只需在pom.xml文件里加入依赖的坐标即刻 什么是坐标 关于scope 配置 1、官网下载 2、环境配置 此电脑->属性->高级系统设置->环境变量 新建系统变量,变量名maven_home,变量值maven安装目录 系统...