MySQL分区表基础

前言

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

理解分区表

假设现在要查询一个超过1TB的数据集，你打算如何查询这个表？首先肯定不能出现全表扫描，你可能会想到使用索引，也许情况好点可以通过一两个索引优化这个查询，不过在这种超TB级别的情况下，B-TREE索引不会起到任何作用，除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，这将会产生大量随机I/O，数据量巨大的时候，维护索引的代价也非常高。
这正是分区要做的事情。你可以将其当作索引的最初形态来理解分区，以代价非常小的方式定位到需要的数据在哪一片“区域”。在这片“区域”内，你可以使用顺序扫描，也可以建立索引，还可以将数据放进内存等等。因为分区的代价非常低。只需要一个简单的表达式就可以表达每个分区存放的是什么数据。

分区表原理

分区表对应用是透明的，对于用户来说，分区表就是一张普通的表。存储引擎管理分区的各个底层表和管理普通表是一样的，分区表的索引只是在各个底层表上各自加上一个完全相同的索引。但是MySQL底层实现分区表和普通表是有区别的，分区表底层由多个物理子表组成，每一个分区表都有一个使用#分隔命名的表文件。在分区表上进行操作逻辑如下：

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

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

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

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

上面的所有操作都支持WHERE过滤，先过滤后再由分区层确定操作记录所在的分区，上面的操作“先打开并锁住所有的底层表”，并不是说处理过程中是锁住全表的，这个操作由存储引擎自己实现，比如InnoDB，实现的就是行级锁。

适合分区的场景

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

• 表非常大，或者只在表的最后部分有热点数据，其他均是历史数据。
• 需要经常大量维护数据的时候，分区表的数据更容易进行维护。还可以对独立分区进行优化，检查，修复等操作。
• 分区表的数据可以分布在不同的物理设备上，从而高效利用多个硬件设备。
• 碰到InnoDB的单个索引的互斥访问，ext3文件系统的inode锁竞争等，可以使用分区表来避免这些特殊的瓶颈。
• 分区还可以备份和恢复，这在非常大的数据集的场景下效果非常好。

分区表带来的问题

在某些场景下，通过查询过滤掉额外的分区以及分区本身不会带来太多额外的代价的特点会有问题。

• NULL值会使分区过滤无效
• 分区列和索引列不匹配
• 选择分区的成本可能很高
• 打开并锁住所有底层表的成本很高
• 维护分区的成本很高

实现分区的一些限制

• 所有分区都必须使用相同的存储引擎。
• 分区函数中可以使用的函数和表达式也有一些限制。
• 某些存储引擎不支持分区。
• 较老版本MySQL会有更多的问题。