Chapter聽7.聽Optimization - MySQL 5.1参考手册中文版

褰撲娇鐢�MyISAM瀛樺偍寮曟搸鏃讹紝MySQL浣跨敤鏋佸揩閫熺殑琛ㄩ攣瀹氾紝浠ヤ究鍏佽�稿�氭�¤�绘垨涓�娆″啓銆備娇鐢ㄨ�ュ瓨鍌ㄥ紩鎿庣殑鏈�澶ч棶棰樺嚭鐜板湪鍚屼竴涓�琛ㄤ腑杩涜�屾贩鍚堢ǔ瀹氭暟鎹�娴佹洿鏂颁笌鎱㈤�熼�夋嫨銆傚�傛灉杩欏彧鏄�鏌愪簺琛ㄧ殑闂�棰橈紝浣犲彲浠ヤ娇鐢ㄥ彟涓�涓�瀛樺偍寮曟搸銆傚弬瑙�绗�15绔狅細瀛樺偍寮曟搸鍜岃〃绫诲瀷銆�

MySQL鍙�浠ヤ娇鐢ㄤ簨鍔¤〃鍜岄潪浜嬪姟琛ㄣ�備负浜嗘洿瀹规槗鍦拌�╅潪浜嬪姟琛ㄩ『鍒╁伐浣�(濡傛灉鍑虹幇闂�棰樹笉鑳藉洖婊�)锛�MySQL閲囩敤涓嬭堪瑙勫垯銆傝�锋敞鎰忚繖浜涜�勫垯鍙�閫傜敤浜�涓嶈繍琛屽湪涓ユ牸妯″紡涓嬫垨涓�INSERT鎴�UPDATE浣跨敤IGNORE瑙勫畾绋嬪簭鏃躲��

路         鎵�鏈夊垪鏈夐粯璁ゅ�笺�傝�锋敞鎰忓綋杩愯�屽湪涓ユ牸SQL妯″紡(鍖呮嫭TRADITIONAL SQL妯″紡)鏃讹紝蹇呴』涓�NOT NULL鍒楁寚瀹氶粯璁ゅ�笺��

路         濡傛灉鍚戝垪鍐呮彃鍏ヤ笉鍚堥�傜殑鎴栬秴鍑鸿寖鍥寸殑鍊硷紝MySQL灏嗚�ュ垪璁惧畾涓衡�滄渶濂界殑鍙�鑳界殑鍊�鈥濓紝鑰屼笉鏄�鎶ュ憡閿欒��銆傚�逛簬鏁板瓧鍊硷紝涓�0銆佸彲鑳界殑鏈�灏忓�兼垨鏈�澶у�笺�傚�逛簬瀛楃�︿覆锛屼负绌哄瓧绗︿覆鎴栧垪鍐呭彲浠ヤ繚瀛樼殑瀛楃�︿覆銆傝�锋敞鎰忓綋杩愯�屽湪涓ユ牸妯″紡鎴�TRADITIONAL SQL妯″紡鏃惰�ヨ�屼负涓� 閫傜敤銆�

路         鎵�鏈夎〃杈惧紡鐨勮�＄畻缁撴灉杩斿洖涓�涓�琛ㄧず閿欒��鐘跺喌鐨勪俊鍙枫�備緥濡傦紝1/0杩斿洖NULL銆�(浣跨敤ERROR_FOR_DIVISION_BY_ZERO SQL妯″紡鍙�浠ユ洿鏀硅�ヨ�屼负)銆�

濡傛灉姝ｄ娇鐢ㄩ潪浜嬪姟琛�锛屼笉搴旇�ヤ娇鐢�MySQL鏉ユ��鏌ュ垪鐨勫唴瀹广�備竴鑸�鎯呭喌锛屾渶瀹夊叏鐨�(閫氬父鏄�鏈�蹇�鐨�)鏂规硶寰勬槸璁╁簲鐢ㄧ▼搴忕‘淇濆彧鍚戞暟鎹�搴撲紶閫掑悎娉曞�笺��

鐩稿叧璇︾粏淇℃伅鍙傝��1.8.6鑺傦紝鈥淢ySQL澶勭悊绾︽潫鐨勬柟寮忊��鍜�13.2.4鑺傦紝鈥淚NSERT璇�娉曗��鎴�5.3.2鑺傦紝鈥淪QL鏈嶅姟鍣ㄦā寮忊��銆�

鍥犱负涓嶅悓SQL鏈嶅姟鍣ㄥ疄鐜颁簡鏍囧噯SQL鐨勪笉鍚岄儴鍒嗭紝闇�瑕佽姳鍔熷か鏉ョ紪鍐欏彲绉绘�嶇殑SQL搴旂敤绋嬪簭銆傚�瑰緢绠�鍗曠殑閫夋嫨/鎻掑叆锛屽緢瀹规槗瀹炵幇绉绘�嶏紝浣嗘槸闇�瑕佺殑鍔熻兘瓒婂�氬垯瓒婂洶闅俱�傚�傛灉鎯宠�佸簲鐢ㄧ▼搴忓�瑰緢澶氭暟鎹�搴撶郴缁熼兘蹇�锛屽畠鍙樺緱鏇撮毦锛�

涓轰簡浣夸竴涓�澶嶆潅搴旂敤绋嬪簭鍙�绉绘�嶏紝浣犻渶瑕侀�夋嫨瀹冨簲璇ュ伐浣滅殑SQL鏈嶅姟鍣�锛屽苟纭�瀹氳繖浜涙湇鍔″櫒鏀�鎸佷粈涔堢壒鎬с��

鎵�鏈夋暟鎹�搴撻兘鏈変竴浜涘急鐐广�傝繖灏辨槸瀹冧滑涓嶅悓鐨勮�捐�℃姌琛峰�艰嚧鐨勪笉鍚岃�屼负銆�

鍙�浠ヤ娇鐢�MySQL鐨�crash-me绋嬪簭鏉ユ壘鍑鸿兘鐢ㄤ簬鏁版嵁搴撴湇鍔″櫒閫夋嫨鐨勫嚱鏁般�佺被鍨嬪拰闄愬埗銆�crash-me骞朵笉鑳芥壘鍑烘墍鏈夌殑鐗规�э紝浣嗘槸鍏跺箍搴︿粛鐒跺緢鍚堢悊锛屽彲浠ヨ繘琛屽ぇ绾�450涓�娴嬭瘯銆�

crash-me鍙�浠ユ彁渚涚殑涓�绉嶇被鍨嬬殑淇℃伅鐨勪緥瀛愶細濡傛灉鎯宠�佷娇鐢�Informix鎴�DB2锛屼笉搴旇�ヤ娇鐢ㄨ秴杩�18涓�瀛楃�︾殑鍒楀悕銆�

crash-me绋嬪簭鍜�MySQL鍩哄噯绋嬪簭鏄�鐙�绔嬩簬鏁版嵁搴撶殑銆傞�氳繃瑙傚療瀹冧滑鏄�濡備綍缂栧啓鐨勶紝缂栧彲浠ョ煡閬撳繀椤讳负缂栧啓鐙�绔嬩簬鏁版嵁搴撶殑搴旂敤绋嬪簭鍋氫粈涔堛�傚熀鍑嗘湰韬�鍙�鍦�MySQL婧愮爜鍒嗗彂鐨�鈥渟ql-bench鈥�鐩�褰曚笅鎵惧埌銆傚畠浠�鐢�DBI鏁版嵁搴撴帴鍙ｄ互Perl鍐欐垚銆備娇鐢�DBI鏈�韬�鍗冲彲浠ヨВ鍐抽儴鍒嗙Щ妞嶆�ч棶棰橈紝鍥犱负瀹冩彁渚涗笌鏁版嵁搴撴棤鍏崇殑鐨勫瓨鍙栨柟娉曘��

鍏充簬crash-me缁撴灉锛岃�块棶http://dev.mysql.com/tech-resources/crash-me.php銆傚埌http://dev.mysql.com/tech-resources/benchmarks/鐪嬭繖涓�鍩哄噯鐨勭粨鏋溿��

濡傛灉浣犱负鏁版嵁搴撶殑鐙�绔嬫�ц�屽姫鍔涳紝闇�瑕佸緢濂藉湴浜嗚В姣忎釜SQL鏈嶅姟鍣ㄧ殑鐡堕�堛�備緥濡傦紝MySQL鍦ㄦ��绱㈠拰鏇存柊MyISAM琛ㄨ�板綍鏂归潰寰堝揩锛屼絾鏄�鍦ㄥ悓涓�涓�琛ㄤ笂娣峰悎鎱㈤�熻�昏�呭拰鍐欒�呮柟闈㈡湁涓�涓�闂�棰樸�傚彟涓�鏂归潰锛屽綋浣犺瘯鍥捐�块棶鏈�杩戞洿鏂颁簡(鐩村埌瀹冧滑琚�鍒锋柊鍒扮�佺洏涓�)鐨勮�屾椂锛屽湪Oracle涓�鏈変竴涓�寰堝ぇ鐨勯棶棰樸�備簨鍔℃暟鎹�搴撴�荤殑鏉ヨ�村湪浠庤�板綍鏂囦欢琛ㄤ腑鐢熸垚鎬荤粨琛ㄦ柟闈�涓嶆槸寰堝ソ锛屽洜涓哄湪杩欑�嶆儏鍐典笅锛岃�岄攣瀹氬嚑涔庢病鏈夌敤銆�

涓轰簡浣垮簲鐢ㄧ▼搴忊�滅‘瀹炩�濈嫭绔嬩簬鏁版嵁搴擄紝闇�瑕佸畾涔変竴涓�瀹规槗鎵╁睍鐨勬帴鍙ｏ紝鐢ㄥ畠鍙�鎿嶇旱鏁版嵁銆傚洜涓�C++鍦ㄥぇ澶氭暟绯荤粺涓婂彲浠ラ�傜敤锛屼娇鐢ㄦ暟鎹�搴撶殑涓�涓�C++ 绫绘帴鍙ｆ槸鏈夋剰涔夌殑銆�

濡傛灉浣犱娇鐢ㄦ煇涓�鏁版嵁搴撶壒瀹氱殑鍔熻兘(渚嬪��MySQL涓撶敤鐨�REPLACE璇�鍙�)锛屽簲璇ヤ负SQL鏈嶅姟鍣ㄧ紪鐮佷竴涓�鏂规硶浠ュ疄鐜板悓鏍风殑鍔熻兘銆傚敖绠℃參浜涳紝浣嗙‘鍏佽�稿叾瀹冩湇鍔″櫒鎵ц�屽悓鏍风殑浠诲姟銆�

鐢�MySQL锛屽彲浠ヤ娇鐢�/*! */璇�娉曟妸MySQL鐗瑰畾鐨勫叧閿�璇嶅姞鍒版煡璇�涓�銆傚湪/**/涓�鐨勪唬鐮佸皢琚�鍏跺畠澶у�氭暟SQL鏈嶅姟鍣ㄨ�嗕负娉ㄩ噴(骞惰��蹇界暐)銆�

濡傛灉楂樻�ц兘鐪熺殑姣斿噯纭�鎬ф洿閲嶈�侊紝灏卞儚鍦ㄤ竴浜�web搴旂敤绋嬪簭閭ｆ牱锛屼竴绉嶅彲琛岀殑鏂规硶鏄�鍒涘缓涓�涓�搴旂敤灞傦紝缂撳瓨鎵�鏈夌殑缁撴灉浠ヤ究寰楀埌鏇撮珮鐨勬�ц兘銆傞�氳繃鍙�鏄�璁╂棫鐨勭粨鏋滃湪鐭�鏃堕棿鍚庘�樿繃鏈熲�欙紝鑳戒繚鎸佺紦瀛樺悎鐞嗗湴鍒锋柊銆傝繖鍦ㄦ瀬楂樿礋杞界殑鎯呭喌涓嬫槸鐩稿綋涓嶉敊鐨勶紝鍦ㄦ�ゆ儏鍐典笅锛岃兘鍔ㄦ�佸湴澧炲姞缂撳瓨骞朵笖璁惧畾杈冮珮鐨勮繃鏈熸椂闄愮洿鍒颁竴鍒囨仮澶嶆�ｅ父銆�

鍦ㄨ繖绉嶆儏鍐典笅锛岃〃鍒涘缓淇℃伅搴旇�ュ寘鍚�缂撳瓨鍒濆�嬪ぇ灏忓拰琛ㄥ埛鏂伴�戠巼绛変俊鎭�銆�

瀹炴柦搴旂敤绋嬪簭缂撳瓨鐨勪竴绉嶆柟娉曟槸浣跨敤MySQL鏌ヨ�㈢紦瀛樸�傚惎鐢ㄦ煡璇㈢紦瀛樺悗锛屾湇鍔″櫒鍙�浠ョ‘瀹氭槸鍚﹀彲浠ラ噸鏂颁娇鐢ㄦ煡璇㈢粨鏋溿�傝繖鏍风畝鍖栦簡浣犵殑搴旂敤绋嬪簭銆傚弬瑙�5.13鑺傦紝鈥淢ySQL鏌ヨ�㈤珮閫熺紦鍐测��銆�

璇ヨ妭鎻忚堪浜�Mysql鐨勬棭鏈熷簲鐢ㄧ▼搴忋��

鍦�MySQL鏈�鍒濆紑鍙戞湡闂达紝MySQL鐨勫姛鑳介�傚悎澶у�氭暟瀹㈡埛銆�MySQL涓虹憺鍏哥殑涓�浜涙渶澶х殑闆跺敭鍟嗗�勭悊鏁版嵁浠撳簱銆�

鎴戜滑浠庢墍鏈夊晢搴楀緱鍒版墍鏈夌孩鍒╁崱浜ゆ槗鐨勬瘡鍛ㄦ�荤粨锛屽苟涓旀垜浠�鏈熸湜涓烘墍鏈夊簵涓绘彁渚涙湁鐢ㄧ殑淇℃伅浠ュ府鍔╀粬浠�寰楀嚭浠栦滑鐨勫箍鍛婃垬濡備綍褰卞搷浠栦滑鐨勯【瀹�銆�

鏁版嵁鏄�鐩稿綋宸ㄩ噺鐨�(澶х害姣忔湀7鐧句竾瀹椾氦鏄撴�荤粨)骞朵笖鎴戜滑淇濆瓨4-10骞存潵鐨勬暟鎹�闇�瑕佸憟鐜扮粰鐢ㄦ埛銆傛垜浠�姣忓懆浠庨【瀹㈤偅閲屽緱鍒拌�锋眰锛屼粬浠�鎯宠�佲�滅珛鍒烩�濊�块棶鏉ヨ嚜璇ユ暟鎹�鐨勬柊鎶ュ憡銆�

鎴戜滑閫氳繃姣忔湀灏嗘墍鏈変俊鎭�瀛樺偍鍦ㄥ帇缂╃殑鈥滀氦鏄撯�濊〃涓�鏉ヨВ鍐冲畠銆傛垜浠�鏈変竴濂楃畝鍗曠殑瀹�/鑴氭湰鐢ㄦ潵鐢熸垚鏉ヨ嚜浜ゆ槗琛ㄧ殑涓嶅悓鏉′欢( 浜у搧缁勩�侀【瀹�id锛屽晢搴�...)鐨勬�荤粨琛ㄣ�傛姤鍛婃槸鐢变竴涓�杩涜�岃��娉曞垎鏋愮綉椤电殑灏�perl鑴氭湰鍔ㄦ�佺敓鎴愮殑缃戦〉锛屽湪鑴氭湰涓�鎵ц��SQL璇�鍙ュ苟涓旀彃鍏ョ粨鏋溿�傛垜浠�寰堟兂浣跨敤PHP鎴�mod_perl锛屼絾鏄�閭ｆ椂瀹冧滑杩樹笉鍙�鐢ㄣ��

瀵瑰浘褰㈡暟鎹�锛屾垜浠�鐢�C璇�瑷�缂栧啓浜嗕竴涓�绠�鍗曠殑宸ュ叿锛屽畠鑳藉熀浜庨偅浜涚粨鏋滃�勭悊SQL鏌ヨ�㈢粨鏋滃苟鐢熸垚GIF鍥惧舰銆傝�ュ伐鍏蜂篃浠庡垎鏋�Web缃戦〉鐨�perl鑴氭湰涓�鍔ㄦ�佸湴鎵ц�屻��

鍦ㄥぇ澶氭暟鎯呭喌涓嬶紝涓�涓�鏂扮殑鎶ュ憡閫氳繃绠�鍗曞湴澶嶅埗涓�涓�鐜版湁鑴氭湰骞朵笖淇�鏀瑰叾涓�鐨�SQL鏌ヨ�㈡潵瀹屾垚銆傚湪涓�浜涙儏鍐典笅锛屾垜浠�灏嗛渶瑕佹妸鏇村�氱殑鍒楀姞鍒颁竴涓�鐜版湁鐨勬�荤粨琛ㄤ腑鎴栦骇鐢熶竴涓�鏂扮殑锛屼絾鏄�杩欎篃鐩稿綋绠�鍗曪紝鍥犱负鎴戜滑鍦ㄧ�佺洏涓婁繚瀛樻墍鏈変氦鏄撹〃銆傦紙鐩�鍓嶆垜浠�澶х害鏈�50G鐨勪氦鏄撹〃鍜�200G鐨勫叾瀹冮【瀹㈡暟鎹�)銆�

鎴戜滑涔熻�╂垜浠�鐨勯【瀹㈢洿鎺ョ敤ODBC璁块棶鎬荤粨琛ㄤ互渚块珮绾х敤鎴疯兘鑷�宸辩敤杩欎簺鏁版嵁杩涜�岃瘯楠屻��

璇ョ郴缁熷伐浣滃緱寰堝ソ锛屾垜浠�鍙�浠ユ��鏃犻棶棰樺湴鐢ㄥ緢閫傚害鐨�Sun Ultra SPARC宸ヤ綔绔欑‖浠�(2x200MHz)鏉ュ�勭悊鏁版嵁銆傝�ョ郴缁熻��閫愭�ョЩ妞嶅埌浜�Linux涓�銆�

鏈�鑺傚簲璇ュ寘鍚�MySQL鍩哄噯濂椾欢(鍜�crash-me)鐨勬妧鏈�鎻忚堪锛屼絾鏄�璇ユ弿杩拌繕娌″啓鎴愩�傜洰鍓嶏紝浣犲彲浠ラ�氳繃鍦�MySQL婧愮爜鍒嗗彂涓�鐨�鈥渟ql-bench鈥�鐩�褰曚笅鐨勪唬鐮佸拰缁撴灉浜嗚В鍩哄噯濂椾欢鏄�濡備綍宸ヤ綔鐨勩��

閫氳繃鍩哄噯鐢ㄦ埛鍙�浠ヤ簡瑙ｄ竴涓�缁欏畾鐨�SQL瀹炵幇鍦ㄥ摢鏂归潰鎵ц�屽緱寰堝ソ鎴栧緢绯熺硶銆�

娉ㄦ剰锛岃繖涓�鍩哄噯鏄�鍗曠嚎绋嬬殑锛屽畠鍙�浠ユ祴閲忔搷浣滄墽琛岀殑鏈�灏忔椂闂淬�傛垜浠�璁″垝灏嗘潵鍦ㄥ熀鍑嗗�椾欢涓�娣诲姞澶氱嚎绋嬫祴璇曘��

瑕佷娇鐢ㄥ熀鍑嗗�椾欢锛屽繀椤绘弧瓒充笅闈㈢殑瑕佹眰锛�

路         鍩哄噯濂椾欢闅�MySQL婧愮爜鍒嗗彂鎻愪緵銆傚彲浠ヤ粠http://dev.mysql.com/downloads/涓嬭浇鍒嗗彂锛屾垨鑰呬娇鐢ㄥ綋鍓嶇殑寮�鍙戞簮鐮佹爲(鍙傝��2.8.3鑺傦紝鈥滀粠寮�鍙戞簮鐮佹爲瀹夎�呪��)銆�

路         鍩哄噯鑴氭湰鐢�Perl缂栧啓鑰屾垚锛屼娇鐢�Perl DBI妯″潡璁块棶鏁版嵁搴撴湇鍔″櫒锛屽洜姝ゅ繀椤诲畨瑁�DBI銆傝繕闇�瑕佷负姣忎釜寰呮祴璇曠殑鏈嶅姟鍣ㄦ彁渚涙湇鍔″櫒涓撶敤DBD椹卞姩绋嬪簭銆備緥濡傦紝瑕佹祴璇�MySQL銆�PostgreSQL鍜�DB2锛屽繀椤诲畨瑁�DBD::mysql銆�DBD::Pg鍜�DBD::DB2妯″潡銆傚弬瑙�2.13鑺傦紝鈥淧erl瀹夎�呮敞鎰忎簨椤光��銆�

鑾峰緱MySQL婧愮爜鍒嗗彂鍚庯紝鍙�浠ュ湪sql-bench鐩�褰曟壘鍒板熀鍑嗗�椾欢銆傝�佽繍琛屽熀鍑嗘祴璇曪紝搴旀瀯寤�MySQL锛岀劧鍚庤繘鍏�sql-bench鐩�褰曞苟鎵ц��run-all-tests鑴氭湰锛�

shell> cd sql-bench

shell> perl run-all-tests --server=server_name

server_name鏄�涓�涓�鏀�鎸佺殑鏈嶅姟鍣ㄣ�傝�佽幏寰楁墍鏈夐�夐」鍜屾敮鎸佺殑鏈嶅姟鍣�锛岃皟鐢ㄥ懡浠わ細

shell> perl run-all-tests --help

crash-me鑴氭湰涔熶綅浜�sql-bench鐩�褰曘��crash-me灏濊瘯閫氳繃瀹為檯杩愯�屾煡璇㈢‘瀹氭暟鎹�搴撴敮鎸佺殑鐗规�т互鍙婂叾鍔熻兘鍜岄檺鍒躲�備緥濡傦紝瀹冪‘瀹氾細

路         鏀�鎸佷粈涔堝垪绫诲瀷

路         鏀�鎸佸�氬皯绱㈠紩

路         鏀�鎸佷粈涔堝嚱鏁�

路         鏌ヨ�㈠彲浠ュ�氬ぇ

路         VARCHAR鍒楀彲浠ュ�氬ぇ

鍙�浠ヤ粠http://dev.mysql.com/tech-resources/crash-me.php鍙戠幇璁稿�氫笉鍚屾暟鎹�搴撴湇鍔″櫒鐨�crash-me鐨勭粨鏋溿�傚叧浜庡熀鍑嗘祴璇曠粨鏋滅殑璇︾粏淇℃伅锛岃�块棶http://dev.mysql.com/tech-resources/benchmarks/銆�

涓�瀹氳�佹祴璇曞簲鐢ㄧ▼搴忓拰鏁版嵁搴擄紝浠ュ彂鐜扮摱棰堝湪鍝�鍎裤�傞�氳繃淇�姝ｅ畠(鎴栭�氳繃鐢ㄤ竴涓�鈥滃搼妯″潡鈥濅唬鏇跨摱棰�)锛屽彲浠ュ緢瀹规槗鍦扮‘瀹氫笅涓�涓�鐡堕�堛�傚嵆浣夸綘鐨勫簲鐢ㄧ▼搴忕殑鏁翠綋鎬ц兘鐩�鍓嶅彲浠ユ帴鍙楋紝鑷冲皯搴旇�ュ�规瘡涓�鐡堕�堝仛涓�涓�璁″垝锛屽�傛灉鏌愬ぉ纭�瀹為渶瑕佹洿濂界殑鎬ц兘锛屽簲鐭ラ亾濡備綍瑙ｅ喅瀹冦��

鍏充簬涓�浜涘彲绉绘�嶇殑鍩哄噯绋嬪簭鐨勪緥瀛愶紝鍙傝��MySQL鍩哄噯濂椾欢銆傝�峰弬瑙�7.1.4鑺傦紝鈥淢ySQL鍩哄噯濂椾欢鈥�銆傚彲浠ュ埄鐢ㄨ繖涓�濂椾欢鐨勪换浣曠▼搴忓苟涓旀牴鎹�浣犵殑闇�瑕佷慨鏀瑰畠銆傞�氳繃杩欐牱鍋氾紝鍙�浠ュ皾璇曚笉鍚岀殑闂�棰樼殑瑙ｅ喅鏂规�堝苟娴嬭瘯鍝�涓�涓�鏄�鏈�濂界殑瑙ｅ喅鏂规�堛��

鍙︿竴涓�鍏嶈垂鍩哄噯濂椾欢鏄�寮�鏀炬簮鐮佹暟鎹�搴撳熀鍑嗗�椾欢锛屽弬瑙�http://osdb.sourceforge.net/銆�

鍦ㄧ郴缁熻礋杞界箒閲嶆椂鍑虹幇涓�浜涢棶棰樻槸寰堟櫘閬嶇殑锛屽苟涓斿緢澶氬�㈡埛宸茬粡涓庢垜浠�鑱旂郴浜嗭紝浠栦滑鍦ㄧ敓浜х郴缁熶腑鏈変竴涓�(娴嬭瘯)绯荤粺骞朵笖鏈夎礋杞介棶棰樸�傚ぇ澶氭暟鎯呭喌涓嬶紝鎬ц兘闂�棰樼粡璇佹槑鏄�涓庡熀鏈�鏁版嵁搴撹�捐�℃湁鍏崇殑闂�棰�(渚嬪�傦紝琛ㄦ壂鎻忓湪楂樿礋杞芥椂琛ㄧ幇涓嶅ソ)鎴栨搷浣滅郴缁熸垨搴撻棶棰樸�傚�傛灉绯荤粺宸茬粡涓嶅湪鐢熶骇绯荤粺涓�锛屽畠浠�澶у�氭暟灏�寰�瀹规槗淇�姝ｃ��

涓轰簡閬垮厤杩欐牱鐨勯棶棰橈紝搴旇�ユ妸宸ヤ綔閲嶇偣鏀惧湪鍦ㄥ彲鑳芥渶鍧忕殑璐熻浇涓嬫祴璇曚綘鐨勬暣涓�搴旂敤绋嬪簭銆備綘鍙�浠ヤ娇鐢�Super Smack銆傝�ュ伐鍏峰彲浠ヤ粠http://jeremy.zawodny.com/mysql/super-smack/鑾峰緱銆傛�ｅ�傚畠鐨勫悕瀛楁墍寤鸿��锛屽畠鍙�浠ユ牴鎹�浣犵殑闇�瑕佹彁渚涘悎鐞嗙殑绯荤粺锛屽洜姝ょ‘淇濆彧鐢ㄤ簬浣犵殑寮�鍙戠郴缁熴��

mysql> SELECT BENCHMARK(1000000,1+1)锛�

+------------------------+

| BENCHMARK(1000000,1+1) |

+------------------------+

|                      0 |

+------------------------+

1 row in set (0.32 sec)

EXPLAIN tbl_name

鎴栵細

EXPLAIN [EXTENDED] SELECT select_options

EXPLAIN璇�鍙ュ彲浠ョ敤浣�DESCRIBE鐨勪竴涓�鍚屼箟璇嶏紝鎴栬幏寰楀叧浜�MySQL濡備綍鎵ц��SELECT璇�鍙ョ殑淇℃伅锛�

路         EXPLAIN tbl_name鏄�DESCRIBE tbl_name鎴�SHOW COLUMNS FROM tbl_name鐨勪竴涓�鍚屼箟璇嶃��

路         濡傛灉鍦�SELECT璇�鍙ュ墠鏀句笂鍏抽敭璇�EXPLAIN锛�MySQL灏嗚В閲婂畠濡備綍澶勭悊SELECT锛屾彁渚涙湁鍏宠〃濡備綍鑱旀帴鍜岃仈鎺ョ殑娆″簭銆�

璇ヨ妭瑙ｉ噴EXPLAIN鐨勭��2涓�鐢ㄦ硶銆�

鍊熷姪浜�EXPLAIN锛屽彲浠ョ煡閬撲粈涔堟椂鍊欏繀椤讳负琛ㄥ姞鍏ョ储寮曚互寰楀埌涓�涓�浣跨敤绱㈠紩鏉ュ�绘壘璁板綍鐨勬洿蹇�鐨�SELECT銆�

濡傛灉鐢变簬浣跨敤涓嶆�ｇ‘鐨勭储寮曞嚭鐜颁簡闂�棰橈紝搴旇繍琛�ANALYZE TABLE鏇存柊琛ㄧ殑缁熻�★紙渚嬪�傚叧閿�瀛楅泦鐨勫娍锛夛紝杩欐牱浼氬奖鍝嶄紭鍖栧櫒杩涜�岀殑閫夋嫨銆傚弬瑙�13.5.2.1鑺傦紝鈥淎NALYZE TABLE璇�娉曗��銆�

杩樺彲浠ョ煡閬撲紭鍖栧櫒鏄�鍚︿互涓�涓�鏈�浣虫�″簭鑱旀帴琛ㄣ�備负浜嗗己鍒朵紭鍖栧櫒璁╀竴涓�SELECT璇�鍙ユ寜鐓ц〃鍛藉悕椤哄簭鐨勮仈鎺ユ�″簭锛岃��鍙ュ簲浠�STRAIGHT_JOIN鑰屼笉鍙�鏄�SELECT寮�澶淬��

EXPLAIN涓虹敤浜�SELECT璇�鍙ヤ腑鐨勬瘡涓�琛ㄨ繑鍥炰竴琛屼俊鎭�銆傝〃浠ュ畠浠�鍦ㄥ�勭悊鏌ヨ�㈣繃绋嬩腑灏嗚��MySQL璇诲叆鐨勯『搴忚��鍒楀嚭銆�MySQL鐢ㄤ竴閬嶆壂鎻忓�氭�¤仈鎺ワ紙single-sweep multi-join锛夌殑鏂瑰紡瑙ｅ喅鎵�鏈夎仈鎺ャ�傝繖鎰忓懗鐫�MySQL浠庣��涓�涓�琛ㄤ腑璇讳竴琛岋紝鐒跺悗鎵惧埌鍦ㄧ��浜屼釜琛ㄤ腑鐨勪竴涓�鍖归厤琛岋紝鐒跺悗鍦ㄧ��3涓�琛ㄤ腑绛夌瓑銆傚綋鎵�鏈夌殑琛ㄥ�勭悊瀹屽悗锛屽畠杈撳嚭閫変腑鐨勫垪骞朵笖杩斿洖琛ㄦ竻鍗曠洿鍒版壘鍒颁竴涓�鏈夋洿澶氱殑鍖归厤琛岀殑琛ㄣ�備粠璇ヨ〃璇诲叆涓嬩竴琛屽苟缁х画澶勭悊涓嬩竴涓�琛ㄣ��

褰撲娇鐢�EXTENDED鍏抽敭瀛楁椂锛�EXPLAIN浜х敓闄勫姞淇℃伅锛屽彲浠ョ敤SHOW WARNINGS娴忚�堛�傝�ヤ俊鎭�鏄剧ず浼樺寲鍣ㄩ檺瀹�SELECT璇�鍙ヤ腑鐨勮〃鍜屽垪鍚嶏紝閲嶅啓骞朵笖鎵ц�屼紭鍖栬�勫垯鍚�SELECT璇�鍙ユ槸浠�涔堟牱瀛愶紝骞朵笖杩樺彲鑳藉寘鎷�浼樺寲杩囩▼鐨勫叾瀹冩敞瑙ｃ��

EXPLAIN鐨勬瘡涓�杈撳嚭琛屾彁渚涗竴涓�琛ㄧ殑鐩稿叧淇℃伅锛屽苟涓旀瘡涓�琛屽寘鎷�涓嬮潰鐨勫垪锛�

路         id

SELECT璇嗗埆绗︺�傝繖鏄�SELECT鐨勬煡璇㈠簭鍒楀彿銆�

路         select_type

SELECT绫诲瀷锛屽彲浠ヤ负浠ヤ笅浠讳綍涓�绉嶏細

o        SIMPLE

绠�鍗�SELECT(涓嶄娇鐢�UNION鎴栧瓙鏌ヨ��)

o        PRIMARY

鏈�澶栭潰鐨�SELECT

o        UNION

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

o        DEPENDENT UNION

UNION涓�鐨勭��浜屼釜鎴栧悗闈㈢殑SELECT璇�鍙ワ紝鍙栧喅浜庡�栭潰鐨勬煡璇�

o        UNION RESULT

UNION鐨勭粨鏋溿��

o        SUBQUERY

瀛愭煡璇�涓�鐨勭��涓�涓�SELECT

o        DEPENDENT SUBQUERY

瀛愭煡璇�涓�鐨勭��涓�涓�SELECT锛屽彇鍐充簬澶栭潰鐨勬煡璇�

o        DERIVED

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

路         table

杈撳嚭鐨勮�屾墍寮曠敤鐨勮〃銆�

路         type

鑱旀帴绫诲瀷銆備笅闈㈢粰鍑哄悇绉嶈仈鎺ョ被鍨嬶紝鎸夌収浠庢渶浣崇被鍨嬪埌鏈�鍧忕被鍨嬭繘琛屾帓搴忥細

o        system

琛ㄤ粎鏈変竴琛�(=绯荤粺琛�)銆傝繖鏄�const鑱旀帴绫诲瀷鐨勪竴涓�鐗逛緥銆�

o        const

琛ㄦ渶澶氭湁涓�涓�鍖归厤琛岋紝瀹冨皢鍦ㄦ煡璇㈠紑濮嬫椂琚�璇诲彇銆傚洜涓轰粎鏈変竴琛岋紝鍦ㄨ繖琛岀殑鍒楀�煎彲琚�浼樺寲鍣ㄥ墿浣欓儴鍒嗚�や负鏄�甯告暟銆�const琛ㄥ緢蹇�锛屽洜涓哄畠浠�鍙�璇诲彇涓�娆★紒

const鐢ㄤ簬鐢ㄥ父鏁板�兼瘮杈�PRIMARY KEY鎴�UNIQUE绱㈠紩鐨勬墍鏈夐儴鍒嗘椂銆傚湪涓嬮潰鐨勬煡璇�涓�锛�tbl_name鍙�浠ョ敤浜�const琛�锛�

SELECT * from tbl_name WHERE primary_key=1锛�

SELECT * from tbl_name

WHERE primary_key_part1=1鍜� primary_key_part2=2锛�

o        eq_ref

瀵逛簬姣忎釜鏉ヨ嚜浜庡墠闈㈢殑琛ㄧ殑琛岀粍鍚堬紝浠庤�ヨ〃涓�璇诲彇涓�琛屻�傝繖鍙�鑳芥槸鏈�濂界殑鑱旀帴绫诲瀷锛岄櫎浜�const绫诲瀷銆傚畠鐢ㄥ湪涓�涓�绱㈠紩鐨勬墍鏈夐儴鍒嗚��鑱旀帴浣跨敤骞朵笖绱㈠紩鏄�UNIQUE鎴�PRIMARY KEY銆�

eq_ref鍙�浠ョ敤浜庝娇鐢�= 鎿嶄綔绗︽瘮杈冪殑甯︾储寮曠殑鍒椼�傛瘮杈冨�煎彲浠ヤ负甯搁噺鎴栦竴涓�浣跨敤鍦ㄨ�ヨ〃鍓嶉潰鎵�璇诲彇鐨勮〃鐨勫垪鐨勮〃杈惧紡銆�

鍦ㄤ笅闈㈢殑渚嬪瓙涓�锛�MySQL鍙�浠ヤ娇鐢�eq_ref鑱旀帴鏉ュ�勭悊ref_tables锛�

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column_part1=other_table.column

    AND ref_table.key_column_part2=1;

o        ref

瀵逛簬姣忎釜鏉ヨ嚜浜庡墠闈㈢殑琛ㄧ殑琛岀粍鍚堬紝鎵�鏈夋湁鍖归厤绱㈠紩鍊肩殑琛屽皢浠庤繖寮犺〃涓�璇诲彇銆傚�傛灉鑱旀帴鍙�浣跨敤閿�鐨勬渶宸﹁竟鐨勫墠缂�锛屾垨濡傛灉閿�涓嶆槸UNIQUE鎴�PRIMARY KEY锛堟崲鍙ヨ瘽璇达紝濡傛灉鑱旀帴涓嶈兘鍩轰簬鍏抽敭瀛楅�夋嫨鍗曚釜琛岀殑璇濓級锛屽垯浣跨敤ref銆傚�傛灉浣跨敤鐨勯敭浠呬粎鍖归厤灏戦噺琛岋紝璇ヨ仈鎺ョ被鍨嬫槸涓嶉敊鐨勩��

ref鍙�浠ョ敤浜庝娇鐢�=鎴�<=>鎿嶄綔绗︾殑甯︾储寮曠殑鍒椼��

鍦ㄤ笅闈㈢殑渚嬪瓙涓�锛�MySQL鍙�浠ヤ娇鐢�ref鑱旀帴鏉ュ�勭悊ref_tables锛�

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column_part1=other_table.column

    AND ref_table.key_column_part2=1;

o        ref_or_null

璇ヨ仈鎺ョ被鍨嬪�傚悓ref锛屼絾鏄�娣诲姞浜�MySQL鍙�浠ヤ笓闂ㄦ悳绱㈠寘鍚�NULL鍊肩殑琛屻�傚湪瑙ｅ喅瀛愭煡璇�涓�缁忓父浣跨敤璇ヨ仈鎺ョ被鍨嬬殑浼樺寲銆�

鍦ㄤ笅闈㈢殑渚嬪瓙涓�锛�MySQL鍙�浠ヤ娇鐢�ref_or_null鑱旀帴鏉ュ�勭悊ref_tables锛�

SELECT * FROM ref_table

WHERE key_column=expr OR key_column IS NULL;

鍙傝��7.2.7鑺傦紝鈥淢ySQL濡備綍浼樺寲IS NULL鈥�銆�

o        index_merge

璇ヨ仈鎺ョ被鍨嬭〃绀轰娇鐢ㄤ簡绱㈠紩鍚堝苟浼樺寲鏂规硶銆傚湪杩欑�嶆儏鍐典笅锛�key鍒楀寘鍚�浜嗕娇鐢ㄧ殑绱㈠紩鐨勬竻鍗曪紝key_len鍖呭惈浜嗕娇鐢ㄧ殑绱㈠紩鐨勬渶闀跨殑鍏抽敭鍏冪礌銆傝�︾粏淇℃伅鍙傝��7.2.6鑺傦紝鈥滅储寮曞悎骞朵紭鍖栤��銆�

o        unique_subquery

璇ョ被鍨嬫浛鎹�浜嗕笅闈㈠舰寮忕殑IN瀛愭煡璇㈢殑ref锛�

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery鏄�涓�涓�绱㈠紩鏌ユ壘鍑芥暟锛屽彲浠ュ畬鍏ㄦ浛鎹㈠瓙鏌ヨ��锛屾晥鐜囨洿楂樸��

o        index_subquery

璇ヨ仈鎺ョ被鍨嬬被浼间簬unique_subquery銆傚彲浠ユ浛鎹�IN瀛愭煡璇�锛屼絾鍙�閫傚悎涓嬪垪褰㈠紡鐨勫瓙鏌ヨ��涓�鐨勯潪鍞�涓�绱㈠紩锛�

value IN (SELECT key_column FROM single_table WHERE some_expr)

o        range

鍙�妫�绱㈢粰瀹氳寖鍥寸殑琛岋紝浣跨敤涓�涓�绱㈠紩鏉ラ�夋嫨琛屻��key鍒楁樉绀轰娇鐢ㄤ簡鍝�涓�绱㈠紩銆�key_len鍖呭惈鎵�浣跨敤绱㈠紩鐨勬渶闀垮叧閿�鍏冪礌銆傚湪璇ョ被鍨嬩腑ref鍒椾负NULL銆�

褰撲娇鐢�=銆�<>銆�>銆�>=銆�<銆�<=銆�IS NULL銆�<=>銆�BETWEEN鎴栬��IN鎿嶄綔绗︼紝鐢ㄥ父閲忔瘮杈冨叧閿�瀛楀垪鏃讹紝鍙�浠ヤ娇鐢�range锛�

SELECT * FROM tbl_name

WHERE key_column = 10;

SELECT * FROM tbl_name

WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name

WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name

WHERE key_part1= 10 AND key_part2 IN (10,20,30);

o        index

璇ヨ仈鎺ョ被鍨嬩笌ALL鐩稿悓锛岄櫎浜嗗彧鏈夌储寮曟爲琚�鎵�鎻忋�傝繖閫氬父姣�ALL蹇�锛屽洜涓虹储寮曟枃浠堕�氬父姣旀暟鎹�鏂囦欢灏忋��

褰撴煡璇㈠彧浣跨敤浣滀负鍗曠储寮曚竴閮ㄥ垎鐨勫垪鏃讹紝MySQL鍙�浠ヤ娇鐢ㄨ�ヨ仈鎺ョ被鍨嬨��

o        ALL

瀵逛簬姣忎釜鏉ヨ嚜浜庡厛鍓嶇殑琛ㄧ殑琛岀粍鍚堬紝杩涜�屽畬鏁寸殑琛ㄦ壂鎻忋�傚�傛灉琛ㄦ槸绗�涓�涓�娌℃爣璁�const鐨勮〃锛岃繖閫氬父涓嶅ソ锛屽苟涓旈�氬父鍦ㄥ畠鎯呭喌涓�寰�宸�銆傞�氬父鍙�浠ュ�炲姞鏇村�氱殑绱㈠紩鑰屼笉瑕佷娇鐢�ALL锛屼娇寰楄�岃兘鍩轰簬鍓嶉潰鐨勮〃涓�鐨勫父鏁板�兼垨鍒楀�艰��妫�绱㈠嚭銆�

路         possible_keys

possible_keys鍒楁寚鍑�MySQL鑳戒娇鐢ㄥ摢涓�绱㈠紩鍦ㄨ�ヨ〃涓�鎵惧埌琛屻�傛敞鎰忥紝璇ュ垪瀹屽叏鐙�绔嬩簬EXPLAIN杈撳嚭鎵�绀虹殑琛ㄧ殑娆″簭銆傝繖鎰忓懗鐫�鍦�possible_keys涓�鐨勬煇浜涢敭瀹為檯涓婁笉鑳芥寜鐢熸垚鐨勮〃娆″簭浣跨敤銆�

濡傛灉璇ュ垪鏄�NULL锛屽垯娌℃湁鐩稿叧鐨勭储寮曘�傚湪杩欑�嶆儏鍐典笅锛屽彲浠ラ�氳繃妫�鏌�WHERE瀛愬彞鐪嬫槸鍚﹀畠寮曠敤鏌愪簺鍒楁垨閫傚悎绱㈠紩鐨勫垪鏉ユ彁楂樹綘鐨勬煡璇㈡�ц兘銆傚�傛灉鏄�杩欐牱锛屽垱閫犱竴涓�閫傚綋鐨勭储寮曞苟涓斿啀娆＄敤EXPLAIN妫�鏌ユ煡璇�銆傚弬瑙�13.1.2鑺傦紝鈥淎LTER TABLE璇�娉曗��銆�

涓轰簡鐪嬫竻涓�寮犺〃鏈変粈涔堢储寮曪紝浣跨敤SHOW INDEX FROM tbl_name銆�

路         key

key鍒楁樉绀�MySQL瀹為檯鍐冲畾浣跨敤鐨勯敭锛堢储寮曪級銆傚�傛灉娌℃湁閫夋嫨绱㈠紩锛岄敭鏄�NULL銆傝�佹兂寮哄埗MySQL浣跨敤鎴栧拷瑙�possible_keys鍒椾腑鐨勭储寮曪紝鍦ㄦ煡璇�涓�浣跨敤FORCE INDEX銆�USE INDEX鎴栬��IGNORE INDEX銆傚弬瑙�13.2.7鑺傦紝鈥淪ELECT璇�娉曗��銆�

瀵逛簬MyISAM鍜�BDB琛�锛岃繍琛�ANALYZE TABLE鍙�浠ュ府鍔╀紭鍖栧櫒閫夋嫨鏇村ソ鐨勭储寮曘�傚�逛簬MyISAM琛�锛屽彲浠ヤ娇鐢�myisamchk --analyze銆傚弬瑙�13.5.2.1鑺傦紝鈥淎NALYZE TABLE璇�娉曗��鍜�5.9.4鑺傦紝鈥滆〃缁存姢鍜屽穿婧冩仮澶嶁��銆�

路         key_len

key_len鍒楁樉绀�MySQL鍐冲畾浣跨敤鐨勯敭闀垮害銆傚�傛灉閿�鏄�NULL锛屽垯闀垮害涓�NULL銆傛敞鎰忛�氳繃key_len鍊兼垜浠�鍙�浠ョ‘瀹�MySQL灏嗗疄闄呬娇鐢ㄤ竴涓�澶氶儴鍏抽敭瀛楃殑鍑犱釜閮ㄥ垎銆�

路         ref

ref鍒楁樉绀轰娇鐢ㄥ摢涓�鍒楁垨甯告暟涓�key涓�璧蜂粠琛ㄤ腑閫夋嫨琛屻��

路         rows

rows鍒楁樉绀�MySQL璁や负瀹冩墽琛屾煡璇㈡椂蹇呴』妫�鏌ョ殑琛屾暟銆�

路         Extra

璇ュ垪鍖呭惈MySQL瑙ｅ喅鏌ヨ�㈢殑璇︾粏淇℃伅銆備笅闈㈣В閲婁簡璇ュ垪鍙�浠ユ樉绀虹殑涓嶅悓鐨勬枃鏈�瀛楃�︿覆锛�

o        Distinct

MySQL鍙戠幇绗�1涓�鍖归厤琛屽悗锛屽仠姝�涓哄綋鍓嶇殑琛岀粍鍚堟悳绱㈡洿澶氱殑琛屻��

o        Not exists

MySQL鑳藉�熷�规煡璇㈣繘琛�LEFT JOIN浼樺寲锛屽彂鐜�1涓�鍖归厤LEFT JOIN鏍囧噯鐨勮�屽悗锛屼笉鍐嶄负鍓嶉潰鐨勭殑琛岀粍鍚堝湪璇ヨ〃鍐呮��鏌ユ洿澶氱殑琛屻��

涓嬮潰鏄�涓�涓�鍙�浠ヨ繖鏍蜂紭鍖栫殑鏌ヨ�㈢被鍨嬬殑渚嬪瓙锛�

SELECT * 浠�t1 LEFT JOIN t2 ON t1.id=t2.id

  WHERE t2.id IS NULL锛�

鍋囧畾t2.id瀹氫箟涓�NOT NULL銆傚湪杩欑�嶆儏鍐典笅锛�MySQL浣跨敤t1.id鐨勫�兼壂鎻�t1骞舵煡鎵�t2涓�鐨勮�屻�傚�傛灉MySQL鍦�t2涓�鍙戠幇涓�涓�鍖归厤鐨勮�岋紝瀹冪煡閬�t2.id缁濅笉浼氫负NULL锛屽苟涓斾笉鍐嶆壂鎻�t2鍐呮湁鐩稿悓鐨�id鍊肩殑琛屻�傛崲鍙ヨ瘽璇达紝瀵逛簬t1鐨勬瘡涓�琛岋紝MySQL鍙�闇�瑕佸湪t2涓�鏌ユ壘涓�娆★紝鏃犺��t2鍐呭疄闄呮湁澶氬皯鍖归厤鐨勮�屻��

o        range checked for each record (index map: #)

MySQL娌℃湁鍙戠幇濂界殑鍙�浠ヤ娇鐢ㄧ殑绱㈠紩锛屼絾鍙戠幇濡傛灉鏉ヨ嚜鍓嶉潰鐨勮〃鐨勫垪鍊煎凡鐭ワ紝鍙�鑳介儴鍒嗙储寮曞彲浠ヤ娇鐢ㄣ�傚�瑰墠闈㈢殑琛ㄧ殑姣忎釜琛岀粍鍚堬紝MySQL妫�鏌ユ槸鍚﹀彲浠ヤ娇鐢�range鎴�index_merge璁块棶鏂规硶鏉ョ储鍙栬�屻�傚叧浜庨�傜敤鎬ф爣鍑嗙殑鎻忚堪鍙傝��7.2.5鑺傦紝鈥滆寖鍥翠紭鍖栤��鍜�7.2.6鑺傦紝鈥滅储寮曞悎骞朵紭鍖栤��锛屼笉鍚岀殑鏄�鍓嶉潰琛ㄧ殑鎵�鏈夊垪鍊煎凡鐭ュ苟涓旇�や负鏄�甯搁噺銆�

杩欏苟涓嶅緢蹇�锛屼絾姣旀墽琛屾病鏈夌储寮曠殑鑱旀帴瑕佸揩寰楀�氥��

o        Using filesort

MySQL闇�瑕侀�濆�栫殑涓�娆′紶閫掞紝浠ユ壘鍑哄�備綍鎸夋帓搴忛『搴忔��绱㈣�屻�傞�氳繃鏍规嵁鑱旀帴绫诲瀷娴忚�堟墍鏈夎�屽苟涓烘墍鏈夊尮閰�WHERE瀛愬彞鐨勮�屼繚瀛樻帓搴忓叧閿�瀛楀拰琛岀殑鎸囬拡鏉ュ畬鎴愭帓搴忋�傜劧鍚庡叧閿�瀛楄��鎺掑簭锛屽苟鎸夋帓搴忛『搴忔��绱㈣�屻�傚弬瑙�7.2.12鑺傦紝鈥淢ySQL濡備綍浼樺寲ORDER BY鈥�銆�

o        Using index

浠庡彧浣跨敤绱㈠紩鏍戜腑鐨勪俊鎭�鑰屼笉闇�瑕佽繘涓�姝ユ悳绱㈣�诲彇瀹為檯鐨勮�屾潵妫�绱㈣〃涓�鐨勫垪淇℃伅銆傚綋鏌ヨ�㈠彧浣跨敤浣滀负鍗曚竴绱㈠紩涓�閮ㄥ垎鐨勫垪鏃讹紝鍙�浠ヤ娇鐢ㄨ�ョ瓥鐣ャ��

o        Using temporary

涓轰簡瑙ｅ喅鏌ヨ��锛�MySQL闇�瑕佸垱寤轰竴涓�涓存椂琛ㄦ潵瀹圭撼缁撴灉銆傚吀鍨嬫儏鍐靛�傛煡璇㈠寘鍚�鍙�浠ユ寜涓嶅悓鎯呭喌鍒楀嚭鍒楃殑GROUP BY鍜�ORDER BY瀛愬彞鏃躲��

o        Using where

WHERE瀛愬彞鐢ㄤ簬闄愬埗鍝�涓�涓�琛屽尮閰嶄笅涓�涓�琛ㄦ垨鍙戦�佸埌瀹㈡埛銆傞櫎闈炰綘涓撻棬浠庤〃涓�绱㈠彇鎴栨��鏌ユ墍鏈夎�岋紝濡傛灉Extra鍊间笉涓�Using where骞朵笖琛ㄨ仈鎺ョ被鍨嬩负ALL鎴�index锛屾煡璇㈠彲鑳戒細鏈変竴浜涢敊璇�銆�

濡傛灉鎯宠�佷娇鏌ヨ�㈠敖鍙�鑳藉揩锛屽簲鎵惧嚭Using filesort 鍜�Using temporary鐨�Extra鍊笺��

o        Using sort_union(...), Using union(...), Using intersect(...)

杩欎簺鍑芥暟璇存槑濡備綍涓�index_merge鑱旀帴绫诲瀷鍚堝苟绱㈠紩鎵�鎻忋�傝�︾粏淇℃伅鍙傝��7.2.6鑺傦紝鈥滅储寮曞悎骞朵紭鍖栤��銆�

o        Using index for group-by

绫讳技浜庤�块棶琛ㄧ殑Using index鏂瑰紡锛�Using index for group-by琛ㄧずMySQL鍙戠幇浜嗕竴涓�绱㈠紩锛屽彲浠ョ敤鏉ユ煡璇�GROUP BY鎴�DISTINCT鏌ヨ�㈢殑鎵�鏈夊垪锛岃�屼笉瑕侀�濆�栨悳绱㈢‖鐩樿�块棶瀹為檯鐨勮〃銆傚苟涓旓紝鎸夋渶鏈夋晥鐨勬柟寮忎娇鐢ㄧ储寮曪紝浠ヤ究瀵逛簬姣忎釜缁勶紝鍙�璇诲彇灏戦噺绱㈠紩鏉＄洰銆傝�︽儏鍙傝��7.2.13鑺傦紝鈥淢ySQL濡備綍浼樺寲GROUP BY鈥�銆�

閫氳繃鐩镐箻EXPLAIN杈撳嚭鐨�rows鍒楃殑鎵�鏈夊�硷紝浣犺兘寰楀埌涓�涓�鍏充簬涓�涓�鑱旀帴濡備綍鐨勬彁绀恒�傝繖搴旇�ョ矖鐣ュ湴鍛婅瘔浣�MySQL蹇呴』妫�鏌ュ�氬皯琛屼互鎵ц�屾煡璇�銆傚綋浣犱娇鐢�max_join_size鍙橀噺闄愬埗鏌ヨ�㈡椂锛屼篃鐢ㄨ繖涓�涔樼Н鏉ョ‘瀹氭墽琛屽摢涓�澶氳〃SELECT璇�鍙ャ�傚弬瑙�7.5.2鑺傦紝鈥滆皟鑺傛湇鍔″櫒鍙傛暟鈥�銆�

涓嬪垪渚嬪瓙鏄剧ず鍑轰竴涓�澶氳〃JOIN濡備綍鑳戒娇鐢�EXPLAIN鎻愪緵鐨勪俊鎭�閫愭�ヨ��浼樺寲銆�

鍋囧畾浣犳湁涓嬮潰鎵�绀虹殑SELECT璇�鍙ワ紝璁″垝浣跨敤EXPLAIN鏉ユ��鏌ュ畠锛�

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;

瀵逛簬杩欎釜渚嬪瓙锛屽亣瀹氾細

路         琚�姣旇緝鐨勫垪澹版槑濡備笅锛�

路         琛ㄦ湁涓嬮潰鐨勭储寮曪細

路         tt.ActualPC鍊间笉鏄�鍧囧寑鍒嗗竷鐨勩��

寮�濮嬶紝鍦ㄨ繘琛屼紭鍖栧墠锛�EXPLAIN璇�鍙ヤ骇鐢熶笅鍒椾俊鎭�锛�

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,   NULL NULL    NULL 3872

           ClientID,

           ActualPC

      range checked for each record (key map: 35)

鍥犱负type瀵规瘡寮犺〃鏄�ALL锛岃繖涓�杈撳嚭鏄剧ずMySQL姝ｅ湪瀵规墍鏈夎〃浜х敓涓�涓�绗涘崱灏斾箻绉�锛涘嵆姣忎竴涓�琛岀殑缁勫悎锛佽繖灏嗚姳鐩稿綋闀跨殑鏃堕棿锛屽洜涓哄繀椤绘��鏌ユ瘡寮犺〃鐨勮�屾暟鐨勪箻绉�锛佸�逛簬涓�涓�瀹炰緥锛岃繖鏄�74 * 2135 * 74 * 3872 = 45,268,558,720琛屻�傚�傛灉琛ㄦ洿澶э紝浣犲彧鑳芥兂璞″畠灏嗚姳澶氶暱鏃堕棿鈥︹��

杩欓噷鐨勪竴涓�闂�棰樻槸MySQL鑳芥洿楂樻晥鍦板湪澹版槑鍏锋湁鐩稿悓绫诲瀷鍜屽昂瀵哥殑鍒椾笂浣跨敤绱㈠紩銆傚湪鏈�鏂囦腑锛�VARCHAR鍜�CHAR鏄�鐩稿悓鐨勶紝闄ら潪瀹冧滑澹版槑涓轰笉鍚岀殑闀垮害銆傚洜涓�tt.ActualPC琚�澹版槑涓�CHAR(10)骞朵笖et.EMPLOYID琚�澹版槑涓�CHAR(15)锛岄暱搴︿笉鍖归厤銆�

涓轰簡淇�姝ｅ湪鍒楅暱搴︿笂鐨勪笉鍚岋紝浣跨敤ALTER TABLE灏�ActualPC鐨勯暱搴︿粠10涓�瀛楃�﹀彉涓�15涓�瀛楃�︼細

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

鐜板湪tt.ActualPC鍜�et.EMPLOYID閮芥槸VARCHAR(15)锛屽啀鎵ц��EXPLAIN璇�鍙ヤ骇鐢熻繖涓�缁撴灉锛�

table type   possible_keys key     key_len ref         rows    Extra

tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using

             ClientID,                                         where

             ActualPC

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

杩欎笉鏄�瀹岀編鐨勶紝浣嗘槸濂戒竴浜涗簡锛�rows鍊肩殑涔樼Н灏戜簡涓�涓�鍥犲瓙74銆傝繖涓�鐗堟湰鍦ㄥ嚑绉掑唴鎵ц�屽畬銆�

绗�2绉嶆柟娉曡兘娑堥櫎tt.AssignedPC = et_1.EMPLOYID鍜�tt.ClientID = do.CUSTNMBR姣旇緝鐨勫垪鐨勯暱搴﹀け閰嶉棶棰橈細

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),

    ->                MODIFY ClientID   VARCHAR(15);

EXPLAIN浜х敓鐨勮緭鍑烘樉绀哄湪涓嬮潰锛�

table type   possible_keys key      key_len ref           rows Extra

et    ALL    PRIMARY       NULL     NULL    NULL          74

tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using

             ClientID,                                         where

             ActualPC

et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1

do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

杩欏嚑涔庡緢濂戒簡銆�

鍓╀笅鐨勯棶棰樻槸锛岄粯璁ゆ儏鍐碉紝MySQL鍋囪�惧湪tt.ActualPC鍒楃殑鍊兼槸鍧囧寑鍒嗗竷鐨勶紝骞朵笖瀵�tt琛ㄤ笉鏄�杩欐牱銆傚垢濂斤紝寰堝�规槗鍛婅瘔MySQL鏉ュ垎鏋愬叧閿�瀛楀垎甯冿細

mysql> ANALYZE TABLE tt锛�

鐜板湪鑱旀帴鏄�鈥滃畬缇庘�濈殑浜嗭紝鑰屼笖EXPLAIN浜х敓杩欎釜缁撴灉锛�

table type   possible_keys key     key_len ref           rows Extra

tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using

             ClientID,                                        where

             ActualPC

et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1

et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1

do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

