程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL優化之分區表，mysql分區表

MySQL優化之分區表，mysql分區表

編輯：MySQL綜合教程

MySQL優化之分區表，mysql分區表

當數據庫數據量漲到一定數量時，性能就成為我們不能不關注的問題，如何優化呢？常用的方式不外乎那麼幾種：

　　1、分表，即把一個很大的表達數據分到幾個表中，這樣每個表數據都不多。

　　　　優點：提高並發量，減小鎖的粒度
　　　　缺點：代碼維護成本高，相關sql都需要改動

　　2、分區，所有的數據還在一個表中，但物理存儲數據根據一定的規則存放在不同的文件中，文件也可以放到另外磁盤上

　　　　優點：代碼維護量小，基本不用改動，提高IO吞吐量
　　　　缺點：表的並發程度沒有增加

　　3、拆分業務，這個本質還是分表。

　　　　優點：長期支持更好
　　　　缺點：代碼邏輯重構，工作量很大

　　當然，每種情況都有合適的應用場景，需要根據具體業務具體選擇。由於分表和拆分業務和mysql本身關系不大屬於業務層面，我們只說和數據庫關系最緊密的方式：表分區。不過使用表分區有個前提就是你的數據庫必須支持。那麼，怎麼知道我的數據庫是否支持表分區呢？請執行下面命令　　

復制代碼代碼如下:
show plugins; ---在mysql控制台中執行

據說5.4一下的版本是另外一個命令，不過我沒有測試
復制代碼代碼如下:
show variables like '%part%';

　　數據庫的表分區一般有兩種方式：縱向和橫向。縱向就是把表中不同字段分到不同數據文件中。橫向是把表中前一部分數據放到一個文件中，另一部分數據放到一個文件中。mysql只支持後後一種方式，橫向拆分。

1、創建分區表

　　如果要使用表的分區優勢，不但要數據庫版本支持分區，關鍵要建分區表，這個表和普通表不一樣，並且必須建表的時候就要指定分區，否則無法把普通表改成分區表。那麼，如果創建一個分區表呢？其他很簡單，請看下面建表語句

