當(dāng)前位置：首頁(yè) > 人文社科 > 生活经验 >内容正文

生活经验

第18章：MYSQL分区

發(fā)布時(shí)間：2023/11/27 生活经验 30 豆豆

生活随笔收集整理的這篇文章主要介紹了第18章：MYSQL分区小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

第18章：分區(qū)

18.1. MySQL中的分區(qū)概述18.2. 分區(qū)類型

18.2.1. RANGE分區(qū)18.2.2. LIST分區(qū)18.2.3. HASH分區(qū)18.2.4. KEY分區(qū)18.2.5. 子分區(qū)18.2.6. MySQL分區(qū)處理NULL值的方式

18.3. 分區(qū)管理

18.3.1. RANGE和LIST分區(qū)的管理18.3.2. HASH和KEY分區(qū)的管理18.3.3. 分區(qū)維護(hù)18.3.4. 獲取關(guān)于分區(qū)的信息

本章討論MySQL 5.1.中實(shí)現(xiàn)的分區(qū)。關(guān)于分區(qū)和分區(qū)概念的介紹可以在18.1節(jié)，“MySQL中的分區(qū)概述”中找到。MySQL 5.1?支持哪幾種類型的分區(qū)，在18.2節(jié)，“分區(qū)類型”?中討論。關(guān)于子分區(qū)在18.2.5節(jié)，“子分區(qū)”?中討論。現(xiàn)有分區(qū)表中分區(qū)的增加、刪除和修改的方法在18.3節(jié)，“分區(qū)管理”?中介紹。和分區(qū)表一同使用的表維護(hù)命令在18.3.3節(jié)，“分區(qū)維護(hù)”?中介紹。

請(qǐng)注意：MySQL 5.1中的分區(qū)實(shí)現(xiàn)仍然很新（pre-alpha品質(zhì)），此時(shí)還不是可生產(chǎn)的（not production-ready）。同樣，許多也適用于本章：在這里描述的一些功能還沒有實(shí)際上實(shí)現(xiàn)(分區(qū)維護(hù)和重新分區(qū)命令），其他的可能還沒有完全如所描述的那樣實(shí)現(xiàn)(例如,?用于分區(qū)的數(shù)據(jù)目錄（DATA DIRECTORY）和索引目錄（INDEX DIRECTORY）選項(xiàng)受到Bug #13520)?不利的影響).?我們已經(jīng)設(shè)法在本章中標(biāo)出這些差異。在提出缺陷報(bào)告前，我們鼓勵(lì)參考下面的一些資源：

MySQL 分區(qū)論壇

這是一個(gè)為對(duì)MySQL分區(qū)技術(shù)感興趣或用MySQL分區(qū)技術(shù)做試驗(yàn)提供的官方討論論壇。來自MySQL?的開發(fā)者和其他的人，會(huì)在上面發(fā)表和更新有關(guān)的材料。它由分區(qū)開發(fā)和文獻(xiàn)團(tuán)隊(duì)的成員負(fù)責(zé)監(jiān)控。
分區(qū)缺陷報(bào)告

已經(jīng)歸檔在缺陷系統(tǒng)中的、所有分區(qū)缺陷的一個(gè)列表，而無論這些缺陷的年限、嚴(yán)重性或當(dāng)前的狀態(tài)如何。根據(jù)許多規(guī)則可以對(duì)這些缺陷進(jìn)行篩選，或者可以從MySQL缺陷系統(tǒng)主頁(yè)開始，然后查找你特別感興趣的缺陷。
Mikael Ronstr?m's Blog

MySQL分區(qū)體系結(jié)構(gòu)和領(lǐng)先的開發(fā)者M(jìn)ikael Ronstr?m?經(jīng)常在這里貼關(guān)于他研究MySQL?分區(qū)和MySQL簇的文章。
PlanetMySQL

一個(gè)MySQL?新聞網(wǎng)站，它以匯集MySQL相關(guān)的網(wǎng)誌為特點(diǎn)，那些使用我的MySQL的人應(yīng)該對(duì)此有興趣。我們鼓勵(lì)查看那些研究MySQL分區(qū)的人的網(wǎng)誌鏈接，或者把你自己的網(wǎng)誌加到這些新聞報(bào)道中。

MySQL 5.1的二進(jìn)制版本目前還不可用；但是，可以從BitKeeper知識(shí)庫(kù)中獲得源碼。要激活分區(qū)，需要使用--with-分區(qū)選項(xiàng)編譯服務(wù)器。關(guān)于建立MySQL?的更多信息，請(qǐng)參見2.8節(jié)，“使用源碼分發(fā)版安裝MySQL”。如果在編譯一個(gè)激活分區(qū)的MySQL 5.1創(chuàng)建中碰到問題，可以在MySQL分區(qū)論壇中查找解決辦法，如果在論壇中已經(jīng)貼出的文章中沒有找到問題的解決辦法，可以在上面尋找?guī)椭?/p>

18.1.?MySQL中的分區(qū)概述

本節(jié)提供了關(guān)于MySQL 5.1.分區(qū)在概念上的概述。

SQL標(biāo)準(zhǔn)在數(shù)據(jù)存儲(chǔ)的物理方面沒有提供太多的指南。SQL語言的使用獨(dú)立于它所使用的任何數(shù)據(jù)結(jié)構(gòu)或圖表、表、行或列下的介質(zhì)。但是，大部分高級(jí)數(shù)據(jù)庫(kù)管理系統(tǒng)已經(jīng)開發(fā)了一些根據(jù)文件系統(tǒng)、硬件或者這兩者來確定將要用于存儲(chǔ)特定數(shù)據(jù)塊物理位置的方法。在MySQL中，InnoDB存儲(chǔ)引擎長(zhǎng)期支持表空間的概念，并且MySQL服務(wù)器甚至在分區(qū)引入之前，就能配置為存儲(chǔ)不同的數(shù)據(jù)庫(kù)使用不同的物理路徑(關(guān)于如何配置的解釋，請(qǐng)參見7.6.1節(jié)，“使用符號(hào)鏈接”)。

分區(qū)又把這個(gè)概念推進(jìn)了一步，它允許根據(jù)可以設(shè)置為任意大小的規(guī)則，跨文件系統(tǒng)分配單個(gè)表的多個(gè)部分。實(shí)際上，表的不同部分在不同的位置被存儲(chǔ)為單獨(dú)的表。用戶所選擇的、實(shí)現(xiàn)數(shù)據(jù)分割的規(guī)則被稱為分區(qū)函數(shù)，這在MySQL中它可以是模數(shù)，或者是簡(jiǎn)單的匹配一個(gè)連續(xù)的數(shù)值區(qū)間或數(shù)值列表，或者是一個(gè)內(nèi)部HASH函數(shù)，或一個(gè)線性HASH函數(shù)。函數(shù)根據(jù)用戶指定的分區(qū)類型來選擇，把用戶提供的表達(dá)式的值作為參數(shù)。該表達(dá)式可以是一個(gè)整數(shù)列值，或一個(gè)作用在一個(gè)或多個(gè)列值上并返回一個(gè)整數(shù)的函數(shù)。這個(gè)表達(dá)式的值傳遞給分區(qū)函數(shù)，分區(qū)函數(shù)返回一個(gè)表示那個(gè)特定記錄應(yīng)該保存在哪個(gè)分區(qū)的序號(hào)。這個(gè)函數(shù)不能是常數(shù)，也不能是任意數(shù)。它不能包含任何查詢，但是實(shí)際上可以使用MySQL?中任何可用的SQL表達(dá)式，只要該表達(dá)式返回一個(gè)小于MAXVALUE（最大可能的正整數(shù)）的正數(shù)值。分區(qū)函數(shù)的例子可以在本章后面關(guān)于分區(qū)類型的討論中找到?(請(qǐng)參見18.2節(jié)，“分區(qū)類型”?)，也可在13.1.5節(jié)，“CREATE TABLE語法”的分區(qū)語法描述中找到。

當(dāng)二進(jìn)制碼變成可用時(shí)（也就是說，5.1?-max?二進(jìn)制碼將通過--with-partition?建立），分區(qū)支持就將包含在MySQL 5.1的-max?版本中。如果MySQL二進(jìn)制碼是使用分區(qū)支持建立的，那么激活它不需要任何其他的東西?(例如，在my.cnf?文件中，不需要特殊的條目)。可以通過使用SHOW VARIABLES命令來確定MySQL是否支持分區(qū)，例如：

mysql> SHOW VARIABLES LIKE '%partition%';

+-----------------------+-------+

| Variable_name???????? | Value |

+-----------------------+-------+

| have_partition_engine | YES?? |

+-----------------------+-------+

1 row in set (0.00 sec)

在如上列出的一個(gè)正確的SHOW VARIABLES?命令所產(chǎn)生的輸出中，如果沒有看到變量have_partition_engine的值為YES，那么MySQL的版本就不支持分區(qū)。（注意：在顯示任何有關(guān)分區(qū)支持信息的命令SHOW ENGINES的輸出中，不會(huì)給出任何信息；必須使用SHOW VARIABLES命令來做出這個(gè)判斷）。

對(duì)于創(chuàng)建了分區(qū)的表，可以使用你的MySQL?服務(wù)器所支持的任何存儲(chǔ)引擎；MySQL?分區(qū)引擎在一個(gè)單獨(dú)的層中運(yùn)行，并且可以和任何這樣的層進(jìn)行相互作用。在MySQL 5.1版中，同一個(gè)分區(qū)表的所有分區(qū)必須使用同一個(gè)存儲(chǔ)引擎；例如，不能對(duì)一個(gè)分區(qū)使用MyISAM，而對(duì)另一個(gè)使用InnoDB。但是，這并不妨礙在同一個(gè)?MySQL?服務(wù)器中，甚至在同一個(gè)數(shù)據(jù)庫(kù)中，對(duì)于不同的分區(qū)表使用不同的存儲(chǔ)引擎。

要為某個(gè)分區(qū)表配置一個(gè)專門的存儲(chǔ)引擎，必須且只能使用[STORAGE] ENGINE?選項(xiàng)，這如同為非分區(qū)表配置存儲(chǔ)引擎一樣。但是，必須記住[STORAGE] ENGINE（和其他的表選項(xiàng)）必須列在用在CREATE TABLE語句中的其他任何分區(qū)選項(xiàng)之前。下面的例子給出了怎樣創(chuàng)建一個(gè)通過HASH分成6個(gè)分區(qū)、使用InnoDB存儲(chǔ)引擎的表：

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)

??? ENGINE=INNODB

??? PARTITION BY HASH(MONTH(tr_date))

??? PARTITIONS 6;

（注釋：每個(gè)PARTITION?子句可以包含一個(gè)?[STORAGE] ENGINE?選項(xiàng)，但是在MySQL 5.1版本中，這沒有作用）。

