Mysql分区表使用的一些限制和需要注意的地方

最新推荐文章于 2025-04-19 22:11:57 发布

转载最新推荐文章于 2025-04-19 22:11:57 发布 · 363 阅读

mysql 专栏收录该内容

10 篇文章

订阅专栏

本文探讨了MySQL分区表在实际应用中可能遇到的问题，包括NULL值导致的分区过滤失效、分区列与索引列不匹配带来的查询效率下降、选择分区的成本及锁表开销等问题，并提出了相应的解决方案。

mysql分区策略都基于两个非常重要的假设：查询都能够过滤（prunning)掉很多额外的分区、分区本身并不会带来很多额外的代价。而事实证明，这两个假设在某些场景下会有问题。下面介绍一些可能会遇到的问题。

NULL位会使分区过滤无效

关于分区表一个容易让人误解的地方就是分区的表达式的值可以是NULL：第一个分区是一个特殊分区。假设按照PARTITION BY RANGE YEAR(order_date）分区，那么所有order_date为NULL或者是一个非法值的时候，记录都会被存放到第一个分区。现在假设有下面的查询：WHERE order_date BETWEEN '2012-01-01'AND’2012-01-31’。实际上，MySQL会检查两个分区，而不是之前猜想的一个：它会检查2012年这个分区，同时它还会检查这个表的第一个分区。检查第一个分区是因为YEAR（）函数在接收非法值的时候可能会返回NULL值，那么这个范围的值可能会返回NULL而被存放到第一个分区了。这一点对于其他很多函数，例如TO_DAYS（）也一样。

如果第一个分区非常大，特别是当使用“全量扫描数据，不要任何索引”的策略时，代价会非常大。而且扫描两个分区来查找列也不是我们使用分区表的初衷。为了避免这种情况，可以创建一个“无用”的第一个分区，例如，上面的例子中可以使用PARTITION p_nulls VALUES LESS THAN(0）来创建第一个分区。如果插入表中的数据都是有效的，那么第一个分区就是空的，这样即使需要检测第一个分区，代价也会非常小。

在MySQL5.5中就不需要这个优化技巧了，因为可以直接使用列本身而不是基于列的函数进行分区：PARTITION BY RANGE COLUMNS(order_date).所以这个案例最好的解决方越是能够直接使用MySQL5.5的这个语法。

分区列和索引列不匹配

如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。假设在列a上定义了索引，而在列b上进行分区。因为每个分区都有其独立的索引，所以扫描列b上的索引就需要扫描每一个分区内对应的索引。如果每个分区内对应索引的非叶子节点都在内存中，那么扫描的速度还可以接受，但如果能跳过某些分区索引当然会更好。要避免这个问题，应该避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件。

听起来避免这个问题很简单，不过有时候也会遇到一些意想不到的问题。例如，在一个关联查询中，分区表在关联顺序中是第二个表，井且关联使用的索引和分区条件并不匹配。那么关联时针对第一个表符合条件的每一行，都需要访问并搜索第二个表的所有分区。

选择分区的成本可能很高

如前所述分区有很多类型，不同类型分区的实现方式也不同，所以它们的性能也各不相同。尤其是范围分区，对于回答“这一行属于哪个分区”、“这些符合查询条件的行在哪些分区”这样的问题的成本可能会非常高，因为服务器需要扫描所有的分区定义的列表来找到正确的答案。类似这样的线性搜索的效率不高，所以随着分区数的增长，成本会越来越高。

我们所实际碰到的类似这样的最糟牒的一次问题是按行写入大量数据的时候。每写入一行数据到范围分区的表时，都需要扫描分区定义列表来找到合适的目标分区。可以通过限制分区的数量来缓解此问题，根据实践经验，对大多数系统来说，100个左右的分区是没有问题的。其他的分区类型，比如键分区和哈希分区，则没有这样的问题。

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

当查询访问分区表的时候，MySQL需要打开井锁住所有的底层表，这是分区表的另一个开销。这个操作在分区过滤之前发生，所以无法通过分区过滤降低此开销，并且该开销也和分区类型无关，会影响所有的查询。这一点对一些本身操作非常快的查询，比如根据主键查找单行，会带来明显的额外开销。可以用批量操作的方式来降低单个操作的此类开销，例如使用批量插入或者 LOAD DATA INFILE、一次删除多行数据，等等。当然同时还是需要限制分区的个数。

维护分区的成本可能很高

某些分区维护操作的速度会非常快，例如新增或者删除分区（当删除一个大分区可能会很慢，不过这是另一回事）。而有些操作，例如重组分区或者类似ALTER语句的操作：这类操作需要复制数据。重组分区的原理与ALTER类似，先创建一个临时的分区，然后将数据复制到其中，最后再删除原分区。

如上所述，分区表不是什么“银弹”。下面是目前分区实现中的一些其他限制：如上所述，分区表不是什么“银弹”。下面是目前分区实现中的一些其他限制：

所有分区都必须使用相同的存储引擎。
分区函数中可以使用的函数和表达式也有一些限制。某些存储引擎不支持分区。
对于MyISAM的分区表，不能再使用LOAD INDEXI INTO CACHE操作。
对于MyISAM表，使用分区表时需要打开更多的文件描述符。虽然看起来是一个表其实背后有很多独立的分区，每一个分区对于存储引擎来说都是一个独立的表。这样即使分区表只占用一个表缓存条目，文件描述符还是需要多个。因此，即使已经配置了合适的表缓存，以确保不会超过操作系统的单个进程可•以打开的文件描述符的个数，但对于分区表而言，还是会出现超过文件描述符限制的问题。

最后，需要指出的是较老版本的MySQL问题会更多些。所有的软件都是有bug的。分区表在MySQL5.1中引人，在后面的5.1.40和5.1.50之后修复了很多分区表的b吨。MySQL5.5中，分区表又做了很多改进，这才使得分区表可以逐步考虑用在生产环境了在即将发布的MySQL5.6版本中，分区表做了更多的增强，例如新引火的ALTER TABLE EXCHANGE PARTITION。