专栏首页程序员的成长之路一文带你搞懂 MySQL 中的分区!

一文带你搞懂 MySQL 中的分区!

作者 | GrimMjx

一.InnoDB逻辑存储结构

首先要先介绍一下InnoDB逻辑存储结构和区的概念,它的所有数据都被逻辑地存放在表空间,表空间又由段,区,页组成。

段就是上图的segment区域,常见的段有数据段、索引段、回滚段等,在InnoDB存储引擎中,对段的管理都是由引擎自身所完成的。

区就是上图的extent区域,区是由连续的页组成的空间,无论页的大小怎么变,区的大小默认总是为1MB。

为了保证区中的页的连续性,InnoDB存储引擎一次从磁盘申请4-5个区,InnoDB页的大小默认为16kb,即一个区一共有64(1MB/16kb=16)个连续的页。

每个段开始,先用32页(page)大小的碎片页来存放数据,在使用完这些页之后才是64个连续页的申请。这样做的目的是,对于一些小表或者是undo类的段,可以开始申请较小的空间,节约磁盘开销。

页就是上图的page区域,也可以叫块。页是InnoDB磁盘管理的最小单位。默认大小为16KB,可以通过参数innodb_page_size来设置。

常见的页类型有:数据页,undo页,系统页,事务数据页,插入缓冲位图页,插入缓冲空闲列表页,未压缩的二进制大对象页,压缩的二进制大对象页等。

二.分区概述

分区

这里讲的分区,此“区”非彼“区”,这里讲的分区的意思是指将同一表中不同行的记录分配到不同的物理文件中,几个分区就有几个.idb文件,不是我们刚刚说的区。

MySQL在5.1时添加了对水平分区的支持。分区是将一个表或索引分解成多个更小,更可管理的部分。

每个区都是独立的,可以独立处理,也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能,业务代码无需改动。

要知道MySQL是面向OLTP的数据,它不像TIDB等其他DB。那么对于分区的使用应该非常小心,如果不清楚如何使用分区可能会对性能产生负面的影响。

MySQL数据库的分区是局部分区索引,一个分区中既存了数据,又放了索引。也就是说,每个区的聚集索引和非聚集索引都放在各自区的(不同的物理文件)。目前MySQL数据库还不支持全局分区。

无论哪种类型的分区,如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分。  

三.分区类型

目前MySQL支持以下几种类型的分区,RANGE分区,LIST分区,HASH分区,KEY分区。

如果表存在主键或者唯一索引时,分区列必须是唯一索引的一个组成部分。实战十有八九都是用RANGE分区。

RANGE分区

RANGE分区是实战最常用的一种分区类型,行数据基于属于一个给定的连续区间的列值被放入分区。

但是记住,当插入的数据不在一个分区中定义的值的时候,会抛异常。

RANGE分区主要用于日期列的分区,比如交易表啊,销售表啊等。可以根据年月来存放数据。

如果你分区走的唯一索引中date类型的数据,那么注意了,优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择。实战中可以用int类型,那么只用存yyyyMM就好了。也不用关心函数了。

