Chapter聽18.聽Partitioning - MySQL 5.1参考手册中文版

鎸夌収RANGE鍒嗗尯鐨勮〃鏄�閫氳繃濡備笅涓�绉嶆柟寮忚繘琛屽垎鍖虹殑锛屾瘡涓�鍒嗗尯鍖呭惈閭ｄ簺鍒嗗尯琛ㄨ揪寮忕殑鍊间綅浜庝竴涓�缁欏畾鐨勮繛缁�鍖洪棿鍐呯殑琛屻�傝繖浜涘尯闂磋�佽繛缁�涓斾笉鑳界浉浜掗噸鍙狅紝浣跨敤VALUES LESS THAN鎿嶄綔绗︽潵杩涜�屽畾涔夈�傚湪涓嬮潰鐨勫嚑涓�渚嬪瓙涓�锛屽亣瀹氫綘鍒涘缓浜嗕竴涓�濡備笅鐨勪竴涓�琛�锛岃�ヨ〃淇濆瓨鏈�20瀹堕煶鍍忓簵鐨勮亴鍛樿�板綍锛岃繖20瀹堕煶鍍忓簵鐨勭紪鍙蜂粠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

)锛�

鏍规嵁浣犵殑闇�瑕侊紝杩欎釜琛ㄥ彲浠ユ湁澶氱�嶆柟寮忔潵鎸夌収鍖洪棿杩涜�屽垎鍖恒�備竴绉嶆柟寮忔槸浣跨敤store_id 鍒椼�備緥濡傦紝浣犲彲鑳藉喅瀹氶�氳繃娣诲姞涓�涓�PARTITION BY RANGE瀛愬彞鎶婅繖涓�琛�鍒嗗壊鎴�4涓�鍖洪棿锛屽�備笅鎵�绀�:

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)

)锛�

鎸夌収杩欑�嶅垎鍖烘柟妗堬紝鍦ㄥ晢搴�1鍒�5宸ヤ綔鐨勯泧鍛樼浉瀵瑰簲鐨勬墍鏈夎�岃��淇濆瓨鍦ㄥ垎鍖�P0涓�锛屽晢搴�6鍒�10鐨勯泧鍛樹繚瀛樺湪P1涓�锛屼緷娆＄被鎺ㄣ�傛敞鎰忥紝姣忎釜鍒嗗尯閮芥槸鎸夐『搴忚繘琛屽畾涔夛紝浠庢渶浣庡埌鏈�楂樸�傝繖鏄�PARTITION BY RANGE 璇�娉曠殑瑕佹眰锛涘湪杩欑偣涓婏紝瀹冪被浼间簬C鎴�Java涓�鐨勨��switch ... case鈥濊��鍙ャ��

瀵逛簬鍖呭惈鏁版嵁(72, 'Michael', 'Widenius', '1998-06-25', NULL, 13)鐨勪竴涓�鏂拌�岋紝鍙�浠ュ緢瀹规槗鍦扮‘瀹氬畠灏嗘彃鍏ュ埌p2鍒嗗尯涓�锛屼絾鏄�濡傛灉澧炲姞浜嗕竴涓�缂栧彿涓虹��21鐨勫晢搴楋紝灏嗕細鍙戠敓浠�涔堝憿锛熷湪杩欑�嶆柟妗堜笅锛岀敱浜庢病鏈夎�勫垯鎶�store_id澶т簬20鐨勫晢搴楀寘鍚�鍦ㄥ唴锛屾湇鍔″櫒灏嗕笉鐭ラ亾鎶婅�ヨ�屼繚瀛樺湪浣曞�勶紝灏嗕細瀵艰嚧閿欒��銆� 瑕侀伩鍏嶈繖绉嶉敊璇�锛屽彲浠ラ�氳繃鍦�CREATE TABLE璇�鍙ヤ腑浣跨敤涓�涓�鈥�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 琛ㄧず鏈�澶х殑鍙�鑳界殑鏁存暟鍊笺�傜幇鍦�锛�store_id 鍒楀�煎ぇ浜庢垨绛変簬16锛堝畾涔変簡鐨勬渶楂樺�硷級鐨勬墍鏈夎�岄兘灏嗕繚瀛樺湪鍒嗗尯p3涓�銆傚湪灏嗘潵鐨勬煇涓�鏃跺�欙紝褰撳晢搴楁暟宸茬粡澧為暱鍒�25, 30, 鎴栨洿澶� 锛屽彲浠ヤ娇鐢�ALTER TABLE璇�鍙�涓哄晢搴�21-25, 26-30,绛夌瓑澧炲姞鏂扮殑鍒嗗尯 锛堝叧浜庡�備綍瀹炵幇鐨勮�︾粏淇℃伅鍙傝��18.3鑺傦紝鈥滃垎鍖虹�＄悊鈥� )銆�

鍦ㄥ嚑涔庝竴鏍风殑缁撴瀯涓�锛屼綘杩樺彲浠ュ熀浜庨泧鍛樼殑宸ヤ綔浠ｇ爜鏉ュ垎鍓茶〃锛屼篃灏辨槸璇达紝鍩轰簬job_code 鍒楀�肩殑杩炵画鍖洪棿銆備緥濡傗�斺�斿亣瀹�2浣嶆暟瀛楃殑宸ヤ綔浠ｇ爜鐢ㄦ潵琛ㄧず鏅�閫氾紙搴楀唴鐨勶級宸ヤ汉锛屼笁涓�鏁板瓧浠ｇ爜琛ㄧず鍔炲叕瀹ゅ拰鏀�鎸佷汉鍛橈紝鍥涗釜鏁板瓧浠ｇ爜琛ㄧず绠＄悊灞傦紝浣犲彲浠ヤ娇鐢ㄤ笅闈㈢殑璇�鍙ュ垱寤鸿�ュ垎鍖鸿〃锛�

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)

)锛�

鍦ㄨ繖涓�渚嬪瓙涓�, 搴楀唴宸ヤ汉鐩稿叧鐨勬墍鏈夎�屽皢淇濆瓨鍦ㄥ垎鍖�p0涓�锛屽姙鍏�瀹ゅ拰鏀�鎸佷汉鍛樼浉鍏崇殑鎵�鏈夎�屼繚瀛樺湪鍒嗗尯p1涓�锛岀�＄悊灞傜浉鍏崇殑鎵�鏈夎�屼繚瀛樺湪鍒嗗尯p2涓�銆�

鍦�VALUES LESS THAN 瀛愬彞涓�浣跨敤涓�涓�琛ㄨ揪寮忎篃鏄�鍙�鑳界殑銆傝繖閲屾渶鍊煎緱娉ㄦ剰鐨勯檺鍒舵槸MySQL 蹇呴』鑳藉�熻�＄畻琛ㄨ揪寮忕殑杩斿洖鍊间綔涓�LESS THAN (<)姣旇緝鐨勪竴閮ㄥ垎锛涘洜姝わ紝琛ㄨ揪寮忕殑鍊间笉鑳戒负NULL 銆傜敱浜庤繖涓�鍘熷洜锛岄泧鍛樿〃鐨�hired, separated, job_code,鍜�store_id鍒楀凡缁忚��瀹氫箟涓洪潪绌猴紙NOT NULL锛夈��

闄や簡鍙�浠ユ牴鎹�鍟嗗簵缂栧彿鍒嗗壊琛ㄦ暟鎹�澶栵紝浣犺繕鍙�浠ヤ娇鐢ㄤ竴涓�鍩轰簬涓や釜DATE 锛堟棩鏈燂級涓�鐨勪竴涓�鐨勮〃杈惧紡鏉ュ垎鍓茶〃鏁版嵁銆備緥濡傦紝鍋囧畾浣犳兂鍩轰簬姣忎釜闆囧憳绂诲紑鍏�鍙哥殑骞翠唤鏉ュ垎鍓茶〃锛屼篃灏辨槸璇达紝YEAR(separated)鐨勫�笺�傚疄鐜拌繖绉嶅垎鍖烘ā寮忕殑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 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

)锛�

鍦ㄨ繖涓�鏂规�堜腑锛屽湪1991骞村墠闆囦剑鐨勬墍鏈夐泧鍛樼殑璁板綍淇濆瓨鍦ㄥ垎鍖�p0涓�锛�1991骞村埌1995骞存湡闂�闆囦剑鐨勬墍鏈夐泧鍛樼殑璁板綍淇濆瓨鍦ㄥ垎鍖�p1涓�锛� 1996骞村埌2000骞存湡闂�闆囦剑鐨勬墍鏈夐泧鍛樼殑璁板綍淇濆瓨鍦ㄥ垎鍖�p2涓�锛�2000骞村悗闆囦剑鐨勬墍鏈夊伐浜虹殑淇℃伅淇濆瓨鍦�p3涓�銆�

RANGE鍒嗗尯鍦ㄥ�備笅鍦哄悎鐗瑰埆鏈夌敤锛�

路         褰撻渶瑕佸垹闄も�滄棫鐨勨�濇暟鎹�鏃躲�傚�傛灉浣犱娇鐢ㄤ笂闈㈡渶杩戠殑閭ｄ釜渚嬪瓙缁欏嚭鐨勫垎鍖烘柟妗堬紝浣犲彧闇�绠�鍗曞湴浣跨敤 鈥�ALTER TABLE employees DROP PARTITION p0锛�鈥�鏉ュ垹闄ゆ墍鏈夊湪1991骞村墠灏卞凡缁忓仠姝㈠伐浣滅殑闆囧憳鐩稿�瑰簲鐨勬墍鏈夎�屻��(鏇村�氫俊鎭�璇峰弬瑙�13.1.2鑺傦紝鈥淎LTER TABLE璇�娉曗�� 鍜� 18.3鑺傦紝鈥滃垎鍖虹�＄悊鈥�锛夈��瀵逛簬鏈夊ぇ閲忚�岀殑琛�锛岃繖姣旇繍琛屼竴涓�濡傗��DELETE FROM employees WHERE YEAR(separated) <= 1990锛�鈥濊繖鏍风殑涓�涓�DELETE鏌ヨ�㈣�佹湁鏁堝緱澶氥��

路         鎯宠�佷娇鐢ㄤ竴涓�鍖呭惈鏈夋棩鏈熸垨鏃堕棿鍊硷紝鎴栧寘鍚�鏈変粠涓�浜涘叾浠栫骇鏁板紑濮嬪�為暱鐨勫�肩殑鍒椼��

路         缁忓父杩愯�岀洿鎺ヤ緷璧栦簬鐢ㄤ簬鍒嗗壊琛ㄧ殑鍒楃殑鏌ヨ��銆備緥濡傦紝褰撴墽琛屼竴涓�濡傗��SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id锛�鈥濊繖鏍风殑鏌ヨ�㈡椂锛�MySQL鍙�浠ュ緢杩呴�熷湴纭�瀹氬彧鏈夊垎鍖�p2闇�瑕佹壂鎻忥紝杩欐槸鍥犱负浣欎笅鐨勫垎鍖轰笉鍙�鑳藉寘鍚�鏈夌�﹀悎璇�WHERE瀛愬彞鐨勪换浣曡�板綍銆�娉ㄩ噴锛氳繖绉嶄紭鍖栬繕娌℃湁鍦�MySQL 5.1婧愮▼搴忎腑鍚�鐢�锛屼絾鏄�锛屾湁鍏冲伐浣滄�ｅ湪杩涜�屼腑銆�

MySQL涓�鐨�LIST鍒嗗尯鍦ㄥ緢澶氭柟闈㈢被浼间簬RANGE鍒嗗尯銆傚拰鎸夌収RANGE鍒嗗尯涓�鏍凤紝姣忎釜鍒嗗尯蹇呴』鏄庣‘瀹氫箟銆傚畠浠�鐨勪富瑕佸尯鍒�鍦ㄤ簬锛�LIST鍒嗗尯涓�姣忎釜鍒嗗尯鐨勫畾涔夊拰閫夋嫨鏄�鍩轰簬鏌愬垪鐨勫�间粠灞炰簬涓�涓�鍊煎垪琛ㄩ泦涓�鐨勪竴涓�鍊硷紝鑰�RANGE鍒嗗尯鏄�浠庡睘浜庝竴涓�杩炵画鍖洪棿鍊肩殑闆嗗悎銆�LIST鍒嗗尯閫氳繃浣跨敤鈥�PARTITION BY LIST(expr)鈥�鏉ュ疄鐜帮紝鍏朵腑鈥�expr鈥� 鏄�鏌愬垪鍊兼垨涓�涓�鍩轰簬鏌愪釜鍒楀�笺�佸苟杩斿洖涓�涓�鏁存暟鍊肩殑琛ㄨ揪寮忥紝鐒跺悗閫氳繃鈥�VALUES IN (value_list)鈥濈殑鏂瑰紡鏉ュ畾涔夋瘡涓�鍒嗗尯锛屽叾涓�鈥�value_list鈥濇槸涓�涓�閫氳繃閫楀彿鍒嗛殧鐨勬暣鏁板垪琛ㄣ��

娉ㄩ噴锛�鍦�MySQL 5.1涓�锛屽綋浣跨敤LIST鍒嗗尯鏃讹紝鏈夊彲鑳藉彧鑳藉尮閰嶆暣鏁板垪琛ㄣ��

涓嶅儚鎸夌収RANGE瀹氫箟鍒嗗尯鐨勬儏褰�锛�LIST鍒嗗尯涓嶅繀澹版槑浠讳綍鐗瑰畾鐨勯『搴忋�傚叧浜�LIST鍒嗗尯鏇磋�︾粏鐨勮��娉曚俊鎭�锛岃�峰弬鑰�13.1.5鑺傦紝鈥淐REATE TABLE璇�娉曗�� 銆�

瀵逛簬涓嬮潰缁欏嚭鐨勪緥瀛愶紝鎴戜滑鍋囧畾灏嗚�佽��鍒嗗尯鐨勮〃鐨勫熀鏈�瀹氫箟鏄�閫氳繃涓嬮潰鐨勨��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鑺傦紝鈥淩ANGE鍒嗗尯鈥� 涓�鐨勪緥瀛愪腑浣跨敤鐨勬槸鍚屼竴涓�琛�锛�銆� 

鍋囧畾鏈�20涓�闊冲儚搴楋紝鍒嗗竷鍦�4涓�鏈夌粡閿�鏉冪殑鍦板尯锛屽�備笅琛ㄦ墍绀猴細

瑕佹寜鐓у睘浜庡悓涓�涓�鍦板尯鍟嗗簵鐨勮�屼繚瀛樺湪鍚屼竴涓�鍒嗗尯涓�鐨勬柟寮忔潵鍒嗗壊琛�锛屽彲浠ヤ娇鐢ㄤ笅闈㈢殑鈥�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)

)锛�

杩欎娇寰楀湪琛ㄤ腑澧炲姞鎴栧垹闄ゆ寚瀹氬湴鍖虹殑闆囧憳璁板綍鍙樺緱瀹规槗璧锋潵銆備緥濡傦紝鍋囧畾瑗垮尯鐨勬墍鏈夐煶鍍忓簵閮藉崠缁欎簡鍏朵粬鍏�鍙搞�傞偅涔堜笌鍦ㄨタ鍖洪煶鍍忓簵宸ヤ綔闆囧憳鐩稿叧鐨勬墍鏈夎�板綍锛堣�岋級鍙�浠ヤ娇鐢ㄦ煡璇⑩��ALTER TABLE employees DROP PARTITION pWest锛�鈥濇潵杩涜�屽垹闄わ紝瀹冧笌鍏锋湁鍚屾牱浣滅敤鐨�DELETE 锛堝垹闄わ級鏌ヨ�⑩��DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)锛�鈥濇瘮璧锋潵锛岃�佹湁鏁堝緱澶氥��

瑕佺偣锛氬�傛灉璇曞浘鎻掑叆鍒楀�硷紙鎴栧垎鍖鸿〃杈惧紡鐨勮繑鍥炲�硷級涓嶅湪鍒嗗尯鍊煎垪琛ㄤ腑鐨勪竴琛屾椂锛岄偅涔堚��INSERT鈥�鏌ヨ�㈠皢澶辫触骞舵姤閿欍�備緥濡傦紝鍋囧畾LIST鍒嗗尯鐨勯噰鐢ㄤ笂闈㈢殑鏂规�堬紝涓嬮潰鐨勬煡璇㈠皢澶辫触锛�

INSERT INTO employees VALUES 

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

杩欐槸鍥犱负鈥�store_id鈥�鍒楀��21涓嶈兘鍦ㄧ敤浜庡畾涔夊垎鍖�pNorth, pEast, pWest,鎴�pCentral鐨勫�煎垪琛ㄤ腑鎵惧埌銆傝�侀噸鐐规敞鎰忕殑鏄�锛�LIST鍒嗗尯娌℃湁绫讳技濡�鈥淰ALUES LESS THAN MAXVALUE鈥�杩欐牱鐨勫寘鍚�鍏朵粬鍊煎湪鍐呯殑瀹氫箟銆傚皢瑕佸尮閰嶇殑浠讳綍鍊奸兘蹇呴』鍦ㄥ�煎垪琛ㄤ腑鎵惧埌銆�

LIST鍒嗗尯闄や簡鑳藉拰RANGE鍒嗗尯缁撳悎璧锋潵鐢熸垚涓�涓�澶嶅悎鐨勫瓙鍒嗗尯锛屼笌HASH鍜�KEY鍒嗗尯缁撳悎璧锋潵鐢熸垚澶嶅悎鐨勫瓙鍒嗗尯涔熸槸鍙�鑳界殑銆� 鍏充簬杩欐柟闈㈢殑璁ㄨ�猴紝璇峰弬鑰�18.2.5鑺傦紝鈥滃瓙鍒嗗尯鈥�銆�

HASH鍒嗗尯涓昏�佺敤鏉ョ‘淇濇暟鎹�鍦ㄩ�勫厛纭�瀹氭暟鐩�鐨勫垎鍖轰腑骞冲潎鍒嗗竷銆傚湪RANGE鍜�LIST鍒嗗尯涓�锛屽繀椤绘槑纭�鎸囧畾涓�涓�缁欏畾鐨勫垪鍊兼垨鍒楀�奸泦鍚堝簲璇ヤ繚瀛樺湪鍝�涓�鍒嗗尯涓�锛涜�屽湪HASH鍒嗗尯涓�锛�MySQL 鑷�鍔ㄥ畬鎴愯繖浜涘伐浣滐紝浣犳墍瑕佸仛鐨勫彧鏄�鍩轰簬灏嗚�佽��鍝堝笇鐨勫垪鍊兼寚瀹氫竴涓�鍒楀�兼垨琛ㄨ揪寮忥紝浠ュ強鎸囧畾琚�鍒嗗尯鐨勮〃灏嗚�佽��鍒嗗壊鎴愮殑鍒嗗尯鏁伴噺銆�

瑕佷娇鐢�HASH鍒嗗尯鏉ュ垎鍓蹭竴涓�琛�锛岃�佸湪CREATE TABLE 璇�鍙ヤ笂娣诲姞涓�涓�鈥�PARTITION BY HASH (expr)鈥濆瓙鍙ワ紝鍏朵腑鈥�expr鈥濇槸涓�涓�杩斿洖涓�涓�鏁存暟鐨勮〃杈惧紡銆傚畠鍙�浠ヤ粎浠呮槸瀛楁�电被鍨嬩负MySQL 鏁村瀷鐨勪竴鍒楃殑鍚嶅瓧銆傛�ゅ�栵紝浣犲緢鍙�鑳介渶瑕佸湪鍚庨潰鍐嶆坊鍔犱竴涓�鈥�PARTITIONS num鈥濆瓙鍙ワ紝鍏朵腑num 鏄�涓�涓�闈炶礋鐨勬暣鏁帮紝瀹冭〃绀鸿〃灏嗚�佽��鍒嗗壊鎴愬垎鍖虹殑鏁伴噺銆�

渚嬪�傦紝涓嬮潰鐨勮��鍙ュ垱寤轰簡涓�涓�浣跨敤鍩轰簬鈥�store_id鈥�鍒楄繘琛� 鍝堝笇澶勭悊鐨勮〃锛岃�ヨ〃琚�鍒嗘垚浜�4涓�鍒嗗尯锛�

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锛�

濡傛灉娌℃湁鍖呮嫭涓�涓�PARTITIONS瀛愬彞锛岄偅涔堝垎鍖虹殑鏁伴噺灏嗛粯璁や负1銆� 渚嬪�栵細 瀵逛簬NDB Cluster锛堢皣锛�琛�锛岄粯璁ょ殑鍒嗗尯鏁伴噺灏嗕笌绨囨暟鎹�鑺傜偣鐨勬暟閲忕浉鍚岋紝杩欑�嶄慨姝ｅ彲鑳芥槸鑰冭檻浠讳綍MAX_ROWS 璁剧疆锛屼互渚跨‘淇濇墍鏈夌殑琛岄兘鑳藉悎閫傚湴鎻掑叆鍒板垎鍖轰腑銆傦紙鍙傝��绗�17绔狅細MySQL绨�锛�銆�

濡傛灉鍦ㄥ叧閿�瀛椻��PARTITIONS鈥�鍚庨潰娌℃湁鍔犱笂鍒嗗尯鐨勬暟閲忥紝灏嗕細鍑虹幇璇�娉曢敊璇�銆�

