sql定期執行的命令

Ⅰ MySql涓濡備綍浣跨敤explain鏌ヨSQL鐨勬墽琛岃″垝

explain鍛戒護鏄鏌ョ湅鏌ヨ浼樺寲鍣ㄥ備綍鍐沖畾鎵ц屾煡璇㈢殑涓昏佹柟娉曘

榪欎釜鍔熻兘鏈夊矓闄愭э紝騫朵笉鎬諱細璇村嚭鐪熺浉錛屼絾瀹冪殑杈撳嚭鏄鍙浠ヨ幏鍙栫殑鏈濂戒俊鎮錛屽煎緱鑺辨椂闂村幓浜嗚В錛屽洜涓哄彲浠ュ︿範鍒版煡璇㈡槸濡備綍鎵ц岀殑銆

1銆佷粈涔堟槸MySQL鎵ц岃″垝

瑕佸規墽琛岃″垝鏈変釜姣旇緝濂界殑鐞嗚В錛岄渶瑕佸厛瀵筂ySQL鐨勫熀紜緇撴瀯鍙婃煡璇㈠熀鏈鍘熺悊鏈夌畝鍗曠殑浜嗚В銆

MySQL鏈韜鐨勫姛鑳芥灦鏋勫垎涓轟笁涓閮ㄥ垎錛屽垎鍒鏄 搴旂敤灞傘侀昏緫灞傘佺墿鐞嗗眰錛屼笉鍙鏄疢ySQL 錛屽叾浠栧ぇ澶氭暟鏁版嵁搴撲駭鍝侀兘鏄鎸夎繖縐嶆灦鏋勬潵榪涜屽垝鍒嗙殑銆

搴旂敤灞傦紝涓昏佽礋璐ｄ笌瀹㈡埛絝榪涜屼氦浜掞紝寤虹珛閾炬帴錛岃頒綇閾炬帴鐘舵侊紝榪斿洖鏁版嵁錛屽搷搴旇鋒眰錛岃繖涓灞傛槸鍜屽㈡埛絝鎵撲氦閬撶殑銆

閫昏緫灞傦紝涓昏佽礋璐ｆ煡璇㈠勭悊銆佷簨鍔＄＄悊絳夊叾浠栨暟鎹搴撳姛鑳藉勭悊錛屼互鏌ヨ涓轟緥銆

棣栧厛鎺ユ敹鍒版煡璇SQL涔嬪悗錛屾暟鎹搴撲細絝嬪嵆鍒嗛厤涓涓綰跨▼瀵瑰叾榪涜屽勭悊錛岀涓姝ユ煡璇㈠勭悊鍣ㄤ細瀵筍QL鏌ヨ㈣繘琛屼紭鍖栵紝浼樺寲鍚庝細鐢熸垚鎵ц岃″垝錛岀劧鍚庝氦鐢辮″垝鎵ц屽櫒鏉ユ墽琛屻

璁″垝鎵ц屽櫒闇瑕佽塊棶鏇村簳灞傜殑浜嬪姟綆＄悊鍣錛屽瓨鍌ㄧ＄悊鍣ㄦ潵鎿嶄綔鏁版嵁錛屼粬浠鍚勮嚜鐨勫垎宸ュ悇鏈変笉鍚岋紝鏈緇堥氳繃璋冪敤鐗╃悊灞傜殑鏂囦歡鑾峰彇鍒版煡璇㈢粨鏋勪俊鎮錛屽皢鏈緇堢粨鏋滃搷搴旂粰搴旂敤灞傘

鐗╃悊灞傦紝瀹為檯鐗╃悊紓佺洏涓婂瓨鍌ㄧ殑鏂囦歡錛屼富瑕佹湁鍒嗘枃鏁版嵁鏂囦歡錛屾棩蹇楁枃浠躲


閫氳繃涓婇潰鐨勬弿榪幫紝鐢熸垚鎵ц岃″垝鏄鎵ц屼竴鏉SQL蹇呬笉鍙灝戠殑姝ラわ紝涓鏉SQL鎬ц兘鐨勫ソ鍧忥紝鍙浠ラ氳繃鏌ョ湅鎵ц岃″垝寰堢洿瑙傜殑鐪嬪嚭鏉ワ紝鎵ц岃″垝鎻愪緵浜嗗悇縐嶆煡璇㈢被鍨嬩笌綰у埆錛屾柟闈㈡垜浠榪涜屾煡鐪嬩互鍙婁負浣滀負鎬ц兘鍒嗘瀽鐨勪緷鎹銆


