mysql 分区学习

从索引说起： 为了加快数据的查询，避免扫描整个表，通常我们会建立各种表。典型的索引是B-tree索引。然而当数据量非常大的情况下，索引将非常庞大。建立，维护和使用索引的代价会非常大。 在数据量巨大的时候索引带来的空间和维护上的消耗以及大量的碎片时间使索引本身是不起作用的。

• Mysql 的索引会造成很多的随机读。而随机IO要比顺序IO慢的多
  数据量巨大的时候Mysql性能的分析可以参考这篇文章: Why Mysql could be slow with large tables
  因此需要一种更粗粒度的划分。 这就是mysql 分区

实现方式：

• 对底层表的封装，将几个物理表逻辑的抽象到一起。索引是按照分区的子表定义的， 没有全局索引 应用分区时，在构造查询的时候应该必免扫描所有分区。
  由于它的实现所致，我们应尽量避免扫描所有的子表。导致这些扫描所有表的操作有使用了不合适的索引、批量的处理数据，如导入导出导致跨多个子表、大量NULL导致某个分区非常大（或者策略不平衡导致分区不平衡）

适用场景：

• 表非常大以至于无法全部都放在内存中。或者只在表的最后部分有热点数据。如transaction_histroy这张表， 我们使用的数据一般最多为前2个月的
• 分区表的数据更容易维护，如批量删除某个分区、备份或独立恢复
• 分区表的数据可以 分布在不同的物理设备上。
• 可以使用分区表来避免某些特殊的瓶颈。如InnoDb的单个索引互斥访问。ext3文件系统的inode锁竞争等（这一点不是太懂）
  在数据量巨大的时候索引带来的空间和维护上的消耗以及大量的碎片时间使索引本身是不起作用的

几点要注意的

• All columns used in the table’s partitioning expression must be part of every unique key that the table may have, including any primary key.
• 对于NULL的处理，mysql处理null值为单独一个值。不同的分区类型对null的处理有所不同
  

  • RANGE Partitioning: NULL Less THAN 任何值，永远会存放在第0个分区
    
  • LIst Partitioning: 报错除非单独指出null为一个单独分区
    
  • HASH and KEY Parttitioning: 当作0来对待

  
  

查看分区信息：

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
 > FROM INFORMATION_SCHEMA.PARTITIONS
 > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';

Partitioning Type

• Range Partition : 连续的
• List Partition： 离散的
• Hash Partition: 自己定义hash函数
• Key Partition: 系统定义的hash 函数

Range Partition

语法： create table .... PARTITION by RANGE

• RANGE COLUMNS partitioning

CREATE TABLE table_name
PARTITIONED BY RANGE COLUMNS(column_list) (
 PARTITION partition_name VALUES LESS THAN (value_list)[,
 PARTITION partition_name VALUES LESS THAN (value_list)][,
 ...]
) 

column_list:
 column_name[, column_name][, ...]

value_list:
 value[, value][, ...]

list Partition

demo:

CREATE TABLE employees (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT,
 store_id INT
)
PARTITION BY LIST(store_id) (
 PARTITION pNorth VALUES IN (3,5,6,9,17),
 PARTITION pEast VALUES IN (1,2,10,11,19,20),
 PARTITION pWest VALUES IN (4,12,13,14,18),
 PARTITION pCentral VALUES IN (7,8,15,16)
);

WARNING插入不属于任何分区的数据会导致报错

hash Partition

demo:

CREATE TABLE employees (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT,
 store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

注意： 需要在分区时指定分区数，否则默认的分区数为1 分区数用partitions来指定
系统通过计算mod(expr_result, partion_num) 来决定将数据存在哪个分区

key partition

key partition 同样需要人工来指定分区数

分区的维护：

• 删除某个分区的所有数据： ALTER TABLE ... TRUNCATE PARTITION
• 删除某个分区： ALTER TABLE ... DROP PARTITION
• 添加分区 ALTER TABLE ... PARTITION BY ...
• REORGANIZE PARTITION ALTER TABLE members REORGANIZE PARTITION p0 INTO (
PARTITION s0 VALUES LESS THAN (1960),
PARTITION s1 VALUES LESS THAN (1970)
);


• RANGE 与 LIST类型 分区：

  • 添加分匀 : ALTER TABLE ... ADD PARTITION (PARTITION name less than)

• 导出某个分区的数据 EXCHANGE话法： ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt 将某个分区的数据交换到某个表上