MySql數(shù)據(jù)庫(kù)分區(qū)與分表方法的詳細(xì)說(shuō)明及分區(qū)與分表說(shuō)明

1、為什么要分表和分區(qū)

日常開(kāi)發(fā)中我們經(jīng)常會(huì)遇到大表的情況，所謂的大表是指存儲(chǔ)了百萬(wàn)級(jí)乃至千萬(wàn)級(jí)條記錄的表。這樣的表過(guò)于龐大，導(dǎo)致數(shù)據(jù)庫(kù)在查詢和插入的時(shí)候耗時(shí)太長(zhǎng)，性能低下，如果涉及聯(lián)合查詢的情況，性能會(huì)更加糟糕。分表和表分區(qū)的目的就是減少數(shù)據(jù)庫(kù)的負(fù)擔(dān)，提高數(shù)據(jù)庫(kù)的效率，通常點(diǎn)來(lái)講就是提高表的增刪改查效率。

2、什么是分表和分區(qū)

2.1 分表

分表是將一個(gè)大表按照一定的規(guī)則分解成多張具有獨(dú)立存儲(chǔ)空間的實(shí)體表，我們可以稱為子表，每個(gè)表都對(duì)應(yīng)三個(gè)文件，MYD數(shù)據(jù)文件，.MYI索引文件，.frm表結(jié)構(gòu)文件。這些子表可以分布在同一塊磁盤上，也可以在不同的機(jī)器上。app讀寫(xiě)的時(shí)候根據(jù)事先定義好的規(guī)則得到對(duì)應(yīng)的子表名，然后去操作它。

2.2 分區(qū)

分區(qū)和分表相似，都是按照規(guī)則分解表。不同在于分表將大表分解為若干個(gè)獨(dú)立的實(shí)體表，而分區(qū)是將數(shù)據(jù)分段劃分在多個(gè)位置存放，可以是同一塊磁盤也可以在不同的機(jī)器。分區(qū)后，表面上還是一張表，但數(shù)據(jù)散列到多個(gè)位置了。app讀寫(xiě)的時(shí)候操作的還是大表名字，db自動(dòng)去組織分區(qū)的數(shù)據(jù)。
分區(qū)的主要目的是為了在特定的SQL操作中減少數(shù)據(jù)讀寫(xiě)的總量以縮減響應(yīng)時(shí)間。

2.3 mysql分表和分區(qū)有什么聯(lián)系呢？

1）、都能提高mysql的性能，在高并發(fā)狀態(tài)下都有一個(gè)良好的表現(xiàn)。
2）、分表和分區(qū)不矛盾，可以相互配合的，對(duì)于那些大訪問(wèn)量，并且表數(shù)據(jù)比較多的表，我們可以采取分表和分區(qū)結(jié)合的方式，訪問(wèn)量不大，但是表數(shù)據(jù)很多的表，我們可以采取分區(qū)的方式等。
3）、分表技術(shù)是比較麻煩的，需要手動(dòng)去創(chuàng)建子表，app服務(wù)端讀寫(xiě)時(shí)候需要計(jì)算子表名。采用merge好一些，但也要?jiǎng)?chuàng)建子表和配置子表間的union關(guān)系。
4）、表分區(qū)相對(duì)于分表，操作方便，不需要?jiǎng)?chuàng)建子表。

3、分表的幾種方式

3.1 mysql集群

它并不是分表，但起到了和分表相同的作用。集群可分擔(dān)數(shù)據(jù)庫(kù)的操作次數(shù)，將任務(wù)分擔(dān)到多臺(tái)數(shù)據(jù)庫(kù)上。集群可以讀寫(xiě)分離，減少讀寫(xiě)壓力。從而提升數(shù)據(jù)庫(kù)性能。

3.2 自定義規(guī)則分表

大表可以按照業(yè)務(wù)的規(guī)則來(lái)分解為多個(gè)子表。通常為以下幾種類型，也可自己定義規(guī)則。

Range（范圍）–這種模式允許將數(shù)據(jù)劃分不同范圍。例如可以將一個(gè)表通過(guò)年份劃分成若干個(gè)分區(qū)。
Hash（哈希）–這中模式允許通過(guò)對(duì)表的一個(gè)或多個(gè)列的Hash Key進(jìn)行計(jì)算，最后通過(guò)這個(gè)Hash碼不同數(shù)值對(duì)應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)。例如可以建立一個(gè)對(duì)表主鍵進(jìn)行分區(qū)的表。
Key（鍵值）–上面Hash模式的一種延伸，這里的Hash Key是MySQL系統(tǒng)產(chǎn)生的。
List（預(yù)定義列表）–這種模式允許系統(tǒng)通過(guò)預(yù)定義的列表的值來(lái)對(duì)數(shù)據(jù)進(jìn)行分割。
Composite（復(fù)合模式）–以上模式的組合使用　

