當(dāng)數(shù)據(jù)庫數(shù)據(jù)量漲到一定數(shù)量時，性能就成為我們不能不關(guān)注的問題，如何優(yōu)化呢？ 常用的方式不外乎那么幾種：

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

　　　　優(yōu)點：提高并發(fā)量，減小鎖的粒度
　　　　缺點：代碼維護成本高，相關(guān)sql都需要改動

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

　　　　優(yōu)點：代碼維護量小，基本不用改動，提高IO吞吐量
　　　　缺點：表的并發(fā)程度沒有增加

　　3、拆分業(yè)務(wù)，這個本質(zhì)還是分表。

　　　　優(yōu)點：長期支持更好
　　　　缺點：代碼邏輯重構(gòu)，工作量很大

　　當(dāng)然，每種情況都有合適的應(yīng)用場景，需要根據(jù)具體業(yè)務(wù)具體選擇。由于分表和拆分業(yè)務(wù)和mysql本身關(guān)系不大屬于業(yè)務(wù)層面，我們只說和數(shù)據(jù)庫關(guān)系最緊密的方式：表分區(qū)。不過使用表分區(qū)有個前提就是你的數(shù)據(jù)庫必須支持。那么，怎么知道我的數(shù)據(jù)庫是否支持表分區(qū)呢 ？ 請執(zhí)行下面命令　　

據(jù)說5.4一下的版本是另外一個命令，不過我沒有測試

1、創(chuàng)建分區(qū)表

 　　如果要使用表的分區(qū)優(yōu)勢，不但要數(shù)據(jù)庫版本支持分區(qū)，關(guān)鍵要建分區(qū)表，這個表和普通表不一樣，并且必須建表的時候就要指定分區(qū)，否則無法把普通表改成分區(qū)表。那么，如果創(chuàng)建一個分區(qū)表呢？ 其他很簡單，請看下面建表語句

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)(    -----指定分區(qū)方式
  PARTITION p0 VALUES less THAN (10),-- 分了兩個區(qū)
  PARTITION p1 VALUES less THAN (20)
)

上面語句建了一個“T_part”表，有兩個字段f_id和f_name，并且根據(jù)RANGE方式把表分成兩個區(qū)p0、p1，當(dāng)f_id小于10放入p0分區(qū)，當(dāng)f_id大于0小于20放入分區(qū)p1. 那么當(dāng)f_id大于20的數(shù)據(jù)放入哪個分區(qū)呢？ 你猜對了，insert語句會報錯。

　　看到了吧，創(chuàng)建分區(qū)表就這么簡單！當(dāng)然，你隨時可以添加刪除分區(qū)，不過要注意，刪除分區(qū)的時候會把當(dāng)前分區(qū)下所有數(shù)據(jù)都刪除。

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

　　1）RANGE分區(qū)：

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

　　　　上面的例子就是RANGE分區(qū).

　　2）LIST分區(qū)：

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

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), ----區(qū)間值不能重復(fù)
 PARTITION p1 VALUES in(4,5,6)
);

3)HASH分區(qū)：

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

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；---分區(qū)個數(shù)

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

　　4）LINEAR HASH分區(qū)：

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

　　5）KEY分區(qū)：

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

　　　　KEY分區(qū)的語法和HASH語法類似，只是把關(guān)鍵字改成KEY?！　?/p>

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）子分區(qū)：

　　　　子分區(qū)的意思就是在分區(qū)的基礎(chǔ)上再次分區(qū)。且每個分區(qū)必須有相同個數(shù)的子分區(qū)。

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分區(qū)，每個分區(qū)根據(jù)hash有分別有兩個子分區(qū)，實際上整個表分成2×2=4個分區(qū)。當(dāng)然，要詳細定義每個分區(qū)屬性也是可以的

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'
  )
)

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

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

最新評論