Mysql的分区表

因hive数据在20180101 用sqoop同步到mysql的时候出错，最终查找原因发现是因为mysql中 对应的表的分区没有建导致的，顺带研究下mysql的分区表

mysql的分区表的概述

mysql中分区表的定义：

将一个表或索引分解为多个更小、更可管理的部分，从逻辑上讲，只有一个表或者索引，但是物理上这个表或者索引可能由数十个物理分区组成

数据库表的分区一般有两种：
水平分区：指将同一表中不同行的记录分配到不同的物理文件中
垂直分区：指将同一表中不同列的记录分配到不同的物理文件中
mysql 支持的全是前者。

mysql分区表的优势（为什么要分区）

• 对于大体量的数据能够非常有效的提高查询的速度（表非常大不能够一次加载到内存中，表的组成由 大量的历史数据+部分热点数据）

• 降低管理和维护成本。对一个表中的每个独立的分区都能够进行独立的操作（备份，恢复，导出，查询的优化…）

• 分区表可以避免某些特殊的瓶颈。InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争…

分区的策略

• 全量扫描数据：通过where条件语句来限制扫描的分区，需要注意的是，限制的分区的数量不能太多。

• 建立分区索引，设置热点分区 将热点数据设置到一个分区中，这样能够充分利用缓存和索引，大幅提高数据查询效率

ps:以上策略均以查询得到过滤，丢掉额外的分区，分区本身不产生额外的代价为准则

分区表的类型

• range 分区（现在很少用，因为使用上有局限在mysql5.5之后的话增加了Columns 分区，更加好用）
• list 分区 ：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
• hash分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数
• key分区：根据MySQLS数据库提供的哈希函数来进行分区

【注：无论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分】

---

【MYSQL5.5之后，更好的支持】
RANGE分区和LIST分区只支持整数分区，从而导致需要额外的函数计算得到整数或者通过额外的转换表来转换为整数再分区的问题。Columns分区可以细分为RANGE Columns分区和LIST Columns分区，RANGE Columns分区和LIST Columns分区都支持整数、日期时间、字符串三大数据类型

分区表相关的操作

#查看当前数据库是否支持分区
mysql>show variables like'%partition%';
#创建分区表
RANGE分区：
mysql>CREATETABLE`operation_log`(
->`id`int(11)unsignedNOT NULLAUTO_INCREMENT,
->`cid`mediumint(7)unsignedNOT NULL,
->`accountid`mediumint(8)NOT NULLDEFAULT'0',
->`flag`tinyint(1)unsignedNOT NULLDEFAULT'0',
->`addtime`int(11)unsignedNOT NULL,
->`device`tinyint(1)unsignedNOT NULLDEFAULT'1',
->PRIMARY KEY(`id`,`addtime`),
->KEY`idx_accountid_addtime`(`accountid`,`addtime`),
->KEY`idx_accountid_flag`(`accountid`,`flag`),
->)ENGINE=InnoDBAUTO_INCREMENT=50951039DEFAULTCHARSET=utf8COMMENT='操作记录'
->/*!50100 PARTITION BY RANGE (addtime)
->(PARTITION `2013-05` VALUES LESS THAN (1370016000) ENGINE = InnoDB,
-> PARTITION `2013-06` VALUES LESS THAN (1372608000) ENGINE = InnoDB,
-> PARTITION `2013-07` VALUES LESS THAN (1375286400) ENGINE = InnoDB,
-> PARTITION `2013-08` VALUES LESS THAN (1377964800) ENGINE = InnoDB,
-> PARTITION `2013-09` VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */；
#list分区
mysql> CREATE TABLE IF NOT EXISTS `list_part` (
->   `id` int(11) NOT NULL  COMMENT '用户ID',
->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省',
->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女'
-> ) ENGINE=INNODB  DEFAULT CHARSET=utf8
-> PARTITION BY LIST (province_id) (
-> PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),
-> PARTITION p1 VALUES IN (9,10,11,12,16,21),
-> PARTITION p2 VALUES IN (13,14,15,19),
-> PARTITION p3 VALUES IN (17,18,20,22,23,24)
-> );