2銆佸備綍鍒嗘瀽鎵ц岃″垝

MySQL涓烘垜浠鎻愪緵浜 explain 鍏抽敭瀛楁潵鐩磋傜殑鏌ョ湅涓鏉SQL鐨勬墽琛岃″垝銆

explain鏄劇ず浜哅ySQL濡備綍浣跨敤緔㈠紩鏉ュ勭悊select璇鍙ヤ互鍙婅繛鎺ヨ〃錛屽彲浠ュ府鍔╅夋嫨鏇村ソ鐨勭儲寮曞拰鍐欏嚭鏇翠紭鍖栫殑鏌ヨ㈣鍙ャ

涓嬮潰鎴戜滑浣跨敤 explain 鍋氫竴涓鏌ヨ錛屽備笅錛

mysql> explain select * from payment;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| 1 | SIMPLE | payment | NULL | ALL | NULL | NULL | NULL | NULL | 16086 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.01 sec)
鏌ヨ㈢粨鏋勪腑鏈12鍒楋紝鐞嗚В姣忎竴鍒楃殑鍚涔夛紝瀵圭悊瑙ｆ墽琛岃″垝鑷沖叧閲嶈侊紝涓嬮潰鐢ㄤ竴涓琛ㄦ牸鐨勫艦寮忚繘琛岃存槑銆



鍒楀悕
璇存槑




id
SELECT璇嗗埆絎︼紝榪欐槸SELECT鐨勬煡璇㈠簭鍒楀彿銆


select_type

SELECT綾誨瀷,鍙浠ヤ負浠ヤ笅浠諱綍涓縐:



SIMPLE:綆鍗昐ELECT(涓嶄嬌鐢║NION鎴栧瓙鏌ヨ)



PRIMARY:鏈澶栭潰鐨凷ELECT



UNION:UNION涓鐨勭浜屼釜鎴栧悗闈㈢殑SELECT璇鍙



DEPENDENT UNION:UNION涓鐨勭浜屼釜鎴栧悗闈㈢殑SELECT璇鍙,鍙栧喅浜庡栭潰鐨勬煡璇



UNION RESULT:UNION 鐨勭粨鏋



SUBQUERY:瀛愭煡璇涓鐨勭涓涓猄ELECT



DEPENDENT SUBQUERY:瀛愭煡璇涓鐨勭涓涓猄ELECT,鍙栧喅浜庡栭潰鐨勬煡璇



DERIVED:瀵煎嚭琛ㄧ殑SELECT(FROM瀛愬彞鐨勫瓙鏌ヨ)






table
杈撳嚭鐨勮屾墍寮曠敤鐨勮〃


partitions
濡傛灉鏌ヨ㈡槸鍩轟簬鍒嗗尯琛ㄧ殑璇濓紝鏄劇ず鏌ヨ㈠皢璁塊棶鐨勫垎鍖恆


type

鑱旀帴綾誨瀷銆備笅闈㈢粰鍑哄悇縐嶈仈鎺ョ被鍨,鎸夌収浠庢渶浣崇被鍨嬪埌鏈鍧忕被鍨嬭繘琛屾帓搴:



system:琛ㄤ粎鏈変竴琛(=緋葷粺琛)銆傝繖鏄痗onst鑱旀帴綾誨瀷鐨勪竴涓鐗逛緥銆



const:琛ㄦ渶澶氭湁涓涓鍖歸厤琛,瀹冨皢鍦ㄦ煡璇㈠紑濮嬫椂琚璇誨彇銆傚洜涓轟粎鏈変竴琛,鍦ㄨ繖琛岀殑鍒楀煎彲琚浼樺寲鍣ㄥ墿浣欓儴鍒嗚や負鏄甯告暟銆俢onst琛ㄥ緢蹇,鍥犱負瀹冧滑鍙璇誨彇涓嬈!



eq_ref:瀵逛簬姣忎釜鏉ヨ嚜浜庡墠闈㈢殑琛ㄧ殑琛岀粍鍚,浠庤ヨ〃涓璇誨彇涓琛屻傝繖鍙鑳芥槸鏈濂界殑鑱旀帴綾誨瀷,闄や簡const綾誨瀷銆