鈥�expr鈥濊繕鍙�浠ユ槸涓�涓�杩斿洖涓�涓�鏁存暟鐨�SQL琛ㄨ揪寮忋�備緥濡傦紝涔熻�镐綘鎯冲熀浜庨泧鐢ㄩ泧鍛樼殑骞翠唤鏉ヨ繘琛屽垎鍖恒�傝繖鍙�浠ラ�氳繃涓嬮潰鐨勮��鍙ユ潵瀹炵幇锛�

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 涓�鏈夋晥鐨勪换浣曞嚱鏁版垨鍏朵粬琛ㄨ揪寮忥紝鍙�瑕佸畠浠�杩斿洖涓�涓�鏃㈤潪甯告暟銆佷篃闈為殢鏈烘暟鐨勬暣鏁般�傦紙鎹㈠彞璇濊�达紝瀹冩棦鏄�鍙樺寲鐨勪絾鍙堟槸纭�瀹氱殑锛夈�備絾鏄�搴斿綋璁颁綇锛屾瘡褰撴彃鍏ユ垨鏇存柊锛堟垨鑰呭彲鑳藉垹闄わ級涓�琛岋紝杩欎釜琛ㄨ揪寮忛兘瑕佽�＄畻涓�娆★紱杩欐剰鍛崇潃闈炲父澶嶆潅鐨勮〃杈惧紡鍙�鑳戒細寮曡捣鎬ц兘闂�棰橈紝灏ゅ叾鏄�鍦ㄦ墽琛屽悓鏃跺奖鍝嶅ぇ閲忚�岀殑杩愮畻锛堜緥濡傛壒閲忔彃鍏ワ級鐨勬椂鍊欍��

鏈�鏈夋晥鐜囩殑鍝堝笇鍑芥暟鏄�鍙�瀵瑰崟涓�琛ㄥ垪杩涜�岃�＄畻锛屽苟涓斿畠鐨勫�奸殢鍒楀�艰繘琛屼竴鑷村湴澧炲ぇ鎴栧噺灏忥紝鍥犱负杩欒�冭檻浜嗗湪鍒嗗尯鑼冨洿涓婄殑鈥滀慨鍓�鈥濄�備篃灏辨槸璇达紝琛ㄨ揪寮忓�煎拰瀹冩墍鍩轰簬鐨勫垪鐨勫�煎彉鍖栬秺鎺ヨ繎锛�MySQL灏卞彲浠ヨ秺鏈夋晥鍦颁娇鐢ㄨ�ヨ〃杈惧紡鏉ヨ繘琛�HASH鍒嗗尯銆�

渚嬪�傦紝鈥渄ate_col鈥� 鏄�涓�涓狣ATE锛堟棩鏈燂級绫诲瀷鐨勫垪锛岄偅涔堣〃杈惧紡TO_DAYS(date_col)灏卞彲浠ヨ�存槸闅忓垪鈥渄ate_col鈥濆�肩殑鍙樺寲鑰屽彂鐢熺洿鎺ョ殑鍙樺寲锛屽洜涓哄垪鈥渄ate_col鈥濆�肩殑姣忎釜鍙樺寲锛岃〃杈惧紡鐨勫�间篃灏嗗彂鐢熶笌涔嬩竴鑷寸殑鍙樺寲銆傝�岃〃杈惧紡YEAR(date_col)鐨勫彉鍖栧氨娌℃湁琛ㄨ揪寮廡O_DAYS(date_col)閭ｄ箞鐩存帴锛屽洜涓轰笉鏄�鍒椻�渄ate_col鈥濇瘡娆″彲鑳界殑鏀瑰彉閮借兘浣胯〃杈惧紡YEAR(date_col)鍙戠敓鍚岀瓑鐨勬敼鍙樸�傚嵆渚垮�傛�わ紝琛ㄨ揪寮廦EAR(date_col)涔熻繕鏄�涓�涓�鐢ㄤ簬 鍝堝笇鍑芥暟鐨勩�佸ソ鐨勫�欓�夎〃杈惧紡锛屽洜涓哄畠闅忓垪date_col鐨勪竴閮ㄥ垎鍙戠敓鐩存帴鍙樺寲锛屽苟涓斿垪date_col鐨勫彉鍖栦笉鍙�鑳藉紩璧疯〃杈惧紡YEAR(date_col)涓嶆垚姣斾緥鐨勫彉鍖栥��

浣滀负瀵圭収锛屽亣瀹氭湁涓�涓�绫诲瀷涓烘暣鍨嬶紙INT锛夌殑銆佸垪鍚嶄负鈥渋nt_col鈥濈殑鍒椼�傜幇鍦ㄨ�冭檻琛ㄨ揪寮忊�淧OW(5-int_col,3) + 6鈥濄�傝繖瀵逛簬鍝堝笇鍑芥暟灏辨槸涓�涓�涓嶅ソ鐨勯�夋嫨锛屽洜涓衡�渋nt_col鈥濆�肩殑鍙樺寲骞朵笉鑳戒繚璇佽〃杈惧紡浜х敓鎴愭瘮渚嬬殑鍙樺寲銆傚垪 鈥渋nt_col鈥濈殑鍊煎彂鐢熶竴涓�缁欏畾鏁扮洰鐨勫彉鍖栵紝鍙�鑳戒細寮曡捣琛ㄨ揪寮忕殑鍊间骇鐢熶竴涓�寰堝ぇ涓嶅悓鐨勫彉鍖栥�備緥濡傦紝鎶婂垪鈥渋nt_col鈥濈殑鍊间粠5鍙樹负6锛岃〃杈惧紡鐨勫�煎皢浜х敓鈥滐紞1鈥濈殑鏀瑰彉锛屼絾鏄�鎶婂垪鈥渋nt_col鈥濈殑鍊间粠6鍙樹负7鏃讹紝琛ㄨ揪寮忕殑鍊煎皢浜х敓鈥滐紞7鈥濈殑鍙樺寲銆�

鎹㈠彞璇濊�达紝濡傛灉鍒楀�间笌琛ㄨ揪寮忓�间箣姣旂殑鏇茬嚎鍥捐秺鎺ヨ繎鐢辩瓑寮忊��y=nx锛堝叾涓�n涓洪潪闆剁殑甯告暟锛夋弿缁樺嚭鐨勭洿绾匡紝鍒欒�ヨ〃杈惧紡瓒婇�傚悎浜� 鍝堝笇銆傝繖鏄�鍥犱负锛岃〃杈惧紡鐨勯潪绾挎�ц秺涓ラ噸锛屽垎鍖轰腑鏁版嵁浜х敓闈炲潎琛″垎甯冪殑瓒嬪娍涔熷皢瓒婁弗閲嶃��

鐞嗚�轰笂璁诧紝瀵逛簬娑夊強鍒板�氬垪鐨勮〃杈惧紡锛屸�滀慨鍓�锛�pruning锛夆�濅篃鏄�鍙�鑳界殑锛屼絾鏄�瑕佺‘瀹氬摢浜涢�備簬 鍝堝笇鏄�闈炲父鍥伴毦鍜岃�楁椂鐨勩�傚熀浜庤繖涓�鍘熷洜锛屽疄闄呬笂涓嶆帹鑽愪娇鐢ㄦ秹鍙婂埌澶氬垪鐨勫搱甯岃〃杈惧紡銆�

褰撲娇鐢ㄤ簡鈥�PARTITION BY HASH鈥�鏃讹紝MySQL灏嗗熀浜庣敤鎴峰嚱鏁扮粨鏋滅殑妯℃暟鏉ョ‘瀹氫娇鐢ㄥ摢涓�缂栧彿鐨勫垎鍖恒�傛崲鍙ヨ瘽锛屽�逛簬涓�涓�琛ㄨ揪寮忊��expr鈥濓紝灏嗚�佷繚瀛樿�板綍鐨勫垎鍖虹紪鍙蜂负N 锛屽叾涓�鈥�N = MOD(expr, num)鈥�銆備緥濡傦紝鍋囧畾琛�t1 瀹氫箟濡備笅锛屽畠鏈�4涓�鍒嗗尯锛�

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

    PARTITION BY HASH( YEAR(col3) )

    PARTITIONS 4锛�

濡傛灉鎻掑叆涓�涓�col3鍒�鍊间负'2005-09-15'鐨勮�板綍鍒拌〃t1涓�锛岄偅涔堜繚瀛樿�ユ潯璁板綍鐨勫垎鍖虹‘瀹氬�備笅锛�

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

=  MOD(2005,4)

=  1

MySQL 5.1 杩樻敮鎸佷竴涓�琚�绉颁负鈥�linear hashing锛堢嚎鎬у搱甯屽姛鑳斤級鈥濈殑鍙橀噺锛屽畠浣跨敤涓�涓�鏇村姞澶嶆潅鐨勭畻娉曟潵纭�瀹氭柊琛屾彃鍏ュ埌宸茬粡鍒嗗尯浜嗙殑琛ㄤ腑鐨勪綅缃�銆傚叧浜庤繖绉嶇畻娉曠殑鎻忚堪锛岃�峰弬瑙�18.2.3.1鑺傦紝鈥淟INEAR HASH鍒嗗尯鈥� 銆�

姣忓綋鎻掑叆鎴栨洿鏂颁竴鏉¤�板綍锛岀敤鎴峰嚱鏁伴兘瑕佽�＄畻涓�娆°�傚綋鍒犻櫎璁板綍鏃讹紝鐢ㄦ埛鍑芥暟涔熷彲鑳借�佽繘琛岃�＄畻锛岃繖鍙栧喅浜庢墍澶勭殑鐜�澧冦��

娉ㄩ噴锛�濡傛灉灏嗚�佸垎鍖虹殑琛ㄦ湁涓�涓�鍞�涓�鐨勯敭锛岄偅涔堢敤鏉ヤ綔涓�HASH鐢ㄦ埛鍑芥暟鐨勮嚜鍙樻暟鎴栬�呬富閿�鐨�column_list鐨勮嚜鍙樻暟鐨勪换鎰忓垪閮藉繀椤绘槸閭ｄ釜閿�鐨勪竴閮ㄥ垎銆�

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锛�

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

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

    PARTITION BY LINEAR HASH( YEAR(col3) )

    PARTITIONS 6;

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

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

   = 2003 & 7

   = 3

(3 >= 6 涓哄亣锛�FALSE锛�: 璁板綍灏嗚��淇濆瓨鍒�#3鍙峰垎鍖轰腑)

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鍒嗗尯涓�)

鎸夌収KEY杩涜�屽垎鍖虹被浼间簬鎸夌収HASH鍒嗗尯锛岄櫎浜�HASH鍒嗗尯浣跨敤鐨勭敤鎴峰畾涔夌殑琛ㄨ揪寮忥紝鑰�KEY鍒嗗尯鐨� 鍝堝笇鍑芥暟鏄�鐢�MySQL 鏈嶅姟鍣ㄦ彁渚涖��MySQL 绨囷紙Cluster锛変娇鐢ㄥ嚱鏁�MD5()鏉ュ疄鐜�KEY鍒嗗尯锛涘�逛簬浣跨敤鍏朵粬瀛樺偍寮曟搸鐨勮〃锛屾湇鍔″櫒浣跨敤鍏惰嚜宸卞唴閮ㄧ殑 鍝堝笇鍑芥暟锛岃繖浜涘嚱鏁版槸鍩轰簬涓�PASSWORD()涓�鏍风殑杩愮畻娉曞垯銆�

鈥�CREATE TABLE ... PARTITION BY KEY鈥�鐨勮��娉曡�勫垯绫讳技浜庡垱寤轰竴涓�閫氳繃HASH鍒嗗尯鐨勮〃鐨勮�勫垯銆傚畠浠�鍞�涓�鐨勫尯鍒�鍦ㄤ簬浣跨敤鐨勫叧閿�瀛楁槸KEY鑰屼笉鏄�HASH锛屽苟涓�KEY鍒嗗尯鍙�閲囩敤涓�涓�鎴栧�氫釜鍒楀悕鐨勪竴涓�鍒楄〃銆�

閫氳繃绾挎��KEY鍒嗗壊涓�涓�琛ㄤ篃鏄�鍙�鑳界殑銆備笅闈㈡槸涓�涓�绠�鍗曠殑渚嬪瓙锛�

CREATE TABLE tk (

    col1 INT NOT NULL,

    col2 CHAR(5),

    col3 DATE

) 

PARTITION BY LINEAR KEY (col1)

PARTITIONS 3;

鍦�KEY鍒嗗尯涓�浣跨敤鍏抽敭瀛�LINEAR鍜屽湪HASH鍒嗗尯涓�浣跨敤鍏锋湁鍚屾牱鐨勪綔鐢�锛屽垎鍖虹殑缂栧彿鏄�閫氳繃2鐨勫箓锛�powers-of-two锛夌畻娉曞緱鍒帮紝鑰屼笉鏄�閫氳繃妯℃暟绠楁硶銆傚叧浜庤�ョ畻娉曞強鍏惰暣娑靛紡鐨勬弿杩拌�峰弬鑰� 18.2.3.1鑺傦紝鈥淟INEAR HASH鍒嗗尯鈥� 銆�

瀛愬垎鍖烘槸鍒嗗尯琛ㄤ腑姣忎釜鍒嗗尯鐨勫啀娆″垎鍓层�備緥濡傦紝鑰冭檻涓嬮潰鐨�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涓�RANGE鍒嗗尯銆傝繖3涓�鍒嗗尯涓�鐨勬瘡涓�涓�鍒嗗尯鈥斺��p0, p1, 鍜� p2 鈥斺�斿張琚�杩涗竴姝ュ垎鎴愪簡2涓�瀛愬垎鍖恒�傚疄闄呬笂锛屾暣涓�琛ㄨ��鍒嗘垚浜�3 * 2 = 6涓�鍒嗗尯銆備絾鏄�锛岀敱浜�PARTITION BY RANGE瀛愬彞鐨勪綔鐢�锛岃繖浜涘垎鍖虹殑澶�2涓�鍙�淇濆瓨鈥減urchased鈥�鍒椾腑鍊煎皬浜�1990鐨勯偅浜涜�板綍銆�

鍦�MySQL 5.1涓�锛屽�逛簬宸茬粡閫氳繃RANGE鎴�LIST鍒嗗尯浜嗙殑琛ㄥ啀杩涜�屽瓙鍒嗗尯鏄�鍙�鑳界殑銆傚瓙鍒嗗尯鏃㈠彲浠ヤ娇鐢�HASH甯屽垎鍖猴紝涔熷彲浠ヤ娇鐢�KEY鍒嗗尯銆傝繖涔熻��绉颁负澶嶅悎鍒嗗尯锛�composite partitioning锛夈��

涓轰簡瀵逛釜鍒�鐨勫瓙鍒嗗尯鎸囧畾閫夐」锛屼娇鐢�SUBPARTITION 瀛愬彞鏉ユ槑纭�瀹氫箟瀛愬垎鍖轰篃鏄�鍙�鑳界殑銆備緥濡傦紝鍒涘缓鍦ㄥ墠闈�渚嬪瓙涓�缁欏嚭鐨勫悓涓�涓�琛ㄧ殑銆佷竴涓�鏇村姞璇︾粏鐨勬柟寮忓�備笅锛�

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

        )

    );

鍑犵偣瑕佹敞鎰忕殑璇�娉曢」锛�

路         姣忎釜鍒嗗尯蹇呴』鏈夌浉鍚屾暟閲忕殑瀛愬垎鍖恒��

路         濡傛灉鍦ㄤ竴涓�鍒嗗尯琛ㄤ笂鐨勪换浣曞垎鍖轰笂浣跨敤SUBPARTITION 鏉ユ槑纭�瀹氫箟浠讳綍瀛愬垎鍖猴紝閭ｄ箞灏卞繀椤诲畾涔夋墍鏈夌殑瀛愬垎鍖恒�傛崲鍙ヨ瘽璇达紝涓嬮潰鐨勮��鍙ュ皢鎵ц�屽け璐ワ細

路                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

路                        )

路                    )锛�

鍗充究杩欎釜璇�鍙ュ寘鍚�浜嗕竴涓�SUBPARTITIONS 2瀛愬彞锛屼絾鏄�瀹冧粛鐒朵細鎵ц�屽け璐ャ��

路         姣忎釜SUBPARTITION 瀛愬彞蹇呴』鍖呮嫭 (鑷冲皯)瀛愬垎鍖虹殑涓�涓�鍚嶅瓧銆傚惁鍒欙紝浣犲彲鑳借�佸�硅�ュ瓙鍒嗗尯璁剧疆浠讳綍浣犳墍闇�瑕佺殑閫夐」锛屾垨鑰呭厑璁歌�ュ瓙鍒嗗尯瀵归偅浜涢�夐」閲囩敤鍏堕粯璁ょ殑璁剧疆銆�

路         鍦ㄦ瘡涓�鍒嗗尯鍐咃紝瀛愬垎鍖虹殑鍚嶅瓧蹇呴』鏄�鍞�涓�鐨勶紝浣嗘槸鍦ㄦ暣涓�琛ㄤ腑锛屾病鏈夊繀瑕佷繚鎸佸敮涓�銆備緥濡傦紝涓嬮潰鐨�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

路                        )

路                    )锛�

瀛愬垎鍖哄彲浠ョ敤浜庣壒鍒�澶х殑琛�锛屽湪澶氫釜纾佺洏闂村垎閰嶆暟鎹�鍜岀储寮曘�傚亣璁炬湁6涓�纾佺洏锛屽垎鍒�涓�/disk0锛� /disk1锛� /disk2绛夈�傜幇鍦ㄨ�冭檻涓嬮潰鐨勪緥瀛愶細

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'

        )

    )锛�

鍦ㄨ繖涓�渚嬪瓙涓�锛屾瘡涓�RANGE鍒嗗尯鐨勬暟鎹�鍜岀储寮曢兘浣跨敤涓�涓�鍗曠嫭鐨勭�佺洏銆傝繕鍙�鑳芥湁璁稿�氬叾浠栫殑鍙樺寲锛涗笅闈㈡槸鍙﹀�栦竴涓�鍙�鑳界殑渚嬪瓙锛�

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

        )

    )锛�

鍦ㄨ繖涓�渚嬪瓙涓�锛屽瓨鍌ㄧ殑鍒嗛厤濡備笅锛�

路         璐�涔版棩鏈熷湪1990骞村墠鐨勮�板綍鍗犱簡澶ч噺鐨勫瓨鍌ㄧ┖闂达紝鎵�浠ユ妸瀹冨垎涓轰簡鍥涗釜閮ㄥ垎杩涜�屽瓨鍌�锛岀粍鎴�p0鍒嗗尯鐨�涓や釜瀛愬垎鍖猴紙s0a 鍜�s0b锛夌殑鏁版嵁鍜岀储寮曢兘鍒嗗埆鐢ㄤ竴涓�鍗曠嫭鐨勭�佺洏杩涜�屽瓨鍌ㄣ�傛崲鍙ヨ瘽璇达細

o        瀛愬垎鍖�s0a 鐨勬暟鎹�淇濆瓨鍦ㄧ�佺洏/disk0涓�銆�

o        瀛愬垎鍖�s0a 鐨勭储寮曚繚瀛樺湪纾佺洏/disk1涓�銆�

o        瀛愬垎鍖�s0b 鐨勬暟鎹�淇濆瓨鍦ㄧ�佺洏/disk2涓�銆�

o        瀛愬垎鍖�s0b 鐨勭储寮曚繚瀛樺湪纾佺洏/disk3涓�銆�

路         淇濆瓨璐�涔版棩鏈熶粠1990骞村埌1999骞撮棿鐨勮�板綍锛堝垎鍖�p1锛変笉闇�瑕佷繚瀛樿喘涔版棩鏈熷湪1990骞翠箣鍓嶇殑璁板綍閭ｄ箞澶х殑瀛樺偍绌洪棿銆傝繖浜涜�板綍鍒嗗湪2涓�纾佺洏锛�/disk4鍜�/disk5锛変笂淇濆瓨锛岃�屼笉鏄�4涓�纾佺洏锛�

o        灞炰簬鍒嗗尯p1鐨勭��涓�涓�瀛愬垎鍖猴紙s1a锛夌殑鏁版嵁鍜岀储寮曚繚瀛樺湪纾佺洏/disk4涓� 鈥� 鍏朵腑鏁版嵁淇濆瓨鍦ㄨ矾寰�/disk4/data涓嬶紝绱㈠紩淇濆瓨鍦�/disk4/idx涓嬨��

o        灞炰簬鍒嗗尯p1鐨勭��浜屼釜瀛愬垎鍖猴紙s1b锛夌殑鏁版嵁鍜岀储寮曚繚瀛樺湪纾佺洏/disk5涓� 鈥� 鍏朵腑鏁版嵁淇濆瓨鍦ㄨ矾寰�/disk5/data涓嬶紝绱㈠紩淇濆瓨鍦�/disk5/idx涓嬨��

路         淇濆瓨璐�涔版棩鏈熶粠2000骞村埌鐜板湪鐨勮�板綍锛堝垎鍖�p2锛変笉闇�瑕佸墠闈�涓や釜RANGE鍒嗗尯閭ｄ箞澶х殑绌洪棿銆傚綋鍓嶏紝鍦ㄩ粯璁ょ殑浣嶇疆鑳藉�熻冻澶熶繚瀛樻墍鏈夎繖浜涜�板綍銆�

灏嗘潵锛屽�傛灉浠�2000骞村紑濮嬪悗鍗佸勾璐�涔扮殑鏁伴噺宸茬粡杈惧埌浜嗛粯璁ょ殑浣嶇疆涓嶈兘澶熸彁渚涜冻澶熺殑淇濆瓨绌洪棿鏃讹紝鐩稿簲鐨勮�板綍锛堣�岋級鍙�浠ラ�氳繃浣跨敤鈥�ALTER TABLE ... REORGANIZE PARTITION鈥濊��鍙ョЩ鍔ㄥ埌鍏朵粬鐨勪綅缃�銆傚叧浜庡�備綍瀹炵幇鐨勮�存槑锛岃�峰弬瑙�18.3鑺傦紝鈥滃垎鍖虹�＄悊鈥� 銆�

MySQL 涓�鐨勫垎鍖哄湪绂佹�㈢┖鍊硷紙NULL锛変笂娌℃湁杩涜�屽�勭悊锛屾棤璁哄畠鏄�涓�涓�鍒楀�艰繕鏄�涓�涓�鐢ㄦ埛瀹氫箟琛ㄨ揪寮忕殑鍊笺�備竴鑸�鑰岃█锛屽湪杩欑�嶆儏鍐典笅MySQL 鎶�NULL瑙嗕负0銆傚�傛灉浣犲笇鏈涘洖閬胯繖绉嶅仛娉曪紝浣犲簲璇ュ湪璁捐�¤〃鏃朵笉鍏佽�哥┖鍊硷紱鏈�鍙�鑳界殑鏂规硶鏄�锛岄�氳繃澹版槑鍒椻��NOT NULL鈥濇潵瀹炵幇杩欎竴鐐广��

鍦ㄦ湰鑺備腑锛屾垜浠�鎻愪緵浜嗕竴浜涗緥瀛愶紝鏉ヨ�存槑褰撳喅瀹氫竴涓�琛屽簲璇ヤ繚瀛樺埌鍝�涓�鍒嗗尯鏃讹紝MySQL 鏄�濡備綍澶勭悊NULL鍊肩殑銆�

濡傛灉鎻掑叆涓�琛屽埌鎸夌収RANGE鎴�LIST鍒嗗尯鐨勮〃锛岃�ヨ�岀敤鏉ョ‘瀹氬垎鍖虹殑鍒楀�间负NULL锛屽垎鍖哄皢鎶婅��NULL鍊艰�嗕负0銆備緥濡傦紝鑰冭檻涓嬮潰鐨勪袱涓�琛�锛岃〃鐨勫垱寤哄拰鎻掑叆璁板綍濡備笅锛�

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)

鍦ㄤ袱涓�琛ㄤ腑锛�id鍒楁病鏈夊０鏄庝负鈥�NOT NULL鈥濓紝杩欐剰鍛崇潃瀹冧滑鍏佽��Null鍊笺�傚彲浠ラ�氳繃鍒犻櫎杩欎簺鍒嗗尯锛岀劧鍚庨噸鏂拌繍琛�SELECT 璇�鍙ワ紝鏉ラ獙璇佽繖浜涜�岃��淇濆瓨鍦ㄦ瘡涓�琛ㄧ殑p1鍒嗗尯涓�锛�

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鍒嗗尯鐨勬儏鍐典笅锛屼换浣曚骇鐢�NULL鍊肩殑琛ㄨ揪寮忛兘瑙嗗悓濂藉儚瀹冪殑杩斿洖鍊间负0銆傛垜浠�鍙�浠ラ�氳繃鍏堝垱寤轰竴涓�鎸�HASH鍒嗗尯鐨勮〃锛岀劧鍚庢彃鍏ヤ竴涓�鍖呭惈鏈夐�傚綋鍊肩殑璁板綍锛屽啀妫�鏌ュ�规枃浠剁郴缁熺殑浣滅敤锛屾潵楠岃瘉杩欎竴鐐广�傚亣瀹氭湁浣跨敤涓嬮潰鐨勮��鍙ュ湪娴嬭瘯鏁版嵁搴撲腑鍒涘缓浜嗕竴涓�琛�tnhash锛�

CREATE TABLE tnhash (

    id INT,

    name VARCHAR(5)

)

PARTITION BY HASH(id)

PARTITIONS 2锛�

鍋囧��Linux 涓婄殑MySQL 鐨勪竴涓�RPM瀹夎�咃紝杩欎釜璇�鍙ュ湪鐩�褰�/var/lib/mysql/test涓�鍒涘缓浜嗕袱涓�.MYD鏂囦欢锛岃繖涓や釜鏂囦欢鍙�浠ュ湪bash shell涓�鏌ョ湅锛岀粨鏋滃�備笅锛�

/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

娉ㄦ剰锛氭瘡涓�鏂囦欢鐨勫ぇ灏忎负0瀛楄妭銆傜幇鍦ㄥ湪琛�tnhash 涓�鎻掑叆涓�琛�id鍒楀�间负NULL鐨勮�岋紝鐒跺悗楠岃瘉璇ヨ�屽凡缁忚��鎻掑叆锛�

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)

鍥炴兂涓�涓嬶紝瀵逛簬浠绘剰鐨勬暣鏁�N,NULL MOD N 鐨勫�兼�绘槸绛変簬NULL銆傝繖涓�缁撴灉鍦ㄧ‘瀹氭�ｇ‘鐨勫垎鍖烘柟闈㈣��璁や负鏄�0銆傚洖鍒扮郴缁�shell(浠嶇劧鍋囧畾bash鐢ㄤ簬杩欎釜鐩�鐨�) 锛岄�氳繃鍐嶆�″垪鍑烘暟鎹�鏂囦欢锛屽彲浠ョ湅鍑哄�艰��鎴愬姛鍦版彃鍏ュ埌绗�涓�涓�鍒嗗尯锛堥粯璁ゅ悕绉颁负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锛屽畠鍦ㄧ�佺洏涓婄殑灏哄�稿�炲姞浜嗭紝鑰屾病鏈夊奖鍝嶅叾浠栫殑鏂囦欢銆�

鍋囧畾鏈変笅闈㈢殑涓�涓�琛�锛�

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鍑芥暟涓�鏍凤紝YEAR(NULL)杩斿洖NULL鍊笺�備竴涓�dt鍒楀�间负NULL鐨勮�岋紝鍏跺垎鍖鸿〃杈惧紡鐨勮�＄畻缁撴灉琚�瑙嗕负0锛岃�ヨ�岃��鎻掑叆鍒板垎鍖�p0涓�銆�

鍏充簬濡備綍娣诲姞鍜屽垹闄ゅ垎鍖虹殑澶勭悊锛�RANGE鍜�LIST鍒嗗尯闈炲父鐩镐技銆傚熀浜庤繖涓�鍘熷洜锛屾垜浠�鍦ㄦ湰鑺傝�ㄨ�鸿繖涓ょ�嶅垎鍖虹殑绠＄悊銆傚叧浜�HASH鍜�KEY鍒嗗尯绠＄悊鐨勪俊鎭�锛岃�峰弬瑙�18.3.2鑺傦紝鈥淗ASH鍜孠EY鍒嗗尯鐨勭�＄悊鈥�銆傚垹闄や竴涓�RANGE鎴�LIST鍒嗗尯姣斿�炲姞涓�涓�鍒嗗尯瑕佹洿鍔犵畝鍗曟槗鎳傦紝鎵�浠ユ垜浠�鍏堣�ㄨ�哄墠鑰呫��

浠庝竴涓�鎸夌収RANGE鎴�LIST鍒嗗尯鐨勮〃涓�鍒犻櫎涓�涓�鍒嗗尯锛屽彲浠ヤ娇鐢ㄥ甫涓�涓�DROP PARTITION瀛愬彞鐨�ALTER TABLE鍛戒护鏉ュ疄鐜般�傝繖閲屾湁涓�涓�闈炲父鍩烘湰鐨勪緥瀛愶紝鍋囪�惧凡缁忎娇鐢ㄤ笅闈㈢殑CREATE TABLE鍜�INSERT璇�鍙ュ垱寤轰簡涓�涓�鎸夌収RANGE鍒嗗尯鐨勮〃锛屽苟涓斿凡缁忔彃鍏ヤ簡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)                  

鍙�浠ラ�氳繃浣跨敤涓嬮潰鐨勫懡浠ゆ煡鐪嬮偅浜涜�板綍宸茬粡鎻掑叆鍒颁簡鍒嗗尯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)

瑕佸垹闄ゅ悕瀛椾负p2鐨勫垎鍖猴紝鎵ц�屼笅闈㈢殑鍛戒护锛�

mysql> ALTER TABLE tr DROP PARTITION p2;

Query OK, 0 rows affected (0.03 sec)

璁颁綇涓嬮潰涓�鐐归潪甯搁噸瑕侊細褰撳垹闄や簡涓�涓�鍒嗗尯锛屼篃鍚屾椂鍒犻櫎浜嗚�ュ垎鍖轰腑鎵�鏈夌殑鏁版嵁銆�鍙�浠ラ�氳繃閲嶆柊杩愯�屽墠闈㈢殑SELECT鏌ヨ�㈡潵楠岃瘉杩欎竴鐐癸細

mysql> SELECT * FROM tr WHERE purchased 

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

Empty set (0.00 sec)

濡傛灉甯屾湜浠庢墍鏈夊垎鍖哄垹闄ゆ墍鏈夌殑鏁版嵁锛屼絾鏄�鍙堜繚鐣欒〃鐨勫畾涔夊拰琛ㄧ殑鍒嗗尯妯″紡锛屼娇鐢�TRUNCATE TABLE鍛戒护銆�锛�璇峰弬瑙�13.2.9鑺傦紝鈥淭RUNCATE璇�娉曗��锛�銆�

濡傛灉甯屾湜鏀瑰彉琛ㄧ殑鍒嗗尯鑰屽張涓嶄涪澶辨暟鎹�锛屼娇鐢ㄢ��ALTER TABLE ... REORGANIZE PARTITION鈥�璇�鍙�銆傚弬瑙佷笅闈㈢殑鍐呭�癸紝鎴栬�呭湪13.1.2鑺傦紝鈥淎LTER TABLE璇�娉曗�� 涓�鍙傝�冨叧浜�REORGANIZE PARTITION鐨勪俊鎭�銆�

濡傛灉鐜板湪鎵ц�屼竴涓�SHOW CREATE TABLE鍛戒护锛屽彲浠ヨ�傚療鍒拌〃鐨勫垎鍖虹粨鏋勬槸濡備綍琚�鏀瑰彉鐨勶細

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)

濡傛灉鎻掑叆璐�涔版棩鏈熷垪鐨勫�煎湪'1995-01-01'鍜� '2004-12-31'涔嬮棿锛堝惈锛夌殑鏂拌�屽埌宸茬粡淇�鏀瑰悗鐨勮〃涓�鏃讹紝杩欎簺琛屽皢琚�淇濆瓨鍦ㄥ垎鍖�p3涓�銆傚彲浠ラ�氳繃涓嬮潰鐨勬柟寮忔潵楠岃瘉杩欎竴鐐癸細

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鈥�璇�鍙ュ紩璧风殑銆佷粠琛ㄤ腑鍒犻櫎鐨勮�屾暟骞舵病鏈夎��鏈嶅姟鍣ㄦ姤鍛婂嚭鏉ワ紝灏卞ソ鍍忛�氳繃鍚岀瓑鐨�DELETE鏌ヨ�㈡搷浣滀竴鏍枫��

