彻底搞懂MySQL分区 _MySQL

作者： GrimMjx

一.InnoDB逻辑存储结构

二.分区概述

分区

三.分区类型

RANGE分区
LIST分区
HASH分区
KEY分区

四.分区和性能
一.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就好了。也不用关心函数了。
CREATETABLE`m_test_db`. `Order`(
`id`INTNOTNULLAUTO_INCREMENT,
`partition_key`INTNOTNULL,
`amt`DECIMAL( 5) NULL,
PRIMARY KEY( `id`, `partition_key`)) PARTITIONBYRANGE(partition_key) PARTITIONS5( PARTITIONpart0 VALUESLESSTHAN( 201901), PARTITIONpart1 VALUESLESSTHAN( 201902), PARTITIONpart2 VALUESLESSTHAN( 201903), PARTITIONpart3 VALUESLESSTHAN( 201904), PARTITIONpart4 VALUESLESSTHAN( 201905)) ;
这时候我们先插入一些数据
INSERTINTO`m_test_db`. `Order`( `id`, `partition_key`, `amt`) VALUES( '1', '201901', '1000');
INSERTINTO`m_test_db`. `Order`( `id`, `partition_key`, `amt`) VALUES( '2', '201902', '800');
INSERTINTO`m_test_db`. `Order`( `id`, `partition_key`, `amt`) VALUES( '3', '201903', '1200');
现在我们查询一下，通过EXPLAIN PARTITION命令发现SQL优化器只需搜对应的区，不会搜索所有分区