MySQL数据表分区策略及优缺点分析

为什么需要分区？

当面对巨大的数据表的时候，至少有一件事情是确定的，表太大了以至于每次查询的时候我们没法做全表扫描。而这个时候也没法使用索引，或者说索引意义不大，更不用说索引的维护代价和空间占用非常高。如果是依赖索引，会导致大量的碎片和低聚集度的数据，这会导致查询的时候有上千次的随机 I/O 访问而导致宕机。这种情况下一般只会使用1-2个索引，而不会更多。这种情况下，有两个可行的选项：查询必须从数据表的指定的部分顺序查找或者是期望的部分数据及其索引与服务器的内存匹配。

需要再次重申：在存储空间过大时，除非索引覆盖了整个查询，否则二叉树索引就无法发挥作用。服务端需要查找数据表的一整行数据，并且会在一个大空间跨度里执行随机 I/O 操作，这会导致查询响应时间无法接受。而维护索引（磁盘空间，I/O 操作）的代价同样很高。

而这是分区能够解决的问题。这其中的关键就是分区是索引的一个初级形式，它的负荷低并且能够让我们从临近的数据中获取结果。这种情形下，我们可以依次扫描相邻的数据或者是将临近的数据加载到内存进行检索。分区之所以负荷低是因为它并没有指针指向对应的数据行，也不需要被更新。分区并不精确地将数据按行划分，也没有涉及到所谓的数据结构。实际上，分区相当于对数据进行了分类。

分区的策略

对于大数据表，有两种策略进行分区：

• 不使用索引：创建数据表时不增加索引，而是使用分区定位到所需要的数据行。只要你使用 WHERE 条件将查询切分到很小的分区范围，就已经足够了。这个时候需要通过数学方法计算查询的响应时间是否能够接受。当然，这里的假设是不会将数据放到内存中，而是全部数据都从磁盘读取。因此数据很快就会被其他查询覆盖，使用缓存没什么意义。这种情况一般用于大量数据表的基数是常规的。需要注意的是，需要限制分区数在几百。
• 使用索引，并且隔离热区数据：如果除了热区数据外，大部分数据是不使用的，则可以将热区数据单独的分区，这个分区算上索引都能够加载到内存中。这个时候可以通过索引来优化性能，就像操作普通的数据表一样。

分区隐患

两种分区策略是基于两个关键假设：在查询的时候可以通过过滤分区缩小查找范围，且分区自身的代价不高。然而，这两个假设未必总是有效，下面是可能遇到的问题：

• NULL 空值可能导致分区过滤失效：当分区函数可能是 NULL 时，分区工作的结果就会很奇特。它会假设第一个分区是特殊的。假设使用 PARTITION BY RANGE YEAR(order_date)这样的分区方法，如果 order_date 这个列是 NULL 或者无效的日期都会存储在第一个分区。假设写了一个查询使用了这样的查询条件 :WHERE order_date BETWEEN '2021-01-01' AND '2021-01-31'。MySQL 实际上会检查2个分区，一个是 YEAR 这个函数 在接收到无效输入时可能会返回 NULL，另一个是符合条件的值可能是 NULL（存储在第一个分区中）。这种情况对其他函数也可能，例如 TO_DAYS。如果第一个分区很大的话，就会产生问题，尤其是使用第一种不使用索引策略时。从两个分区查找数据而不是一个分区的效果是完全意外的。为了避免这种情况，应该创造“假的”第一分区，例如 PARTITION p_nulls VALUES LESS THAN (0)。如果没有无效数据存入数据表的话，这个第一分区将是空的，即便它也会被扫描，但是因为是空的或者数据量很少，对性能影响不大。这种情况在 MySQL 5.5以后，如果直接使用列进行分区的话就不需要处理，但是如果是使用函数的话就要这样做。
• 索引与分区不匹配：假设定义了一个索引与分区条件不匹配，查询就可能无法对分区进行过滤。假设定义了 字段 a 的索引却使用 字段 b 进行分区。由于每个分区都会有自己的索引，针对这个索引的查询会遍历所有分区的索引树。如果索引树的非叶子节点都常驻内存查询起来还比较快，但是也没法避免全部索引的扫描。为了避免这种情况，应当尽量避免使用非分区的索引列，除非WHERE 条件本身能够指定分区。看起来这样很容易避免，实际上却令人吃惊。例如，假设一个分区表用在第二个表查询联合查询后，而联合查询使用的索引并不是分区的索引。则联合查询的每一行都会访问和扫码第二张表的分区。
• 决定使用哪个分区代价可能很高：分区实现的方式各有差异，因此实际的性能并不总是一致。特别是当遇到“这个数据行属于哪个分区”或者“如何才能查找到与查询条件匹配的数据行”这样的问题时。在众多分区的情况下来回答这样的问题很费劲。线性搜索并不总是那么有效，结果是随着分区数的增长代价也在上升。最为糟糕的形式是逐行插入。每次插入一行数据到分区的数据表，服务器都需要扫描一次使用哪个分区存放新的数据行。可以通过限制分区的数量来减轻这个问题，事实上，一般不建议超过100个分区。当然，对于其他分区类型，如键值和哈希分区则不会有这样的限制。
• 打开和锁定分区代价也可能很高：分区表带来的一个负面效应是查询时需要对每个分区进行打开和锁定。而这个过程是在过滤分区前进行的。这个代价与分区类型无关，且会影响所有的操作语句。这种影响对于短数据量的查询尤其明显，例如只查询一行数据时。这种缺陷可以通过批量操作替代单次来降低，例如一次插入多行，或 LOAD DATA INFILE，一次按范围删除数据等等。当然，限制分区的数量也是有效的。
• 维护操作代价可能很高：有些分区的维护是很快的，例如创建或者删除分区。而其他操作，例如调整分区，就有点像 ALTER 对表的操作那样了：需要循环复制数据行。例如，调整分区会创建一个临时分区，然后将数据移入到新的分区，再删除旧的分区。

如上所述，分区并不是完美解决方案，目前版本的 MySQL还有一些其他的约束：

• 所有分区必须使用相同的存储引擎。
• 分区函数能够选用的函数或表达式有一定的限制。
• 有些存储引擎并不支持分区。
• 对于 MYISAM 数据表，无法使用 LOAD INDEX INTO CACHE。
• 对于 MYISAM 数据表，分区表需要更多的打开文件描述符，这意味着单个数据表的缓存入口可能对应多个文件描述符。因此基本配置限制了数据表的缓存以避免超出服务器操作系统的预处理量，而分区表可能导致实际超出这个限制。

当然，随着 MySQL 版本的更新迭代，对分区的支持也越来越好，并且很多分区的问题都得到了修复。

以上就是MySQL数据表分区策略及优缺点分析的详细内容，更多关于MySQL数据表分区策略及优缺点的资料请关注我们其它相关文章！