MySQL分区方法及注意点

1.基本语法

create table order(
    order_date datetime not null,
    xxxx
)engine=innodb partition by range(year(order_date)) (  //针对order_date进行分区
    partition p_1 values less than(2018),           //order_date在2018年之前的单子放到p_1区
    partition p_2 values less than(2019),
    partition p_3 values less than(2020),
    partition p_4 values less than maxvalue);

上面是一个基本的分区表的写法。一般数据表极大时，索引对查询速度已经不起作用了（一方面是查索引后回表产生的随机io实在太多，一方面是增删数据后索引树的维护成本也很高），就需要用到分区表。分区表说到底就是在内部以很多小表的形式存储数据，在外部逻辑上看还是一张表，每次操作分区表都会根据分区列的条件过滤不符合条件的分区，大大缩小操作范围从而提升效率，说到底分区表也是一个粒度很粗的索引的功能，负责定位到一块较大的区域。

例如一张表已经工作10年了，有十亿数据，但是经常使用的数据是最近一个月的大约一千万数据，这时如果将表设计为分区表将这一千万热点数据放进一个分区，每次操作根据分区条件大部分操作的范围就限制在这一千万数据的小表里了。效率会得到极大提升。

2. 使用要注意的地方

使用分区表有一些需要注意的地方，不然效率一不小心又会变的很低。

(1) null值会使分区过滤无效

year（order_date）函数运算时，如果列的值非法，则函数会返回null，这时数据会被放到第一个分区，所以在MySQL在确定要去操作的底层小表时会把第一分区也算在内，如果第一分区的数据量特别大则会对效率造成很大影响。

解决办法一般是把partition p_0 values less than(0)这样把非法值专门放到一个分区p_0，这个分区的数据量一般情况都是很小的。还有就是不使用函数运算列的值，直接对列值进行分区，如：partition by range columns(order_date)。

(2) 查询条件中分区列和索引列不匹配

简单的说就是where条件中使用的列有索引，但是这个列却不是分区条件列，由于每个分区都有独立的索引，这时就会去每个分区的索引查询，无法完成分区过滤。

例如给列a定义了索引，没有给分区列b定义索引，这时执行where a = 10这样的sql就会使得分区过滤失效，因为需要查询各个分区的a索引。所以一般要同时加上分区列作为查询条件where a = 10 and b = 2010。

(3) 避免分区选择的成本过高

每插入一行数据都需要知道这一行属于哪个分区，这一个过程不能太复杂，否则数据量变化大时这个过程会非常耗时。例如在上述示例中根据order_date选择分区显然是一个线性选择,O(n)的复杂度n为分区数，分区一多会很耗时。

一般推荐使用键分区、hash分区，经过对分区列的运算直接得到分区，而不是线性的去匹配。

(4) 降低锁住所有底层表的成本

由于在确定分区前会锁住所有底层表，所以对于分区表查询要尽量使用批量操作代替单挑操作，还需要控制分区数量。