創(chuàng)建分區(qū)的臨時(shí)表也是可能的；但是，這種表的生命周期只有當(dāng)前MySQL?的會(huì)話的時(shí)間那么長(zhǎng)。對(duì)于非分區(qū)的臨時(shí)表，這也是一樣的。

注釋：分區(qū)適用于一個(gè)表的所有數(shù)據(jù)和索引；不能只對(duì)數(shù)據(jù)分區(qū)而不對(duì)索引分區(qū)，反之亦然，同時(shí)也不能只對(duì)表的一部分進(jìn)行分區(qū)。

可以通過使用用來創(chuàng)建分區(qū)表的CREATE TABLE語句的PARTITION子句的DATA DIRECTORY（數(shù)據(jù)路徑）和INDEX DIRECTORY（索引路徑）選項(xiàng)，為每個(gè)分區(qū)的數(shù)據(jù)和索引指定特定的路徑。此外，MAX_ROWS和MIN_ROWS選項(xiàng)可以用來設(shè)定最大和最小的行數(shù)，它們可以各自保存在每個(gè)分區(qū)里。關(guān)于這些選項(xiàng)的更多信息，請(qǐng)參見18.3節(jié)，“分區(qū)管理”。注釋：這個(gè)特殊的功能由于Bug #13250的原因，目前還不能實(shí)用。在第一個(gè)5.1二進(jìn)制版本投入使用時(shí)，我們應(yīng)該已經(jīng)把這個(gè)問題解決了。

分區(qū)的一些優(yōu)點(diǎn)包括：

·?????????與單個(gè)磁盤或文件系統(tǒng)分區(qū)相比，可以存儲(chǔ)更多的數(shù)據(jù)。

·?????????對(duì)于那些已經(jīng)失去保存意義的數(shù)據(jù)，通常可以通過刪除與那些數(shù)據(jù)有關(guān)的分區(qū)，很容易地刪除那些數(shù)據(jù)。相反地，在某些情況下，添加新數(shù)據(jù)的過程又可以通過為那些新數(shù)據(jù)專門增加一個(gè)新的分區(qū)，來很方便地實(shí)現(xiàn)。

通常和分區(qū)有關(guān)的其他優(yōu)點(diǎn)包括下面列出的這些。MySQL?分區(qū)中的這些功能目前還沒有實(shí)現(xiàn)，但是在我們的優(yōu)先級(jí)列表中，具有高的優(yōu)先級(jí)；我們希望在5.1的生產(chǎn)版本中，能包括這些功能。

·?????????一些查詢可以得到極大的優(yōu)化，這主要是借助于滿足一個(gè)給定WHERE?語句的數(shù)據(jù)可以只保存在一個(gè)或多個(gè)分區(qū)內(nèi)，這樣在查找時(shí)就不用查找其他剩余的分區(qū)。因?yàn)榉謪^(qū)可以在創(chuàng)建了分區(qū)表后進(jìn)行修改，所以在第一次配置分區(qū)方案時(shí)還不曾這么做時(shí)，可以重新組織數(shù)據(jù)，來提高那些常用查詢的效率。

·?????????涉及到例如SUM()?和?COUNT()這樣聚合函數(shù)的查詢，可以很容易地進(jìn)行并行處理。這種查詢的一個(gè)簡(jiǎn)單例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id；”。通過“并行”，?這意味著該查詢可以在每個(gè)分區(qū)上同時(shí)進(jìn)行，最終結(jié)果只需通過總計(jì)所有分區(qū)得到的結(jié)果。

·?????????通過跨多個(gè)磁盤來分散數(shù)據(jù)查詢，來獲得更大的查詢吞吐量。

要經(jīng)常檢查本頁(yè)和本章，因?yàn)樗鼘㈦SMySQL 5.1后續(xù)的分區(qū)進(jìn)展而更新。

18.2.?分區(qū)類型

18.2.1. RANGE分區(qū)18.2.2. LIST分區(qū)18.2.3. HASH分區(qū)18.2.4. KEY分區(qū)18.2.5. 子分區(qū)18.2.6. MySQL分區(qū)處理NULL值的方式

本節(jié)討論在MySQL 5.1中可用的分區(qū)類型。這些類型包括：

·?????????RANGE?分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。參見18.2.1節(jié)，“RANGE分區(qū)”。

·?????????LIST?分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來進(jìn)行選擇。參見18.2.2節(jié)，“LIST分區(qū)”。

·?????????HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包含MySQL?中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。參見18.2.3節(jié)，“HASH分區(qū)”。

·?????????KEY?分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL?服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。參見18.2.4節(jié)，“KEY分區(qū)”。

無論使用何種類型的分區(qū)，分區(qū)總是在創(chuàng)建時(shí)就自動(dòng)的順序編號(hào)，且從0開始記錄，記住這一點(diǎn)非常重要。當(dāng)有一新行插入到一個(gè)分區(qū)表中時(shí)，就是使用這些分區(qū)編號(hào)來識(shí)別正確的分區(qū)。例如，如果你的表使用4個(gè)分區(qū)，那么這些分區(qū)就編號(hào)為0,?1,?2,?和3。對(duì)于RANGE和LIST分區(qū)類型，確認(rèn)每個(gè)分區(qū)編號(hào)都定義了一個(gè)分區(qū)，很有必要。對(duì)HASH分區(qū)，使用的用戶函數(shù)必須返回一個(gè)大于0的整數(shù)值。對(duì)于KEY分區(qū)，這個(gè)問題通過MySQL服務(wù)器內(nèi)部使用的哈希函數(shù)自動(dòng)進(jìn)行處理。

分區(qū)的名字基本上遵循其他MySQL?標(biāo)識(shí)符應(yīng)當(dāng)遵循的原則，例如用于表和數(shù)據(jù)庫(kù)名字的標(biāo)識(shí)符。但是應(yīng)當(dāng)注意，分區(qū)的名字是不區(qū)分大小寫的。例如，下面的CREATE TABLE語句將會(huì)產(chǎn)生如下的錯(cuò)誤：

mysql> CREATE TABLE t2 (val INT)

??? -> PARTITION BY LIST(val)(

??? ->???? PARTITION mypart VALUES IN (1,3,5),

??? ->???? PARTITION MyPart VALUES IN (2,4,6)

??? -> );

錯(cuò)誤1488 (HY000): 表的所有分區(qū)必須有唯一的名字。

這是因?yàn)镸ySQL認(rèn)為分區(qū)名字mypart和MyPart沒有區(qū)別。

注釋：在下面的章節(jié)中，我們沒有必要提供可以用來創(chuàng)建每種分區(qū)類型語法的所有可能形式，這些信息可以在13.1.5節(jié)，“CREATE TABLE語法”?中找到。

18.2.1.?RANGE分區(qū)

按照RANGE分區(qū)的表是通過如下一種方式進(jìn)行分區(qū)的，每個(gè)分區(qū)包含那些分區(qū)表達(dá)式的值位于一個(gè)給定的連續(xù)區(qū)間內(nèi)的行。這些區(qū)間要連續(xù)且不能相互重疊，使用VALUES LESS THAN操作符來進(jìn)行定義。在下面的幾個(gè)例子中，假定你創(chuàng)建了一個(gè)如下的一個(gè)表，該表保存有20家音像店的職員記錄，這20家音像店的編號(hào)從1到20。

CREATE TABLE employees (

??? id INT NOT NULL,

??? fname VARCHAR(30),

??? lname VARCHAR(30),

??? hired DATE NOT NULL DEFAULT '1970-01-01',

??? separated DATE NOT NULL DEFAULT '9999-12-31',

??? job_code INT NOT NULL,

??? store_id INT NOT NULL

)；

根據(jù)你的需要，這個(gè)表可以有多種方式來按照區(qū)間進(jìn)行分區(qū)。一種方式是使用store_id?列。例如，你可能決定通過添加一個(gè)PARTITION BY RANGE子句把這個(gè)表分割成4個(gè)區(qū)間，如下所示:

