总结MySQL的分区

前言

分区是指根据一定的规则将一个大表分解成多个更小的部分，这里的规则一般就是利用分区规则将表进行水平切分；逻辑上没有发生变化但实际上表已经被拆分成了多个物理对象，每个分成被划分成了一个独立的对象。相对于没有分区的当个表而言分区的表有很多的优势包括： 并发统计查询、快速归档删除分区数据、分散存储、查询性能更佳。

mysql5.7以后查询语句支持指定分区例如：“ SELECT * FROM t PARTITION (p0,p1) WHERE c < 5 ”指定分区同样适用DELETE, INSERT, REPLACE, UPDATE, and LOAD DATA, LOAD XML.

数据库版本：mysql5.7.12

是否支持分区

SHOW PLUGINS ;

查询partition的的状态是active就代表支持分区，如果是源码安装的话在编译的过程中要添加“-DWITH_PARTITION_STORAGE_ENGINE=1 \”。

注意： MERGE, CSV, or FEDERATED存储引擎不支持分区，同一个表所有的分区必须使用相同的存储引擎，不能分区1使用MYISAM分区2又使用INNODB；不同的分区表可以是不同的存储引擎。

分区介绍

目前mysql可用的分区类型主要有以下几种：

RANGE分区：基于一个给定的连续区间范围，RANGE主要是基于整数的分区，对于非整形的字段需要利用表达式将其转换成整形。

LIST分区：是基于列出的枚举值列表进行分区。

COLUMNS分区：可以无需通过表达式进行转换直接对非整形字段进行分区，同时COLUMNS分区还支持多个字段组合分区，只有RANGELIST存在COLUMNS分区，COLUMNS是RANGE和LIST分区的升级。

HASH分区：基于给定的分区个数，将数据分配到不同的分区，HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数。

KEY分区：支持除text和BLOB之外的所有数据类型的分区,key分区可以直接基于字段做分区无需转换成整数。

说明

1.注意分区名的大小写敏感问题，和关键字问题。

2.无论哪种分区类型，要么分区表中没有主键或唯一键，要么主键或唯一键包含在分区列里面，对于存在主键或者唯一键的表不能使用主键或者唯一键之外的字段作为分区字段。

3.5.7以前的版本显示分区的执行计划使用：explain PARTITIONS；5.7以后直接执行：explain

4.没有强制要求分区列非空，建议分区的列为NOT NULL的列；在RANGE 分区中如果往分区列中插入NULL值会被当作最小的值来处理，在LIST分区中NULL值必须在枚举列表中否则插入失败，在HASH/KEY分区中NULL值会被当作0来处理。

5.基于时间类型的字段的转换函数mysql提供了"YEAR(),MONTH(),DAY(),TO_DAYS(),TO_SECONDS(),WEEKDAY(),DAYOFYEAR()"

6.拆分合并分区后会导致修改的分区的统计信息失效,没有修改的分区的统计信息还在,不影响新插入的值加入到统计信息；这时需要对表执行Analyze操作.

7.针对非整形字段进行RANG\LIST分区建议使用COLUMNS分区。

删除增加分区

在每个分区内容介绍中详细介绍了每种分区的用法，但是都是介绍在创建表的时候创建分区和修改删除分区单个，也可以在一张已经存在的表中加入分区，可以一次性删除整个表的分区。

1.移除表的分区

ALTER TABLE tablename
REMOVE PARTITIONING ;

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

2.对已经存在记录的表创建分区，以增加range分区为例，和创建表建分区的语法一样。

ALTER TABLE `tb_partition`.`tb_varchar`
PARTITION BY RANGE(id) PARTITIONS 3( PARTITION part0 VALUES LESS THAN (5000), PARTITION part1 VALUES LESS THAN (10000), PARTITION part2 VALUES LESS THAN (MAXVALUE)) ;

注意：对已有的表创建分区之后，数据会按照分区的定义分布到各个分区文件当中

总结

以上就是MySQL的分区介绍总结的全部内容，希望本文对大家使用mysql的时候有所帮助。

赞 (0)

