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

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




MySQL查某个分区 mysql查分区表_分区表


意义

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

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

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

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

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

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


MySQL查某个分区 mysql查分区表_mysql 分区_02


分区表的原理

存储引擎管理分区的各个底层表和管理普通表并没有什么区别(所有的底层表都必须使用相同的存储引擎)

,分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度看,底层表和一个普通表并没有什么区别,存储引擎也无需知道这是一个普通表还是一个分区表的一部分。

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

SELECT 查询

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

INSERT 操作

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

DELETE 操作

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

UPDATE 操作

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


MySQL查某个分区 mysql查分区表_数据_03


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

虽然每个操作都会“先打开并锁住所有的底层表”, 但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁,则会在分区层释放对应表锁。这个加锁和解锁过程与普通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索引就已经无法祈祷作用了。

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

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

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

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

什么情况下会出问题

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

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

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