分表規(guī)則與分區(qū)規(guī)則一樣，在分區(qū)模塊詳細(xì)介紹。

下面以Range簡(jiǎn)單介紹下如何分表（按照年份表）。

假設(shè)表結(jié)構(gòu)有4個(gè)字段：自增id，姓名，存款金額，存款日期
把存款日期作為規(guī)則分表，分別創(chuàng)建幾個(gè)表
2011年：account_2011
2012年：account_2012
……
2015年：account_2015
app在讀寫(xiě)的時(shí)候根據(jù)日期來(lái)查找對(duì)應(yīng)的表名，需要手動(dòng)來(lái)判定。

var getTableName = function() {
  var data = {
    name: 'tom',
    money: 2800.00,
    date: '201410013059'
  };
  var tablename = 'account_';
  var year = parseInt(data.date.substring(0, 4));
  if (year < 2012) {
    tablename += 2011; // account_2011
  } else if (year < 2013) {
    tablename += 2012; // account_2012
  } else if (year < 2014) {
    tablename += 2013; // account_2013
  } else if (year < 2015) {
    tablename += 2014; // account_2014
  } else {
    tablename += 2015; // account_2015
  }
  return tablename;
}

3.3 利用merge存儲(chǔ)引擎來(lái)實(shí)現(xiàn)分表

merge分表，分為主表和子表，主表類似于一個(gè)殼子，邏輯上封裝了子表，實(shí)際上數(shù)據(jù)都是存儲(chǔ)在子表中的。

我們可以通過(guò)主表插入和查詢數(shù)據(jù)，如果清楚分表規(guī)律，也可以直接操作子表。

子表2011年

CREATE TABLE `account_2011` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money` float NOT NULL ,
`tradeDate` datetime NOT NULL
PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=2
CHECKSUM=0
ROW_FORMAT=DYNAMIC
DELAY_KEY_WRITE=0
;

子表2012年

CREATE TABLE `account_2012` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money` float NOT NULL ,
`tradeDate` datetime NOT NULL
PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=2
CHECKSUM=0
ROW_FORMAT=DYNAMIC
DELAY_KEY_WRITE=0
;

主表，所有年

CREATE TABLE `account_all` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money` float NOT NULL ,
`tradeDate` datetime NOT NULL
PRIMARY KEY (`id`)
)
ENGINE=MRG_MYISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
UNION=(`account_2011`,`account_2012`)
INSERT_METHOD=LAST
ROW_FORMAT=DYNAMIC
;

創(chuàng)建主表的時(shí)候有個(gè)INSERT_METHOD，指明插入方式，取值可以是：0 不允許插入；FIRST 插入到UNION中的第一個(gè)表； LAST 插入到UNION中的最后一個(gè)表。

通過(guò)主表查詢的時(shí)候，相當(dāng)于將所有子表合在一起查詢。這樣并不能體現(xiàn)分表的優(yōu)勢(shì)，建議還是查詢子表。

4、分區(qū)的幾種方式

4.1 Range

create table range( 
　　id int(11), 
　　money int(11) unsigned not null, 
　　date datetime 
　　)partition by range(year(date))( 
　　partition p2007 values less than (2008), 
　　partition p2008 values less than (2009), 
　　partition p2009 values less than (2010) 
　　partition p2010 values less than maxvalue 
);

4.2 List

create table list( 
　　a int(11), 
　　b int(11) 
　　)(partition by list (b) 
　　partition p0 values in (1,3,5,7,9), 
　　partition p1 values in (2,4,6,8,0) 
　);

4.3 Hash

create table hash( 
　　a int(11), 
　　b datetime 
　　)partition by hash (YEAR(b) 
　　partitions 4;

4.4 key

create table t_key( 
　　a int(11), 
　　b datetime) 
　　partition by key (b) 
　　partitions 4;

4.5 分區(qū)管理

4.5.1 新增分區(qū)

ALTER TABLE sale_data
ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

4.5.2 刪除分區(qū)

當(dāng)刪除了一個(gè)分區(qū)，也同時(shí)刪除了該分區(qū)中所有的數(shù)據(jù)。

ALTER TABLE sale_data DROP PARTITION p201010;

4.5.3 合并分區(qū)

下面的SQL，將p201001 - p201009 合并為3個(gè)分區(qū)p2010Q1 - p2010Q3

ALTER TABLE sale_data
REORGANIZE PARTITION p201001,p201002,p201003,
p201004,p201005,p201006,
p201007,p201008,p201009 INTO
(
PARTITION p2010Q1 VALUES LESS THAN (201004),
PARTITION p2010Q2 VALUES LESS THAN (201007),
PARTITION p2010Q3 VALUES LESS THAN (201010)
);

以上就是MySql數(shù)據(jù)庫(kù)分區(qū)和分表方法的詳解及分區(qū)和分表介紹的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！