MySQL高级特性——数据表分区的概念及机制详解

MySQL 的分区的实现方式是对数据表进行一层包装,这意味着索引实际是基于每个分区定义的,而不是整张表。这个特性和 Oracle 是不同的,在 Oracle 中的索引和数据表可以使用更灵活和更复杂的方式进行分区。​

MySQL 的分区通过定义 PATITION BY 子句的条件来决定数据行所属分区的归属。在执行查询的时候,查询优化器会区分所在分区,这意味着查询不会检查全部分区,而仅仅是那些包含索要查询数据所在的分区。​

分区的主要目的是对数据表进行大致形式的索引和聚集。这样可以减少数据表的过大范围的访问,并可以将相关的数据行临近存储。分区的收益是显著的,尤其是对于下面的场景:

  • 当数据表过大导致内存空间难以承载时,或者一张数据表中有很多历史数据以及热区行。
  • 分区数据相比为分区数据更容易维护。例如,通过删除整个分区很容易将旧的数据清除,同时对于单个分区也可以很方便地进行优化、检查和修复操作。
  • 分区数据在物理上可以分布存储,这使得服务器可以更高效地使用多个硬盘驱动器。
  • 可以使用分区避免某些工作负荷的瓶颈。
  • 对于数据备份而言,可以单独备份或恢复单个分区,这对于大的数据集来说十分有益。

MySQL 的分区实现细节十分复杂,弄清楚是很难的,我们只需要关注它的性能即可。如果想进一步了解,可以翻阅 MySQL 手册中关于分区的部分。有了分区后,也带来了其他问题以及限制:

  • 创建表和更改表的命令更为复杂。
  • 每张表最多只能有1024个分区。
  • 在 MySQL 5.1版本中,分区表达式必须是整数或者返回一个整数;在 MySQL 5.5以后,在某些情况下可以使用列进行分区。
  • 任主键或唯一索引都必须包含分区表达式中的全部列。
  • 不可以使用外键约束。

分区机制

如前所述,分区表实际有多个隐藏的物理存储表,这通过句柄对象呈现。我们不能直接访问分区。通常,每个分区是通过存储引擎管理的(因此要求所有分区的存储引擎相同),而数据表中的索引实际上是隐藏的物理存储表的索引。从存储引擎的角度来看,分区也是数据表。存储引擎实际并不知道数据表是独立的还是一个大的数据表的一个分区。对于分区表的操作通过如下的逻辑操作实现:

SELECT 查询

当对分区表进行查询时,分区层会打开和锁定全部的隐藏分区,查询优化器会决定那些隐藏分区可以忽略,然后分区层通过句柄 API 调用管理分区的存储引擎获取查询结果。

INSERT 操作

当插入一行数据时,分区层会打开和锁定全部分区,然后决定那个分区存储当前的数据行,并将该数据行存入对应分区。

DELETE 操作

删除一行数据时,分区层会打开和锁定全部分区,检查哪个分区包含该行数据,再将删除请求发送到该分区。

UPDATE 操作

修改一行数据时,分区层打开和锁定所有分区,检查哪个分区包含该行数据,并获取该行数据进行修改,然后再确定哪个分区应当包含新的数据行,并把插入请求发送到该分区,同时发送删除请求到旧的分区。
上面的有些操作支持分区过滤(即忽略无关的分区)。例如,删除一行时,服务器需要首先定位数据行位置。如果在 WHERE 条件中指定了匹配的分区表达式条件,服务器可以忽略掉不包含该行的分区。对于 UPDATE 操作也是类似,而 INSERT 操作本身就是如此,服务器会只查找需要插入的一个分区,而不是全部。

虽然分区层打开和锁定了全部分区,但并不意味着分区会保持锁定。像 InnoDB 的存储引擎,可以支持行级别的锁定,会只是分区层解除分区的锁定。这个加锁和解锁的过程和普通的 InnoDB 数据表的锁定过程类似。

分区的类型

MySQL 支持几种类型的分区,最常用的类型是范围分区——也就是针对某些列的的值或表达式按不同的范围进行分区。例如,下面的语句就是根据年份将销售数据分到不同的分区中:

CREATE TABLE sales (
  order_date DATETIME NOT NULL
  --其他列定义
) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) (
  PARTITION p_2018 VALUES LESS THAN (2018),
  PARTITION p_2019 VALUES LESS THAN (2019),
  PARTITION p_2020 VALUES LESS THAN (2020),
  PARTITION p_other VALUES LESS THAN MAXVALUE);

可以在分区子句中使用多种函数。最主要的要求是必须返回一个非常量的,确定的整数。在上面的例子中使用的是 YEAR 函数,也可以使用其他函数,例如 TO_DAYS()。使用时间间隔进行分区是基于日期数据的常用方式。​

MySQL 也支持键,哈希以及列表的分区方法,有些还支持子分区(实际很少用)。在 MySQL 5.5以后,可u一使用 RANGE COLUMNS 的分区类型直接按基于日期的列进行分区,而不需要使用函数将日期转换为整数。 其他常见的分区技巧包括:

  • 使用键进行分区以减少 InnoDB 的互斥量的竞争;
  • 可以使用取余计算的方法来循环构建范围的分区,例如如果只需要保持最近几天的数据,可以通过对日期对7取余,或者使用所在的周天数进行分区。
  • 假设数据表没有自增的主键,但是也想对聚集在一起的热区数据分区。由于时间戳不在主键里,也无法使用时间戳分区。这时候可以使用 HASH(id DIV 1000000),这会在每1000000行数据进行分区。这使得无需更改主键也能完成我们要的效果。同时这样还有附加的效果。那就是我们无需创建分区的常量去保留新的数据。