娉ㄦ剰鍦ㄤ粠EXPLAIN杈撳嚭鐨�rows鍒楁槸涓�涓�鏉ヨ嚜MySQL鑱旀帴浼樺寲鍣ㄧ殑鈥滄暀鑲茬寽娴嬧�濄�備綘搴旇�ユ��鏌ユ暟瀛楁槸鍚︽帴杩戜簨瀹炪�傚�傛灉涓嶆槸锛屽彲浠ラ�氳繃鍦�SELECT璇�鍙ラ噷闈�浣跨敤STRAIGHT_JOIN骞朵笖璇曠潃鍦�FROM瀛愬彞浠ヤ笉鍚岀殑娆″簭鍒楀嚭琛�锛屽彲鑳藉緱鍒版洿濂界殑鎬ц兘銆�

鍦ㄥぇ澶氭暟鎯呭喌涓嬶紝鍙�浠ラ�氳繃璁＄畻纾佺洏鎼滅储鏉ヤ及璁℃�ц兘銆傚�瑰皬鐨勮〃锛岄�氬父鑳藉湪1娆＄�佺洏鎼滅储涓�鎵惧埌琛�(鍥犱负绱㈠紩鍙�鑳借��缂撳瓨)銆傚�规洿澶х殑琛�锛屽彲浠ヤ娇鐢�B锛嶆爲绱㈠紩杩涜�屼及璁★紝灏嗛渶瑕�log(row_count)/log(index_block_length/3 * 2/(index_length + data_pointer_length))+1娆℃悳绱㈡墠鑳芥壘鍒拌�屻��

鍦�MySQL涓�锛岀储寮曞潡閫氬父鏄�1024涓�瀛楄妭锛屾暟鎹�鎸囬拡閫氬父鏄�4涓�瀛楄妭锛岃繖瀵逛簬鏈変竴涓�闀垮害涓�3(涓�绛夋暣鏁�)鐨勭储寮曠殑500,000琛岀殑琛�锛岄�氳繃鍏�寮忓彲浠ヨ�＄畻鍑�log(500,000)/log(1024/3*2/(3+4))+1= 4娆℃悳绱�銆�

涓婇潰鐨勭储寮曢渶瑕佸ぇ绾�500,000 * 7 * 3/2 = 5.2MB锛�(鍋囪�惧吀鍨嬫儏鍐典笅绱㈠紩缂撳瓨鍖哄～鍏呯巼涓�2/3)锛屽彲浠ュ皢澶ч儴鍒嗙储寮曚繚瀛樺湪鍐呭瓨涓�锛屼粎闇�瑕�1-2璋冪敤浠�OS璇绘暟鎹�鏉ユ壘鍑鸿�屻��

鐒惰�屽�逛簬鍐欙紝灏嗛渶瑕�4娆℃悳绱㈣�锋眰(濡備笂)鏉ユ壘鍒板湪鍝�鍎垮瓨鏀炬柊绱㈠紩锛屽苟涓旈�氬父闇�瑕�2娆℃悳绱㈡潵鏇存柊杩欎釜绱㈠紩骞朵笖鍐欏叆琛屻��

娉ㄦ剰锛屼笂杩拌�ㄨ�哄苟涓嶆剰鍛崇潃搴旂敤绋嬪簭鐨勬�ц兘灏嗙紦鎱㈠湴浠�logN 閫�鍖栵紒褰撹〃鏍煎彉寰楁洿澶ф椂锛屾墍鏈夊唴瀹圭紦瀛樺埌OS鎴�SQL鏈嶅姟鍣ㄥ悗锛屽皢浠呬粎鎴栧�氭垨灏戝湴鏇存參銆傚湪鏁版嵁鍙樺緱澶�澶т笉鑳界紦瀛樺悗锛屽皢閫愭笎鍙樺緱鏇存參锛岀洿鍒板簲鐢ㄧ▼搴忓彧鑳借繘琛岀�佺洏鎼滅储(浠�logN澧炲姞)銆備负浜嗛伩鍏嶈繖涓�闂�棰橈紝闅忔暟鎹�澧炲姞鑰屽�炲姞 閿�楂橀�熺紦鍐插尯澶у皬銆傚�逛簬MyISAM琛�, 鐢�key_buffer_size绯荤粺鍙橀噺鎺у埗 閿�楂橀�熺紦鍐插尯澶у皬銆傚弬瑙�7.5.2鑺傦紝鈥滆皟鑺傛湇鍔″櫒鍙傛暟鈥�銆�

鎬荤殑鏉ヨ�达紝瑕佹兂浣夸竴涓�杈冩參閫�SELECT ... WHERE鏇村揩锛屽簲棣栧厛妫�鏌ユ槸鍚﹁兘澧炲姞涓�涓�绱㈠紩銆備笉鍚岃〃涔嬮棿鐨勫紩鐢ㄩ�氬父閫氳繃绱㈠紩鏉ュ畬鎴愩�備綘鍙�浠ヤ娇鐢�EXPLAIN璇�鍙ユ潵纭�瀹�SELECT璇�鍙ヤ娇鐢ㄥ摢浜涚储寮曘�傚弬瑙�7.4.5鑺傦紝鈥淢ySQL濡備綍浣跨敤绱㈠紩鈥�鍜�7.2.1鑺傦紝鈥淓XPLAIN璇�娉曪紙鑾峰彇鍏充簬SELECT鐨勪俊鎭�锛�鈥�銆�

涓嬮潰鏄�涓�浜涘姞閫熷��MyISAM琛ㄧ殑鏌ヨ�㈢殑涓�鑸�寤鸿��锛�

路         涓轰簡甯�鍔�MySQL鏇村ソ鍦颁紭鍖栨煡璇�锛屽湪涓�涓�瑁呰浇鏁版嵁鍚庣殑琛ㄤ笂杩愯��ANALYZE TABLE鎴�myisamchk --analyze銆傝繖鏍蜂负姣忎竴涓�绱㈠紩鏇存柊鎸囧嚭鏈夌浉鍚屽�肩殑琛岀殑骞冲潎琛屾暟鐨勫�硷紙褰撶劧锛屽�傛灉鍙�鏈変竴涓�绱㈠紩锛岃繖鎬绘槸1銆傦級MySQL浣跨敤璇ユ柟娉曟潵鍐冲畾褰撲綘鑱旀帴涓や釜鍩轰簬闈炲父閲忚〃杈惧紡鐨勮〃鏃堕�夋嫨鍝�涓�绱㈠紩銆備綘鍙�浠ヤ娇鐢�SHOW INDEX FROM tbl_name骞舵��鏌�Cardinality鍊兼潵妫�鏌ヨ〃鍒嗘瀽缁撴灉銆�myisamchk --description --verbose鍙�浠ユ樉绀虹储寮曞垎甯冧俊鎭�銆�

路         瑕佹兂鏍规嵁涓�涓�绱㈠紩鎺掑簭涓�涓�绱㈠紩鍜屾暟鎹�锛屼娇鐢�myisamchk --sort-index --sort-records=1锛堝�傛灉浣犳兂瑕佸湪绱㈠紩1涓婃帓搴忥級銆傚�傛灉鍙�鏈変竴涓�绱㈠紩锛屾兂瑕佹牴鎹�璇ョ储寮曠殑娆″簭璇诲彇鎵�鏈夌殑璁板綍锛岃繖鏄�浣挎煡璇㈡洿蹇�鐨勪竴涓�濂芥柟娉曘�備絾鏄�璇锋敞鎰忥紝绗�涓�娆″�逛竴涓�澶ц〃鎸夌収杩欑�嶆柟娉曟帓搴忔椂灏嗚姳寰堥暱鏃堕棿锛�

SELECT * FROM tbl_name WHERE key_col IS NULL;

SELECT * FROM tbl_name WHERE key_col <=> NULL;

SELECT * FROM tbl_name

    WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;

SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;

SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;

SELECT * FROM t1, t2

    WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;

SELECT * FROM t1, t2

    WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);

SELECT * FROM t1, t2

    WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)

    OR (t1.a=t2.a AND t2.a IS NULL AND ...);

SELECT * FROM t1, t2

     WHERE (t1.a=t2.a AND t2.a IS NULL)

     OR (t1.b=t2.b AND t2.b IS NULL);

鍦ㄨ�稿�氭儏鍐典笅缁撳悎ORDER BY鐨�DISTINCT闇�瑕佷竴涓�涓存椂琛ㄣ��

璇锋敞鎰忓洜涓�DISTINCT鍙�鑳戒娇鐢�GROUP BY锛屽繀椤绘竻妤�MySQL濡備綍浣跨敤鎵�閫夊畾鍒楃殑涓�閮ㄥ垎鐨�ORDER BY鎴�HAVING瀛愬彞涓�鐨勫垪銆傚弬瑙�12.10.3鑺傦紝鈥滃叿鏈夐殣鍚�瀛楁�电殑GROUP BY鈥�銆�

鍦ㄥぇ澶氭暟鎯呭喌涓嬶紝DISTINCT瀛愬彞鍙�浠ヨ�嗕负GROUP BY鐨勭壒娈婃儏鍐点�備緥濡傦紝涓嬮潰鐨勪袱涓�鏌ヨ�㈡槸绛夋晥鐨勶細

SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;

SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;

鐢变簬杩欎釜绛夋晥鎬э紝閫傜敤浜�GROUP BY鏌ヨ�㈢殑浼樺寲涔熼�傜敤浜庢湁DISTINCT瀛愬彞鐨勬煡璇�銆傝繖鏍凤紝鍏充簬DISTINCT鏌ヨ�㈢殑浼樺寲鐨勬洿璇︾粏鐨勬儏鍐碉紝鍙傝��7.2.13鑺傦紝鈥淢ySQL濡備綍浼樺寲GROUP BY鈥�銆�

缁撳悎LIMIT row_count鍜�DISTINCT鍚庯紝MySQL鍙戠幇鍞�涓�鐨�row_count琛屽悗绔嬪嵆鍋滄��銆�

濡傛灉涓嶄娇鐢ㄦ煡璇�涓�鍛藉悕鐨勬墍鏈夎〃鐨勫垪锛�MySQL鍙戠幇绗�1涓�鍖归厤鍚庣珛鍗冲仠姝㈡壂鎻忔湭浣跨敤鐨勮〃銆傚湪涓嬮潰鐨勬儏鍐典腑锛屽亣瀹�t1鍦�t2涔嬪墠浣跨敤(鍙�浠ョ敤EXPLAIN妫�鏌�)锛屽彂鐜�t2涓�鐨勭��1琛屽悗锛�MySQL涓嶅啀(涓�t1涓�鐨勪换浣曡��)璇�t2锛�

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

SELECT *

    FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)

    WHERE b.key=d.key;

SELECT *

    FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)

    WHERE b.key=d.key;

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

琛ㄧず鑱旀帴鐨勮��娉曞厑璁稿祵濂楄仈鎺ャ�備笅闈㈢殑璁ㄨ�哄紩鐢ㄤ簡13.2.7.1鑺傦紝鈥淛OIN璇�娉曗��涓�鎻忚堪鐨勮仈鎺ヨ��娉曘��

鍚�SQL鏍囧噯姣旇緝锛�table_factor璇�娉曞凡缁忔墿灞曚簡銆傚悗鑰呭彧鎺ュ彈table_reference锛岃�屼笉鏄�鎷�鍙峰唴鎵�鍒楃殑銆�

table_reference椤瑰垪琛ㄥ唴鐨勬瘡涓�閫楀彿绛変环浜庡唴閮ㄨ仈鎺ワ紝杩欐槸涓�涓�淇濈暀鎵╁睍鍚嶃�備緥濡傦細

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)

                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

绛変环浜庯細

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)

                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

鍦�MySQL涓�锛�CROSS JOIN璇�娉曚笂绛変环浜�INNER JOIN (瀹冧滑鍙�浠ュ郊姝や唬鏇裤�傚湪鏍囧噯SQL涓�锛屽畠浠�涓嶇瓑浠枫��INNER JOIN缁撳悎ON瀛愬彞浣跨敤锛�CROSS JOIN 鐢ㄤ簬鍏跺畠鍦版柟銆�

鎬荤殑鏉ヨ�达紝鍦ㄥ彧鍖呭惈鍐呴儴鑱旀帴鎿嶄綔鐨勮仈鎺ヨ〃杈惧紡涓�鍙�浠ュ拷鐣ユ嫭鍙枫�傚垹闄ゆ嫭鍙峰苟灏嗘搷浣滅粍鍚堝埌宸︿晶鍚庯紝鑱旀帴琛ㄨ揪寮忥細

t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)

   ON t1.a=t2.a

杞�鎹�涓鸿〃杈惧紡锛�

(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3

    ON t2.b=t3.b OR t2.b IS NULL

浣嗘槸杩欎袱涓�琛ㄨ揪寮忎笉绛夋晥銆傝�佽�存槑杩欑偣锛屽亣瀹氳〃t1銆�t2鍜�t3鏈変笅闈㈢殑鐘舵�侊細

路         琛�t1鍖呭惈琛�{1}銆�{2}

路         琛�t2鍖呭惈琛�{1,101}

路         琛�t3鍖呭惈琛�{101}

鍦ㄨ繖绉嶆儏鍐典笅锛岀��1涓�琛ㄨ揪寮忚繑鍥炲寘鎷�琛�{1,1,101,101}銆�{2,NULL,NULL,NULL}鐨勭粨鏋滐紝绗�2涓�琛ㄨ揪寮忚繑鍥炶��{1,1,101,101}銆�{2,NULL,NULL,101}锛�

mysql> SELECT *

    -> FROM t1

    ->      LEFT JOIN

    ->      (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)

    ->      ON t1.a=t2.a;

+------+------+------+------+

| a    | a    | b    | b    |

+------+------+------+------+

|    1 |    1 |  101 |  101 |

|    2 | NULL | NULL | NULL |

+------+------+------+------+

mysql> SELECT *

    -> FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)

    ->      LEFT JOIN t3

    ->      ON t2.b=t3.b OR t2.b IS NULL;

+------+------+------+------+

| a    | a    | b    | b    |

+------+------+------+------+

|    1 |    1 |  101 |  101 |

|    2 | NULL | NULL |  101 |

+------+------+------+------+

鍦ㄤ笅闈㈢殑渚嬪瓙涓�锛屽�栭潰鐨勮仈鎺ユ搷浣滅粨鍚堝唴閮ㄨ仈鎺ユ搷浣滀娇鐢�锛�

t1 LEFT JOIN (t2锛�t3) ON t1.a=t2.a

璇ヨ〃杈惧紡涓嶈兘杞�鎹�涓轰笅闈㈢殑琛ㄨ揪寮忥細

t1 LEFT JOIN t2 ON t1.a=t2.a锛�t3.

瀵逛簬缁欏畾鐨勮〃鐘舵�侊紝绗�1涓�琛ㄨ揪寮忚繑鍥炶��{1,1,101,101}銆�{2,NULL,NULL,NULL}锛岀��2涓�琛ㄨ揪寮忚繑鍥炶��{1,1,101,101}銆�{2,NULL,NULL,101}锛�

mysql> SELECT *

    -> FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;

+------+------+------+------+

| a    | a    | b    | b    |

+------+------+------+------+

|    1 |    1 |  101 |  101 |

|    2 | NULL | NULL | NULL |

+------+------+------+------+

mysql> SELECT *

    -> FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;

+------+------+------+------+

| a    | a    | b    | b    |

+------+------+------+------+

|    1 |    1 |  101 |  101 |

|    2 | NULL | NULL |  101 |

+------+------+------+------+

鍥犳�わ紝濡傛灉鎴戜滑蹇界暐鑱旀帴琛ㄨ揪寮忎腑鐨勬嫭鍙疯繛鍚屽�栭潰鐨勮仈鎺ユ搷浣滅�︼紝鎴戜滑浼氭敼鍙樺師琛ㄨ揪寮忕殑缁撴灉銆�

鏇寸‘鍒囧湴璇达紝鎴戜滑涓嶈兘蹇借�嗗乏澶栬仈鎺ユ搷浣滅殑鍙虫搷浣滄暟鍜屽彸鑱旀帴鎿嶄綔鐨勫乏鎿嶄綔鏁颁腑鐨勬嫭鍙枫�傛崲鍙ヨ瘽璇达紝鎴戜滑涓嶈兘蹇借�嗗�栬仈鎺ユ搷浣滀腑鐨勫唴琛ㄨ揪寮忎腑鐨勬嫭鍙枫�傚彲浠ュ拷瑙嗗叾瀹冩搷浣滄暟涓�鐨勬嫭鍙�(澶栭儴琛ㄧ殑鎿嶄綔鏁�)銆�

瀵逛簬浠讳綍琛�t1銆�t2銆�t3鍜屽睘鎬�t2.b鍜�t3.b鐨勪换浣曟潯浠�P锛屼笅闈㈢殑琛ㄨ揪寮忥細

(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)

绛変环浜庤〃杈惧紡

t1锛�t2 LEFT JOIN t3 ON P(t2.b,t3.b)

濡傛灉鑱旀帴琛ㄨ揪寮�(join_table)涓�鐨勮仈鎺ユ搷浣滅殑鎵ц�岄『搴忎笉鏄�浠庡乏鍒板彸锛屾垜浠�鍒欏簲璁ㄨ�哄祵濂楃殑鑱旀帴銆傝繖鏍凤紝涓嬮潰鐨勬煡璇�锛�

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a

  WHERE t1.a > 1

SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a

  WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1

鑱旀帴琛�锛�

t2 LEFT JOIN t3 ON t2.b=t3.b

t2, t3

璁や负鏄�宓屽�楃殑銆傜��1涓�鏌ヨ�㈢粨鍚堝乏鑱旀帴鎿嶄綔鍒欏舰鎴愬祵濂楃殑鑱旀帴锛岃�屽湪绗�浜屼釜鏌ヨ��涓�缁撳悎鍐呰仈鎺ユ搷浣滃舰鎴愬祵濂楄仈鎺ャ��

鍦ㄧ��1涓�鏌ヨ��涓�锛屾嫭鍙峰彲浠ュ拷鐣ワ細鑱旀帴琛ㄨ揪寮忕殑璇�娉曠粨鏋勪笌鑱旀帴鎿嶄綔鐨勬墽琛岄『搴忕浉鍚屻�備絾瀵逛簬绗�2涓�鏌ヨ��锛屾嫭鍙蜂笉鑳界渷鐣ワ紝灏界�″�傛灉娌℃湁鎷�鍙凤紝杩欓噷鐨勮仈鎺ヨ〃杈惧紡瑙ｉ噴涓嶆竻妤氥��(鍦ㄥ�栭儴鎵╁睍璇�娉曚腑锛岄渶瑕佺��2涓�鏌ヨ�㈢殑(t2锛�t3)鐨勬嫭鍙凤紝灏界�′粠鐞嗚�轰笂瀵规煡璇㈠垎鏋愭椂涓嶉渶瑕佹嫭鍙凤細杩欎簺鏌ヨ�㈢殑璇�娉曠粨鏋勫皢浠嶇劧涓嶆竻妤氾紝鍥犱负LEFT JOIN鍜�ON灏嗗厖褰撹〃杈惧紡(t2,t3)鐨勫乏銆佸彸鐣屽畾绗︾殑瑙掕壊)銆�

鍓嶉潰鐨勪緥瀛愯�存槑浜嗚繖浜涚偣锛�

路         瀵逛簬鍙�鍖呭惈鍐呰仈鎺�(鑰岄潪澶栬仈鎺�)鐨勮仈鎺ヨ〃杈惧紡锛屽彲浠ュ垹闄ゆ嫭鍙枫�備綘鍙�浠ョЩ闄ゆ嫭鍙峰苟浠庡乏鍒板彸璇勪及(鎴栧疄闄呬笂锛屼綘鍙�浠ユ寜浠讳綍椤哄簭璇勪及琛�)銆�

路         鎬荤殑鏉ヨ�达紝瀵瑰�栬仈鎺ュ嵈涓嶆槸杩欐牱銆傚幓闄ゆ嫭鍙峰彲鑳戒細鏇存敼缁撴灉銆�

路         鎬荤殑鏉ヨ�达紝瀵瑰�栬仈鎺ュ拰鍐呰仈鎺ョ殑缁撳悎锛屼篃涓嶆槸杩欐牱銆傚幓闄ゆ嫭鍙峰彲鑳戒細鏇存敼缁撴灉銆�

鍚�宓屽�楀�栬仈鎺ョ殑鏌ヨ�㈡寜鍚�鍐呰仈鎺ョ殑鏌ヨ�㈢殑鐩稿悓鐨勭�￠亾鏂瑰紡鎵ц�屻�傛洿纭�鍒囧湴璇达紝鍒╃敤浜嗗祵濂楃幆鑱旀帴绠楁硶銆傝�╂垜浠�鍥炲繂宓屽�楃幆鑱旀帴鎵ц�屾煡璇㈡椂閲囩敤浠�涔堢畻娉曘��

鍋囧畾鎴戜滑鏈変竴涓�濡備笅褰㈠紡鐨勮〃T1銆�T2銆�T3鐨勮仈鎺ユ煡璇�锛�

SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)

                 INNER JOIN T3 ON P2(T2,T3)

  WHERE P(T1,T2,T3).

杩欓噷锛�P1(T1,T2)鍜�P2(T3,T3)鏄�涓�浜涜仈鎺ユ潯浠�(琛ㄨ揪寮�)锛屽叾涓�P(t1,t2,t3)鏄�琛�T1銆�T2銆�T3鐨勫垪鐨勪竴涓�鏉′欢銆�

宓屽�楃幆鑱旀帴绠楁硶灏嗘寜涓嬮潰鐨勬柟寮忔墽琛岃�ユ煡璇�锛�

FOR each row t1 in T1 {

  FOR each row t2 in T2 such that P1(t1,t2) {

    FOR each row t3 in T3 such that P2(t2,t3) {

      IF P(t1,t2,t3) {

         t:=t1||t2||t3; OUTPUT t;

      }

    }

  }

}

绗﹀彿t1||t2||t3琛ㄧず鈥�杩炴帴琛�t1銆�t2鍜�t3鐨勫垪缁勬垚鐨勮��鈥濄�傚湪涓嬮潰鐨勪竴浜涗緥瀛愪腑锛屽嚭鐜拌�屽悕鐨�NULL琛ㄧずNULL鐢ㄤ簬琛岀殑姣忎釜鍒椼�備緥濡傦紝t1||t2||NULL琛ㄧず鈥�杩炴帴琛�t1鍜�t2鐨勫垪浠ュ強t3鐨勬瘡涓�鍒楃殑NULL缁勬垚鐨勮��鈥濄��

鐜板湪璁╂垜浠�鑰冭檻甯﹀祵濂楃殑澶栬仈鎺ョ殑鏌ヨ��锛�

SELECT * FROM T1 LEFT JOIN

              (T2 LEFT JOIN T3 ON P2(T2,T3))

              ON P1(T1,T2)

  WHERE P(T1,T2,T3)銆�

瀵逛簬璇ユ煡璇㈡垜浠�淇�鏀瑰祵濂楃幆妯″紡鍙�浠ュ緱鍒帮細

FOR each row t1 in T1 {

  BOOL f1:=FALSE;

  FOR each row t2 in T2 such that P1(t1,t2) {

    BOOL f2:=FALSE;

    FOR each row t3 in T3 such that P2(t2,t3) {

      IF P(t1,t2,t3) {

        t:=t1||t2||t3; OUTPUT t;

      }

      f2=TRUE;

      f1=TRUE;

    }

    IF (!f2) {

      IF P(t1,t2,NULL) {

        t:=t1||t2||NULL; OUTPUT t;

      }

      f1=TRUE;

    }

  }

  IF (!f1) {

    IF P(t1,NULL,NULL) {

      t:=t1||NULL||NULL; OUTPUT t;

    }

  }

}

鎬荤殑鏉ヨ�达紝瀵逛簬澶栬仈鎺ユ搷浣滀腑鐨勭��涓�涓�鍐呰〃鐨勫祵濂楃幆锛屽紩鍏ヤ簡涓�涓�鏍囧織锛屽湪鐜�涔嬪墠鍏抽棴骞朵笖鍦ㄧ幆涔嬪悗鎵撳紑銆傚�傛灉瀵逛簬澶栭儴琛ㄧ殑褰撳墠琛岋紝濡傛灉鍖归厤琛ㄧず鍐呮搷浣滄暟鐨勮〃锛屽垯鏍囧織鎵撳紑銆傚�傛灉鍦ㄥ惊鐜�缁撳熬澶勬爣蹇椾粛鐒跺叧闂�锛屽垯瀵逛簬澶栭儴琛ㄧ殑褰撳墠琛岋紝娌℃湁鍙戠幇鍖归厤銆傚湪杩欑�嶆儏鍐典笅锛屽�逛簬鍐呰〃鐨勫垪锛屽簲浣跨敤NULL鍊艰ˉ鍏呰�屻�傜粨鏋滆�岃��浼犻�掑埌杈撳嚭杩涜�屾渶缁堟��鏌ユ垨浼犻�掑埌涓嬩竴涓�宓屽�楃幆锛屼絾鍙�鑳藉湪琛屾弧瓒虫墍鏈夊祵鍏ュ紡澶栬仈鎺ョ殑鑱旀帴鏉′欢鏃躲��

鍦ㄦ垜浠�鐨勪緥瀛愪腑锛屽祵鍏ヤ簡涓嬮潰琛ㄨ揪寮忚〃绀虹殑澶栬仈鎺ヨ〃锛�

(T2 LEFT JOIN T3 ON P2(T2,T3))

璇锋敞鎰忓�逛簬鏈夊唴鑱旀帴鐨勬煡璇�锛屼紭鍖栧櫒鍙�浠ラ�夋嫨涓嶅悓鐨勫祵濂楃幆椤哄簭锛屼緥濡傦細

FOR each row t3 in T3 {

  FOR each row t2 in T2 such that P2(t2,t3) {

    FOR each row t1 in T1 such that P1(t1,t2) {

      IF P(t1,t2,t3) {

         t:=t1||t2||t3; OUTPUT t;

      }

    }

  }

}

瀵逛簬鏈夊�栬仈鎺ョ殑鏌ヨ��锛屼紭鍖栧櫒鍙�浠ュ彧閫夋嫨杩欐牱鐨勯『搴忥細澶栬〃鐨勭幆浼樺厛浜庡唴琛ㄧ殑鐜�銆傝繖鏍凤紝瀵逛簬鏈夊�栬仈鎺ョ殑鏌ヨ��锛屽彧鍙�鑳芥湁涓�绉嶅祵濂楅『搴忋�傚湪涓嬮潰鐨勬煡璇�涓�锛屼紭鍖栧櫒灏嗚瘎浼颁袱涓�涓嶅悓鐨勫祵濂楋細

SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)

  WHERE P(T1,T2,T3)

宓屽�椾负锛�

FOR each row t1 in T1 {

  BOOL f1:=FALSE;

  FOR each row t2 in T2 such that P1(t1,t2) {

    FOR each row t3 in T3 such that P2(t1,t3) {

      IF P(t1,t2,t3) {

        t:=t1||t2||t3; OUTPUT t;

      }

      f1:=TRUE

    }

  }

  IF (!f1) {

    IF P(t1,NULL,NULL) {

      t:=t1||NULL||NULL; OUTPUT t;

    }

  }

}

鍜�

FOR each row t1 in T1 {

  BOOL f1:=FALSE;

  FOR each row t3 in T3 such that P2(t1,t3) {

    FOR each row t2 in T2 such that P1(t1,t2) {

      IF P(t1,t2,t3) {

        t:=t1||t2||t3; OUTPUT t;

      }

      f1:=TRUE

    }

  }

  IF (!f1) {

    IF P(t1,NULL,NULL) {

      t:=t1||NULL||NULL; OUTPUT t;

    }

  }

}

鍦ㄤ袱涓�宓屽�椾腑锛屽繀椤诲湪澶栫幆涓�澶勭悊T1锛屽洜涓哄畠鐢ㄤ簬澶栬仈鎺ヤ腑銆�T2鍜�T3鐢ㄤ簬鍐呰仈鎺ヤ腑锛屽洜姝よ仈鎺ュ繀椤诲湪鍐呯幆涓�澶勭悊銆備絾鏄�锛屽洜涓鸿�ヨ仈鎺ユ槸涓�涓�鍐呰仈鎺ワ紝T2鍜�T3鍙�浠ヤ互浠讳綍椤哄簭澶勭悊銆�

褰撹�ㄨ�哄唴鑱旀帴宓屽�楃幆鐨勭畻娉曟椂锛屾垜浠�蹇界暐浜嗛儴鍒嗚�︽儏锛屽彲鑳藉�规煡璇㈡墽琛岀殑鎬ц兘鐨勫奖鍝嶄細寰堝ぇ銆傛垜浠�娌℃湁鎻愬強鎵�璋撶殑鈥滀笅鎺ㄢ�濇潯浠躲�傚亣瀹氬彲浠ョ敤杩炴帴鍏�寮忚〃绀烘垜浠�鐨�WHERE鏉′欢P(T1,T2,T3)锛�

P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3)銆�

鍦ㄨ繖绉嶆儏鍐典笅锛�MySQL瀹為檯浣跨敤浜嗕笅闈㈢殑宓屽�楃幆鏂规�堟潵鎵ц�屽甫鍐呰仈鎺ュ緱鍒版煡璇�锛�

FOR each row t1 in T1 such that C1(t1) {

  FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2)  {

    FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) {

      IF P(t1,t2,t3) {

         t:=t1||t2||t3; OUTPUT t;

      }

    }

  }

}

