MySQL中分区表的详细介绍-木庄网络博客

本文摘自PHP中文网，作者不言，侵删。

本篇文章给大家带来的内容是关于MySQL中分区表的详细介绍，有一定的参考价值，有需要的朋友可以参考一下，希望对你有所帮助。

对于用户而言，分区表是一个独立的逻辑表，但是在底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象的封装，对分区表的请求都会通过句柄对象转化成对存储引擎的接口调用

意义

MySQL在创建表的时候可以通过使用 PARTITION BY 子句定义每个分区存放的数据。在执行查询的时候，优化器根据分区定义过滤那些没有我们需要的数据的分区，这样查询就可以无需扫描所有分区――只需要查找包含需要数据的分区即可。

分区的一个主要目的是将数据按照一个较粗的粒度分别存放在不同的表中。这样做可以将相关的数据存放在一起，另外，当我们想要一次批量删除整个分区的数据也会变得很方便。

在以下的场景中，分区可以起到很大的作用：

表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据其他均是历史数据
分区表的数据更容易维护
分区表的数据可以分布在不同的物理设备上
可以使用分区表来避免某些特殊的瓶颈
如果需要，可以备份和回复独立的分区

分区表本身也有一些限制，下面几点尤为重要：

一张表最多只能有1024个分区
在MySQL5.1 中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5 中，某些场景可以直接使用列来进行分区
分区表中无法使用外键约束
如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

分区表的原理

存储引擎管理分区的各个底层表和管理普通表并没有什么区别（所有的底层表都必须使用相同的存储引擎）
，分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度看，底层表和一个普通表并没有什么区别，存储引擎也无需知道这是一个普通表还是一个分区表的一部分。

分区表上的操作按照下面的操作逻辑进行：

SELECT 查询

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据

INSERT 操作

当写入一条记录的的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表

DELETE 操作

当删除一条记录的的时候，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

UPDATE 操作

当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新的记录再哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

这些操作都是支持过滤的。

虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁，则会在分区层释放对应表锁。这个加锁和解锁过程与普通InnoDB上的查询类似。

分区表的类型

MySQL支持多种分区表，我们看到最多的就是根据范围进行分区，每个分区存储落在某个范围内的记录。分区表达式可以是列，也可以是包含列的表达式。

例如，如下表就将每一年的销售额都存放在不同的分区中：

CREATE TABLE sales(
    order_date DATETIME NOT NULL,
    ....
)ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date))(
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE;
)

PARTITION 分区子句中可以使用各种函数。但是有一个要求， 表达式返回的值必须是一个确定的整数，且不能是一个常数。

MySQL还支持键值、哈希和列表分区等。

如何使用分区表

如果我们希望从一个非常大的表中查询出一段时间的记录，我们应该如何查询这个表，如何才能更加高效？

因为数据量非常大，肯定不能在每次查询的时候都扫描全表，考虑到索引在空间和维护上的消耗，我们也不希望使用索引。即使真的使用索引，也会发现数据并不是按照想要的方式进行聚集，会产生大量的碎片，最终导致一个查询产生成千上万的随机I/O。而事实上，当数据量超级大时，B-Tree索引就已经无法祈祷作用了。

因此我们可以选择一些更粗粒度但消耗更少的方式检索数据，例如在大量的数据上只索引对应的一小块元数据。

这正是分区要做的事情，理解分区可以将其当作索引的最初形态。因为分区无需额外的数据结构记录每个分区有哪些数据――分区不需要精确定位每条数据的位置，也就无须额外的数据结构――所以其代价非常低。只需要一个简单的表达式就可以表达每个分区存放的是什么数据。

为了保证大数据量的可扩展性，一般有以下两个策略：

全量扫描数据，不需要任何索引： 只要能够使用 WHERE 条件，将需要的数据限制在少数分区中，则效率是很高的。使用这种策略假设不用将数据完全放入内存中，同时还假设需要的数据全部都在磁盘上。因为内存相对较小，数据很快会被挤出内存，所以缓存起不了任何作用。这个策略适用于以正常的方式访问大量数据的时候。
索引数据，并分离热点： 如果数据有明显的“热点”，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据可以有机会都缓存在内存中。这样的查询可以只访问一个很小的分区表，能够使用索引，也能够有效的使用缓存。

什么情况下会出问题

上面介绍的两个分区策略都基于两个非常重要的假设：查询都能够过滤掉很多额外的分区、分区本身并不会带来很多额外的代价。

事实证明，这两个假设在某些场景下会有问题：

分区列和索引列不匹配： 如果定义的索引列和分区列不匹配，会导致可查询无法进行分区过滤。
选择分区的成本可能很高： 不同类型的分区的实现方式也不同，所以它们的性能也各不相同。尤其是范围分区，对于查询符合条件的行在哪些分区的成本可能会非常高，因为服务器需要扫描所有的分区定义的列表来找到正确的答案。
打开并锁住所有底层表的成本可能很高： 当查询访问分区表的时候，MySQL需要打开并锁住所有的底层表，这是分区表的另一个开销。
维护分区的成本可能很高： 某些分区维护操作的速度会非常快，例如新增或者删除分区。而有些操作，比如重组分区或者类似ALTER语句的操作成本可能会很高，因为这类操作需要复制数据。

以上就是MySQL中分区表的详细介绍的详细内容，更多文章请关注木庄网络博客！