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