CREATE TABLE employees (

??? id INT NOT NULL,

??? fname VARCHAR(30),

??? lname VARCHAR(30),

??? hired DATE NOT NULL DEFAULT '1970-01-01',

??? separated DATE NOT NULL DEFAULT '9999-12-31',

??? job_code INT NOT NULL,

??? store_id INT NOT NULL

PARTITION BY RANGE (store_id) (

??? PARTITION p0 VALUES LESS THAN (6),

??? PARTITION p1 VALUES LESS THAN (11),

??? PARTITION p2 VALUES LESS THAN (16),

??? PARTITION p3 VALUES LESS THAN (21)

)；

按照這種分區(qū)方案，在商店1到5工作的雇員相對(duì)應(yīng)的所有行被保存在分區(qū)P0中，商店6到10的雇員保存在P1中，依次類推。注意，每個(gè)分區(qū)都是按順序進(jìn)行定義，從最低到最高。這是PARTITION BY RANGE?語法的要求；在這點(diǎn)上，它類似于C或Java中的“switch ... case”語句。

對(duì)于包含數(shù)據(jù)(72, 'Michael', 'Widenius', '1998-06-25', NULL, 13)的一個(gè)新行，可以很容易地確定它將插入到p2分區(qū)中，但是如果增加了一個(gè)編號(hào)為第21的商店，將會(huì)發(fā)生什么呢？在這種方案下，由于沒有規(guī)則把store_id大于20的商店包含在內(nèi)，服務(wù)器將不知道把該行保存在何處，將會(huì)導(dǎo)致錯(cuò)誤。?要避免這種錯(cuò)誤，可以通過在CREATE TABLE語句中使用一個(gè)“catchall”?VALUES LESS THAN子句，該子句提供給所有大于明確指定的最高值的值：

CREATE TABLE employees (

??? id INT NOT NULL,

??? fname VARCHAR(30),

??? lname VARCHAR(30),

??? hired DATE NOT NULL DEFAULT '1970-01-01',

??? separated DATE NOT NULL DEFAULT '9999-12-31',

??? job_code INT NOT NULL,

??? store_id INT NOT NULL

PARTITION BY RANGE (store_id) (

??? PARTITION p0 VALUES LESS THAN (6),

??? PARTITION p1 VALUES LESS THAN (11),

??? PARTITION p2 VALUES LESS THAN (16),

??? PARTITION p3 VALUES LESS THAN MAXVALUE

)；

MAXVALUE?表示最大的可能的整數(shù)值。現(xiàn)在，store_id?列值大于或等于16（定義了的最高值）的所有行都將保存在分區(qū)p3中。在將來的某個(gè)時(shí)候，當(dāng)商店數(shù)已經(jīng)增長(zhǎng)到25, 30,?或更多，可以使用ALTER TABLE語句為商店21-25, 26-30,等等增加新的分區(qū) （關(guān)于如何實(shí)現(xiàn)的詳細(xì)信息參見18.3節(jié)，“分區(qū)管理”?)。

在幾乎一樣的結(jié)構(gòu)中，你還可以基于雇員的工作代碼來分割表，也就是說，基于job_code?列值的連續(xù)區(qū)間。例如——假定2位數(shù)字的工作代碼用來表示普通（店內(nèi)的）工人，三個(gè)數(shù)字代碼表示辦公室和支持人員，四個(gè)數(shù)字代碼表示管理層，你可以使用下面的語句創(chuàng)建該分區(qū)表：

CREATE TABLE employees (

??? id INT NOT NULL,

??? fname VARCHAR(30),

??? lname VARCHAR(30),

??? hired DATE NOT NULL DEFAULT '1970-01-01',

??? separated DATE NOT NULL DEFAULT '9999-12-31',

??? job_code INT NOT NULL,

??? store_id INT NOT NULL

PARTITION BY RANGE (job_code) (

??? PARTITION p0 VALUES LESS THAN (100),

??? PARTITION p1 VALUES LESS THAN (1000),

??? PARTITION p2 VALUES LESS THAN (10000)

)；

在這個(gè)例子中,?店內(nèi)工人相關(guān)的所有行將保存在分區(qū)p0中，辦公室和支持人員相關(guān)的所有行保存在分區(qū)p1中，管理層相關(guān)的所有行保存在分區(qū)p2中。

在VALUES LESS THAN?子句中使用一個(gè)表達(dá)式也是可能的。這里最值得注意的限制是MySQL?必須能夠計(jì)算表達(dá)式的返回值作為L(zhǎng)ESS THAN?(<)比較的一部分；因此，表達(dá)式的值不能為NULL?。由于這個(gè)原因，雇員表的hired,separated,?job_code,和store_id列已經(jīng)被定義為非空（NOT NULL）。

除了可以根據(jù)商店編號(hào)分割表數(shù)據(jù)外，你還可以使用一個(gè)基于兩個(gè)DATE?（日期）中的一個(gè)的表達(dá)式來分割表數(shù)據(jù)。例如，假定你想基于每個(gè)雇員離開公司的年份來分割表，也就是說，YEAR(separated)的值。實(shí)現(xiàn)這種分區(qū)模式的CREATE TABLE?語句的一個(gè)例子如下所示：

CREATE TABLE employees (

??? id INT NOT NULL,

??? fname VARCHAR(30),

??? lname VARCHAR(30),

??? hired DATE NOT NULL DEFAULT '1970-01-01',

??? separated DATE NOT NULL DEFAULT '9999-12-31',

??? job_code INT,

??? store_id INT

PARTITION BY RANGE (YEAR(separated)) (

??? PARTITION p0 VALUES LESS THAN (1991),

??? PARTITION p1 VALUES LESS THAN (1996),

??? PARTITION p2 VALUES LESS THAN (2001),

??? PARTITION p3 VALUES LESS THAN MAXVALUE

)；

在這個(gè)方案中，在1991年前雇傭的所有雇員的記錄保存在分區(qū)p0中，1991年到1995年期間雇傭的所有雇員的記錄保存在分區(qū)p1中，?1996年到2000年期間雇傭的所有雇員的記錄保存在分區(qū)p2中，2000年后雇傭的所有工人的信息保存在p3中。

RANGE分區(qū)在如下場(chǎng)合特別有用：

·?????????當(dāng)需要?jiǎng)h除“舊的”數(shù)據(jù)時(shí)。如果你使用上面最近的那個(gè)例子給出的分區(qū)方案，你只需簡(jiǎn)單地使用 “ALTER TABLE employees DROP PARTITION p0；”來刪除所有在1991年前就已經(jīng)停止工作的雇員相對(duì)應(yīng)的所有行。(更多信息請(qǐng)參見13.1.2節(jié)，“ALTER TABLE語法”?和?18.3節(jié)，“分區(qū)管理”）。對(duì)于有大量行的表，這比運(yùn)行一個(gè)如“DELETE FROM employees WHERE YEAR(separated) <= 1990；”這樣的一個(gè)DELETE查詢要有效得多。

·?????????想要使用一個(gè)包含有日期或時(shí)間值，或包含有從一些其他級(jí)數(shù)開始增長(zhǎng)的值的列。

·?????????經(jīng)常運(yùn)行直接依賴于用于分割表的列的查詢。例如，當(dāng)執(zhí)行一個(gè)如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id；”這樣的查詢時(shí)，MySQL可以很迅速地確定只有分區(qū)p2需要掃描，這是因?yàn)橛嘞碌姆謪^(qū)不可能包含有符合該WHERE子句的任何記錄。注釋：這種優(yōu)化還沒有在MySQL 5.1源程序中啟用，但是，有關(guān)工作正在進(jìn)行中。

18.2.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ù)列表。

注釋：在MySQL 5.1中，當(dāng)使用LIST分區(qū)時(shí)，有可能只能匹配整數(shù)列表。

不像按照RANGE定義分區(qū)的情形，LIST分區(qū)不必聲明任何特定的順序。關(guān)于LIST分區(qū)更詳細(xì)的語法信息，請(qǐng)參考13.1.5節(jié)，“CREATE TABLE語法”?。

對(duì)于下面給出的例子，我們假定將要被分區(qū)的表的基本定義是通過下面的“CREATE TABLE”語句提供的：

CREATE TABLE employees (

??? id INT NOT NULL,

?? ?fname VARCHAR(30),

??? lname VARCHAR(30),

??? hired DATE NOT NULL DEFAULT '1970-01-01',

??? separated DATE NOT NULL DEFAULT '9999-12-31',

??? job_code INT,

??? store_id INT

)；

（這和18.2.1節(jié)，“RANGE分區(qū)”?中的例子中使用的是同一個(gè)表）。?

假定有20個(gè)音像店，分布在4個(gè)有經(jīng)銷權(quán)的地區(qū)，如下表所示：

地區(qū)	商店ID?號(hào)
北區(qū)	3, 5, 6, 9, 17
東區(qū)	1, 2, 10, 11, 19, 20
西區(qū)	4, 12, 13, 14, 18
中心區(qū)	7, 8, 15, 16

要按照屬于同一個(gè)地區(qū)商店的行保存在同一個(gè)分區(qū)中的方式來分割表，可以使用下面的“CREATE TABLE”語句：

CREATE TABLE employees (

??? id INT NOT NULL,

??? fname VARCHAR(30),

??? lname VARCHAR(30),

??? hired DATE NOT NULL DEFAULT '1970-01-01',

??? separated DATE NOT NULL DEFAULT '9999-12-31',

??? job_code INT,

??? store_id INT

PARTITION BY LIST(store_id)

??? PARTITION pNorth VALUES IN (3,5,6,9,17),

??? PARTITION pEast VALUES IN (1,2,10,11,19,20),

??? PARTITION pWest VALUES IN (4,12,13,14,18),

??? PARTITION pCentral VALUES IN (7,8,15,16)

)；

這使得在表中增加或刪除指定地區(qū)的雇員記錄變得容易起來。例如，假定西區(qū)的所有音像店都賣給了其他公司。那么與在西區(qū)音像店工作雇員相關(guān)的所有記錄（行）可以使用查詢“ALTER TABLE employees DROP PARTITION pWest；”來進(jìn)行刪除，它與具有同樣作用的DELETE?（刪除）查詢“DELETE?query?DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；”比起來，要有效得多。

要點(diǎn)：如果試圖插入列值（或分區(qū)表達(dá)式的返回值）不在分區(qū)值列表中的一行時(shí)，那么“INSERT”查詢將失敗并報(bào)錯(cuò)。例如，假定LIST分區(qū)的采用上面的方案，下面的查詢將失敗：

INSERT INTO employees VALUES

????(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);

這是因?yàn)椤皊tore_id”列值21不能在用于定義分區(qū)pNorth,?pEast,?pWest,或pCentral的值列表中找到。要重點(diǎn)注意的是，LIST分區(qū)沒有類似如“VALUES LESS THAN MAXVALUE”這樣的包含其他值在內(nèi)的定義。將要匹配的任何值都必須在值列表中找到。

LIST分區(qū)除了能和RANGE分區(qū)結(jié)合起來生成一個(gè)復(fù)合的子分區(qū)，與HASH和KEY分區(qū)結(jié)合起來生成復(fù)合的子分區(qū)也是可能的。?關(guān)于這方面的討論，請(qǐng)參考18.2.5節(jié)，“子分區(qū)”。

18.2.3.?HASH分區(qū)

18.2.3.1. LINEAR 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?語句上添加一個(gè)“PARTITION BY HASH (expr)”子句，其中“expr”是一個(gè)返回一個(gè)整數(shù)的表達(dá)式。它可以僅僅是字段類型為MySQL?整型的一列的名字。此外，你很可能需要在后面再添加一個(gè)“PARTITIONS?num”子句，其中num?是一個(gè)非負(fù)的整數(shù)，它表示表將要被分割成分區(qū)的數(shù)量。

例如，下面的語句創(chuàng)建了一個(gè)使用基于“store_id”列進(jìn)行哈希處理的表，該表被分成了4個(gè)分區(qū)：

CREATE TABLE employees (

??? id INT NOT NULL,

??? fname VARCHAR(30),

??? lname VARCHAR(30),

??? hired DATE NOT NULL DEFAULT '1970-01-01',

??? separated DATE NOT NULL DEFAULT '9999-12-31',

??? job_code INT,

?? ?store_id INT

PARTITION BY HASH(store_id)

PARTITIONS 4；

如果沒有包括一個(gè)PARTITIONS子句，那么分區(qū)的數(shù)量將默認(rèn)為1。?例外：?對(duì)于NDB Cluster（簇）表，默認(rèn)的分區(qū)數(shù)量將與簇?cái)?shù)據(jù)節(jié)點(diǎn)的數(shù)量相同，這種修正可能是考慮任何MAX_ROWS?設(shè)置，以便確保所有的行都能合適地插入到分區(qū)中。（參見第17章：MySQL簇）。

如果在關(guān)鍵字“PARTITIONS”后面沒有加上分區(qū)的數(shù)量，將會(huì)出現(xiàn)語法錯(cuò)誤。

“expr”還可以是一個(gè)返回一個(gè)整數(shù)的SQL表達(dá)式。例如，也許你想基于雇用雇員的年份來進(jìn)行分區(qū)。這可以通過下面的語句來實(shí)現(xiàn)：

CREATE TABLE employees (

??? id INT NOT NULL,

??? fname VARCHAR(30),

??? lname VARCHAR(30),

??? hired DATE NOT NULL DEFAULT '1970-01-01',

??? separated DATE NOT NULL DEFAULT '9999-12-31',

??? job_code INT,

??? store_id INT

PARTITION BY HASH(YEAR(hired))

PARTITIONS 4；

“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ū)。

