鍝庯紝鍏勫紵浠紒鏈夋病鏈夐亣鍒拌繃杩欑鎯呭喌锛熸槑鏄庣粰MySQL鍔犱簡绱㈠紩锛屾煡璇㈤€熷害杩樻槸鎱㈠緱璁╀汉鎶撶媯锛熻繖鏃跺€欎綘鍙兘闇€瑕佲€?strong>鈥嬩慨鏀圭储寮曗€?/strong>鈥嬩簡銆備絾闂鏉ヤ簡鈥斺€旇繖鐜╂剰鍎垮埌搴曟€庝箞鏀规墠瀹夊叏锛熶粖澶╁挶浠氨鎺板紑浜嗘弶纰庝簡鑱婏紝鎵嬫妸鎵嬫暀浣犳搷浣滐紒

涓€銆佷负鍟ヨ淇敼绱㈠紩锛?/h3>

鍏堣涓墡蹇冪殑浜嬪疄锛氣€?strong>鈥?0%鐨勬暟鎹簱鎬ц兘闂閮借窡绱㈠紩鐬庢姌鑵炬湁鍏斥€?/strong>鈥嬨€備綘鍙兘閬囧埌杩囷細

• 涓氬姟闇€姹傚彉浜嗭紝鍘熸潵鐨勭储寮曚笉椤剁敤浜?/li>
• 鎵嬫粦寤洪敊浜嗙储寮曞瓧娈甸『搴?/li>
• 绱㈠紩鍗犵殑瀛樺偍绌洪棿姣旀暟鎹〃杩樺ぇ锛堣繖璋侀《寰椾綇鍟婏級

涓句釜鏍楀瓙馃尠锛氬師鍏堢粰鐢ㄦ埛琛ㄥ缓鐨勭储寮曟槸锛坅ge, gender锛夛紝鐜板湪涓氬姟闇€瑕佹寜锛坓ender, reg_time锛夋帓搴忔煡璇紝浣犺杩欑储寮曟槸涓嶆槸寰楁敼锛?/p>

---

浜屻€佷慨鏀瑰墠蹇呴』鍋氱殑鍑嗗

鈥?strong>鈥嬮噸鐐规潵浜嗭紒鏀圭储寮曞氨鍍忓仛澶栫鎵嬫湳锛屾病鏈墠妫€鏌ュ彲涓嶈锛氣€?/strong>鈥?/p>

1. 鈥?strong>鈥嬫煡鐜版湁绱㈠紩鈥?/strong>鈥嬶細SHOW INDEX FROM 琛ㄥ悕; 杩欏懡浠ゅ彲姣擷鍏夎繕绠＄敤
2. 鈥?strong>鈥嬬洃鎺ф參鏌ヨ鈥?/strong>鈥嬶細鐢?code>SHOW PROCESSLIST;鐪嬬湅鍝簺SQL鍦ㄧ（娲嬪伐
3. 鈥?strong>鈥嬪浠斤紒澶囦唤锛佸浠斤紒鈥?/strong>鈥嬶紙閲嶈鐨勪簨鎯呰涓夐亶锛夊缓璁敤mysqldump鍏ㄩ噺澶囦唤

涓夈€佹墜鎶婃墜淇敼姝ラ

鈥?strong>鈥嬪墠鏂归珮鑳斤紒璺熺潃姝ラ璧帮紝缈昏溅姒傜巼闄?0%锛氣€?/strong>鈥?/p>

1. 鈥?strong>鈥嬪厛绀煎悗鍏碘€?/strong>鈥嬶細ALTER TABLE 鐢ㄦ埛琛?DROP INDEX 鏃х储寮曞悕;锛堝垹闄ゆ棫绱㈠紩锛?/li>
2. 鈥?strong>鈥嬮噸寤烘柊绱㈠紩鈥?/strong>鈥嬶細ALTER TABLE 鐢ㄦ埛琛?ADD INDEX 鏂扮储寮曞悕(gender, reg_time);
3. 鈥?strong>鈥嬮獙璇佹晥鏋溾€?/strong>鈥嬶細鐢?code>EXPLAIN SELECT...鐪嬫墽琛岃鍒掓湁娌℃湁璧版柊绱㈠紩

鈥?strong>鈥嬫敞鎰忚繖涓潙锛佲€?/strong>鈥?澶ц〃鐩存帴鍒犵储寮曞彲鑳藉紩鍙戦攣琛紝杩欐椂鍊欏氨寰楃敤ALGORITHM=INPLACE鍦ㄧ嚎淇敼锛?/p>

