分区隐患

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

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 数据表，分区表需要更多的打开文件描述符，这意味着单个数据表的缓存入口可能对应多个文件描述符。因此基本配置限制了数据表的缓存以避免超出服务器操作系统的预处理量，而分区表可能导致实际超出这个限制。