MySQL優(yōu)化之分區(qū)表_MySQL

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

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

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

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

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

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

　　　　優(yōu)點(diǎn)：長期支持更好
　　　　缺點(diǎn)：代碼邏輯重構(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í)行下面命令　　

代碼如下:

show plugins;? —在mysql控制臺中執(zhí)行

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

代碼如下:

?show variables like ‘%part%’;

?　　數(shù)據(jù)庫的表分區(qū)一般有兩種方式：縱向和橫向。縱向就是把表中不同字段分到不同數(shù)據(jù)文件中。橫向是把表中前一部分?jǐn)?shù)據(jù)放到一個文件中，另一部分?jǐn)?shù)據(jù)放到一個文件中。mysql只支持后后一種方式，橫向拆分。

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語句會報(bào)錯。

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

代碼如下:

alter table T_part add partition(partition p2 values less than (MAXVALUE));? —新增分區(qū)
alter table T_part DROP partition p2; —-刪除分區(qū)

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

　　1）RANGE分區(qū)：

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

　　　　上面的例子就是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)”來實(shí)現(xiàn)，其中“expr” 是某列值或一個基于某個列值、并返回一個整數(shù)值的表達(dá)式，然后通過“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 自動完成這些工作，你所要做的只是基于將要被哈希的列值指定一個列值或表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。要使用HASH分區(qū)來分割一個表，要在CREATE TABLE 語句上添加一個“PARTITION BY HASH (expr)”子句，其中“expr”是一個返回一個整數(shù)的表達(dá)式。它可以僅僅是字段類型為MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一個“PARTITIONS num”子句，其中num 是一個非負(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ū)個數(shù)

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

　　4）LINEAR HASH分區(qū)：

　　　　MySQL還支持線性哈希功能，它與常規(guī)哈希的區(qū)別在于，線性哈希功能使用的一個線性的2的冪（powers-oftwo）運(yùn)算法則，而常規(guī) 哈希使用的是求哈希函數(shù)值的模數(shù)。線性哈希分區(qū)和常規(guī)哈希分區(qū)在語法上的唯一區(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ū)；對于使用其他存儲引擎的表，服務(wù)器使用其自己內(nèi)部的 哈希函數(shù)，這些函數(shù)是基于與PASSWORD()一樣的運(yùn)算法則。

　　　　KEY分區(qū)的語法和HASH語法類似，只是把關(guān)鍵字改成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）子分區(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ū)，實(shí)際上整個表分成2×2=4個分區(qū)。當(dāng)然，要詳細(xì)定義每個分區(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）上沒有進(jìn)行處理，無論它是一個列值還是一個用戶定義表達(dá)式的值。一般而言，在這種情況下MySQL 把NULL視為0。如果你希望回避這種做法，你應(yīng)該在設(shè)計(jì)表時不允許空值；最可能的方法是，通過聲明列“NOT NULL”來實(shí)現(xiàn)這一點(diǎn)。