站内搜索: 请输入搜索关键词
当前页面: 在线文档首页 > MySQL 5.1参考手册中文版

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

绗18绔狅細鍒嗗尯

鏈珷璁ㄨMySQL 5.1.涓疄鐜扮殑鍒嗗尯銆傚叧浜庡垎鍖哄拰鍒嗗尯姒傚康鐨勪粙缁嶅彲浠ュ湪18.1鑺傦紝鈥淢ySQL涓殑鍒嗗尯姒傝堪鈥涓壘鍒般MySQL 5.1 鏀寔鍝嚑绉嶇被鍨嬬殑鍒嗗尯锛屽湪18.2鑺傦紝鈥滃垎鍖虹被鍨嬧 涓璁恒傚叧浜庡瓙鍒嗗尯鍦18.2.5鑺傦紝鈥滃瓙鍒嗗尯鈥 涓璁恒傜幇鏈夊垎鍖鸿〃涓垎鍖虹殑澧炲姞銆佸垹闄ゅ拰淇敼鐨勬柟娉曞湪18.3鑺傦紝鈥滃垎鍖虹鐞嗏 涓粙缁嶃 鍜屽垎鍖鸿〃涓鍚屼娇鐢ㄧ殑琛ㄧ淮鎶ゅ懡浠ゅ湪18.3.3鑺傦紝鈥滃垎鍖虹淮鎶も 涓粙缁嶃

