MySQL分区之LIST分区详解

目录
  • 介绍
  • 一、创建分区
    • 1.插入数据
    • 2.插入不在列表中的值
  • 二、分区管理
    • 1.增加分区
    • 2.合并分区
    • 3.拆分分区
    • 4.删除分区
  • 三、其它分区
    • 1.对时间字段进行分区
  • 四、移除表的分区
  • 参考：
  • 总结

介绍

LIST分区和RANGE分区非常的相似，主要区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合。二者在语法方面非常的相似。同样建议LIST分区列是非null列，否则插入null值如果枚举列表里面不存在null值会插入失败，这点和其它的分区不一样，RANGE分区会将其作为最小分区值存储，HASH\KEY分为会将其转换成0存储，主要LIST分区只支持整形，非整形字段需要通过函数转换成整形；5.5版本之后可以不需要函数转换使用LIST COLUMN分区支持非整形字段，在COLUMN分区中有详细的讲解。

一、创建分区

List各个分区枚举的值只需要不相同即可，没有固定的顺序。

CREATE TABLE tblist (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION a VALUES IN (1,5,6),
    PARTITION b VALUES IN (2,7,8),
    PARTITION c VALUES IN (3,9,10),
    PARTITION d VALUES IN (4,11,12)
);
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblist';

1.插入数据

insert into tblist(id,store_id) values(1,1),(7,7);

往a、b两个分区中各插入一条记录

2.插入不在列表中的值

当往分区中插入不在枚举列表中的值是会插入失败，插入null值如果null值不在枚举列表中也同样失败

二、分区管理

1.增加分区

ALTER TABLE tblist ADD PARTITION (PARTITION e VALUES IN (20));

注意：不能增加包含现有任意值的分区。

2.合并分区

ALTER TABLE tblist REORGANIZE PARTITION  a,b INTO (PARTITION m VALUES IN (1,5,6,2,7,8));

将分区a,b合并为分区m

注意：同RANGE分区一样，只能合并相邻的几个分区，不能跨分区合并。例如不能合并a,c两个分区，只能通过合并a,b,c

3.拆分分区

ALTER TABLE tblist REORGANIZE PARTITION  a,b,c INTO
(PARTITION n VALUES IN (1,5,6,3,9,10),
PARTITION m VALUES IN (2,7,8));

ALTER TABLE tblist REORGANIZE PARTITION  n INTO
    ( PARTITION a VALUES IN (1,5,6),
    PARTITION b VALUES IN (3,9,10));

经过两轮的拆分，枚举列表（3,9,10）排到了（2,7,8）的前面去了；其实是这样的，一开始合并abc成nm两个分区由于n中的枚举值小于m所以n在m的前面，后面再拆分n分区由于n分区在m分区的前面所以拆分出来的分区也是排在m分区的前面，由于a分区的值小于b分区的值所以a排在b的前面。

注意：1.在5.7.12版本中测试发现，合并和拆分分区重新定义的枚举值可以不是原来的值，如果原来的枚举值包含了数据而新合并或拆分的分区枚举值又不不包含原来的枚举值会造成数据丢失。虽然不知道为什么mysql不会禁止该行为，但是人为的要求无论是合并还是拆分分区枚举值保持不变，或者只能增加不能减少，这样能保证数据不丢失。

2.合并和拆分后的分区由于是相邻的分区进行合并和拆分会根据原本的分区的值新的分区也会在原本的分区的顺序位置。

4.删除分区

ALTER TABLE tblist DROP PARTITION e;

注意：删除分区同时会将分区中的数据删除，同时枚举的list值也被删除，后面无法往表中插入该值的数据。

三、其它分区

1.对时间字段进行分区

CREATE TABLE listdate (
    id INT NOT NULL,
    hired DATETIME NOT NULL
)
PARTITION BY LIST( YEAR(hired) )
(
    PARTITION a VALUES IN (1990),
    PARTITION b VALUES IN (1991),
    PARTITION c VALUES IN (1992),
    PARTITION d VALUES IN (1993)
);

ALTER TABLE listdate ADD INDEX ix_hired(hired);

INSERT INTO listdate() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(1,'1992-01-01 10:00:00');
EXPLAIN SELECT * FROM listdate WHERE hired='1990-01-01 10:00:00';

LIST分区也支持对非整形的时间类型字段的转换分区。

四、移除表的分区

ALTER TABLE tablename
REMOVE PARTITIONING ;

注意：使用remove移除分区是仅仅移除分区的定义，并不会删除数据和drop PARTITION不一样，后者会连同数据一起删除

参考：

RANGE分区：https://www.jb51.net/article/244269.htm

COLUMN分区：https://www.jb51.net/article/96515.htm

HASH分区：https://www.jb51.net/article/244277.htm

KEY分区：https://www.jb51.net/article/244282.htm

子分区：https://www.jb51.net/article/244294.htm