例如，“date_col” 是一個(gè)DATE（日期）類型的列，那么表達(dá)式TO_DAYS(date_col)就可以說是隨列“date_col”值的變化而發(fā)生直接的變化，因?yàn)榱小癲ate_col”值的每個(gè)變化，表達(dá)式的值也將發(fā)生與之一致的變化。而表達(dá)式Y(jié)EAR(date_col)的變化就沒有表達(dá)式TO_DAYS(date_col)那么直接，因?yàn)椴皇橇小癲ate_col”每次可能的改變都能使表達(dá)式Y(jié)EAR(date_col)發(fā)生同等的改變。即便如此，表達(dá)式Y(jié)EAR(date_col)也還是一個(gè)用于哈希函數(shù)的、好的候選表達(dá)式，因?yàn)樗S列date_col的一部分發(fā)生直接變化，并且列date_col的變化不可能引起表達(dá)式Y(jié)EAR(date_col)不成比例的變化。

作為對(duì)照，假定有一個(gè)類型為整型（INT）的、列名為“int_col”的列。現(xiàn)在考慮表達(dá)式“POW(5-int_col,3) + 6”。這對(duì)于哈希函數(shù)就是一個(gè)不好的選擇，因?yàn)椤癷nt_col”值的變化并不能保證表達(dá)式產(chǎn)生成比例的變化。列 “int_col”的值發(fā)生一個(gè)給定數(shù)目的變化，可能會(huì)引起表達(dá)式的值產(chǎn)生一個(gè)很大不同的變化。例如，把列“int_col”的值從5變?yōu)?，表達(dá)式的值將產(chǎn)生“－1”的改變，但是把列“int_col”的值從6變?yōu)?時(shí)，表達(dá)式的值將產(chǎn)生“－7”的變化。

換句話說，如果列值與表達(dá)式值之比的曲線圖越接近由等式“y=nx（其中n為非零的常數(shù)）描繪出的直線，則該表達(dá)式越適合于哈希。這是因?yàn)?#xff0c;表達(dá)式的非線性越嚴(yán)重，分區(qū)中數(shù)據(jù)產(chǎn)生非均衡分布的趨勢(shì)也將越嚴(yán)重。

理論上講，對(duì)于涉及到多列的表達(dá)式，“修剪（pruning）”也是可能的，但是要確定哪些適于哈希是非常困難和耗時(shí)的。基于這個(gè)原因，實(shí)際上不推薦使用涉及到多列的哈希表達(dá)式。

當(dāng)使用了“PARTITION BY HASH”時(shí)，MySQL將基于用戶函數(shù)結(jié)果的模數(shù)來確定使用哪個(gè)編號(hào)的分區(qū)。換句話，對(duì)于一個(gè)表達(dá)式“expr”，將要保存記錄的分區(qū)編號(hào)為N?，其中“N?= MOD(expr,?num)”。例如，假定表t1?定義如下，它有4個(gè)分區(qū)：

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)

??? PARTITION BY HASH( YEAR(col3) )

??? PARTITIONS 4；

如果插入一個(gè)col3列值為'2005-09-15'的記錄到表t1中，那么保存該條記錄的分區(qū)確定如下：

MOD(YEAR('2005-09-01'),4)

=? MOD(2005,4)

=? 1

MySQL 5.1?還支持一個(gè)被稱為“linear hashing（線性哈希功能）”的變量，它使用一個(gè)更加復(fù)雜的算法來確定新行插入到已經(jīng)分區(qū)了的表中的位置。關(guān)于這種算法的描述，請(qǐng)參見18.2.3.1節(jié)，“LINEAR HASH分區(qū)”?。

每當(dāng)插入或更新一條記錄，用戶函數(shù)都要計(jì)算一次。當(dāng)刪除記錄時(shí)，用戶函數(shù)也可能要進(jìn)行計(jì)算，這取決于所處的環(huán)境。

注釋：如果將要分區(qū)的表有一個(gè)唯一的鍵，那么用來作為HASH用戶函數(shù)的自變數(shù)或者主鍵的column_list的自變數(shù)的任意列都必須是那個(gè)鍵的一部分。

18.2.3.1.?LINEAR HASH分區(qū)

MySQL還支持線性哈希功能，它與常規(guī)哈希的區(qū)別在于，線性哈希功能使用的一個(gè)線性的2的冪（powers-of-two）運(yùn)算法則，而常規(guī) 哈希使用的是求哈希函數(shù)值的模數(shù)。

線性哈希分區(qū)和常規(guī)哈希分區(qū)在語法上的唯一區(qū)別在于，在“PARTITION BY”?子句中添加“LINEAR”關(guān)鍵字，如下面所示：

CREATE TABLE employees (

??? id INT NOT NULL,

??? fname VARCHAR(30),

??? lname VARCHAR(30),

??? hired DATE NOT NULL DEFAULT '1970-01-01',

??? separated DATE NOT NULL DEFAULT '9999-12-31',

??? job_code INT,

??? store_id INT

PARTITION BY LINEAR HASH(YEAR(hired))

PARTITIONS 4；

假設(shè)一個(gè)表達(dá)式expr,?當(dāng)使用線性哈希功能時(shí)，記錄將要保存到的分區(qū)是num?個(gè)分區(qū)中的分區(qū)N，其中N是根據(jù)下面的算法得到：

1.????找到下一個(gè)大于num.的、2的冪，我們把這個(gè)值稱為V?，它可以通過下面的公式得到：

2.?????????? V = POWER(2, CEILING(LOG(2, num)))

（例如，假定num是13。那么LOG(2,13)就是3.7004397181411。?CEILING(3.7004397181411)就是4，則V?=POWER(2,4),?即等于16）。

3.????設(shè)置?N?=?F(column_list) & (V?- 1).

4.????當(dāng)?N?>=?num:

·?????????設(shè)置?V?= CEIL(V?/ 2)

·?????????設(shè)置?N?=?N?& (V?- 1)

例如，假設(shè)表t1，使用線性哈希分區(qū)且有4個(gè)分區(qū)，是通過下面的語句創(chuàng)建的：

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)

??? PARTITION BY LINEAR HASH( YEAR(col3) )

??? PARTITIONS 6;

現(xiàn)在假設(shè)要插入兩行記錄到表t1中，其中一條記錄col3列值為'2003-04-14'，另一條記錄col3列值為'1998-10-19'。第一條記錄將要保存到的分區(qū)確定如下：

V = POWER(2, CEILING(LOG(2,7))) = 8

N = YEAR('2003-04-14') & (8 - 1)

?? = 2003 & 7

?? = 3