璇锋敞鎰MySQL 5.1涓殑鍒嗗尯瀹炵幇浠嶇劧寰堟柊锛pre-alpha鍝佽川锛夛紝姝ゆ椂杩樹笉鏄彲鐢熶骇鐨勶紙not production-ready锛夈 鍚屾牱锛岃澶氫篃閫傜敤浜庢湰绔狅細鍦ㄨ繖閲屾弿杩扮殑涓浜涘姛鑳借繕娌℃湁瀹為檯涓婂疄鐜(鍒嗗尯缁存姢鍜岄噸鏂板垎鍖哄懡浠わ級锛屽叾浠栫殑鍙兘杩樻病鏈夊畬鍏ㄥ鎵鎻忚堪鐨勯偅鏍峰疄鐜(渚嬪, 鐢ㄤ簬鍒嗗尯鐨勬暟鎹洰褰曪紙DATA DIRECTORY锛夊拰绱㈠紩鐩綍锛INDEX DIRECTORY锛夐夐」鍙楀埌Bug #13520) 涓嶅埄鐨勫奖鍝). 鎴戜滑宸茬粡璁炬硶鍦ㄦ湰绔犱腑鏍囧嚭杩欎簺宸紓銆傚湪鎻愬嚭缂洪櫡鎶ュ憡鍓嶏紝鎴戜滑榧撳姳鍙傝冧笅闈㈢殑涓浜涜祫婧愶細

  • MySQL 鍒嗗尯璁哄潧

    杩欐槸涓涓负瀵MySQL鍒嗗尯鎶鏈劅鍏磋叮鎴栫敤MySQL鍒嗗尯鎶鏈仛璇曢獙鎻愪緵鐨勫畼鏂硅璁鸿鍧涖傛潵鑷MySQL 鐨勫紑鍙戣呭拰鍏朵粬鐨勪汉锛屼細鍦ㄤ笂闈㈠彂琛ㄥ拰鏇存柊鏈夊叧鐨勬潗鏂欍傚畠鐢卞垎鍖哄紑鍙戝拰鏂囩尞鍥㈤槦鐨勬垚鍛樿礋璐g洃鎺с

  • 鍒嗗尯缂洪櫡鎶ュ憡

    宸茬粡褰掓。鍦ㄧ己闄风郴缁熶腑鐨勩佹墍鏈夊垎鍖虹己闄风殑涓涓垪琛紝鑰屾棤璁鸿繖浜涚己闄风殑骞撮檺銆佷弗閲嶆ф垨褰撳墠鐨勭姸鎬佸浣曘傛牴鎹澶氳鍒欏彲浠ュ杩欎簺缂洪櫡杩涜绛涢夛紝鎴栬呭彲浠ヤ粠MySQL缂洪櫡绯荤粺涓婚〉寮濮嬶紝鐒跺悗鏌ユ壘浣犵壒鍒劅鍏磋叮鐨勭己闄枫

  • Mikael Ronstr枚m's Blog

    MySQL鍒嗗尯浣撶郴缁撴瀯鍜岄鍏堢殑寮鍙戣Mikael Ronstr枚m 缁忓父鍦ㄨ繖閲岃创鍏充簬浠栫爺绌MySQL 鍒嗗尯鍜MySQL绨囩殑鏂囩珷銆

  • PlanetMySQL

    涓涓MySQL 鏂伴椈缃戠珯锛屽畠浠ユ眹闆MySQL鐩稿叧鐨勭綉瑾屼负鐗圭偣锛岄偅浜涗娇鐢ㄦ垜鐨MySQL鐨勪汉搴旇瀵规鏈夊叴瓒c傛垜浠紦鍔辨煡鐪嬮偅浜涚爺绌MySQL鍒嗗尯鐨勪汉鐨勭綉瑾岄摼鎺ワ紝鎴栬呮妸浣犺嚜宸辩殑缃戣獙鍔犲埌杩欎簺鏂伴椈鎶ラ亾涓

MySQL 5.1鐨勪簩杩涘埗鐗堟湰鐩墠杩樹笉鍙敤锛涗絾鏄紝鍙互浠BitKeeper鐭ヨ瘑搴撲腑鑾峰緱婧愮爜銆傝婵娲诲垎鍖猴紝闇瑕佷娇鐢--with-鍒嗗尯閫夐」缂栬瘧鏈嶅姟鍣ㄣ傚叧浜庡缓绔MySQL 鐨勬洿澶氫俊鎭紝璇峰弬瑙2.8鑺傦紝鈥滀娇鐢ㄦ簮鐮佸垎鍙戠増瀹夎MySQL鈥銆傚鏋滃湪缂栬瘧涓涓縺娲诲垎鍖虹殑MySQL 5.1鍒涘缓涓鍒伴棶棰橈紝鍙互鍦MySQL鍒嗗尯璁哄潧涓煡鎵捐В鍐冲姙娉曪紝濡傛灉鍦ㄨ鍧涗腑宸茬粡璐村嚭鐨勬枃绔犱腑娌℃湁鎵惧埌闂鐨勮В鍐冲姙娉曪紝鍙互鍦ㄤ笂闈㈠鎵惧府鍔┿

18.1.聽MySQL涓殑鍒嗗尯姒傝堪

鏈妭鎻愪緵浜嗗叧浜MySQL 5.1.鍒嗗尯鍦ㄦ蹇典笂鐨勬杩般

SQL鏍囧噯鍦ㄦ暟鎹瓨鍌ㄧ殑鐗╃悊鏂归潰娌℃湁鎻愪緵澶鐨勬寚鍗椼SQL璇█鐨勪娇鐢ㄧ嫭绔嬩簬瀹冩墍浣跨敤鐨勪换浣曟暟鎹粨鏋勬垨鍥捐〃銆佽〃銆佽鎴栧垪涓嬬殑浠嬭川銆備絾鏄紝澶ч儴鍒嗛珮绾ф暟鎹簱绠$悊绯荤粺宸茬粡寮鍙戜簡涓浜涙牴鎹枃浠剁郴缁熴佺‖浠舵垨鑰呰繖涓よ呮潵纭畾灏嗚鐢ㄤ簬瀛樺偍鐗瑰畾鏁版嵁鍧楃墿鐞嗕綅缃殑鏂规硶銆傚湪MySQL锛孖nnoDB瀛樺偍寮曟搸闀挎湡鏀寔琛ㄧ┖闂寸殑姒傚康锛屽苟涓MySQL鏈嶅姟鍣ㄧ敋鑷冲湪鍒嗗尯寮曞叆涔嬪墠锛屽氨鑳介厤缃负瀛樺偍涓嶅悓鐨勬暟鎹簱浣跨敤涓嶅悓鐨勭墿鐞嗚矾寰(鍏充簬濡備綍閰嶇疆鐨勮В閲婏紝璇峰弬瑙7.6.1鑺傦紝鈥滀娇鐢ㄧ鍙烽摼鎺モ)

鍒嗗尯鍙堟妸杩欎釜姒傚康鎺ㄨ繘浜嗕竴姝ワ紝瀹冨厑璁告牴鎹彲浠ヨ缃负浠绘剰澶у皬鐨勮鍒欙紝璺ㄦ枃浠剁郴缁熷垎閰嶅崟涓〃鐨勫涓儴鍒嗐傚疄闄呬笂锛岃〃鐨勪笉鍚岄儴鍒嗗湪涓嶅悓鐨勪綅缃瀛樺偍涓哄崟鐙殑琛ㄣ傜敤鎴锋墍閫夋嫨鐨勩佸疄鐜版暟鎹垎鍓茬殑瑙勫垯琚О涓鍒嗗尯鍑芥暟杩欏湪MySQL涓畠鍙互鏄ā鏁帮紝鎴栬呮槸绠鍗曠殑鍖归厤涓涓繛缁殑鏁板煎尯闂存垨鏁板煎垪琛紝鎴栬呮槸涓涓唴閮HASH鍑芥暟锛屾垨涓涓嚎鎬HASH鍑芥暟銆傚嚱鏁版牴鎹敤鎴锋寚瀹氱殑鍒嗗尯绫诲瀷鏉ラ夋嫨锛屾妸鐢ㄦ埛鎻愪緵鐨勮〃杈惧紡鐨勫间綔涓哄弬鏁般傝琛ㄨ揪寮忓彲浠ユ槸涓涓暣鏁板垪鍊硷紝鎴栦竴涓綔鐢ㄥ湪涓涓垨澶氫釜鍒楀间笂骞惰繑鍥炰竴涓暣鏁扮殑鍑芥暟銆傝繖涓〃杈惧紡鐨勫间紶閫掔粰鍒嗗尯鍑芥暟锛屽垎鍖哄嚱鏁拌繑鍥炰竴涓〃绀洪偅涓壒瀹氳褰曞簲璇ヤ繚瀛樺湪鍝釜鍒嗗尯鐨勫簭鍙枫傝繖涓嚱鏁颁笉鑳芥槸甯告暟锛屼篃涓嶈兘鏄换鎰忔暟銆傚畠涓嶈兘鍖呭惈浠讳綍鏌ヨ锛屼絾鏄疄闄呬笂鍙互浣跨敤MySQL 涓换浣曞彲鐢ㄧ殑SQL琛ㄨ揪寮忥紝鍙璇ヨ〃杈惧紡杩斿洖涓涓皬浜MAXVALUE锛堟渶澶у彲鑳界殑姝f暣鏁帮級鐨勬鏁板笺傚垎鍖哄嚱鏁扮殑渚嬪瓙鍙互鍦ㄦ湰绔犲悗闈㈠叧浜庡垎鍖虹被鍨嬬殑璁ㄨ涓壘鍒 (璇峰弬瑙18.2鑺傦紝鈥滃垎鍖虹被鍨嬧 )锛涔熷彲鍦13.1.5鑺傦紝鈥淐REATE TABLE璇硶鈥鐨勫垎鍖鸿娉曟弿杩颁腑鎵惧埌銆

褰撲簩杩涘埗鐮佸彉鎴愬彲鐢ㄦ椂锛堜篃灏辨槸璇达紝5.1 -max 浜岃繘鍒剁爜灏嗛氳繃--with-partition 寤虹珛锛夛紝鍒嗗尯鏀寔灏卞皢鍖呭惈鍦MySQL 5.1-max 鐗堟湰涓傚鏋MySQL浜岃繘鍒剁爜鏄娇鐢ㄥ垎鍖烘敮鎸佸缓绔嬬殑锛岄偅涔堟縺娲诲畠涓嶉渶瑕佷换浣曞叾浠栫殑涓滆タ (渚嬪锛屽湪my.cnf 鏂囦欢涓紝涓嶉渶瑕佺壒娈婄殑鏉$洰)銆傚彲浠ラ氳繃浣跨敤SHOW VARIABLES鍛戒护鏉ョ‘瀹MySQL鏄惁鏀寔鍒嗗尯锛屼緥濡傦細

mysql> SHOW VARIABLES LIKE '%partition%';
 
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| have_partition_engine | YES   |
+-----------------------+-------+
1 row in set (0.00 sec)

鍦ㄥ涓婂垪鍑虹殑涓涓纭殑SHOW VARIABLES 鍛戒护鎵浜х敓鐨勮緭鍑轰腑锛屽鏋滄病鏈夌湅鍒板彉閲have_partition_engine鐨勫间负YES锛岄偅涔MySQL鐨勭増鏈氨涓嶆敮鎸佸垎鍖恒傦紙娉ㄦ剰锛氬湪鏄剧ず浠讳綍鏈夊叧鍒嗗尯鏀寔淇℃伅鐨勫懡浠SHOW ENGINES鐨勮緭鍑轰腑锛屼笉浼氱粰鍑轰换浣曚俊鎭紱蹇呴』浣跨敤SHOW VARIABLES鍛戒护鏉ュ仛鍑鸿繖涓垽鏂級

瀵逛簬鍒涘缓浜嗗垎鍖虹殑琛紝鍙互浣跨敤浣犵殑MySQL 鏈嶅姟鍣ㄦ墍鏀寔鐨勪换浣曞瓨鍌ㄥ紩鎿庯紱MySQL 鍒嗗尯寮曟搸鍦ㄤ竴涓崟鐙殑灞備腑杩愯锛屽苟涓斿彲浠ュ拰浠讳綍杩欐牱鐨勫眰杩涜鐩镐簰浣滅敤銆傚湪MySQL 5.1鐗堜腑锛屽悓涓涓垎鍖鸿〃鐨勬墍鏈夊垎鍖哄繀椤讳娇鐢ㄥ悓涓涓瓨鍌ㄥ紩鎿庯紱渚嬪锛屼笉鑳藉涓涓垎鍖轰娇鐢MyISAM锛岃屽鍙︿竴涓娇鐢InnoDB銆備絾鏄紝杩欏苟涓嶅Θ纰嶅湪鍚屼竴涓 MySQL 鏈嶅姟鍣ㄤ腑锛岀敋鑷冲湪鍚屼竴涓暟鎹簱涓紝瀵逛簬涓嶅悓鐨勫垎鍖鸿〃浣跨敤涓嶅悓鐨勫瓨鍌ㄥ紩鎿庛

瑕佷负鏌愪釜鍒嗗尯琛ㄩ厤缃竴涓笓闂ㄧ殑瀛樺偍寮曟搸锛屽繀椤讳笖鍙兘浣跨敤[STORAGE] ENGINE 閫夐」锛岃繖濡傚悓涓洪潪鍒嗗尯琛ㄩ厤缃瓨鍌ㄥ紩鎿庝竴鏍枫備絾鏄紝蹇呴』璁颁綇[STORAGE] ENGINE锛堝拰鍏朵粬鐨勮〃閫夐」锛夊繀椤诲垪鍦ㄧ敤CREATE TABLE璇彞涓殑鍏朵粬浠讳綍鍒嗗尯閫夐」涔嬪墠銆備笅闈㈢殑渚嬪瓙缁欏嚭浜嗘庢牱鍒涘缓涓涓氳繃HASH鍒嗘垚6涓垎鍖恒佷娇鐢InnoDB瀛樺偍寮曟搸鐨勮〃锛

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=INNODB
    PARTITION BY HASH(MONTH(tr_date))
    PARTITIONS 6;

锛堟敞閲婏細姣忎釜PARTITION 瀛愬彞鍙互鍖呭惈涓涓 [STORAGE] ENGINE 閫夐」锛屼絾鏄湪MySQL 5.1鐗堟湰涓紝杩欐病鏈変綔鐢級

鍒涘缓鍒嗗尯鐨勪复鏃惰〃涔熸槸鍙兘鐨勶紱浣嗘槸锛岃繖绉嶈〃鐨勭敓鍛藉懆鏈熷彧鏈夊綋鍓MySQL 鐨勪細璇濈殑鏃堕棿閭d箞闀裤傚浜庨潪鍒嗗尯鐨勪复鏃惰〃锛岃繖涔熸槸涓鏍风殑銆

娉ㄩ噴鍒嗗尯閫傜敤浜庝竴涓〃鐨勬墍鏈夋暟鎹拰绱㈠紩锛涗笉鑳藉彧瀵规暟鎹垎鍖鸿屼笉瀵圭储寮曞垎鍖猴紝鍙嶄箣浜︾劧锛屽悓鏃朵篃涓嶈兘鍙琛ㄧ殑涓閮ㄥ垎杩涜鍒嗗尯銆

鍙互閫氳繃浣跨敤鐢ㄦ潵鍒涘缓鍒嗗尯琛ㄧ殑CREATE TABLE璇彞鐨PARTITION瀛愬彞鐨DATA DIRECTORY锛堟暟鎹矾寰勶級INDEX DIRECTORY锛堢储寮曡矾寰勶級閫夐」锛屼负姣忎釜鍒嗗尯鐨勬暟鎹拰绱㈠紩鎸囧畾鐗瑰畾鐨勮矾寰勩傛澶栵紝MAX_ROWSMIN_ROWS閫夐」鍙互鐢ㄦ潵璁惧畾鏈澶у拰鏈灏忕殑琛屾暟锛屽畠浠彲浠ュ悇鑷繚瀛樺湪姣忎釜鍒嗗尯閲屻傚叧浜庤繖浜涢夐」鐨勬洿澶氫俊鎭紝璇峰弬瑙18.3鑺傦紝鈥滃垎鍖虹鐞嗏娉ㄩ噴杩欎釜鐗规畩鐨勫姛鑳界敱浜Bug #13250鐨勫師鍥狅紝鐩墠杩樹笉鑳藉疄鐢ㄣ傚湪绗竴涓5.1浜岃繘鍒剁増鏈姇鍏ヤ娇鐢ㄦ椂锛屾垜浠簲璇ュ凡缁忔妸杩欎釜闂瑙e喅浜嗐

鍒嗗尯鐨勪竴浜涗紭鐐瑰寘鎷細

         涓庡崟涓鐩樻垨鏂囦欢绯荤粺鍒嗗尯鐩告瘮锛屽彲浠ュ瓨鍌ㄦ洿澶氱殑鏁版嵁銆

         瀵逛簬閭d簺宸茬粡澶卞幓淇濆瓨鎰忎箟鐨勬暟鎹紝閫氬父鍙互閫氳繃鍒犻櫎涓庨偅浜涙暟鎹湁鍏崇殑鍒嗗尯锛屽緢瀹规槗鍦板垹闄ら偅浜涙暟鎹傜浉鍙嶅湴锛屽湪鏌愪簺鎯呭喌涓嬶紝娣诲姞鏂版暟鎹殑杩囩▼鍙堝彲浠ラ氳繃涓洪偅浜涙柊鏁版嵁涓撻棬澧炲姞涓涓柊鐨勫垎鍖猴紝鏉ュ緢鏂逛究鍦板疄鐜般

閫氬父鍜屽垎鍖烘湁鍏崇殑鍏朵粬浼樼偣鍖呮嫭涓嬮潰鍒楀嚭鐨勮繖浜涖MySQL 鍒嗗尯涓殑杩欎簺鍔熻兘鐩墠杩樻病鏈夊疄鐜帮紝浣嗘槸鍦ㄦ垜浠殑浼樺厛绾у垪琛ㄤ腑锛屽叿鏈夐珮鐨勪紭鍏堢骇锛涙垜浠笇鏈涘湪5.1鐨勭敓浜х増鏈腑锛岃兘鍖呮嫭杩欎簺鍔熻兘銆

         涓浜涙煡璇㈠彲浠ュ緱鍒版瀬澶х殑浼樺寲锛岃繖涓昏鏄熷姪浜庢弧瓒充竴涓粰瀹WHERE 璇彞鐨勬暟鎹彲浠ュ彧淇濆瓨鍦ㄤ竴涓垨澶氫釜鍒嗗尯鍐咃紝杩欐牱鍦ㄦ煡鎵炬椂灏变笉鐢ㄦ煡鎵惧叾浠栧墿浣欑殑鍒嗗尯銆傚洜涓哄垎鍖哄彲浠ュ湪鍒涘缓浜嗗垎鍖鸿〃鍚庤繘琛屼慨鏀癸紝鎵浠ュ湪绗竴娆¢厤缃垎鍖烘柟妗堟椂杩樹笉鏇捐繖涔堝仛鏃讹紝鍙互閲嶆柊缁勭粐鏁版嵁锛屾潵鎻愰珮閭d簺甯哥敤鏌ヨ鐨勬晥鐜囥

         娑夊強鍒颁緥濡SUM() COUNT()杩欐牱鑱氬悎鍑芥暟鐨勬煡璇紝鍙互寰堝鏄撳湴杩涜骞惰澶勭悊銆傝繖绉嶆煡璇㈢殑涓涓畝鍗曚緥瀛愬 鈥SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id銆傞氳繃骞惰 杩欐剰鍛崇潃璇ユ煡璇㈠彲浠ュ湪姣忎釜鍒嗗尯涓婂悓鏃惰繘琛岋紝鏈缁堢粨鏋滃彧闇閫氳繃鎬昏鎵鏈夊垎鍖哄緱鍒扮殑缁撴灉銆

         閫氳繃璺ㄥ涓鐩樻潵鍒嗘暎鏁版嵁鏌ヨ锛屾潵鑾峰緱鏇村ぇ鐨勬煡璇㈠悶鍚愰噺銆

瑕佺粡甯告鏌ユ湰椤靛拰鏈珷锛屽洜涓哄畠灏嗛殢MySQL 5.1鍚庣画鐨勫垎鍖鸿繘灞曡屾洿鏂般

18.2. 鍒嗗尯绫诲瀷

鏈妭璁ㄨ鍦MySQL 5.1涓彲鐢ㄧ殑鍒嗗尯绫诲瀷銆傝繖浜涚被鍨嬪寘鎷細

         RANGE 鍒嗗尯鍩轰簬灞炰簬涓涓粰瀹氳繛缁尯闂寸殑鍒楀硷紝鎶婂琛屽垎閰嶇粰鍒嗗尯銆傚弬瑙18.2.1鑺傦紝鈥淩ANGE鍒嗗尯鈥

         LIST 鍒嗗尯绫讳技浜庢寜RANGE鍒嗗尯锛屽尯鍒湪浜LIST鍒嗗尯鏄熀浜庡垪鍊煎尮閰嶄竴涓鏁e奸泦鍚堜腑鐨勬煇涓兼潵杩涜閫夋嫨銆傚弬瑙18.2.2鑺傦紝鈥LIST鍒嗗尯鈥

         HASH鍒嗗尯鍩轰簬鐢ㄦ埛瀹氫箟鐨勮〃杈惧紡鐨勮繑鍥炲兼潵杩涜閫夋嫨鐨勫垎鍖猴紝璇ヨ〃杈惧紡浣跨敤灏嗚鎻掑叆鍒拌〃涓殑杩欎簺琛岀殑鍒楀艰繘琛岃绠椼傝繖涓嚱鏁板彲浠ュ寘鍚MySQL 涓湁鏁堢殑銆佷骇鐢熼潪璐熸暣鏁板肩殑浠讳綍琛ㄨ揪寮忋傚弬瑙18.2.3鑺傦紝鈥淗ASH鍒嗗尯鈥

         KEY 鍒嗗尯锛氱被浼间簬鎸HASH鍒嗗尯锛屽尯鍒湪浜KEY鍒嗗尯鍙敮鎸佽绠椾竴鍒楁垨澶氬垪锛屼笖MySQL 鏈嶅姟鍣ㄦ彁渚涘叾鑷韩鐨勫搱甯屽嚱鏁般傚繀椤绘湁涓鍒楁垨澶氬垪鍖呭惈鏁存暟鍊笺傚弬瑙18.2.4鑺傦紝鈥淜EY鍒嗗尯鈥

鏃犺浣跨敤浣曠绫诲瀷鐨勫垎鍖猴紝鍒嗗尯鎬绘槸鍦ㄥ垱寤烘椂灏辫嚜鍔ㄧ殑椤哄簭缂栧彿锛屼笖浠0寮濮嬭褰曪紝璁颁綇杩欎竴鐐归潪甯搁噸瑕併傚綋鏈変竴鏂拌鎻掑叆鍒颁竴涓垎鍖鸿〃涓椂锛屽氨鏄娇鐢ㄨ繖浜涘垎鍖虹紪鍙锋潵璇嗗埆姝g‘鐨勫垎鍖恒備緥濡傦紝濡傛灉浣犵殑琛ㄤ娇鐢4涓垎鍖猴紝閭d箞杩欎簺鍒嗗尯灏辩紪鍙蜂负0, 1, 2, 3銆傚浜RANGELIST鍒嗗尯绫诲瀷锛岀‘璁ゆ瘡涓垎鍖虹紪鍙烽兘瀹氫箟浜嗕竴涓垎鍖猴紝寰堟湁蹇呰銆傚HASH鍒嗗尯锛屼娇鐢ㄧ殑鐢ㄦ埛鍑芥暟蹇呴』杩斿洖涓涓ぇ浜0鐨勬暣鏁板笺傚浜KEY鍒嗗尯锛岃繖涓棶棰橀氳繃MySQL鏈嶅姟鍣ㄥ唴閮ㄤ娇鐢ㄧ殑 鍝堝笇鍑芥暟鑷姩杩涜澶勭悊銆

鍒嗗尯鐨勫悕瀛楀熀鏈笂閬靛惊鍏朵粬MySQL 鏍囪瘑绗﹀簲褰撻伒寰殑鍘熷垯锛屼緥濡傜敤浜庤〃鍜屾暟鎹簱鍚嶅瓧鐨勬爣璇嗙銆備絾鏄簲褰撴敞鎰忥紝鍒嗗尯鐨勫悕瀛楁槸涓嶅尯鍒嗗ぇ灏忓啓鐨勩備緥濡傦紝涓嬮潰鐨CREATE TABLE璇彞灏嗕細浜х敓濡備笅鐨勯敊璇細

mysql> CREATE TABLE t2 (val INT)
    -> PARTITION BY LIST(val)(
    ->     PARTITION mypart VALUES IN (1,3,5),
    ->     PARTITION MyPart VALUES IN (2,4,6)
    -> );
閿欒1488 (HY000): 琛ㄧ殑鎵鏈夊垎鍖哄繀椤绘湁鍞竴鐨勫悕瀛椼

杩欐槸鍥犱负MySQL璁や负鍒嗗尯鍚嶅瓧mypartMyPart娌℃湁鍖哄埆銆

娉ㄩ噴鍦ㄤ笅闈㈢殑绔犺妭涓紝鎴戜滑娌℃湁蹇呰鎻愪緵鍙互鐢ㄦ潵鍒涘缓姣忕鍒嗗尯绫诲瀷璇硶鐨勬墍鏈夊彲鑳藉舰寮忥紝杩欎簺淇℃伅鍙互鍦13.1.5鑺傦紝鈥淐REATE TABLE璇硶鈥 涓壘鍒般

18.2.1.聽RANGE鍒嗗尯

鎸夌収RANGE鍒嗗尯鐨勮〃鏄氳繃濡備笅涓绉嶆柟寮忚繘琛屽垎鍖虹殑锛屾瘡涓垎鍖哄寘鍚偅浜涘垎鍖鸿〃杈惧紡鐨勫间綅浜庝竴涓粰瀹氱殑杩炵画鍖洪棿鍐呯殑琛屻傝繖浜涘尯闂磋杩炵画涓斾笉鑳界浉浜掗噸鍙狅紝浣跨敤VALUES LESS THAN鎿嶄綔绗︽潵杩涜瀹氫箟銆傚湪涓嬮潰鐨勫嚑涓緥瀛愪腑锛屽亣瀹氫綘鍒涘缓浜嗕竴涓涓嬬殑涓涓〃锛岃琛ㄤ繚瀛樻湁20瀹堕煶鍍忓簵鐨勮亴鍛樿褰曪紝杩20瀹堕煶鍍忓簵鐨勭紪鍙蜂粠120

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

鎸夌収杩欑鍒嗗尯鏂规锛屽湪鍟嗗簵15宸ヤ綔鐨勯泧鍛樼浉瀵瑰簲鐨勬墍鏈夎琚繚瀛樺湪鍒嗗尯P0涓紝鍟嗗簵610鐨勯泧鍛樹繚瀛樺湪P1涓紝渚濇绫绘帹銆傛敞鎰忥紝姣忎釜鍒嗗尯閮芥槸鎸夐『搴忚繘琛屽畾涔夛紝浠庢渶浣庡埌鏈楂樸傝繖鏄PARTITION BY RANGE 璇硶鐨勮姹傦紱鍦ㄨ繖鐐逛笂锛屽畠绫讳技浜CJava涓殑鈥switch ... case鈥濊鍙ャ

瀵逛簬鍖呭惈鏁版嵁(72, 'Michael', 'Widenius', '1998-06-25', NULL, 13)鐨勪竴涓柊琛岋紝鍙互寰堝鏄撳湴纭畾瀹冨皢鎻掑叆鍒p2鍒嗗尯涓紝浣嗘槸濡傛灉澧炲姞浜嗕竴涓紪鍙蜂负绗21鐨勫晢搴楋紝灏嗕細鍙戠敓浠涔堝憿锛熷湪杩欑鏂规涓嬶紝鐢变簬娌℃湁瑙勫垯鎶store_id澶т簬20鐨勫晢搴楀寘鍚湪鍐咃紝鏈嶅姟鍣ㄥ皢涓嶇煡閬撴妸璇ヨ淇濆瓨鍦ㄤ綍澶勶紝灏嗕細瀵艰嚧閿欒銆 瑕侀伩鍏嶈繖绉嶉敊璇紝鍙互閫氳繃鍦CREATE TABLE璇彞涓浣跨敤涓涓catchallVALUES 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浣嶆暟瀛楃殑宸ヤ綔浠g爜鐢ㄦ潵琛ㄧず鏅氾紙搴楀唴鐨勶級宸ヤ汉锛屼笁涓暟瀛椾唬鐮佽〃绀哄姙鍏鍜屾敮鎸佷汉鍛橈紝鍥涗釜鏁板瓧浠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 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鍒嗗尯鍦ㄥ涓嬪満鍚堢壒鍒湁鐢細

         褰撻渶瑕佸垹闄も滄棫鐨勨濇暟鎹椂銆傚鏋滀綘浣跨敤涓婇潰鏈杩戠殑閭d釜渚嬪瓙缁欏嚭鐨勫垎鍖烘柟妗堬紝浣犲彧闇绠鍗曞湴浣跨敤 鈥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婧愮▼搴忎腑鍚敤锛屼絾鏄紝鏈夊叧宸ヤ綔姝e湪杩涜涓

18.2.2.聽LIST鍒嗗尯

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涓湁缁忛攢鏉冪殑鍦板尯锛屽涓嬭〃鎵绀猴細

鍦板尯

鍟嗗簵ID

鍖楀尯

3, 5, 6, 9, 17

涓滃尯

1, 2, 10, 11, 19, 20

瑗垮尯

4, 12, 13, 14, 18

涓績鍖

7, 8, 15, 16

瑕佹寜鐓у睘浜庡悓涓涓湴鍖哄晢搴楃殑琛屼繚瀛樺湪鍚屼竴涓垎鍖轰腑鐨勬柟寮忔潵鍒嗗壊琛紝鍙互浣跨敤涓嬮潰鐨勨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鍒嗗尯缁撳悎璧锋潵鐢熸垚涓涓鍚堢殑瀛愬垎鍖猴紝涓HASHKEY鍒嗗尯缁撳悎璧锋潵鐢熸垚澶嶅悎鐨勫瓙鍒嗗尯涔熸槸鍙兘鐨勩 鍏充簬杩欐柟闈㈢殑璁ㄨ锛岃鍙傝18.2.5鑺傦紝鈥滃瓙鍒嗗尯鈥

18.2.3.聽HASH鍒嗗尯

HASH鍒嗗尯涓昏鐢ㄦ潵纭繚鏁版嵁鍦ㄩ鍏堢‘瀹氭暟鐩殑鍒嗗尯涓钩鍧囧垎甯冦傚湪RANGELIST鍒嗗尯涓紝蹇呴』鏄庣‘鎸囧畾涓涓粰瀹氱殑鍒楀兼垨鍒楀奸泦鍚堝簲璇ヤ繚瀛樺湪鍝釜鍒嗗尯涓紱鑰屽湪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锛堢皣锛琛紝榛樿鐨勫垎鍖烘暟閲忓皢涓绨囨暟鎹妭鐐圭殑鏁伴噺鐩稿悓锛岃繖绉嶄慨姝e彲鑳芥槸鑰冭檻浠讳綍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)閭d箞鐩存帴锛屽洜涓轰笉鏄垪鈥渄ate_col鈥濇瘡娆″彲鑳界殑鏀瑰彉閮借兘浣胯〃杈惧紡YEAR(date_col)鍙戠敓鍚岀瓑鐨勬敼鍙樸傚嵆渚垮姝わ紝琛ㄨ揪寮廦EAR(date_col)涔熻繕鏄竴涓敤浜 鍝堝笇鍑芥暟鐨勩佸ソ鐨勫欓夎〃杈惧紡锛屽洜涓哄畠闅忓垪date_col鐨勪竴閮ㄥ垎鍙戠敓鐩存帴鍙樺寲锛屽苟涓斿垪date_col鐨勫彉鍖栦笉鍙兘寮曡捣琛ㄨ揪寮廦EAR(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涓紝閭d箞淇濆瓨璇ユ潯璁板綍鐨勫垎鍖虹‘瀹氬涓嬶細

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

MySQL 5.1 杩樻敮鎸佷竴涓绉颁负鈥linear hashing锛堢嚎鎬у搱甯屽姛鑳斤級鈥濈殑鍙橀噺锛屽畠浣跨敤涓涓洿鍔犲鏉傜殑绠楁硶鏉ョ‘瀹氭柊琛屾彃鍏ュ埌宸茬粡鍒嗗尯浜嗙殑琛ㄤ腑鐨勪綅缃傚叧浜庤繖绉嶇畻娉曠殑鎻忚堪锛岃鍙傝18.2.3.1鑺傦紝鈥淟INEAR HASH鍒嗗尯鈥

姣忓綋鎻掑叆鎴栨洿鏂颁竴鏉¤褰曪紝鐢ㄦ埛鍑芥暟閮借璁$畻涓娆°傚綋鍒犻櫎璁板綍鏃讹紝鐢ㄦ埛鍑芥暟涔熷彲鑳借杩涜璁$畻锛岃繖鍙栧喅浜庢墍澶勭殑鐜銆

娉ㄩ噴濡傛灉灏嗚鍒嗗尯鐨勮〃鏈変竴涓敮涓鐨勯敭锛岄偅涔堢敤鏉ヤ綔涓HASH鐢ㄦ埛鍑芥暟鐨勮嚜鍙樻暟鎴栬呬富閿殑column_list鐨勮嚜鍙樻暟鐨勪换鎰忓垪閮藉繀椤绘槸閭d釜閿殑涓閮ㄥ垎銆

18.2.3.1.聽LINEAR HASH鍒嗗尯

MySQL杩樻敮鎸佺嚎鎬у搱甯屽姛鑳斤紝瀹冧笌甯歌鍝堝笇鐨勫尯鍒湪浜庯紝绾挎у搱甯屽姛鑳戒娇鐢ㄧ殑涓涓嚎鎬х殑2鐨勫箓锛powers-of-two锛夎繍绠楁硶鍒欙紝鑰屽父瑙 鍝堝笇浣跨敤鐨勬槸姹傚搱甯屽嚱鏁板肩殑妯℃暟銆

绾挎у搱甯屽垎鍖哄拰甯歌鍝堝笇鍒嗗尯鍦ㄨ娉曚笂鐨勫敮涓鍖哄埆鍦ㄤ簬锛屽湪鈥PARTITION BY鈥 瀛愬彞涓坊鍔犫LINEAR鈥濆叧閿瓧锛屽涓嬮潰鎵绀猴細

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

鍋囪涓涓〃杈惧紡expr, 褰撲娇鐢ㄧ嚎鎬у搱甯屽姛鑳芥椂锛岃褰曞皢瑕佷繚瀛樺埌鐨勫垎鍖烘槸num 涓垎鍖轰腑鐨勫垎鍖N锛屽叾涓N鏄牴鎹笅闈㈢殑绠楁硶寰楀埌锛

1.    鎵惧埌涓嬩竴涓ぇ浜num.鐨勩2鐨勫箓锛屾垜浠妸杩欎釜鍊肩О涓V 锛屽畠鍙互閫氳繃涓嬮潰鐨勫叕寮忓緱鍒帮細

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

锛堜緥濡傦紝鍋囧畾num13銆傞偅涔LOG(2,13)灏辨槸3.7004397181411 CEILING(3.7004397181411)灏辨槸4锛屽垯V = POWER(2,4), 鍗崇瓑浜16

3.    璁剧疆 N = F(column_list) & (V - 1).

4.    N >= num:

         璁剧疆 V = CEIL(V / 2)

         璁剧疆 N = N & (V - 1)

渚嬪锛屽亣璁捐〃t1锛屼娇鐢ㄧ嚎鎬у搱甯屽垎鍖轰笖鏈4涓垎鍖猴紝鏄氳繃涓嬮潰鐨勮鍙ュ垱寤虹殑锛

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR HASH( YEAR(col3) )
    PARTITIONS 6;

鐜板湪鍋囪瑕佹彃鍏ヤ袱琛岃褰曞埌琛t1涓紝鍏朵腑涓鏉¤褰col3鍒楀间负'2003-04-14'锛屽彟涓鏉¤褰col3鍒楀间负'1998-10-19'銆傜涓鏉¤褰曞皢瑕佷繚瀛樺埌鐨勫垎鍖虹‘瀹氬涓嬶細

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

鎸夌収绾挎у搱甯屽垎鍖虹殑浼樼偣鍦ㄤ簬澧炲姞銆佸垹闄ゃ佸悎骞跺拰鎷嗗垎鍒嗗尯灏嗗彉寰楁洿鍔犲揩鎹凤紝鏈夊埄浜庡鐞嗗惈鏈夋瀬鍏跺ぇ閲忥紙1000鍚夛級鏁版嵁鐨勮〃銆傚畠鐨勭己鐐瑰湪浜庯紝涓庝娇鐢ㄥ父瑙HASH鍒嗗尯寰楀埌鐨勬暟鎹垎甯冪浉姣旓紝鍚勪釜鍒嗗尯闂存暟鎹殑鍒嗗竷涓嶅ぇ鍙兘鍧囪 銆

18.2.4.聽KEY鍒嗗尯

鎸夌収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鍒嗗尯鈥

18.2.5. 瀛愬垎鍖

瀛愬垎鍖烘槸鍒嗗尯琛ㄤ腑姣忎釜鍒嗗尯鐨勫啀娆″垎鍓层備緥濡傦紝鑰冭檻涓嬮潰鐨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 3RANGE鍒嗗尯銆傝繖3涓垎鍖轰腑鐨勬瘡涓涓垎鍖鈥斺p0, p1, p2 鈥斺斿張琚繘涓姝ュ垎鎴愪簡2涓瓙鍒嗗尯銆傚疄闄呬笂锛屾暣涓〃琚垎鎴愪簡3 * 2 = 6涓垎鍖恒備絾鏄紝鐢变簬PARTITION BY RANGE瀛愬彞鐨勪綔鐢紝杩欎簺鍒嗗尯鐨勫ご2涓彧淇濆瓨鈥減urchased鈥鍒椾腑鍊煎皬浜1990鐨勯偅浜涜褰曘

MySQL 5.1涓紝瀵逛簬宸茬粡閫氳繃RANGELIST鍒嗗尯浜嗙殑琛ㄥ啀杩涜瀛愬垎鍖烘槸鍙兘鐨勩傚瓙鍒嗗尯鏃㈠彲浠ヤ娇鐢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 瀛愬彞蹇呴』鍖呮嫭 (鑷冲皯)瀛愬垎鍖虹殑涓涓悕瀛椼傚惁鍒欙紝浣犲彲鑳借瀵硅瀛愬垎鍖鸿缃换浣曚綘鎵闇瑕佺殑閫夐」锛屾垨鑰呭厑璁歌瀛愬垎鍖哄閭d簺閫夐」閲囩敤鍏堕粯璁ょ殑璁剧疆銆

         鍦ㄦ瘡涓垎鍖哄唴锛屽瓙鍒嗗尯鐨勫悕瀛楀繀椤绘槸鍞竴鐨勶紝浣嗘槸鍦ㄦ暣涓〃涓紝娌℃湁蹇呰淇濇寔鍞竴銆備緥濡傦紝涓嬮潰鐨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骞翠箣鍓嶇殑璁板綍閭d箞澶х殑瀛樺偍绌洪棿銆傝繖浜涜褰曞垎鍦2涓鐩橈紙/disk4/disk5锛変笂淇濆瓨锛岃屼笉鏄4涓鐩橈細

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

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

         淇濆瓨璐拱鏃ユ湡浠2000骞村埌鐜板湪鐨勮褰曪紙鍒嗗尯p2锛変笉闇瑕佸墠闈袱涓RANGE鍒嗗尯閭d箞澶х殑绌洪棿銆傚綋鍓嶏紝鍦ㄩ粯璁ょ殑浣嶇疆鑳藉瓒冲淇濆瓨鎵鏈夎繖浜涜褰曘

灏嗘潵锛屽鏋滀粠2000骞村紑濮嬪悗鍗佸勾璐拱鐨勬暟閲忓凡缁忚揪鍒颁簡榛樿鐨勪綅缃笉鑳藉鎻愪緵瓒冲鐨勪繚瀛樼┖闂存椂锛岀浉搴旂殑璁板綍锛堣锛夊彲浠ラ氳繃浣跨敤鈥ALTER TABLE ... REORGANIZE PARTITION鈥濊鍙ョЩ鍔ㄥ埌鍏朵粬鐨勪綅缃傚叧浜庡浣曞疄鐜扮殑璇存槑锛岃鍙傝18.3鑺傦紝鈥滃垎鍖虹鐞嗏

18.2.6. MySQL鍒嗗尯澶勭悊NULL鍊肩殑鏂瑰紡

MySQL 涓殑鍒嗗尯鍦ㄧ姝㈢┖鍊硷紙NULL锛変笂娌℃湁杩涜澶勭悊锛屾棤璁哄畠鏄竴涓垪鍊艰繕鏄竴涓敤鎴峰畾涔夎〃杈惧紡鐨勫笺備竴鑸岃█锛屽湪杩欑鎯呭喌涓MySQL NULL瑙嗕负0銆傚鏋滀綘甯屾湜鍥為伩杩欑鍋氭硶锛屼綘搴旇鍦ㄨ璁¤〃鏃朵笉鍏佽绌哄硷紱鏈鍙兘鐨勬柟娉曟槸锛岄氳繃澹版槑鍒椻NOT NULL鈥濇潵瀹炵幇杩欎竴鐐广

鍦ㄦ湰鑺備腑锛屾垜浠彁渚涗簡涓浜涗緥瀛愶紝鏉ヨ鏄庡綋鍐冲畾涓涓搴旇淇濆瓨鍒板摢涓垎鍖烘椂锛MySQL 鏄浣曞鐞NULL鍊肩殑銆

濡傛灉鎻掑叆涓琛屽埌鎸夌収RANGELIST鍒嗗尯鐨勮〃锛岃琛岀敤鏉ョ‘瀹氬垎鍖虹殑鍒楀间负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鍒楁病鏈夊0鏄庝负鈥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)

鍦ㄦ寜HASHKEY鍒嗗尯鐨勬儏鍐典笅锛屼换浣曚骇鐢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銆傝繖涓粨鏋滃湪纭畾姝g‘鐨勫垎鍖烘柟闈㈣璁や负鏄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涓

18.3. 鍒嗗尯绠$悊

MySQL 5.1 鎻愪緵浜嗚澶氫慨鏀瑰垎鍖鸿〃鐨勬柟寮忋傛坊鍔犮佸垹闄ゃ侀噸鏂板畾涔夈佸悎骞舵垨鎷嗗垎宸茬粡瀛樺湪鐨勫垎鍖烘槸鍙兘鐨勩傛墍鏈夎繖浜涙搷浣滈兘鍙互閫氳繃浣跨敤ALTER TABLE 鍛戒护鐨勫垎鍖烘墿灞曟潵瀹炵幇(鍏充簬璇硶鐨勫畾涔夛紝璇峰弬瑙13.1.2鑺傦紝鈥淎LTER TABLE璇硶鈥 )銆備篃鏈夎幏寰楀垎鍖鸿〃鍜屽垎鍖轰俊鎭殑鏂瑰紡銆傚湪鏈妭锛屾垜浠璁轰笅闈㈣繖浜涗富棰橈細

         RANGELIST鍒嗗尯鐨勮〃鐨勫垎鍖虹鐞嗙殑鏈夊叧淇℃伅锛岃鍙傝18.3.1鑺傦紝鈥淩ANGE鍜孡IST鍒嗗尯鐨勭鐞嗏

         鍏充簬HASHKEY鍒嗗尯绠$悊鐨勮璁猴紝璇峰弬瑙18.3.2鑺傦紝鈥淗ASH鍜孠EY鍒嗗尯鐨勭鐞

         MySQL 5.1涓彁渚涚殑銆佽幏寰楀叧浜庡垎鍖鸿〃鍜屽垎鍖轰俊鎭殑鏈哄埗鐨勮璁猴紝璇峰弬瑙18.3.4鑺傦紝鈥滆幏鍙栧叧浜庡垎鍖虹殑淇℃伅鈥

         鍏充簬鎵ц鍒嗗尯缁存姢鎿嶄綔鐨勮璁猴紝璇峰弬瑙18.3.3鑺傦紝鈥滃垎鍖虹淮鎶も

娉ㄩ噴锛氬湪MySQL 5.1涓紝涓涓垎鍖鸿〃鐨勬墍鏈夊垎鍖洪兘蹇呴』鏈夊瓙鍒嗗尯鍚屾牱鐨勫悕瀛楋紝骞朵笖涓鏃﹁〃宸茬粡鍒涘缓锛屽啀鏀瑰彉瀛愬垎鍖烘槸涓嶅彲鑳界殑銆

瑕佺偣锛褰撳墠锛屼粠5.1绯诲垪璧峰缓绔嬬殑MySQL 鏈嶅姟鍣ㄥ氨鎶娾ALTER TABLE ... PARTITION BY ...鈥浣滀负鏈夋晥鐨勮娉曪紝浣嗘槸杩欎釜璇彞鐩墠杩樹笉璧蜂綔鐢ㄣ傛垜浠湡鏈MySQL 5.1杈惧埌鐢熶骇鐘舵佹椂锛岃兘澶熸寜鐓т笅闈㈢殑鎻忚堪瀹炵幇璇ヨ鍙ョ殑鍔熻兘銆

瑕佹敼鍙樹竴涓〃鐨勫垎鍖烘ā寮忥紝鍙渶瑕佷娇鐢ㄥ甫鏈変竴涓partition_options鈥濆瓙鍙ョ殑ALTER TABLE 鐨勫懡浠ゃ傝繖涓瓙鍙ュ拰涓庡垱寤轰竴涓垎鍖鸿〃鐨CREATE TABLE鍛戒护涓鍚屼娇鐢ㄧ殑瀛愬彞鏈夌浉鍚岀殑璇硶锛屽苟涓旀绘槸浠ュ叧閿瓧PARTITION BY 寮澶淬備緥濡傦紝鍋囪鏈変竴涓娇鐢ㄤ笅闈CREATE TABLE璇彞寤虹珛鐨勬寜鐓RANGE鍒嗗尯鐨勮〃锛

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

鐜板湪锛岃鎶婅繖涓〃鎸夌収浣跨敤id鍒楀间綔涓洪敭鐨勫熀纭锛岄氳繃KEY鍒嗗尯鎶婂畠閲嶆柊鍒嗘垚涓や釜鍒嗗尯锛屽彲浠ヤ娇鐢ㄤ笅闈㈢殑璇彞锛

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2

杩欏拰鍏堝垹闄よ繖涓〃銆佺劧鍚庝娇鐢ㄢCREATE TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2鈥濋噸鏂板垱寤鸿繖涓〃鍏锋湁鍚屾牱鐨勬晥鏋溿

18.3.1. RANGE鍜孡IST鍒嗗尯鐨勭鐞

鍏充簬濡備綍娣诲姞鍜屽垹闄ゅ垎鍖虹殑澶勭悊锛RANGELIST鍒嗗尯闈炲父鐩镐技銆傚熀浜庤繖涓師鍥狅紝鎴戜滑鍦ㄦ湰鑺傝璁鸿繖涓ょ鍒嗗尯鐨勭鐞嗐傚叧浜HASHKEY鍒嗗尯绠$悊鐨勪俊鎭紝璇峰弬瑙18.3.2鑺傦紝鈥淗ASH鍜孠EY鍒嗗尯鐨勭鐞銆傚垹闄や竴涓RANGELIST鍒嗗尯姣斿鍔犱竴涓垎鍖鸿鏇村姞绠鍗曟槗鎳傦紝鎵浠ユ垜浠厛璁ㄨ鍓嶈呫

浠庝竴涓寜鐓RANGELIST鍒嗗尯鐨勮〃涓垹闄や竴涓垎鍖猴紝鍙互浣跨敤甯︿竴涓DROP PARTITION瀛愬彞鐨ALTER TABLE鍛戒护鏉ュ疄鐜般傝繖閲屾湁涓涓潪甯稿熀鏈殑渚嬪瓙锛屽亣璁惧凡缁忎娇鐢ㄤ笅闈㈢殑CREATE TABLEINSERT璇彞鍒涘缓浜嗕竴涓寜鐓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鍒嗗尯鈥

瑕佸鍔犱竴涓柊鐨RANGELIST鍒嗗尯鍒颁竴涓墠闈㈠凡缁忓垎鍖轰簡鐨勮〃锛屼娇鐢ㄢ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骞达紙浠ュ強浠ュ悗骞翠唤锛夊嚭鐢熺殑鎴愬憳銆傚彲浠ユ寜鐓т笅闈㈢殑鏂瑰紡锛屼慨鏀规垚鍛樿〃鏉ュ绾冲嚭鐢熷湪19901999骞翠箣闂寸殑鎴愬憳锛

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

鍙互閫氳繃涓嬮潰鐨勬柟娉曟坊鍔犱竴涓柊鐨勫垎鍖猴紝鐢ㄦ潵淇濆瓨鎷ユ湁鏁版嵁鍒楀71421鐨勮锛

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锛岀劧鍚庝娇鐢ㄤ慨姝e悗鐨勫畾涔夋坊鍔犱竴涓柊鐨勫垎鍖p1銆備絾鏄紝姝e鎴戜滑鍓嶉潰璁ㄨ杩囩殑锛岃繖灏嗗鑷翠繚瀛樺湪鍒嗗尯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鍒嗘垚浜嗕袱涓柊鐨勫垎鍖s0s1銆傚悓鏃讹紝瀹冭繕鏍规嵁鍖呭惈鍦ㄤ袱涓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 鎶婁繚瀛樺湪鍒嗗尯s0s1涓殑鎵鏈夋暟鎹兘绉诲埌鍒嗗尯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銆傝鎴戜滑鍥炲埌閭d釜闂锛屽嵆澧炲姞涓涓柊鐨勫垎鍖哄埌宸茬粡鎸夌収LIST鍒嗗尯鐨勮〃tt涓紝浣嗘槸鍥犱负璇ユ柊鍒嗗尯鏈変竴涓煎凡缁忓瓨鍦ㄤ簬鐜版湁鍒嗗尯鐨勫煎垪琛ㄤ腑锛屾坊鍔犳柊鐨勫垎鍖哄け璐ャ傛垜浠彲浠ラ氳繃鍏娣诲姞鍙寘鍚潪鍐茬獊鍊肩殑鍒嗗尯锛岀劧鍚庨噸鏂扮粍缁囪鏂板垎鍖哄拰鐜版湁鐨勯偅涓垎鍖猴紝浠ヤ究淇濆瓨鍦ㄧ幇鏈夌殑閭d釜鍒嗗尯涓殑鍊肩幇鍦ㄧЩ鍒颁簡鏂扮殑鍒嗗尯涓紝鏉ュ鐞嗚繖涓棶棰橈細

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鈥濇潵瀵瑰凡缁忔寜鐓RANGELIST鍒嗗尯琛ㄨ繘琛岄噸鏂板垎鍖烘椂锛屼笅闈㈡槸涓浜涜璁颁綇鐨勫叧閿偣锛

         鐢ㄦ潵纭畾鏂板垎鍖烘ā寮忕殑PARTITION瀛愬彞浣跨敤涓庣敤鍦CREATE TABLE涓‘瀹氬垎鍖烘ā寮忕殑PARTITION瀛愬彞鐩稿悓鐨勮鍒欍

鏈閲嶈鐨勬槸锛屽簲璇ヨ浣忥細鏂板垎鍖烘ā寮忎笉鑳芥湁浠讳綍閲嶅彔鐨勫尯闂达紙閫傜敤浜庢寜鐓RANGE鍒嗗尯鐨勮〃锛夋垨鍊奸泦鍚堬紙閫傜敤浜庨噸鏂扮粍缁囨寜鐓LIST鍒嗗尯鐨勮〃锛夈

         partition_definitions 鍒楄〃涓垎鍖虹殑鍚堥泦搴旇涓庡湪partition_list 涓懡鍚嶅垎鍖虹殑鍚堥泦鍗犳湁鐩稿悓鐨勫尯闂存垨鍊奸泦鍚堛

渚嬪锛屽湪鏈妭涓敤浣滀緥瀛愮殑鎴愬憳琛ㄤ腑锛屽垎鍖p1p2鎬诲叡瑕嗙洊浜19801999鐨勮繖浜涘勾銆傚洜姝わ紝瀵硅繖涓や釜鍒嗗尯鐨勯噸鏂扮粍缁囬兘搴旇瑕嗙洊鐩稿悓鑼冨洿鐨勫勾浠姐

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

渚嬪锛屼笉鑳戒娇鐢ㄤ互鈥ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ...鈥濆紑澶寸殑璇彞锛屾潵閲嶆柊缁勭粐鏈妭涓敤浣滀緥瀛愮殑鎴愬憳琛ㄣ傚洜涓猴紝p0瑕嗙洊浜1970骞翠互鍓嶇殑骞翠唤锛岃p2瑕嗙洊浜嗕粠19901999锛堝寘鎷19901999锛変箣闂寸殑骞翠唤锛屽洜鑰岃繖涓や釜鍒嗗尯涓嶆槸鐩搁偦鐨勫垎鍖恒

         涓嶈兘浣跨敤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 ...鈥杩樻病鏈夊疄鐜般備綔涓烘浛浠o紝瑕佷箞浣跨敤鍏堝垹闄よ〃锛岀劧鍚庝娇鐢ㄦ兂瑕佺殑鍒嗗尯閲嶅缓琛紝鎴栬鈥斺濡傛灉闇瑕佷繚鐣欏凡缁忓瓨鍌ㄥ湪琛ㄤ腑鐨勬暟鎹鈥斺鍙互浣跨敤鈥淐REATE TABLE ... SELECT ...鈥鏉ュ垱寤烘柊鐨勮〃锛岀劧鍚庝粠鏃ц〃涓妸鏁版嵁鎷疯礉鍒版柊琛ㄤ腑锛屽啀鍒犻櫎鏃ц〃锛屽鏈夊繀瑕侊紝鏈鍚庨噸鏂板懡鍚嶆柊琛ㄣ

18.3.2. HASH鍜孠EY鍒嗗尯鐨勭鐞

鍦ㄦ敼鍙樺垎鍖鸿缃柟闈紝鎸夌収HASH鍒嗗尯鎴KEY鍒嗗尯鐨勮〃褰兼闈炲父鐩镐技锛屼絾鏄畠浠張涓庢寜鐓RANGELIST鍒嗗尯鐨勮〃鍦ㄥ緢澶氭柟闈㈡湁宸埆銆傛墍浠ワ紝鏈妭鍙璁烘寜鐓HASHKEY鍒嗗尯琛ㄧ殑淇敼銆傚叧浜庢坊鍔犲拰鍒犻櫎鎸夌収RANGELIST杩涜鍒嗗尯鐨勮〃鐨勫垎鍖虹殑璁ㄨ锛屽弬瑙18.3.1鑺傦紝鈥淩ANGE鍜孡IST鍒嗗尯鐨勭鐞嗏

涓嶈兘浣跨敤涓庝粠鎸夌収RANGELIST鍒嗗尯鐨勮〃涓垹闄ゅ垎鍖虹浉鍚岀殑鏂瑰紡锛屾潵浠HASHKEY鍒嗗尯鐨勮〃涓垹闄ゅ垎鍖恒備絾鏄紝鍙互浣跨敤鈥ALTER TABLE ... COALESCE PARTITION鈥濆懡浠ゆ潵鍚堝苟HASHKEY鍒嗗尯銆備緥濡傦紝鍋囧畾鏈変竴涓寘鍚【瀹俊鎭暟鎹殑琛紝瀹冭鍒嗘垚浜12涓垎鍖恒傝椤惧琛ㄧ殑瀹氫箟濡備笅锛

CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12

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

mysql> ALTER TABLE clients COALESCE PARTITION 6
Query OK, 0 rows affected (0.02 sec)

瀵逛簬鎸夌収HASHKEYLINEAR 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浠f浛

瑕佸鍔犻【瀹㈣〃鐨勫垎鍖烘暟閲忎粠1218锛屼娇鐢ㄢALTER TABLE ... ADD PARTITION鈥,鍏蜂綋濡備笅锛

ALTER TABLE clients ADD PARTITION PARTITIONS 18

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

18.3.3. 鍒嗗尯缁存姢

娉ㄩ噴瀹為檯涓婏紝鏈妭璁ㄨ鐨勫懡浠よ繕娌℃湁鍦MySQL 5.1涓疄鐜帮紝 鍦ㄨ繖閲屾彁鍑虹殑鐩殑锛屾槸涓轰簡鍦5.1鐗堟姇浜у墠鐨勫紑鍙戝懆鏈熸湡闂达紝寮曞嚭鏉ヨ嚜鐢ㄦ埛娴嬭瘯璇ヨ蒋浠剁殑鍙嶉鎰忚銆(鎹㈠彞璇濊锛屽氨鏄滆涓嶈鍙嶉杩欐牱鐨勭己闄凤紝璇磋繖浜涘懡浠や笉璧蜂綔鐢ㄢ濓級闅忕潃MySQL5.1鐗堝紑鍙戠殑缁х画锛岃繖浜涗俊鎭緢鏈夊彲鑳藉彂鐢熷彉鍖栥傞殢鐫鍒嗗尯鍔熻兘鐨勫疄鐜板拰鎻愰珮锛屾垜浠皢鏇存柊鏈妭鐨勫唴瀹广

MySQL 5.1涓彲浠ユ墽琛岃澶氬垎鍖虹淮鎶ょ殑浠诲姟銆傚浜庡垎鍖鸿〃锛MySQL涓嶆敮鎸佸懡浠CHECK TABLEOPTIMIZE TABLEANALYZE TABLE锛屾垨REPAIR TABLE銆備綔涓烘浛浠o紝鍙互浣跨敤ALTER TABLE 鐨勮澶氭墿灞曟潵鍦ㄤ竴涓垨澶氫釜鍒嗗尯涓婄洿鎺ュ湴鎵ц杩欎簺鎿嶄綔锛屽涓嬮潰鍒楀嚭鐨勯偅鏍凤細

         閲嶅缓鍒嗗尯: 杩欏拰鍏堝垹闄や繚瀛樺湪鍒嗗尯涓殑鎵鏈夎褰曪紝鐒跺悗閲嶆柊鎻掑叆瀹冧滑锛屽叿鏈夊悓鏍风殑鏁堟灉銆傚畠鍙敤浜庢暣鐞嗗垎鍖虹鐗囥

绀轰緥锛

ALTER TABLE t1 REBUILD PARTITION (p0, p1)

         浼樺寲鍒嗗尯锛濡傛灉浠庡垎鍖轰腑鍒犻櫎浜嗗ぇ閲忕殑琛岋紝鎴栬呭涓涓甫鏈夊彲鍙橀暱搴︾殑琛岋紙涔熷氨鏄锛屾湁VARCHARBLOB锛屾垨TEXT绫诲瀷鐨勫垪锛変綔浜嗚澶氫慨鏀癸紝鍙互浣跨敤鈥ALTER TABLE ... OPTIMIZE PARTITION鈥鏉ユ敹鍥炴病鏈変娇鐢ㄧ殑绌洪棿锛屽苟鏁寸悊鍒嗗尯鏁版嵁鏂囦欢鐨纰庣墖銆

绀轰緥锛

ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1)

鍦ㄤ竴涓粰瀹氱殑鍒嗗尯琛ㄤ笂浣跨敤鈥OPTIMIZE PARTITION鈥绛夊悓浜庡湪閭d釜鍒嗗尯涓婅繍琛CHECK PARTITIONANALYZE 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鈥鏉ヤ慨琛ヨ鍒嗗尯銆

杩樺彲浠ヤ娇鐢mysqlcheckmyisamchk 搴旂敤绋嬪簭锛屽湪瀵硅〃杩涜鍒嗗尯鏃舵墍浜х敓鐨勩佸崟鐙殑MYI鏂囦欢涓杩涜鎿嶄綔锛屾潵瀹屾垚杩欎簺浠诲姟銆傝鍙傝8.7鑺傦紝鈥渕ysqlcheck锛氳〃缁存姢鍜岀淮淇▼搴忊銆傦紙鍦pre-alpha缂栫爜涓紝杩欎釜鍔熻兘宸茬粡鍙互浣跨敤锛夈

18.3.4. 鑾峰彇鍏充簬鍒嗗尯鐨勪俊鎭

鏈妭璁ㄨ鑾峰彇鍏充簬鐜版湁鍒嗗尯鐨勪俊鎭傝繖涓姛鑳戒粛鐒跺浜庤鍒掗樁娈碉紝鎵浠ョ幇闃舵鍦ㄨ繖閲屾弿杩扮殑锛屽疄闄呬笂鏄垜浠兂瑕佸湪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)