CREATE TABLE \`m\_test\_db\`.\`Order\` (  
  \`id\` INT NOT NULL AUTO_INCREMENT,  
  \`partition_key\` INT NOT NULL,  
  \`amt\` DECIMAL(5) NULL,  
  PRIMARY KEY (\`id\`, \`partition_key\`)) PARTITION BY RANGE(partition_key) PARTITIONS 5( PARTITION part0 VALUES LESS THAN (201901),  PARTITION part1 VALUES LESS THAN (201902),  PARTITION part2 VALUES LESS THAN (201903),  PARTITION part3 VALUES LESS THAN (201904),  PARTITION part4 VALUES LESS THAN (201905)) ;  

这时候我们先插入一些数据

INSERT INTO \`m\_test\_db\`.\`Order\` (\`id\`, \`partition_key\`, \`amt\`) VALUES ('1', '201901', '1000');  
INSERT INTO \`m\_test\_db\`.\`Order\` (\`id\`, \`partition_key\`, \`amt\`) VALUES ('2', '201902', '800');  
INSERT INTO \`m\_test\_db\`.\`Order\` (\`id\`, \`partition_key\`, \`amt\`) VALUES ('3', '201903', '1200');  

现在我们查询一下,通过EXPLAIN PARTITION命令发现SQL优化器只需搜对应的区,不会搜索所有分区。

如果sql语句有问题,那么会走所有区。会很危险。所以分区表后,select语句必须走分区键。

以下3种不是太常用,就一笔带过了。

LIST分区

LIST分区和RANGE分区很相似,只是分区列的值是离散的,不是连续的。LIST分区使用VALUES IN,因为每个分区的值是离散的,因此只能定义值。

HASH分区

说到哈希,那么目的很明显了,将数据均匀的分布到预先定义的各个分区中,保证每个分区的数量大致相同。

KEY分区

KEY分区和HASH分区相似,不同之处在于HASH分区使用用户定义的函数进行分区,KEY分区使用数据库提供的函数进行分区。

四.分区和性能

一项技术,不是用了就一定带来益处。比如显式锁功能比内置锁强大,你没玩好可能导致很不好的情况。

分区也是一样,不是启动了分区数据库就会运行的更快,分区可能会给某些sql语句性能提高,但是分区主要用于数据库高可用性的管理。数据库应用分为2类,一类是OLTP(在线事务处理),一类是OLAP(在线分析处理)。

对于OLAP应用分区的确可以很好的提高查询性能,因为一般分析都需要返回大量的数据,如果按时间分区,比如一个月用户行为等数据,则只需扫描响应的分区即可。在OLTP应用中,分区更加要小心,通常不会获取一张大表的10%的数据,大部分是通过索引返回几条数据即可。

比如一张表1000w数据量,如果一句select语句走辅助索引,但是没有走分区键。那么结果会很尴尬。如果1000w的B+树的高度是3,现在有10个分区。那么不是要(3+3)*10次的逻辑IO?(3次聚集索引,3次辅助索引,10个分区)。所以在OLTP应用中请小心使用分区表。

在日常开发中,如果想查看sql语句的分区查询结果可以使用explain partitions + select sql来获取,partitions标识走了哪几个分区。

mysql> explain partitions select * from TxnList where startTime>'2016-08-25 00:00:00' and startTime<'2016-08-25 23:59:00';    
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+    
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | Extra       |    
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+    
|  1 | SIMPLE      | ClientActionTrack | p20160825  | ALL  | NULL          | NULL | NULL    | NULL | 33868 | Using where |    
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+    
row in set (0.00 sec)  

参考:《MySQL技术内幕》

<END>

本文分享自微信公众号 - 程序员的成长之路(cxydczzl),作者:戳一戳→

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-04-22

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Linux 启动流程分析

    这个部分比较有意思。因为在BIOS阶段,计算机的行为基本上被写死了,程序员可以做的事情并不多;但是,一旦进入操作系统,程序员几乎可以定制所有方面。所以,这个部分...

    良月柒
  • 必会的 24 道 Shell 脚本面试题

    虽然现在 Python 在运维工作中已经使用很普遍,但是很多企业在找 Linux 云计算工程师的时候还是会问到 shell 脚本的问题,它有助于你在工作环境中自...

    良月柒
  • 微信支付的跨平台架构到底有多牛?

    作为一个重要业务,微信支付在客户端上面临着各种问题。其中最核心问题就是分平台实现导致的问题:

    良月柒
  • Linux-fdisk磁盘分区命令(16)

    名称: fdisk 使用: fdisk [块设备磁盘] 说明: 将一个块设备(磁盘)分成若干个块设备(磁盘),并将分区的信息写进分区表。  fdisk命令菜单常...

    张诺谦
  • 最小化约束下的杂质分区

    原文标题:Minimizing Impurity Partition Under Constraints

    李欣颖6837176
  • 数据分区------《Designing Data-Intensive Applications》读书笔记9

    分区与副本是很容易混淆的概念,我们这里离清一下两者。 数据分区的每个副本可以存储在多个节点上。这意味着,即使每个记录恰好属于一个分区,它仍然可以存储在几个不同...

    HappenLee
  • Linux Partition scheme 分区方案(一)

    根分区包含Linux系统所有的目录。如果在安装系统时只分配了/分区,那么上面的/boot、/usr和/var将都包含在根分区中,也就是这些分区将占用根分区的空间...

    wuweixiang
  • linux学习第十四篇:查看磁盘,文件大小命令:df,du;磁盘分区

    df命令 df:查看已挂载磁盘的总容量、使用容量、剩余容量等,可以不加任何参数,默认是按k为单位显示的。 ? 带有tmpfs的都是临时的文件系统,所以在对应的挂...

    用户1215343
  • MySQL支持哪几类分区表?

    Q 题目 MySQL支持哪几类分区表? A 答案 表分区是指根据一定规则,将数据库中的一张表分解成多个更小的,容易管理的部分。从逻辑上看,只有一张表,但是底层却...

    企鹅号小编
  • oracle表空间表分区详解及oracle表分区查询使用方法(转+整理)

    此文从以下几个方面来整理关于分区表的概念及操作: 1.表空间及分区表的概念 2.表分区的具体作用 3.表分区的优缺点 4.表分区的几种类型及操作方法 5...

    用户1221057

扫码关注云+社区

领取腾讯云代金券