代码编织梦想


MySQL45讲

实践篇

43 | 要不要使用分区表?

分区表是什么?

CREATE TABLE `t` (
  `ftime` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);

表 t 的磁盘文件
在这里插入图片描述
按照定义的分区规则,插入的两行记录分别落在 p_2018 和 p_2019 这两个分区上。

这个表包含了一个 .frm 文件和 4 个 .ibd 文件,每个分区对应一个 .ibd 文件。

  • 对于引擎层来说,这是 4 个表;
  • 对于 Server 层来说,这是 1 个表。

.frm 文件由 server 创建,.ibd 文件由存储引擎创建。

分区表的引擎层行为

分区表间隙锁示例
在这里插入图片描述
根据间隙锁加锁规则,session A 的 select 语句对索引 ftime 上这两个记录之间的间隙加了锁。如果是一个普通表,那么 T1 时刻,在表 t 的 ftime 索引上,间隙和加锁状态应该如下图所示:
在这里插入图片描述
sesion B 的两条插入语句应该都要进入锁等待状态。

但是,从分区表间隙锁示例可以看出,session B 的第一个 insert 语句执行成功。因为,对于引擎来说,p_2018 和 p_2019 是两个不同的表,也就是说 2017-4-1 的下一个记录并不是 2018-4-1,而是 p_2018 分区的 supremum。所以 ,T1 时刻,在表 t 的 ftime 索引上,间隙和加锁的状态其实如下图所示:
在这里插入图片描述
show engine innodb status 的部分结果
在这里插入图片描述
MyISAM 表锁验证
在这里插入图片描述
由于 MyISAM 引擎只支持表锁,所以 sessionA 的 update 语句会锁住整个表 t 。

由上图可知,session B 的第一条查询语句可以正常执行,第二条语句才进入锁等待状态。

因为 MyISAM 的表锁是在引擎层实现的,session A 加的表锁,其实是锁在分区 p_2018 上。因此,只会堵住在这个分区上执行的查询,落到其他分区的查询不受影响。

分区策略

每当第一次访问一个分区表的时候,MySQL 需要把所有的分区都访问一遍。 一个典型的报错情况:如果一个分区表的分区很多,比如超过了 1000 个,而 MySQL 启动的时候,open_files_limit 参数使用的是默认值 1024,那么就会在访问这个表的时候,由于需要打开所有的文件,导致打开表文件的个数超过了上限而报错。

创建一个包含了很多分区的表 t_myisam,执行一条插入语句后报错的情况。
在这里插入图片描述
此处用的是 MyISAM 引擎。使用 InnoDB 引擎,并不会出现这个问题。

InnoDB分区表使用了本地分区策略以后,即使分区个数大于open_files_limit ,打开InnoDB分区表也不会报“打开文件过多”这个错误。在InnoDB引擎打开文件超过 innodb_open_files这个值的时候,就会关掉一些之前打开的文件。

MyISAM 分区表使用的分区策略,称为通用分区策略(generic partitioning),每次访问分区都由 server 层控制。 通用分区策略,是 MySQL 一开始支持分区表的时候就存在的代码,在文件管理、表管理的实现上很粗糙,因此有比较严重的性能问题。

从 MySQL 5.7.9 开始,InnoDB 引擎引入了本地分区策略(native partitioning),由 InnoDB 内部自己管理打开分区的行为。

MySQL 从 5.7.17 开始,将 MyISAM 分区表标记为即将弃用 (deprecated),意思是“从这个版本开始不建议这么使用,请使用替代方案,在将来的版本中会废弃这个功能”。

从 MySQL 8.0 版本开始,就不允许创建 MyISAM 分区表了,只允许创建已经实现了本地分区策略的引擎。 目前来看,只有 InnoDB 和 NDB 这两个引擎支持了本地分区策略。

分区表的 server 层行为

