MySQL进阶实战8,分区表详解

一、分区表

分区表是一个独立的逻辑表,底层是由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象的封装。对分区表的请求,都会通过句柄对象转化成对存储引擎的接口调用。每一个分区表都有一个使用#分隔命名的表文件。

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

分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中,这样做就可以将相关的数据存放在一起,另外,如果想一次性删除整个分区的数据也会变得更加简单。

二、分区的作用

  1. 表非常大以至于无法全部放在内存中,或者在表中存在热点数据,也有历史数据;
  2. 分区表的数据更容易维护,想批量删除大量数据时,可以使用清除整个分区的方式,还可以对一个独立的分区进行优化、检查、修复等操作;
  3. 分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备;
  4. 可以通过分区表来避免某些特殊的瓶颈,比如InnoDB单个索引的互斥访问;
  5. 可以备份和恢复独立的分区;

三、分区的一些限制

  1. 一个表最多只能有1024个分区;
  2. 某些场景中可以直接使用列进行分区;
  3. 分区表中无法使用外键约束;

四、分区表的增删改查

1、select

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

2、insert

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

3、delete

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

4、update

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

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

五、分区表的类型

MySQL支持多种分区表,分区表达式可以是列,也可以是包含列的表达式,根据时间分区尤为多见。

六、如何使用分区表

假设有一个数据量非常大的表,想从中查询出一段时间的记录,这个表中包含很多年的历史数据,数据是按时间排序的。

因为数据量巨大,肯定不能在每次查询的时候都扫描全表。首先考虑到要使用索引,但此时发现数据并不是按照想要的方式聚集的,而且有大量的碎片,最终会导致一个查询产生成千上万的随机I/O,应用程序随之卡死。

可以有两种方式,对其进行优化。

  1. 让所有的查询都只在数据表上做顺序扫描;
  2. 将数据表和索引全部缓存在内存里;

在数据量超大的时候,B-Tree索引就不好用了,除非是索引覆盖查询,否则数据库服务器需要根据索引扫描的结果回表,查询所有符合条件的记录,如果数据量巨大,还会产生大量随机I/O,查询时间漫长。此外,索引维护的代价也是非常高。此时,可以用分区表来解决,可以通过较小的代价,确定需要的数据在哪个分区表,在此分区表中做顺序扫描,可以建索引,还可以将数据缓存在内存中。

可以通过建立热点分区的方式解决,将热点数据放到一个分区中,让这个分区的数据能够有机会缓存在内存中,这样查询只访问一个很小的分区,能够使用索引,也能够使用缓存,速度快的多。

七、分区表会有哪些问题?

1、分区列和索引列不匹配

如果定义的分区列和索引列不匹配,会导致查询无法进行分区过滤。

假设在列id上定义了索引,在列create_time上进行了分区,因为每个分区都有其独立的索引,所以扫描列create_time上的索引就需要扫描每一个分区内对应的索引。如果每个分区内对应索引的非叶子节点都在内存中,那么扫描的速度还是可以接受的。

2、选择分区的成本可能很高

某一行属于哪个分区?这些符合条件的行在哪个分区?服务器需要扫描所有的分区来找到正确的分区,这样的线性查找的效率并不高,随着分区数的增长,成本会越来越高。

3、打开并锁住所有底层表的成本可能会很高

当查询访问分区表的时候,MySQL需要打开并锁住所有的底层表,这是分区表的一个很大的开销。这个操作在分区过滤之前,所以无法通过分区过滤降低此开销,并且此开销也和分区类型无关,会影响所有的查询。这一点对一些本身查询速度非常快的查询会带来明显的额外开销。可以通过批量操作的方式来降低单个操作的此类开销。

综上所述,分区会有很多隐患和问题。所以目前在进行分区的时候会加入一些限制。

  1. 所有分区都必须使用相同的存储引擎;
  2. 某些存储引擎不支持分区;
  3. 对于MyISAM的分区表,不能使用LOAD INDEX INTO CACHE

八、查询优化

分区最大的优点就是优化器可以根据分区函数来过滤一些分区,通过分区过滤通常可以让查询扫描更少的数据。