sql澶嶅埗
ALTER TABLE 澶ф暟鎹〃 
DROP INDEX old_index,
ADD INDEX new_index(col1,col2) 
ALGORITHM=INPLACE;

鍥涖€佸繀鐪嬬殑娉ㄦ剰浜嬮」

1. 鈥?strong>鈥嬫暟鎹噺瓒呰繃500涓団€?/strong>鈥嬶紵寤鸿鍦ㄤ笟鍔′綆宄版湡鎿嶄綔
2. 鈥?strong>鈥嬭仈鍚堢储寮曞瓧娈甸『搴忊€?/strong>鈥嬪氨鍍忔眽鍫″寘缁撴瀯锛?
   • 鏈€宸﹀瓧娈佃鏄珮棰戞煡璇㈡潯浠?/li>
   • 鎺掑簭瀛楁鏀炬渶鍚?/li>
3. 鈥?strong>鈥嬪埆鐩镐俊鐜勫鈥?/strong>鈥嬶紒鏀瑰畬绱㈠紩涓€瀹氳鐢ㄧ湡瀹炴暟鎹祴璇?/li>
4. 鈥?strong>鈥嬬洃鎺х鐩樼┖闂粹€?/strong>鈥嬶細鏈変簺鎯呭喌涓嬬储寮曢噸寤轰細涓存椂鍗犵敤鍙屽€嶇┖闂?/li>

浜斻€佸父瑙侀棶棰樼瓟鐤?/h3>

鈥?strong>鈥婹锛氫慨鏀圭储寮曚細瀵艰嚧鏁版嵁涓㈠け鍚楋紵鈥?/strong>鈥?br/> A锛氬彧瑕佷笉鍒犺〃灏变笉浼氾紒浣嗘敼绱㈠紩鏈熼棿鍙兘鏈夌煭鏆傞攣琛紝寤鸿鐢╬t-online-schema-change宸ュ叿

鈥?strong>鈥婹锛氫负浠€涔堟垜鏀逛簡绱㈠紩鏌ヨ鍙嶈€屾洿鎱簡锛熲€?/strong>鈥?br/> A锛氬叓鎴愭槸娌¤蛋鏂扮储寮曪紝鐢?code>FORCE INDEX(绱㈠紩鍚?寮哄埗鎸囧畾璇曡瘯

鈥?strong>鈥婹锛氫慨鏀圭储寮曞拰鍒犻櫎閲嶅缓鏈夊暐鍖哄埆锛熲€?/strong>鈥?br/> A锛氱洿鎺ュ姣旂粰浣犵湅锛?/p>

鎿嶄綔鏂瑰紡	鎵ц閫熷害	閿佽〃椋庨櫓	閫傜敤鍦烘櫙
ALTER鐩存帴淇敼	蹇?/td>	楂?/td>	灏忚〃/绱ф€ヤ慨鏀?/td>
鍒犻櫎鍚庨噸寤?/td>	鎱?/td>	浣?/td>	澶ц〃/闈炴牳蹇冧笟鍔?/td>

---

涓汉瑙傜偣鏃堕棿

璇村彞鎺忓績绐濆瓙鐨勮瘽锛屽緢澶欴BA鎶婄储寮曞綋浜插効瀛愪竴鏍蜂緵鐫€锛屽叾瀹炩€?strong>鈥嬬储寮曞氨鍍忕┛琛ｆ湇鈥斺€斿緱璺熺潃涓氬姟闇€姹傛崲瀛ｈ妭鈥?/strong>鈥嬨€傛垜瑙佽繃鏈€绂昏氨鐨勬渚嬫槸锛氭湁涓數鍟嗗钩鍙扮‖鎵涚潃涓嶄慨鏀逛笁骞村墠鐨勭储寮曠粨鏋勶紝缁撴灉澶т績鏃舵暟鎹簱鐩存帴韬哄钩銆傝浣忓晩鏈嬪弸浠細鈥?strong>鈥嬫病鏈変竴鍔虫案閫哥殑绱㈠紩锛屽彧鏈変笌鏃朵勘杩涚殑浼樺寲鈥?/strong>鈥嬶紒

鏈€鍚庡敔鍙ㄤ竴鍙ワ細鏀圭储寮曚笉鍙€曪紝鍙€曠殑鏄洸鐩搷浣溿€傛寜鐓т粖澶╄鐨勬楠よ蛋锛屼繚绠′綘鏀瑰緱绋冲鑰佺嫍銆傝鏄亣鍒版悶涓嶅畾鐨勬儏鍐碉紝鍒ご閾侊紝璧剁揣rollback澶囦唤锛屽挶涓嶄涪浜猴紒