发表于 2009-1-5 19:48
| 
MySQL鎬ц兘浼樺寲鍏ㄦ敾鐣ワ紙4锛夌浉鍏虫暟鎹簱鍛戒护
鍙戝竷: 2008-7-01 09:02 | 浣滆: admin | 鏌ョ湅: 26娆
MySQL鎬ц兘浼樺寲鍏ㄦ敾鐣ワ紙4锛夌浉鍏虫暟鎹簱鍛戒护
銆愬瓧浣擄細灏 澶с
MySQL鎬ц兘浼樺寲鍏ㄦ敾鐣ワ紙4锛夌浉鍏虫暟鎹簱鍛戒护
浣滆咃細涓嶈 鏂囩珷鏉ユ簮锛氱綉缁 鐐瑰嚮鏁帮細413 鏇存柊鏃堕棿锛2007-1-21 
鎺ヤ笅鏉ユ垜浠璁ㄨ鐨勬槸鏁版嵁搴撴ц兘浼樺寲鐨勫彟涓鏂归潰锛屽嵆杩愮敤鏁版嵁搴撴湇鍔″櫒鍐呭缓鐨勫伐鍏疯緟鍔╂ц兘鍒嗘瀽鍜屼紭鍖栥
銆銆 鈻 SHOW
銆銆 鎵ц涓嬮潰杩欎釜鍛戒护鍙互浜嗚В鏈嶅姟鍣ㄧ殑杩愯鐘舵侊細
MySQL >show status;
銆銆 璇ュ懡浠ゅ皢鏄剧ず鍑轰竴闀垮垪鐘舵佸彉閲忓強鍏跺搴旂殑鍊硷紝鍏朵腑鍖呮嫭锛氳涓璁块棶鐨勭敤鎴锋暟閲忥紝琚腑姝㈢殑杩炴帴鏁伴噺锛屽皾璇曡繛鎺ョ殑娆℃暟锛屽苟鍙戣繛鎺ユ暟閲忔渶澶у硷紝浠ュ強鍏朵粬璁稿鏈夌敤鐨勪俊鎭傝繖浜涗俊鎭浜庣‘瀹氱郴缁熼棶棰樺拰鏁堢巼浣庝笅鐨勫師鍥犳槸鍗佸垎鏈夌敤鐨勩
銆銆 SHOW鍛戒护闄や簡鑳藉鏄剧ず鍑篗ySQL鏈嶅姟鍣ㄦ暣浣撶姸鎬佷俊鎭箣澶栵紝瀹冭繕鑳藉鏄剧ず鍑烘湁鍏虫棩蹇楁枃浠躲佹寚瀹氭暟鎹簱銆佽〃銆佺储寮曘佽繘绋嬪拰璁稿彲鏉冮檺琛ㄧ殑瀹濊吹淇℃伅銆傝璁块棶MySQL.com/doc/S/H/SHOW.html">http://www.MySQL.com/doc/S/H/SHOW.html浜嗚В鏇村淇℃伅銆
銆銆 EXPLAIN鑳藉鍒嗘瀽SELECT鍛戒护鐨勫鐞嗚繃绋嬨傝繖涓嶄粎瀵逛簬鍐冲畾鏄惁瑕佷负琛ㄥ姞涓婄储寮曞緢鏈夌敤锛岃屼笖瀵逛簬浜嗚ВMySQL澶勭悊澶嶆潅杩炴帴鐨勮繃绋嬩篃寰堟湁鐢ㄣ
銆銆 涓嬮潰杩欎釜渚嬪瓙鏄剧ず浜嗗浣曠敤EXPLAIN鎻愪緵鐨勪俊鎭愭鍦颁紭鍖栬繛鎺ユ煡璇€傦紙鏈緥鏉ヨ嚜MySQL鏂囨。锛岃http://www.mysql.com/doc/E/X/EXPLAIN.html銆傚師鏂囧啓鍒拌繖閲屼技涔庢湁鐐规溅鑽変簡浜嬶紝鐗瑰姞涓婃渚嬨傦級
銆銆 鍋囧畾鐢‥XPLAIN鍒嗘瀽鐨凷ELECT鍛戒护濡備笅鎵绀猴細
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
tt.ProjectReference, tt.EstimatedShipDate,
tt.ActualShipDate, tt.ClientID,
tt.ServiceCodes, tt.RepetitiveID,
tt.CurrentProcess, tt.CurrentDPPerson,
tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
et_1.COUNTRY, do.CUSTNAME
FROM tt, et, et AS et_1, do
WHERE tt.SubmitTime IS NULL
AND tt.ActualPC = et.EMPLOYID
AND tt.AssignedPC = et_1.EMPLOYID
AND tt.ClientID = do.CUSTNMBR;
銆銆 SELECT鍛戒护涓嚭鐜扮殑琛ㄥ畾涔夊涓嬶細
銆銆 鈥昏〃瀹氫箟
琛 鍒 鍒楃被鍨
tt ActualPC CHAR(10)
tt AssignedPC CHAR(10)
tt ClientID CHAR(10)
et EMPLOYID CHAR(15)
do CUSTNMBR CHAR(15)
鈥荤储寮
琛 绱㈠紩
tt ActualPC
tt AssignedPC
tt ClientID
et EMPLOYID (涓婚敭)
do CUSTNMBR (涓婚敭)
銆銆 鈥籺t.ActualPC鍊煎垎甯冧笉鍧囧寑
銆銆 鍦ㄨ繘琛屼换浣曚紭鍖栦箣鍓嶏紝EXPLAIN瀵筍ELECT鎵ц鍒嗘瀽鐨勭粨鏋滃涓嬶細
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
do ALL PRIMARY NULL NULL NULL 2135
et_1 ALL PRIMARY NULL NULL NULL 74
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872
range checked for each record (key map: 35)
銆銆 姣忎竴涓〃鐨則ype閮芥槸ALL锛屽畠琛ㄦ槑MySQL涓烘瘡涓涓〃杩涜浜嗗畬鍏ㄨ繛鎺ワ紒杩欎釜鎿嶄綔鏄浉褰撹楁椂鐨勶紝鍥犱负寰呭鐞嗚鐨勬暟閲忚揪鍒版瘡涓涓〃琛屾暟鐨勪箻绉紒鍗筹紝杩欓噷鐨勬诲鐞嗚鏁颁负74 * 2135 * 74 * 3872 = 45,268,558,720銆
銆銆 杩欓噷鐨勯棶棰樹箣涓鍦ㄤ簬锛屽鏋滄暟鎹簱鍒楃殑澹版槑涓嶅悓锛孧ySQL锛堣繕锛変笉鑳芥湁鏁堝湴杩愮敤鍒楃殑绱㈠紩銆傚湪杩欎釜闂涓婏紝VARCHAR鍜孋HAR鏄竴鏍风殑锛岄櫎闈炲畠浠０鏄庣殑闀垮害涓嶅悓銆傜敱浜巘t.ActualPC澹版槑涓篊HAR(10)锛岃宔t.EMPLOYID澹版槑涓篊HAR(15)锛屽洜姝よ繖閲屽瓨鍦ㄥ垪闀垮害涓嶅尮閰嶉棶棰樸
銆銆 涓轰簡瑙ｅ喅杩欎袱涓垪鐨勯暱搴︿笉鍖归厤闂锛岀敤ALTER TABLE鍛戒护鎶夾ctualPC鍒椾粠10涓瓧绗︽墿灞曞埌15瀛楃锛屽涓嬫墍绀猴細
mysql > ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
鐜板湪tt.ActualPC鍜宔t.EMPLOYID閮芥槸VARCHAR(15)浜嗭紝鎵цEXPLAIN杩涜鍒嗘瀽寰楀埌鐨勭粨鏋滃涓嬫墍绀猴細
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used
do ALL PRIMARY NULL NULL NULL 2135
range checked for each record (key map: 1)
et_1 ALL PRIMARY NULL NULL NULL 74
range checked for each record (key map: 1)
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
銆銆 杩欒繕绠椾笉涓婂畬缇庯紝浣嗗凡缁忓ソ澶氫簡锛堣鏁扮殑涔樼Н鐜板湪灏戜簡涓涓郴鏁74锛夈傜幇鍦ㄨ繖涓猄QL鍛戒护鎵ц澶ф闇瑕佹暟绉掗挓鏃堕棿銆
銆銆 涓轰簡閬垮厤tt.AssignedPC = et_1.EMPLOYID浠ュ強tt.ClientID = do.CUSTNMBR姣旇緝涓殑鍒楅暱搴︿笉鍖归厤锛屾垜浠彲浠ヨ繘琛屽涓嬫敼鍔細
