MySQL 分区介绍

眼看着数据库就要爆了，从几十M到几十G再到几百G，怎么办能！或许我们第一个想到的就是数据仓库吧，

如果时间或者其它原因不允许那有怎么办呢！我们就不得不使用分区暂时死撑着了，好吧，那就开始分区呗！

 

分区类型：

·RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

·LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

·HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。

 这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

·KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

 

分区方法（我采用的）:

-采用RANGE按日期分区（只支持Year,to_days分区函数）

-采用HASH按分类分区

 

案例：

以下是监控记录信息表,由于写操作频率及数据量非常大（几亿数据），所以采用InnoDB引擎

CREATE TABLE `usersession` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ClientSN` bigint(20) unsigned NOT NULL,
  `SessionID` char(40) NOT NULL,
  `IPAddress` varchar(64) NOT NULL ,
  `UserName` char(25) DEFAULT NULL ,
  `Operation` int(10) DEFAULT NULL,
  `StartTime` datetime NOT NULL ,
  `EndTime` datetime DEFAULT NULL ,
  `ApplicationID` smallint(6) NOT NULL ',
  `Grade` tinyint(4) NOT NULL DEFAULT '-1' ,
  `Subject` tinyint(4) DEFAULT '-1',
  `BookCode` char(32) DEFAULT NULL,
  `Data` varchar(256) DEFAULT NULL COMMENT ,
  `UpdateTimeStamp` datetime NOT NULL,
  `IsUploaded` bit(1) NOT NULL DEFAULT b'0' ,
  PRIMARY KEY (`Id`,`StartTime`,`Grade`),
  KEY `clientsn_idx` (`ClientSN`) USING BTREE,
  KEY `starttime_subject_grade_idx` (`StartTime`,`Subject`,`Grade`) USING BTREE,
  KEY `grade_subject_idx` (`Grade`,`Subject`) USING BTREE,
  KEY `sessionid_idx` (`SessionID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4433710 DEFAULT CHARSET=utf8 

分区代码：

通过按日期（RANGE）分区+分类（HASH）子分区，日期划到2015年，由于是教学应用软件，所以每年按学期划分为两区域

 

CREATE TABLE `usersession` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ClientSN` bigint(20) unsigned NOT NULL,
  `SessionID` char(40) NOT NULL,
  `IPAddress` varchar(64) NOT NULL COMMENT 'IP 地址',
  `UserName` char(25) DEFAULT NULL COMMENT '从加密狗提取的用户名',
  `Operation` int(10) DEFAULT NULL,
  `StartTime` datetime NOT NULL COMMENT '启动时间戳',
  `EndTime` datetime DEFAULT NULL COMMENT '关闭时间，异常关闭的情况下为空',
  `ApplicationID` smallint(6) NOT NULL COMMENT '应用程序ID（电子书、书架、白板）',
  `Grade` tinyint(4) NOT NULL DEFAULT '-1' COMMENT '年级',
  `Subject` tinyint(4) DEFAULT '-1',
  `BookCode` char(32) DEFAULT NULL,
  `Data` varchar(256) DEFAULT NULL COMMENT '例如：启动时选的哪本书',
  `UpdateTimeStamp` datetime NOT NULL,
  `IsUploaded` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否已上传到云端',
  PRIMARY KEY (`Id`,`StartTime`,`Grade`),
  KEY `clientsn_idx` (`ClientSN`) USING BTREE,
  KEY `starttime_subject_grade_idx` (`StartTime`,`Subject`,`Grade`) USING BTREE,
  KEY `grade_subject_idx` (`Grade`,`Subject`) USING BTREE,
  KEY `sessionid_idx` (`SessionID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4433710 DEFAULT CHARSET=utf8 COMMENT='记录用户会话信息'
