MySql資料庫分區和分錶方法的詳解及分區和分錶介紹

這篇文章主要為大家詳細介紹了MySql資料庫分區和分錶方法，告訴大家甚麼是分錶和分區，mysql分錶和分區有什麼聯繫，具有一定的參考價值，有興趣的小夥伴們可以參考一下

1、為什麼要分錶和分區

我們經常會在日常開發中遇到大表的情況，所謂的大表是指儲存了百萬級乃至千萬級筆記錄的表。這樣的表過於龐大，導致資料庫在查詢和插入的時候耗時太長，效能低下，如果涉及聯合查詢的情況，效能會更加糟糕。分錶和表格分割的目的就是減少資料庫的負擔，提高資料庫的效率，通常點來講就是提高表格的增刪改查效率。

2、什麼是分錶與分割區

#2.1 分錶

分錶是將一個大表按照一定的規則分解成多張具有獨立存儲空間的實體表，我們可以稱為子表，每個表都對應三個文件，MYD數據文件，.MYI索引文件，.frm表結構文件。這些子表可以分佈在同一塊磁碟上，也可以在不同的機器上。 app讀寫的時候根據事先定義好的規則得到對應的子表名，然後去操作它。

2.2 分割區

分割區和分錶相似，都是依照規則分解表。不同在於分錶將大表分解為若干個獨立的實體表，而分割區是將資料分段劃分在多個位置存放，可以是同一塊磁碟也可以在不同的機器。分區後，表面上還是一張表，但資料散列到多個位置了。 app讀寫的時候操作的還是大表名字，db自動去組織分區的資料。
分區的主要目的是為了在特定的SQL操作中減少資料讀寫的總量以縮減回應時間。

2.3 mysql分錶和分割區有什麼關聯呢？

1）、都能提升mysql的效能，在高並發狀態下都有一個好的表現。
2）、分錶和分區不矛盾，可以相互配合的，對於那些大訪問量，並且表數據比較多的表，我們可以採取分錶和分區結合的方式，訪問量不大，但是表資料很多的表，我們可以採取分區的方式等。
3）、分錶技術是比較麻煩的，需要手動去建立子表，app服務端讀寫時候需要計算子表名。採用merge好一些，但也要建立子表和配置子表間的union關係。
4）、表格分割區相對於分錶，操作方便，不需要建立子表。

3、分錶的幾種方式

#3.1 mysql叢集

它並不是分錶，但起到了和分錶相同的作用。叢集可分擔資料庫的操作次數，將任務分擔到多台資料庫上。集群可以讀寫分離，減少讀寫壓力。從而提升資料庫效能。

3.2 自訂規則分錶

#大表可以依照業務的規則來分解為多個子表。通常為以下幾種類型，也可自行定義規則。

Range（範圍）–這種模式允許將資料分割不同範圍。例如可以將一個表格透過年份劃分成若干個分區。
Hash（雜湊）–這中模式允許透過對錶的一個或多個列的Hash Key進行計算，最後透過這個Hash碼不同數值對應的資料區域進行分區。例如可以建立一個對錶主鍵進行分區的表。
Key（鍵值）–上面Hash模式的一種延伸，這裡的Hash Key是MySQL系統產生的。
List（預定義清單）–這個模式允許系統透過預先定義的清單的值來分割資料。
Composite（複合模式）–以上模式的組合使用

分錶規則與分區規則一樣，在分區模組詳細介紹。

以下以Range簡單介紹下如何分錶（依照年份表）。

假設表結構有4個欄位：自增id，姓名，存款金額，存款日期
把存款日期當作規則分錶，分別建立幾個表
2011年：account_2011
2012年：account_2012
……
2015年：account_2015
app在讀寫的時候根據日期來找出對應的表名，需要手動來判定。

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儲存引擎來實現分錶

merge分錶，分成主表和子表，主表類似於一個殼子，邏輯上封裝了子表，實際上資料都是儲存在子表中的。

我們可以透過主表插入和查詢數據，如果清楚分錶規律，也可以直接操作子表。

子表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
;

創建主表的時候有個INSERT_METHOD，指明插入方式，取值可以是：0 不允許插入；FIRST 插入到UNION中的第一個表； LAST 插入到UNION中的最後一個表。

透過主表查詢的時候，相當於將所有子表合在一起查詢。這樣並不能反映分錶的優勢，建議還是查詢子表。

4、分割區的幾種方式

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 分割區管理

#4.5.1 新增分割區

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

4.5.2 刪除分割區

當刪除了一個分割區，也同時刪除了該分割區中所有的資料。

ALTER TABLE sale_data DROP PARTITION p201010;

4.5.3 合併分割區

下面的SQL，將p201001 - p201009 合併為3個分割區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資料庫分區和分錶方法的詳解及分區和分錶介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！