娉ㄩ噴锛褰撳墠锛屽浜庢寜HASHKEY鍒嗗尯鐨勮〃锛PARTITIONS瀛愬彞骞朵笉鏄剧ず銆 (Bug #14327)

SHOW TABLE STATUS鐢ㄤ簬鍒嗗尯琛紝瀹冪殑杈撳嚭涓庣敤浜庨潪鍒嗗尯琛ㄧ殑杈撳嚭鐩稿悓锛岄櫎浜嗗紩鎿庯紙Engine锛夊垪鎬绘槸鍖呭惈'PARTITION'鍊笺(鍏充簬杩欎釜鍛戒护鐨勬洿澶氫俊鎭紝鍙傝13.5.4.18鑺傦紝鈥淪HOW TABLE STATUS璇硶鈥瑕佽幏鍙栧崟涓垎鍖虹殑鐘舵佷俊鎭紝鎴戜滑璁″垝瀹炵幇涓涓SHOW PARTITION STATUS鍛戒护璇峰弬瑙佷笅闈)

璁″垝鐢ㄤ簬鍒嗗尯琛ㄧ殑銆佷袱涓檮鍔犵殑SHOW鍛戒护鏄細

         SHOW PARTITIONS

杩欎釜鍛戒护棰勬湡鍏跺姛鑳界被浼间簬SHOW TABLESSHOW DATABASES锛岄櫎浜嗚鍛戒护灏嗗垪鍑虹殑鏄垎鍖鸿屼笉鏄〃鎴栨暟鎹簱銆傝繖涓懡浠ょ殑杈撳嚭鍙兘鍖呭惈鍗曚釜绉颁负Partitions_in_tbl_name 鐨勫垪锛屽叾涓tbl_name 鏄垎鍖鸿〃鐨勫悕瀛椼傚浜SHOW TABLES鍛戒护鑰岃█锛屽鏋滀竴鏃﹂夋嫨浜嗕竴涓暟鎹簱锛岄殢鍚庤鏁版嵁搴撳皢浣滀负SHOW TABLES鍛戒护鐨勯粯璁ゆ暟鎹簱銆備絾鏄敱浜SHOW PARTITIONS鍛戒护涓嶅彲鑳界敤杩欐牱鐨勬柟寮忔潵閫夋嫨涓涓〃锛屽畠寰堝彲鑳介渶瑕佷娇鐢FROM瀛愬彞锛屼互渚MySQL鐭ラ亾瑕佹樉绀虹殑鏄摢涓〃鐨勫垎鍖轰俊鎭

         SHOW PARTITION STATUS

杩欎釜鍛戒护灏嗘彁渚涘叧浜庝竴涓垨澶氫釜鍒嗗尯鐨勮缁嗙姸鎬佷俊鎭傚畠鐨勮緭鍑哄緢鍙兘鍖呭惈鏈変笌SHOW TABLE STATUS 鐨勮緭鍑虹浉鍚屾垨绫讳技鐨勫垪锛屾澶栵紝杩樺寘鎷樉绀虹敤浜庡垎鍖虹殑鏁版嵁鍜岀储寮曡矾寰勭殑闄勫姞鍒椼傝繖涓懡浠ゅ彲鑳芥敮鎸LIKEFROM瀛愬彞锛岃繖鏍蜂娇寰楅氳繃鍚嶅瓧鑾峰緱鍏充簬涓涓粰瀹氬垎鍖虹殑淇℃伅锛屾垨鑰呰幏寰楀叧浜庡睘浜庢寚瀹氳〃鎴栨暟鎹簱鐨勫垎鍖虹殑淇℃伅锛屾垚涓哄彲鑳姐

鎵╁睍INFORMATION_SCHEMA 鏁版嵁搴撶殑璁″垝涔熷湪杩涜涓紝浠ヤ究鎻愪緵鍏充簬鍒嗗尯琛ㄥ拰鍒嗗尯鐨勪俊鎭傝繖涓鍒掑綋鍓嶈繕澶勪竴涓湪闈炲父鏃╃殑闃舵锛涢殢鐫琛ュ厖鐨勪俊鎭彉寰楀彲鐢紝浠ュ強浠讳綍鏂扮殑銆佷笌鍒嗗尯鏈夊叧鐨INFORMATION_SCHEMA鎵╁睍寰椾互瀹炵幇锛屾垜浠皢鏇存柊鎵嬪唽鐩稿叧閮ㄥ垎鐨勫唴瀹广


杩欐槸MySQL鍙傝冩墜鍐岀殑缈昏瘧鐗堟湰锛屽叧浜嶮ySQL鍙傝冩墜鍐岋紝璇疯闂dev.mysql.com銆傚師濮嬪弬鑰冩墜鍐屼负鑻辨枃鐗堬紝涓庤嫳鏂囩増鍙傝冩墜鍐岀浉姣旓紝鏈炕璇戠増鍙兘涓嶆槸鏈鏂扮殑銆