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；

八、查询优化

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

MySQL进阶实战系列文章

MySQL进阶实战1，数据类型与三范式

MySQL进阶实战2，那些年学过的事务

MySQL进阶实战3，mysql索引详解，上篇

MySQL进阶实战4，那些年学过的索引，下篇

MySQL进阶实战5，为什么查询速度会慢

MySQL进阶实战6，缓存表、视图、计数器表

MySQL进阶实战7，查询的执行过程

SQL性能优化的21个小技巧

mysql索引详解

MySql基础知识总结（SQL优化篇）

哪吒精品系列文章

Java学习路线总结，搬砖工逆袭Java架构师

10万字208道Java经典面试题总结(附答案)

Java基础教程系列

Spring Boot 进阶实战