MySQL高级特性——数据表分区的概念及机制详解
作者:岛上码农 发布时间:2024-01-28 12:30:28
目录
分区机制
SELECT 查询
INSERT 操作
DELETE 操作
UPDATE 操作
分区的类型
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行数据进行分区。这使得无需更改主键也能完成我们要的效果。同时这样还有附加的效果。那就是我们无需创建分区的常量去保留新的数据。
来源:https://juejin.cn/post/6962141919925403662
猜你喜欢
- 平时写的一些 Python 的代码,需要在其他模块里面复用。最粗狂的方法就是直接 copy 过去。但这种方式太麻烦,copy 一堆代码,导致
- 在利用Keras进行实验的时候,后端为Tensorflow,出现了以下问题:1. 服务器端激活Anaconda环境跑程序时,实验结果很差。环
- 本文主要研究的是Django中migrate和makemigrations的差别,具体如下。在你改动了 model.py的内容之后执行下面的
- 在pycharm中,当调用( import / from … import… )其他文件夹下的函数或模块,会发现编辑器无法识别( can n
- 随着ajax再网站建设中的的大范围流行,XmlHttp也自然被人们所熟悉。本文介绍了asp任何检测服务器端是否支持xmlhttp组件的方法,
- 前言提示:这里可以添加本文要记录的大概内容:将一个EXCEL等份拆成多个EXCEL将多个小EXCEL合并成一个大EXCEL并标记来源提示:以
- 一、浪漫玫瑰花实现代码:from turtle import *import timesetup(1000,800,0,0)speed(0)
- Pycharm中import torch报错问题描述:今天在跑GitHub上一个深度学习的模型,需要引入一个torch包,在pycharm中
- 一、前言在Python中,类表示具有相同属性和方法的对象的集合。在使用类时,需要先定义类,然后再创建类的实例,通过类的实例就可以访问类中的属
- 1.MySQL 5.5命令行里面set global log_slow_queries = on; &nb
- 代码: import os while True: dynamic = input('输入计算表达式:') if dynam
- 本文实例为大家分享了python学生管理系统的具体代码,供大家参考,具体内容如下基于列表存储的学生管理系统,实现如下功能==========
- matplotlib默认根据数据系列自动缩放坐标轴范围。pyplot模块中的autoscale函数可以切换是否自动缩放坐标轴范围,xlim(
- 07年,我是极力推崇网站规范制作,好处很多,对商业,对用户,对设计师。我们走了两年,现在有了一定的成效,我们也看到了规范所带来的好处和便利。
- 前言动态生成表格是前端开发中非常重要的内容,常常是后端返回数据(大多是json类型),我们前端通过js循环来动态添加,所以这部分内容是十分重
- f-string 字符串格式化语法f-string 是 Python 3.6 版本引入的一种新的字符串格式化语法。与其他字符串格式
- 作用:pygame一般用来做游戏注意:1.在使用pygame提供的功能之前,需要调用init方法2.在游戏结束前需要调用 quit 方法py
- 前言在学校太闲,就写了这个程序,可以绘制函数图像,并且可以绘制其导函数图像和不定积分的图像,效果非常不错。效果图说明1,程序无法绘制复数图像
- 下面的代码是日期函数的一些简单运用,应该不用解释,生成当月的日历,当然你可以根据实际情况进行扩充!效果图:<%@LANGUAGE=&q
- 目录logging的简单使用|2logging常见对象|3logging基本使用|4logging之Formatter对象|5logging