鍐欏湪鍓嶉潰馃搶Xmind鏂囦欢鑾峰彇锛欸itHub鎸佺画鏇存柊锛屽埆蹇樹簡star~@TOC銆奐ava瀛︿範+闈㈣瘯鎸囧崡銆嬫€濈淮瀵煎浘锛岃绠楁満鑷鎸囧崡锛屽寘鎷琂ava鍩虹銆丣VM銆佹暟鎹簱銆乵ysql銆乺edis銆佽绠楁満缃戠粶銆佺畻娉曘€佹暟鎹粨鏋勩€佹搷浣滅郴缁熺瓑锛屽悗鍙版妧鏈爤/鏋舵瀯甯堜箣璺?鍏╯tack寮€鍙戠ぞ鍖猴紝闃块噷锛岃吘璁紝鐧惧害锛岀編鍥紝浠婃棩澶存潯绛夋槬鎷?绉嬫嫑/鏍℃嫑/闈㈣瘯鎬濈淮瀵煎浘锛坧ng鏍煎紡鍙笅杞芥斁澶э級mysql浜嬪姟鐨勫洓澶х壒鐐癸紙ACID锛夊師瀛愭€ц涔堝叏閮ㄥ畬鎴愭垨鏍规湰涓嶅伐浣淐onsistency澶氫釜浜嬪姟璇诲彇鐩稿悓鏁版嵁鐨勭粨鏋滄槸鐩稿悓鐨勯殧绂汇€傚苟鍙戜簨鍔′箣闂寸殑鏁版嵁搴撴槸鐙珛鐨勩€傛寔涔呭寲鏁版嵁鐨勫彉鍖栧氨鏄寔涔呭寲璇诲彇銆傝剰璇籄璇伙紝B璇伙紝A鍥炴粴锛孊涓嶆纭€傞噸澶嶈A璇?锛孊鍐?锛孉璇?骞昏A璇?锛孊鍒犻櫎1锛孉璇?鍥涗釜闅旂绾у埆READ-UNCOMMITTED锛堣鏈彁浜わ級鍏佽璇绘湭鎻愪氦鐨勬暟鎹?READ-COMMITTED锛堣宸叉彁浜わ級鍏佽璇诲苟鍙戜簨鍔″凡鎻愪氦锛岄槻姝㈣剰璇籖EPEATABLE-READ锛堝彲閲嶅璇伙級澶氭璇诲彇缁撴灉涓€鑷达紝闃叉鑴忚鍜屼笉鍙噸澶嶈SERIALIZABLE锛堝彲搴忓垪鍖栵級渚濇鎵ц閫愪竴鎵ц闅旂鏈哄埗鐨勫疄鐜版槸鍩轰簬閿佹満鍒跺拰骞跺彂璋冨害锛圡VVC锛堝鐗堟湰骞跺彂鎺у埗锛夛紝閫氳繃淇濆瓨淇敼杩囩殑鏃х増鏈俊鎭級Mysql榛樿浣跨敤REPEATABLE_READ闅旂绾у埆锛屽垎甯冨紡浜嬪姟SERIALIZABLE锛堝彲搴忓垪鍖栵級Oracle榛樿浣跨敤READ_COMMITTED闅旂绾у埆閿丷eadUncommitted涓嶉渶瑕佸姞鍏变韩閿侊紝涓嶄細鍜屼慨鏀规暟鎹笂鐨勬帓浠栭攣鍐茬獊銆俁eadCommitted鍔犲叡浜攣锛岃鍙ユ墽琛屽悗閲婃斁鍏变韩閿併€俁epeatableRead闇€瑕佸姞鍏变韩閿侊紝蹇呴』绛夊緟浜嬪姟瀹屾垚銆傚叡浜攣绋嶅悗閲婃斁銆係ERIALIZABLE閿佸畾鏁翠釜閿寖鍥村苟鎸佹湁閿佺洿鍒颁簨鍔″畬鎴愩€傜矑搴﹁〃绾ч攣鐨勪唬浠峰皬锛屽姞閿侀€熷害蹇€傚姞閿佺矑搴﹀ぇ锛屽彂鍑洪攣鍐茬獊鐨勬鐜囨渶楂橈紝骞跺彂搴︽渶浣庛€侻YISAM鍜孖NNODB琛岀骇閿佸紑閿€澶э紝鍔犻攣閫熷害鎱€傞攣绮掑害鏈€灏忥紝閿佸啿绐佹鐜囨渶浣庯紝骞跺彂搴︿篃鏈€楂樸€侷NNODB椤电骇閿佸紑閿€鍜屽姞閿佹椂闂翠粙浜庤〃閿佸拰琛岄攣涔嬮棿銆備竴缁勭浉閭昏褰曠殑骞跺彂琚攣瀹氫竴娆°€傞€氱敤绫诲叡浜攣璇婚攣鍙互鍚屾椂鍔犲涓帓浠栭攣鍐欓攣鍙兘鍔犱竴涓叾浠栫殑鎺掍粬閿侊紝鍏变韩閿侀兘鏄帓浠栭攣杩欑缂栧彿鏈哄埗鎴栬€匔AS绠楁硶瀹炵幇鍐欐瘮杈冨皯鐨勬儏鍐碉紙澶氳鍦烘櫙锛変笁绉嶈寖寮忓熀纭€鐭ヨ瘑绗竴鑼冨紡鍒椾笉鑳藉啀鍒嗙浜岃寖寮忛潪涓婚敭瀹屽叏渚濊禆涓婚敭锛屽苟涓斾笉鑳介儴鍒嗕緷璧栫涓夎寖寮忋€傞潪涓婚敭鍙緷璧栦簬涓婚敭銆?涓嶄緷璧栭潪涓婚敭鏉冮檺琛╱ser鐢ㄦ埛璐﹀彿淇℃伅,鍏ㄥ眬db璐﹀彿姣忎釜鏁版嵁搴撶殑鎿嶄綔鏉冮檺table_priv琛ㄧ骇鎿嶄綔鏉冮檺column_priv鍒楃骇鎿嶄綔鏉冮檺host缁檋ostbinlogstatement淇敼鏁版嵁sql:鍑忓皯鏃ュ織閲忥紝瑙ｅ喅io锛岄渶瑕佷繚瀛樹笂涓嬫枃锛屽嚱鏁颁箣绫荤殑涓嶈兘澶嶅埗銆俽ow璁板綍姣忎竴琛岀殑鍙樺寲锛氬叏閮ㄥ啓涓嬫潵锛屼俊鎭噺澶э紝鏃ュ織閲忓ぇ锛宮ixed涓€鑸敤浜巗tatement锛宺ow涓嶈兘鐢ㄤ簬mysql涓讳粠澶嶅埗銆侻aster灏嗚嚜宸辩殑浜岃繘鍒舵棩蹇椾紶閫掔粰slave锛岃揪鍒颁富浠庢暟鎹竴鑷存€х殑鐩殑鏁版嵁鎭㈠锛氫娇鐢╩ysqlbinlog宸ュ叿鎭㈠鏁版嵁鏁版嵁绫诲瀷鏁村瀷TINYINT銆丼MALLINT銆丮EDIUMINT銆両NT銆丅IGINT锛屽垎鍒唬琛?瀛楄妭銆?瀛楄妭銆?瀛楄妭銆?瀛楄妭銆?瀛楄妭Integer鍔燯NSIGNED琛ㄧず鏃犵鍙锋寚瀹氶暱搴︼細INT(11)鍙奖鍝嶆樉绀哄瓧绗︼紝闇€瑕佸拰UNSIGNEDZEROFILL缁撳悎鎵嶆湁鎰忎箟int(20)鏄剧ず瀛楃鐨勯暱搴︿负20锛屽嵆鏈€澶ф樉绀哄搴︿负20锛屼絾浠嶅崰鐢?瀛楄妭瀛樺偍锛屽瓨鍌ㄨ寖鍥翠笉鍙橈紝int(1)鍜宨nt(20)鐨勫瓨鍌ㄥ拰璁＄畻鐩稿悓锛屼笉褰卞搷鍐呴儴瀛樺偍锛屼絾鍙奖鍝嶆湁zerofill瀹氫箟鐨刬nt锛屽墠闈㈠姞浜嗗灏戜釜0锛屾姤鍛婃樉绀哄疄鏁扮被鍨婦ECIMAL鍙敤浜庡瓨鍌ㄦ瘮鍊糂IGINT涔熸槸涓€涓ぇ鏁存暟锛屽彲浠ュ瓨鍌ㄧ簿纭皬鏁般€備絾鏄紝FLOAT鍜孌OUBLE鍏锋湁鍙栧€艰寖鍥村苟鏀寔浣跨敤鏍囧噯娴偣鏁拌繘琛岃繎浼艰绠椼€傚湪璁＄畻鏃讹紝FLOAT鍜孌OUBLE姣擠ECIMAL鏇撮珮鏁堛€備綘鍙互鎶奃ECIMAL鐞嗚В鎴愪竴涓瓧绗︿覆鏉ヨ繘琛屽鐞嗐€侳LOAT绫诲瀷涓?瀛楄妭锛孌OUBLE绫诲瀷涓?瀛楄妭銆傚瓧绗︿覆绫诲瀷VARCHAR鐢ㄤ簬瀛樺偍鍙橀暱瀛楃涓诧紝姣斿畾闀跨被鍨嬫洿鑺傜渷绌洪棿銆俈ARCHAR浣跨敤棰濆鐨?鎴?涓瓧鑺傛潵瀛樺偍瀛楃涓查暱搴︺€傚綋鍒楅暱搴﹀皬浜?55瀛楄妭鏃讹紝鐢?瀛楄妭琛ㄧず锛屽惁鍒欑敤2瀛楄妭琛ㄧず銆傚綋VARCHAR涓瓨鍌ㄧ殑鍐呭瓒呰繃璁剧疆鐨勯暱搴︽椂锛屽唴瀹瑰皢琚埅鏂€侰HARCHAR鏄畾闀跨殑锛屾牴鎹畾涔夌殑瀛楃涓查暱搴﹀垎閰嶈冻澶熺殑绌洪棿銆侰HAR浼氭牴鎹渶瑕佽ˉ涓婄┖鏍硷紝鏂逛究姣旇緝銆侰HAR閫傚悎瀛樺偍寰堢煭鐨勫瓧绗︿覆锛屾垨鑰呮墍鏈夌殑鍊奸兘鎺ヨ繎鐩稿悓鐨勯暱搴︺€傚綋CHAR涓瓨鍌ㄧ殑鍐呭瓒呰繃璁惧畾鐨勯暱搴︽椂锛屽唴瀹逛篃浼氳鎴柇銆傞暱搴︽槸鍥哄畾鐨勶紝鎵€浠ヨ闂€熷害姣攙archar蹇緢澶氾紝鍗充娇蹇?0%CHAR涔熶笉瀹规槗浜х敓纰庣墖銆傚浜庨潪甯哥煭鐨勫垪锛孋HAR姣擵ARCHAR鐨勫瓨鍌ㄧ┖闂存晥鐜囨洿楂樸€備娇鐢ㄦ椂娉ㄦ剰鍙垎閰嶉渶瑕佺殑绌洪棿銆傝緝闀跨殑鍒楀湪鎺掑簭鏃朵細娑堣€楁洿澶氱殑鍐呭瓨銆傚敖鍙兘閬垮厤浣跨敤TEXT/BLOB绫诲瀷銆傛煡璇㈡椂浼氱敤鍒颁复鏃惰〃锛岄€犳垚涓ラ噸鐨勬€ц兘寮€閿€銆傛€ц兘瑙掑害锛坈har鏇村揩锛夊拰纾佺洏绌洪棿鑺傜渷瑙掑害锛坴archar鏇村皬锛塩hart(10)鍜寁archar(10)琛ㄧず瀛樺偍鏁版嵁鐨勫ぇ灏忥紝鍗冲瓨鍌ㄥ灏戜釜瀛楃char(10)琛ㄧず瀛樺偍10涓畾闀垮瓧绗︼紝濡傛灉灏戜簬10涓瓧绗︼紝鐢ㄧ┖鏍艰ˉ婊★紝澶氬崰瀛樺偍绌洪棿銆倂archar(10)琛ㄧず瀛樺偍10涓彉闀垮瓧绗︼紝鎯虫斁澶氬皯绌烘牸灏辨斁澶氬皯銆傝繖鏄拰char(10)涓€鏍枫€傜┖鏍间笉鍚岋紝char(10)绌烘牸鏄寚鍗犱綅绗︿笉绠楀瓧绗﹀瘑鐮乭ash銆乻alt銆佺敤鎴稩D鍙风瓑瀹氶暱瀛楃涓插簲璇ュ瓨鏀惧湪char涓紝鑰屼笉鏄痸archar锛屽彲浠ヨ妭鐪佺┖闂达紝鎻愰珮妫€绱㈡晥鐜囥€傛棩鏈熸椂闂寸被鍨嬪敖閲忎娇鐢╰imestamp锛岀┖闂存晥鐜囬珮浜巇atetime锛岃€屼笖鏃堕棿鎴崇敤鏁存暟瀛樺偍閫氬父涓嶆柟渚匡紝濡傛灉闇€瑕佸瓨鍌ㄥ井绉掞紝鍙互浣跨敤bigint瀛樺偍.鍏抽敭瀛梚n鍜宔xistsinexternalandinternaltablesforhashconnectionexistsexternal琛ㄥ惊鐜紝姣忔寰幆鏌ヨ鍐呰〃銆備袱涓〃鐨勫ぇ灏忔槸涓€鏍风殑锛屾墍浠ヤ娇鐢╥n鍜宔xists娌℃湁澶ぇ鍖哄埆銆傚鏋滃瓙鏌ヨ琛ㄥぇ锛屽垯浣跨敤exists锛屽鏋滃瓙鏌ヨ琛ㄥ皬锛屽垯浣跨敤innotin銆傞偅涔堬紝鍐呭琛ㄩ兘浼氳鍏ㄦ壂鎻忥紝涓嶄娇鐢ㄧ储寮昻otexists鐨勫瓙鏌ヨ浠嶇劧鏄浉鍚岀殑銆傚彲浠ヤ娇鐢ㄨ〃涓婄殑绱㈠紩UNION鍜孶NIONALLUNIONALL锛岄噸澶嶇殑璁板綍琛屼笉浼氳鍚堝苟銆俇NION鐨勬晥鐜囬珮浜嶶NIONALLdrop銆乨elete鍜宼runcateDelete銆傝〃缁撴瀯浠嶇劧鍙互鍥炴粴锛屽垹闄よ〃涓叏閮ㄦ垨閮ㄥ垎鏁版嵁琛屾瘮杈冩參銆傞渶瑕侀€愯鍒犻櫎Truncate涓嶈兘鍥炴粴琛ㄧ粨鏋勮繕鍦紝鍙互蹇€熷垹闄よ鍒犺〃鐨勬墍鏈夋暟鎹€備笅闄嶄笉鑳藉洖婊氥€備粠鏁版嵁搴撲腑鍒犻櫎涓€涓〃锛屾墍鏈夋暟鎹銆佺储寮曞拰鏉冮檺涔熷皢琚垹闄ゃ€傚垹闄ら€熷害鏈€蹇笉鍐嶉渶瑕佽〃鏃朵娇鐢╠rop锛涘綋浣犳兂鍒犻櫎涓€浜涙暟鎹鏃朵娇鐢╠elete锛涘綋鎮ㄦ兂鍦ㄤ繚鐣欒〃鐨勫悓鏃跺垹闄ゆ墍鏈夋暟鎹椂浣跨敤鎴柇銆侲ngineInnodb寮曟搸ACID浜嬪姟鏀寔琛岀骇閿侊紝楂樺苟鍙戝閿害鏉烮NSERT锛孶PDATE锛孌ELETEB+鏍戠储寮曪紝Innodb鏄储寮曠粍缁囪〃锛岀皣绱㈠紩锛屼富閿储寮曞彾瀛愯妭鐐瑰瓨鍌ㄨ鏁版嵁锛岄潪涓婚敭绱㈠紩鍙跺瓙nodesstore鏈€閲嶈鐨勬槸灏嗕富閿拰鍏朵粬绱㈠紩鍒楁暟鎹搱甯岀储寮曟寜鐓т富閿殑澶у皬渚濇鎻掑叆鍒版洿澶氱殑鍐呭瓨鍜屽瓨鍌ㄤ腑锛屽叾涓撶敤鐨勭紦鍐叉睜浼氬湪涓诲瓨涓缓绔嬬紦瀛樻暟鎹拰鎸囨爣銆侻yIASM寮曟搸鍙互琚帇缂┿€傚瓨鍌ㄧ┖闂村皬鐨凷ELECT锛岃鍐檌nsert涓昏鏄疊+鏍戠储寮曪紝myisam鏄爢琛紝闈炶仛闆嗙储寮曪紝鍙跺瓙鑺傜偣瀛樺偍琛屾暟鎹湴鍧€锛岀劧鍚庡鍧€鍏ㄦ枃绱㈠紩瀛樺偍鍦ㄦ寜鐓ц褰曟彃鍏ラ『搴忕殑鏂囦欢褰㈠紡锛屽湪璺ㄥ钩鍙版暟鎹紶杈撲腑浼氶潪甯告柟渚裤€傜储寮曠殑浼樼偣鍜岀己鐐逛紭鐐瑰拰缂虹偣鍔犲揩妫€绱㈤€熷害浣跨敤浼樺寲鐨勯殣钘忓櫒鎻愰珮绯荤粺鎬ц兘缂虹偣鍒涘缓鍜岀淮鎶ょ储寮曚細鑺辫垂鏃堕棿锛屽姩鎬佺淮鎶や細闄嶄綆娣诲姞/淇敼/鍒犻櫎鐨勬墽琛屾晥鐜囧崰鐢ㄧ墿鐞嗙┖闂翠娇鐢ㄥ満鏅痺hereorderby涓嶄娇鐢ㄥ閮ㄦ帓搴忥紝鍙槸鍙栧嚭绱㈠紩琛ㄤ竴瀹氳寖鍥村唴绱㈠紩瀵瑰簲鐨勬暟鎹甧xplainselect...possible_keys瀹為檯涓婂彲鑳戒娇鐢ㄧ储寮曡鐩栫洿鎺ュ湪绱㈠紩琛ㄤ腑鏌ヨ锛岃€屼笉璁块棶鍘熷鏁版嵁锛堝惁鍒欙紝鍙涓€涓瓧娈垫病鏈夌储寮曞氨浼氳繘琛屽叏琛ㄦ壂鎻忥級绫诲瀷涓婚敭绱㈠紩锛氫笉鍏佽閲嶅锛屼笉鍏佽NULL锛屼竴琛ㄥ敮涓€绱㈠紩锛氫笉鍏佽閲嶅锛孨ULL鍊煎厑璁革紝涓€寮犺〃鍙互鏈夊涓叕鍏盜ndex锛氭病鏈夊敮涓€鎬ч檺鍒讹紝鍏佽瀵筃ULL鍊艰繘琛屽叏鏂囩储寮曘€傛暟鎹粨鏋刡+鏍戜笉浠呭彲浠ョ敤浜?銆?銆?=銆?銆?=銆乥etween绛夋瘮杈冭繍绠楃锛岃繕鍙互鐢ㄤ簬like杩愮畻绗﹂潪缁堢鑺傜偣鍙互鐪嬫垚鏄储寮曢儴鍒嗙殑鍙跺瓙鑺傜偣鍖呭惈鎵€鏈夊叧閿瓧鐨勪俊鎭紝鑰屾彃鍏ュ拰鍒犻櫎鎸囧悜鍖呭惈杩欎簺鍏抽敭瀛楃殑璁板綍鐨勬寚閽堟暟鎹璞″湪鍙跺瓙鑺傜偣涓婂彧鏈変袱涓ご鎸囬拡锛屼竴涓槸鏍戞牴鑺傜偣锛屼竴涓槸閿爜鏈€灏忕殑鍙跺瓙鑺傜偣锛岃嚜鐒舵敮鎸乺ange锛屾帓搴忔棤娉曢伩鍏嶅洖琛ㄦ煡璇㈡暟鎹紝鑱氱皣绱㈠紩鍜岃鐩栫储寮曠洿鎺ラ€氳繃绱㈠紩hash鍙兘鐢ㄤ簬鐐瑰鐐规瘮杈冿紝绛変环鏌ヨ锛屽垱寤洪€熷害鏇村揩鍘熷垯锛屾渶浣冲乏鍓嶇紑鍖归厤鍘熷垯宸茬粡琚悜鍙冲尮閰嶏紝鐩村埌棰戠箒浣跨敤鑼冨洿鏌ヨ浣滀负鏌ヨ鏉′欢鏇存柊棰戠箒瀛楁銆備笉閫傜敤浜庝笉鑳芥湁鏁堝尯鍒嗘暟鎹殑鍒椼€備笉閫傜敤浜庢湁澶栭敭鐨勬暟鎹垪銆傛偍蹇呴』灏藉彲鑳藉湴鎵╁睍绱㈠紩锛屽苟涓斾笉瑕佸垱寤烘柊绱㈠紩銆傚垱寤烘柟娉旵REATETABLEALTERTABLECREATEINDEX涓嶈兘鍒涘缓PRIMARYKEY绱㈠紩鍒犻櫎绱㈠紩altertable琛ㄥ悕dropKEY绱㈠紩鍚嶇櫨涓囩骇鍒犻櫎浼氫骇鐢熷绱㈠紩鏂囦欢鐨勯澶栨搷浣?浼氶檷浣庢柊澧?淇敼/鍒犻櫎鐨勬墽琛屾晥鐜囥€傚彲浠ュ厛鍒犻櫎绱㈠紩锛屽垹闄ら噷闈㈡棤鐢ㄧ殑鏁版嵁锛岀劧鍚庨噸鏂板垱寤虹储寮曘€侭-tree鍜孊+鏍戠殑鍖哄埆鍐呴儴缁撶偣鍙瓨鍌╧ey锛屼竴娆″彲浠ュ湪鍐呭瓨涓煡鎵炬洿澶氱殑keyword锛屽噺灏慖O璇诲啓娆℃暟锛涚敱閾炬潯杩炴帴锛岃寖鍥存煡璇紱瀹冨繀椤婚噰鐢ㄤ粠鏍硅妭鐐瑰埌鍙惰妭鐐圭殑璺緞銆傛煡璇㈡晥鐜囩浉褰撹仛绨囩储寮曞拰闈炶仛绨囩储寮曡仛绨囩储寮曠殑鏁版嵁瀛樺偍鍜岀储寮曟斁鍦ㄩ潪鑱氱皣绱㈠紩涓紝灏嗘暟鎹瓨鍌ㄥ湪绱㈠紩鐨勫崟鐙粨鏋勪腑銆傚湪InnoDB涓紝鍙湁涓婚敭绱㈠紩鏄仛闆嗙储寮曘€傚鏋滄病鏈変富閿紝鍒欓€夋嫨涓€涓€備娇鐢ㄥ敮涓€閿垱寤鸿仛绨囩储寮曞鏋滄病鏈夊敮涓€閿紝鍒欓殣寮忕敓鎴愪竴涓敭鏉ュ垱寤鸿仛绨囩储寮曘€傚湪鑱氱皣绱㈠紩涔嬩笂鍒涘缓鐨勭储寮曠О涓鸿緟鍔╃储寮曪紝瀹冩€绘槸闇€瑕佷簩娆℃悳绱€傞潪鑱氶泦绱㈠紩閮芥槸杈呭姪绱㈠紩锛屽澶嶅悎绱㈠紩銆佸墠缂€绱㈠紩銆佸敮涓€绱㈠紩绛夈€傝緟鍔╃储寮曞彾瀛愯妭鐐瑰瓨鍌ㄧ殑涓嶅啀鏄鐨勭墿鐞嗕綅缃紝鑰屾槸涓婚敭鍊笺€傞潪鑱氶泦绱㈠紩涓嶄竴瀹氳繑鍥炶〃鏌ヨ銆傚鏋滄墍鏈夌殑绱㈠紩閮借鍛戒腑锛屽垯涓嶉渶瑕佸姞鍏ョ储寮曘€傚鏋滆鍛戒腑绱㈠紩锛岄渶瑕佹寜鐓у垱寤虹储寮曟椂瀛楁鐨勯『搴忥紝涓€涓竴涓殑浣跨敤銆傞绻佺殑鏌ヨ闇€姹傛垨瀛楁閫夋嫨楂樻€ц兘鍒楁斁鍦ㄥ叾浠栬鍥剧殑鍓嶉潰銆傚熀纭€琛紙瀹炶〃锛夌敓鎴愮殑琛紙铏氳〃锛夌殑寤虹珛鍜屽垹闄や笉褰卞搷鍩虹琛ㄣ€傛洿鏂帮紙澧炲垹鏀规煡锛夌洿鎺ュ奖鍝嶅熀纭€琛ㄣ€傚涓熀纭€琛ㄦ潵鏃讹紝涓嶅厑璁稿鍒犳敼鏁版嵁缂虹偣鎬ц兘淇敼瀛樺偍杩囩▼闄愬埗棰勭紪璇慡QL璇彞浼樼偣棰勭紪璇戯紝鎵ц鏁堢巼楂樼洿鎺ュ瓨鍏ユ暟鎹簱锛屽噺灏戠綉缁滈€氫俊瀹夊叏鎬ч珮锛屾煇浜涙潈闄愬彲浠ュ鐢ㄧ己鐐硅皟璇曢夯鐑﹁縼绉籶roblemsre-Compilationissues鐢ㄦ埛闇€姹傜殑澧炲姞浼氬鑷存暟鎹粨鏋勭殑鍙樺寲銆傝Е鍙戜竴娈典唬鐮併€傚綋浜嬩欢琚Е鍙戞椂锛岃繖浜涗唬鐮佷細鑷姩鎵ц銆傚叧鑱旀煡璇氦鍙夎繛鎺ワ紙绗涘崱灏旂Н锛塻electr.,s.浠巖,s鍐呴儴鍔犲叆閫夋嫨r.,s銆俧romrinnerjoinsonr.c=s.cleftjoinselectr.,s.fromrleftjoinsonr.c=s.crightjoinselectr.,s.fromrrightjoinsonr.c=s.cfulltablemysql杩炴帴涓嶆敮鎸乻electr.,s銆俧romrfulljoinsonr.c=s.cSELECTFROMALEFTJOINBONA.id=B.idUNIONSELECTFROMARIGHTJOINBONA.id=B.idoptimizationexplain鏌ョ湅璇彞鐨勬墽琛岃鍒抜d锛屽叾涓〃绀轰竴涓煡璇腑鍚勪釜瀛愭煡璇㈢殑鎵ц椤哄簭瓒婇珮锛屼紭鍏堢骇瓒婇珮锛岃秺鏃╂墽琛宨ndexrangesearchref浣跨敤闈炲敮涓€绱㈠紩鏌ユ壘鏁版嵁constsprimarykeyoruniqueindexpossible_keys鍙兘浣跨敤鏌ヨ瀹為檯浣跨敤鐨勭储寮昸ey绱㈠紩澶ц〃鏁版嵁鏌ヨ浼樺寲shema,sql璇彞+绱㈠紩绉掑姞缂撳瓨,memcached,redismaster-浠庡鍒讹紝璇诲啓鍒嗙鍨傜洿鎷嗗垎锛屾牴鎹綘妯″潡鐨勮€﹀悎搴︼紝鎶婁竴涓ぇ绯荤粺鍒嗘垚澶氫釜灏忕郴缁燂紝涔熷氨鏄垎甯冨紡绯荤粺鐨勬按骞虫媶鍒嗐€傚浜庢暟鎹噺澶х殑琛紝杩欎竴姝ユ渶楹荤儲锛屾渶鑳借€冮獙鎶€鏈按骞筹紝閫夋嫨鍚堢悊鐨剆hardingkey锛屼负浜嗘湁濂界殑鏌ヨ鏁堢巼锛岃〃缁撴瀯涔熻鏀癸紝鍋氫竴浜涘啑浣欙紝搴旂敤涔熻鏀广€傚敖閲忓湪SQL涓寘鍚玸hardingkey锛屽皢鏁版嵁瀹氫綅鍒版湁闄愮殑琛ㄤ腑鏌ヨ锛岃€屼笉鏄壂鎻忔墍鏈夎〃锛涜秴澶ч〉MySQL涓嶈烦杩噊ffset琛岋紝鑰屾槸鍙杘ffset+N琛岋紝鐒跺悗杩斿洖涔嬪墠鐨刼ffset琛屾斁寮冿紝骞惰繑鍥濶琛屻€傚綋鍋忕Щ寰堝ぇ鏃讹紝鏁堢巼寰堜綆銆傚厛蹇€熷畾浣嶅埌闇€瑕佽幏鍙栨鐨刬d锛岀劧鍚庡叧鑱擲ELECTa.*FROMtable1a,(selectidfromtable1whereconditionLIMIT100000,20)bwherea.id=b.idpagemysql>SELECT*鏉ヨ嚜琛↙IMIT5,10锛?/妫€绱㈣褰曡6-15mysql>SELECT*FROMtableLIMIT95,-1;//妫€绱㈣褰曡96-last.mysql>SELECT*FROMtableLIMIT5;//妫€绱㈠墠5鏉¤褰曡鎱㈡煡璇㈡棩蹇楃敤浜庤褰曟椂闂磋秴杩囨煇涓复鐣屽€肩殑鎵цSQL鏃ュ織閰嶇疆椤癸細slow_query_log浼樺寲鏄惁鍔犺浇棰濆鏁版嵁锛屾煡璇㈠啑浣欒骞朵涪寮冿紝鍙兘鏄偅涔堝鍒楀姞杞界粨鏋滀腑涓嶉渶瑕佺殑鍐呭骞朵娇鐢ㄧ储寮曘€傛暟鎹噺鏄惁澶ぇ锛熷ぇ鍨嬫按骞虫垨鍨傜洿瀛愯〃鐨勮嚜鍔ㄥ閲廔D鎴朥UID銆備笉瑕佷娇鐢║UID鑷ID锛岄偅涔堝彧闇€瑕佺户缁悜鍚庢帓鍒桿UID鍗冲彲銆傜敱浜庝紶鍏ョ殑ID鍜屽師濮嬪昂瀵告槸涓嶇‘瀹氱殑锛屼細閫犳垚寰堝闂銆傛暟鎹彃鍏ュ拰鏁版嵁绉诲姩娌℃湁涓婚敭銆侷nnoDB浼氶€夋嫨涓€涓敮涓€鐨勯敭浣滀负鑱氱皣绱㈠紩銆傚鏋滄病鏈夊敮涓€閿紝灏嗙敓鎴愪竴涓殣寮忎富閿€傚畾涔変负notnull锛宯ull鍊间細鍗犵敤鏇村鐨勫瓧鑺傦紝鍦ㄧ▼搴忎腑浼氬嚭鐜板緢澶氫笉绗﹀悎棰勬湡鐨勫湴鏂广€係QL璇彞浼樺寲浼樺寲WHERE瀛愬彞锛屽where鍜宱rderby娑夊強鐨勫垪寤虹珛绱㈠紩锛岄伩鍏峸here瀛愬彞鍒ゆ柇瀛楁涓虹┖鍊硷紝鍚﹀垯浼氬鑷村紩鎿庢斁寮冧娇鐢ㄧ储寮曪紝鎵ц鍏ㄨ〃鎵弿銆傞伩鍏嶅湪where瀛愬彞涓娇鐢?=鎴?>杩愮畻绗﹂伩鍏嶅湪where瀛愬彞涓娇鐢╫r鏉ヨ繛鎺ユ潯浠秈n鍜宯otin杩樿璋ㄦ厧浣跨敤閬垮厤鍦╳here瀛愬彞涓娇鐢ㄥ弬鏁伴伩鍏嶅湪where瀛愬彞涓瀛楁杩涜琛ㄨ揪寮忔搷浣滈伩鍏嶄娇鐢╳here瀛愬彞瀵硅鍙ヤ腑鐨勫瓧娈佃繘琛屽嚱鏁版搷浣滐紝浼樺寲query鏌ヨ鏃剁殑鏁版嵁璁块棶涓嶅繀瑕佺殑鏁版嵁浣跨敤闄愬埗锛岃В鍐冲琛ㄥ叧鑱旇繑鍥炴墍鏈夊垪鎸囧畾鍒楀悕锛屽缁堣繑鍥炴墍鏈夊垪閬垮厤浣跨敤SELECT*閲嶅鏌ヨ鍚屼竴涓猟ata鍙互缂撳瓨鏁版嵁浠ヤ紭鍖栭暱鍥伴毦鐨勬煡璇㈡渶濂戒娇鐢ㄥ敖鍙兘灏忕殑鏌ヨ銆傚皢涓€涓ぇ鏌ヨ鍒嗘垚澶氫釜杈冨皬鐨勭浉鍚屾煡璇€傚垎瑙ｅ叧鑱旀煡璇€傜壒瀹氱被鍨嬬殑鏌ヨ銆俢ount(*)蹇界暐鎵€鏈夊垪锛屼笉浣跨敤count(鍒楀悕)娣诲姞姹囨€昏〃浣跨敤缂撳瓨浼樺寲鍏宠仈鏌ヨON鎴朥SING瀛愬彞涓槸鍚︽湁绱㈠紩GROUPBY鍜孫RDERBY琛ㄤ腑鍙湁涓€鍒椾紭鍖栧瓙鏌ヨ浼樺寲GROUPBY鍜孌ISTINCT锛屼娇鐢ㄧ储寮曟潵浼樺寲锛屾槸鏈€鏈夋晥鐨勪紭鍖栨柟娉曪紝涓嶉渶瑕丱RDERBY銆傚湪鎵цGROUPBY鏃舵坊鍔燨RDERBYNUL銆傛暟鎹簱浼樺寲銆傜粨鏋勪紭鍖栥€傚皢鍏锋湁璁稿瀛楁鐨勮〃鍒嗚В涓哄涓〃銆傛坊鍔犱腑闂磋〃銆傞渶瑕侀绻佽仈鍚堟煡璇㈢殑琛ㄤ細澧炲姞鍐椾綑瀛楁銆傝〃鐨勬爣鍑嗗寲绋嬪害瓒婇珮锛岃〃鍜岃〃涔嬮棿鐨勫叧绯昏秺澶氾紝闇€瑕佺殑杩炴帴鏌ヨ灏辫秺澶氾紝鎬ц兘灏辫秺宸€俢pu椋欏崌骞舵鏌ュ唴閮ㄦ鍦ㄨ繍琛岀殑浼氳瘽銆傛湁娌℃湁杩愯鑰楄祫婧愮殑sql锛熸壘鍑洪珮娑堣€楃殑sql锛屾鏌ユ墽琛岃鍒掓槸鍚﹀噯纭紝鏄惁缂哄皯绱㈠紩锛屾垨鑰呮暟鎹噺杩囧ぇ锛屾潃鎺夎繖浜涚嚎绋嬨€傜粡杩囩浉搴旂殑璋冩暣锛堟瘮濡傚姞绱㈠紩锛屾敼sql锛屾敼鍐呭瓨鍙傛暟锛夛紝閲嶆柊杩愯杩欎簺SQL锛屾瘡鏉ql骞舵病鏈夋秷鑰楀灏戣祫婧愶紝浣嗘槸绐佺劧锛屽ぇ閲弒ession杩炴帴瀵艰嚧CPU椋欏崌銆傚垎鏋愪负浠€涔堣繛鎺ユ暟浼氭縺澧烇紝鐒跺悗鍋氱浉搴旂殑璋冩暣锛屾瘮濡傞檺鍒惰繛鎺ユ暟绛夊ぇ琛紝浼樺寲鏁版嵁鑼冨洿锛岃鍐欏垎绂汇€備富搴撹礋璐ｅ啓鍏ワ紝浠庡簱璐熻矗璇诲彇鍜岀紦瀛樸€備娇鐢∕ySQL缂撳瓨銆傚彟澶栵紝瀵逛簬閲嶉噺绾у拰鏇存柊杈冨皯鐨勬暟鎹紝鍙互鑰冭檻浣跨敤鍨傜洿鍒嗗尯鏁版嵁琛ㄥ垪鐨勫簲鐢ㄧ骇缂撳瓨鍒嗗簱鍒嗚〃鎷嗗垎锛涙湁浜涘垪甯哥敤锛屾湁浜涘垪涓嶅父鐢紝浣胯鏁版嵁鍙樺皬锛屽噺灏戞煡璇㈡椂璇诲彇鐨勫潡鏁帮紝鍑忓皯I/O娆℃暟浼氬啑浣欙紝鍐椾綑鍒楅渶瑕佺鐞嗭紝浼氬紩璧稪oin鎿嶄綔;瀵逛簬搴旂敤灞傦紝閫昏緫绠楁硶澧炲姞浜嗘按骞虫媶鍒嗘暟鎹〃琛岀殑寮€鍙戞垚鏈紝鑰屾按骞虫媶鍒嗘槸鍑忓皯鏁版嵁搴撳垎鍖轰紭鍔垮湪鏌ヨ涓鍙栫殑鏁版嵁閲忓拰椤垫暟鐨勬渶浣虫柟寮忛渶瑕佽鍙栫储寮曪紝绱㈠紩鐨勫眰鏁颁篃鍑忓皯浜嗭紝鏌ヨ娆℃暟澧炲姞锛屼互鏀寔闈炲父澶х殑鏁版嵁瀛樺偍閲忥紝搴旂敤绔殑鏀归€犱篃灏戜簡銆傜己鐐癸細鍒嗙墖浜嬪姟闅句互瑙ｅ喅锛岃法鐣宩oin鎬ц兘杈冨樊锛岄€昏緫澶嶆潅锛岄€氬父闇€瑕佸涓〃鍚嶈繘琛屾煡璇紝闇€瑕乁NION鎿嶄綔鎵嶈兘鏌ヨ鍒版墍鏈夋暟鎹€傚湪搴旂敤绋嬪簭鍜屾暟鎹箣闂存坊鍔犱簡涓€涓唬鐞嗗眰銆傚垎鐗囬€昏緫缁熶竴缁存姢鍦ㄤ腑闂翠欢鏈嶅姟涓€傞棶棰樹簨鍔℃敮鎸佹暟鎹簱鏈韩鐨勫垎甯冨紡浜嬪姟绠＄悊搴旂敤绋嬪簭锛岃緟鍔╂帶鍒惰法搴搄oin锛屽湪涓ゆ鏌ヨ涓疄鐜般€傚湪绗竴娆℃煡璇㈢殑缁撴灉闆嗕腑鎵惧埌鍏宠仈鏁版嵁鐨刬d锛屾牴鎹繖浜沬d鍙戣捣绗簩娆¤姹傝幏鍙栧叧鑱旀暟鎹€傝法鑺傜偣count銆乷rderby銆乬roupby銆佽仛鍚堝嚱鏁伴棶棰樼被浼间簬姹傝В璺ㄨ妭鐐筳oin闂銆傚湪姣忎釜鑺傜偣涓婂緱鍒扮粨鏋滃悗锛屽悎骞跺彲浠ュ湪搴旂敤绔苟琛屾墽琛岋紝鎵€浠ュ緢澶氭儏鍐典笅姣斿崟涓ぇ琛ㄨ蹇緢澶欼D闂銆備笉鑳戒繚璇佽嚜琛岀敓鎴愮殑ID鏄叏灞€鍞竴鐨勩€傚湪鎻掑叆鏁版嵁涔嬪墠锛屾偍闇€瑕佽幏鍙栫敤浜嶴QL璺敱鐨処D銆傚父瑙佺殑涓婚敭鐢熸垚绛栫暐UUIDUUID寰堥暱銆傞櫎浜嗗崰鐢ㄥぇ閲忕殑瀛樺偍绌洪棿锛屾渶涓昏鐨勯棶棰樿繕鏄湪绱㈠紩涓婏紝鍦ㄥ缓绔嬬储寮曞拰鏍规嵁绱㈠紩杩涜鏌ヨ鐨勬椂鍊欓兘鏈夋€ц兘闂銆傝法鍒嗙墖鎺掑簭鍒嗛〉鎺掑簭瀛楁闈炲垎鐗囧瓧娈靛湪涓嶅悓鍒嗙墖鑺傜偣鎺掑簭杩斿洖鏁版嵁锛屽苟瀵逛笉鍚屽垎鐗囪繑鍥炵殑缁撴灉闆嗚繘琛屾眹鎬婚噸鏂版帓搴忎富浠庡鍒堕€氳繃浜岃繘鍒舵棩蹇楋紙BINLOG锛変紶杈撳埌浠庡簱锛屼互鍙婄劧鍚庨噸鏂版墽琛岋紙閲嶅仛锛夎繖浜涙棩蹇楋紱濡傛灉涓诲簱涓€鑷存€ф湁闂锛屽彲浠ュ垏鎹㈠埌浠庡簱銆傛暟鎹簱绾у埆鐨勮鍐欏垎绂绘槸鍙兘鐨勩€傚彲浠ュ湪浠庢暟鎹簱涓婃墽琛屾瘡鏃ュ浠姐€傝В鍐抽棶棰樻暟鎹垎甯冿細闅忔剰鍚姩鎴栧仠姝㈠鍒讹紝灏嗘暟鎹浠藉垎甯冨湪涓嶅悓鐨勫湴鐞嗕綅缃礋杞藉潎琛★細闄嶄綆鍗曞彴鏈嶅姟鍣ㄧ殑鍘嬪姏楂樺彲鐢ㄥ拰鏁呴殰杞Щ锛氬府鍔╁簲鐢ㄩ伩鍏嶅崟鐐规晠闅滃崌绾ф祴璇曪細鏇撮珮鐗堟湰鐨凪ySQL鍙互浣滀负浠庡簱杩涚▼master锛歜inlogthread鈥斺€旇褰曟墍鏈夋敼鍙樻暟鎹簱鏁版嵁鐨勮鍙ワ紝骞舵斁鍏aster涓婄殑binlog锛泂lave锛歩o绾跨▼鈥斺€斾娇鐢╯tartslave鍚庯紝璐熻矗浠巑aster涓婃媺鍙朾inlog鍐呭锛屾斁鍏ヨ嚜宸辩殑relaylog涓紱from锛歴ql鎵ц绾跨▼鈥斺€旀墽琛屼腑缁ф棩蹇椾腑鐨勮鍙ワ紱鏂规浣跨敤mysql-proxy浠ｇ悊闄嶄綆鎬ц兘锛屼笉鏀寔浜嬪姟浣跨敤AbstractRoutingDataSource+aop+annotation閫氳繃mybatis鎻掍欢鍦╠ao灞傚垽鏂暟鎹簮mybatis鎷︽埅sql璇彞锛屾墍鏈塱nsert/update/delete璁块棶master搴擄紝allselectaccesssalve涔熼渶瑕侀噸鍐橠ataSourceTransactionManager锛屾妸鍙浜嬪姟涓㈠埌read搴擄紝鍓╀笅鐨勮鍐欎簨鍔′涪鍒皐rite搴擄紝浣跨敤AbstractRoutingDataSource+aop+annotation鍦ㄦ湇鍔″眰鍒ゆ柇鏁版嵁婧愬彲浠ユ敮鎸佷氦鏄撱€?/p>