/*!50100 PARTITION BY RANGE (to_days(StartTime))
SUBPARTITION BY HASH (Grade)
(PARTITION p20130201 VALUES LESS THAN (735265)
 (SUBPARTITION p0sp0 ENGINE = InnoDB,
  SUBPARTITION p0sp1 ENGINE = InnoDB,
  SUBPARTITION p0sp2 ENGINE = InnoDB,
  SUBPARTITION p0sp3 ENGINE = InnoDB,
  SUBPARTITION p0sp4 ENGINE = InnoDB,
  SUBPARTITION p0sp5 ENGINE = InnoDB,
  SUBPARTITION p0sp6 ENGINE = InnoDB,
  SUBPARTITION p0sp7 ENGINE = InnoDB,
  SUBPARTITION p0sp8 ENGINE = InnoDB,
  SUBPARTITION p0sp9 ENGINE = InnoDB),
 PARTITION p20130801 VALUES LESS THAN (735446)
 (SUBPARTITION p1sp0 ENGINE = InnoDB,
  SUBPARTITION p1sp1 ENGINE = InnoDB,
  SUBPARTITION p1sp2 ENGINE = InnoDB,
  SUBPARTITION p1sp3 ENGINE = InnoDB,
  SUBPARTITION p1sp4 ENGINE = InnoDB,
  SUBPARTITION p1sp5 ENGINE = InnoDB,
  SUBPARTITION p1sp6 ENGINE = InnoDB,
  SUBPARTITION p1sp7 ENGINE = InnoDB,
  SUBPARTITION p1sp8 ENGINE = InnoDB,
  SUBPARTITION p1sp9 ENGINE = InnoDB),
 PARTITION p20140201 VALUES LESS THAN (735630)
 (SUBPARTITION p2sp0 ENGINE = InnoDB,
  SUBPARTITION p2sp1 ENGINE = InnoDB,
  SUBPARTITION p2sp2 ENGINE = InnoDB,
  SUBPARTITION p2sp3 ENGINE = InnoDB,
  SUBPARTITION p2sp4 ENGINE = InnoDB,
  SUBPARTITION p2sp5 ENGINE = InnoDB,
  SUBPARTITION p2sp6 ENGINE = InnoDB,
  SUBPARTITION p2sp7 ENGINE = InnoDB,
  SUBPARTITION p2sp8 ENGINE = InnoDB,
  SUBPARTITION p2sp9 ENGINE = InnoDB),
 PARTITION p20140801 VALUES LESS THAN (735811)
 (SUBPARTITION p3sp0 ENGINE = InnoDB,
  SUBPARTITION p3sp1 ENGINE = InnoDB,
  SUBPARTITION p3sp2 ENGINE = InnoDB,
  SUBPARTITION p3sp3 ENGINE = InnoDB,
  SUBPARTITION p3sp4 ENGINE = InnoDB,
  SUBPARTITION p3sp5 ENGINE = InnoDB,
  SUBPARTITION p3sp6 ENGINE = InnoDB,
  SUBPARTITION p3sp7 ENGINE = InnoDB,
  SUBPARTITION p3sp8 ENGINE = InnoDB,
  SUBPARTITION p3sp9 ENGINE = InnoDB),
 PARTITION p20150201 VALUES LESS THAN (735995)
 (SUBPARTITION p4sp0 ENGINE = InnoDB,
  SUBPARTITION p4sp1 ENGINE = InnoDB,
  SUBPARTITION p4sp2 ENGINE = InnoDB,
  SUBPARTITION p4sp3 ENGINE = InnoDB,
  SUBPARTITION p4sp4 ENGINE = InnoDB,
  SUBPARTITION p4sp5 ENGINE = InnoDB,
  SUBPARTITION p4sp6 ENGINE = InnoDB,
  SUBPARTITION p4sp7 ENGINE = InnoDB,
  SUBPARTITION p4sp8 ENGINE = InnoDB,
  SUBPARTITION p4sp9 ENGINE = InnoDB),
 PARTITION p20150801 VALUES LESS THAN (736176)
 (SUBPARTITION p5sp0 ENGINE = InnoDB,
  SUBPARTITION p5sp1 ENGINE = InnoDB,
  SUBPARTITION p5sp2 ENGINE = InnoDB,
  SUBPARTITION p5sp3 ENGINE = InnoDB,
  SUBPARTITION p5sp4 ENGINE = InnoDB,
  SUBPARTITION p5sp5 ENGINE = InnoDB,
  SUBPARTITION p5sp6 ENGINE = InnoDB,
  SUBPARTITION p5sp7 ENGINE = InnoDB,
  SUBPARTITION p5sp8 ENGINE = InnoDB,
  SUBPARTITION p5sp9 ENGINE = InnoDB),
 PARTITION pmax VALUES LESS THAN MAXVALUE
 (SUBPARTITION p6sp0 ENGINE = InnoDB,
  SUBPARTITION p6sp1 ENGINE = InnoDB,
  SUBPARTITION p6sp2 ENGINE = InnoDB,
  SUBPARTITION p6sp3 ENGINE = InnoDB,
  SUBPARTITION p6sp4 ENGINE = InnoDB,
  SUBPARTITION p6sp5 ENGINE = InnoDB,
  SUBPARTITION p6sp6 ENGINE = InnoDB,
  SUBPARTITION p6sp7 ENGINE = InnoDB,
  SUBPARTITION p6sp8 ENGINE = InnoDB,
  SUBPARTITION p6sp9 ENGINE = InnoDB)) */;

 

查询演示：

查询时没加条件，所以扫描所有分区共2383137条记录

加上分区条件查询,可以看到只在对就的分区扫描共记录73924