Jlin's Blog

林剑的博客

0%

MySQL分区

发表于 更新于 分类于
本文字数： 3.5k 阅读时长 ≈ 13 分钟

一.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就好了。也不用关心函数了。

1
2
3
4
5
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)) ;

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

1
2
3
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标识走了哪几个分区。

1
2
3
4
5
6
7
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 |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
1 row in set (0.00 sec)

五、分表和分区

　　分表从表面意思说就是把一张表分成多个小表，分区则是把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上。

分表和分区的区别

1，实现方式上

mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完正的一张表，都对应三个文件（MyISAM引擎：一个.MYD数据文件，.MYI索引文件，.frm表结构文件）。

2，数据处理上

分表后数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表里面。分区则不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表还是一张表，数据处理还是由自己来完成。

3，提高性能上

分表后，单表的并发能力提高了，磁盘I/O性能也提高了。分区突破了磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。

分表

分表和分区类似，区别是，分区是把一个逻辑表文件分成几个物理文件后进行存储，而分表则是把原先的一个表分成几个表。进行分表查询时可以通过union或者视图。

分表又分垂直分割和水平分割，其中水平分分割最为常用。水平分割通常是指切分到另外一个数据库或表中。例如对于一个会员表，按对3的模进行分割:

table = id%3

如果id%3 = 0 则将用户数据放入到user_0表中，如id%3=1就放入user_1表中，依次类推。

对于一些流量统计系统，其数据量比较大，并且对过往数据的关注度不高，这时按年、月、日进行分表，将每日统计信息放到一个以日期命名的表中；或者按照增量进行分表，如每个表100万数据，超过100万就放入第二个表。还可以按Hash进行分表，但是按日期和取模余数分表最为常见，也容易扩展。

分表后可能会遇到新的问题，那就是查询，分页和统计。通用的方法是在程序中进行处理，辅助视图。

六、以下引用下大佬的见解

作者：赵伟链接：https://www.zhihu.com/question/378240599/answer/1077561855来源：知乎著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

在单机mysql实例（不是分布式数据库 的情况下）使用分区表的原因，主要是因为单表数据量太大导致索引过大，从而降低了查询性能。

考虑一个巨大的单表并且主键字段较大的最坏情形，我们来计算一下主表b+树的高度。

例子1。 比如单表100亿行，每行数据平均占用1000字节的存储空间，16KB的page size，那么主表页节点就要占用约10TB空间，约7亿个页面。假设主键占用空间较大导致内节点每个索引行平均占据256字节，于是每个内节点页面存放64个索引行。那么主表b+树的高度就是

1
1 + ceiling(lg(64, 700000000)) = 6，

假设主键索引取的非常蠢导致内节点每个索引行占据512字节，那么主表b+树的高度是

1
1 + ceiling(lg(32, 700000000)) = 7.

例子2。 假设上述数据只有1千万行，那么用相同的方法计算可得主表b+树的高度分别是4 （256字节的主键索引）和5（512字节的主键索引） — 高度只相差2。

假设例子1的主表真的做了1000个分区那么每个分区表就是例子2的主表，那么通过分区，可以让每次树搜索减少2次页面获取（极大概率从buffer pool获取，否则系统性能无法实用）。 这个差别确实会导致例子2查询性能有所提升，但是区别其实并不大。

另外，分区后另一个性能优势是分散了根节点的访问，从而提升并发性能。不过要知道b+树做遍历并不会持有内节点页面的事务锁，只需要短暂持有根结点页面的read latch，所以除了页面分裂（低频操作）以外的遍历，是可以并发执行的。

综上，我认为这单机做表分区获得的性能提升的理论上限很有限，估计也就10%以内，随数据和查询特征略有波动，随数据库系统的实现也有不同。感兴趣的同学可以使用postgresql或者mysql做一个实验对比一下。在不同的数据库系统实现中，分区表的实际性能与单表相比，提升各不相同，甚至未必能提升，甚至会降低。

mysql分区表的详情如下。

首先，如果很多表都做分区，会导致mysql innodb数据目录下文件数目非常多（比如1000个表分区会产生2000个文件），从而使操作系统的文件系统工作效率降低。并且由于mysql打开表文件的数目限制（该限制虽然可以手动修改但是也受限与操作系统可用资源量）从而导致打开的表反复被淘汰和重新打开，从而降低了所有查询的性能。

在mysql5.7的早期版本中，分区表的实现性能较差，与相同数据量的单表相比性能下降约10%。后来在mysql5.7.19才做了优化，可以去http://bugs.mysql.com上面看一下这个bug。但是即使这个bug修复之后，分区表仍然比相同数据量的单表有大约5%的性能下降（8个分区，100GB数据量，sysbench oltp测例）。

在上例中如果单表占据10TB空间那么单个服务器节点的计算资源（内存，cpu，存储）恐怕已经无法支持业务运行了，所以大概率还是要做分库分表才行。而如果单表只有1TB以内的空间那么完全没必要做表分区。也就是说，在单节点mysql实例中做表分区并没有什么必要，也不会有明显的性能优势。

如果是做分库分表的话，那么通过分区表来实现分库分表是一些简单的分表中间件常用的方法，也比较有效。在昆仑分布式数据库中，我们在计算节点中实现分库分表，在存储节点中永远使用单表做存储，不使用分区表，就是基于上述mysql分区表性能降低的原因。