从 server 层看,一个分区表就只是一个表。
在这里插入图片描述
虽然 session B 只需要操作 p_2017 这个分区,但是由于 session A 持有整个表 t 的 MDL 锁,就导致了 session B 的 alter 语句被堵住。这也是 DBA 经常说的:分区表,在做 DDL 的时候,影响会更大。

小结:

  • MySQL 在第一次打开分区表的时候,需要访问所有的分区;
  • 在 server 层,认为这是同一张表,因此所有分区共用同一个 MDL 锁;
  • 在引擎层,认为这是不同的表,因此 MDL 锁之后的执行过程,会根据分区表规则,只访问必要的分区。

分区表的应用场景

分区表的一个显而易见的优势是对业务透明,相对于用户分表来说,使用分区表的业务代码更简洁。还有,分区表可以很方便的清理历史数据。

如果一项业务跑的时间足够长,往往就会有根据时间删除历史数据的需求。这时候,按照时间分区的分区表,就可以直接通过 alter table t drop partition …(直接删除分区文件,效果跟 drop 普通表类似。与使用 delete 语句删除数据相比,优势是速度快、对系统影响小。) 删掉分区,从而删掉过期的历史数据。

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/TQ20160412/article/details/122458945

mysql实战43 | 要不要使用分区表?_weixin_33853827的博客-爱代码爱编程