浣犱細鐪嬭�佹瘡涓�杩炴帴 C1(T1)锛�C2(T2)锛�C3(T3)琚�浠庢渶鍐呴儴鐨勭幆鍐呮帹鍑哄埌鍙�浠ュ�瑰畠杩涜�岃瘎浼扮殑鏈�澶栫殑鐜�涓�銆傚�傛灉C1(T1)鏄�涓�涓�闄愬埗鎬у緢寮虹殑鏉′欢锛屼笅鎺ㄦ潯浠跺彲浠ュぇ澶ч檷浣庝粠琛�T1浼犻�掑埌鍐呯幆鐨勮�屾暟銆傜粨鏋滄槸鏌ヨ�㈠ぇ澶у姞閫熴��

瀵逛簬鏈夊�栬仈鎺ョ殑鏌ヨ��锛屽彧鏈夋煡鍑哄�栬〃鐨勫綋鍓嶇殑琛屽彲浠ュ尮閰嶅唴琛ㄥ悗锛屾墠鍙�浠ユ��鏌�WHERE鏉′欢銆傝繖鏍凤紝瀵瑰唴宓屽�楃幆涓嬫帹鐨勬潯浠朵笉鑳界洿鎺ョ敤浜庡甫澶栬仈鎺ョ殑鏌ヨ��銆傝繖閲屾垜浠�蹇呴』寮曞叆鏈夋潯浠朵笅鎺ㄥ墠鎻愶紝鐢遍亣鍒板尮閰嶅悗鎵撳紑鐨勬爣蹇椾繚鎶ゃ��

瀵逛簬甯︿笅闈㈢殑澶栬仈鎺ョ殑渚嬪瓙

P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)

浣跨敤鍙椾繚鎶ょ殑涓嬫帹鏉′欢鐨勫祵濂楃幆鏂规�堢湅璧锋潵搴斾负锛�

FOR each row t1 in T1 such that C1(t1) {

  BOOL f1:=FALSE;

  FOR each row t2 in T2

      such that P1(t1,t2) AND (f1?C2(t2):TRUE) {

    BOOL f2:=FALSE;

    FOR each row t3 in T3

        such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) {

      IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {

        t:=t1||t2||t3; OUTPUT t;

      }

      f2=TRUE;

      f1=TRUE;

    }

    IF (!f2) {

      IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) {

        t:=t1||t2||NULL; OUTPUT t;

      }

      f1=TRUE;

    }

  }

  IF (!f1 && P(t1,NULL,NULL)) {

      t:=t1||NULL||NULL; OUTPUT t;

  }

}

鎬荤殑鏉ヨ�达紝鍙�浠ヤ粠鑱旀帴鏉′欢锛堜緥濡�P1(T1,T2)鍜�P(T2,T3)锛夋彁鍙栦笅鎺ㄥ墠鎻愩�傚湪杩欑�嶆儏鍐典笅锛屼笅鎺ㄥ墠鎻愪篃鍙椾竴涓�鏍囧織淇濇姢锛岄槻姝㈡��鏌ョ敱鐩稿簲澶栬仈鎺ユ搷浣滄墍浜х敓鐨�NULL-琛ュ厖鐨勮�岀殑鏂�瑷�銆�

璇锋敞鎰忓�傛灉浠庡垽鏂�寮忕殑WHERE鏉′欢鎺ㄥ�煎嚭锛屾牴鎹�浠庝竴涓�鍐呰〃鍒扮浉鍚屽祵濂楄仈鎺ョ殑鍙︿竴涓�琛ㄧ殑鍏抽敭瀛楄繘琛岀殑璁块棶琚�绂佹��銆�(鍦ㄨ繖绉嶆儏鍐典笅锛屾垜浠�鍙�浠ヤ娇鐢ㄦ湁鏉′欢鍏抽敭瀛楄�块棶锛屼絾鏄�璇ユ妧鏈�杩樻湭鐢ㄤ簬MySQL 5.1涓�锛�銆�

(T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) =

(T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)

T1 LEFT JOIN T2 ON T1.A=T2.A

T2.B IS NOT NULL,

T2.B > 3,

T2.C <= T1.C,

T2.B < 2 OR T2.C > 1

T2.B IS NULL,

T1.B < 3 OR T2.B IS NOT NULL,

T1.B < 3 OR T2.B > 3

SELECT * FROM T1 LEFT JOIN

              (T2 LEFT JOIN T3 ON T3.B=T2.B)

              ON T2.A=T1.A AND T3.C=T1.C

  WHERE T3.D > 0 OR T1.D > 0

WHERE鏉′欢瀵逛簬宓屽叆寮忓�栬仈鎺ヤ笉涓烘嫆缁�null鐨勶紝浣嗗祵鍏ュ紡澶栬仈鎺�T2.A=T1.A AND T3.C=T1.C鐨勮仈鎺ユ潯浠朵负鎷掔粷null鐨�銆�鍥犳�よ�ユ煡璇㈠彲浠ヨ浆鎹�涓�锛�

鍦ㄦ煇浜涙儏鍐典腑锛�MySQL鍙�浠ヤ娇鐢ㄤ竴涓�绱㈠紩鏉ユ弧瓒�ORDER BY瀛愬彞锛岃�屼笉闇�瑕侀�濆�栫殑鎺掑簭銆�

鍗充娇ORDER BY涓嶇‘鍒囧尮閰嶇储寮曪紝鍙�瑕�WHERE瀛愬彞涓�鐨勬墍鏈夋湭浣跨敤鐨勭储寮曢儴鍒嗗拰鎵�鏈夐�濆�栫殑ORDER BY 鍒椾负甯告暟锛屽氨鍙�浠ヤ娇鐢ㄧ储寮曘�備笅闈㈢殑鏌ヨ��浣跨敤绱㈠紩鏉ヨВ鍐�ORDER BY閮ㄥ垎锛�

SELECT * FROM t1

    ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1

    WHERE key_part1=constant

    ORDER BY key_part2;

SELECT * FROM t1

    ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1

    WHERE key_part1=1

    ORDER BY key_part1 DESC, key_part2 DESC;

鍦ㄦ煇浜涙儏鍐典笅锛�MySQL涓嶈兘浣跨敤绱㈠紩鏉ヨВ鍐�ORDER BY锛屽敖绠″畠浠嶇劧浣跨敤绱㈠紩鏉ユ壘鍒板尮閰�WHERE瀛愬彞鐨勮�屻�傝繖浜涙儏鍐靛寘鎷�锛�

路         瀵逛笉鍚岀殑鍏抽敭瀛椾娇鐢�ORDER BY锛�

路                SELECT * FROM t1 ORDER BY key1, key2锛�

路         瀵瑰叧閿�瀛楃殑闈炶繛缁�鍏冪礌浣跨敤ORDER BY锛�

路                SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2锛�

路         娣峰悎ASC鍜�DESC锛�

路                SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC锛�

路         鐢ㄤ簬鏌ヨ�㈣�岀殑鍏抽敭瀛椾笌ORDER BY涓�鎵�浣跨敤鐨勪笉鐩稿悓锛�

路                SELECT * FROM t1 WHERE key2=constant ORDER BY key1锛�

路         浣犳�ｈ仈鎺ヨ�稿�氳〃锛屽苟涓�ORDER BY涓�鐨勫垪骞朵笉鏄�鍏ㄩ儴鏉ヨ嚜绗�1涓�鐢ㄤ簬鎼滅储琛岀殑闈炲父閲忚〃銆�(杩欐槸EXPLAIN杈撳嚭涓�鐨勬病鏈�const鑱旀帴绫诲瀷鐨勭��1涓�琛�锛�銆�

路         鏈変笉鍚岀殑ORDER BY鍜�GROUP BY琛ㄨ揪寮忋��

路         浣跨敤鐨勮〃绱㈠紩鐨勭被鍨嬩笉鑳芥寜椤哄簭淇濆瓨琛屻�備緥濡傦紝瀵逛簬HEAP琛ㄧ殑HASH绱㈠紩鎯呭喌鍗冲�傛�ゃ��

閫氳繃EXPLAIN SELECT ...ORDER BY锛屽彲浠ユ��鏌�MySQL鏄�鍚﹀彲浠ヤ娇鐢ㄧ储寮曟潵瑙ｅ喅鏌ヨ��銆傚�傛灉Extra鍒楀唴鏈�Using filesort锛屽垯涓嶈兘瑙ｅ喅鏌ヨ��銆傚弬瑙�7.2.1鑺傦紝鈥淓XPLAIN璇�娉曪紙鑾峰彇鍏充簬SELECT鐨勪俊鎭�锛�鈥�銆�

鏂囦欢鎺掑簭浼樺寲涓嶄粎鐢ㄤ簬璁板綍鎺掑簭鍏抽敭瀛楀拰琛岀殑浣嶇疆锛屽苟涓旇繕璁板綍鏌ヨ�㈤渶瑕佺殑鍒椼�傝繖鏍峰彲浠ラ伩鍏嶄袱娆¤�诲彇琛屻�傛枃浠舵帓搴忕畻娉曠殑宸ヤ綔璞¤繖鏍凤細

1.    璇昏�屽尮閰�WHERE瀛愬彞鐨勮�岋紝濡傚墠闈㈡墍绀恒��

2.    瀵逛簬姣忎釜琛岋紝璁板綍鏋勬垚鎺掑簭鍏抽敭瀛楀拰琛屼綅缃�鐨勪竴绯诲垪鍊硷紝骞朵笖璁板綍鏌ヨ�㈤渶瑕佺殑鍒椼��

3.    鏍规嵁鎺掑簭鍏抽敭瀛楁帓搴忓厓缁�

4.    鎸夋帓搴忕殑椤哄簭妫�绱㈣�岋紝浣嗙洿鎺ヤ粠鎺掑簭鐨勫厓缁勮�诲彇闇�瑕佺殑鍒楋紝鑰屼笉鏄�鍐嶄竴娆¤�块棶琛ㄣ��

璇ョ畻娉曟瘮浠ュ墠鐗堟湰鐨�Mysql鏈夊緢澶х殑鏀硅繘銆�

涓轰簡閬垮厤閫熷害鍙樻參锛岃�ヤ紭鍖栧彧鐢ㄤ簬鎺掑簭鍏冪粍涓�鐨�extra鍒楃殑鎬诲ぇ灏忎笉瓒呰繃max_length_for_sort_data绯荤粺鍙橀噺鍊肩殑鏃跺�欍��(灏嗚�ュ彉閲忚�剧疆寰楀お楂樼殑鐨勮抗璞℃槸灏嗙湅鍒扮‖鐩樻椿鍔ㄥお棰戠箒鑰�CPU娲诲姩杈冧綆锛�銆�

濡傛灉鎯宠�佸�炲姞ORDER BY鐨勯�熷害锛岄�栧厛鐪嬫槸鍚﹀彲浠ヨ��MySQL浣跨敤绱㈠紩鑰屼笉鏄�棰濆�栫殑鎺掑簭闃舵�点�傚�傛灉涓嶈兘锛屽彲浠ュ皾璇曚笅闈㈢殑绛栫暐锛�

路         澧炲姞sort_buffer_size鍙橀噺鐨勫ぇ灏忋��

路         澧炲姞read_rnd_buffer_size鍙橀噺鐨勫ぇ灏忋��

路         鏇存敼tmpdir鎸囧悜鍏锋湁澶ч噺绌洪棽绌洪棿鐨勪笓鐢ㄦ枃浠剁郴缁熴�傝�ラ�夐」鎺ュ彈鍑犱釜浣跨敤round-robin(寰�鐜�)妯″紡鐨勮矾寰勩�傚湪Unix涓�璺�寰勫簲鐢ㄥ啋鍙�(鈥�:鈥�)鍖洪棿寮�锛屽湪Windows銆�NetWare鍜�OS/2涓�鐢ㄥ垎鍙�(鈥�锛�鈥�)銆傚彲浠ヤ娇鐢ㄨ�ョ壒鎬у皢璐熻浇鍧囧垎鍒板嚑涓�鐩�褰曚腑銆�娉ㄩ噴锛�璺�寰勫簲涓轰綅浜庝笉鍚�鐗╃悊纭�鐩樹笂鐨勬枃浠剁郴缁熺殑鐩�褰曪紝鑰屼笉鏄�鍚屼竴纭�鐩樼殑涓嶅悓鐨勫垎鍖恒��

榛樿�ゆ儏鍐典笅锛�MySQL鎺掑簭鎵�鏈�GROUP BY col1锛�col2锛�...鏌ヨ�㈢殑鏂规硶濡傚悓鍦ㄦ煡璇�涓�鎸囧畾ORDER BY col1锛�col2锛�...銆傚�傛灉鏄惧紡鍖呮嫭涓�涓�鍖呭惈鐩稿悓鐨勫垪鐨�ORDER BY瀛愬彞锛�MySQL鍙�浠ユ��涓嶅噺閫熷湴瀵瑰畠杩涜�屼紭鍖栵紝灏界�′粛鐒惰繘琛屾帓搴忋�傚�傛灉鏌ヨ�㈠寘鎷�GROUP BY浣嗕綘鎯宠�侀伩鍏嶆帓搴忕粨鏋滅殑娑堣�楋紝浣犲彲浠ユ寚瀹�ORDER BY NULL绂佹�㈡帓搴忋�備緥濡傦細

INSERT INTO foo

SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

婊¤冻GROUP BY瀛愬彞鐨勬渶涓�鑸�鐨勬柟娉曟槸鎵�鎻忔暣涓�琛ㄥ苟鍒涘缓涓�涓�鏂扮殑涓存椂琛�锛岃〃涓�姣忎釜缁勭殑鎵�鏈夎�屽簲涓鸿繛缁�鐨勶紝鐒跺悗浣跨敤璇ヤ复鏃惰〃鏉ユ壘鍒扮粍骞跺簲鐢ㄧ疮绉�鍑芥暟(濡傛灉鏈�)銆傚湪鏌愪簺鎯呭喌涓�锛�MySQL鑳藉�熷仛寰楁洿濂斤紝閫氳繃绱㈠紩璁块棶鑰屼笉鐢ㄥ垱寤轰复鏃惰〃銆�

涓�GROUP BY浣跨敤绱㈠紩鐨勬渶閲嶈�佺殑鍓嶆彁鏉′欢鏄� 鎵�鏈�GROUP BY鍒楀紩鐢ㄥ悓涓�绱㈠紩鐨勫睘鎬э紝骞朵笖绱㈠紩鎸夐『搴忎繚瀛樺叾鍏抽敭瀛�(渚嬪�傦紝杩欐槸B-鏍戠储寮曪紝鑰屼笉鏄�HASH绱㈠紩)銆傛槸鍚︾敤绱㈠紩璁块棶鏉ヤ唬鏇夸复鏃惰〃鐨勪娇鐢ㄨ繕鍙栧喅浜庡湪鏌ヨ��涓�浣跨敤浜嗗摢閮ㄥ垎绱㈠紩銆佷负璇ラ儴鍒嗘寚瀹氱殑鏉′欢锛屼互鍙婇�夋嫨鐨勭疮绉�鍑芥暟銆�

鏈変袱绉嶆柟娉曢�氳繃绱㈠紩璁块棶鎵ц��GROUP BY鏌ヨ��锛屽�備笅闈㈢殑绔犺妭鎵�鎻忚堪銆傚湪绗�1涓�鏂规硶涓�锛岀粍鍚堟搷浣滅粨鍚堟墍鏈夎寖鍥村垽鏂�寮忎娇鐢�(濡傛灉鏈�)銆傜��2涓�鏂规硶棣栧厛鎵ц�岃寖鍥存壂鎻忥紝鐒跺悗缁勫悎缁撴灉鍏冪粍銆�

     SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

     SELECT c1,c2 FROM t1 GROUP BY c2, c3;

     SELECT c1,c3 FROM t1 GROUP BY c1, c2锛�

     SELECT c1锛�c2锛�c3 FROM t1 WHERE c2 = 'a' GROUP BY c1锛�c3;

     SELECT c1锛�c2锛�c3 FROM t1 WHERE c1 = 'a' GROUP BY c2锛�c3;

鍦ㄤ竴浜涙儏鍐典腑锛屽綋浣犱娇鐢�LIMIT row_count鑰屼笉浣跨敤HAVING鏃讹紝MySQL灏嗕互涓嶅悓鏂瑰紡澶勭悊鏌ヨ��銆�

路         濡傛灉浣犵敤LIMIT鍙�閫夋嫨涓�浜涜�岋紝褰�MySQL閫夋嫨鍋氬畬鏁寸殑琛ㄦ壂鎻忔椂锛屽畠灏嗗湪涓�浜涙儏鍐典笅浣跨敤绱㈠紩銆�

路         濡傛灉浣犱娇鐢�LIMIT row_count涓�ORDER BY锛�MySQL涓�鏃︽壘鍒颁簡鎺掑簭缁撴灉鐨勭��涓�涓�row_count琛岋紝灏嗙粨鏉熸帓搴忚�屼笉鏄�鎺掑簭鏁翠釜琛ㄣ�傚�傛灉浣跨敤绱㈠紩锛屽皢寰堝揩銆傚�傛灉蹇呴』杩涜�屾枃浠舵帓搴忥紙filesort锛夛紝蹇呴』閫夋嫨鎵�鏈夊尮閰嶆煡璇㈡病鏈�LIMIT瀛愬彞鐨勮�岋紝骞朵笖鍦ㄧ‘瀹氬凡缁忔壘鍒扮��1涓�row_count琛屽墠锛屽繀椤诲�瑰畠浠�鐨勫ぇ閮ㄥ垎杩涜�屾帓搴忋�傚湪浠讳綍涓�绉嶆儏鍐典笅锛屼竴鏃︽壘鍒颁簡琛岋紝鍒欎笉闇�瑕佸啀鎺掑簭缁撴灉鐨勫叾瀹冮儴鍒嗭紝骞朵笖MySQL涓嶅啀杩涜�屾帓搴忋��

路         褰撶粨鍚�LIMIT row_count鍜�DISTINCT鏃讹紝MySQL涓�鏃︽壘鍒�row_count涓�鍞�涓�鐨勮�岋紝瀹冨皢鍋滄��銆�

路         鍦ㄤ竴浜涙儏鍐典笅锛�GROUP BY鑳介�氳繃椤哄簭璇诲彇閿�(鎴栧湪閿�涓婂仛鎺掑簭)鏉ヨВ鍐筹紝鐒跺悗璁＄畻鎽樿�佺洿鍒板叧閿�瀛楃殑鍊兼敼鍙樸�傚湪杩欑�嶆儏鍐典笅锛�LIMIT row_count灏嗕笉璁＄畻浠讳綍涓嶅繀瑕佺殑GROUP BY鍊笺��

路         鍙�瑕�MySQL宸茬粡鍙戦�佷簡闇�瑕佺殑琛屾暟鍒板�㈡埛锛屽畠灏嗘斁寮冩煡璇�锛岄櫎闈炰綘姝ｄ娇鐢�SQL_CALC_FOUND_ROWS銆�

路         LIMIT 0灏嗘�绘槸蹇�閫熻繑鍥炰竴涓�绌洪泦鍚堛�傝繖瀵规��鏌ユ煡璇㈢殑鏈夋晥鎬ф槸鏈夌敤鐨勩�傚綋浣跨敤MySQL API鏃讹紝瀹冧篃鍙�浠ョ敤鏉ュ緱鍒扮粨鏋滃垪鐨勫垪绫诲瀷銆�(璇ユ妧宸у湪MySQL Monitor涓�涓嶅伐浣滐紝鍙�鏄剧ずEmpty set锛涘簲浣跨敤SHOW COLUMNS鎴�DESCRIBE锛�銆�

路         褰撴湇鍔″櫒浣跨敤涓存椂琛ㄦ潵杩涜�屾煡璇㈡椂锛屼娇鐢�LIMIT row_count瀛愬彞鏉ヨ�＄畻闇�瑕佸�氬皯绌洪棿銆�

EXPLAIN鐨勮緭鍑烘樉绀轰簡褰�MySQL浣跨敤琛ㄦ壂鎻忔潵瑙ｅ喅鏌ヨ�㈡椂浣跨敤鐨勬墍鏈夌被鍨嬪垪銆傝繖閫氬父鍦ㄥ�備笅鏉′欢涓嬪彂鐢燂細

路         琛ㄥ緢灏忥紝鎵�鎻忚〃姣旀煡鎵惧叧閿�瀛楅�熷害蹇�銆傝繖瀵逛簬灏戜簬10琛屽苟涓旇�岃緝鐭�鐨勮〃姣旇緝鏅�閬嶃��

路         鍦�ON鎴�WHERE瀛愬彞涓�娌℃湁閫傜敤鐨勭储寮曞垪鐨勭害鏉熴��

路         姝ｇ敤甯搁噺鍊兼瘮杈冪储寮曞垪锛屽苟涓�MySQL宸茬粡璁＄畻鍒�(鍩轰簬绱㈠紩鏍�)甯告暟瑕嗙洊浜嗚〃鐨勫緢澶ч儴鍒嗗苟涓旇〃鎵�鎻忓皢浼氭瘮杈冨揩銆傚弬瑙�7.2.4鑺傦紝鈥淢ySQL鎬庢牱浼樺寲WHERE瀛愬彞鈥�銆�

路         浣犳�ｉ�氳繃鍙︿竴涓�鍒椾娇鐢ㄤ竴涓�浣庣殑闆嗙殑鍔跨殑鍏抽敭瀛�(璁稿�氳�屽尮閰嶅叧閿�瀛�)銆傚湪杩欑�嶆儏鍐典笅锛�MySQL鍋囪�鹃�氳繃浣跨敤鍏抽敭瀛楀畠鍙�鑳戒細杩涜�岃�稿�氬叧閿�瀛楁煡鎵撅紝琛ㄦ壂鎻忓皢浼氭洿蹇�銆�

瀵逛簬灏忚〃锛岃〃鎵�鎻忛�氬父鍚堥�傘�傚�逛簬澶ц〃锛屽皾璇曚笅闈㈢殑鎶�宸т互閬垮厤浼樺寲鍣ㄩ敊閫変簡琛ㄦ壂鎻忥細

路         浣跨敤ANALYZE TABLE tbl_name涓烘壂鎻忕殑琛ㄦ洿鏂板叧閿�瀛楀垎甯冦�傚弬瑙�13.5.2.1鑺傦紝鈥淎NALYZE TABLE璇�娉曗��銆�

路         瀵规壂鎻忕殑琛ㄤ娇鐢�FORCE INDEX鍛婄煡MySQL锛岀浉瀵逛簬浣跨敤缁欏畾鐨勭储寮曡〃鎵�鎻忓皢闈炲父鑰楁椂銆傚弬瑙�13.2.7鑺傦紝鈥淪ELECT璇�娉曗��銆�

路                SELECT * FROM t1, t2 FORCE INDEX (index_for_column)

路                    WHERE t1.col_name=t2.col_name锛�

路         鐢�--max-seeks-for-key=1000閫夐」鍚�鍔�mysqld鎴栦娇鐢�SET max_seeks_for_key=1000鍛婄煡浼樺寲鍣ㄥ亣璁惧叧閿�瀛楁壂鎻忎笉浼氳秴杩�1,000娆″叧閿�瀛楁悳绱�銆傚弬瑙�5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺鈥�銆�

鎻掑叆涓�涓�璁板綍闇�瑕佺殑鏃堕棿鐢变笅鍒楀洜绱犵粍鎴�锛屽叾涓�鐨勬暟瀛楄〃绀哄ぇ绾︽瘮渚嬶細

• 杩炴帴锛�(3)
• 鍙戦�佹煡璇㈢粰鏈嶅姟鍣�锛�(2)
• 鍒嗘瀽鏌ヨ��锛�(2)
• 鎻掑叆璁板綍锛氾紙1x璁板綍澶у皬锛�
• 鎻掑叆绱㈠紩锛氾紙1x绱㈠紩锛�
• 鍏抽棴锛�(1)

杩欎笉鑰冭檻鎵撳紑琛ㄧ殑鍒濆�嬪紑閿�锛屾瘡涓�骞跺彂杩愯�岀殑鏌ヨ�㈡墦寮�銆�

琛ㄧ殑澶у皬浠�logN (B鏍�)鐨勯�熷害鍑忔參绱㈠紩鐨勬彃鍏ャ��

鍔犲揩鎻掑叆鐨勪竴浜涙柟娉曪細

路         濡傛灉鍚屾椂浠庡悓涓�涓�瀹㈡埛绔�鎻掑叆寰堝�氳�岋紝浣跨敤鍚�澶氫釜VALUE鐨�INSERT璇�鍙ュ悓鏃舵彃鍏ュ嚑琛屻�傝繖姣斾娇鐢ㄥ崟琛�INSERT璇�鍙ュ揩(鍦ㄦ煇浜涙儏鍐典笅蹇�鍑犲��)銆傚�傛灉浣犳�ｅ悜涓�涓�闈炵┖琛ㄦ坊鍔犳暟鎹�锛屽彲浠ヨ皟鑺�bulk_insert_buffer_size鍙橀噺锛屼娇鏁版嵁鎻掑叆鏇村揩銆傚弬瑙�5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺鈥�銆�

路         濡傛灉浣犱粠涓嶅悓鐨勫�㈡埛绔�鎻掑叆寰堝�氳�岋紝鑳介�氳繃INSERT DELAYED璇�鍙ュ姞蹇�閫熷害銆傚弬瑙�13.2.4鑺傦紝鈥淚NSERT璇�娉曗��銆�

路         鐢�MyISAM锛屽�傛灉鍦ㄨ〃涓�娌℃湁鍒犻櫎鐨勮�岋紝鑳藉湪SELECT璇�鍙ユ�ｅ湪杩愯�岀殑鍚屾椂鎻掑叆琛屻��

路         褰撲粠涓�涓�鏂囨湰鏂囦欢瑁呰浇涓�涓�琛ㄦ椂锛屼娇鐢�LOAD DATA INFILE銆傝繖閫氬父姣斾娇鐢ㄥ緢澶�INSERT璇�鍙ュ揩20鍊嶃�傚弬瑙�13.2.5鑺傦紝鈥淟OAD DATA INFILE璇�娉曗��銆�

路         褰撹〃鏈夊緢澶氱储寮曟椂锛屾湁鍙�鑳借�佸�氬仛浜涘伐浣滀娇寰�LOAD DATA INFILE鏇村揩浜涖�備娇鐢ㄤ笅鍒楄繃绋嬶細

1. 鏈夐�夋嫨鍦扮敤CREATE TABLE鍒涘缓琛ㄣ��
2. 鎵ц��FLUSH TABLES璇�鍙ユ垨鍛戒护mysqladmin flush-tables銆�
3. 浣跨敤myisamchk --keys-used=0 -rq /path/to/db/tbl_name銆傝繖灏嗕粠琛ㄤ腑鍙栨秷鎵�鏈夌储寮曠殑浣跨敤銆�
4. 鐢�LOAD DATA INFILE鎶婃暟鎹�鎻掑叆鍒拌〃涓�锛屽洜涓轰笉鏇存柊浠讳綍绱㈠紩锛屽洜姝ゅ緢蹇�銆�
5. 濡傛灉鍙�鎯冲湪浠ュ悗璇诲彇琛�锛屼娇鐢�myisampack鍘嬬缉瀹冦�傚弬瑙�15.1.3.3鑺傦紝鈥滃帇缂╄〃鐗规�р��銆�
6. 鐢�myisamchk -r -q /path/to/db/tbl_name閲嶆柊鍒涘缓绱㈠紩銆傝繖灏嗗湪鍐欏叆纾佺洏鍓嶅湪鍐呭瓨涓�鍒涘缓绱㈠紩鏍戯紝骞朵笖瀹冩洿蹇�锛屽洜涓洪伩鍏嶄簡澶ч噺纾佺洏鎼滅储銆傜粨鏋滅储寮曟爲涔熻��瀹岀編鍦板钩琛°��
7. 鎵ц��FLUSH TABLES璇�鍙ユ垨mysqladmin flush-tables鍛戒护銆�

璇锋敞鎰忓�傛灉鎻掑叆涓�涓�绌�MyISAM琛�锛�LOAD DATA INFILE涔熷彲浠ユ墽琛屽墠闈㈢殑浼樺寲锛涗富瑕佷笉鍚屽�勬槸鍙�浠ヨ��myisamchk涓哄垱寤虹储寮曞垎閰嶆洿澶氱殑涓存椂鍐呭瓨锛屾瘮鎵ц��LOAD DATA INFILE璇�鍙ユ椂涓烘湇鍔″櫒閲嶆柊鍒涘缓绱㈠紩鍒嗛厤寰楄�佸�氥��

涔熷彲浠ヤ娇鐢�ALTER TABLE tbl_name DISABLE KEYS浠ｆ浛myisamchk --keys-used=0 -rq /path/to/db/tbl_name锛屼娇鐢�ALTER TABLE tbl_name ENABLE KEYS浠ｆ浛myisamchk -r -q /path/to/db/tbl_name銆備娇鐢ㄨ繖绉嶆柟寮忥紝杩樺彲浠ヨ烦杩�FLUSH TABLES銆�

路         閿佸畾琛ㄥ彲浠ュ姞閫熺敤澶氫釜璇�鍙ユ墽琛岀殑INSERT鎿嶄綔锛�

• LOCK TABLES a WRITE;
• INSERT INTO a VALUES (1,23),(2,34),(4,33);
• INSERT INTO a VALUES (8,26),(6,29);
• UNLOCK TABLES;

杩欐牱鎬ц兘浼氭彁楂橈紝鍥犱负绱㈠紩缂撳瓨鍖轰粎鍦ㄦ墍鏈�INSERT璇�鍙ュ畬鎴愬悗鍒锋柊鍒扮�佺洏涓婁竴娆°�備竴鑸�鏈夊�氬皯INSERT璇�鍙ュ嵆鏈夊�氬皯绱㈠紩缂撳瓨鍖哄埛鏂般�傚�傛灉鑳界敤涓�涓�璇�鍙ユ彃鍏ユ墍鏈夌殑琛岋紝灏变笉闇�瑕侀攣瀹氥��

瀵逛簬浜嬪姟琛�锛屽簲浣跨敤BEGIN鍜�COMMIT浠ｆ浛LOCK TABLES鏉ュ姞蹇�鎻掑叆銆�

閿佸畾涔熷皢闄嶄綆澶氳繛鎺ユ祴璇曠殑鏁翠綋鏃堕棿锛屽敖绠″洜涓哄畠浠�绛夊�欓攣瀹氭渶澶х瓑寰呮椂闂村皢涓婂崌銆備緥濡傦細

Connection 1 does 1000 inserts

Connections 2, 3, and 4 do 1 insert

Connection 5 does 1000 inserts

濡傛灉涓嶄娇鐢ㄩ攣瀹氾紝2銆�3鍜�4灏嗗湪1鍜�5鍓嶅畬鎴愩�傚�傛灉浣跨敤閿佸畾锛�2銆�3鍜�4灏嗗彲鑳戒笉鍦�1鎴�5鍓嶅畬鎴愶紝浣嗘槸鏁翠綋鏃堕棿搴旇�ュ揩澶х害40%銆�

INSERT銆�UPDATE鍜�DELETE鎿嶄綔鍦�MySQL涓�鏄�寰堝揩鐨勶紝閫氳繃涓哄湪涓�琛屼腑澶氫簬澶х害5娆¤繛缁�涓嶆柇鍦版彃鍏ユ垨鏇存柊鐨勬搷浣滃姞閿侊紝鍙�浠ヨ幏寰楁洿濂界殑鏁翠綋鎬ц兘銆傚�傛灉鍦ㄤ竴琛屼腑杩涜�屽�氭�℃彃鍏ワ紝鍙�浠ユ墽琛�LOCK TABLES锛岄殢鍚庣珛鍗虫墽琛�UNLOCK TABLES(澶х害姣�1000琛�)浠ュ厑璁稿叾瀹冪殑绾跨▼璁块棶琛ㄣ�傝繖涔熶細鑾峰緱濂界殑鎬ц兘銆�

INSERT瑁呰浇鏁版嵁姣�LOAD DATA INFILE瑕佹參寰楀�氾紝鍗充娇鏄�浣跨敤涓婅堪鐨勭瓥鐣ャ��

路         涓轰簡瀵�LOAD DATA INFILE鍜�INSERT鍦�MyISAM琛ㄥ緱鍒版洿蹇�鐨勯�熷害锛岄�氳繃澧炲姞key_buffer_size绯荤粺鍙橀噺鏉ユ墿澶� 閿�楂橀�熺紦鍐插尯銆傚弬瑙�7.5.2鑺傦紝鈥滆皟鑺傛湇鍔″櫒鍙傛暟鈥�銆�

鏇存柊鏌ヨ�㈢殑浼樺寲鍚�SELECT鏌ヨ��涓�鏍凤紝闇�瑕侀�濆�栫殑鍐欏紑閿�銆傚啓閫熷害渚濊禆浜庢洿鏂扮殑鏁版嵁澶у皬鍜屾洿鏂扮殑绱㈠紩鐨勬暟閲忋�傛病鏈夋洿鏀圭殑绱㈠紩涓嶈��鏇存柊銆�

浣挎洿鏀规洿蹇�鐨勫彟涓�涓�鏂规硶鏄�鎺ㄨ繜鏇存敼鐒跺悗鍦ㄤ竴琛屽唴杩涜�屽�氭�℃洿鏂般�傚�傛灉閿佸畾琛�锛屽悓鏃跺仛澶氫釜鏇存柊姣斾竴娆″仛涓�涓�蹇�寰楀�氥��

璇锋敞鎰忓�逛娇鐢ㄥ姩鎬佽�板綍鏍煎紡鐨�MyISAM琛�锛屾洿鏂颁竴涓�杈冮暱鎬婚暱鐨勮�板綍鍙�鑳戒細鍒囧垎璁板綍銆傚�傛灉缁忓父杩欐牱璇ワ紝鍋跺皵浣跨敤OPTIMIZE TABLE寰堥噸瑕併�傚弬瑙�13.5.2.5鑺傦紝鈥淥PTIMIZE TABLE璇�娉曗��銆�

鍒犻櫎涓�涓�璁板綍鐨勬椂闂翠笌绱㈠紩鏁伴噺纭�鍒囨垚姝ｆ瘮銆備负浜嗘洿蹇�閫熷湴鍒犻櫎璁板綍锛屽彲浠ュ�炲姞閿�楂橀�熺紦鍐茬殑澶у皬銆傚弬瑙�7.5.2鑺傦紝鈥滆皟鑺傛湇鍔″櫒鍙傛暟鈥�銆�

濡傛灉鎯宠�佸垹闄や竴涓�琛ㄧ殑鎵�鏈夎�岋紝浣跨敤TRUNCATE TABLE tbl_name 鑰屼笉瑕佺敤DELETE FROM tbl_name銆傚弬瑙�13.2.9鑺傦紝鈥淭RUNCATE璇�娉曗��銆�

璇ヨ妭鍒楀嚭浜嗘彁楂樻煡璇㈤�熷害鐨勫悇绉嶆妧宸э細

路         浣跨敤鎸佷箙鐨勮繛鎺ユ暟鎹�搴撲互閬垮厤杩炴帴寮�閿�銆傚�傛灉涓嶈兘浣跨敤鎸佷箙鐨勮繛鎺ュ苟涓斾綘姝ｅ惎鍔ㄨ�稿�氭柊鐨勪笌鏁版嵁搴撶殑杩炴帴锛屽彲鑳借�佹洿鏀�thread_cache_size鍙橀噺鐨勫�笺�傚弬瑙�7.5.2鑺傦紝鈥滆皟鑺傛湇鍔″櫒鍙傛暟鈥�銆�

路         鎬绘槸妫�鏌ユ墍鏈夋煡璇㈢‘瀹炰娇鐢ㄥ凡缁忓湪琛ㄤ腑鍒涘缓浜嗙殑绱㈠紩銆傚湪MySQL涓�锛屽彲浠ョ敤EXPLAIN鍛戒护鍋氬埌銆傚弬瑙�7.2.1鑺傦紝鈥淓XPLAIN璇�娉曪紙鑾峰彇鍏充簬SELECT鐨勪俊鎭�锛�鈥�銆�

路         灏濊瘯閬垮厤鍦ㄩ�戠箒鏇存柊鐨勮〃涓婃墽琛屽�嶆潅鐨�SELECT鏌ヨ��锛屼互閬垮厤涓庨攣瀹氳〃鏈夊叧鐨勭敱浜庤�汇�佸啓鍐茬獊鍙戠敓鐨勯棶棰樸��

路         瀵逛簬娌℃湁鍒犻櫎鐨勮�岀殑MyISAM琛�锛屽彲浠ュ湪鍙︿竴涓�鏌ヨ�㈡�ｄ粠琛ㄤ腑璇诲彇鐨勫悓鏃跺湪鏈�灏炬彃鍏ヨ�屻�傚�傛灉杩欏緢閲嶈�侊紝搴旇�冭檻鎸夌収閬垮厤鍒犻櫎琛岀殑鏂瑰紡浣跨敤琛ㄣ�傚彟涓�涓�鍙�鑳芥�ф槸鍦ㄥ垹闄ゅぇ閲忚�屽悗杩愯��OPTIMIZE TABLE銆傚弬瑙�15.1鑺傦紝鈥淢yISAM瀛樺偍寮曟搸鈥�銆�

路         瑕佷慨澶嶄换浣�ARCHIVE琛ㄥ彲浠ュ彂鐢熺殑鍘嬬缉闂�棰橈紝鍙�浠ユ墽琛�OPTIMIZE TABLE銆傚弬瑙�15.8鑺傦紝鈥淎RCHIVE瀛樺偍寮曟搸鈥�銆�

路         濡傛灉浣犱富瑕佹寜expr1锛�expr2锛�...椤哄簭妫�绱㈣�岋紝浣跨敤ALTER TABLE ... ORDER BY expr1, expr2, ...銆傚�硅〃澶ч噺鏇存敼鍚庝娇鐢ㄨ�ラ�夐」锛屽彲浠ヨ幏寰楁洿濂界殑鎬ц兘銆�

路         鍦ㄤ竴浜涙儏鍐典笅锛屼娇寰楀熀浜庢潵鑷�鍏跺畠琛ㄧ殑鍒楃殑淇℃伅寮曞叆涓�涓�鈥� 鍝堝笇鈥濈殑鍒楁湁鎰忎箟銆傚�傛灉璇ュ垪杈冪煭骞朵笖鏈夊悎鐞嗙殑鍞�涓�鍊硷紝瀹冨彲浠ユ瘮鍦ㄨ�稿�氬垪涓婄殑涓�涓�澶х储寮曞揩浜涖�傚湪MySQL涓�锛屽緢瀹规槗浣跨敤杩欎釜棰濆�栧垪锛�

路                SELECT * FROM tbl_name

路                     WHERE hash_col=MD5(CONCAT(col1,col2))

路                     AND col1='constant' AND col2='constant';

路         瀵逛簬棰戠箒鏇存敼鐨�MyISAM琛�锛屽簲璇曞浘閬垮厤鎵�鏈夊彉闀垮垪(VARCHAR銆�BLOB鍜�TEXT)銆傚�傛灉琛ㄥ寘鎷�鍗曚竴鐨勫彉闀垮垪鍒欎娇鐢ㄥ姩鎬佽�板綍鏍煎紡銆傚弬瑙�绗�15绔狅細瀛樺偍寮曟搸鍜岃〃绫诲瀷銆�

路         鍙�鏄�鍥犱负琛屽お澶э紝灏嗕竴寮犺〃鍒嗗壊涓轰笉鍚岀殑琛ㄤ竴鑸�娌℃湁浠�涔堢敤澶勩�備负浜嗚�块棶琛岋紝鏈�澶х殑鎬ц兘鍐插嚮鏄�纾佺洏鎼滅储浠ユ壘鍒拌�岀殑绗�涓�涓�瀛楄妭銆傚湪鎵惧埌鏁版嵁鍚庯紝澶у�氭暟鏂板瀷纾佺洏瀵瑰ぇ澶氭暟搴旂敤绋嬪簭鏉ヨ�磋冻澶熷揩锛岃兘璇诲叆鏁翠釜琛屻�傜‘瀹炴湁蹇呰�佸垎鍓茬殑鍞�涓�鎯呭舰鏄�濡傛灉瀹冩槸浣跨敤鍔ㄦ�佽�板綍鏍煎紡浣夸箣鍙樹负鍥哄畾鐨勮�板綍澶у皬鐨�MyISAM琛�(瑙佷笂杩�)锛屾垨濡傛灉浣犻渶瑕佸緢棰戠箒鍦版壂鎻忚〃鑰屼笉闇�瑕佸ぇ澶氭暟鍒椼�傚弬瑙�绗�15绔狅細瀛樺偍寮曟搸鍜岃〃绫诲瀷銆�

路         濡傛灉浣犻渶瑕佸緢缁忓父鍦拌�＄畻缁撴灉锛屼緥濡傚熀浜庢潵鑷�寰堝�氳�岀殑淇℃伅鐨勮�℃暟锛屽紩鍏ヤ竴涓�鏂拌〃骞跺疄鏃舵洿鏂拌�℃暟鍣ㄥ彲鑳芥洿濂戒竴浜涖�備笅闈㈠舰寮忕殑鏇存柊浼氭洿蹇�涓�浜涳細

UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;

褰撲綘浣跨敤璞�MyISAM閭ｆ牱鐨勫彧鏈夎〃绾ч攣瀹氱殑MySQL瀛樺偍寮曟搸(澶氶噸璇�/鍗曚釜鍐�)鏃讹紝杩欑‘瀹炲緢閲嶈�併�傝繖涔熺粰澶у�氭暟鏁版嵁搴撹緝濂界殑鎬ц兘锛屽洜涓鸿�岄攣瀹氱�＄悊鍣ㄥ湪杩欑�嶆儏鍐典笅鏈夎緝灏戠殑浜嬫儏瑕佸仛銆�

路         濡傛灉浣犻渶瑕佷粠澶х殑璁板綍鏂囦欢琛ㄤ腑鏀堕泦缁熻�′俊鎭�锛屼娇鐢ㄦ�荤粨鎬х殑琛ㄨ�屼笉鏄�鎵�鎻忔暣涓�琛ㄣ�傜淮鎶ゆ�荤粨搴旇�ユ瘮灏濊瘯鍋氣�滃疄鏃垛�濈粺璁¤�佸揩浜涖�傚綋鏈夊彉鍖栨椂浠庢棩蹇楅噸鏂扮敓鎴愭柊鐨勬�荤粨琛ㄦ瘮鏀瑰彉杩愯�岀殑搴旂敤(鍙栧喅浜庝笟鍔″喅绛�)瑕佸揩寰楀�氥��

• 濡傛灉鍙�鑳斤紝搴旇�ュ皢鎶ュ憡鍒嗙被涓衡�滃疄鏃垛�濇垨鈥滅粺璁♀�濓紝杩欓噷缁熻�℃姤鍛婃墍闇�鐨勬暟鎹�浠呬粎鍩轰簬浠庡疄闄呮暟鎹�瀹氭湡浜х敓鐨勬�荤粨琛ㄤ腑浜х敓銆�
• 鍏呭垎鍒╃敤鍒楁湁榛樿�ゅ�肩殑浜嬪疄銆傚彧鏈夊綋鎻掑叆鐨勫�间笉鍚屼簬榛樿�ゅ�兼椂锛屾墠鏄庣‘鍦版彃鍏ュ�笺�傝繖鍑忓皯MySQL闇�瑕佸仛鐨勮��娉曞垎鏋愪粠鑰屾彁楂樻彃鍏ラ�熷害銆�
• 鍦ㄤ竴浜涙儏鍐典笅锛屽寘瑁呭苟瀛樺偍鏁版嵁鍒颁竴涓�BLOB鍒椾腑鏄�寰堟柟渚跨殑銆傚湪杩欑�嶆儏鍐典笅锛屽繀椤诲湪浣犵殑搴旂敤涓�澧炲姞棰濆�栫殑浠ｇ爜鏉ユ墦鍖�/瑙ｅ寘淇℃伅锛屼絾鏄�杩欑�嶆柟娉曞彲浠ュ湪鏌愪簺闃舵�佃妭鐪佸緢澶氳�块棶銆傚綋鏈変笉绗﹀悎琛屽拰鍒楄〃缁撴瀯鐨勬暟鎹�鏃讹紝杩欏緢瀹炵敤銆�
• 鍦ㄤ竴鑸�鎯呭喌涓嬶紝搴旇�ュ皾璇曚互闈炲啑浣欐柟寮�(鏌ョ湅鏁版嵁搴撶悊璁轰腑鐨�绗�涓夋�ｅ垯褰㈠紡)淇�瀛樻暟鎹�锛屼絾鏄�涓轰簡鑾峰緱鏇村揩鐨勯�熷害锛屽彲浠ュ�嶅埗淇℃伅鎴栧垱寤烘�荤粨琛ㄣ��
• 瀛樺偍杩囩▼鎴�UDF(鐢ㄦ埛瀹氫箟鍑芥暟)鍙�鑳芥槸鑾峰緱鏇村ソ鎬ц兘鐨勪竴涓�濂芥柟娉曪紝璇︾粏淇℃伅鍙傝��绗�20绔狅細瀛樺偍绋嬪簭鍜屽嚱鏁�鍜�27.2鑺傦紝鈥滀负MySQL娣诲姞鏂板嚱鏁扳��銆�
• 鎬绘槸鑳介�氳繃鍦ㄥ簲鐢ㄧ▼搴忎腑缂撳瓨鏌ヨ��/绛旀�堝苟灏濊瘯鍚屾椂鎵ц�屽緢澶氭彃鍏�/鏇存柊鏉ヨ幏寰椾竴浜涘ソ澶勩�傚�傛灉鏁版嵁搴撴敮鎸侀攣瀹氳〃(璞�MySQL鍜�Oracle)锛岃繖搴旇�ユ湁鍔╀簬纭�淇濈储寮曠紦瀛樺彧鍦ㄦ墍鏈夋洿鏂板悗鍒锋柊涓�娆°�傝繕鍙�浠ュ埄鐢�MySQL鐨勬煡璇㈢紦瀛樻潵鑾峰緱绫讳技鐨勭粨鏋滐紱鍙傝��5.13鑺傦紝鈥淢ySQL鏌ヨ�㈤珮閫熺紦鍐测��銆�
• 褰撲笉闇�瑕佺煡閬撲綍鏃跺啓鍏ユ暟鎹�鏃讹紝浣跨敤INSERT DELAYED銆傝繖鏍峰彲浠ュ姞蹇�澶勭悊锛屽洜涓哄緢澶氳�板綍鍙�浠ラ�氳繃涓�娆＄�佺洏鍐欏叆琚�鍐欏叆銆�
• 褰撲綘鎯宠�佽�╅�夋嫨鏄惧緱鏇撮噸瑕佹椂锛屼娇鐢�INSERT /*! LOW_PRIORITY */銆�
• 浣跨敤INSERT LOW_PRIORITY鏉ュ彇寰楁彃鍏ラ槦鍒楃殑妫�绱�锛屼篃灏辨槸鍗充娇鏈夊彟涓�涓�瀹㈡埛绛夊緟鍐欏叆涔熻�佹墽琛�SELECT銆�
• 浣跨敤澶氳��INSERT璇�鍙ラ�氳繃涓�涓�SQL鍛戒护鏉ュ瓨鍌ㄥ緢澶氳��(璁稿��SQL鏈嶅姟鍣ㄦ敮鎸佸畠锛屽寘鎷�MySQL)銆�
• 浣跨敤LOAD DATA INFILE瑁呰浇杈冨ぇ鏁伴噺鐨勬暟鎹�銆傝繖姣斾娇鐢�INSERT瑕佸揩寰楀�氥��
• 浣跨敤AUTO_INCREMENT鍒楁瀯鎴愬敮涓�鍊笺��
• 褰�MyISAM浣跨敤鍔ㄦ�佽〃鏍煎紡鏃讹紝鍋跺皵浣跨敤OPTIMIZE TABLE鍙�浠ラ伩鍏嶇�庣墖銆傚弬瑙�15.1.3鑺傦紝鈥淢yISAM琛ㄧ殑瀛樺偍鏍煎紡鈥�銆�
• 鍙�鑳芥椂浣跨敤MEMORY琛ㄤ互寰楀埌鏇村揩鐨勯�熷害銆�鍙傝��15.4鑺傦紝鈥淢EMORY (HEAP)瀛樺偍寮曟搸鈥�銆�
• 鍦�Web鏈嶅姟鍣ㄤ腑锛屽浘璞″拰鍏跺畠浜岃繘鍒惰祫浜у簲璇ヤ綔涓烘枃浠跺瓨鍌ㄣ�備篃灏辨槸浠呭湪鏁版嵁搴撲腑瀛樺偍鐨勬湰鏂囦欢鐨勫紩鐢ㄨ�屼笉鏄�鏂囦欢鏈�韬�銆傚ぇ澶氭暟Web鏈嶅姟鍣ㄥ湪缂撳瓨鏂囦欢鏂归潰姣旀暟鎹�搴撳唴瀹硅�佸ソ寰楀�氾紝鍥犳�や娇鐢ㄦ枃浠朵竴鑸�瑕佸揩寰楀�氥��
• 瀵圭粡甯歌�块棶鐨勪笉閲嶈�佹暟鎹�(濡備负娌℃湁鍦�Web 娴忚�堝櫒涓�鍚�鐢�cookie鐨勭敤鎴锋渶鍚庢樉绀虹殑鏍囪��鐨勭浉鍏充俊鎭�)浣跨敤鍐呭瓨琛ㄣ�傚湪璁稿��Web搴旂敤绋嬪簭鐜�澧冧腑涔熷彲浠ヤ娇鐢ㄧ敤鎴蜂細璇濇潵澶勭悊鍙�鍙樼姸鎬佹暟鎹�銆�
• 鍦ㄤ笉鍚岃〃涓�鍏锋湁鐩稿悓淇℃伅鐨勫垪搴旇�ヨ��澹版槑涓虹浉鍚岀殑骞舵湁鐩稿悓鐨勫悕瀛椼�傚皾璇曚娇鍚嶅瓧绠�鍗曞寲銆備緥濡傦紝鍦�customer琛ㄤ腑浣跨敤name鑰屼笉鏄�customer_name銆備负浜嗕娇鍚嶅瓧鑳界Щ妞嶅埌鍏跺畠SQL鏈嶅姟鍣�锛屽簲璇ヤ娇鍚嶅瓧鐭�浜�18涓�瀛楃�︺��
• 濡傛灉纭�瀹為渶瑕佸緢楂樼殑閫熷害锛屽簲璇ョ爺绌朵竴涓嬩笉鍚�SQL鏈嶅姟鍣ㄦ敮鎸佺殑鏁版嵁瀛樺偍鐨勪綆灞傛帴鍙ｏ紒渚嬪�傜洿鎺ヨ�块棶MySQL MyISAM瀛樺偍寮曟搸锛屾瘮璧蜂娇鐢�SQL鎺ュ彛锛岄�熷害鍙�浠ユ彁楂�2-5鍊嶃�備负浜嗚兘瀹炵幇锛屾暟鎹�蹇呴』涓庡簲鐢ㄧ▼搴忓湪鍚屼竴鍙版湇鍔″櫒涓婏紝骞朵笖閫氬父鍙�搴旇�ヨ��涓�涓�杩涚▼璁块棶(鍥犱负澶栭儴鏂囦欢閿佸畾纭�瀹炲緢鎱�)銆傞�氳繃鍦�MySQL鏈嶅姟鍣ㄤ腑寮曡繘浣庡眰MyISAM鍛戒护鑳芥秷闄や互涓婇棶棰�(濡傛灉闇�瑕侊紝杩欏彲鑳芥槸鑾峰緱鏇村ソ鎬ц兘鐨勪竴涓�绠�鍗曠殑鏂规硶)銆傞�氳繃绮惧績璁捐�℃暟鎹�搴撴帴鍙ｏ紝搴旇�ヨ兘鐩稿綋瀹规槗鍦版敮鎸佽繖绫讳紭鍖栥��
• 濡傛灉姝ｄ娇鐢ㄦ暟瀛楁暟鎹�锛屽湪璁稿�氭儏鍐典笅锛屼粠涓�涓�鏁版嵁搴撹�块棶淇℃伅(浣跨敤瀹炴椂杩炴帴)姣旇�块棶涓�涓�鏂囨湰鏂囦欢蹇�浜涖�傝繖鏄�鍥犱负鏁版嵁搴撲腑鐨勪俊鎭�姣旀枃鏈�鏂囦欢鏇寸揣鍑戯紝鍥犳�よ繖灏嗘秹鍙婃洿灏戠殑纾佺洏璁块棶銆傝繕鍙�浠ュ湪搴旂敤绋嬪簭涓�鑺傜渷浠ｇ爜锛屽洜涓轰笉椤诲垎鏋愭枃鏈�鏂囦欢鏉ユ壘鍑鸿�屽拰鍒楃殑杈圭晫銆�

路         閫氳繃澶嶅埗鍙�浠ユ彁楂樻煇浜涙搷浣滅殑鎬ц兘銆傚彲浠ュ湪澶嶅埗鏈嶅姟鍣ㄤ腑鍒嗗竷瀹㈡埛鐨勬��绱�浠ュ潎鍒嗚礋杞姐�備负浜嗛槻姝㈠�囦唤鏃朵富鏈嶅姟鍣ㄥ彉鎱�锛屽彲浠ヤ娇鐢ㄤ竴涓�浠庢湇鍔″櫒鏉ュ�囦唤銆傚弬瑙�绗�6绔狅細MySQL涓�鐨勫�嶅埗銆�

路         鐢�DELAY_KEY_WRITE=1閫夐」澹版槑MyISAM琛ㄥ彲浠ヤ娇绱㈠紩鏇存柊鏇村揩锛屽洜涓哄湪琛ㄥ叧闂�涔嬪墠瀹冧滑涓嶅埛鏂板埌纭�鐩樹笂銆備笉鍒╀箣澶勬槸褰撹〃鎵撳紑鏃跺�傛灉鏉�鎺夋湇鍔″櫒锛屽簲纭�淇濈敤--myisam-recover閫夐」杩愯�屾湇鍔″櫒淇濊瘉娌℃湁闂�棰橈紝鎴栬�呭湪閲嶅惎鏈嶅姟鍣ㄤ箣鍓嶈繍琛�myisamchk銆�(鐒惰�岋紝鍗充娇鍦ㄨ繖绉嶆儏鍐典笅锛屽簲閫氳繃浣跨敤DELAY_KEY_WRITE淇濊瘉涓嶄涪澶辨暟鎹�锛屽洜涓哄叧閿�瀛椾俊鎭�鎬绘槸鍙�浠ヤ粠鏁版嵁琛屼骇鐢燂級銆�

路                UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;

路                DELETE FROM tbl_name WHERE unique_key_col=key_value;

涓鸿揪鍒版渶楂橀攣瀹氶�熷害锛岄櫎InnoDB鍜�BDB涔嬪�栵紝瀵规墍鏈夊瓨鍌ㄥ紩鎿庯紝MySQL浣跨敤琛ㄩ攣瀹�(鑰屼笉鏄�椤点�佽�屾垨鑰呭垪閿佸畾)銆�

瀵逛簬InnoDB鍜�BDB琛�锛屽�傛灉浣犵敤LOCK TABLES鏄惧紡閿佸畾琛�锛�MySQL鍙�浣跨敤琛ㄩ攣瀹氥�傚�逛簬杩欎簺琛ㄧ被鍨嬶紝鎴戜滑寤鸿��浣犳牴鏈�涓嶈�佷娇鐢�LOCK TABLES锛屽洜涓�InnoDB浣跨敤鑷�鍔ㄨ�岀骇閿佸畾鑰�BDB浣跨敤椤电骇閿佸畾鏉ヤ繚璇佷簨鍔￠殧绂汇��

瀵逛簬澶ц〃锛屽�逛簬澶у�氭暟搴旂敤绋嬪簭锛岃〃閿佸畾姣旇�岄攣瀹氭洿濂斤紝浣嗗瓨鍦ㄩ儴鍒嗙己闄枫��

琛ㄩ攣瀹氫娇璁稿�氱嚎绋嬪悓鏃朵粠涓�涓�琛ㄤ腑杩涜�岃�诲彇鎿嶄綔锛屼絾濡傛灉涓�涓�绾跨▼鎯宠�佸�硅〃杩涜�屽啓鎿嶄綔锛屽畠蹇呴』棣栧厛鑾峰緱鐙�鍗犺�块棶銆傛洿鏂版湡闂达紝鎵�鏈夊叾瀹冩兂瑕佽�块棶璇ヨ〃鐨勭嚎绋嬪繀椤荤瓑寰呯洿鍒版洿鏂板畬鎴愩��

琛ㄦ洿鏂伴�氬父鎯呭喌璁や负姣旇〃妫�绱㈡洿閲嶈�侊紝鍥犳�ょ粰浜堝畠浠�鏇撮珮鐨勪紭鍏堢骇銆傝繖搴旂‘淇濇洿鏂颁竴涓�琛ㄧ殑娲诲姩涓嶈兘鈥�楗挎��鈥濓紝鍗充娇璇ヨ〃涓婃湁寰堢箒閲嶇殑SELECT娲诲姩銆�

琛ㄩ攣瀹氬湪杩欑�嶆儏鍐典笅浼氶�犳垚闂�棰橈紝渚嬪�傚綋绾跨▼姝ｇ瓑寰咃紝鍥犱负纭�鐩樺凡婊″苟涓斿湪绾跨▼鍙�浠ュ�勭悊涔嬪墠蹇呴』鏈夌┖闂茬┖闂淬�傚湪杩欑�嶆儏鍐典笅锛屾墍鏈夋兂瑕佽�块棶鍑虹幇闂�棰樼殑琛ㄧ殑绾跨▼涔熻��璁剧疆鎴愮瓑寰呯姸鎬侊紝鐩村埌鏈夋洿澶氱殑纭�鐩樼┖闂村彲鐢ㄣ��

琛ㄩ攣瀹氬湪涓嬮潰鐨勬儏鍐典笅涔熷瓨鍦ㄩ棶棰橈細

路         涓�涓�瀹㈡埛鍙戝嚭闀挎椂闂磋繍琛岀殑鏌ヨ��銆�

路         鐒跺悗锛屽彟涓�涓�瀹㈡埛瀵瑰悓涓�涓�琛ㄨ繘琛屾洿鏂般�傝�ュ�㈡埛蹇呴』绛夊緟鐩村埌SELECT瀹屾垚銆�

路         鍙︿竴涓�瀹㈡埛瀵瑰悓涓�涓�琛ㄤ笂鍙戝嚭浜嗗彟涓�涓�SELECT璇�鍙ャ�傚洜涓�UPDATE姣�SELECT浼樺厛绾ч珮锛岃��SELECT璇�鍙ョ瓑寰�UPDATE瀹屾垚锛屽苟涓旂瓑寰呯��1涓�SELECT瀹屾垚銆�

涓嬮潰鎻忚堪浜嗕竴浜涙柟娉曟潵閬垮厤鎴栧噺灏戣〃閿佸畾閫犳垚鐨勭珵浜夛細

路         璇曞浘浣�SELECT璇�鍙ヨ繍琛屽緱鏇村揩銆備綘鍙�鑳藉繀椤诲垱寤轰竴浜涙憳瑕�(summary)琛ㄥ仛鍒拌繖鐐广��

路         鐢�--low-priority-updates鍚�鍔�mysqld銆傝繖灏嗙粰鎵�鏈夋洿鏂�(淇�鏀�)涓�涓�琛ㄧ殑璇�鍙ヤ互姣�SELECT璇�鍙ヤ綆鐨勪紭鍏堢骇銆傚湪杩欑�嶆儏鍐典笅锛屽湪鍏堝墠鎯呭舰鐨勭��2涓�SELECT璇�鍙ュ皢鍦�UPDATE璇�鍙ュ墠鎵ц�岋紝鑰屼笉闇�瑕佺瓑鍊欑��1涓�SELECT瀹屾垚銆�

路         鍙�浠ヤ娇鐢�SET LOW_PRIORITY_UPDATES=1璇�鍙ユ寚瀹氬叿浣撹繛鎺ヤ腑鐨勬墍鏈夋洿鏂板簲浣跨敤浣庝紭鍏堢骇銆傚弬瑙�13.5.3鑺傦紝鈥淪ET璇�娉曗��

路         鍙�浠ョ敤LOW_PRIORITY灞炴�х粰涓庝竴涓�鐗瑰畾鐨�INSERT銆�UPDATE鎴�DELETE璇�鍙ヨ緝浣庝紭鍏堢骇銆�

路         鍙�浠ョ敤HIGH_PRIORITY灞炴�х粰涓庝竴涓�鐗瑰畾鐨�SELECT璇�鍙ヨ緝楂樹紭鍏堢骇銆傚弬瑙�13.2.7鑺傦紝鈥淪ELECT璇�娉曗��銆�

路         涓�max_write_lock_count绯荤粺鍙橀噺鎸囧畾涓�涓�浣庡�兼潵鍚�鍔�mysqld鏉ュ己鍒�MySQL鍦ㄥ叿浣撴暟閲忕殑鎻掑叆瀹屾垚鍚庝复鏃舵彁楂樻墍鏈夌瓑寰呬竴涓�琛ㄧ殑SELECT璇�鍙ョ殑浼樺厛绾с�傝繖鏍峰厑璁稿湪涓�瀹氭暟閲忕殑WRITE閿佸畾鍚庣粰鍑�READ閿佸畾銆�

路         濡傛灉浣犳湁鍏充簬INSERT缁撳悎SELECT鐨勯棶棰橈紝鍒囨崲鍒颁娇鐢ㄦ柊鐨�MyISAM琛�锛屽洜涓哄畠浠�鏀�鎸佸苟鍙戠殑SELECT鍜�INSERT銆�

路         濡傛灉浣犲�瑰悓涓�涓�琛ㄦ贩鍚堟彃鍏ュ拰鍒犻櫎锛�INSERT DELAYED灏嗕細鏈夊緢澶х殑甯�鍔┿�傚弬瑙�13.2.4.2鑺傦紝鈥淚NSERT DELAYED璇�娉曗��銆�

路         濡傛灉浣犲�瑰悓涓�涓�琛ㄦ贩鍚堜娇鐢�SELECT鍜�DELETE璇�鍙ュ嚭鐜伴棶棰橈紝DELETE鐨�LIMIT閫夐」鍙�浠ユ湁鎵�甯�鍔┿�傚弬瑙�13.2.1鑺傦紝鈥淒ELETE璇�娉曗��銆�

路         瀵�SELECT璇�鍙ヤ娇鐢�SQL_BUFFER_RESULT鍙�浠ュ府鍔╀娇琛ㄩ攣瀹氭椂闂村彉鐭�銆傚弬瑙�13.2.7鑺傦紝鈥淪ELECT璇�娉曗��銆�

路         鍙�浠ユ洿鏀�mysys/thr_lock.c涓�鐨勯攣浠ｇ爜浠ヤ娇鐢ㄥ崟涓�鐨勯槦鍒椼�傚湪杩欑�嶆儏鍐典笅锛屽啓閿佸畾鍜岃�婚攣瀹氬皢鍏锋湁鐩稿悓鐨勪紭鍏堢骇锛屽�逛竴浜涘簲鐢ㄧ▼搴忎細鏈夊府鍔┿��

杩欓噷鏄�涓�浜�MySQL涓�琛ㄩ攣瀹氱浉鍏崇殑鎶�宸э細

路         濡傛灉涓嶆贩鍚堟洿鏂颁笌闇�瑕佸湪鍚屼竴涓�琛ㄤ腑妫�鏌ヨ�稿�氳�岀殑閫夋嫨锛屽彲浠ヨ繘琛屽苟琛屾搷浣溿��

路         鍙�浠ヤ娇鐢�LOCK TABLES鏉ユ彁楂橀�熷害锛屽洜涓哄湪涓�涓�閿佸畾涓�杩涜�岃�稿�氭洿鏂版瘮娌℃湁閿佸畾鐨勬洿鏂拌�佸揩寰楀�氥�傚皢琛ㄤ腑鐨勫唴瀹瑰垏鍒嗕负鍑犱釜琛ㄤ篃鍙�浠ユ湁鎵�甯�鍔┿��

路         濡傛灉鍦�MySQL涓�琛ㄩ攣瀹氭椂閬囧埌閫熷害闂�棰橈紝鍙�浠ュ皢浣犵殑琛ㄨ浆鎹�涓�InnoDB鎴�BDB琛ㄦ潵鎻愰珮鎬ц兘銆傚弬瑙�15.2鑺傦紝鈥淚nnoDB瀛樺偍寮曟搸鈥�鍜�15.5鑺傦紝鈥淏DB (BerkeleyDB)瀛樺偍寮曟搸鈥�銆�

鏈�鍩烘湰鐨勪紭鍖栦箣涓�鏄�浣胯〃鍦ㄧ�佺洏涓婂崰鎹�鐨勭┖闂村敖鍙�鑳藉皬銆傝繖鑳界粰鍑哄法澶х殑鏀硅繘锛屽洜涓虹�佺洏璇诲叆杈冨揩锛屽苟涓斿湪鏌ヨ�㈡墽琛岃繃绋嬩腑灏忚〃鐨勫唴瀹硅��澶勭悊鏃跺崰鐢ㄨ緝灏戠殑涓诲瓨鍌ㄥ櫒銆傚�傛灉鍦ㄦ洿灏忕殑鍒椾笂鍋氱储寮曪紝绱㈠紩涔熷崰鎹�杈冨皯鐨勮祫婧愩��

MySQL鏀�鎸佽�稿�氫笉鍚岀殑瀛樺偍寮曟搸(琛ㄧ被鍨�)鍜岃�屾牸寮忋�傚�逛簬姣忎釜琛�锛屽彲浠ョ‘瀹氫娇鐢ㄥ摢涓�瀛樺偍寮曟搸鍜岀储寮曟柟娉曘�備负搴旂敤绋嬪簭閫夋嫨鍚堥�傜殑琛ㄦ牸寮忓彲浠ュぇ澶ф彁楂樻�ц兘銆傚弬瑙�绗�15绔狅細瀛樺偍寮曟搸鍜岃〃绫诲瀷銆�

鍙�浠ヤ娇鐢ㄤ笅闈㈢殑鎶�鏈�鍙�浠ヤ娇琛ㄧ殑鎬ц兘鏇村ソ骞朵笖浣垮瓨鍌ㄧ┖闂存渶灏忥細

• 灏藉彲鑳藉湴浣跨敤鏈�鏈夋晥(鏈�灏�)鐨勬暟鎹�绫诲瀷銆�MySQL鏈夊緢澶氳妭鐪佺�佺洏绌洪棿鍜屽唴瀛樼殑涓撲笟鍖栫被鍨嬨��
• 灏藉彲鑳戒娇鐢ㄨ緝灏忕殑鏁存暟绫诲瀷浣胯〃鏇村皬銆備緥濡傦紝MEDIUMINT缁忓父姣�INT濂戒竴浜涳紝鍥犱负MEDIUMINT鍒椾娇鐢ㄧ殑绌洪棿瑕佸皯25%銆�
• 濡傛灉鍙�鑳斤紝澹版槑鍒椾负NOT NULL銆傚畠浣夸换浣曚簨鎯呮洿蹇�鑰屼笖姣忓垪鍙�浠ヨ妭鐪佷竴浣嶃�傛敞鎰忓�傛灉鍦ㄥ簲鐢ㄧ▼搴忎腑纭�瀹為渶瑕�NULL锛屽簲璇ユ��鏃犵枒闂�浣跨敤瀹冿紝鍙�鏄�閬垮厤 榛樿�ゅ湴鍦ㄦ墍鏈夊垪涓婃湁瀹冦��
• 瀵逛簬MyISAM琛�锛屽�傛灉娌℃湁浠讳綍鍙橀暱鍒�(VARCHAR銆�TEXT鎴�BLOB鍒�)锛屼娇鐢ㄥ浐瀹氬昂瀵哥殑璁板綍鏍煎紡銆傝繖姣旇緝蹇�浣嗘槸涓嶅垢鍦板彲鑳戒細娴�璐逛竴浜涚┖闂淬�傚弬瑙�15.1.3鑺傦紝鈥淢yISAM琛ㄧ殑瀛樺偍鏍煎紡鈥�銆傚嵆浣夸綘宸茬粡鐢�CREATE閫夐」璁�VARCHAR鍒�ROW_FORMAT=fixed锛屼篃鍙�浠ユ彁绀烘兂浣跨敤鍥哄畾闀垮害鐨勮�屻��
• 鍦�MySQL/InnoDB涓�锛�InnoDB琛ㄤ娇鐢ㄦ洿绱у噾鐨勫瓨鍌ㄦ牸寮忋�傚湪浠ュ墠鐗堟湰鐨�MySQL涓�锛�InnoDB璁板綍鍖呭惈涓�浜涘啑浣欎俊鎭�锛屼緥濡傚垪鏁扮洰鍜屾瘡涓�鍒楃殑闀垮害锛屽嵆浣垮�逛簬鍥哄畾澶у皬鐨勫垪銆傞粯璁ゆ儏鍐碉紝鍒涘缓鐨勮〃涓虹揣鍑戞牸寮�(ROW_FORMAT=COMPACT)銆傚�傛灉鎯宠�侀檷绾ф棫鐗堟湰鐨�MySQL/InnoDB锛屽彲浠ョ敤ROW_FORMAT=REDUNDANT瑕佹眰鏃х殑鏍煎紡銆�
• 绱у噾鐨�InnoDB鏍煎紡涔熸敼鍙樹簡鍖呭惈UTF-8鏁版嵁鐨�CHAR鍒楃殑淇濆瓨鏂瑰紡銆傚湪ROW_FORMAT=REDUNDANT鏍煎紡涓�锛�UTF-8 CHAR(n)鍗犵敤3*n瀛楄妭锛�鍋�瀹�UTF-8缂栫爜鐨勫瓧绗︾殑鏈�澶ч暱搴︽槸3瀛楄妭銆傝�稿�氳��瑷�鍙�浠ヤ富瑕佺敤鍗曞瓧鑺�UTF-8瀛楃�︽潵缂栧啓锛屽浐瀹氱殑瀛樺偍闀垮害閫氬父浼氭氮璐圭┖闂淬�傞�氳繃鏍规嵁闇�瑕佸墺绂诲熬閮ㄧ殑绌烘牸锛�ROW_FORMAT=COMPACT鏍煎紡涓鸿繖浜涘垪鍒嗛厤鍙�鍙樻暟閲忕殑n..3*n瀛楄妭銆傛渶灏忓瓨鍌ㄩ暱搴︽寜椤哄簭淇濆瓨涓�n瀛楄妭锛屼互鍦ㄥ吀鍨嬫儏鍐典笅甯�鍔╂洿鏂般��
• 姣忓紶琛ㄧ殑涓荤储寮曞簲璇ュ敖鍙�鑳界煭銆傝繖浣夸竴琛岀殑璇嗗埆瀹规槗鑰屾湁鏁堛��
• 鍙�鍒涘缓浣犵‘瀹為渶瑕佺殑绱㈠紩銆傜储寮曞�规��绱㈡湁濂藉�勶紝浣嗘槸褰撲綘闇�瑕佸揩閫熷瓨鍌ㄤ笢瑗挎椂灏卞彉寰楃碂绯曘�傚�傛灉涓昏�侀�氳繃鎼滅储鍒楃殑缁勫悎鏉ュ瓨鍙栦竴涓�琛�锛屽�瑰畠浠�鍋氫竴涓�绱㈠紩銆傜��涓�涓�绱㈠紩閮ㄥ垎搴旇�ユ槸鏈�甯哥敤鐨勫垪銆傚�傛灉浠庤〃涓�閫夋嫨鏃舵�绘槸浣跨敤璁稿�氬垪锛屽簲璇ラ�栧厛浠ユ洿澶氱殑鍓�鏈�浣跨敤鍒椾互鑾峰緱鏇村ソ鐨勭储寮曞帇缂┿��
• 濡傛灉寰堝彲鑳戒竴涓�绱㈠紩鍦ㄥご鍑犱釜瀛楃�︿笂鏈夊敮涓�鐨勫墠缂�锛屼粎浠呯储寮曡�ュ墠缂�姣旇緝濂姐��MySQL鏀�鎸佸�逛竴涓�瀛楃�﹀垪鐨勬渶宸﹁竟閮ㄥ垎鍒涘缓涓�涓�绱㈠紩(鍙傝��13.1.4鑺傦紝鈥淐REATE INDEX璇�娉曗��)銆傛洿鐭�鐨勭储寮曚細鏇村揩锛屼笉浠呭洜涓哄畠浠�鍗犺緝灏戠殑纾佺洏绌洪棿锛岃�屼笖鍥犱负瀹冧滑灏嗗湪绱㈠紩缂撳瓨涓�鎻愪緵鏇村�氱殑璁块棶锛屽洜姝ょ�佺洏鎼滅储鏇村皯銆傚弬瑙�7.5.2鑺傦紝鈥滆皟鑺傛湇鍔″櫒鍙傛暟鈥�銆�

路         鍦ㄤ竴浜涙儏褰�涓嬶紝灏嗕竴涓�缁忓父琚�鎵�鎻忕殑琛ㄥ垎鍓蹭负2涓�琛ㄦ槸鏈夌泭鐨勩�傜壒鍒�鏄�濡傛灉瀹冩槸涓�涓�鍔ㄦ�佹牸寮忕殑琛�锛屽苟涓斿彲鑳戒娇鐢ㄤ竴涓�鎵�鎻忚〃鏃惰兘鐢ㄦ潵鎵惧嚭鐩稿叧琛岀殑杈冨皬闈欐�佹牸寮忕殑琛ㄣ��

鎵�鏈�MySQL鍒楃被鍨嬪彲浠ヨ��绱㈠紩銆傚�圭浉鍏冲垪浣跨敤绱㈠紩鏄�鎻愰珮SELECT鎿嶄綔鎬ц兘鐨勬渶浣抽�斿緞銆�

鏍规嵁瀛樺偍寮曟搸瀹氫箟姣忎釜琛ㄧ殑鏈�澶х储寮曟暟鍜屾渶澶х储寮曢暱搴︺�傚弬瑙�绗�15绔狅細瀛樺偍寮曟搸鍜岃〃绫诲瀷銆傛墍鏈夊瓨鍌ㄥ紩鎿庢敮鎸佹瘡涓�琛ㄨ嚦灏�16涓�绱㈠紩锛屾�荤储寮曢暱搴﹁嚦灏戜负256瀛楄妭銆傚ぇ澶氭暟瀛樺偍寮曟搸鏈夋洿楂樼殑闄愬埗銆�

鍦ㄧ储寮曞畾涔変腑鐢�col_name(length)璇�娉曪紝浣犲彲浠ュ垱寤轰竴涓�鍙�浣跨敤CHAR鎴�VARCHAR鍒楃殑绗�1涓�length瀛楃�︾殑绱㈠紩銆傛寜杩欑�嶆柟寮忓彧绱㈠紩鍒楀�肩殑鍓嶇紑鍙�浠ヤ娇绱㈠紩鏂囦欢灏忓緱澶氥��

MyISAM鍜�InnoDB瀛樺偍寮曟搸杩樻敮鎸佸��BLOB鍜�TEXT鍒楃殑绱㈠紩銆傚綋绱㈠紩涓�涓�BLOB鎴�TEXT鍒楁椂锛屼綘蹇呴』涓虹储寮曟寚瀹氬墠缂�闀垮害銆備緥濡傦細

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

鍦�MySQL 5.1涓�锛屽�逛簬MyISAM鍜�InnoDB琛�锛屽墠缂�鍙�浠ヨ揪鍒�1000瀛楄妭闀裤�傝�锋敞鎰忓墠缂�鐨勯檺鍒跺簲浠ュ瓧鑺備负鍗曚綅杩涜�屾祴閲忥紝鑰�CREATE TABLE璇�鍙ヤ腑鐨勫墠缂�闀垮害瑙ｉ噴涓哄瓧绗︽暟銆�褰撲负浣跨敤澶氬瓧鑺傚瓧绗﹂泦鐨勫垪鎸囧畾鍓嶇紑闀垮害鏃朵竴瀹氳�佸姞浠ヨ�冭檻銆�

杩樺彲浠ュ垱寤�FULLTEXT绱㈠紩銆傝�ョ储寮曞彲浠ョ敤浜庡叏鏂囨悳绱�銆傚彧鏈�MyISAM瀛樺偍寮曟搸鏀�鎸�FULLTEXT绱㈠紩锛屽苟涓斿彧涓�CHAR銆�VARCHAR鍜�TEXT鍒椼�傜储寮曟�绘槸瀵规暣涓�鍒楄繘琛岋紝涓嶆敮鎸佸眬閮�(鍓嶇紑)绱㈠紩銆傝�︽儏鍙傝��12.7鑺傦紝鈥滃叏鏂囨悳绱㈠姛鑳解��銆�

涔熷彲浠ヤ负绌洪棿鍒楃被鍨嬪垱寤虹储寮曘�傚彧鏈�MyISAM瀛樺偍寮曟搸鏀�鎸佺┖闂寸被鍨嬨�傜┖闂寸储寮曚娇鐢�R-鏍戙��

榛樿�ゆ儏鍐�MEMORY(HEAP)瀛樺偍寮曟搸浣跨敤hash绱㈠紩锛屼絾涔熸敮鎸�B-鏍戠储寮曘��

MySQL鍙�浠ヤ负澶氫釜鍒楀垱寤虹储寮曘�備竴涓�绱㈠紩鍙�浠ュ寘鎷�15涓�鍒椼�傚�逛簬鏌愪簺鍒楃被鍨嬶紝鍙�浠ョ储寮曞垪鐨勫墠缂�(鍙傝��7.4.3鑺傦紝鈥滃垪绱㈠紩鈥�)銆�

澶氬垪绱㈠紩鍙�浠ヨ�嗕负鍖呭惈閫氳繃杩炴帴绱㈠紩鍒楃殑鍊艰�屽垱寤虹殑鍊肩殑鎺掑簭鐨勬暟缁勩��

MySQL鎸夎繖鏍风殑鏂瑰紡浣跨敤澶氬垪绱㈠紩锛氬綋浣犲湪WHERE瀛愬彞涓�涓虹储寮曠殑绗�1涓�鍒楁寚瀹氬凡鐭ョ殑鏁伴噺鏃讹紝鏌ヨ�㈠緢蹇�锛屽嵆浣夸綘娌℃湁鎸囧畾鍏跺畠鍒楃殑鍊笺��

鍋囧畾琛ㄥ叿鏈変笅闈㈢殑缁撴瀯锛�

CREATE TABLE test (

    id INT NOT NULL,

    last_name CHAR(30) NOT NULL,

    first_name CHAR(30) NOT NULL,

    PRIMARY KEY (id),

    INDEX name (last_name,first_name)

);

name绱㈠紩鏄�涓�涓�瀵�last_name鍜�first_name鐨勭储寮曘�傜储寮曞彲浠ョ敤浜庝负last_name锛屾垨鑰呬负last_name鍜�first_name鍦ㄥ凡鐭ヨ寖鍥村唴鎸囧畾鍊肩殑鏌ヨ��銆傚洜姝わ紝name绱㈠紩鐢ㄤ簬涓嬮潰鐨勬煡璇�锛�

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test

    WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test

    WHERE last_name='Widenius'

    AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test

    WHERE last_name='Widenius'

    AND first_name >='M' AND first_name < 'N';

鐒惰�岋紝name绱㈠紩涓�鐢ㄤ簬涓嬮潰鐨勬煡璇�锛�

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test

    WHERE last_name='Widenius' OR first_name='Michael';

MySQL浣跨敤绱㈠紩鎻愰珮鏌ヨ�㈡�ц兘鐨勬柟寮忓皢鍦�7.4.5鑺傦紝鈥淢ySQL濡備綍浣跨敤绱㈠紩鈥�涓�璁ㄨ�恒��

绱㈠紩鐢ㄤ簬蹇�閫熸壘鍑哄湪鏌愪釜鍒椾腑鏈変竴鐗瑰畾鍊肩殑琛屻�備笉浣跨敤绱㈠紩锛�MySQL蹇呴』浠庣��1鏉¤�板綍寮�濮嬬劧鍚庤�诲畬鏁翠釜琛ㄧ洿鍒版壘鍑虹浉鍏崇殑琛屻�傝〃瓒婂ぇ锛岃姳璐圭殑鏃堕棿瓒婂�氥�傚�傛灉琛ㄤ腑鏌ヨ�㈢殑鍒楁湁涓�涓�绱㈠紩锛�MySQL鑳藉揩閫熷埌杈句竴涓�浣嶇疆鍘绘悳瀵诲埌鏁版嵁鏂囦欢鐨勪腑闂达紝娌℃湁蹇呰�佺湅鎵�鏈夋暟鎹�銆傚�傛灉涓�涓�琛ㄦ湁1000琛岋紝杩欐瘮椤哄簭璇诲彇鑷冲皯蹇�100鍊嶃�傛敞鎰忓�傛灉浣犻渶瑕佽�块棶澶ч儴鍒嗚�岋紝椤哄簭璇诲彇瑕佸揩寰楀�氾紝鍥犱负姝ゆ椂鎴戜滑閬垮厤纾佺洏鎼滅储銆�

澶у�氭暟MySQL绱㈠紩(PRIMARY KEY銆�UNIQUE銆�INDEX鍜�FULLTEXT)鍦�B鏍戜腑瀛樺偍銆傚彧鏄�绌洪棿鍒楃被鍨嬬殑绱㈠紩浣跨敤R-鏍戯紝骞朵笖MEMORY琛ㄨ繕鏀�鎸�hash绱㈠紩銆�

瀛楃�︿覆鑷�鍔ㄥ湴鍘嬬缉鍓嶇紑鍜岀粨灏剧┖鏍笺�傚弬瑙�13.1.4鑺傦紝鈥淐REATE INDEX璇�娉曗��銆�

鎬荤殑鏉ヨ�达紝鎸夊悗闈㈢殑璁ㄨ�轰娇鐢ㄧ储寮曘�傛湰鑺傛渶鍚庢弿杩�hash绱㈠紩(鐢ㄤ簬MEMORY琛�)鐨勭壒寰併��

绱㈠紩鐢ㄤ簬涓嬮潰鐨勬搷浣滐細

路         蹇�閫熸壘鍑哄尮閰嶄竴涓�WHERE瀛愬彞鐨勮�屻��

路         鍒犻櫎琛屻�傚�傛灉鍙�浠ュ湪澶氫釜绱㈠紩涓�杩涜�岄�夋嫨锛�MySQL閫氬父浣跨敤鎵惧埌鏈�灏戣�岀殑绱㈠紩銆�

路         褰撴墽琛岃仈鎺ユ椂锛屼粠鍏跺畠琛ㄦ��绱㈣�屻��

路         瀵瑰叿浣撴湁绱㈠紩鐨勫垪key_col鎵惧嚭MAX()鎴�MIN()鍊笺�傜敱棰勫�勭悊鍣ㄨ繘琛屼紭鍖栵紝妫�鏌ユ槸鍚﹀�圭储寮曚腑鍦�key_col涔嬪墠鍙戠敓鎵�鏈夊叧閿�瀛楀厓绱犱娇鐢ㄤ簡WHERE key_part_# = constant銆傚湪杩欑�嶆儏鍐典笅锛�MySQL涓烘瘡涓�MIN()鎴�MAX()琛ㄨ揪寮忔墽琛屼竴娆″叧閿�瀛楁煡鎵撅紝骞剁敤甯告暟鏇挎崲瀹冦�傚�傛灉鎵�鏈夎〃杈惧紡鏇挎崲涓哄父閲忥紝鏌ヨ�㈢珛鍗宠繑鍥炪�備緥濡傦細

路                SELECT MIN(key_part2),MAX(key_part2)

路                    FROM tbl_name WHERE key_part1=10;

路         濡傛灉瀵逛竴涓�鍙�鐢ㄥ叧閿�瀛楃殑鏈�宸﹂潰鐨勫墠缂�杩涜�屼簡鎺掑簭鎴栧垎缁�(渚嬪�傦紝ORDER BY key_part_1,key_part_2)锛屾帓搴忔垨鍒嗙粍涓�涓�琛ㄣ�傚�傛灉鎵�鏈夊叧閿�瀛楀厓绱犲悗闈㈡湁DESC锛屽叧閿�瀛椾互鍊掑簭琚�璇诲彇銆傚弬瑙�7.2.12鑺傦紝鈥淢ySQL濡備綍浼樺寲ORDER BY鈥�銆�

路         鍦ㄤ竴浜涙儏鍐典腑锛屽彲浠ュ�逛竴涓�鏌ヨ�㈣繘琛屼紭鍖栦互渚夸笉鐢ㄦ煡璇㈡暟鎹�琛屽嵆鍙�浠ユ��绱㈠�笺��濡傛灉鏌ヨ�㈠彧浣跨敤鏉ヨ嚜鏌愪釜琛ㄧ殑鏁板瓧鍨嬪苟涓旀瀯鎴愭煇浜涘叧閿�瀛楃殑鏈�宸﹂潰鍓嶇紑鐨勫垪锛屼负浜嗘洿蹇�锛屽彲浠ヤ粠绱㈠紩鏍戞��绱㈠嚭鍊笺��

路                SELECT key_part3 FROM tbl_name

路                    WHERE key_part1=1

鍋囧畾浣犳墽琛屼笅闈㈢殑SELECT璇�鍙ワ細

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

濡傛灉col1鍜�col2涓婂瓨鍦ㄤ竴涓�澶氬垪绱㈠紩锛屽彲浠ョ洿鎺ュ彇鍑虹浉搴旇�屻�傚�傛灉col1鍜�col2涓婂瓨鍦ㄥ崟鍒楃储寮曪紝浼樺寲鍣ㄨ瘯鍥鹃�氳繃鍐冲畾鍝�涓�绱㈠紩灏嗘壘鍒版洿灏戠殑琛屾潵鎵惧嚭鏇村叿闄愬埗鎬х殑绱㈠紩骞朵笖浣跨敤璇ョ储寮曞彇琛屻��

濡傛灉琛ㄦ湁涓�涓�澶氬垪绱㈠紩锛屼紭鍖栧櫒鍙�浠ヤ娇鐢ㄦ渶宸﹂潰鐨勭储寮曞墠缂�鏉ユ壘鍑鸿�屻�備緥濡傦紝濡傛灉鏈変竴涓�3鍒楃储寮�(col1,col2,col3)锛屽垯宸茬粡瀵�(col1)銆�(col1,col2)鍜�(col1,col2,col3)涓婄殑鎼滅储杩涜�屼簡绱㈠紩銆�

濡傛灉鍒椾笉鏋勬垚绱㈠紩鏈�宸﹂潰鐨勫墠缂�锛�MySQL涓嶈兘浣跨敤灞�閮ㄧ储寮曘�傚亣瀹氭湁涓嬮潰鏄剧ず鐨�SELECT璇�鍙ャ��

SELECT * FROM tbl_name WHERE col1=val1;

SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;

SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

濡傛灉 (col1锛�col2锛�col3)鏈変竴涓�绱㈠紩锛屽彧鏈夊墠2涓�鏌ヨ��浣跨敤绱㈠紩銆傜��3涓�鍜岀��4涓�鏌ヨ�㈢‘瀹炲寘鎷�绱㈠紩鐨勫垪锛屼絾(col2)鍜�(col2锛�col3)涓嶆槸 (col1锛�col2锛�col3)鐨勬渶宸﹁竟鐨勫墠缂�銆�

涔熷彲浠ュ湪琛ㄨ揪寮忛�氳繃=銆�>銆�>=銆�<銆�<=鎴栬��BETWEEN鎿嶄綔绗︿娇鐢�B-鏍戠储寮曡繘琛屽垪姣旇緝銆傚�傛灉LIKE鐨勫弬鏁版槸涓�涓�涓嶄互閫氶厤绗﹀紑澶寸殑甯搁噺瀛楃�︿覆锛岀储寮曚篃鍙�浠ョ敤浜�LIKE姣旇緝銆備緥濡傦紝涓嬮潰鐨�SELECT璇�鍙ヤ娇鐢ㄧ储寮曪細

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';

SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

鍦ㄧ��1涓�璇�鍙ヤ腑锛屽彧鑰冭檻甯�'Patrick' <=key_col < 'Patricl'鐨勮�屻�傚湪绗�2涓�璇�鍙ヤ腑锛屽彧鑰冭檻甯�'Pat' <=key_col < 'Pau'鐨勮�屻��

涓嬮潰鐨�SELECT璇�鍙ヤ笉浣跨敤绱㈠紩锛�

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';

SELECT * FROM tbl_name WHERE key_col LIKE other_col;

鍦ㄧ��涓�鏉¤��鍙ヤ腑锛�LIKE鍊间互涓�涓�閫氶厤绗﹀瓧绗﹀紑濮嬨�傚湪绗�浜屾潯璇�鍙ヤ腑锛�LIKE鍊间笉鏄�涓�涓�甯告暟銆�

濡傛灉浣跨敤... LIKE '%string%'骞朵笖string瓒呰繃3涓�瀛楃�︼紝MySQL浣跨敤Turbo Boyer-Moore绠楁硶鍒濆�嬪寲瀛楃�︿覆鐨勬ā寮忕劧鍚庝娇鐢ㄨ�ユā寮忔潵鏇村揩鍦拌繘琛屾悳绱�銆�

濡傛灉col_name琚�绱㈠紩锛屼娇鐢�col_name IS NULL鐨勬悳绱㈠皢浣跨敤绱㈠紩銆�

浠讳綍涓嶈法瓒�WHERE瀛愬彞涓�鐨勬墍鏈�AND绾х殑绱㈠紩涓嶇敤浜庝紭鍖栨煡璇�銆傛崲鍙ヨ瘽璇达紝涓轰簡鑳藉�熶娇鐢ㄧ储寮曪紝蹇呴』鍦ㄦ瘡涓�AND缁勪腑浣跨敤绱㈠紩鍓嶇紑銆�

涓嬮潰鐨�WHERE瀛愬彞浣跨敤绱㈠紩锛�

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

    /* index = 1 OR index = 2 */

... WHERE index=1 OR A=10 AND index=2

    /* optimized like "index_part1='hello'" */

... WHERE index_part1='hello' AND index_part3=5

    /* Can use index on index1 but not on index2 or index3 */

... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

涓嬮潰鐨�WHERE瀛愬彞涓嶄娇鐢ㄧ储寮曪細

    /* index_part1 is not used */

... WHERE index_part2=1 AND index_part3=2

    /*  Index is not used in both parts of the WHERE clause  */

... WHERE index=1 OR A=10

    /* No index spans all rows  */

... WHERE index_part1=1 OR index_part2=10

鏈夋椂MySQL涓嶄娇鐢ㄧ储寮曪紝鍗充娇鏈夊彲鐢ㄧ殑绱㈠紩銆備竴绉嶆儏褰㈡槸褰撲紭鍖栧櫒浼拌�″埌浣跨敤绱㈠紩灏嗛渶瑕�MySQL璁块棶琛ㄤ腑鐨勫ぇ閮ㄥ垎琛屾椂銆�(鍦ㄨ繖绉嶆儏鍐典笅锛岃〃鎵�鎻忓彲鑳戒細鏇村揩浜涳紝鍥犱负闇�瑕佺殑鎼滅储瑕佸皯锛�銆�鐒惰�岋紝濡傛灉姝ょ被鏌ヨ��浣跨敤LIMIT鍙�鎼滅储閮ㄥ垎琛岋紝MySQL鍒欎娇鐢ㄧ储寮曪紝鍥犱负瀹冨彲浠ユ洿蹇�鍦版壘鍒板嚑琛屽苟鍦ㄧ粨鏋滀腑杩斿洖銆�

Hash绱㈠紩杩樻湁涓�浜涘叾瀹冪壒寰侊細

路         瀹冧滑鍙�鐢ㄤ簬浣跨敤=鎴�<=>鎿嶄綔绗︾殑绛夊紡姣旇緝(浣�寰堝揩)銆傚畠浠�鐢ㄤ簬姣旇緝 鎿嶄綔绗︼紝渚嬪�傚彂鐜拌寖鍥村�肩殑<銆�

路         浼樺寲鍣ㄤ笉鑳戒娇鐢�hash绱㈠紩鏉ュ姞閫�ORDER BY鎿嶄綔銆�(璇ョ被绱㈠紩涓嶈兘鐢ㄦ潵鎸夐『搴忔悳绱�涓嬩竴涓�鏉＄洰锛�銆�

路         MySQL涓嶈兘纭�瀹氬湪涓や釜鍊间箣闂村ぇ绾︽湁澶氬皯琛�(杩欒��鑼冨洿浼樺寲鍣ㄧ敤鏉ョ‘瀹氫娇鐢ㄥ摢涓�绱㈠紩)銆傚�傛灉浣犲皢涓�涓�MyISAM琛ㄦ敼涓�hash-绱㈠紩鐨�MEMORY琛�锛屼細褰卞搷涓�浜涙煡璇�銆�

路         鍙�鑳戒娇鐢ㄦ暣涓�鍏抽敭瀛楁潵鎼滅储涓�琛屻��(鐢�B-鏍戠储寮曪紝浠讳綍鍏抽敭瀛楃殑鏈�宸﹂潰鐨勫墠缂�鍙�鐢ㄦ潵鎵惧埌琛岋級銆�

涓轰簡浣跨‖鐩�I/O鏈�灏忓寲锛�MyISAM瀛樺偍寮曟搸浣跨敤涓�涓�琚�璁稿�氭暟鎹�搴撶�＄悊绯荤粺浣跨敤鐨勭瓥鐣ャ�傚畠浣跨敤涓�涓�缂撳瓨鏈哄埗灏嗙粡甯歌�块棶鐨勮〃閿佸湪鍐呭瓨涓�锛�

路         瀵逛簬绱㈠紩鍧楋紝缁存姢涓�涓�绉颁箣涓�閿�楂橀��缂撳啿(鎴�閿�楂橀�熺紦鍐插尯)鐨勭壒娈婄粨鏋勩�傝�ョ粨鏋勫寘鍚�澶ч噺鍧楃紦瀛樺尯锛屽叾涓�鏀剧疆浜嗘渶甯哥敤鐨勭储寮曞潡銆�

路         瀵逛簬鏁版嵁鍧楋紝MySQL涓嶄娇鐢ㄧ壒娈婄紦瀛樸�傝�屼娇鐢ㄥ師鐢熺殑鎿嶄綔绯荤粺鏂囦欢绯荤粺鐨勭紦瀛樸��

鏈�鑺傞�栧厛鎻忚堪浜�MyISAM閿�楂橀�熺紦鍐茬殑鍩烘湰鎿嶄綔銆傜劧鍚庤�ㄨ�轰簡鎻愰珮 閿�楂橀�熺紦鍐叉�ц兘骞朵娇浣犳洿濂藉湴鎺у埗缂撳瓨鎿嶄綔鐨勬渶鏂扮殑鏇存敼锛�

路         澶氫釜绾跨▼鍙�浠ュ苟琛岃�块棶缂撳瓨銆�

路         鍙�浠ヨ�剧疆澶氫釜閿�楂橀�熺紦鍐诧紝骞跺皢琛ㄧ储寮曟寚瀹氱粰鍏蜂綋缂撳瓨銆�

鍙�浠ヤ娇鐢�key_buffer_size绯荤粺鍙橀噺鎺у埗 閿�楂橀�熺紦鍐茬殑澶у皬銆傚�傛灉璇ュ彉閲忚�剧疆涓洪浂锛屼笉浣跨敤閿�楂橀�熺紦鍐层�傚�傛灉key_buffer_size鍊煎お灏忎笉鑳藉垎閰嶆渶灏忔暟閲忕殑鍧楃紦瀛樺尯(8)锛屼篃涓嶄娇鐢� 閿�楂橀�熺紦鍐层��

濡傛灉閿�楂橀�熺紦鍐蹭笉宸ヤ綔锛屽彧浣跨敤鎿嶄綔绯荤粺鎻愪緵鐨勫師鐢熸枃浠剁郴缁熺紦瀛樺尯璁块棶绱㈠紩鏂囦欢銆�(鎹㈠彞璇濊�达紝浣跨敤涓庤〃鏁版嵁鍧楃浉鍚岀殑绛栫暐琛ㄦ潵璁块棶绱㈠紩鍧楋級銆�

绱㈠紩鍧楁槸涓�涓�杩炵画鐨勮�块棶MyISAM绱㈠紩鏂囦欢鐨勫崟浣嶃�傞�氬父涓�涓�绱㈠紩鍧楃殑澶у皬绛変簬绱㈠紩B-鏍戣妭鐐圭殑澶у皬銆�(鍦ㄧ‖鐩樹笂浣跨敤B-鏍戞暟鎹�缁撴瀯琛ㄧず绱㈠紩銆傛爲搴曢儴鐨勮妭鐐逛负鍙跺瓙鑺傜偣銆傚彾瀛愯妭鐐逛笂闈㈢殑鑺傜偣涓洪潪鍙跺瓙鑺傜偣锛�銆�

閿�楂橀�熺紦鍐茬粨鏋勪腑鐨勬墍鏈夊潡缂撳瓨鍖哄ぇ灏忕浉鍚屻�傝�ュぇ灏忓彲浠ョ瓑浜庛�佸ぇ浜庢垨灏忎簬琛ㄧ储寮曞潡鐨勫ぇ灏忋�傞�氬父杩欎袱涓�鍊间腑鐨勪竴涓�鏄�鍙︿竴涓�鐨勫嚑鍊嶃��

褰撳繀椤昏�块棶琛ㄧ储寮曞潡涓�鐨勬暟鎹�鏃讹紝鏈嶅姟鍣ㄩ�栧厛妫�鏌ユ槸鍚﹀畠鍙�浠ョ敤浜庨敭楂橀�熺紦鍐蹭腑鐨勬煇浜涘潡缂撳瓨鍖恒�傚�傛灉閫傜敤锛屾湇鍔″櫒璁块棶閿�楂橀�熺紦鍐蹭腑鐨勬暟鎹�鑰屼笉鏄�纭�鐩樹笂鐨勬暟鎹�銆備篃灏辨槸璇达紝浠庣紦瀛樿�诲彇鎴栧啓鍏ョ紦瀛橈紝鑰屼笉鏄�浠庣‖鐩樿�诲啓銆傚惁鍒欙紝鏈嶅姟鍣ㄩ�夋嫨涓�涓�鍖呭惈涓�涓�涓嶅悓鐨勮〃绱㈠紩鍧楃殑缂撳瓨鍧楃紦瀛樺尯锛屽苟鐢ㄩ渶瑕佺殑琛ㄧ储寮曞潡鐨勬嫹璐濇浛鎹㈤偅閲岀殑鏁版嵁銆備竴鏃︽柊鐨勭储寮曞潡浣嶄簬缂撳瓨涓�锛屽彲浠ヨ�块棶绱㈠紩鏁版嵁銆�

濡傛灉鐢ㄤ簬鏇挎崲鐨勫潡宸茬粡琚�淇�鏀逛簡锛屽潡琚�瑙嗕负鈥滆剰浜嗏�濄�傚湪杩欑�嶆儏鍐典笅锛屽湪鏇挎崲鍓嶏紝鍏跺唴瀹硅��鍒锋柊鍒板畠鏉ヨ嚜鐨勮〃绱㈠紩銆�

閫氬父鏈嶅姟鍣ㄩ伒浠�LRU(鏈�杩戞渶灏戜娇鐢�)绛栫暐锛氬綋閫夋嫨涓�涓�鍧楃敤浜庢浛鎹㈡椂锛屽畠閫夋嫨鏈�杩戞渶灏戜娇鐢ㄧ殑绱㈠紩鍧椼�備负浜嗕娇璇ラ�夋嫨鏇村�规槗锛� 閿�楂橀�熺紦鍐叉ā鍧楃淮鎶ゆ墍鏈変娇鐢ㄧ殑鍧楃殑涓撻棬闃熷垪(LRU閾�)銆傚綋璁块棶鍧楁椂锛屽畠琚�鏀惧埌闃熷垪鏈�鍚庛�傚綋鍧楅渶瑕佹浛鎹㈡椂锛岄槦鍒楀紑澶寸殑鍧楁槸鏈�杩戞渶灏戜娇鐢ㄧ殑鍧楋紝骞舵垚涓虹��1涓�鍊欓�夎�呫��

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;

+---------+--------------------+----------+----------+

| Table   | Op                 | Msg_type | Msg_text |

+---------+--------------------+----------+----------+

| test.t1 | assign_to_keycache | status   | OK       |

| test.t2 | assign_to_keycache | status   | OK       |

| test.t3 | assign_to_keycache | status   | OK       |

+---------+--------------------+----------+----------+

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

mysql> SET GLOBAL keycache1.key_buffer_size=0;

mysql> set global key_buffer_size = 0;

mysql> show variables like 'key_buffer_size';

+-----------------+---------+

| Variable_name   | Value   |

+-----------------+---------+

| key_buffer_size | 8384512 |

+-----------------+---------+

key_buffer_size = 4G

hot_cache.key_buffer_size = 2G

cold_cache.key_buffer_size = 2G

init_file=/path/to/data-directory/mysqld_init.sql

CACHE INDEX a.t1, a.t2, b.t3 IN hot_cache

CACHE INDEX a.t4, b.t5, b.t6 IN cold_cache

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;

+---------+--------------+----------+----------+

| Table   | Op           | Msg_type | Msg_text |

+---------+--------------+----------+----------+

| test.t1 | preload_keys | status   | OK       |

| test.t2 | preload_keys | status   | OK       |

+---------+--------------+----------+----------+

mysql> SET GLOBAL cold_cache銆�key_buffer_size=4*1024*1024锛�

瀛樺偍寮曟搸鎼滈泦浼樺寲鍣ㄤ娇鐢ㄧ殑琛ㄧ殑缁熻�′俊鎭�銆傝〃缁熻�″熀浜庢暟鏁板�肩粍锛屽叾涓�鏁版暟鍊肩粍鏄�涓�绯诲垪鏈夌浉鍚岀殑鍏抽敭瀛楀墠缂�鍊肩殑璁板綍銆傚�逛簬浼樺寲鍣�锛岄噸瑕佺殑缁熻�″嵆涓烘暟鏁板�肩粍鐨勫钩鍧囧ぇ灏忋��

MySQL鐢ㄤ笅杩版柟寮忎娇鐢ㄥ钩鍧囨暟鏁板�肩粍锛�

路         浼拌�″繀椤讳负姣忎釜ref璁块棶璇诲彇澶氬皯琛�

路         浼拌�￠儴鍒嗚仈鎺ュ皢浜х敓澶氬皯琛岋紱涔熷氨鏄�璇达紝涓嬭堪褰㈠紡鐨勬搷浣滃皢浜х敓鐨勮�屾暟锛�

路                  (...) JOIN tbl_name ON tbl_name.key = expr

闅忕潃绱㈠紩鐨勫钩鍧囨暟鏁板�肩粍澶у皬鐨勫�炲姞锛岀储寮曞皢鏇存病鏈夌敤锛屽洜涓烘瘡涓�鏌ユ壘鐨勫钩鍧囪�屾暟澧炲姞锛氫负浜嗚�╃储寮曟湁鍒╀簬浼樺寲鐩�鐨勶紝鏈�濂芥槸姣忎釜绱㈠紩鍊煎�瑰簲琛ㄥ唴鐨勫皯閲忚�屾暟銆傚綋鏌愪釜缁欏畾鐨勭储寮曞�间骇鐢熻緝澶氳�屾椂锛岀储寮曟洿鍔犳病鏈夌敤锛�MySQL鏇翠笉鍙�鑳戒娇鐢ㄥ畠銆�

骞冲潎鏁版暟鍊肩粍澶у皬涓庤〃鐨勯泦鐨勫娍鐩稿叧锛屽嵆鏁版暟鍊肩粍鐨勬暟鐩�銆�SHOW INDEX璇�鍙ユ樉绀洪泦鐨勫娍鍊硷紙鍩轰簬N/S锛夛紝鍏朵腑N鏄�琛ㄥ唴鐨勮�板綍鏁帮紝S鏄�骞冲潎鏁版暟鍊肩粍澶у皬銆傝�ユ瘮渚嬩骇鐢熻〃鍐呮暟鏁板�肩粍鐨勫ぇ绾︽暟銆�

瀵逛簬鍩轰簬<=>姣旇緝 鎿嶄綔绗︾殑鑱旀帴锛�NULL骞朵笉瑙嗕负涓庝换浣曞叾瀹冨�间笉鍚岋細NULL <=> NULL锛屾�ｅ�傚�逛簬鍏跺畠N 锛�N <=> N銆�

鐒惰�岋紝瀵逛簬鍩轰簬=鎿嶄綔绗︾殑鑱旀帴锛�NULL涓庨潪NULL鍊间笉鍚岋細褰�expr1鎴�expr2(鎴栦袱鑰�)涓�NULL鏃讹紝expr1 = expr2涓嶄负鐪熴�傝繖鏍峰奖鍝嶆瘮杈冨舰寮�tbl_name.key = expr鐨�ref璁块棶锛氬�傛灉expr褰撳墠鐨勫�间负NULL锛�MySQL涓嶄細璁块棶琛�锛屽洜涓烘瘮杈冧笉鑳戒负鐪熴��

瀵逛簬=姣旇緝锛岃〃鍐呮湁澶氬皯NULL鍊煎苟涓嶉噸瑕併�備负浜嗕紭鍖栫洰鐨勶紝鐩稿叧鍊间负闈�NULL鏁板�肩粍鐨勫钩鍧囧ぇ灏忋�傜劧鑰岋紝MySQL鐩�鍓嶄笉鍏佽�告悳闆嗘垨浣跨敤璇ュ钩鍧囧ぇ灏忋��

瀵逛簬MyISAM琛�锛屼綘鍙�浠ヤ娇鐢�myisam_stats_method绯荤粺鍙橀噺閮ㄥ垎鎺у埗琛ㄧ粺璁′俊鎭�鐨勬悳闆嗐�傝�ュ彉閲忔湁涓や釜鍙�鑳界殑涓嶅悓鍊硷紝濡備笅鎵�绀猴細

路         褰�myisam_stats_method涓�nulls_equal鏃讹紝鎵�鏈�NULL鍊艰��瑙嗕负鐩哥瓑鐨�(涔熷氨鏄�璇达紝瀹冧滑閮藉舰鎴愪竴涓�鏁板�肩粍)銆�

濡傛灉NULL鏁板�肩粍澶у皬杩滃ぇ浜庡钩鍧囬潪NULL鏁板�肩粍澶у皬锛岃�ユ柟娉曞悜涓婂�炬枩骞冲潎鏁版暟鍊肩粍澶у皬銆傝繖鏍蜂娇绱㈠紩瀵逛簬浼樺寲鍣ㄦ潵璇存瘮瀹冨疄闄呬负鏌ユ壘闈�NULL鍊肩殑鑱旀帴鏇村姞娌℃湁鐢ㄣ�傜粨鏋滄槸锛�nulls_equal鏂规硶浼氫娇浼樺寲鍣ㄨ繘琛�ref璁块棶鏃舵湰搴斾娇鐢ㄧ储寮曡�屾病鏈変娇鐢ㄣ��

路         褰�myisam_stats_method涓�nulls_unequal鏃讹紝NULL鍊间笉瑙嗕负鐩稿悓銆傜浉鍙嶏紝姣忎釜NULL鍊煎舰鎴愪竴涓�鍗曠嫭鐨勬暟鍊肩粍锛屽ぇ灏忎负1銆�

濡傛灉浣犳湁璁稿��NULL鍊硷紝璇ユ柟娉曞悜涓嬪�炬枩骞冲潎鏁版暟鍊肩粍澶у皬銆傚�傛灉骞冲潎闈�NULL鏁板�肩粍杈冨ぇ锛岀粺璁″ぇ灏忎负1鐨勬瘡涓�缁勭殑NULL鍊间細浣夸紭鍖栧櫒杩囬珮浼拌�℃煡鎵鹃潪NULL鍊肩殑鑱旀帴鐨勭储寮曞�笺�傜粨鏋滄槸锛屽綋鍏跺畠鏂规硶浼氭洿濂芥椂锛�nulls_unequal鏂规硶浼氫娇浼樺寲鍣ㄤ负ref鏌ユ壘浣跨敤璇ョ储寮曘��

濡傛灉浣犺�佷娇鐢ㄨ�稿�氫娇鐢�<=>鑰屼笉鏄�=鐨勮仈鎺ワ紝鍦ㄦ瘮杈冭繃绋嬩腑NULL鍊煎苟涓嶇壒娈婏紝涓�涓�NULL绛変簬鍙︿竴涓�NULL銆傚湪杩欑�嶆儏鍐典笅锛�nulls_equal鏄�鍚堥�傜殑缁熻�℃柟娉曘��

myisam_stats_method绯荤粺鍙橀噺鏈夊叏灞�鍜屼細璇濆�笺�傝�剧疆鍏ㄥ眬鍊间細褰卞搷MyISAM 涓烘墍鏈�MyISAM琛ㄧ殑缁熻�＄殑鎼滈泦銆傝�剧疆浼氳瘽鍊煎彧褰卞搷褰撳墠瀹㈡埛杩炴帴鐨勭粺璁＄殑鎼滈泦銆傝繖璇存槑浣犲彲浠ュ己鍒剁敤缁欏畾鐨勬柟娉曢噸鏂扮敓鎴愯〃鐨勭粺璁＄殑鎼滈泦锛岃�屼笉闇�瑕佸洜涓鸿�剧疆myisam_stats_method鐨勪細璇濆�艰�屽奖鍝嶅叾瀹冨�㈡埛銆�

鍙�浠ヤ娇鐢ㄤ笅闈�浠讳竴鏂规硶鏉ラ噸鏂扮敓鎴愯〃鐨勭粺璁′俊鎭�锛�

路         璁剧疆myisam_stats_method锛岀劧鍚庢墽琛�CHECK TABLE璇�鍙�

路         鎵ц��myisamchk --stats_method=method_name --analyze

路         鏇存敼琛�锛屼娇鍏剁粺璁′俊鎭�涓嶄负鏈�鏂�(渚嬪�傦紝鎻掑叆涓�琛岀劧鍚庡垹闄ゅ畠)锛岀劧鍚庤�剧疆myisam_stats_method骞舵墽琛�ANALYZE TABLE璇�鍙�

浣跨敤myisam_stats_method鐨勪竴浜涜�﹀憡锛�

浣犲彲浠ュ己鍒舵樉寮忔悳闆嗚〃鐨勭粺璁′俊鎭�锛屽�備笂鎵�杩般�傜劧鑰岋紝MySQL涔熷彲浠ヨ嚜鍔ㄦ悳闆嗙粺璁′俊鎭�銆備緥濡傦紝濡傛灉鍦ㄤ负琛ㄦ墽琛岃��鍙ョ殑杩囩▼涓�锛屼竴浜涜��鍙ヤ慨鏀逛簡琛�锛�MySQL鍙�浠ユ悳闆嗙粺璁′俊鎭�銆�(渚嬪�傦紝澶ф壒鎻掑叆鎴栧垹闄わ紝鎴栬�呮墽琛�ALTER TABLE璇�鍙ユ椂鍙�鑳藉彂鐢燂級銆�濡傛灉鍙戠敓锛屼娇鐢�myisam_stats_method姝ゆ椂鎵�鏈夌殑鍊兼悳闆嗙粺璁′俊鎭�銆傝繖鏍凤紝濡傛灉浣犱娇鐢ㄤ竴涓�鏂规硶鎼滈泦缁熻�′俊鎭�锛屼絾褰撳悗闈㈣嚜鍔ㄦ悳闆嗕竴涓�琛ㄧ殑缁熻�′俊鎭�鏃�myisam_stats_method琚�璁剧疆涓哄彟涓�涓�鏂规硶锛屽皢浣跨敤鍏跺畠鏂规硶銆�

瀵逛簬缁欏畾鐨�MyISAM琛�锛岃繕涓嶈兘璇村嚭浣跨敤鍝�涓�鏂规硶鏉ヤ骇鐢熺粺璁′俊鎭�銆�

myisam_stats_method鍙�閫傚悎MyISAM琛ㄣ�傚叾瀹冨瓨鍌ㄥ紩鎿庡彧鏈変竴涓�鏂规硶鏉ユ悳闆嗚〃鐨勭粺璁′俊鎭�銆傞�氬父瀹冩帴杩戜簬nulls_equal鏂规硶銆�

褰撹繍琛�mysqladmin status鏃讹紝灏嗙湅瑙佽薄杩欐牱鐨勪竴浜涗笢瑗匡細

Uptime: 426 Running threads: 1 Questions: 11082

Reloads: 1 Open tables: 12

濡傛灉浣犱粎鏈�6涓�琛�锛�Open tables鍊间负12鍙�鑳芥湁鐐逛护浜哄洶鎯戙��

MySQL鏄�澶氱嚎绋嬬殑锛屽洜姝よ�稿�氬�㈡埛鍙�浠ュ悓鏃跺湪鍚屼竴涓�琛ㄤ笂杩涜�屾煡璇�銆備负浜嗕娇澶氫釜瀹㈡埛绾跨▼鍦ㄥ悓涓�涓�琛ㄤ笂鏈変笉鍚岀姸鎬佺殑闂�棰樺噺鍒版渶灏忥紝琛ㄨ��姣忎釜骞跺彂杩涚▼鐙�绔嬪湴鎵撳紑銆傝繖鏍烽渶瑕侀�濆�栫殑鍐呭瓨浣嗕竴鑸�浼氭彁楂樻�ц兘銆傚�逛簬MyISAM琛�锛屾暟鎹�鏂囦欢闇�瑕佷负姣忎釜鎵撳紑琛ㄧ殑瀹㈡埛鎻愪緵涓�涓�棰濆�栫殑鏂囦欢鎻忚堪绗︺��(绱㈠紩鏂囦欢鎻忚堪绗﹀湪鎵�鏈夌嚎绋嬩箣闂村叡浜�)銆�

涓嬩竴鑺備腑鎻愪緵浜嗚�ヤ富棰樼殑鏇村�氱殑淇℃伅銆傚弬瑙�7.4.9鑺傦紝鈥淢ySQL濡備綍鎵撳紑鍜屽叧闂�琛ㄢ��銆�

table_cache銆�max_connections鍜�max_tmp_tables绯荤粺鍙橀噺褰卞搷鏈嶅姟鍣ㄤ繚鎸佹墦寮�鐨勬枃浠剁殑鏈�澶ф暟閲忋�傚�傛灉浣犲�炲姞杩欎簺鍊煎叾涓�鐨勪竴涓�鎴栦袱涓�锛屼細閬囧埌鎿嶄綔绯荤粺涓烘瘡涓�杩涚▼鎵撳紑鏂囦欢鎻忚堪绗︾殑鏁伴噺寮哄姞鐨勯檺鍒躲�傝�稿�氭搷浣滅郴缁熷厑璁镐綘澧炲姞鎵撳紑鐨勬枃浠剁殑闄愬埗锛屽敖绠¤�ユ柟娉曢殢绯荤粺鐨勪笉鍚岃�屼笉鍚屻�傛煡闃呮搷浣滅郴缁熸枃妗ｄ互纭�瀹氭槸鍚﹀彲浠ュ�炲姞闄愬埗浠ュ強濡備綍鎿嶄綔銆�

table_cache涓�max_connections鏈夊叧銆備緥濡傦紝瀵逛簬200涓�骞惰�岃繍琛岀殑杩炴帴锛屽簲璇ヨ�╄〃鐨勭紦瀛樿嚦灏戞湁200 * N锛岃繖閲�N鏄�鍙�浠ユ墽琛岀殑鏌ヨ�㈢殑涓�涓�鑱旀帴涓�琛ㄧ殑鏈�澶ф暟閲忋�傝繕闇�瑕佷负涓存椂琛ㄥ拰鏂囦欢淇濈暀涓�浜涢�濆�栫殑鏂囦欢鎻忚堪绗︺��

纭�淇濇搷浣滅郴缁熷彲浠ュ�勭悊table_cache璁剧疆鎵�鎸囩殑鎵撳紑鐨勬枃浠舵弿杩扮�︾殑鏁扮洰銆傚�傛灉table_cacheis璁惧緱澶�楂橈紝MySQL鍙�鑳戒负鏂囦欢鎻忚堪绗﹁�楀敖璧勬簮骞舵嫆缁濊繛鎺ワ紝涓嶈兘鎵ц�屾煡璇�锛屽苟涓斿緢涓嶅彲闈犮�傝繕蹇呴』鑰冭檻鍒�MyISAM瀛樺偍寮曟搸闇�瑕佷负姣忎釜鎵撳紑鐨勮〃鎻愪緵涓や釜鏂囦欢鎻忚堪绗︺�傚彲浠ュ湪mysqld_safe涓�浣跨敤--open-files-limit鍚�鍔ㄩ�夐」鏉ュ�炲姞MySQL閫傜敤鐨勬枃浠舵弿杩扮�︾殑鏁伴噺銆傚弬瑙�A.2.17鑺傦紝鈥滄枃浠舵湭鎵惧埌鈥�銆�

鎵撳紑琛ㄧ殑缂撳瓨鍙�浠ヤ繚鎸佸湪table_cache鏉°�� 榛樿�や负64锛涘彲浠ョ敤mysqld鐨�--table_cache閫夐」鏉ユ洿鏀广�傝�锋敞鎰� MySQL鍙�浠ヤ复鏃舵墦寮�鏇村�氱殑 琛ㄤ互鎵ц�屾煡璇�銆�

鍦ㄤ笅闈㈢殑鏉′欢涓嬶紝鏈�浣跨敤鐨勮〃灏嗚��鍏抽棴骞朵粠琛ㄧ紦瀛樹腑绉诲嚭锛�

路         褰撶紦瀛樻弧浜嗗苟涓斾竴涓�绾跨▼璇曞浘鎵撳紑涓�涓�涓嶅湪缂撳瓨涓�鐨勮〃鏃躲��

路         褰撶紦瀛樺寘鍚�瓒呰繃table_cache涓�鏉＄洰锛屽苟涓旂紦瀛樹腑鐨勮〃涓嶅啀琚�浠讳綍绾跨▼浣跨敤銆�

路         褰撹〃鍒锋柊鎿嶄綔鍙戠敓銆傚綋鎵ц��FLUSH TABLES璇�鍙ユ垨鎵ц��mysqladmin flush-tables鎴�mysqladmin refresh鍛戒护鏃朵細鍙戠敓銆�

褰撹〃缂撳瓨婊℃椂锛屾湇鍔″櫒浣跨敤涓嬪垪杩囩▼鎵惧埌涓�涓�缂撳瓨鍏ュ彛鏉ヤ娇鐢�锛�

路         褰撳墠鏈�浣跨敤鐨勮〃琚�閲婃斁锛屼互鏈�杩戞渶灏戜娇鐢ㄩ『搴忋��

路         濡傛灉缂撳瓨婊′簡骞朵笖娌℃湁琛ㄥ彲浠ラ噴鏀撅紝浣嗘槸涓�涓�鏂拌〃闇�瑕佹墦寮�锛岀紦瀛樺繀椤讳复鏃惰��鎵╁ぇ銆�

濡傛灉缂撳瓨澶勪簬涓�涓�涓存椂鎵╁ぇ鐘舵�佸苟涓斾竴涓�琛ㄤ粠鍦ㄧ敤鍙樹负涓嶅湪鐢ㄧ姸鎬侊紝瀹冭��鍏抽棴骞朵粠缂撳瓨涓�閲婃斁銆�

瀵规瘡涓�骞跺彂璁块棶鎵撳紑涓�涓�琛ㄣ�傝繖鎰忓懗鐫�锛屽�傛灉2涓�绾跨▼璁块棶鍚屼竴涓�琛ㄦ垨鍦ㄥ悓涓�涓�鏌ヨ��涓�璁块棶琛ㄤ袱娆�(渚嬪�傦紝灏嗚〃杩炴帴涓鸿嚜韬�鏃�)锛岃〃闇�瑕佽��鎵撳紑涓ゆ�°�傛瘡涓�骞惰�岀殑鎵撳紑瑕佹眰鍦ㄨ〃缂撳瓨涓�鏈変竴涓�鏉＄洰銆備换浣曡〃鐨勭��涓�娆℃墦寮�鍗�2涓�鏂囦欢鎻忚堪绗︼細涓�涓�鐢ㄤ簬鏁版嵁鏂囦欢鍙︿竴涓�鐢ㄤ簬绱㈠紩鏂囦欢銆傝〃鐨勬瘡涓�娆￠�濆�栦娇鐢ㄤ粎鍗犱竴涓�鏁版嵁鏂囦欢鐨勬枃浠舵弿杩扮�︺�傜储寮曟枃浠舵弿杩扮�﹀湪鎵�鏈夌嚎绋嬩箣闂村叡浜�銆�

濡傛灉浣犳�ｇ敤HANDLER tbl_name OPEN璇�鍙ユ墦寮�涓�涓�琛�锛屽皢涓鸿�ョ嚎绋嬩笓闂ㄥ垎閰嶄竴涓�琛ㄣ�傝�ヨ〃涓嶈��鍏跺畠绾跨▼鍏变韩锛屽彧鏈夌嚎绋嬭皟鐢�HANDLER tbl_name CLOSE鎴栫嚎绋嬬粓姝㈠悗鎵嶈��鍏抽棴銆傝〃鍏抽棴鍚庯紝琚�鎷夊洖琛ㄧ紦瀛樹腑(濡傛灉缂撳瓨涓嶆弧)銆傚弬瑙�13.2.3鑺傦紝鈥淗ANDLER璇�娉曗��銆�

鍙�浠ラ�氳繃妫�鏌�mysqld鐨勭姸鎬佸彉閲�Opened_tables纭�瀹氳〃缂撳瓨鏄�鍚﹀お灏忥細

mysql> SHOW STATUS LIKE 'Opened_tables';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Opened_tables | 2741  |

+---------------+-------+

濡傛灉鍊煎緢澶э紝鍗充娇浣犳病鏈夊彂鍑鸿�稿��FLUSH TABLES璇�鍙ワ紝涔熷簲澧炲姞琛ㄧ紦瀛樼殑澶у皬銆傚弬瑙�5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺鈥�鍜�5.3.4鑺傦紝鈥滄湇鍔″櫒鐘舵�佸彉閲忊��銆�

鎴戜滑浠庣郴缁熺骇鍥犵礌寮�濮嬶紝鍥犱负蹇呴』灏芥棭鍦拌繘琛岄儴鍒嗗喅绛栦互鑾峰緱杈冨ぇ鎬ц兘銆傚湪鍏跺畠鎯呭喌涓嬶紝蹇�閫熸祻瑙堣�ヨ妭灏辫冻澶熶簡銆備絾鏄�锛屼簡瑙ｄ竴涓嬫洿鏀硅�ュ眰娆＄殑鍙傛暟鑳藉�熻幏寰楀�氬皯鎬ц兘鎻愰珮鏄�寰堟湁鎰忎箟鐨勩��

浣跨敤鐨勬搷浣滅郴缁熷緢閲嶈�併�備负浜嗘洿濂藉湴浣跨敤澶�CPU鏈哄櫒锛屽簲浣跨敤Solaris(鍥犱负鍏剁嚎绋嬪伐浣滃緱寰堝ソ)鎴�Linux(鍥犱负2.4鍜屼互鍚庣殑鍐呮牳鏈夊緢濂界殑SMP鏀�鎸�)銆傝�锋敞鎰忛粯璁ゆ儏鍐垫棫鐨�Linux鍐呮牳鏈変竴涓�2GB鐨勬枃浠跺ぇ灏忛檺鍒躲�傚�傛灉鏈夎繖鏍风殑涓�涓�鍐呮牳骞朵笖闇�瑕佹枃浠跺ぇ浜�2GB锛屽簲寰楀埌ext2鏂囦欢绯荤粺鐨勫ぇ鏂囦欢鏀�鎸�(LFS)琛ヤ竵銆傚叾瀹冩枃浠剁郴缁熶緥濡�ReiserFS鍜�XFS娌℃湁姝�2GB闄愬埗銆�

灏�MySQL鐢ㄤ簬鐢熶骇鍓嶏紝鎴戜滑寤鸿��浣犲湪鎯崇敤鐨勫钩鍙颁笂瀵瑰畠杩涜�屾祴璇曘��

鍏跺畠鎶�宸э細

路         濡傛灉鏈夎冻澶熺殑RAM锛屽彲浠ョЩ闄ゆ墍鏈夌殑浜ゆ崲璁惧�囥�傛湁浜涙搷浣滅郴缁熷嵆浣挎湁鑷�鐢卞唴瀛樹篃浣跨敤浜ゆ崲璁惧�囥��

路         浣跨敤--skip-external-locking MySQL閫夐」浠ラ伩鍏嶅�栭儴閿佸畾銆傝�ラ�夐」榛樿�ゅ紑鍚�銆�

璇锋敞鎰忓彧瑕佷綘鍙�杩愯�屼竴涓�鏈嶅姟鍣�锛�--skip-external-locking閫夐」涓嶅奖鍝�MySQL鐨勫姛鑳姐�傚彧瑕佽�颁綇杩愯��myisamchk涔嬪墠鍏抽棴鏈嶅姟鍣�(鎴栭攣瀹氬苟鍒锋柊鐩稿叧琛�)銆傚湪涓�浜涚郴缁熶笂璇ラ�夐」鏄�寮哄埗鐨勶紝鍥犱负鍦ㄤ换浣曟儏鍐典笅澶栭儴閿佸畾鍧囦笉宸ヤ綔銆�

涓嶈兘浣跨敤--skip-external-locking鐨勫敮涓�鎯呭喌鏄�瀵圭浉鍚岀殑鏁版嵁杩愯�屽�氫釜MySQL鏈嶅姟鍣�(闈炲�㈡埛)鐨勬儏鍐碉紝鎴栬�呭�傛灉娌℃湁浜嬪厛鍛婅瘔鏈嶅姟鍣ㄥ埛鏂板苟閿佸畾涓�涓�琛ㄥ嵆杩愯��myisamchk鏉ユ��鏌�(闈炰慨澶�)璇ヨ〃銆傝�锋敞鎰忎竴鑸�涓嶅缓璁�浣跨敤澶氫釜MySQL鏈嶅姟鍣ㄦ潵骞惰�岃�块棶鐩稿悓鐨勬暟鎹�锛岄櫎浜嗕娇鐢�MySQL Cluster鏃躲��

鍗充娇浣跨敤--skip-external-locking锛屼粛鐒跺彲浠ヤ娇鐢�LOCK TABLES鍜�UNLOCK TABLES銆�

鍙�浠ョ敤杩欎釜鍛戒护寰楀埌mysqld鏈嶅姟鍣� 榛樿�ょ紦瀛樺尯鐨勫ぇ灏忥細

shell> mysqld --verbose --help

杩欎釜鍛戒护鐢熸垚鎵�鏈�mysqld閫夐」鍜屽彲閰嶇疆鍙橀噺鐨勫垪琛ㄣ�傝緭鍑哄寘鎷� 榛樿�ゅ�煎苟涓旂湅涓婂幓璞¤繖鏍凤細

help                              TRUE

abort-slave-event-count           0

allow-suspicious-udfs             FALSE

auto-increment-increment          1

auto-increment-offset             1

automatic-sp-privileges           TRUE

basedir                           /home/jon/bin/mysql/

bdb                               FALSE

bind-address                      (No default value)

character-set-client-handshake    TRUE

character-set-server              latin1

character-sets-dir                /home/jon/bin/mysql/share/mysql/charsets/

chroot                            (No default value)

collation-server                  latin1_swedish_ci

completion-type                   0

concurrent-insert                 1

console                           FALSE

datadir                           /home/jon/bin/mysql/var/

default-character-set             latin1

default-collation                 latin1_swedish_ci

default-time-zone                 (No default value)

disconnect-slave-event-count      0

enable-locking                    FALSE

enable-pstack                     FALSE

engine-condition-pushdown         FALSE

external-locking                  FALSE

gdb                               FALSE

large-pages                       FALSE

init-connect                      (No default value)

init-file                         (No default value)

init-slave                        (No default value)

innodb                            TRUE

innodb_checksums                  TRUE

innodb_data_home_dir              (No default value)

innodb_doublewrite                TRUE

innodb_fast_shutdown              1

innodb_file_per_table             FALSE

innodb_flush_log_at_trx_commit    1

innodb_flush_method               (No default value)

innodb_locks_unsafe_for_binlog    FALSE

innodb_log_arch_dir               (No default value)

innodb_log_group_home_dir         (No default value)

innodb_max_dirty_pages_pct        90

innodb_max_purge_lag              0

innodb_status_file                FALSE

innodb_table_locks                TRUE

innodb_support_xa                 TRUE

isam                              FALSE

language                          /home/jon/bin/mysql/share/mysql/english

local-infile                      TRUE

log                               /home/jon/bin/mysql/var/master1.log

log-bin                           /home/jon/bin/mysql/var/master1

log-bin-index                     (No default value)

log-bin-trust-routine-creators    FALSE

log-error                         /home/jon/bin/mysql/var/master1.err

log-isam                          myisam.log

log-queries-not-using-indexes     FALSE

log-short-format                  FALSE

log-slave-updates                 FALSE

log-slow-admin-statements         FALSE

log-slow-queries                  (No default value)

log-tc                            tc.log

log-tc-size                       24576

log-update                        (No default value)

log-warnings                      1

low-priority-updates              FALSE

master-connect-retry              60

master-host                       (No default value)

master-info-file                  master.info

master-password                   (No default value)

master-port                       3306

master-retry-count                86400

master-ssl                        FALSE

master-ssl-ca                     (No default value)

master-ssl-capath                 (No default value)

master-ssl-cert                   (No default value)

master-ssl-cipher                 (No default value)

master-ssl-key                    (No default value)

master-user                       test

max-binlog-dump-events            0

memlock                           FALSE

myisam-recover                    OFF

ndbcluster                        FALSE

ndb-connectstring                 (No default value)

ndb-mgmd-host                     (No default value)

ndb-nodeid                        0

ndb-autoincrement-prefetch-sz     32

ndb-distibution                   KEYHASH

ndb-force-send                    TRUE

ndb_force_send                    TRUE

ndb-use-exact-count               TRUE

ndb_use_exact_count               TRUE

ndb-shm                           FALSE

ndb-optimized-node-selection      TRUE

ndb-cache-check-time              0

ndb-index-stat-enable             TRUE

ndb-index-stat-cache-entries      32

ndb-index-stat-update-freq        20

new                               FALSE

old-alter-table                   FALSE

old-passwords                     FALSE

old-style-user-limits             FALSE

pid-file                          /home/jon/bin/mysql/var/hostname.pid1

port                              3306

relay-log                         (No default value)

relay-log-index                   (No default value)

relay-log-info-file               relay-log.info

replicate-same-server-id          FALSE

report-host                       (No default value)

report-password                   (No default value)

report-port                       3306

report-user                       (No default value)

rpl-recovery-rank                 0

safe-user-create                  FALSE

secure-auth                       FALSE

server-id                         1

show-slave-auth-info              FALSE

skip-grant-tables                 FALSE

skip-slave-start                  FALSE

slave-load-tmpdir                 /tmp/

socket                            /tmp/mysql.sock

sporadic-binlog-dump-fail         FALSE

sql-mode                          OFF

symbolic-links                    TRUE

tc-heuristic-recover              (No default value)

temp-pool                         TRUE

timed_mutexes                     FALSE

tmpdir                            (No default value)

use-symbolic-links                TRUE

verbose                           TRUE

warnings                          1

back_log                          50

binlog_cache_size                 32768

bulk_insert_buffer_size           8388608

connect_timeout                   5

date_format                       (No default value)

datetime_format                   (No default value)

default_week_format               0

delayed_insert_limit              100

delayed_insert_timeout            300

delayed_queue_size                1000

expire_logs_days                  0

flush_time                        0

ft_max_word_len                   84

ft_min_word_len                   4

ft_query_expansion_limit          20

ft_stopword_file                  (No default value)

group_concat_max_len              1024

innodb_additional_mem_pool_size   1048576

innodb_autoextend_increment       8

innodb_buffer_pool_awe_mem_mb     0

innodb_buffer_pool_size           8388608

innodb_concurrency_tickets        500

innodb_file_io_threads            4

innodb_force_recovery             0

innodb_lock_wait_timeout          50

innodb_log_buffer_size            1048576

innodb_log_file_size              5242880

innodb_log_files_in_group         2

innodb_mirrored_log_groups        1

innodb_open_files                 300

innodb_sync_spin_loops            20

innodb_thread_concurrency         20

innodb_commit_concurrency         0

innodb_thread_sleep_delay         10000

interactive_timeout               28800

join_buffer_size                  131072

key_buffer_size                   8388600

key_cache_age_threshold           300

key_cache_block_size              1024

key_cache_division_limit          100

long_query_time                   10

lower_case_table_names            0

max_allowed_packet                1048576

max_binlog_cache_size             4294967295

max_binlog_size                   1073741824

max_connect_errors                10

max_connections                   100

max_delayed_threads               20

max_error_count                   64

max_heap_table_size               16777216

max_join_size                     4294967295

max_length_for_sort_data          1024

max_relay_log_size                0

max_seeks_for_key                 4294967295

max_sort_length                   1024

max_tmp_tables                    32

max_user_connections              0

max_write_lock_count              4294967295

multi_range_count                 256

myisam_block_size                 1024

myisam_data_pointer_size          6

myisam_max_extra_sort_file_size   2147483648

myisam_max_sort_file_size         2147483647

myisam_repair_threads             1

myisam_sort_buffer_size           8388608

myisam_stats_method               nulls_unequal

net_buffer_length                 16384

net_read_timeout                  30

net_retry_count                   10

net_write_timeout                 60

open_files_limit                  0

optimizer_prune_level             1

optimizer_search_depth            62

preload_buffer_size               32768

query_alloc_block_size            8192

query_cache_limit                 1048576

query_cache_min_res_unit          4096

query_cache_size                  0

query_cache_type                  1

query_cache_wlock_invalidate      FALSE

query_prealloc_size               8192

range_alloc_block_size            2048

read_buffer_size                  131072

read_only                         FALSE

read_rnd_buffer_size              262144

div_precision_increment           4

record_buffer                     131072

relay_log_purge                   TRUE

relay_log_space_limit             0

slave_compressed_protocol         FALSE

slave_net_timeout                 3600

slave_transaction_retries         10

slow_launch_time                  2

sort_buffer_size                  2097144

sync-binlog                       0

sync-frm                          TRUE

sync-replication                  0

sync-replication-slave-id         0

sync-replication-timeout          10

table_cache                       64

table_lock_wait_timeout           50

thread_cache_size                 0

thread_concurrency                10

thread_stack                      196608

time_format                       (No default value)

tmp_table_size                    33554432

transaction_alloc_block_size      8192

transaction_prealloc_size         4096

updatable_views_with_limit        1

wait_timeout                      28800

濡傛灉鏈変竴涓�mysqld鏈嶅姟鍣ㄦ�ｅ湪杩愯�岋紝閫氳繃杩炴帴瀹冨苟鎵ц�岃繖涓�鍛戒护锛屽彲浠ョ湅鍒板疄闄呬笂浣跨敤鐨勫彉閲忕殑鍊硷細

mysql> SHOW VARIABLES;

杩樺彲浠ラ�氳繃涓嬮潰鐨勮��鍙ョ湅鍒拌繍琛屾湇鍔″櫒鐨勭粺璁″拰鐘舵�佹寚鏍囷細

mysql>SHOW STATUS锛�

浣跨敤mysqladmin杩樺彲浠ヨ幏寰楃郴缁熷彉閲忓拰鐘舵�佷俊鎭�锛�

shell> mysqladmin variables

shell> mysqladmin extended-status

鍏充簬鎵�鏈夌郴缁熷拰鐘舵�佸彉閲忕殑瀹屽叏鎻忚堪鍙傝��5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺鈥�鍜�5.3.4鑺傦紝鈥滄湇鍔″櫒鐘舵�佸彉閲忊��銆�

MySQL浣跨敤瀹屽叏鍙�浠ュ崌绾х殑绠楁硶锛屽洜姝ら�氬父杩愯�屾椂鍙�浠ョ敤寰堝皯鐨勫唴瀛樸�傜劧鑰岋紝閫氬父鎯呭喌鑻ョ粰MySQL鏇村�氱殑鍐呭瓨鎬ц兘浼氭洿濂姐��

褰撹皟鑺�MySQL鏈嶅姟鍣ㄦ椂锛岃�侀厤缃�鐨勪袱涓�鏈�閲嶈�佺殑鍙橀噺鏄�key_buffer_size鍜�table_cache銆傚湪璇曞浘鏇存敼鍏跺畠鍙橀噺鍓嶄綘搴斿厛纭�淇″凡缁忛�傚綋鍦伴厤缃�浜嗚繖浜涘彉閲忋��

涓嬮潰鐨勪緥瀛愭樉绀轰簡閮ㄥ垎鍏稿瀷鐨勪笉鍚岀殑杩愯�屾椂閰嶇疆鐨勫彉閲忓�笺��

路         濡傛灉鑷冲皯鏈�256MB鍐呭瓨鍜岃�稿�氳〃锛屾兂瑕佸湪涓�绛夋暟閲忕殑瀹㈡埛鏃惰幏寰楁渶澶ф�ц兘锛屽簲浣跨敤锛�

路                shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \

路                           --sort_buffer_size=4M --read_buffer_size=1M &

路         濡傛灉鍙�鏈�128MB鍐呭瓨鍜屽皯閲忚〃锛屼絾浠嶇劧瑕佽繘琛屽ぇ閲忕殑鎺掑簭锛屽彲浠ヤ娇鐢�锛�

路                shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M

濡傛灉鏈夎�稿�氬苟琛岃繛鎺ワ紝浜ゆ崲闂�棰樹細鍙戠敓锛岄櫎闈�mysqld宸茬粡閰嶇疆鎴愪负姣忎釜杩炴帴鍒嗛厤寰堝皯鐨勫唴瀛樸�傚�傛灉鏈夎冻澶熺殑鍐呭瓨鐢ㄤ簬鎵�鏈夎繛鎺ワ紝mysqld浼氭墽琛屽緱鏇村ソ銆�

路         瀵逛簬灏戦噺鍐呭瓨鍜屽ぇ閲忚繛鎺ワ紝搴斾娇鐢�锛�

路                shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \

路                           --read_buffer_size=100K &

鎴栫敋鑷充负锛�

shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \

           --table_cache=32 --read_buffer_size=8K \

           --net_buffer_length=1K &

濡傛灉姝ｅ�硅繙杩滃ぇ浜庡彲鐢ㄥ唴瀛樼殑琛ㄦ墽琛�GROUP BY鎴�ORDER BY鎿嶄綔锛屽簲澧炲姞read_rnd_buffer_size鐨勫�间互鍔犻�熸帓搴忔搷浣滃悗闈㈢殑琛岃�诲彇銆�

濡傛灉宸茬粡瀹夎�呬簡MySQL锛�support-files鐩�褰曞寘鍚�涓�浜涗笉鍚岀殑my.cnf绀轰緥鏂囦欢锛�my-huge.cnf銆�my-澶�.cnf銆�my-medium.cnf鍜�my-small.cnf銆傚彲浠ヤ娇鐢ㄨ繖浜涙枃浠舵潵浼樺寲绯荤粺銆�

璇锋敞鎰忓�傛灉鍦ㄥ懡浠よ�屼腑涓�mysqld鎴�mysqld_safe鎸囧畾涓�涓�閫夐」锛屽畠鍙�鍦ㄨ�ユ�℃湇鍔″櫒璋冪敤涓�淇濇寔鏈夋晥銆傝�佹兂姣忔�℃湇鍔″櫒杩愯�屾椂浣跨敤璇ラ�夐」锛屽皢瀹冩斁鍦ㄩ�夐」鏂囦欢涓�銆�

瑕佹兂鐪嬪弬鏁版洿鏀圭殑鏁堟灉锛屽簲鎵ц�岋細

shell> mysqld --key_buffer_size=32M --verbose ---help

鍙橀噺鍊煎垪浜庤緭鍑虹殑鏈�鍚庛�傜‘淇�--verbose鍜�---help閫夐」鍦ㄦ渶鍚庛�傚惁鍒欙紝鍦ㄥ懡浠よ�屼腑鍒椾簬瀹冧滑鍚庨潰鐨勯�夐」鐨勬晥鏋滀笉浼氬弽鏄犲埌杈撳嚭涓�銆�

鍏充簬璋冭妭InnoDB瀛樺偍寮曟搸鐨勪俊鎭�锛屽弬瑙�15.2.11鑺傦紝鈥淚nnoDB鎬ц兘璋冭妭鎻愮ず鈥�銆�

鏌ヨ��浼樺寲鍣ㄧ殑浠诲姟鏄�鍙戠幇鎵ц��SQL鏌ヨ�㈢殑鏈�浣虫柟妗堛�傚洜涓衡��濂�鈥濇柟妗堝拰鈥�鍧�鈥濇柟妗堜箣闂寸殑鎬ц兘宸�鍒�浼氬法澶�(涔熷氨鏄�璇达紝绉掔浉瀵逛簬灏忔椂鎴栫敋鑷冲ぉ)锛屽ぇ澶氭暟鏌ヨ��浼樺寲鍣�锛屽寘鎷�MySQL鐨勬煡璇�浼樺寲鍣�锛屾�绘垨澶氭垨灏戝湴鍦ㄦ墍鏈夊彲鑳界殑鏌ヨ�㈣瘎浼版柟妗堜腑鎼滅储鏈�浣虫柟妗堛�傚�逛簬鑱旀帴鏌ヨ��锛�MySQL浼樺寲鍣ㄦ墍璋冩煡鐨勫彲鑳界殑鏂规�堟暟闅忔煡璇�涓�鎵�寮曠敤鐨勮〃鐨勬暟鐩�鍛堟寚鏁板�為暱銆傚�逛簬灏忔暟閲忕殑琛�(鍏稿瀷灏忎簬7-10)锛岃繖涓嶆槸涓�涓�闂�棰樸�傜劧鑰岋紝褰撴彁浜ょ殑鏌ヨ�㈡洿澶ф椂锛屾煡璇�浼樺寲鎵�鑺辩殑鏃堕棿浼氬緢瀹规槗鍦版垚涓烘湇鍔″櫒鎬ц兘鐨勪富瑕佺摱棰堛��

鏌ヨ��浼樺寲鐨勪竴涓�鏇村姞鐏垫椿鐨勬柟娉曟槸鍏佽�哥敤鎴锋帶鍒朵紭鍖栧櫒璇﹀敖鍦版悳绱㈡渶浣虫煡璇㈣瘎浼版柟妗堛�備竴鑸�鎬濇兂鏄�浼樺寲鍣ㄨ皟鏌ョ殑鏂规�堣秺灏戯紝瀹冪紪璇戜竴涓�鏌ヨ�㈡墍鑺辫垂鐨勬椂闂磋秺灏戙�傚彟涓�鏂归潰锛屽洜涓轰紭鍖栧櫒璺宠繃浜嗕竴浜涙柟妗堬紝瀹冨彲鑳介敊杩囦竴涓�鏈�浣虫柟妗堛��

浼樺寲鍣ㄥ叧浜庢柟妗堟暟閲忚瘎浼扮殑琛屼负鍙�浠ラ�氳繃涓や釜绯荤粺鍙橀噺鏉ユ帶鍒讹細

路         optimizer_prune_level鍙橀噺鍛婅瘔浼樺寲鍣ㄦ牴鎹�瀵规瘡涓�琛ㄨ�块棶鐨勮�屾暟鐨勪及璁¤烦杩囨煇浜涙柟妗堛�傛垜浠�鐨勮瘯楠屾樉绀鸿�ョ被鈥�鏈夋牴鎹�鐨勭寽娴�鈥濆緢灏戦敊杩囨渶浣虫柟妗堬紝骞朵笖鍙�浠ュぇ澶ч檷浣庢煡璇㈢紪杈戞�℃暟銆傝繖灏辨槸涓轰粈涔堥粯璁ゆ儏鍐佃�ラ�夐」涓�on(optimizer_prune_level=1)銆傜劧鑰岋紝濡傛灉浣犺�や负浼樺寲鍣ㄩ敊杩囦簡涓�涓�鏇村ソ鐨勬煡璇㈡柟妗堬紝鍒欒�ラ�夐」鍙�浠ュ叧闂�(optimizer_prune_level=0)锛岄�庨櫓鏄�鏌ヨ�㈢紪杈戣姳璐圭殑鏃堕棿鏇撮暱銆傝�锋敞鎰忓嵆浣夸娇鐢ㄨ�ュ惎鍙戯紝浼樺寲鍣ㄤ粛鐒跺彲浠ユ帰娴嬪憟鎸囨暟鏁扮洰鐨勬柟妗堛��

路         optimizer_search_depth鍙橀噺鍛婅瘔浼樺寲鍣ㄥ�逛簬姣忎釜鏈�瀹屾垚鐨勨�滄湭鏉ョ殑鈥濇柟妗堬紝搴旀煡鐪嬪�氭繁锛屼互璇勪及鏄�鍚﹀簲瀵瑰畠杩涗竴姝ユ墿澶с��optimizer_search_depth鍊艰緝灏忎細浣挎煡璇㈢紪杈戞�℃暟澶уぇ鍑忓皬銆備緥濡傦紝濡傛灉optimizer_search_depth鎺ヨ繎浜庢煡璇�涓�琛ㄧ殑鏁伴噺锛屽��12銆�13鎴栨洿澶氳〃鐨勬煡璇㈠緢鍙�鑳介渶瑕佸嚑灏忔椂鐢氳嚦鍑犲ぉ鐨勬椂闂存潵缂栬瘧銆傚悓鏃讹紝濡傛灉鐢�optimizer_search_depth绛変簬3鎴�4缂栬緫锛屽�逛簬鍚屼竴涓�鏌ヨ��锛岀紪璇戝櫒缂栬瘧鏃堕棿鍙�浠ュ皯浜�1鍒嗛挓銆傚�傛灉涓嶈兘纭�瀹氬悎鐞嗙殑optimizer_search_depth鍊硷紝璇ュ彉閲忓彲浠ヨ�剧疆涓�0锛屽憡璇変紭鍖栧櫒鑷�鍔ㄧ‘瀹氳�ュ�笺��