以上就是MySQL高级特性——数据表分区的概念及机制详解的详细内容,更多关于MySQL高级特性 数据表分区的资料请关注我们其它相关文章!

(0)

相关推荐

  • 详解MySQL分区表

    前言: 分区是一种表的设计模式,通俗地讲表分区是将一大表,根据条件分割成若干个小表.但是对于应用程序来讲,分区的表和没有分区的表是一样的.换句话来讲,分区对于应用是透明的,只是数据库对于数据的重新整理.本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场景,有需要的朋友可以参考一下,希望对你有所帮助. 1.分区的目的及分区类型 MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据.在执行查询的时候,优化器根据分区定义过滤那些没有我们需要的数据的分区,这

  • MySQL分区表的最佳实践指南

    前言: 分区是一种表的设计模式,通俗地讲表分区是将一大表,根据条件分割成若干个小表.但是对于应用程序来讲,分区的表和没有分区的表是一样的.换句话来讲,分区对于应用是透明的,只是数据库对于数据的重新整理.本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场景,有需要的朋友可以参考一下,希望对你有所帮助. 1.分区的目的及分区类型 MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据.在执行查询的时候,优化器根据分区定义过滤那些没有我们需要的数据的分区,这

  • python 实现mysql自动增删分区的方法

    连接mysql #!/usr/bin/python #-*- coding:utf-8 -*- import time import pymysql class connect_mysql(object): def __init__(self, host, dbname): self.mysql_config = { 'host': host, 'port': 33071, 'user': 'sysbench', 'passwd': '970125', 'db': dbname, 'charse

  • MySql分表、分库、分片和分区知识点介绍

    一.前言 数据库的数据量达到一定程度之后,为避免带来系统性能上的瓶颈.需要进行数据的处理,采用的手段是分区.分片.分库.分表. 二.分片(类似分库) 分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题.Shard这个词的意思是"碎片".如果将一个数据库当作一块大玻璃,将这块玻璃打碎,那么每一小块都称为数据库的碎片(DatabaseShard).将整个数据库打碎的过程就叫做分片,可以

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

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

  • MySQL最佳实践之分区表基本类型

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

  • MySQL学习记录之KEY分区引发的血案

    需求背景 业务表tb_image部分数据如下所示,其中id唯一,image_no不唯一.image_no表示每个文件的编号,每个文件在业务系统中会生成若干个文件,每个文件的唯一ID就是字段id: 业务表tb_image的一些情况如下: 根据image_no查询和根据id查询: 存量数据2kw: 日增长4w左右: 日查询量20w左右: 非ToC系统,所以并发的天花板可见: 方案选择 根据上面对业务的分析,分库分表完全没有必要.单库分表的话,由于要根据image_no和id查询,所以,一种方案是冗余

  • MySql分表、分库、分片和分区知识深入详解

    一.前言 数据库的数据量达到一定程度之后,为避免带来系统性能上的瓶颈.需要进行数据的处理,采用的手段是分区.分片.分库.分表. 二.分片(类似分库) 分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题.Shard这个词的意思是"碎片".如果将一个数据库当作一块大玻璃,将这块玻璃打碎,那么每一小块都称为数据库的碎片(DatabaseShard).将整个数据库打碎的过程就叫做分片,可以

  • Mysql临时表及分区表区别详解

    临时表与内存表 内存表,指的是使用Memory引擎的表,建表语法是create table - engine=memory.这种 表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还在.除了这两个特性看 上去比较"奇怪"外,从其他的特征上看,它就是一个正常的表 临时表,可以使用各种引擎类型 .如果是使用InnoDB引擎或者MyISAM引擎的临时表,写 数据的时候是写到磁盘上的.当然,临时表也可以使用Memory引擎. 临时表特性 建表语法是create temporary ta

  • MySQL分库分表与分区的入门指南

    前言 关系型数据库比较容易成为系统瓶颈,单机存储容量.连接数.处理能力都有限,当数据量和并发量起来之后,就必须对数据库进行切分了. 数据切分(sharding)的手段就是分库分表.分库分表有两方面,可能是光分库不分表,也可能是光分表不分库. 数据库分布式的核心内容无非就是数据切分,以及切分后对数据的定位.整合. 为什么要分库分表 分表 单表数据量太大时,会严重影响sql执行的性能.一般单表到达几百万的时候,性能就会相对差一些了,这时就得分表了. 分表就是把一个表的数据放到多个表中,然后查询的时候

  • MySQL分表和分区的具体实现方法

    垂直分表 垂直分表就是一个包含有很多列的表拆分成多个表,比如表A包含20个字段,现在拆分成表A1和A2,两个表各十个字段(具体如何拆根据业务来选择). 优势:在高并发的情境下,可以减少表锁和行锁的次数. 劣势:在数据记录非常大的情况下,读写速度还是会遇到瓶颈. 水平分表 假如某个网站,它的数据库的某个表已经达到了上亿条记录,那么此时如果通过select去查询,在没有索引的情况下,他的查询会非常慢,那么就可以通过hash算法将这个表分成10个子表(此时每个表的 的数据量只有1000万条了). 同时

随机推荐