CREATE TABLE `T_part` (
  `f_id` INT DEFAULT NULL,
  `f_name` VARCHAR (20) DEFAULT NULL,
  PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION BY RANGE (f_id)(    -----指定分區方式
  PARTITION p0 VALUES less THAN (10),-- 分了兩個區
  PARTITION p1 VALUES less THAN (20)
)

上面語句建了一個“T_part”表，有兩個字段f_id和f_name，並且根據RANGE方式把表分成兩個區p0、p1，當f_id小於10放入p0分區，當f_id大於0小於20放入分區p1. 那麼當f_id大於20的數據放入哪個分區呢？你猜對了，insert語句會報錯。

　　看到了吧，創建分區表就這麼簡單！當然，你隨時可以添加刪除分區，不過要注意，刪除分區的時候會把當前分區下所有數據都刪除。

復制代碼代碼如下:
alter table T_part add partition(partition p2 values less than (MAXVALUE)); ---新增分區
alter table T_part DROP partition p2; ----刪除分區

2、表分區的幾種方式
　　mysql支持5種分區方式：RANGE分區、LIST分區、HASH分區、LINEAR HASH分區和KEY分區。每種分區都有自己的使用場景。

　　1）RANGE分區：

　　　　RANGE分區的表是通過如下一種方式進行分區的，每個分區包含那些分區表達式的值位於一個給定的連續區間內的行。這些區間要連續且不能相互重疊，使用VALUES LESS THAN操作符來進行定義。

　　　　上面的例子就是RANGE分區.

　　2）LIST分區：

　　　　MySQL中的LIST分區在很多方面類似於RANGE分區。和按照RANGE分區一樣，每個分區必須明確定義。它們的主要區別在於，LIST分區中每個分區的定義和選擇是基於某列的值從屬於一個值列表集中的一個值，而RANGE分區是從屬於一個連續區間值的集合。LIST分區通過使用“PARTITION BY LIST(expr)”來實現，其中“expr” 是某列值或一個基於某個列值、並返回一個整數值的表達式，然後通過“VALUES IN (value_list)”的方式來定義每個分區，其中“value_list”是一個通過逗號分隔的整數列表。

CREATE TABLE `T_list` (
  `f_id` INT DEFAULT NULL,
  `f_name` VARCHAR (20) DEFAULT NULL,
  PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION by list(f_id)
(
 PARTITION p0 VALUES in(1,2,3), ----區間值不能重復
 PARTITION p1 VALUES in(4,5,6)
);

3)HASH分區：

　　　　HASH分區主要用來確保數據在預先確定數目的分區中平均分布。在RANGE和LIST分區中，必須明確指定一個給定的列值或列值集合應該保存在哪個分區中；而在HASH分區中，MySQL 自動完成這些工作，你所要做的只是基於將要被哈希的列值指定一個列值或表達式，以及指定被分區的表將要被分割成的分區數量。要使用HASH分區來分割一個表，要在CREATE TABLE 語句上添加一個“PARTITION BY HASH (expr)”子句，其中“expr”是一個返回一個整數的表達式。它可以僅僅是字段類型為MySQL 整型的一列的名字。此外，你很可能需要在後面再添加一個“PARTITIONS num”子句，其中num 是一個非負的整數，它表示表將要被分割成分區的數量。

CREATE TABLE `T_hash` (
  `f_id` INT DEFAULT NULL,
  `f_name` VARCHAR (20) DEFAULT NULL,
  PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION BY HASH(f_id) ---可以指定多列
PARTITIONS 4；---分區個數

“expr”還可以是MySQL 中有效的任何函數或其他表達式，只要它們返回一個既非常數、也非隨機數的整數。（換句話說，它既是變化的但又是確定的）。但是應當記住，每當插入或更新（或者可能刪除）一行，這個表達式都要計算一次；這意味著非常復雜的表達式可能會引起性能問題，尤其是在執行同時影響大量行的運算（例如批量插入）的時候。最有效率的哈希函數是只對單個表列進行計算，並且它的值隨列值進行一致地增大或減小，因為這考慮了在分區范圍上的“修剪”。也就是說，表達式值和它所基於的列的值變化越接近，MySQL就可以越有效地使用該表達式來進行HASH分區。

　　4）LINEAR HASH分區：

　　　　MySQL還支持線性哈希功能，它與常規哈希的區別在於，線性哈希功能使用的一個線性的2的冪（powers-oftwo）運算法則，而常規哈希使用的是求哈希函數值的模數。線性哈希分區和常規哈希分區在語法上的唯一區別在於，在“PARTITION BY” 子句中添加“LINEAR”關鍵字.

　　5）KEY分區：

　　　　按照KEY進行分區類似於按照HASH分區，除了HASH分區使用的用戶定義的表達式，而KEY分區的哈希函數是由MySQL 服務器提供。MySQL 簇（Cluster）使用函數MD5()來實現KEY分區；對於使用其他存儲引擎的表，服務器使用其自己內部的哈希函數，這些函數是基於與PASSWORD()一樣的運算法則。

　　　　KEY分區的語法和HASH語法類似，只是把關鍵字改成KEY。　　

CREATE TABLE `T_key` (
  `f_id` INT DEFAULT NULL,
  `f_name` VARCHAR (20) DEFAULT NULL,
  PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION BY LINEAR key(f_id)
PARTITIONS 3；

6）子分區：

　　　　子分區的意思就是在分區的基礎上再次分區。且每個分區必須有相同個數的子分區。

CREATE TABLE `T_part` (
  `f_id` INT DEFAULT NULL,
  `f_name` VARCHAR (20) DEFAULT NULL,
  PRIMARY KEY (`f_id`)
) 
PARTITION BY RANGE (f_id)
SUBPARTITION BY HASH(F_ID)
SUBPARTITIONS 2
(
  PARTITION p0   VALUES     less THAN (10),
  PARTITION p1  VALUES    less THAN (20)
)

上面語句的意思是，建立兩個range分區，每個分區根據hash有分別有兩個子分區，實際上整個表分成2×2=4個分區。當然，要詳細定義每個分區屬性也是可以的

CREATE TABLE `T_part` (
  `f_id` INT DEFAULT NULL,
  `f_name` VARCHAR (20) DEFAULT NULL,
  PRIMARY KEY (`f_id`)
) 
PARTITION BY RANGE (f_id)
SUBPARTITION BY HASH(F_ID)
(
  PARTITION p0   VALUES less THAN (10)
  (
    SUBPARTITION s0 
      DATA DIRECTORY = '/disk0/data' 
      INDEX DIRECTORY = '/disk0/idx',
    SUBPARTITION s1 
      DATA DIRECTORY = '/disk1/data' 
      INDEX DIRECTORY = '/disk1/idx'
  ),
  PARTITION p1  VALUES less THAN (20)
  (
    SUBPARTITION s2
      DATA DIRECTORY = '/disk0/data' 
      INDEX DIRECTORY = '/disk0/idx',
    SUBPARTITION s3 
      DATA DIRECTORY = '/disk1/data' 
      INDEX DIRECTORY = '/disk1/idx'
  )
)

這樣可以對每個分區指定具體存儲磁盤。前提磁盤是存在的。　　

　　MySQL 中的分區在禁止空值（NULL）上沒有進行處理，無論它是一個列值還是一個用戶定義表達式的值。一般而言，在這種情況下MySQL 把NULL視為0。如果你希望回避這種做法，你應該在設計表時不允許空值；最可能的方法是，通過聲明列“NOT NULL”來實現這一點。