MySQL分区表

标签: Mysql  mysql

表初始化

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);
  1. 在表t中初始化插入两行记录,按照分区规则,分别落在p_2018和p_2019两个分区上
  2. 包含4个ibd文件,每个分区对应一个ibd文件
    1. 对于Server层来说,只是1个表
    2. 对于引擎层来说,这是4个表

引擎层行为

InnoDB

session A session B
BEGIN;SELECT * FROM t WHERE ftime=’2017-05-01’ FOR UPDATE;
NSERT INTO t VALUES (‘2018-02-01’,1);(Query OK)INSERT INTO t VALUES (‘2017-12-01’,1);(Blocked)
mysql> SELECT locked_index,locked_type,waiting_lock_mode,blocking_lock_mode FROM sys.innodb_lock_waits WHERE locked_table='`test`.`t`';
+--------------+-------------+-------------------+--------------------+
| locked_index | locked_type | waiting_lock_mode | blocking_lock_mode |
+--------------+-------------+-------------------+--------------------+
| ftime        | RECORD      | X                 | X                  |
+--------------+-------------+-------------------+--------------------+


mysql> SHOW ENGINE INNODB STATUS\G;
INSERT INTO t VALUES ('2017-12-01',1)
------- TRX HAS BEEN WAITING 49 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 104 page no 5 n bits 72 index ftime of table `test`.`t` /* Partition `p_2018` */ trx id 7417349 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

对于普通表,session A持有的锁为ftime:Next-Key Lock:(‘2017-4-1’,‘2018-4-1’]
在这里插入图片描述
但对于引擎来说,分区表的分区是不同的表,即2017-4-1的下一个记录是p_2018分区的supremum

在这里插入图片描述

MyISAM

SessionA SessionB SessionC
ALTER TABLE t ENGINE=MyISAM;(MySQL 5.7)
UPDATE t SET c=SLEEP(100) WHERE ftime=’2017-04-01’;
SELECT * FROM t WHERE ftime=’2018-4-1’;(Query OK)
SELECT * FROM t WHERE ftime=’2017-5-1’;(Blocked) SHOW PROCESSLIST;
mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+------------------------------+----------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State                        | Info                                               |
+----+------+-----------+------+---------+------+------------------------------+----------------------------------------------------+
|  2 | root | localhost | test | Query   |   49 | User sleep                   | UPDATE t SET c=SLEEP(100) WHERE ftime='2017-04-01' |
|  3 | root | localhost | test | Query   |   27 | Waiting for table level lock | SELECT * FROM t WHERE ftime='2017-5-1'             |
|  4 | root | localhost | test | Query   |    0 | starting                     | SHOW PROCESSLIST                                   |
+----+------+-----------+------+---------+------+------------------------------+----------------------------------------------------+

  1. 对于MyISAM引擎来说,分区表是4个表
  2. MyISAM只支持表锁,MyISAM的表锁是在引擎层实现的,session A加的表锁,其实是锁在分区p_2018上

手工分表 VS 分区表

  1. 手工分表的逻辑,找到所有需要更新的分表,然后依次更新,在性能上,与分区表并没有实质的差别
  2. 分区表由Server层决定使用哪个分区,手工分表由应用代码决定使用哪一个分表

分区策略

  1. 每当第一次访问一个分区表的时候,MySQL需要把所有的分区都访问一遍
    1. 如果一个分区表的分区很多,比如超过了1000个
    2. 在MySQL启动时,如果需要打开的文件超过了open_files_limit,就会报错
    3. 实际只需要访问一个分区,但语句却无法执行(MyISAM才会如此,InnoDB采用本地分区策略)

MyISAM

  1. MyISAM分区表使用的分区策略是通用分区策略(generic partitioning)。 每次访问分区都由Server层控制
  2. 通用分区策略是MySQL一开始支持分区表时就存在的代码
    1. 在文件管理和表管理的实现上很粗糙
    2. 同时,还有比较严重的性能问题
  3. MySQL 5.7.17开始,将MyISAM分区表标记为Deprecated
  4. 从MySQL 8.0开始,不再允许创建MyISAM分区表了,只允许创建已经实现了本地分区策略的引擎。 目前只有InnoDB引擎和NDB引擎支持本地分区策略

InnoDB

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

Server层行为

从Server层来看,一个分区表就只是一个表

SessionA SessionB sessionC
BEGIN;SELECT * FROM t WHERE ftime=’2018-04-01’;
ALTER TABLE t TRUNCATE PARTITION p_2017;(Blocked)
SHOW PROCESSLIST;
-- session A持有整个表的MDL锁,导致session B被堵住
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------+
| Id | User            | Host      | db   | Command | Time   | State                           | Info                                    |
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 137019 | Waiting on empty queue          | NULL                                    |
| 24 | root            | localhost | test | Sleep   |    126 |                                 | NULL                                    |
| 25 | root            | localhost | test | Query   |      0 | starting                        | SHOW PROCESSLIST                        |
| 26 | root            | localhost | test | Query   |      3 | Waiting for table metadata lock | ALTER TABLE t TRUNCATE PARTITION p_2017 |
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------+