鍒犻櫎LIST鍒嗗尯浣跨敤鍜屽垹闄�RANGE鍒嗗尯瀹屽叏鐩稿悓鐨勨��ALTER TABLE ... DROP PARTITION鈥�璇�娉曘�備絾鏄�锛屽湪瀵瑰叾鍚庝娇鐢ㄨ繖涓�琛ㄧ殑褰卞搷鏂归潰锛岃繕鏄�鏈夐噸澶х殑鍖哄埆锛氬湪杩欎釜琛ㄤ腑锛屽啀涔熶笉鑳芥彃鍏ヨ繖涔堜竴浜涜�岋紝杩欎簺琛岀殑鍒楀�煎寘鍚�鍦ㄥ畾涔夊凡缁忓垹闄や簡鐨勫垎鍖虹殑鍊煎垪琛ㄤ腑 (鏈夊叧绀轰緥锛岃�峰弬瑙�18.2.2鑺傦紝鈥淟IST鍒嗗尯鈥� 锛�銆�

瑕佸�炲姞涓�涓�鏂扮殑RANGE鎴�LIST鍒嗗尯鍒颁竴涓�鍓嶉潰宸茬粡鍒嗗尯浜嗙殑琛�锛屼娇鐢ㄢ��ALTER TABLE ... ADD PARTITION鈥�璇�鍙ャ�傚�逛簬浣跨敤RANGE鍒嗗尯鐨勮〃锛屽彲浠ョ敤杩欎釜璇�鍙ユ坊鍔犳柊鐨勫尯闂村埌宸叉湁鍒嗗尯鐨勫簭鍒楃殑鍓嶉潰鎴栧悗闈�銆備緥濡傦紝鍋囪�炬湁涓�涓�鍖呭惈浣犳墍鍦ㄧ粍缁囩殑鍏ㄤ綋鎴愬憳鏁版嵁鐨勫垎鍖鸿〃锛岃�ヨ〃鐨勫畾涔夊�備笅锛�

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)

);

杩涗竴姝ュ亣璁炬垚鍛樼殑鏈�灏忓勾绾�鏄�16宀併�傞殢鐫�鏃ュ巻鎺ヨ繎2005骞村勾搴曪紝浣犱細璁よ瘑鍒颁笉涔呭皢瑕佹帴绾�1990骞达紙浠ュ強浠ュ悗骞翠唤锛夊嚭鐢熺殑鎴愬憳銆傚彲浠ユ寜鐓т笅闈㈢殑鏂瑰紡锛屼慨鏀规垚鍛樿〃鏉ュ�圭撼鍑虹敓鍦�1990锛�1999骞翠箣闂寸殑鎴愬憳锛�

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

瑕佺偣锛�瀵逛簬閫氳繃RANGE鍒嗗尯鐨勮〃锛屽彧鍙�浠ヤ娇鐢�ADD PARTITION娣诲姞鏂扮殑鍒嗗尯鍒板垎鍖哄垪琛ㄧ殑楂樼��銆傝�炬硶閫氳繃杩欑�嶆柟寮忓湪鐜版湁鍒嗗尯鐨勫墠闈㈡垨涔嬮棿澧炲姞涓�涓�鏂扮殑鍒嗗尯锛屽皢浼氬�艰嚧涓嬮潰鐨勪竴涓�閿欒��锛�

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

閿欒��1463 (HY000): 瀵规瘡涓�鍒嗗尯锛�VALUES LESS THAN 鍊煎繀椤讳弗鏍煎�為暱

閲囩敤涓�涓�绫讳技鐨勬柟寮忥紝鍙�浠ュ�炲姞鏂扮殑鍒嗗尯鍒板凡缁忛�氳繃LIST鍒嗗尯鐨勮〃銆備緥濡傦紝鍋囧畾鏈夊�備笅瀹氫箟鐨勪竴涓�琛�锛�

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)

)锛�

鍙�浠ラ�氳繃涓嬮潰鐨勬柟娉曟坊鍔犱竴涓�鏂扮殑鍒嗗尯锛岀敤鏉ヤ繚瀛樻嫢鏈夋暟鎹�鍒楀��7锛�14鍜�21鐨勮�岋細

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

娉ㄦ剰锛氫笉鑳芥坊鍔犺繖鏍蜂竴涓�鏂扮殑LIST鍒嗗尯锛岃�ュ垎鍖哄寘鍚�鏈夊凡缁忓寘鍚�鍦ㄧ幇鏈夊垎鍖哄�煎垪琛ㄤ腑鐨勪换鎰忓�笺�傚�傛灉璇曞浘杩欐牱鍋氾紝灏嗕細瀵艰嚧閿欒��锛�

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

閿欒��1465 (HY000): 鍦�LIST鍒嗗尯涓�锛屽悓涓�涓�甯告暟鐨勫�氭�″畾涔�

鍥犱负甯︽湁鏁版嵁鍒楀��12鐨勪换浣曡�岄兘宸茬粡鍒嗛厤缁欎簡鍒嗗尯p1锛屾墍浠ヤ笉鑳藉湪琛�tt涓婂啀鍒涘缓涓�涓�鍏跺�煎垪琛ㄥ寘鎷�12鐨勬柊鍒嗗尯銆備负浜嗗疄鐜拌繖涓�鐐癸紝鍙�浠ュ厛鍒犻櫎鍒嗗尯p1锛屾坊鍔犲垎鍖�np锛岀劧鍚庝娇鐢ㄤ慨姝ｅ悗鐨勫畾涔夋坊鍔犱竴涓�鏂扮殑鍒嗗尯p1銆備絾鏄�锛屾�ｅ�傛垜浠�鍓嶉潰璁ㄨ�鸿繃鐨勶紝杩欏皢瀵艰嚧淇濆瓨鍦ㄥ垎鍖�p1涓�鐨勬墍鏈夋暟鎹�涓㈠け鈥斺�旇�岃繖寰�寰�骞朵笉鏄�浣犳墍鐪熸�ｆ兂瑕佸仛鐨勩�傚彟澶栦竴绉嶈В鍐虫柟娉曞彲鑳芥槸锛屽缓绔嬩竴涓�甯︽湁鏂板垎鍖虹殑琛ㄧ殑鍓�鏈�锛岀劧鍚庝娇鐢ㄢ��CREATE TABLE ... SELECT ...鈥濇妸鏁版嵁鎷疯礉鍒拌�ユ柊琛ㄤ腑锛岀劧鍚庡垹闄ゆ棫琛�锛岄噸鏂板懡鍚嶆柊琛�锛屼絾鏄�锛屽綋闇�瑕佸�勭悊澶ч噺鐨勬暟鎹�鏃讹紝杩欏彲鑳芥槸闈炲父鑰楁椂鐨勩�傚湪闇�瑕侀珮鍙�鐢ㄦ�х殑鍦哄悎锛岃繖涔熷彲鑳芥槸涓嶅彲琛岀殑銆�

骞歌繍鍦版槸锛�MySQL 鐨勫垎鍖哄疄鐜版彁渚涗簡鍦ㄤ笉涓㈠け鏁版嵁鐨勬潯浠朵笅閲嶆柊瀹氫箟鍒嗗尯鐨勬柟寮忋�傝�╂垜浠�棣栧厛鐪嬩袱涓�娑夊強鍒�RANGE鍒嗗尯鐨勭畝鍗曚緥瀛愩�傚洖鎯充竴涓嬬幇鍦ㄥ畾涔夊�備笅鐨勬垚鍛樿〃锛�

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骞村墠鎴愬憳鐨勬墍鏈夎�岀Щ鍏ュ埌涓�涓�鍒嗗紑鐨勫垎鍖轰腑銆傛�ｅ�傛垜浠�鍓嶉潰鐪嬪埌鐨勶紝涓嶈兘閫氳繃浣跨敤鈥�ALTER TABLE ... ADD PARTITION鈥濇潵瀹炵幇杩欎竴鐐广�備絾鏄�锛岃�佸疄鐜拌繖涓�鐐癸紝鍙�浠ヤ娇鐢�ALTER TABLE涓婄殑鍙﹀�栦竴涓�涓庡垎鍖烘湁鍏崇殑鎵╁睍锛屽叿浣撳疄鐜板�備笅锛�

ALTER TABLE members REORGANIZE PARTITION p0 INTO (

    PARTITION s0 VALUES LESS THAN (1960),

    PARTITION s1 VALUES LESS THAN (1970)

)锛�

瀹為檯涓婏紝杩欎釜鍛戒护鎶婂垎鍖�p0鍒嗘垚浜嗕袱涓�鏂扮殑鍒嗗尯s0鍜�s1銆傚悓鏃讹紝瀹冭繕鏍规嵁鍖呭惈鍦ㄤ袱涓�鈥�PARTITION ... VALUES ...鈥濆瓙鍙ヤ腑鐨勮�勫垯锛屾妸淇濆瓨鍦ㄥ垎鍖�p0涓�鐨勬暟鎹�绉诲叆鍒颁袱涓�鏂扮殑鍒嗗尯涓�锛屾墍浠ュ垎鍖�s0涓�鍙�鍖呭惈YEAR(dob)灏忎簬1960鐨勯偅浜涜�岋紝s1涓�鍖呭惈閭ｄ簺YEAR(dob)澶т簬鎴栫瓑浜�1960浣嗘槸灏忎簬1970鐨勮�屻��

涓�涓�REORGANIZE PARTITION璇�鍙ヤ篃鍙�浠ョ敤鏉ュ悎骞剁浉閭荤殑鍒嗗尯銆傚彲浠ヤ娇鐢ㄥ�備笅鐨勮��鍙ユ仮澶嶆垚鍛樿〃鍒板畠浠ュ墠鐨勫垎鍖猴細

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (

    PARTITION p0 VALUES LESS THAN (1970)

)锛�

浣跨敤鈥�REORGANIZE PARTITION鈥濇媶鍒嗘垨鍚堝苟鍒嗗尯锛屾病鏈夋暟鎹�涓㈠け銆傚湪鎵ц�屼笂闈㈢殑璇�鍙ヤ腑锛�MySQL 鎶婁繚瀛樺湪鍒嗗尯s0鍜�s1涓�鐨勬墍鏈夋暟鎹�閮界Щ鍒板垎鍖�p0涓�銆�

鈥淩EORGANIZE PARTITION鈥�鐨勫熀鏈�璇�娉曟槸锛�

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions)锛�

鍏朵腑锛�tbl_name 鏄�鍒嗗尯琛ㄧ殑鍚嶇О锛�partition_list 鏄�閫氳繃閫楀彿鍒嗗紑鐨勩�佷竴涓�鎴栧�氫釜灏嗚�佽��鏀瑰彉鐨勭幇鏈夊垎鍖虹殑鍒楄〃銆�partition_definitions 鏄�涓�涓�鏄�閫氳繃閫楀彿鍒嗗紑鐨勩�佹柊鍒嗗尯瀹氫箟鐨勫垪琛�锛屽畠閬靛惊涓庣敤鍦ㄢ��CREATE TABLE鈥�涓�鐨�partition_definitions 鐩稿悓鐨勮�勫垯 (璇峰弬瑙�13.1.5鑺傦紝鈥淐REATE TABLE璇�娉曗��)銆傚簲褰撴敞鎰忓埌锛屽湪鎶婂�氬皯涓�鍒嗗尯鍚堝苟鍒颁竴涓�鍒嗗尯鎴栨妸涓�涓�鍒嗗尯鎷嗗垎鎴愬�氬皯涓�鍒嗗尯鏂归潰锛屾病鏈夐檺鍒躲�備緥濡傦紝鍙�浠ラ噸鏂扮粍缁囨垚鍛樿〃鐨勫洓涓�鍒嗗尯鎴愪袱涓�鍒嗗尯锛屽叿浣撳疄鐜板�備笅锛�

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

    PARTITION m0 VALUES LESS THAN (1980),

    PARTITION m1 VALUES LESS THAN (2000)

)锛�

鍚屾牱锛屽�逛簬鎸�LIST鍒嗗尯鐨勮〃锛屼篃鍙�浠ヤ娇鐢�REORGANIZE PARTITION銆傝�╂垜浠�鍥炲埌閭ｄ釜闂�棰橈紝鍗冲�炲姞涓�涓�鏂扮殑鍒嗗尯鍒板凡缁忔寜鐓�LIST鍒嗗尯鐨勮〃tt涓�锛屼絾鏄�鍥犱负璇ユ柊鍒嗗尯鏈変竴涓�鍊煎凡缁忓瓨鍦ㄤ簬鐜版湁鍒嗗尯鐨勫�煎垪琛ㄤ腑锛屾坊鍔犳柊鐨勫垎鍖哄け璐ャ�傛垜浠�鍙�浠ラ�氳繃鍏�娣诲姞鍙�鍖呭惈闈炲啿绐佸�肩殑鍒嗗尯锛岀劧鍚庨噸鏂扮粍缁囪�ユ柊鍒嗗尯鍜岀幇鏈夌殑閭ｄ釜鍒嗗尯锛屼互渚夸繚瀛樺湪鐜版湁鐨勯偅涓�鍒嗗尯涓�鐨勫�肩幇鍦ㄧЩ鍒颁簡鏂扮殑鍒嗗尯涓�锛屾潵澶勭悊杩欎釜闂�棰橈細

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)

)锛�

褰撲娇鐢ㄢ��ALTER TABLE ... REORGANIZE PARTITION鈥濇潵瀵瑰凡缁忔寜鐓�RANGE鍜�LIST鍒嗗尯琛ㄨ繘琛岄噸鏂板垎鍖烘椂锛屼笅闈㈡槸涓�浜涜�佽�颁綇鐨勫叧閿�鐐癸細

路         鐢ㄦ潵纭�瀹氭柊鍒嗗尯妯″紡鐨�PARTITION瀛愬彞浣跨敤涓庣敤鍦�CREATE TABLE涓�纭�瀹氬垎鍖烘ā寮忕殑PARTITION瀛愬彞鐩稿悓鐨勮�勫垯銆�

鏈�閲嶈�佺殑鏄�锛屽簲璇ヨ�颁綇锛氭柊鍒嗗尯妯″紡涓嶈兘鏈変换浣曢噸鍙犵殑鍖洪棿锛堥�傜敤浜庢寜鐓�RANGE鍒嗗尯鐨勮〃锛夋垨鍊奸泦鍚堬紙閫傜敤浜庨噸鏂扮粍缁囨寜鐓�LIST鍒嗗尯鐨勮〃锛夈��

路         partition_definitions 鍒楄〃涓�鍒嗗尯鐨勫悎闆嗗簲璇ヤ笌鍦�partition_list 涓�鍛藉悕鍒嗗尯鐨勫悎闆嗗崰鏈夌浉鍚岀殑鍖洪棿鎴栧�奸泦鍚堛��

渚嬪�傦紝鍦ㄦ湰鑺備腑鐢ㄤ綔渚嬪瓙鐨勬垚鍛樿〃涓�锛屽垎鍖�p1鍜�p2鎬诲叡瑕嗙洊浜�1980鍒�1999鐨勮繖浜涘勾銆傚洜姝わ紝瀵硅繖涓や釜鍒嗗尯鐨勯噸鏂扮粍缁囬兘搴旇�ヨ�嗙洊鐩稿悓鑼冨洿鐨勫勾浠姐��

路         瀵逛簬鎸夌収RANGE鍒嗗尯鐨勮〃锛屽彧鑳介噸鏂扮粍缁囩浉閭荤殑鍒嗗尯锛涗笉鑳借烦杩�RANGE鍒嗗尯銆�

渚嬪�傦紝涓嶈兘浣跨敤浠モ��ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ...鈥濆紑澶寸殑璇�鍙ワ紝鏉ラ噸鏂扮粍缁囨湰鑺備腑鐢ㄤ綔渚嬪瓙鐨勬垚鍛樿〃銆傚洜涓猴紝p0瑕嗙洊浜�1970骞翠互鍓嶇殑骞翠唤锛岃��p2瑕嗙洊浜嗕粠1990鍒�1999锛堝寘鎷�1990鍜�1999锛変箣闂寸殑骞翠唤锛屽洜鑰岃繖涓や釜鍒嗗尯涓嶆槸鐩搁偦鐨勫垎鍖恒��

路         涓嶈兘浣跨敤REORGANIZE PARTITION鏉ユ敼鍙樿〃鐨勫垎鍖虹被鍨嬶紱涔熷氨鏄�璇达紝渚嬪�傦紝涓嶈兘鎶�RANGE鍒嗗尯鍙樹负HASH鍒嗗尯锛屽弽涔嬩害鐒躲�備篃涓嶈兘浣跨敤璇ュ懡浠ゆ潵鏀瑰彉鍒嗗尯琛ㄨ揪寮忔垨鍒椼�傚�傛灉鎯冲湪涓嶅垹闄ゅ拰閲嶅缓琛ㄧ殑鏉′欢涓嬪疄鐜拌繖涓や釜浠诲姟锛屽彲浠ヤ娇鐢�鈥淎LTER TABLE ... PARTITION BY ....鈥濓紝渚嬪�傦細

路                ALTER TABLE members 

路                    PARTITION BY HASH(YEAR(dob))

路                    PARTITIONS 8锛�

娉ㄩ噴锛�鍦�MySQL 5.1鍙戝竷鍓嶇殑鐗堟湰涓�锛屸��ALTER TABLE ... PARTITION BY ...鈥�杩樻病鏈夊疄鐜般�備綔涓烘浛浠ｏ紝瑕佷箞浣跨敤鍏堝垹闄よ〃锛岀劧鍚庝娇鐢ㄦ兂瑕佺殑鍒嗗尯閲嶅缓琛�锛屾垨鑰�鈥斺��濡傛灉闇�瑕佷繚鐣欏凡缁忓瓨鍌ㄥ湪琛ㄤ腑鐨勬暟鎹�鈥斺��鍙�浠ヤ娇鐢�鈥淐REATE TABLE ... SELECT ...鈥�鏉ュ垱寤烘柊鐨勮〃锛岀劧鍚庝粠鏃ц〃涓�鎶婃暟鎹�鎷疯礉鍒版柊琛ㄤ腑锛屽啀鍒犻櫎鏃ц〃锛屽�傛湁蹇呰�侊紝鏈�鍚庨噸鏂板懡鍚嶆柊琛ㄣ��

鍦ㄦ敼鍙樺垎鍖鸿�剧疆鏂归潰锛屾寜鐓�HASH鍒嗗尯鎴�KEY鍒嗗尯鐨勮〃褰兼�ら潪甯哥浉浼硷紝浣嗘槸瀹冧滑鍙堜笌鎸夌収RANGE鎴�LIST鍒嗗尯鐨勮〃鍦ㄥ緢澶氭柟闈㈡湁宸�鍒�銆傛墍浠ワ紝鏈�鑺傚彧璁ㄨ�烘寜鐓�HASH鎴�KEY鍒嗗尯琛ㄧ殑淇�鏀广�傚叧浜庢坊鍔犲拰鍒犻櫎鎸夌収RANGE鎴�LIST杩涜�屽垎鍖虹殑琛ㄧ殑鍒嗗尯鐨勮�ㄨ�猴紝鍙傝��18.3.1鑺傦紝鈥淩ANGE鍜孡IST鍒嗗尯鐨勭�＄悊鈥�銆�

涓嶈兘浣跨敤涓庝粠鎸夌収RANGE鎴�LIST鍒嗗尯鐨勮〃涓�鍒犻櫎鍒嗗尯鐩稿悓鐨勬柟寮忥紝鏉ヤ粠HASH鎴�KEY鍒嗗尯鐨勮〃涓�鍒犻櫎鍒嗗尯銆備絾鏄�锛屽彲浠ヤ娇鐢ㄢ��ALTER TABLE ... COALESCE PARTITION鈥濆懡浠ゆ潵鍚堝苟HASH鎴�KEY鍒嗗尯銆備緥濡傦紝鍋囧畾鏈変竴涓�鍖呭惈椤惧��淇℃伅鏁版嵁鐨勮〃锛屽畠琚�鍒嗘垚浜�12涓�鍒嗗尯銆傝�ラ【瀹㈣〃鐨勫畾涔夊�備笅锛�

CREATE TABLE clients (

    id INT,

    fname VARCHAR(30),

    lname VARCHAR(30),

    signed DATE

)

PARTITION BY HASH( MONTH(signed) )

PARTITIONS 12锛�

瑕佸噺灏戝垎鍖虹殑鏁伴噺浠�12鍒�6锛屾墽琛屼笅闈㈢殑ALTER TABLE鍛戒护锛�

mysql> ALTER TABLE clients COALESCE PARTITION 6锛�

Query OK, 0 rows affected (0.02 sec)

瀵逛簬鎸夌収HASH锛�KEY锛�LINEAR HASH锛屾垨LINEAR KEY鍒嗗尯鐨勮〃锛� COALESCE鑳借捣鍒板悓鏍风殑浣滅敤銆備笅闈㈡槸涓�涓�绫讳技浜庡墠闈�渚嬪瓙鐨勫彟澶栦竴涓�渚嬪瓙锛屽畠浠�鐨勫尯鍒�鍙�鏄�鍦ㄤ簬琛ㄦ槸鎸夌収LINEAR KEY 杩涜�屽垎鍖猴細

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涓嶈兘鐢ㄦ潵澧炲姞鍒嗗尯鐨勬暟閲忥紝濡傛灉浣犲皾璇曡繖涔堝仛锛岀粨鏋滀細鍑虹幇绫讳技浜庝笅闈㈢殑閿欒��锛�

mysql> ALTER TABLE clients COALESCE PARTITION 18;

閿欒��1478 (HY000): 涓嶈兘绉诲姩鎵�鏈夊垎鍖猴紝浣跨敤DROP TABLE浠ｆ浛

瑕佸�炲姞椤惧�㈣〃鐨勫垎鍖烘暟閲忎粠12鍒�18锛屼娇鐢ㄢ��ALTER TABLE ... ADD PARTITION鈥�,鍏蜂綋濡備笅锛�

ALTER TABLE clients ADD PARTITION PARTITIONS 18锛�

娉ㄩ噴锛�鈥�ALTER TABLE ... REORGANIZE PARTITION鈥�涓嶈兘鐢ㄤ簬鎸夌収HASH鎴�HASH鍒嗗尯鐨勮〃銆�

娉ㄩ噴锛�瀹為檯涓婏紝鏈�鑺傝�ㄨ�虹殑鍛戒护杩樻病鏈夊湪MySQL 5.1涓�瀹炵幇锛� 鍦ㄨ繖閲屾彁鍑虹殑鐩�鐨勶紝鏄�涓轰簡鍦�5.1鐗堟姇浜у墠鐨勫紑鍙戝懆鏈熸湡闂达紝寮曞嚭鏉ヨ嚜鐢ㄦ埛娴嬭瘯璇ヨ蒋浠剁殑鍙嶉�堟剰瑙併��(鎹㈠彞璇濊�达紝灏辨槸鈥滆�蜂笉瑕佸弽棣堣繖鏍风殑缂洪櫡锛岃�磋繖浜涘懡浠や笉璧蜂綔鐢ㄢ�濓級銆�闅忕潃MySQL5.1鐗堝紑鍙戠殑缁х画锛岃繖浜涗俊鎭�寰堟湁鍙�鑳藉彂鐢熷彉鍖栥�傞殢鐫�鍒嗗尯鍔熻兘鐨勫疄鐜板拰鎻愰珮锛屾垜浠�灏嗘洿鏂版湰鑺傜殑鍐呭�广��

MySQL 5.1涓�鍙�浠ユ墽琛岃�稿�氬垎鍖虹淮鎶ょ殑浠诲姟銆傚�逛簬鍒嗗尯琛�锛�MySQL涓嶆敮鎸佸懡浠�CHECK TABLE锛�OPTIMIZE TABLE锛�ANALYZE TABLE锛屾垨REPAIR TABLE銆備綔涓烘浛浠ｏ紝鍙�浠ヤ娇鐢�ALTER TABLE 鐨勮�稿�氭墿灞曟潵鍦ㄤ竴涓�鎴栧�氫釜鍒嗗尯涓婄洿鎺ュ湴鎵ц�岃繖浜涙搷浣滐紝濡備笅闈㈠垪鍑虹殑閭ｆ牱锛�

路         閲嶅缓鍒嗗尯: 杩欏拰鍏堝垹闄や繚瀛樺湪鍒嗗尯涓�鐨勬墍鏈夎�板綍锛岀劧鍚庨噸鏂版彃鍏ュ畠浠�锛屽叿鏈夊悓鏍风殑鏁堟灉銆傚畠鍙�鐢ㄤ簬鏁寸悊鍒嗗尯纰庣墖銆�

绀轰緥锛�

ALTER TABLE t1 REBUILD PARTITION (p0, p1)锛�

路         浼樺寲鍒嗗尯锛�濡傛灉浠庡垎鍖轰腑鍒犻櫎浜嗗ぇ閲忕殑琛岋紝鎴栬�呭�逛竴涓�甯︽湁鍙�鍙橀暱搴︾殑琛岋紙涔熷氨鏄�璇达紝鏈�VARCHAR锛�BLOB锛屾垨TEXT绫诲瀷鐨勫垪锛変綔浜嗚�稿�氫慨鏀癸紝鍙�浠ヤ娇鐢ㄢ��ALTER TABLE ... OPTIMIZE PARTITION鈥�鏉ユ敹鍥炴病鏈変娇鐢ㄧ殑绌洪棿锛屽苟鏁寸悊鍒嗗尯鏁版嵁鏂囦欢鐨�纰庣墖銆�

绀轰緥锛�

ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1)锛�

鍦ㄤ竴涓�缁欏畾鐨勫垎鍖鸿〃涓婁娇鐢ㄢ��OPTIMIZE PARTITION鈥�绛夊悓浜庡湪閭ｄ釜鍒嗗尯涓婅繍琛�CHECK PARTITION锛�ANALYZE PARTITION锛屽拰REPAIR PARTITION銆�

路         鍒嗘瀽鍒嗗尯锛�璇诲彇骞朵繚瀛樺垎鍖虹殑閿�鍒嗗竷銆�

绀轰緥锛�

ALTER TABLE t1 ANALYZE PARTITION (p3)锛�

路         淇�琛ュ垎鍖猴細 淇�琛ヨ��鐮村潖鐨勫垎鍖恒��

绀轰緥锛�

ALTER TABLE t1 REPAIR PARTITION (p0,p1);

路         妫�鏌ュ垎鍖猴細 鍙�浠ヤ娇鐢ㄥ嚑涔庝笌瀵归潪鍒嗗尯琛ㄤ娇鐢�CHECK TABLE 鐩稿悓鐨勬柟寮忔��鏌ュ垎鍖恒��

绀轰緥锛�

ALTER TABLE trb3 CHECK PARTITION (p1)锛�

杩欎釜鍛戒护鍙�浠ュ憡璇変綘琛�t1鐨勫垎鍖�p1涓�鐨勬暟鎹�鎴栫储寮曟槸鍚﹀凡缁忚��鐮村潖銆傚�傛灉鍙戠敓浜嗚繖绉嶆儏鍐碉紝浣跨敤鈥�ALTER TABLE ... REPAIR PARTITION鈥�鏉ヤ慨琛ヨ�ュ垎鍖恒��

杩樺彲浠ヤ娇鐢�mysqlcheck鎴�myisamchk 搴旂敤绋嬪簭锛屽湪瀵硅〃杩涜�屽垎鍖烘椂鎵�浜х敓鐨勩�佸崟鐙�鐨�MYI鏂囦欢涓�杩涜�屾搷浣滐紝鏉ュ畬鎴愯繖浜涗换鍔°�傝�峰弬瑙�8.7鑺傦紝鈥渕ysqlcheck锛氳〃缁存姢鍜岀淮淇�绋嬪簭鈥�銆傦紙鍦�pre-alpha缂栫爜涓�锛岃繖涓�鍔熻兘宸茬粡鍙�浠ヤ娇鐢�锛夈��

鏈�鑺傝�ㄨ�鸿幏鍙栧叧浜庣幇鏈夊垎鍖虹殑淇℃伅銆傝繖涓�鍔熻兘浠嶇劧澶勪簬璁″垝闃舵�碉紝鎵�浠ョ幇闃舵�靛湪杩欓噷鎻忚堪鐨勶紝瀹為檯涓婃槸鎴戜滑鎯宠�佸湪MySQL 5.1涓�瀹炵幇鐨勪竴涓�姒傝�傘��

濡傚湪鏈�绔犱腑鍒�澶勮�ㄨ�虹殑涓�鏍凤紝鍦�SHOW CREATE TABLE鐨勮緭鍑轰腑鍖呭惈浜嗙敤浜庡垱寤哄垎鍖鸿〃鐨�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)

娉ㄩ噴锛�褰撳墠锛屽�逛簬鎸�HASH鎴�KEY鍒嗗尯鐨勮〃锛�PARTITIONS瀛愬彞骞朵笉鏄剧ず銆� (Bug #14327)

SHOW TABLE STATUS鐢ㄤ簬鍒嗗尯琛�锛屽畠鐨勮緭鍑轰笌鐢ㄤ簬闈炲垎鍖鸿〃鐨勮緭鍑虹浉鍚岋紝闄や簡寮曟搸锛�Engine锛夊垪鎬绘槸鍖呭惈'PARTITION'鍊笺��(鍏充簬杩欎釜鍛戒护鐨勬洿澶氫俊鎭�锛屽弬瑙�13.5.4.18鑺傦紝鈥淪HOW TABLE STATUS璇�娉曗��锛�銆�瑕佽幏鍙栧崟涓�鍒嗗尯鐨勭姸鎬佷俊鎭�锛屾垜浠�璁″垝瀹炵幇涓�涓�SHOW PARTITION STATUS鍛戒护锛�璇峰弬瑙佷笅闈�)銆�

璁″垝鐢ㄤ簬鍒嗗尯琛ㄧ殑銆佷袱涓�闄勫姞鐨�SHOW鍛戒护鏄�锛�

路         SHOW PARTITIONS

杩欎釜鍛戒护棰勬湡鍏跺姛鑳界被浼间簬SHOW TABLES鍜�SHOW DATABASES锛岄櫎浜嗚�ュ懡浠ゅ皢鍒楀嚭鐨勬槸鍒嗗尯鑰屼笉鏄�琛ㄦ垨鏁版嵁搴撱�傝繖涓�鍛戒护鐨勮緭鍑哄彲鑳藉寘鍚�鍗曚釜绉颁负Partitions_in_tbl_name 鐨勫垪锛屽叾涓�tbl_name 鏄�鍒嗗尯琛ㄧ殑鍚嶅瓧銆傚�逛簬SHOW TABLES鍛戒护鑰岃█锛屽�傛灉涓�鏃﹂�夋嫨浜嗕竴涓�鏁版嵁搴擄紝闅忓悗璇ユ暟鎹�搴撳皢浣滀负SHOW TABLES鍛戒护鐨勯粯璁ゆ暟鎹�搴撱�備絾鏄�鐢变簬SHOW PARTITIONS鍛戒护涓嶅彲鑳界敤杩欐牱鐨勬柟寮忔潵鈥�閫夋嫨鈥�涓�涓�琛�锛屽畠寰堝彲鑳介渶瑕佷娇鐢�FROM瀛愬彞锛屼互渚�MySQL鐭ラ亾瑕佹樉绀虹殑鏄�鍝�涓�琛ㄧ殑鍒嗗尯淇℃伅銆�

路         SHOW PARTITION STATUS

杩欎釜鍛戒护灏嗘彁渚涘叧浜庝竴涓�鎴栧�氫釜鍒嗗尯鐨勮�︾粏鐘舵�佷俊鎭�銆傚畠鐨勮緭鍑哄緢鍙�鑳藉寘鍚�鏈変笌SHOW TABLE STATUS 鐨勮緭鍑虹浉鍚屾垨绫讳技鐨勫垪锛屾�ゅ�栵紝杩樺寘鎷�鏄剧ず鐢ㄤ簬鍒嗗尯鐨勬暟鎹�鍜岀储寮曡矾寰勭殑闄勫姞鍒椼�傝繖涓�鍛戒护鍙�鑳芥敮鎸�LIKE鍜�FROM瀛愬彞锛岃繖鏍蜂娇寰楅�氳繃鍚嶅瓧鑾峰緱鍏充簬涓�涓�缁欏畾鍒嗗尯鐨勪俊鎭�锛屾垨鑰呰幏寰楀叧浜庡睘浜庢寚瀹氳〃鎴栨暟鎹�搴撶殑鍒嗗尯鐨勪俊鎭�锛屾垚涓哄彲鑳姐��

鎵╁睍INFORMATION_SCHEMA 鏁版嵁搴撶殑璁″垝涔熷湪杩涜�屼腑锛屼互渚挎彁渚涘叧浜庡垎鍖鸿〃鍜屽垎鍖虹殑淇℃伅銆傝繖涓�璁″垝褰撳墠杩樺�勪竴涓�鍦ㄩ潪甯告棭鐨勯樁娈碉紱闅忕潃琛ュ厖鐨勪俊鎭�鍙樺緱鍙�鐢�锛屼互鍙婁换浣曟柊鐨勩�佷笌鍒嗗尯鏈夊叧鐨�INFORMATION_SCHEMA鎵╁睍寰椾互瀹炵幇锛屾垜浠�灏嗘洿鏂版墜鍐岀浉鍏抽儴鍒嗙殑鍐呭�广��