(3 >= 6 為假（FALSE）: 記錄將被保存到#3號(hào)分區(qū)中)

第二條記錄將要保存到的分區(qū)序號(hào)計(jì)算如下：

V = 8

N = YEAR('1998-10-19') & (8-1)

? = 1998 & 7

? = 6

(6 >= 4 為真（TRUE）: 還需要附加的步驟)

N = 6 & CEILING(5 / 2)

? = 6 & 3

? = 2

(2 >= 4 為假（FALSE）: 記錄將被保存到#2分區(qū)中)

按照線性哈希分區(qū)的優(yōu)點(diǎn)在于增加、刪除、合并和拆分分區(qū)將變得更加快捷，有利于處理含有極其大量（1000吉）數(shù)據(jù)的表。它的缺點(diǎn)在于，與使用常規(guī)HASH分區(qū)得到的數(shù)據(jù)分布相比，各個(gè)分區(qū)間數(shù)據(jù)的分布不大可能均衡。

18.2.4.?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)算法則。

“CREATE TABLE ... PARTITION BY KEY”的語法規(guī)則類似于創(chuàng)建一個(gè)通過HASH分區(qū)的表的規(guī)則。它們唯一的區(qū)別在于使用的關(guān)鍵字是KEY而不是HASH，并且KEY分區(qū)只采用一個(gè)或多個(gè)列名的一個(gè)列表。

通過線性KEY分割一個(gè)表也是可能的。下面是一個(gè)簡(jiǎn)單的例子：

CREATE TABLE tk (

??? col1 INT NOT NULL,

??? col2 CHAR(5),

??? col3 DATE

PARTITION BY LINEAR KEY (col1)

PARTITIONS 3;

在KEY分區(qū)中使用關(guān)鍵字LINEAR和在HASH分區(qū)中使用具有同樣的作用，分區(qū)的編號(hào)是通過2的冪（powers-of-two）算法得到，而不是通過模數(shù)算法。關(guān)于該算法及其蘊(yùn)涵式的描述請(qǐng)參考?18.2.3.1節(jié)，“LINEAR HASH分區(qū)”?。

18.2.5.?子分區(qū)

子分區(qū)是分區(qū)表中每個(gè)分區(qū)的再次分割。例如，考慮下面的CREATE TABLE?語句：

CREATE TABLE ts (id INT, purchased DATE)

??? PARTITION BY RANGE(YEAR(purchased))

??? SUBPARTITION BY HASH(TO_DAYS(purchased))

??? SUBPARTITIONS 2

??? (

??????? PARTITION p0 VALUES LESS THAN (1990),

??????? PARTITION p1 VALUES LESS THAN (2000),

??????? PARTITION p2 VALUES LESS THAN MAXVALUE

??? )；

表ts?有3個(gè)RANGE分區(qū)。這3個(gè)分區(qū)中的每一個(gè)分區(qū)——p0,?p1,?和?p2?——又被進(jìn)一步分成了2個(gè)子分區(qū)。實(shí)際上，整個(gè)表被分成了3 * 2 = 6個(gè)分區(qū)。但是，由于PARTITION BY RANGE子句的作用，這些分區(qū)的頭2個(gè)只保存“purchased”列中值小于1990的那些記錄。

在MySQL 5.1中，對(duì)于已經(jīng)通過RANGE或LIST分區(qū)了的表再進(jìn)行子分區(qū)是可能的。子分區(qū)既可以使用HASH希分區(qū)，也可以使用KEY分區(qū)。這也被稱為復(fù)合分區(qū)（composite partitioning）。

為了對(duì)個(gè)別的子分區(qū)指定選項(xiàng)，使用SUBPARTITION?子句來明確定義子分區(qū)也是可能的。例如，創(chuàng)建在前面例子中給出的同一個(gè)表的、一個(gè)更加詳細(xì)的方式如下：

CREATE TABLE ts (id INT, purchased DATE)

??? PARTITION BY RANGE(YEAR(purchased))

??? SUBPARTITION BY HASH(TO_DAYS(purchased))

??? (

??????? PARTITION p0 VALUES LESS THAN (1990)

??????? (

??????????? SUBPARTITION s0,

??????????? SUBPARTITION s1

??????? ),

??????? PARTITION p1 VALUES LESS THAN (2000)

??????? (

??????????? SUBPARTITION s2,

? ??????????SUBPARTITION s3

??????? ),

??????? PARTITION p2 VALUES LESS THAN MAXVALUE

??????? (

??????????? SUBPARTITION s4,

??????????? SUBPARTITION s5

??????? )

??? );

幾點(diǎn)要注意的語法項(xiàng)：

·?????????每個(gè)分區(qū)必須有相同數(shù)量的子分區(qū)。

·?????????如果在一個(gè)分區(qū)表上的任何分區(qū)上使用SUBPARTITION?來明確定義任何子分區(qū)，那么就必須定義所有的子分區(qū)。換句話說，下面的語句將執(zhí)行失敗：

·??????????????? CREATE TABLE ts (id INT, purchased DATE)

·??????????????? ????PARTITION BY RANGE(YEAR(purchased))

·??????????????? ????SUBPARTITION BY HASH(TO_DAYS(purchased))

·??????????????? ????(

·??????????????? ????????PARTITION p0 VALUES LESS THAN (1990)

·??????????????? ????????(

·??????????????? ????????????SUBPARTITION s0,

·??????????????? ????????????SUBPARTITION s1

·??????????????? ????????),

·??????????????? ????????PARTITION p1 VALUES LESS THAN (2000),

·??????????????? ????????PARTITION p2 VALUES LESS THAN MAXVALUE

·??????????????? ????????(

·??????????????? ????????????SUBPARTITION s2,

·??????????????? ????????????SUBPARTITION s3

·??????????????? ????????)

·??????????????? ????)；

即便這個(gè)語句包含了一個(gè)SUBPARTITIONS 2子句，但是它仍然會(huì)執(zhí)行失敗。

·?????????每個(gè)SUBPARTITION?子句必須包括?(至少)子分區(qū)的一個(gè)名字。否則，你可能要對(duì)該子分區(qū)設(shè)置任何你所需要的選項(xiàng)，或者允許該子分區(qū)對(duì)那些選項(xiàng)采用其默認(rèn)的設(shè)置。

·?????????在每個(gè)分區(qū)內(nèi)，子分區(qū)的名字必須是唯一的，但是在整個(gè)表中，沒有必要保持唯一。例如，下面的CREATE TABLE?語句是有效的：

·??????????????? CREATE TABLE ts (id INT, purchased DATE)

·??????????????? ????PARTITION BY RANGE(YEAR(purchased))

·??????????????? ????SUBPARTITION BY HASH(TO_DAYS(purchased))

·??????????????? ????(

·??????????????? ????????PARTITION p0 VALUES LESS THAN (1990)

·??????????????? ????????(

·??????????????? ????????????SUBPARTITION s0,

·??????????????? ????????????SUBPARTITION s1

·??????????????? ????????),

·??????????????? ????????PARTITION p1 VALUES LESS THAN (2000)

·??????????????? ????????(

·??????????????? ????????????SUBPARTITION s0,

·??????????????? ????????????SUBPARTITION s1

·??????????????? ????????),

·??????????????? ????????PARTITION p2 VALUES LESS THAN MAXVALUE

·??????????????? ????????(

·??????????????? ????????????SUBPARTITION s0,

·??????????????? ????????????SUBPARTITION s1

·??????????????? ????????)

·??????????????? ????)；

子分區(qū)可以用于特別大的表，在多個(gè)磁盤間分配數(shù)據(jù)和索引。假設(shè)有6個(gè)磁盤，分別為/disk0，?/disk1，?/disk2等。現(xiàn)在考慮下面的例子：

CREATE TABLE ts (id INT, purchased DATE)

??? PARTITION BY RANGE(YEAR(purchased))

??? SUBPARTITION BY HASH(TO_DAYS(purchased))

??? (

??????? PARTITION p0 VALUES LESS THAN (1990)

??????? (

??????????? 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 (2000)

??????? (

??????????? SUBPARTITION s0

????????????????DATA DIRECTORY = '/disk2/data'

????????????????INDEX DIRECTORY = '/disk2/idx',

??????????? SUBPARTITION s1

????????????????DATA DIRECTORY = '/disk3/data'

????????????????INDEX DIRECTORY = '/disk3/idx'

??????? ),

??????? PARTITION p2 VALUES LESS THAN MAXVALUE

??????? (

??????????? SUBPARTITION s0

????????????????DATA DIRECTORY = '/disk4/data'

????????????????INDEX DIRECTORY = '/disk4/idx',

??????????? SUBPARTITION s1

????????????????DATA DIRECTORY = '/disk5/data'

????????????????INDEX DIRECTORY = '/disk5/idx'

??????? )

??? )；

在這個(gè)例子中，每個(gè)RANGE分區(qū)的數(shù)據(jù)和索引都使用一個(gè)單獨(dú)的磁盤。還可能有許多其他的變化；下面是另外一個(gè)可能的例子：

CREATE TABLE ts (id INT, purchased DATE)

??? PARTITION BY RANGE(YEAR(purchased))

??? SUBPARTITION BY HASH(TO_DAYS(purchased))

??? (

??????? PARTITION p0 VALUES LESS THAN (1990)

??????? (

??????????? SUBPARTITION s0a

????????????????DATA DIRECTORY = '/disk0'

????????????????INDEX DIRECTORY = '/disk1',

??????????? SUBPARTITION s0b

????????????????DATA DIRECTORY = '/disk2'

????????????????INDEX DIRECTORY = '/disk3'

??????? ),

??????? PARTITION p1 VALUES LESS THAN (2000)

??????? (

??????????? SUBPARTITION s1a

????????????????DATA DIRECTORY = '/disk4/data'

????????????????INDEX DIRECTORY = '/disk4/idx',

??????????? SUBPARTITION s1b

????????????????DATA DIRECTORY = '/disk5/data'

????????????????INDEX DIRECTORY = '/disk5/idx'

??????? ),

??????? PARTITION p2 VALUES LESS THAN MAXVALUE

??????? (

??????????? SUBPARTITION s2a,

??????????? SUBPARTITION s2b

??????? )

??? )；

在這個(gè)例子中，存儲(chǔ)的分配如下：

·?????????購(gòu)買日期在1990年前的記錄占了大量的存儲(chǔ)空間，所以把它分為了四個(gè)部分進(jìn)行存儲(chǔ)，組成p0分區(qū)的兩個(gè)子分區(qū)（s0a?和s0b）的數(shù)據(jù)和索引都分別用一個(gè)單獨(dú)的磁盤進(jìn)行存儲(chǔ)。換句話說：

o????????子分區(qū)s0a?的數(shù)據(jù)保存在磁盤/disk0中。

o????????子分區(qū)s0a?的索引保存在磁盤/disk1中。

o????????子分區(qū)s0b?的數(shù)據(jù)保存在磁盤/disk2中。

o????????子分區(qū)s0b?的索引保存在磁盤/disk3中。

·?????????保存購(gòu)買日期從1990年到1999年間的記錄（分區(qū)p1）不需要保存購(gòu)買日期在1990年之前的記錄那么大的存儲(chǔ)空間。這些記錄分在2個(gè)磁盤（/disk4和/disk5）上保存，而不是4個(gè)磁盤：

o????????屬于分區(qū)p1的第一個(gè)子分區(qū)（s1a）的數(shù)據(jù)和索引保存在磁盤/disk4上?— 其中數(shù)據(jù)保存在路徑/disk4/data下，索引保存在/disk4/idx下。

o????????屬于分區(qū)p1的第二個(gè)子分區(qū)（s1b）的數(shù)據(jù)和索引保存在磁盤/disk5上?— 其中數(shù)據(jù)保存在路徑/disk5/data下，索引保存在/disk5/idx下。

·?????????保存購(gòu)買日期從2000年到現(xiàn)在的記錄（分區(qū)p2）不需要前面兩個(gè)RANGE分區(qū)那么大的空間。當(dāng)前，在默認(rèn)的位置能夠足夠保存所有這些記錄。

將來，如果從2000年開始后十年購(gòu)買的數(shù)量已經(jīng)達(dá)到了默認(rèn)的位置不能夠提供足夠的保存空間時(shí)，相應(yīng)的記錄（行）可以通過使用“ALTER TABLE ... REORGANIZE PARTITION”語句移動(dòng)到其他的位置。關(guān)于如何實(shí)現(xiàn)的說明，請(qǐng)參見18.3節(jié)，“分區(qū)管理”?。

18.2.6.?MySQL分區(qū)處理NULL值的方式

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

在本節(jié)中，我們提供了一些例子，來說明當(dāng)決定一個(gè)行應(yīng)該保存到哪個(gè)分區(qū)時(shí)，MySQL?是如何處理NULL值的。

如果插入一行到按照RANGE或LIST分區(qū)的表，該行用來確定分區(qū)的列值為NULL，分區(qū)將把該NULL值視為0。例如，考慮下面的兩個(gè)表，表的創(chuàng)建和插入記錄如下：

mysql> CREATE TABLE tnlist (

??? ->???? id INT,

??? ->???? name VARCHAR(5)

??? -> )

??? -> PARTITION BY LIST(id) (

??? ->???? PARTITION p1 VALUES IN (0),

??? ->???? PARTITION p2 VALUES IN (1)

??? -> );

Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE tnrange (

??? ->???? id INT,

??? ->???? name VARCHAR(5)

??? -> )

??? -> PARTITION BY RANGE(id) (

??? ->???? PARTITION p1 VALUES LESS THAN (1),

??? ->???? PARTITION p2 VALUES LESS THAN MAXVALUE

??? -> );

Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO tnlist VALUES (NULL, 'bob');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tnrange VALUES (NULL, 'jim');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tnlist;

+------+------+

| id?? | name |

+------+------+

| NULL | bob? |

+------+------+

1 row in set (0.00 sec)

mysql> SELECT * FROM tnrange;

+------+------+

| id?? | name |

+------+------+

| NULL | jim? |

+------+------+

1 row in set (0.00 sec)

在兩個(gè)表中，id列沒有聲明為“NOT NULL”，這意味著它們?cè)试SNull值。可以通過刪除這些分區(qū)，然后重新運(yùn)行SELECT?語句，來驗(yàn)證這些行被保存在每個(gè)表的p1分區(qū)中：

mysql> ALTER TABLE tnlist DROP PARTITION p1;

Query OK, 0 rows affected (0.16 sec)

mysql> ALTER TABLE tnrange DROP PARTITION p1;

Query OK, 0 rows affected (0.16 sec)

mysql> SELECT * FROM tnlist;

Empty set (0.00 sec)

mysql> SELECT * FROM tnrange;

Empty set (0.00 sec)

在按HASH和KEY分區(qū)的情況下，任何產(chǎn)生NULL值的表達(dá)式都視同好像它的返回值為0。我們可以通過先創(chuàng)建一個(gè)按HASH分區(qū)的表，然后插入一個(gè)包含有適當(dāng)值的記錄，再檢查對(duì)文件系統(tǒng)的作用，來驗(yàn)證這一點(diǎn)。假定有使用下面的語句在測(cè)試數(shù)據(jù)庫(kù)中創(chuàng)建了一個(gè)表tnhash：

CREATE TABLE tnhash (

??? id INT,

??? name VARCHAR(5)

PARTITION BY HASH(id)

PARTITIONS 2；

假如Linux?上的MySQL?的一個(gè)RPM安裝，這個(gè)語句在目錄/var/lib/mysql/test下創(chuàng)建了兩個(gè).MYD文件，這兩個(gè)文件可以在bash?shell中查看，結(jié)果如下：

/var/lib/mysql/test> ls *.MYD -l

-rw-rw----? 1 mysql mysql 0 2005-11-04 18:41 tnhash_p0.MYD

-rw-rw----? 1 mysql mysql 0 2005-11-04 18:41 tnhash_p1.MYD

注意：每個(gè)文件的大小為0字節(jié)。現(xiàn)在在表tnhash?中插入一行id列值為NULL的行，然后驗(yàn)證該行已經(jīng)被插入：

mysql> INSERT INTO tnhash VALUES (NULL, 'sam');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tnhash;

+------+------+

| id?? | name |

+------+------+

| NULL | sam? |

+------+------+

1 row in set (0.01 sec)

回想一下，對(duì)于任意的整數(shù)N,NULL MOD?N?的值總是等于NULL。這個(gè)結(jié)果在確定正確的分區(qū)方面被認(rèn)為是0。回到系統(tǒng)shell(仍然假定bash用于這個(gè)目的)?，通過再次列出數(shù)據(jù)文件，可以看出值被成功地插入到第一個(gè)分區(qū)（默認(rèn)名稱為p0）中：

var/lib/mysql/test> ls *.MYD -l

-rw-rw----? 1 mysql mysql 20 2005-11-04 18:44 tnhash_p0.MYD

-rw-rw----? 1 mysql mysql? 0 2005-11-04 18:41 tnhash_p1.MYD

可以看出INSERT語句只修改了文件tnhash_p0.MYD，它在磁盤上的尺寸增加了，而沒有影響其他的文件。

假定有下面的一個(gè)表：

CREATE TABLE tndate (

??? id INT,

??? dt DATE

PARTITION BY RANGE( YEAR(dt) ) (

??? PARTITION p0 VALUES LESS THAN (1990),

??? PARTITION p1 VALUES LESS THAN (2000),

??? PARTITION p2 VALUES LESS THAN MAXVALUE

)；

像其他的MySQL函數(shù)一樣，YEAR(NULL)返回NULL值。一個(gè)dt列值為NULL的行，其分區(qū)表達(dá)式的計(jì)算結(jié)果被視為0，該行被插入到分區(qū)p0中。

18.3.?分區(qū)管理

18.3.1. RANGE和LIST分區(qū)的管理18.3.2. HASH和KEY分區(qū)的管理18.3.3. 分區(qū)維護(hù)18.3.4. 獲取關(guān)于分區(qū)的信息

MySQL 5.1?提供了許多修改分區(qū)表的方式。添加、刪除、重新定義、合并或拆分已經(jīng)存在的分區(qū)是可能的。所有這些操作都可以通過使用ALTER TABLE?命令的分區(qū)擴(kuò)展來實(shí)現(xiàn)(關(guān)于語法的定義，請(qǐng)參見13.1.2節(jié)，“ALTER TABLE語法”?)。也有獲得分區(qū)表和分區(qū)信息的方式。在本節(jié)，我們討論下面這些主題：

·?????????按RANGE或LIST分區(qū)的表的分區(qū)管理的有關(guān)信息，請(qǐng)參見18.3.1節(jié)，“RANGE和LIST分區(qū)的管理”。

·?????????關(guān)于HASH和KEY分區(qū)管理的討論，請(qǐng)參見18.3.2節(jié)，“HASH和KEY分區(qū)的管理”。

·?????????MySQL 5.1中提供的、獲得關(guān)于分區(qū)表和分區(qū)信息的機(jī)制的討論，請(qǐng)參見18.3.4節(jié)，“獲取關(guān)于分區(qū)的信息”?。

·?????????關(guān)于執(zhí)行分區(qū)維護(hù)操作的討論，請(qǐng)參見18.3.3節(jié)，“分區(qū)維護(hù)”。

注釋：在MySQL 5.1中，一個(gè)分區(qū)表的所有分區(qū)都必須有子分區(qū)同樣的名字，并且一旦表已經(jīng)創(chuàng)建，再改變子分區(qū)是不可能的。

要點(diǎn)：當(dāng)前，從5.1系列起建立的MySQL?服務(wù)器就把“ALTER TABLE ... PARTITION BY ...”作為有效的語法，但是這個(gè)語句目前還不起作用。我們期望MySQL 5.1達(dá)到生產(chǎn)狀態(tài)時(shí)，能夠按照下面的描述實(shí)現(xiàn)該語句的功能。

要改變一個(gè)表的分區(qū)模式，只需要使用帶有一個(gè)“partition_options”子句的ALTER TABLE?的命令。這個(gè)子句和與創(chuàng)建一個(gè)分區(qū)表的CREATE TABLE命令一同使用的子句有相同的語法，并且總是以關(guān)鍵字PARTITION BY?開頭。例如，假設(shè)有一個(gè)使用下面CREATE TABLE語句建立的按照RANGE分區(qū)的表：

CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)

??? PARTITION BY RANGE(YEAR(purchased))

??? (

??????? PARTITION p0 VALUES LESS THAN (1990),

??????? PARTITION p1 VALUES LESS THAN (1995),

????? ??PARTITION p2 VALUES LESS THAN (2000),

??????? PARTITION p3 VALUES LESS THAN (2005)

??? )；

現(xiàn)在，要把這個(gè)表按照使用id列值作為鍵的基礎(chǔ)，通過KEY分區(qū)把它重新分成兩個(gè)分區(qū)，可以使用下面的語句：

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2；

這和先刪除這個(gè)表、然后使用“CREATE TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2；”重新創(chuàng)建這個(gè)表具有同樣的效果。

18.3.1.?RANGE和LIST分區(qū)的管理

關(guān)于如何添加和刪除分區(qū)的處理，RANGE和LIST分區(qū)非常相似。基于這個(gè)原因，我們?cè)诒竟?jié)討論這兩種分區(qū)的管理。關(guān)于HASH和KEY分區(qū)管理的信息，請(qǐng)參見18.3.2節(jié)，“HASH和KEY分區(qū)的管理”。刪除一個(gè)RANGE或LIST分區(qū)比增加一個(gè)分區(qū)要更加簡(jiǎn)單易懂，所以我們先討論前者。

從一個(gè)按照RANGE或LIST分區(qū)的表中刪除一個(gè)分區(qū)，可以使用帶一個(gè)DROP PARTITION子句的ALTER TABLE命令來實(shí)現(xiàn)。這里有一個(gè)非常基本的例子，假設(shè)已經(jīng)使用下面的CREATE TABLE和INSERT語句創(chuàng)建了一個(gè)按照RANGE分區(qū)的表，并且已經(jīng)插入了10條記錄：

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)

