mysql之分区

1.对用户来说，分区是一个独立的逻辑表，但底层由多个物理字表组成。每个分区表都有一个使用#分隔命名的表文件。
2.mysql中，索引也是按照分区的字表定义，而没有全局索引。oracle可以定义索引和表是否进行分区。
3.mysql在创建表时使用partition by 字句定义每个分区存放的数据。执行查询时，优化器根据分区定义过滤那些没有我们需要的分区，这样查询就无需扫描所有分区。
create table sales(
order_date datetime not null,
--other columns mitted
) ENGINE = InnoDB partition by range(year(order_date))(
partition p_2010 values less than (2010),
partition p_2011 values less than (2011),
partition p_2012 values less than (2012),
partition p_2013 values less than (2013),
partition p_catchall values less than maxvalue);

4.分区的主要目的：将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起，批量删除整个分区数据也很方便。
5.以下场景适合分区：
（1）表非常大，以至于无法全部放到内存，或只在表的最后部分有热点数据，其它均为历史数据。
（2）分区表的数据更容易维护。如：删除大量数据可以通过清除分区的方式。
（3）分区表的数据可以分布在不同的物理设备上。
（4）可以使用分区表避免某些特殊瓶颈，如：InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等。
6.分区表的限制
（1）一个表最多只能有1024个分区
（2）如果分区字段中有主键或者唯一索引的列，那所有的主键列和唯一索引列都必须包含进来。
（3）分区表中无法使用外键约束。
7.分区表的索引只是在各个底层表上加上一个完全相同的索引。
8.分区表的类型
 (1)范围分区
create table sales(
order_date datetime not null,
--other columns mitted
) ENGINE = InnoDB partition by range(year(order_date))(
partition p_2010 values less than (2010),
partition p_2011 values less than (2011),
partition p_2012 values less than (2012),
partition p_2013 values less than (2013),
partition p_catchall values less than maxvalue);

5.5版本后，可以直接使用列 partition by range columns(order_date)

（2）按日期分区
CREATE TABLE h5_channel_pv_month(
f_date INT,
channel STRING,
pv BIGINT
)
PARTITION BY LIST( f_date )
(
PARTITION p_20170605 VALUES IN ( 20170605 ),
PARTITION p_20170604 VALUES IN ( 20170604 ),
PARTITION p_20170603 VALUES IN ( 20170603 ),
);
partition 分区字句中可以使用各种函数，但要求表达式返回值是一个明确的整数，且不能是常数。可以使用函数year(),to_days()。根据时间间隔进行分区。
另外其他的有哈希分区，列表分区等，但用的较少。
9.如何使用分区
对于数据量巨大的表，比如10亿条数据，几年的数据，只想查最近几个月的数据，如果破？
分析：a.数据量巨大，不能做全表扫描；
 b.索引空间和维护上的消耗，且会产生大量的随机I/O，不能使用索引（数据量超大的时候索引就不起作用了）；这时候只有两个选择：1.让所有查询只在数据表上做顺序扫描，2.建数据表和索引全表缓存在内存里
综上分析：
建立分区，以非常小的代价定位到需要的数据在哪个区域，在这个区域内做顺序扫描，建索引。
全量扫描数据，不要任何索引；
如果数据有明显的热点，且其它数据甚少访问，那可以将这部分热点数据单独放在一个分区中，让这个分区的数据有机会缓存在内存中；

10.分区注意的问题：
a.null值会使分区过滤无效   null会将这个范围的值放在第一个分区，如果第一个分区非常大，当使用“全量扫描，不使用索引”策略时，代价会很大。
b.分区列和索引列不匹配 会导致查询无法进行分区过滤，如a列上建了索引，b列上进行分区
c.选择分区的成本很高
d.打开并锁住所有底层表的成本可能很高   分区过滤之前发生，影响所有的查询。
e.维护分区的成本可能很高   重组分区类似alter操作，先创建一个临时分区，然后将数据复制其中，最后再删除原分区。

11.查询优化
对于访问分区表来说，在where条件中带入分区列，有时候看似多余也要带上，这样就可以让优化器过滤掉无需访问的分区。
创建分区时可以使用表达式，但查询时只能根据列来过滤区分。