相关推荐

  • 基于MySQL分区性能的详细介绍

    一,      分区概念  分区允许根据指定的规则,跨文件系统分配单个表的多个部分.表的不同部分在不同的位置被存储为单独的表.MySQL从5.1.3开始支持Partition. 分区和手动分表对比 手动分表  分区 多张数据表 一张数据表 重复数据的风险 没有数据重复的风险 写入多张表 写入一张表 没有统一的约束限制 强制的约束限制 MySQL支持RANGE,LIST,HASH,KEY分区类型,其中以RANGE最为常用: Range(范围)–这种模式允许将数据划分不同范围.例如可以将一个表通过年

  • mysql分区功能详解,以及实例分析

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

  • 创建mysql表分区的方法

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

  • MySQL中表分区技术详细解析

    MySQL 分区技术(是mysql 5.1以版本后开始用->是甲骨文mysql技术团队维护人员以插件形式插入到mysql里面的技术) 1.概述 数据库单表到达一定量后,性能会有衰减,像mysql\sql server等犹为明显,所以需要把这些数据进行分区处理.同时有时候可能出现数据剥离什么的,分区表就更有用处了! MySQL 5.1 中新增的分区(Partition)功能就开始增加,优势也越来越明显了: 与单个磁盘或文件系统分区相比,可以存储更多的数据 很容易就能删除不用或者过时的数据 一些查询

  • mysql分表和分区的区别浅析

    一.什么是mysql分表和分区 什么是分表,从表面意思上看呢,就是把一张表分成N多个小表 什么是分区,分区呢就是把一张表的数据分成N多个区块,这些区块可以在同一个磁盘上,也可以在不同的磁盘上 二.mysql分表和分区有什么区别呢 1.实现方式上 a)mysql的分表是真正的分表,一张表分成很多表后,每一个小表都是完正的一张表,都对应三个文件,一个.MYD数据文件,.MYI索引文件,.frm表结构文件. 复制代码 代码如下: [root@BlackGhost test]# ls |grep use

  • mysql的分区技术详细介绍

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

  • MySQL交换分区的实例详解

    MySQL交换分区的实例详解 前言 在介绍交换分区之前,我们先了解一下 mysql 分区. 数据库的分区有两种:水平分区和垂直分区.而MySQL暂时不支持垂直分区,因此接下来说的都是水平分区.水平分区即:以行为单位对表进行分区.比如:按照时间分区,每一年一个分区等. 在MySQL中,分区是可以交换的,可以将一个分区表中的一个分区和一个普通表中的数据互换. 交换分区的实现 1.交换分区的语法 alter table pt exchange partition p with table nt; 解释

  • 总结MySQL的分区

    总结MySQL的分区

    前言 分区是指根据一定的规则将一个大表分解成多个更小的部分,这里的规则一般就是利用分区规则将表进行水平切分:逻辑上没有发生变化但实际上表已经被拆分成了多个物理对象,每个分成被划分成了一个独立的对象.相对于没有分区的当个表而言分区的表有很多的优势包括: 并发统计查询.快速归档删除分区数据.分散存储.查询性能更佳. mysql5.7以后查询语句支持指定分区例如:" SELECT * FROM t PARTITION (p0,p1) WHERE c < 5 "指定分区同样适用DELET

  • 深入浅析MySQL COLUMNS分区

    深入浅析MySQL COLUMNS分区

    介绍 COLUMN分区是5.5开始引入的分区功能,只有RANGE COLUMN和LIST COLUMN这两种分区:支持整形.日期.字符串:RANGE和LIST的分区方式非常的相似. COLUMNS和RANGE和LIST分区的区别 1.针对日期字段的分区就不需要再使用函数进行转换了,例如针对date字段进行分区不需要再使用YEAR()表达式进行转换. 2.COLUMN分区支持多个字段作为分区键但是不支持表达式作为分区键. COLUMNS支持的类型 整形支持:tinyint,smallint,med

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

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

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

  • mysql表分区的使用与底层原理详解

    目录 什么是分区表 分区表应用场景 分区表的限制 分区类型 分区表的使用 1.范围分区 2.列表分区(list分区) 3.列分区 4.hash分区 5.秘钥分区(key分区) 6.子分区 添加分区 分区表原理 如何使用分区表 注意事项 总结 什么是分区表 MySQL从5.1版本开始支持分区功能,分区是将一个表的数据按照某种方式,比如按照时间上的月份,分成多个较小的,更容易管理的部分,但是逻辑上仍是一个表. 还没出现分区表的时候,所有的数据都是存放在一个文件里面的,如果数据量太大,查询数据时总是避

  • 一文读懂MySQL 表分区

    一文读懂MySQL 表分区

    目录 1. 什么是表分区 2. 分区的两种方式 2.1 水平切分 2.2 垂直切分 3. 为什么需要表分区 4. 分区实践 4.1 RANGE 分区 4.2 LIST 分区 4.3 HASH 分区 4.4 KEY 分区 4.5 COLUMNS 分区 5. 常见分区命令 6. 小结 松哥之前写过文章跟大家介绍过用 MyCat 实现 MySQL 的分库分表,不知道有没有小伙伴研究过,MySQL 其实也自带了分区功能,我们可以创建一个带有分区的表,而且不需要借助任何外部工具,今天我们就一起来看看. 1

  • MySql创建分区的方法实例

    MySql创建分区的方法实例

    目录 一.Mysql分区类型 二.RANGE分区 二.Hash分区 三.Key分区 四.List分区 附:查询分区,各区表数据量 总结 友情链接 一.Mysql分区类型 1.RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区. 2.HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算.这个函数可以包含MySQL中有效的.产生非负整数值的任何表达式. 3.KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列

  • MySQL表分区的几种实现

    MySQL表分区的几种实现

    目录 关于分区以及为什么要分区 水平分区的几种类型及demo 1.range分区 2.list分区 3.hash分区 4.key分区 5.子分区(复合分区) 6.columns分区 关于分区以及为什么要分区 我们首先找到mysql的数据存储目录,可以通过语句show variables like '%datadir%';查看,我本机的是"C:\ProgramData\MySQL\MySQL Server 8.0\Data",在该目录下,可以看到每个数据库对应着一个文件夹,对于没有分区的

随机推荐