小结

  1. MySQL在第一次打开分区表的时候,需要访问所有的分区
  2. 在Server层,认为是同一张表,因此所有分区共用同一个MDL锁
  3. 在引擎层,认为是不同的表,因此拿到MDL锁之后,根据分区规则,只访问必要的分区
    1. 必要的分区需要根据SQL语句中的WHERE条件和分区规则来实现
    2. WHERE ftime=‘2018-4-1’,必要分区是p_2019分区
    3. WHERE ftime>=‘2018-4-1’,必要分区是p_2019分区和p_others分区
    4. 如果查询语句的WHERE条件没有分区Key,就只能访问所有分区了

优势

  1. 对业务透明_,方便清理历史数据_
  2. DROP TABLE t DROP PARTITION,与DELETE语句删除数据相比,速度更快,对系统影响小

PS:参考 mysql45讲

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

智能推荐

20道25K+Android工程师面试必问面试题

25K大致算的上是Android开发的一个分水岭了。没点真正的东西,还真的拿不到25 本文讲解: 我们为什么要选择离职 面试必问面试题 如何选择心仪的公司 一.我们为何选择离职 工资跟不上消费 上班找不到归宿感和成绩感,上班感觉和坐牢一样 在公司没有发展空间 二.25K+Android工程师必问面试题 1.APK安装过程 应用安装涉及到如下几个目录: system/app:系统自带的应用程序,无法...

nginx实现反向代理

一.代理概念: 1.什么是正向代理和反向代理,概念我这里就不做解释,因为网上有大神已经解释的很好了,我这里给出链接https://www.cnblogs.com/Anker/p/6056540.html。 二.介绍nginx反向代理环境准备,工具准备: 1.首先你需要安装一个虚拟机VMware,虚拟机中安装一个linux系统,linux系统中需要安装两个tomcat服务器。 2.因为后面需要对反向...

第27课 二阶构造模式

本文内容取自于对狄泰学院 唐佐林老师 C++深度解析 课程的学习总结 构造函数的回顾 关于构造函数 类的 构造函数 用于对象的 初始化 构造函数 与类同名并且没有返回值 构造函数在对象定义时 自动被调用 问题 如判断 构造函数 的执行结果? 在构造函数中执行 return 语句会发生什么? 构造函数执行 结束是否意味着 对象构造成功? 为什么了回答这几个问题,我们编写一个构造函数,并且构造函数中添...

微机原理 第七章 8255A及I/O口

微机原理 第七章 8255A及I/O口 7.1 概述 一、 并行接口 什么叫并行接口? 连接CPU与并行外设的通道 以字节、双字节或字长为传输单位。 为什么要用?或者说,为什么会广泛应用? 传输速度快,但硬件开销大,近距离传输 一般传输什么信息? 传输的信息主要有状态信息、控制信息和数据信息,所以有对应的寄存器 在端口是不够用的时候,得进行扩展 二、可编程并行接口的功能 有什么特点? 具有端口寄存...

C语言非OS编程架构

    对于单片机非OS程序来说,好的架构必须具备如下特点:代码规范优雅,结构清晰,各模块之间低耦合。个人根据多年工作经历,总结如下:编写代码前应进行结构设计,C语言是面向过程的语言,所以一般系统结构分为三层:驱动层,功能模块层,任务调用层。为了降低耦合性,函数调用规则尽可能做到上层调用下层。     驱动层     非OS驱动层一般由硬...

猜你喜欢

spring boot 源码解析12-servlet容器的建立

前言 spring boot 一般都会加入如下依赖: 加入后,就会启动一个嵌入式容器,其默认启动的是tomcat.那么他是如何启动的,我们接下来就分析下. 解析 通过之前的文章我们知道了在SpringApplication#run方法的第9步会调用AbstractApplicationContext#refresh方法,而在该方法的第5步中会调用invokeBeanFactoryPostProce...

leetcode之除数博弈

原题链接 这道题我看到leetcode上有个巧妙解法,利用N的奇偶性就可以判断先手的输赢; 但是看到这道题目属于动态规划,那么就该朝着动态规划的思路去做,但是我觉得这道题虽然是简单题,但是有点绕。...

mkdir: Cannot create directory /usr/master/input. Name node is in safe mode.解决方案

一、问题描述 在Hadoop启动namenode后,创建目录时报错,错误如下: 显示namenode在安全模式下,无法创建文件夹。 二、问题解决 关闭namenode安全模式: 问题解决,重新创建可查看到创建的文件夹。          欢迎关注博主,欢迎互粉,一起学习!        感谢您的阅读,不足之处...

POI的入门学习(二)遍历工作簿的行和列输出单元格内容

POI的入门学习(二)遍历工作簿的行和列输出单元格内容 引入依赖 提前准备的excel表格内容。 Demo代码 输出结果...

TestNG注解介绍三-多线程

在TestNG的测试方法中创建多线程有两种方式,一种是注解一种是使用xml文件,用两个简单的例子带大家实现下 注解方式 在@Test注解的属性中invocationCount属性可以设定线程数,threadPoolSize则可以设定线程池的大小。 XML方式 我们在suite 标签中可以使用parallel来指定线程在运行的级别:     methods级别:所有用例都可以在不...