MySQL分区表

分区表是一种粗粒度,简易的索引策略，适用于大数据的过滤场景.最适合的场景是,没有合适的索引时，对其中几个分区表进行全表扫描.或者只有一个分区表和索引是热点，而且这个分区和索引能够全部存储在内存中。限制单表分区数不要超过150个，并且注意某些导致无法做分区过滤的细节，分区表对于单条记录的查询没有优势,需要注意这类查询的性能。

分区表语法

分区表分为RANGE，LIST，HASH，KEY四种类型，并且分区表的索引是可以局部针对分区表建立的：

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    amout DOUBLE NOT NULL,
    order_day DATETIME NOT NULL,
    PRIMARY KEY(id,order_day)
) ENGINE=Innodb PARTITION BY RANGE(YEAR()order_day))(
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
    PARTITION p_catchall VALUES THAN MAXVALUE
);

这段语句表示将表内数据按照order_dy的年份范围进行分区，2010年一个区，2011一个，2012一个，剩下的一个。

要注意如果这么做，则order_day必须包含在主键中，且会产生一个问题，就是当年份超过阈值，到了2013,2014时，需要手动创建这些分区。

替代方法就是使用HASH:

CREATE TABLE sales (
    int INT PRIMARY KEY AUTO_INCREMENT,
    amount DOUBLE NOT NULL,
    order_day DATETIME NOT NULL
) ENGINE=Innodb PARTITION BY HASH(id DIV 1000000);

这种分区表每100W条数据建立一个分区，且没有阈值范围的影响。

分区表的应用

对于大数据(如10TB)而言,索引起到的作用相对小,因为索引的空间与维护成本很高,另外如果不是索引覆盖查询,将导致回表,造成大量磁盘IO.那么对于这种情况的解决策略是:
1.全量扫描数据,不要任何索引
通过分区表表达式将数据定位在少量的分区表,然后正常访问这些分区表的数据；
2.分离热点,索引数据
将热点数据分离出来在一个小的分区,并对分区建立索引,对热点数据的查询提高效率.

分区表的问题

1.NULL值使分区过滤无效
2. 选择分区的成本
3. 分区列和索引列不匹配
如果索引列和分区列不匹配,且查询中没有包含过滤分区的条件,会导致无法进行分区过滤,那么将会导致查询所有分区。
4. 打开并锁住所有底层表
分区表的的查询策略是在分区过滤之前,打开并锁住所有底层表,这会造成额外的开销,解决问题的方法是尽量使用批量操作,例如LOAD DATA INFILE,或者一次删除多行数据。

分区本身也有一些限制：
A：一个表最多只能有1024个分区（mysql5.6之后支持8192个分区）
B：在mysql5.1中分区表达式必须是整数，或者是返回整数的表达式，在5.5之后，某些场景可以直接使用字符串列和日期类型列来进行分区（使用varchar字符串类型列时，一般还是字符串的日期作为分区）。
C：如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进来，如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引
D：分区表中无法使用外键约束
E：mysql数据库支持的分区类型为水平分区，并不支持垂直分区，因此，mysql数据库的分区中索引是局部分区索引，一个分区中既存放了数据又存放了索引，而全局分区是指的数据库放在各个分区中，但是所有的数据的索引放在另外一个对象中
F：目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引

分区表的查询条件

过滤分区表的WHERE条件必须是切分分区表的列，而不能带有函数，例如只能是order_day，而不能是YEAR(order_day)。