??? ->???? PARTITION BY RANGE(YEAR(purchased))

??? ->???? (

??? ->???????? PARTITION p0 VALUES LESS THAN (1990),

??? ->???????? PARTITION p1 VALUES LESS THAN (1995),

??? ->???????? PARTITION p2 VALUES LESS THAN (2000),

??? ->???????? PARTITION p3 VALUES LESS THAN (2005)

??? ->???? );

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tr VALUES

??? ->???? (1, 'desk organiser', '2003-10-15'),

??? ->???? (2, 'CD player', '1993-11-05'),

??? ->???? (3, 'TV set', '1996-03-10'),

??? ->???? (4, 'bookcase', '1982-01-10'),

??? ->???? (5, 'exercise bike', '2004-05-09'),

??? ->???? (6, 'sofa', '1987-06-05'),

??? ->???? (7, 'popcorn maker', '2001-11-22'),

??? ->???? (8, 'aquarium', '1992-08-04'),

??? ->???? (9, 'study desk', '1984-09-16'),

??? ->???? (10, 'lava lamp', '1998-12-25');

Query OK, 10 rows affected (0.01 sec)?????????????????

可以通過使用下面的命令查看那些記錄已經(jīng)插入到了分區(qū)p2中：

mysql> SELECT * FROM tr

??? -> WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';

+------+-----------+------------+

| id?? | name????? | purchased? |

+------+-----------+------------+

|??? 3 | TV set??? | 1996-03-10 |

|?? 10 | lava lamp | 1998-12-25 |

+------+-----------+------------+

2 rows in set (0.00 sec)

要?jiǎng)h除名字為p2的分區(qū)，執(zhí)行下面的命令：

mysql> ALTER TABLE tr DROP PARTITION p2;

Query OK, 0 rows affected (0.03 sec)

記住下面一點(diǎn)非常重要：當(dāng)刪除了一個(gè)分區(qū)，也同時(shí)刪除了該分區(qū)中所有的數(shù)據(jù)。可以通過重新運(yùn)行前面的SELECT查詢來驗(yàn)證這一點(diǎn)：

mysql> SELECT * FROM tr WHERE purchased

????-> BETWEEN '1995-01-01' AND '1999-12-31';

Empty set (0.00 sec)

如果希望從所有分區(qū)刪除所有的數(shù)據(jù)，但是又保留表的定義和表的分區(qū)模式，使用TRUNCATE TABLE命令。（請(qǐng)參見13.2.9節(jié)，“TRUNCATE語法”）。

如果希望改變表的分區(qū)而又不丟失數(shù)據(jù)，使用“ALTER TABLE ... REORGANIZE PARTITION”語句。參見下面的內(nèi)容，或者在13.1.2節(jié)，“ALTER TABLE語法”?中參考關(guān)于REORGANIZE PARTITION的信息。

如果現(xiàn)在執(zhí)行一個(gè)SHOW CREATE TABLE命令，可以觀察到表的分區(qū)結(jié)構(gòu)是如何被改變的：

mysql> SHOW CREATE TABLE tr\G

*************************** 1. row ***************************

?????? Table: tr