指定各分区路径：https://www.jb51.net/article/244296.htm

分区索引以及分区介绍总结：https://www.jb51.net/article/244300.htm

总结

重新定义LIST分区时只能重新定义相邻的分区，不能跳过分区定义，重新定义的分区列表枚举必须包含原分区的列表枚举，如果丢失某个包含记录的枚举值那么数据也将被删除；重新定义分区不能改变分区的类型。

到此这篇关于MySQL分区之LIST分区的文章就介绍到这了,更多相关MySQL LIST分区内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • 创建mysql表分区的方法

    表分区是最近才知道的哦 ,以前自己做都是分表来实现上亿级别的数据了,下面我来给大家介绍一下mysql表分区创建与使用吧,希望对各位同学会有所帮助.表分区的测试使用,主要内容来自于其他博客文章以及mysql5.1的参考手册mysql测试版本:mysql5.5.28mysql物理存储文件(有mysql配置的datadir决定存储路径)格式简介数据库engine为MYISAM frm表结构文件,myd表数据文件,myi表索引文件.INNODB engine对应的表物理存储文件innodb的数据库的物理

  • MySQL分区表的局限和限制详解

    禁止构建 分区表达式不支持以下几种构建: 存储过程,存储函数,UDFS或者插件 声明变量或者用户变量 可以参考分区不支持的SQL函数 算术和逻辑运算符 分区表达式支持+,-,*算术运算,但是不支持DIV和/运算(还存在,可以查看Bug #30188, Bug #33182).但是,结果必须是整形或者NULL(线性分区键除外,想了解更多信息,可以查看分区类型). 分区表达式不支持位运算:|,&,^,<<,>>,~ . HANDLER语句 在MySQL 5.7.1之前的分区表不

  • MySQL数据库分区功能的使用教程

    MySQL数据库分区功能的使用教程

    零,什么是数据库分区 来说一下什么是数据库分区,以mysql为例.mysql数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面(可以通过my.cnf中的datadir来查看),一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表索引的.如果一张表的数据量太大的话,那么myd,myi就会变的很大,查找数据就会变的很慢,这个时候我们可以利用mysql的分区功能,在物理上将这一张表对应的三个文件,分割成许多个小块,这样呢,我们查找一条

  • MySQL的表分区详解

    一.什么是表分区通俗地讲表分区是将一大表,根据条件分割成若干个小表.mysql5.1开始支持数据表分区了.如:某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地将表分区.当然也可根据其他的条件分区. 二.为什么要对表进行分区为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率.分区的一些优点包括:      1).与单个磁盘或文件系统分区相比,可以存储更多的数据.      2).对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的

  • mysql的分区技术详细介绍

    一.概述 当 MySQL的总记录数超过了100万后,会出现性能的大幅度下降吗?答案是肯定的,但是,性能下降>的比率不一而同,要看系统的架构.应用程序.还有>包括索引.服务器硬件等多种因素而定.当有网友问我这个问题的时候,我最常见的回答>就是:分表,可以根据id区间或者时间先后顺序等多种规则来分表.分表很容易,然而由此所带来的应用程序甚至是架构方面的改动工作却不>容小觑,还包括将来的扩展性等. 在以前,一种解决方案就是使用 MERGE 类型,这是一个非常方便的做饭.架构和程序基本上不

  • mysql中如何判断是否支持分区

    mysql可以通过下面语句判断是否支持分区: SHOW VARIABLES LIKE '%partition%'; 如果输出: have_partitioning   YES 表示支持分区. 或者通过: SHOW PLUGINS; 显示所有插件,如果有partition ACTIVE STORAGE ENGINE GPL 插件则表明支持分区 ps:什么是数据库分区 前段时间写过一篇关于mysql分表的的文章,下面来说一下什么是数据库分区,以mysql为例.mysql数据库中的数据是以文件的形势存

  • MySQL分区表的正确使用方法

    MySQL分区表概述 我们经常遇到一张表里面保存了上亿甚至过十亿的记录,这些表里面保存了大量的历史记录. 对于这些历史数据的清理是一个非常头疼事情,由于所有的数据都一个普通的表里.所以只能是启用一个或多个带where条件的delete语句去删除(一般where条件是时间). 这对数据库的造成了很大压力.即使我们把这些删除了,但底层的数据文件并没有变小.面对这类问题,最有效的方法就是在使用分区表.最常见的分区方法就是按照时间进行分区. 分区一个最大的优点就是可以非常高效的进行历史数据的清理. 1.

  • mysql数据存储过程参数实例详解

    MySQL 存储过程参数有三种类型:in.out.inout.它们各有什么作用和特点呢? 一.MySQL 存储过程参数(in) MySQL 存储过程 "in" 参数:跟 C 语言的函数参数的值传递类似, MySQL 存储过程内部可能会修改此参数,但对 in 类型参数的修改,对调用者(caller)来说是不可见的(not visible). drop procedure if exists pr_param_in; create procedure pr_param_in ( in id

  • redhat7.1 安装mysql 5.7.10步骤详解(图文详解)

    redhat7.1 安装mysql 5.7.10步骤详解(图文详解)

    在redhat下安装MySQL,步骤如下 Mysql目录安装位置:/usr/local/mysql 数据库保存位置:/data/mysql 日志保存位置:/data/log/mysql 下载安装包 http://downloads.mysql.com/archives/community/ 1. 获取mysql安装包,mysql-5.7.10-Linux-glibc2.5-x86_64.tar解压后目录如下. 2. 解压mysql-5.7.10-linux-glibc2.5-x86_64.tar

  • mysql 的replace into实例详解

    mysql 的replace into实例详解 replace into 跟 insert 功能类似,不同点在于:replace into 首先尝试插入数据到表中. 1.如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据. 2. 否则,直接插入新数据. 要注意的是:插入数据的表必须有主键或者是唯一索引!否则的话,replace into 会直接插入数据,这将导致表中出现重复的数据.  MySQL中replace into有三种写法: 代码如下: replac

  • PHP和MYSQL实现分页导航思路详解

    PHP和MYSQL实现分页导航思路详解

    预期效果 思路 通过SQL语句 SELECT * FROM table LIMIT start end 来从MySql数据库 步骤 传入页码p: 根据页码获取数据php->mysql 显示数据+分页条 源码 github 链接 注意点 table,input,button等控件的样式不会继承body,需要重新定义如下 input,label, select,option,textarea,button,fieldset,legend,table{ font-size:18px; FONT-FAM

  • CentOS 6.6 源码编译安装MySQL 5.7.18教程详解

    一.添加用户和组 1.添加mysql用户组 # groupadd mysql 2.添加mysql用户 # useradd -g mysql -s /bin/nologin mysql -M 二.查看系统中是否安装mysql,如果安装需要卸载 # rpm -qa | grep mysql mysql-libs-5.1.73-3.el6_5.x86_64 # rpm -e mysql-libs-5.1.73-3.el6_5.x86_64 --nodeps 三.安装所需依赖包 # yum -y ins

  • MySQL对中文进行排序详解及实例

    MySQL对中文进行排序详解 MySQL默认只支持对日期.时间和英文字符串进行排序,如果对中文进行order by很可能得不到想要的结果,如下面的查询并不会按我们所想的根据汉字的拼音进行排序: SELECT * from user order by user_name; 如果相对中文进行排序的话,可以使用CONVERT(coloum_name USING GBK)将中文转为GBK编码形式,然后再排序,就可以实现根据汉子的拼音进行排序: SELECT * from user order by CO

  • MySQL binlog 远程备份方法详解

    以前备份binlog时,都是先在本地进行备份压缩,然后发送到远程服务器中.但是这其中还是有一定风险的,因为日志的备份都是周期性的,如果在某个周期中,服务器宕机了,硬盘损坏了,就可能导致这段时间的binlog就丢失了. 而且,以前用脚本对远程服务器进行备份的方式,有个缺点:无法对MySQL服务器当前正在写的二进制日志文件进行备份.所以,只能等到MySQL服务器全部写完才能进行备份.而写完一个binlog的时间并不固定,这就导致备份周期的不确定. 从MySQL5.6开始,mysqlbinlog支持将

  • mysql常用日期时间/数值函数详解(必看)

    1.日期时间函数 时间转化秒函数:time_to_sec MySQL> select time_to_sec('01:01:01'); +-------------------------+ | time_to_sec('01:01:01') | +-------------------------+ | 3661 | +-------------------------+ 1 row in set (0.00 sec) 秒转化时间函数:sec_to_time mysql> select se

  • Linux上安装Mysql及简单的使用详解

    1. 安装mysql sudo apt-get update sudo apt-get install mysql-server sudo apt-get install python-mysqldb(如果python中要使用,请安装) 2.登录root用户: mysql -u root -p 3.查看所有数据库: show databases; 4.选择一个数据库操作: use database_name; 5.查看当前数据库下所有的表: show tables; 6.创建一个数据库: cre

  • MySQL加密和解密实例详解

    MySQL加密和解密实例详解

    MySQL加密和解密实例详解 数据加密.解密在安全领域非常重要.对程序员而言,在数据库中以密文方式存储用户密码对入侵者剽窃用户隐私意义重大. 有多种前端加密算法可用于数据加密.解密,下面我向您推荐一种简单的数据库级别的数据加密.解密解决方案.以MySQL数据库为例,它内建了相应的加密函数(AES_ENCRYPT() )和解密函数(AES_DECRYPT()). 在建表的时候,要注意字段的类型.如下图所示: 在表中插入加密数据 上面的插入语句有三个字段,"用户名"."密码&qu

随机推荐