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

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

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

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

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

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

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

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

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

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

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

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),-- 分了兩個(gè)區(qū)
  PARTITION p1 VALUES less THAN (20)
)

上面語(yǔ)句建了一個(gè)“T_part”表，有兩個(gè)字段f_id和f_name，并且根據(jù)RANGE方式把表分成兩個(gè)區(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ù)放入哪個(gè)分區(qū)呢？ 你猜對(duì)了，insert語(yǔ)句會(huì)報(bào)錯(cuò)。

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

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

　　1）RANGE分區(qū)：

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

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

　　2）LIST分區(qū)：

　　　　MySQL中的LIST分區(qū)在很多方面類似于RANGE分區(qū)。和按照RANGE分區(qū)一樣，每個(gè)分區(qū)必須明確定義。它們的主要區(qū)別在于，LIST分區(qū)中每個(gè)分區(qū)的定義和選擇是基于某列的值從屬于一個(gè)值列表集中的一個(gè)值，而RANGE分區(qū)是從屬于一個(gè)連續(xù)區(qū)間值的集合。LIST分區(qū)通過使用“PARTITION BY LIST(expr)”來實(shí)現(xiàn)，其中“expr” 是某列值或一個(gè)基于某個(gè)列值、并返回一個(gè)整數(shù)值的表達(dá)式，然后通過“VALUES IN (value_list)”的方式來定義每個(gè)分區(qū)，其中“value_list”是一個(gè)通過逗號(hào)分隔的整數(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ū)中，必須明確指定一個(gè)給定的列值或列值集合應(yīng)該保存在哪個(gè)分區(qū)中；而在HASH分區(qū)中，MySQL 自動(dòng)完成這些工作，你所要做的只是基于將要被哈希的列值指定一個(gè)列值或表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。要使用HASH分區(qū)來分割一個(gè)表，要在CREATE TABLE 語(yǔ)句上添加一個(gè)“PARTITION BY HASH (expr)”子句，其中“expr”是一個(gè)返回一個(gè)整數(shù)的表達(dá)式。它可以僅僅是字段類型為MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一個(gè)“PARTITIONS num”子句，其中num 是一個(gè)非負(fù)的整數(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ū)個(gè)數(shù)

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

　　4）LINEAR HASH分區(qū)：

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

　　5）KEY分區(qū)：

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

　　　　KEY分區(qū)的語(yǔ)法和HASH語(yǔ)法類似，只是把關(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ū)。且每個(gè)分區(qū)必須有相同個(gè)數(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)
)

上面語(yǔ)句的意思是，建立兩個(gè)range分區(qū)，每個(gè)分區(qū)根據(jù)hash有分別有兩個(gè)子分區(qū)，實(shí)際上整個(gè)表分成2×2=4個(gè)分區(qū)。當(dāng)然，要詳細(xì)定義每個(gè)分區(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'
  )
)

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

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

最新評(píng)論