Create Table: CREATE TABLE `tr` (

? `id` int(11) default NULL,

? `name` varchar(50) default NULL,

? `purchased` date default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

PARTITION BY RANGE (YEAR(purchased)) (

? PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,

??PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,

??PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM

1 row in set (0.01 sec)

如果插入購(gòu)買日期列的值在'1995-01-01'和?'2004-12-31'之間（含）的新行到已經(jīng)修改后的表中時(shí)，這些行將被保存在分區(qū)p3中。可以通過下面的方式來驗(yàn)證這一點(diǎn)：

mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tr WHERE purchased

????-> BETWEEN '1995-01-01' AND '2004-12-31';

+------+----------------+------------+

| id?? | name?????????? | purchased? |

+------+----------------+------------+

|?? 11 | pencil holder? | 1995-07-12 |

|??? 1 | desk organiser | 2003-10-15 |

|??? 5 | exercise bike? | 2004-05-09 |

|??? 7 | popcorn maker? | 2001-11-22 |

+------+----------------+------------+

4 rows in set (0.00 sec)

mysql> ALTER TABLE tr DROP PARTITION p3;

Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM tr WHERE purchased

????-> BETWEEN '1995-01-01' AND '2004-12-31';

Empty set (0.00 sec)

注意：由“ALTER TABLE ... DROP PARTITION”語句引起的、從表中刪除的行數(shù)并沒有被服務(wù)器報(bào)告出來，就好像通過同等的DELETE查詢操作一樣。

刪除LIST分區(qū)使用和刪除RANGE分區(qū)完全相同的“ALTER TABLE ... DROP PARTITION”語法。但是，在對(duì)其后使用這個(gè)表的影響方面，還是有重大的區(qū)別：在這個(gè)表中，再也不能插入這么一些行，這些行的列值包含在定義已經(jīng)刪除了的分區(qū)的值列表中?(有關(guān)示例，請(qǐng)參見18.2.2節(jié)，“LIST分區(qū)”?）。

要增加一個(gè)新的RANGE或LIST分區(qū)到一個(gè)前面已經(jīng)分區(qū)了的表，使用“ALTER TABLE ... ADD PARTITION”語句。對(duì)于使用RANGE分區(qū)的表，可以用這個(gè)語句添加新的區(qū)間到已有分區(qū)的序列的前面或后面。例如，假設(shè)有一個(gè)包含你所在組織的全體成員數(shù)據(jù)的分區(qū)表，該表的定義如下：

CREATE TABLE members (

??? id INT,

????fname VARCHAR(25),

??? lname VARCHAR(25),

????dob DATE

PARTITION BY RANGE(YEAR(dob)) (

??? PARTITION p0 VALUES LESS THAN (1970),

??? PARTITION p1 VALUES LESS THAN (1980),

??? PARTITION p2 VALUES LESS THAN (1990)

);

進(jìn)一步假設(shè)成員的最小年紀(jì)是16歲。隨著日歷接近2005年年底，你會(huì)認(rèn)識(shí)到不久將要接納1990年（以及以后年份）出生的成員。可以按照下面的方式，修改成員表來容納出生在1990－1999年之間的成員：

ALTER TABLE ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

要點(diǎn)：對(duì)于通過RANGE分區(qū)的表，只可以使用ADD PARTITION添加新的分區(qū)到分區(qū)列表的高端。設(shè)法通過這種方式在現(xiàn)有分區(qū)的前面或之間增加一個(gè)新的分區(qū)，將會(huì)導(dǎo)致下面的一個(gè)錯(cuò)誤：

mysql> ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (1960));

錯(cuò)誤1463 (HY000): 對(duì)每個(gè)分區(qū)，VALUES LESS THAN 值必須嚴(yán)格增長(zhǎng)

采用一個(gè)類似的方式，可以增加新的分區(qū)到已經(jīng)通過LIST分區(qū)的表。例如，假定有如下定義的一個(gè)表：

CREATE TABLE tt (

??? id INT,

????data INT

PARTITION BY LIST(data) (

??? PARTITION p0 VALUES IN (5, 10, 15),

??? PARTITION p1 VALUES IN (6, 12, 18)

)；

可以通過下面的方法添加一個(gè)新的分區(qū)，用來保存擁有數(shù)據(jù)列值7，14和21的行：

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21))；

注意：不能添加這樣一個(gè)新的LIST分區(qū)，該分區(qū)包含有已經(jīng)包含在現(xiàn)有分區(qū)值列表中的任意值。如果試圖這樣做，將會(huì)導(dǎo)致錯(cuò)誤：

mysql> ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8, 12));

錯(cuò)誤1465 (HY000): 在LIST分區(qū)中，同一個(gè)常數(shù)的多次定義

因?yàn)閹в袛?shù)據(jù)列值12的任何行都已經(jīng)分配給了分區(qū)p1，所以不能在表tt上再創(chuàng)建一個(gè)其值列表包括12的新分區(qū)。為了實(shí)現(xiàn)這一點(diǎn)，可以先刪除分區(qū)p1，添加分區(qū)np，然后使用修正后的定義添加一個(gè)新的分區(qū)p1。但是，正如我們前面討論過的，這將導(dǎo)致保存在分區(qū)p1中的所有數(shù)據(jù)丟失——而這往往并不是你所真正想要做的。另外一種解決方法可能是，建立一個(gè)帶有新分區(qū)的表的副本，然后使用“CREATE TABLE ... SELECT ...”把數(shù)據(jù)拷貝到該新表中，然后刪除舊表，重新命名新表，但是，當(dāng)需要處理大量的數(shù)據(jù)時(shí)，這可能是非常耗時(shí)的。在需要高可用性的場(chǎng)合，這也可能是不可行的。

幸運(yùn)地是，MySQL?的分區(qū)實(shí)現(xiàn)提供了在不丟失數(shù)據(jù)的條件下重新定義分區(qū)的方式。讓我們首先看兩個(gè)涉及到RANGE分區(qū)的簡(jiǎn)單例子。回想一下現(xiàn)在定義如下的成員表：

mysql> SHOW CREATE TABLE members\G

*************************** 1. row ***************************

?????? Table: members

Create Table: CREATE TABLE `members` (

? `id` int(11) default NULL,

? `fname` varchar(25) default NULL,

? `lname` varchar(25) default NULL,

? `dob` date default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

PARTITION BY RANGE (YEAR(dob)) (

? PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM,

??PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM,

??PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM.

? PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM

假定想要把表示出生在1960年前成員的所有行移入到一個(gè)分開的分區(qū)中。正如我們前面看到的，不能通過使用“ALTER TABLE ... ADD PARTITION”來實(shí)現(xiàn)這一點(diǎn)。但是，要實(shí)現(xiàn)這一點(diǎn)，可以使用ALTER TABLE上的另外一個(gè)與分區(qū)有關(guān)的擴(kuò)展，具體實(shí)現(xiàn)如下：

ALTER TABLE members REORGANIZE PARTITION p0 INTO (

??? PARTITION s0 VALUES LESS THAN (1960),

??? PARTITION s1 VALUES LESS THAN (1970)

)；

實(shí)際上，這個(gè)命令把分區(qū)p0分成了兩個(gè)新的分區(qū)s0和s1。同時(shí)，它還根據(jù)包含在兩個(gè)“PARTITION ... VALUES ...”子句中的規(guī)則，把保存在分區(qū)p0中的數(shù)據(jù)移入到兩個(gè)新的分區(qū)中，所以分區(qū)s0中只包含YEAR(dob)小于1960的那些行，s1中包含那些YEAR(dob)大于或等于1960但是小于1970的行。

一個(gè)REORGANIZE PARTITION語句也可以用來合并相鄰的分區(qū)。可以使用如下的語句恢復(fù)成員表到它以前的分區(qū)：

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (

??? PARTITION p0 VALUES LESS THAN (1970)

)；

使用“REORGANIZE PARTITION”拆分或合并分區(qū)，沒有數(shù)據(jù)丟失。在執(zhí)行上面的語句中，MySQL?把保存在分區(qū)s0和s1中的所有數(shù)據(jù)都移到分區(qū)p0中。

“REORGANIZE PARTITION”的基本語法是：

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions)；

其中，tbl_name?是分區(qū)表的名稱，partition_list?是通過逗號(hào)分開的、一個(gè)或多個(gè)將要被改變的現(xiàn)有分區(qū)的列表。partition_definitions?是一個(gè)是通過逗號(hào)分開的、新分區(qū)定義的列表，它遵循與用在“CREATE TABLE”中的partition_definitions?相同的規(guī)則?(請(qǐng)參見13.1.5節(jié)，“CREATE TABLE語法”)。應(yīng)當(dāng)注意到，在把多少個(gè)分區(qū)合并到一個(gè)分區(qū)或把一個(gè)分區(qū)拆分成多少個(gè)分區(qū)方面，沒有限制。例如，可以重新組織成員表的四個(gè)分區(qū)成兩個(gè)分區(qū)，具體實(shí)現(xiàn)如下：

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (

??? PARTITION m0 VALUES LESS THAN (1980),

??? PARTITION m1 VALUES LESS THAN (2000)

)；

同樣，對(duì)于按LIST分區(qū)的表，也可以使用REORGANIZE PARTITION。讓我們回到那個(gè)問題，即增加一個(gè)新的分區(qū)到已經(jīng)按照LIST分區(qū)的表tt中，但是因?yàn)樵撔路謪^(qū)有一個(gè)值已經(jīng)存在于現(xiàn)有分區(qū)的值列表中，添加新的分區(qū)失敗。我們可以通過先添加只包含非沖突值的分區(qū)，然后重新組織該新分區(qū)和現(xiàn)有的那個(gè)分區(qū)，以便保存在現(xiàn)有的那個(gè)分區(qū)中的值現(xiàn)在移到了新的分區(qū)中，來處理這個(gè)問題：

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));

ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (

??? PARTITION p1 VALUES IN (6, 18),

??? PARTITION np VALUES in (4, 8, 12)

)；

當(dāng)使用“ALTER TABLE ... REORGANIZE PARTITION”來對(duì)已經(jīng)按照RANGE和LIST分區(qū)表進(jìn)行重新分區(qū)時(shí)，下面是一些要記住的關(guān)鍵點(diǎn)：

·?????????用來確定新分區(qū)模式的PARTITION子句使用與用在CREATE TABLE中確定分區(qū)模式的PARTITION子句相同的規(guī)則。

最重要的是，應(yīng)該記住：新分區(qū)模式不能有任何重疊的區(qū)間（適用于按照RANGE分區(qū)的表）或值集合（適用于重新組織按照LIST分區(qū)的表）。

·?????????partition_definitions?列表中分區(qū)的合集應(yīng)該與在partition_list?中命名分區(qū)的合集占有相同的區(qū)間或值集合。

例如，在本節(jié)中用作例子的成員表中，分區(qū)p1和p2總共覆蓋了1980到1999的這些年。因此，對(duì)這兩個(gè)分區(qū)的重新組織都應(yīng)該覆蓋相同范圍的年份。

·?????????對(duì)于按照RANGE分區(qū)的表，只能重新組織相鄰的分區(qū)；不能跳過RANGE分區(qū)。

例如，不能使用以“ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ...”開頭的語句，來重新組織本節(jié)中用作例子的成員表。因?yàn)?#xff0c;p0覆蓋了1970年以前的年份，而p2覆蓋了從1990到1999（包括1990和1999）之間的年份，因而這兩個(gè)分區(qū)不是相鄰的分區(qū)。

·?????????不能使用REORGANIZE PARTITION來改變表的分區(qū)類型；也就是說，例如，不能把RANGE分區(qū)變?yōu)镠ASH分區(qū)，反之亦然。也不能使用該命令來改變分區(qū)表達(dá)式或列。如果想在不刪除和重建表的條件下實(shí)現(xiàn)這兩個(gè)任務(wù)，可以使用“ALTER TABLE ... PARTITION BY ....”，例如：

·??????????????? ALTER TABLE members