ref:瀵逛簬姣忎釜鏉ヨ嚜浜庡墠闈㈢殑琛ㄧ殑琛岀粍鍚,鎵鏈夋湁鍖歸厤緔㈠紩鍊肩殑琛屽皢浠庤繖寮犺〃涓璇誨彇銆



ref_or_null:璇ヨ仈鎺ョ被鍨嬪傚悓ref,浣嗘槸娣誨姞浜哅ySQL鍙浠ヤ笓闂ㄦ悳緔㈠寘鍚玁ULL鍊肩殑琛屻



index_merge:璇ヨ仈鎺ョ被鍨嬭〃紺轟嬌鐢ㄤ簡緔㈠紩鍚堝苟浼樺寲鏂規硶銆



unique_subquery:璇ョ被鍨嬫浛鎹浜嗕笅闈㈠艦寮忕殑IN瀛愭煡璇㈢殑ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery鏄涓涓緔㈠紩鏌ユ壘鍑芥暟,鍙浠ュ畬鍏ㄦ浛鎹㈠瓙鏌ヨ,鏁堢巼鏇撮珮銆



index_subquery:璇ヨ仈鎺ョ被鍨嬬被浼間簬unique_subquery銆傚彲浠ユ浛鎹IN瀛愭煡璇,浣嗗彧閫傚悎涓嬪垪褰㈠紡鐨勫瓙鏌ヨ涓鐨勯潪鍞涓緔㈠紩: value IN (SELECT key_column FROM single_table WHERE some_expr)



range:鍙媯緔㈢粰瀹氳寖鍥寸殑琛,浣跨敤涓涓緔㈠紩鏉ラ夋嫨琛屻



index:璇ヨ仈鎺ョ被鍨嬩笌ALL鐩稿悓,闄や簡鍙鏈夌儲寮曟爲琚鎵鎻忋傝繖閫氬父姣擜LL蹇,鍥犱負緔㈠紩鏂囦歡閫氬父姣旀暟鎹鏂囦歡灝忋



ALL:瀵逛簬姣忎釜鏉ヨ嚜浜庡厛鍓嶇殑琛ㄧ殑琛岀粍鍚,榪涜屽畬鏁寸殑琛ㄦ壂鎻忥紝璇存槑鏌ヨ㈠氨闇瑕佷紭鍖栦簡銆



涓鑸鏉ヨ達紝寰椾繚璇佹煡璇㈣嚦灝戣揪鍒皉ange綰у埆錛屾渶濂借兘杈懼埌ref銆




possible_keys
鎸囧嚭MySQL鑳戒嬌鐢ㄥ摢涓緔㈠紩鍦ㄨヨ〃涓鎵懼埌琛


key
鏄劇ずMySQL瀹為檯鍐沖畾浣跨敤鐨勯敭(緔㈠紩)銆傚傛灉娌℃湁閫夋嫨緔㈠紩,閿鏄疦ULL銆


key_len
鏄劇ずMySQL鍐沖畾浣跨敤鐨勯敭闀垮害銆傚傛灉閿鏄疦ULL,鍒欓暱搴︿負NULL銆傚湪涓嶆崯澶辯簿紜鎬х殑鎯呭喌涓嬶紝闀垮害瓚婄煭瓚婂ソ


ref
鏄劇ず浣跨敤鍝涓鍒楁垨甯告暟涓巏ey涓璧蜂粠琛ㄤ腑閫夋嫨琛屻


rows
鏄劇ずMySQL璁や負瀹冩墽琛屾煡璇㈡椂蹇呴』媯鏌ョ殑琛屾暟銆傚氳屼箣闂寸殑鏁版嵁鐩鎬箻鍙浠ヤ及綆楄佸勭悊鐨勮屾暟銆


filtered
鏄劇ず浜嗛氳繃鏉′歡榪囨護鍑虹殑琛屾暟鐨勭櫨鍒嗘瘮浼拌″箋


Extra

璇ュ垪鍖呭惈MySQL瑙ｅ喅鏌ヨ㈢殑璇︾粏淇℃伅



Distinct:MySQL鍙戠幇絎1涓鍖歸厤琛屽悗,鍋滄涓哄綋鍓嶇殑琛岀粍鍚堟悳緔㈡洿澶氱殑琛屻



Select tables optimized awayMySQL鏍規湰娌℃湁閬嶅巻琛ㄦ垨緔㈠紩灝辮繑鍥炴暟鎹浜嗭紝琛ㄧず宸茬粡浼樺寲鍒頒笉鑳藉啀浼樺寲浜