我经常被问到这样一个问题:分区表有什么问题,为什么公司规范不让使用分区表呢?今天,我们就来聊聊分区表的使用行为,然后再一起回答这个问题。 分区表是什么? CREATE TABLE `t` ( `ftime` datetime NOT NULL, `c` int(11) DEFAULT NULL, KEY (`ftime`)

43- 大数据-hive 修改表六:添加分区_大富的大数据之路的博客-爱代码爱编程_hive 增加分区

1、静态分区 1> 添加一个 alter table t2 add partition (city=‘shanghai’); 2> 添加多个 alter table t2 add partition (city=

43 | 要不要使用分区表_sxlxwyr的博客-爱代码爱编程

分区表是什么? 创建一个表t: CREATE TABLE `t` (   `ftime` datetime NOT NULL,   `c` int(11) DEFAULT NULL,   KEY (`ftime`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY RANGE (YEAR(ft

SQL优化(三) PostgreSQL Table Partitioning (数据库分区表 ,数据库内路由分区)-爱代码爱编程

  典型使用场景   随着使用时间的增加,数据库中的数据量也不断增加,因此数据库查询越来越慢。   加速数据库的方法很多,如添加特定的索引,将日志目录换到单独的磁盘分区,调整数据库引擎的参数等。这些方法都能将数据库的查询性能提高到一定程度。   对于许多应用数据库来说,许多数据是历史数据并且随着时间的推移它们的重要性逐渐降低。如果能找到一个办法将这些

43讲要不要使用分区表-爱代码爱编程

我经常被问到这样一个问题:分区表有什么问题,为什么公司规范不让使用分区表呢?今天,我们就来聊聊分区表的使用行为,然后再一起回答这个问题。 分区表是什么? 为了说明分区表的组织形式,我先创建一个表t: CREATE TABLE `t` ( `ftime` datetime NOT NULL, `c` int(11) DEFAULT NULL,

MySQL45讲读书笔记 43讲要不要使用分区表-爱代码爱编程

一序   本文属于极客时间MySQL45讲读书笔记系列。本篇老师主要是讲要不要使用分区表。开发同学要知道其中利弊:容易出现全表锁,适用于简单的大数据量业务场景,复杂的大数据量场景可以考虑分表分库水平扩展方案。 二 分区表是什么? 为了说明分区表的组织形式,我先创建一个表t: CREATE TABLE `t` ( `ftime` datetime

clickhouse分区操作实践-爱代码爱编程

  目录 1 分区表 2 创建分区表 2.1不指定分区 2.2 使用函数创建分区 2.3 使用现有字段直接分区 3 分区操作 3.1 删除分区 3.1.1 删除前分区信息如下 3.1.2 删除前磁盘数据目录 3.1.3 删除前数据如下  3.1.4 删除命令 3.1.5 删除后分区信息 3.1.6 删除后磁盘数据目录 3.1.

43要不要使用分区表?-爱代码爱编程

文章目录 43 | 要不要使用分区表?分区表是什么?分区表的引擎层行为分区策略分区表的 server 层行为分区表的应用场景小结上期问题时间 43 | 要不要使用分区表? 我经常被问到这样一个问题:分区表有什么问题,为什么公司规范不让使用分区表呢?今天,我 们就来聊聊分区表的使用行为,然后再一起回答这个问题。 分区表是什么? 为了说明分区

Linux PE修改GPT分区,2018年第43周-Linux下使用gpt给硬盘分区-爱代码爱编程

由于MBR分区表最大支持2T硬盘,而且主分区数量还有限制. 而GPT分区表就没有上述问题,线上服务器基本都是采用这个来格式化系统, 所以使用gpt进行分区. 方法如下: 1.进入parted工具,输入help可查看帮助 [root@amd ~]# sudo parted GNU Parted 3.1 # 默认使用第一个硬盘 Using /

MySQL优化系列12-MySQL分区表-爱代码爱编程

备注:测试数据库版本为MySQL 8.0 文章目录 一.分区表简介二.分区的类型2.1 range分区2.2 list分区2.3 colums分区2.3.1 RANGE COLUMNS分区2.3.2 LIST COLUMNS2.4 哈希分区2.4.1 LINEAR HASH分区2.5 key 分区2.6 子分区2.7 MySQL分区如何处理nul

PostgreSQL数据库表分区介绍-四种分区方式-爱代码爱编程

典型使用场景         随着使用时间的增加,数据库中的数据量也不断增加,因此数据库查询越来越慢。         加速数据库的方法很多,如添加特定的索引,将日志目录换到单独的磁盘分区,调整数据库引擎的参数等。这些方法都能将数据库的查询性能提高到一定程度。         对于许多应用数据库来说,许多数据是历史数据并且随着时间的推移它们的重要性逐

MySQL别再 select * 了这里有 12 个查询技巧-爱代码爱编程

应用程序慢如牛,原因多多,可能是网络的原因、可能是系统架构的原因,还有可能是数据库的原因。 那么如何提高数据库SQL语句执行速度呢?有人会说性能调优是数据库管理员(DBA)的事,然而性能调优跟程序员们也有莫大的关系。 程序中嵌入的一行行的SQL语句,如果使用了一些优化小技巧,定能达到事半功倍的效果。 技巧1  比较运算符能用 “=”就不用“<

MySQL索引-爱代码爱编程

基础知识 索引是创建在表上的,对数据库表中一列或多列的值进行排序的一种结构,可以提高查询的速度。 通俗的来说,数据库中存储的数据比作字典的话,索引就相当于是字典中的目录。如果没有索引,查找一个数据就需要从第一页开始全局检索直至找到需要的诗句,有了索引可以先在目录中根据拼音查找到该数据所在的页数,因此通过索引可以大大减少了查询时间, 存储类型

CentOS7使用yum安装MySql并开启远程-爱代码爱编程

前言:既然安装linux云服务器上mysql,准备工作,需要做好并且需要细致,本教程简单快捷,比网上的其他的改各种配置文件简单方便快捷。好了废话不多说,接下来就开始mysql安装之旅 以阿里云(云服务器ECS_云主机_服务器托管_弹性计算-阿里云)和腾讯云服务器为例,本人因为不想在本地安装数据库,就购买了便宜又好用的云服务器,不仅可以部署项目还可以当远程

windows 安装mysql_mycli安装-爱代码爱编程

windows 安装mysql 1 下载mysql2 配置3 安装mysql服务4 windows上mysql的常用命令5 MySQL连接工具Navicat 1 下载mysql 1) MySQL软件