·??????????????? ????PARTITION BY HASH(YEAR(dob))

·??????????????? ????PARTITIONS 8；

注釋：在MySQL 5.1發(fā)布前的版本中，“ALTER TABLE ... PARTITION BY ...”還沒有實(shí)現(xiàn)。作為替代，要么使用先刪除表，然后使用想要的分區(qū)重建表，或者——如果需要保留已經(jīng)存儲(chǔ)在表中的數(shù)據(jù)——可以使用“CREATE TABLE ... SELECT ...”來創(chuàng)建新的表，然后從舊表中把數(shù)據(jù)拷貝到新表中，再刪除舊表，如有必要，最后重新命名新表。

18.3.2.?HASH和KEY分區(qū)的管理

在改變分區(qū)設(shè)置方面，按照HASH分區(qū)或KEY分區(qū)的表彼此非常相似，但是它們又與按照RANGE或LIST分區(qū)的表在很多方面有差別。所以，本節(jié)只討論按照HASH或KEY分區(qū)表的修改。關(guān)于添加和刪除按照RANGE或LIST進(jìn)行分區(qū)的表的分區(qū)的討論，參見18.3.1節(jié)，“RANGE和LIST分區(qū)的管理”。

不能使用與從按照RANGE或LIST分區(qū)的表中刪除分區(qū)相同的方式，來從HASH或KEY分區(qū)的表中刪除分區(qū)。但是，可以使用“ALTER TABLE ... COALESCE PARTITION”命令來合并HASH或KEY分區(qū)。例如，假定有一個(gè)包含顧客信息數(shù)據(jù)的表，它被分成了12個(gè)分區(qū)。該顧客表的定義如下：

CREATE TABLE clients (

??? id INT,

??? fname VARCHAR(30),

? ??lname VARCHAR(30),

??? signed DATE

PARTITION BY HASH( MONTH(signed) )

PARTITIONS 12；

要減少分區(qū)的數(shù)量從12到6，執(zhí)行下面的ALTER TABLE命令：

mysql> ALTER TABLE clients COALESCE PARTITION 6；

Query OK, 0 rows affected (0.02 sec)

對(duì)于按照HASH，KEY，LINEAR HASH，或LINEAR KEY分區(qū)的表，?COALESCE能起到同樣的作用。下面是一個(gè)類似于前面例子的另外一個(gè)例子，它們的區(qū)別只是在于表是按照LINEAR KEY?進(jìn)行分區(qū)：

mysql> CREATE TABLE clients_lk (

??? ->???? id INT,

??? ->???? fname VARCHAR(30),

??? ->???? lname VARCHAR(30),

??? ->???? signed DATE

??? -> )

??? -> PARTITION BY LINEAR KEY(signed)

??? -> PARTITIONS 12；

Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE clients_lk COALESCE PARTITION 6；

Query OK, 0 rows affected (0.06 sec)

Records: 0? Duplicates: 0? Warnings: 0

COALESCE不能用來增加分區(qū)的數(shù)量，如果你嘗試這么做，結(jié)果會(huì)出現(xiàn)類似于下面的錯(cuò)誤：

mysql> ALTER TABLE clients COALESCE PARTITION 18;

錯(cuò)誤1478 (HY000): 不能移動(dòng)所有分區(qū)，使用DROP TABLE代替

要增加顧客表的分區(qū)數(shù)量從12到18，使用“ALTER TABLE ... ADD PARTITION”,具體如下：

ALTER TABLE clients ADD PARTITION PARTITIONS 18；

注釋：“ALTER TABLE ... REORGANIZE PARTITION”不能用于按照HASH或HASH分區(qū)的表。

18.3.3.?分區(qū)維護(hù)

注釋：實(shí)際上，本節(jié)討論的命令還沒有在MySQL 5.1中實(shí)現(xiàn)，在這里提出的目的，是為了在5.1版投產(chǎn)前的開發(fā)周期期間，引出來自用戶測(cè)試該軟件的反饋意見。(換句話說，就是“請(qǐng)不要反饋這樣的缺陷，說這些命令不起作用”）。隨著MySQL5.1版開發(fā)的繼續(xù)，這些信息很有可能發(fā)生變化。隨著分區(qū)功能的實(shí)現(xiàn)和提高，我們將更新本節(jié)的內(nèi)容。

MySQL 5.1中可以執(zhí)行許多分區(qū)維護(hù)的任務(wù)。對(duì)于分區(qū)表，MySQL不支持命令CHECK TABLE，OPTIMIZE TABLE，ANALYZE TABLE，或REPAIR TABLE。作為替代，可以使用ALTER TABLE?的許多擴(kuò)展來在一個(gè)或多個(gè)分區(qū)上直接地執(zhí)行這些操作，如下面列出的那樣：

·?????????重建分區(qū):?這和先刪除保存在分區(qū)中的所有記錄，然后重新插入它們，具有同樣的效果。它可用于整理分區(qū)碎片。

示例：

ALTER TABLE t1 REBUILD PARTITION (p0, p1)；

·?????????優(yōu)化分區(qū)：如果從分區(qū)中刪除了大量的行，或者對(duì)一個(gè)帶有可變長(zhǎng)度的行（也就是說，有VARCHAR，BLOB，或TEXT類型的列）作了許多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”來收回沒有使用的空間，并整理分區(qū)數(shù)據(jù)文件的碎片。

示例：

ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1)；

在一個(gè)給定的分區(qū)表上使用“OPTIMIZE PARTITION”等同于在那個(gè)分區(qū)上運(yùn)行CHECK PARTITION，ANALYZE PARTITION，和REPAIR PARTITION。

·?????????分析分區(qū)：讀取并保存分區(qū)的鍵分布。

示例：

ALTER TABLE t1 ANALYZE PARTITION (p3)；

·?????????修補(bǔ)分區(qū)：?修補(bǔ)被破壞的分區(qū)。

示例：

ALTER TABLE t1 REPAIR PARTITION (p0,p1);

·?????????檢查分區(qū)：?可以使用幾乎與對(duì)非分區(qū)表使用CHECK TABLE?相同的方式檢查分區(qū)。

示例：

ALTER TABLE trb3 CHECK PARTITION (p1)；

這個(gè)命令可以告訴你表t1的分區(qū)p1中的數(shù)據(jù)或索引是否已經(jīng)被破壞。如果發(fā)生了這種情況，使用“ALTER TABLE ... REPAIR PARTITION”來修補(bǔ)該分區(qū)。

還可以使用mysqlcheck或myisamchk?應(yīng)用程序，在對(duì)表進(jìn)行分區(qū)時(shí)所產(chǎn)生的、單獨(dú)的MYI文件上進(jìn)行操作，來完成這些任務(wù)。請(qǐng)參見8.7節(jié)，“mysqlcheck：表維護(hù)和維修程序”。（在pre-alpha編碼中，這個(gè)功能已經(jīng)可以使用）。

18.3.4.?獲取關(guān)于分區(qū)的信息

本節(jié)討論獲取關(guān)于現(xiàn)有分區(qū)的信息。這個(gè)功能仍然處于計(jì)劃階段，所以現(xiàn)階段在這里描述的，實(shí)際上是我們想要在MySQL 5.1中實(shí)現(xiàn)的一個(gè)概觀。

如在本章中別處討論的一樣，在SHOW CREATE TABLE的輸出中包含了用于創(chuàng)建分區(qū)表的PARTITION BY子句。例如：

mysql> SHOW CREATE TABLE trb3\G

*************************** 1. row ***************************

??? ???Table: trb3

Create Table: CREATE TABLE `trb3` (

? `id` int(11) default NULL,

? `name` varchar(50) default NULL,

? `purchased` date default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

PARTITION BY RANGE (YEAR(purchased)) (

? PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,

??PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,

??PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM,

??PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM

1 row in set (0.00 sec)

注釋：當(dāng)前，對(duì)于按HASH或KEY分區(qū)的表，PARTITIONS子句并不顯示。?(Bug #14327)

SHOW TABLE STATUS用于分區(qū)表，它的輸出與用于非分區(qū)表的輸出相同，除了引擎（Engine）列總是包含'PARTITION'值。(關(guān)于這個(gè)命令的更多信息，參見13.5.4.18節(jié)，“SHOW TABLE STATUS語法”）。要獲取單個(gè)分區(qū)的狀態(tài)信息，我們計(jì)劃實(shí)現(xiàn)一個(gè)SHOW PARTITION STATUS命令（請(qǐng)參見下面)。

計(jì)劃用于分區(qū)表的、兩個(gè)附加的SHOW命令是：

·?????????SHOW PARTITIONS

這個(gè)命令預(yù)期其功能類似于SHOW TABLES和SHOW DATABASES，除了該命令將列出的是分區(qū)而不是表或數(shù)據(jù)庫(kù)。這個(gè)命令的輸出可能包含單個(gè)稱為Partitions_in_tbl_name?的列，其中tbl_name?是分區(qū)表的名字。對(duì)于SHOW TABLES命令而言，如果一旦選擇了一個(gè)數(shù)據(jù)庫(kù)，隨后該數(shù)據(jù)庫(kù)將作為SHOW TABLES命令的默認(rèn)數(shù)據(jù)庫(kù)。但是由于SHOW PARTITIONS命令不可能用這樣的方式來“選擇”一個(gè)表，它很可能需要使用FROM子句，以便MySQL知道要顯示的是哪個(gè)表的分區(qū)信息。

·?????????SHOW PARTITION STATUS

這個(gè)命令將提供關(guān)于一個(gè)或多個(gè)分區(qū)的詳細(xì)狀態(tài)信息。它的輸出很可能包含有與SHOW TABLE STATUS?的輸出相同或類似的列，此外，還包括顯示用于分區(qū)的數(shù)據(jù)和索引路徑的附加列。這個(gè)命令可能支持LIKE和FROM子句，這樣使得通過名字獲得關(guān)于一個(gè)給定分區(qū)的信息，或者獲得關(guān)于屬于指定表或數(shù)據(jù)庫(kù)的分區(qū)的信息，成為可能。

擴(kuò)展INFORMATION_SCHEMA?數(shù)據(jù)庫(kù)的計(jì)劃也在進(jìn)行中，以便提供關(guān)于分區(qū)表和分區(qū)的信息。這個(gè)計(jì)劃當(dāng)前還處一個(gè)在非常早的階段；隨著補(bǔ)充的信息變得可用，以及任何新的、與分區(qū)有關(guān)的INFORMATION_SCHEMA擴(kuò)展得以實(shí)現(xiàn)，我們將更新手冊(cè)相關(guān)部分的內(nèi)容。

轉(zhuǎn)載于:https://www.cnblogs.com/blogsme/p/3510815.html

總結(jié)

以上是生活随笔為你收集整理的第18章：MYSQL分区的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

MySQL
分区

上一篇：求助！！男票父母想请我父母吃饭！！！！！
下一篇：办不动产权证需要交多少税？