Not exists:MySQL鑳藉熷規煡璇㈣繘琛孡EFT JOIN浼樺寲,鍙戠幇1涓鍖歸厤LEFT JOIN鏍囧噯鐨勮屽悗,涓嶅啀涓哄墠闈㈢殑鐨勮岀粍鍚堝湪璇ヨ〃鍐呮鏌ユ洿澶氱殑琛屻



range checked for each record (index map: #):MySQL娌℃湁鍙戠幇濂界殑鍙浠ヤ嬌鐢ㄧ殑緔㈠紩,浣嗗彂鐜板傛灉鏉ヨ嚜鍓嶉潰鐨勮〃鐨勫垪鍊煎凡鐭,鍙鑳介儴鍒嗙儲寮曞彲浠ヤ嬌鐢ㄣ



Using filesort:MySQL闇瑕侀濆栫殑涓嬈′紶閫,浠ユ壘鍑哄備綍鎸夋帓搴忛『搴忔緔㈣岋紝璇存槑鏌ヨ㈠氨闇瑕佷紭鍖栦簡銆



Using index:浠庡彧浣跨敤緔㈠紩鏍戜腑鐨勪俊鎮鑰屼笉闇瑕佽繘涓姝ユ悳緔㈣誨彇瀹為檯鐨勮屾潵媯緔㈣〃涓鐨勫垪淇℃伅銆



Using temporary:涓轟簡瑙ｅ喅鏌ヨ,MySQL闇瑕佸壋寤轟竴涓涓存椂琛ㄦ潵瀹圭撼緇撴灉錛岃存槑鏌ヨ㈠氨闇瑕佷紭鍖栦簡銆



Using where:WHERE 瀛愬彞鐢ㄤ簬闄愬埗鍝涓涓琛屽尮閰嶄笅涓涓琛ㄦ垨鍙戦佸埌瀹㈡埛銆



Using sort_union(...), Using union(...), Using intersect(...):榪欎簺鍑芥暟璇存槑濡備綍涓篿ndex_merge鑱旀帴綾誨瀷鍚堝苟緔㈠紩鎵鎻忋



Using index for group-by:綾諱技浜庤塊棶琛ㄧ殑Using index鏂瑰紡,Using index for group-by琛ㄧずMySQL鍙戠幇浜嗕竴涓緔㈠紩,鍙浠ョ敤鏉ユ煡 璇GROUP BY鎴朌ISTINCT鏌ヨ㈢殑鎵鏈夊垪,鑰屼笉瑕侀濆栨悳緔㈢‖鐩樿塊棶瀹為檯鐨勮〃銆








鎬葷粨

浠ヤ笂鎵榪版槸灝忕紪緇欏ぇ瀹朵粙緇嶇殑MySql涓濡備綍浣跨敤 explain 鏌ヨ SQL 鐨勬墽琛岃″垝錛屽笇鏈涘瑰ぇ瀹舵湁鎵甯鍔╋紝濡傛灉澶у舵湁浠諱綍鐤戦棶璇風粰鎴戠暀璦錛屽皬緙栦細鍙婃椂鍥炲嶅ぇ瀹剁殑銆傚湪姝や篃闈炲父鎰熻阿澶у跺硅剼鏈涔嬪剁綉絝欑殑鏀鎸侊紒



鎮ㄥ彲鑳芥劅鍏磋叮鐨勬枃絝:MySQL鏌ヨ浼樺寲涔媏xplain鐨勬繁鍏ヨВ鏋恗ysql涓璭xplain鐢ㄦ硶璇﹁Вmysql鎬葷粨涔媏xplainMySQL鎬ц兘鍒嗘瀽鍙奺xplain鐨勪嬌鐢ㄨ存槑Mysql涓璭xplain浣滅敤璇﹁ВMysql涔婨XPLAIN鏄劇ずusing filesort浠嬬粛MySQL涓閫氳繃EXPLAIN濡備綍鍒嗘瀽SQL鐨勬墽琛岃″垝璇﹁ВMYSQL explain 鎵ц岃″垝璇﹁ВMySQL涓璄XPLAIN瑙ｉ噴鍛戒護鍙婄敤娉曡茶ВMySQL鎬ц兘浼樺寲紲炲櫒Explain鐨勫熀鏈浣跨敤鍒嗘瀽