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镄勫熀链浣跨敤鍒嗘瀽