mysql基础优化.docVIP

mysql基础优化 show [session | global] status;(榛樿鏄剧ずmysql鏁版嵁搴撶姸鎬? show status like Com% = show session status like Com%;鏄剧ず褰撳墠鎺у埗鍙扮殑鎯呭喌; Show global status like Com%;鏄剧ず鏁版嵁搴撲粠鍚姩鍒板綋鍓?鏌ヨ銆佸垹闄ゃ€乻how鈥?鐨勬鏁? Show status like connections;鏄剧ず璇曞浘杩炴帴mysql鏈嶅姟鍣ㄧ殑娆℃暟; Show statue like uptime;鏄剧ず鏈嶅姟鍣ㄥ伐浣滄椂闂?绉?; Show status like slow_query;鏄剧ず鎱㈡煡璇㈡鏁?榛樿涓烘參鏌ヨ Show status like Handler_read%;鐜板疄绱㈠紩浣跨敤鐘舵€? Show variables;鏄剧ず,mysql杩愯鍙橀噺: Show variables like long_query_time;//鏌ョ湅鎱㈡煡璇㈣缃椂闂? show variables like %char%;//鏌ョ湅瀛楃闆嗕俊鎭? Show table status like %tableName% \G鏌ョ湅琛ㄧ姸鎬?; 鏁版嵁搴撹璁′紭鍖? 浣跨敤procedure analyse();鍑芥暟瀵瑰綋鍓嶅簲鐢ㄧ殑琛ㄥ垎鏋?璇ュ嚱鏁颁細瀵规暟鎹〃绫诲瀷鎻愬嚭浼樺寲寤鸿鐗瑰埆鏄〃涓殑鏁版嵁姣旇緝澶氱殑鎯呭喌)璇硶select * from tbl_name PROCEDURE ANALYSE(); 瀵逛簬(MD5/SHA1)鍔犲瘑鐨勫瓧娈?濡傚瘑鐮?,鐢变簬鍏堕暱搴﹀缁堝浐瀹?鍙互浣跨敤char瀹氶暱绫诲瀷(char(32)/char(40)); 姘歌繙涓烘瘡涓€涓〃璁捐涓€涓狪D(寤鸿浣跨敤 unsigend int); enum(杩樻湁set)鏄潪甯稿揩鑰屼笖绱у噾鐨?淇濆瓨鐨勬槸tinyint,琛ㄧ幇涓哄瓧绗︿覆),涓€浜涘瓧娈?濡?鎬у埆,鍥藉姘戞棌,鐘舵€?,閮ㄩ棬...)杩欎簺瀛楁鐨勫彇鍊间紭鍏堝苟涓斿浐瀹?閭ｄ箞灏卞簲璇ヤ娇鐢╡num鑰屼笉鏄痸archar; 灏藉彲鑳戒娇鐢∟OT NULL; 鍙橀暱琛?鍚湁varchar 銆乼ext銆乥lob鍙橀暱瀛楁鐨勮〃)鏁堢巼娌℃湁鍥哄畾闀垮害闈欐€佽〃鎼?闇€瑕佸畾鏈熺敤OPTIMIZE TABLE浼樺寲; 浣?鍥洪暱琛?浼氭氮璐逛竴瀹氱殑绌洪棿(浣跨敤鍨傜洿鍒嗗壊) 鍨傜洿鍒嗗壊: 灏嗛渶瑕佸彉闀跨殑瀛楁鏀惧湪涓€涓〃,涓嶉渶瑕佺殑鏀惧彟涓€涓〃; 缁忓父鏌ヨ鐨勫瓧娈垫斁鍦ㄤ竴涓〃,涓嶇粡甯告煡璇㈢殑瀛楁鏀惧湪鍙︿竴涓〃(濡傛枃绔犺〃:鏂囩珷淇℃伅鏀句竴涓〃,鏂囩珷鍐呭鏀惧彟涓€涓〃); 姘村钩鍒嗗壊(鏁版嵁鍒嗗涓〃鍌ㄥ瓨,搴旂敤鎯呭喌): 琛ㄥ緢澶? 琛ㄤ腑鏁版嵁鍏锋湁鐙珛鎬?濡?鏈堢數璇濆崟,6鏈堢數璇濆崟); 闇€瑕佸瓨鏀句笉鍚屼粙璐? 閫嗚鑼冨寲(涓嶉伒寰?鑼冨紡):濡?A琛ㄤ腑瀛樻斁鐢ㄦ埛鐨勫鍚嶅拰灞炴€?B琛ㄤ腑瀛樻斁鐫€鍏跺搴旂殑璐﹀崟淇℃伅,杩欐椂灏卞繀椤昏繛鎺ユ潵鑾峰緱A琛ㄤ腑鐢ㄦ埛鍚?閭ｄ箞灏卞彲浠ュ湪B琛ㄤ腑涔熷缓绔嬬敤鎴峰鍚嶇殑鍒椻€?鍙互鐢ㄨЕ鍙戝櫒瑙ｅ喅鏇存柊闂); 浣跨敤涓棿琛ㄦ潵鎻愰珮缁熻鏌ヨ閫熷害:寤虹珛鍜屽師琛ㄧ粨鏋勪竴鏍风殑涓棿琛?瀛樻斁鐗瑰畾鏉′欢淇℃伅浠ヤ究鏌ヨ,铏界劧鍦ㄥ垱寤哄拰鎻掑叆鏁版嵁涓婁細娴垂涓嶅皯鏃堕棿,浣嗗浠ュ悗鐗瑰畾鏉′欢鏌ヨ閫熷害鎻愰珮寰堝; 绱㈠紩: 鍒涘缓鍘熷垯 鍙互鐢╡xplain鍛戒护鍒嗘瀽select鏁堢巼浣庝笅鐨勫師鍥?explain select...)鍙婄储寮曠殑浣跨敤鎯呭喌 ; Explain select 鈥?\G Select_type:琛ㄧずselect鐨勭被鍨? Table:杈撳嚭缁撴灉闆嗙殑琛? Type:琛ㄧず琛ㄧ殑杩炴帴绫诲瀷;(system,鏈€浼?all,琛ㄧず瀵逛簬鍓嶉潰鐨勬瘡涓€琛?閮介€氳繃鍏ㄨ〃鎵弿鏉ュ緱鍒? Possible_keys:鏌ヨ琛ㄥ彲鑳戒娇鐢ㄧ殑绱㈠紩; Key:瀹為檯浣跨敤鍒扮殑绱㈠紩; Key_len:绱㈠紩瀛楁闀垮害; Rows:鎵弿琛岀殑鏁伴噺锛? Extra:鎵ц鎯呭喌鐨勮鏄庡拰鎻忚堪; 鎼滅储鐨勭储寮曞垪--鏈€闇€瑕佺储寮曠殑鍒楁槸鍑虹幇鍦╳here瀛楀彞鎴栬€呮槸杩炴帴瀛楀彞涓殑鍒楋紝鑰屼笉鏄嚭鐜板湪select鍚庣殑鍒? 浣跨敤鍞竴绱㈠紩--绱㈠紩鐨勫熀鏁拌秺澶?瓒婃帴杩戝敮涓€,鍒椾腑鐨勫€煎熀鏈兘涓嶇浉鍚?,绱㈠紩鏁堟灉瓒婂ソ; 浣跨敤鐭储寮?-鍦ㄥ瀛楃涓插垪杩涜绱㈠紩,搴旇