mysql分表規則

標籤：script   類型   梯度   屬性   tail   foreign   複雜   說明   details   

author：skate
time：2013/05/14

Mysql分表準則

在大量使用mysql時，資料量大、高訪問時，為了提高效能需要分表處理，簡介下mysql分表的標準，後續會繼續補充

 

環境：
業務類型：OLTP
硬體：
cpu：8cpu 2.4GHZ
mem：48G
磁碟：raid5 6×sas

 

什麼樣的表需要拆分：根據表的體積、表的行數、訪問特點來衡量表是否需要拆分

一.拆分標準是：
  1.表的體積大於2G或行數大於1000w,以單表主鍵等簡單形式訪問資料，這個時候需要分表
  2.表的體積大於2G或行數大於500W,以兩表jion，小範圍查詢（結果集小100行）等形式訪問資料，這個時候需要分表
  3.表的體積大於2G或行數大於200w,以多表join，範圍查詢，order by，group by，高頻率等複雜形式訪問資料，尤其DML，這個時候需要分表
  4.表的欄位中含有text等大欄位的、varchar(500)以上的、很少使用的字元型欄位拆分成父子表,這種分表可以和以上聯合使用
  5.資料有時間到期特性的，需要做資料分表歸檔處理

 

只要達到上面任何一個標準，都需要做分表處理

 

二.分表方法：
  1.冷熱資料分表：適用小訪問量，冷資料很少使用
     1.1 單表欄位很多，把頻繁使用整型欄位的和非頻繁使用的字元型欄位或大欄位拆到兩個表中
     1.2 表資料具有時間到期性，把到期資料拆分到曆史表裡或者按時間梯度分表
  2.橫向分表：適用大訪問量
     2.1 如雜湊等分切表或其他基於對某數字取餘的切表，優點是方便資料分布，缺點是無法再擴充
     2.2 按主鍵id遞增分表，比如每100w個id一個分表，優點是方便擴充，缺點是壓力不均
     2.3 按日期分表，比如每天、每月、每年一個分表，優點是方便擴充，缺點是壓力不均
      
說明
1.表的體積如何預估
CREATE TABLE `td_skate` (
      `valid` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT ‘值id‘,
      `propertyid` BIGINT(20) NULL DEFAULT NULL COMMENT ‘屬性id‘,
      `text` VARCHAR(400) NULL DEFAULT NULL,
      `entext` VARCHAR(400) NULL DEFAULT NULL,
      `picurl` VARCHAR(200) NULL DEFAULT NULL COMMENT ‘屬性值說明圖片，儲存圖片相對位址‘,
      `isother` BIGINT(20) NULL DEFAULT NULL COMMENT ‘是否是other值， 0  否  1  是‘,
      `createtime` DATETIME NULL DEFAULT NULL COMMENT ‘建立時間‘,
      `createuser` BIGINT(20) NULL DEFAULT NULL COMMENT ‘建立使用者‘,
      `lastmodify` DATETIME NULL DEFAULT NULL COMMENT ‘最後修改時間‘,
      `updatetimeuser` BIGINT(20) NULL DEFAULT NULL COMMENT ‘最後修改人‘,
      `deletetime` DATETIME NULL DEFAULT NULL COMMENT ‘刪除時間‘,
      `deleteuser` BIGINT(20) NULL DEFAULT NULL COMMENT ‘刪除人‘,
      `description` VARCHAR(4000) NULL DEFAULT NULL COMMENT ‘產品描述‘,
      `isdelete` INT(11) NULL DEFAULT ‘0‘,
      PRIMARY KEY (`valid`),
      INDEX `fk_td_prodline_attrval_td_prodline_attr` (`propertyid`),
      CONSTRAINT `fk_td_prodline_attrval_td_prodline_attr` FOREIGN KEY (`propertyid`) REFERENCES `td_prodline_attr` (`propertyid`)
)
COLLATE=‘utf8_general_ci‘
ENGINE=InnoDB
AUTO_INCREMENT=2491650;

把表的所有欄位佔用位元組數相加，再乘以預估行數就是表的體積，比如上面的表，預估有1000W，那他的體積是
（8+8+400+400+200+8+8+8+8+8+8+8+4000+8）×10000000=50.8G，可以看到這個表設計非常不合理，可以修改如下：

int替代bigint
timestamp替代datetime
狀態位isdelete用tinyint替代
根據業務特點看能否把varchar(4000)放到一個字表中

最佳化後表大小：（4+4+400+400+200+4+4+4+4+4+4+4+1）×10000000=10.37G，如果要進一步提升效能，需要刪除外鍵，分表，保證單表在2G以下。
如果需要查看description資訊，通過主鍵關聯查看子表，只會掃描有效子表資訊， 效能將會提升非常大。  

2.表的行數預估就很簡單，根據業務特點，訪問量等預估

mysql的整數型別有這樣幾種：
類型      佔用位元組
tinyint        1
smallint     2
mediumint 3
int              4
bigint         8
這是決定儲存需要佔用多少位元組
tinyint(M), M預設為4;
SMALLINT(M), M預設為6;
MEDIUMINT(M), M預設為9;
INT(M),M預設為11;
BIGINT(M),M預設為20.

轉載地址：http://blog.csdn.net/wyzxg/article/details/8930658

mysql分表規則（轉）