Chapter 15. Storage Engines and Table Types - MySQL 5.1参考手册中文版

绗�15绔狅細瀛樺偍寮曟搸鍜岃〃绫诲瀷

·         --myisam-recover=mode

璁剧疆涓哄穿婧僊yISAM琛ㄨ嚜鍔ㄦ仮澶嶇殑妯″紡銆�

·         --delay-key-write=ALL

瀵逛换浣昅yISAM琛ㄧ殑鍐欐搷浣滀箣闂翠笉瑕佸埛鏂伴敭缂撳啿鍖恒��

娉ㄩ噴锛�濡傛灉浣犺�佽繖涔堝仛銆傚綋琛ㄥ湪浣跨敤涓�涔嬫椂锛屼綘搴旇�ヤ笉浣跨敤鏉ヨ嚜鍙︿竴涓�绋嬪簭鐨凪yISAM琛�锛堟瘮濡備粠鍙︿竴涓狹ySQL鏈嶅姟鍣ㄦ垨鐢�myisamchk锛夈�傝繖涔堝仛浼氬�艰嚧绱㈠紩琚�鐮村潖銆�

瀵逛娇鐢�--delay-key-write鐨勮〃锛屼娇鐢�--external-locking娌℃湁甯�鍔┿��

·         bulk_insert_buffer_size

鐢ㄥ湪鍧楁彃鍏ヤ紭鍖栦腑鐨勬爲缂撳啿鍖虹殑澶у皬銆�娉ㄩ噴锛氳繖鏄�涓�涓�per thread鐨勯檺鍒躲��

·         (OBSOLETE) myisam_max_extra_sort_file_size

杩欎釜鍙傛暟宸茬粡涓嶅湪MySQL涓�浣跨敤銆�

·         myisam_max_sort_file_size

濡傛灉涓存椂鏂囦欢浼氬彉寰楄秴杩囩储寮曪紝涓嶈�佷娇鐢ㄥ揩閫熸帓搴忕储寮曟柟娉曟潵鍒涘缓涓�涓�绱㈠紩銆�娉ㄩ噴锛氳繖涓�鍙傛暟浠ュ瓧鑺傜殑褰㈠紡缁欏嚭銆�

·         myisam_sort_buffer_size

璁剧疆鎭㈠�嶈〃涔嬫椂浣跨敤鐨勭紦鍐插尯鐨勫昂瀵搞��

·         琛ㄨ��鏌ラ敊銆�

·         濡傛灉鏈嶅姟鍣ㄥ彂鐜颁竴涓�閿欒��锛屽畠璇曠潃鍋氬揩閫熻〃淇�澶嶏紙鎺掑簭涓斾笉閲嶆柊鍒涘缓鏁版嵁鏂囦欢锛夈��

·         濡傛灉淇�澶嶅洜涓烘暟鎹�鏂囦欢涓�鐨勪竴涓�閿欒��鑰屽け璐ワ紙渚嬪�傦紝涓�涓�閲嶅�嶉敭閿欒��锛夛紝鏈嶅姟鍣ㄤ細鍐嶆�″皾璇曚慨澶嶏紝杩欎竴娆￠噸寤烘暟鎹�鏂囦欢銆�

·         濡傛灉淇�澶嶄粛鐒跺け璐ワ紝鏈嶅姟鍣ㄧ敤鏃т慨澶嶉�夐」鏂规硶鍐嶉噸璇曚竴娆′慨澶嶏紙涓�琛屾帴涓�琛屽湴鍐欙紝涓嶆帓搴忥級銆傝繖涓�鏂规硶搴旇�ヨ兘淇�澶嶄换浣曠被鍨嬬殑閿欒��锛屽苟涓旈渶瑕佸緢浣庣殑纾佺洏绌洪棿銆�

15.1.3.1. 闈欐�侊紙鍥哄畾闀垮害锛夎〃鐗瑰緛

15.1.3.2. 鍔ㄦ�佽〃鐗瑰緛

15.1.3.3. 宸插帇缂╄〃鐗瑰緛

闈欐�佹牸寮忔槸MyISAM琛ㄧ殑榛樿�ゅ瓨鍌ㄦ牸寮忋�傚綋琛ㄤ笉鍖呭惈鍙橀噺闀垮害鍒楋紙VARCHAR, BLOB, 鎴朤EXT锛夋椂锛屼娇鐢ㄨ繖涓�鏍煎紡銆傛瘡涓�琛岀敤鍥哄畾瀛楄妭鏁板瓨鍌ㄣ��

MyISAM鐨勪笁绉嶅瓨鍌ㄦ牸寮忎腑锛岄潤鎬佹牸寮忓氨鏈�绠�鍗曚篃鏄�鏈�瀹夊叏鐨勶紙鑷冲皯瀵逛簬宕╂簝鑰岃█锛夈�傞潤鎬佹牸寮忎篃鏄�鏈�蹇�鐨刼n-disk鏍煎紡銆傚揩閫熸潵鑷�浜庢暟鎹�鏂囦欢涓�鐨勮�屽湪纾佺洏涓婅��鎵惧埌鐨勫�规槗鏂瑰紡锛氬綋鎸夌収绱㈠紩涓�鐨勮�屽彿鏌ユ壘涓�涓�琛屾椂锛岀敤琛岄暱搴︿箻浠ヨ�屽彿銆傚悓鏍凤紝褰撴壂鎻忎竴涓�琛ㄧ殑 鏃跺�欙紝寰堝�规槗鐢ㄦ瘡涓�纾佺洏璇绘搷浣滆�讳竴瀹氭暟閲忕殑璁板綍銆�

褰揗ySQL鏈嶅姟鍣ㄦ�ｅ線涓�涓�鍥哄畾鏍煎紡MyISAM鏂囦欢鍐欑殑鏃跺�欙紝濡傛灉璁＄畻鏈哄穿婧冧簡锛屽畨鍏ㄦ槸鏄剧劧鐨勩�傚湪杩欑�嶆儏鍐典笅锛�myisamchk鍙�浠ュ�规槗鍦板喅瀹氭瘡琛屼粠鍝�閲屽紑濮嬪埌鍝�閲岀粨鏉燂紝鎵�浠ュ畠閫氬父鍙�浠ユ敹鍥炴墍鏈夎�板綍锛岄櫎浜嗗啓浜嗕竴閮ㄥ垎鐨勮�板綍銆傛敞鎰忥紝鍩轰簬鏁版嵁琛岋紝MyISAM琛ㄧ储寮曞彲浠ヤ竴鐩磋��閲嶆柊鏋勫缓銆�

闈欐�佹牸寮忚〃鐨勪竴鑸�鐗瑰緛锛�

濡傛灉涓�涓狹yISAM琛ㄥ寘鍚�浠讳綍鍙�鍙橀暱搴� 鍒楋紙VARCHAR, BLOB鎴朤EXTDynamic锛夛紝鎴栬�呭�傛灉涓�涓�琛ㄨ��鐢≧OW_FORMAT=DYNAMIC閫夐」鏉ュ垱寤猴紝鍔ㄦ�佸瓨鍌ㄦ牸寮忚��浣跨敤銆�

杩欎釜鏍煎紡鏇翠负澶嶆潅涓�鐐癸紝鍥犱负姣忚�屾湁涓�涓�琛ㄦ槑琛屾湁澶氶暱鐨勫ご銆傚綋涓�涓�璁板綍鍥犱负鏇存柊鐨勭粨鏋滆��鍙樺緱鏇撮暱锛岃�ヨ�板綍涔熷彲浠ュ湪瓒呰繃涓�涓�浣嶇疆澶勭粨鏉熴��

浣犲彲浠ヤ娇鐢∣PTIMIZE TABLE鎴�myisamchk鏉ュ�逛竴涓�琛ㄦ暣鐞嗙�庣墖銆傚�傛灉鍦ㄤ竴涓�琛ㄤ腑鏈変綘棰戠箒璁块棶鎴栨敼鍙樼殑鍥哄畾闀垮害 鍒楋紝琛ㄤ腑涔熸湁涓�浜涘彲鍙橀暱搴﹀垪锛屼粎涓洪伩鍏嶇�庣墖鑰屾妸杩欎簺鍙�鍙橀暱搴﹀垪绉诲埌鍏跺畠琛ㄥ彲鑳芥槸涓�涓�濂戒富鎰忋��

鍔ㄦ�佹牸寮忚〃鐨勪竴鑸�鐗瑰緛锛�

·                3

·                + (number of columns + 7) / 8

·                + (number of char columns)

·                + (packed size of numeric columns)

·                + (length of strings)

·                + (number of NULL columns + 7) / 8

宸插帇缂╁瓨鍌ㄦ牸寮忔槸鐢�myisampack宸ュ叿鍒涘缓鐨勫彧璇绘牸寮忋��

鎵�鏈塎ySQL鍒嗗彂鐗堥噷閮介粯璁ゅ寘鎷�myisampack銆傚凡鍘嬬缉琛ㄥ彲浠ョ敤myisamchk鏉ヨВ鍘嬬缉銆�

宸插帇缂╄〃鏈変笅鍒楃壒寰侊細

15.1.4.1. 鎹熷潖鐨�MyISAM琛�

15.1.4.2. 鏈�琚�閫傚綋鍏抽棴鐨勮〃鐨勯棶棰�

鍗充娇MyISAM琛ㄦ牸寮忛潪甯稿彲闈狅紙SQL璇�鍙ュ�硅〃鍋氱殑鎵�鏈夋敼鍙樺湪璇�鍙ヨ繑鍥炰箣鍓嶈��鍐欎笅锛夛紝濡傛灉涓嬪垪浠讳綍浜嬩欢鍙戠敓锛屼綘渚濈劧鍙�浠ヨ幏寰楁崯鍧忕殑琛�锛�

涓�涓�鎹熷潖鐨勮〃鐨勫吀鍨嬬棁鐘跺�備笅锛�

·                Incorrect key file for table: '...'. Try to repair it

浣犲彲浠ョ敤CHECK TABLE statement璇�鍙ユ潵妫�鏌�MyISAM琛�鐨勫仴搴凤紝骞剁敤REPAIR TABLE淇�澶嶄竴涓� 鎹熷潖鐨凪yISAM琛ㄣ�傚綋mysqld涓嶈繍琛屼箣鏃讹紝浣犱篃鍙�浠ョ敤myisamchk鍛戒护妫�鏌ユ垨淇�鐞嗕竴涓�琛ㄣ�傝�峰弬闃�13.5.2.3鑺傦紝鈥淐HECK TABLE璇�娉�”锛� 13.5.2.6鑺傦紝鈥淩EPAIR TABLE璇�娉�”锛屽拰5.9.5鑺傦紝鈥渕yisamchk — MyISAM琛ㄧ淮鎶ゅ伐鍏�”銆�

濡傛灉浣犵殑琛ㄥ彉寰楅�戠箒鎹熷潖锛屼綘搴旇�ヨ瘯鐫�纭�瀹氫负浠�涔堜細杩欐牱鐨勫師鍥犮�傝�佹槑鐧界殑鏈�閲嶈�佺殑浜嬫槸琛ㄥ彉寰楁崯鍧忔槸涓嶆槸鍥犱负鏈嶅姟鍣ㄥ穿婧冪殑缁撴灉銆備綘鍙�浠ュ湪閿欒��鏃ュ織涓�鏌ユ壘鏈�杩戠殑restarted mysqld娑堟伅鏉ユ棭鏈熼獙璇佽繖涓�銆傚�傛灉瀛樺湪杩欐牱涓�涓�娑堟伅锛屽垯琛ㄦ崯鍧忔槸鏈嶅姟鍣ㄦ�绘帀鐨勪竴涓�缁撴灉鏄�寰堟湁鍙�鑳界殑銆傚惁鍒欙紝鎹熷潖鍙�鑳藉湪姝ｅ父鎿嶄綔涓�鍙戠敓銆傝繖鏄�涓�涓�缂洪櫡銆備綘搴旇�ヨ瘯鐫�鍒涘缓涓�涓�灞曠ず杩欎釜闂�棰樼殑鍙�閲嶅�嶇敓鎴愮殑娴嬭瘯妗堜緥銆傝�峰弬闃�A.4.2鑺傦紝鈥滃�傛灉MySQL淇濇寔宕╂簝锛岃�ユ�庝箞鍋�” 鍙�E.1.6鑺傦紝鈥滃�傛灉鍑虹幇琛ㄥ穿婧冿紝璇风敓鎴愭祴璇曟�堜緥”銆�

姣忎釜MyISAM绱㈠紩鏂囦欢(.MYI)鍦ㄥご鏈変竴涓�璁℃暟鍣�锛屽畠鍙�浠ヨ��鐢ㄦ潵妫�鏌ヤ竴涓�琛ㄦ槸鍚﹁��鎭板綋鍦板叧闂�銆傚�傛灉浣犱粠CHECK TABLE鎴�myisamchk寰楀埌涓嬪垪璀﹀憡锛屾剰鍛崇潃杩欎釜璁℃暟鍣ㄥ凡缁忎笉鍚屾�ヤ簡锛�

clients are using or haven't closed the table properly

杩欎釜璀﹀憡骞朵笉鏄�瀹屽叏鎰忓懗鐫�琛ㄥ凡琚�鐮村潖锛屼絾浣犺嚦灏戝簲璇ユ��鏌ヨ〃銆�

璁℃暟鍣ㄧ殑宸ヤ綔鏂瑰紡濡備笅锛�

鎹㈠彞璇濇潵璇达紝璁℃暟鍣ㄥ彧鏈夊湪涓嬪垪鎯呭喌浼氫笉鍚屾�ワ細

·         鍙�搴旇�ュ垱寤轰竴涓猰y.cnf鎴栨枃浠躲��

·         my.cnf鏂囦欢搴旇�ヨ��鏀惧湪C鐩樻牴鐩�褰曘��

·         my.ini鏂囦欢搴旇�ヨ��鏀剧疆鍦╓INDIR鐩�褰曪紱渚嬪�侰:\WINDOWS鎴朇:\WINNT銆備綘鍙�浠ュ湪Windows鎺у埗鍙扮殑鍛戒护鎻愮ず绗︿娇鐢⊿ET鍛戒护鏉ユ墦鍗癢INDIR鐨勫�硷細

·                C:\> SET WINDIR

·                windir=C:\WINNT

·         濡傛灉浣犵殑PC鍦–鐩樹笉鏄�鍚�鍔ㄧ洏鐨勫湴鏂逛娇鐢ㄥ惎鍔ㄨ�呰浇鏈猴紝浣犲敮涓�鐨勯�夋嫨鏄�浣跨敤my.ini鏂囦欢銆�

·         濡傛灉浣犱娇鐢ㄥ畨瑁呭拰閰嶇疆鍚戝�煎畨瑁呯殑MySQL锛宮y.ini鏂囦欢琚�鏀惧湪MySQL鐨勫畨瑁呯洰褰曘�傝�峰弬闃�2.3.5.14鑺傦紝鈥渕y.ini鏂囦欢鐨勪綅缃�”銆�

·         /etc/my.cnf

鍏ㄥ眬閫夐」銆�

·         $MYSQL_HOME/my.cnf

鏈嶅姟鍣ㄤ笓鐢ㄩ�夐」銆�

·         defaults-extra-file

--defaults-extra-file閫夐」鎸囧畾鐨勬枃浠躲��

·         ~/.my.cnf

鐢ㄦ埛涓撶敤閫夐」銆�

·         innodb_additional_mem_pool_size

InnoDB鐢ㄦ潵瀛樺偍鏁版嵁鐩�褰曚俊鎭�锛嗗叾瀹冨唴閮ㄦ暟鎹�缁撴瀯鐨勫唴瀛樻睜鐨勫ぇ灏忋�備綘搴旂敤绋嬪簭閲岀殑琛ㄨ秺澶氾紝浣犻渶瑕佸湪杩欓噷鍒嗛厤瓒婂�氱殑鍐呭瓨銆傚�傛灉InnoDB鐢ㄥ厜浜嗚繖涓�姹犲唴鐨勫唴瀛橈紝InnoDB寮�濮嬩粠鎿嶄綔绯荤粺鍒嗛厤鍐呭瓨锛屽苟涓斿線MySQL閿欒��鏃ュ織鍐欒�﹀憡淇℃伅銆� 榛樿�ゅ�兼槸1MB銆�

·         innodb_autoextend_increment

褰撹嚜鍔ㄦ墿灞曡〃绌洪棿琚�濉�婊′箣鏃讹紝涓烘墿灞曡�屽�炲姞鐨勫昂瀵革紙MB涓哄崟浣嶏級銆� 榛樿�ゅ�兼槸8銆傝繖涓�閫夐」鍙�浠ュ湪杩愯�屾椂浣滀负鍏ㄥ眬绯荤粺鍙橀噺鑰屾敼鍙樸��

·         innodb_buffer_pool_awe_mem_mb

濡傛灉缂撳啿姹犺��鏀惧湪32浣峎indows鐨凙WE鍐呭瓨閲岋紝杩欎釜鍙傛暟灏辨槸缂撳啿姹犵殑澶у皬锛圡B涓哄崟浣嶏級銆�(浠呭湪32浣峎indows涓婄浉鍏筹級濡傛灉浣犵殑32浣峎indows鎿嶄綔绯荤粺浣跨敤鎵�璋撶殑“鍦板潃绐楀彛鎵╁睍锛�AWE)”鏀�鎸佽秴杩�4GB鍐呭瓨锛屼綘鍙�浠ョ敤杩欎釜鍙傛暟鎶奍nnoDB缂撳啿姹犲垎閰嶈繘AWE鐗╃悊鍐呭瓨銆傝繖涓�鍙傛暟鏈�澶х殑鍙�鑳藉�兼槸64000銆傚�傛灉杩欎釜鍙傛暟琚�鎸囧畾浜嗭紝innodb_buffer_pool_size鏄�鍦�32浣嶅湴鍧�绌洪棿鐨�mysqld鍐呯殑绐楀彛锛孖nnoDB鎶婇偅涓狝WE鍐呭瓨鏄犲皠涓婂幓銆傚�筰nnodb_buffer_pool_size鍙傛暟锛屼竴涓�姣旇緝濂界殑鍊兼槸500MB銆�

·         innodb_buffer_pool_size

InnoDB鐢ㄦ潵缂撳瓨瀹冪殑鏁版嵁鍜岀储寮曠殑鍐呭瓨缂撳啿鍖虹殑澶у皬銆備綘鎶婅繖涓�鍊艰�惧緱瓒婇珮锛岃�块棶琛ㄤ腑鏁版嵁闇�瑕佸緱纾佺洏I/O瓒婂皯銆傚湪涓�涓�涓撶敤鐨勬暟鎹�搴撴湇鍔″櫒涓婏紝浣犲彲浠ヨ�剧疆杩欎釜鍙傛暟杈炬満鍣ㄧ墿鐞嗗唴瀛樺ぇ灏忕殑80%銆傚敖绠″�傛�わ紝杩樻槸涓嶈�佹妸瀹冭�剧疆寰楀お澶э紝鍥犱负瀵圭墿鐞嗗唴瀛樼殑绔炰簤鍙�鑳藉湪鎿嶄綔绯荤粺涓婂�艰嚧鍐呭瓨璋冨害銆�

·         innodb_checksums

InnoDB鍦ㄦ墍鏈夊�圭�佺洏鐨勯〉闈㈣�诲彇涓婁娇鐢ㄦ牎楠屽拰楠岃瘉浠ョ‘淇濋�濆�栧�归敊闃叉�㈢‖浠舵崯鍧忔垨鏁版嵁鏂囦欢銆傚敖绠″�傛�わ紝鍦ㄤ竴浜涘皯瑙佺殑鎯呭喌涓嬶紙姣斿�傝繍琛屾爣鍑嗘��鏌ヤ箣鏃讹級杩欎釜棰濆�栫殑瀹夊叏鐗瑰緛鏄�涓嶅繀瑕佺殑銆傚湪杩欎簺鎯呭喌涓嬶紝杩欎釜閫夐」锛� 榛樿�ゆ槸鍏佽�哥殑锛夊彲浠ョ敤--skip-innodb-checksums鏉ュ叧闂�銆�

·         innodb_data_file_path

鍒板崟鐙�鏁版嵁鏂囦欢鍜屽畠浠�灏哄�哥殑璺�寰勩�傞�氳繃鎶奿nnodb_data_home_dir杩炴帴鍒拌繖閲屾寚瀹氱殑姣忎釜璺�寰勶紝鍒版瘡涓�鏁版嵁鏂囦欢鐨勫畬鏁寸洰褰曡矾寰勫彲琚�鑾峰緱銆傛枃浠跺ぇ灏忛�氳繃缁欏昂瀵稿�煎熬鍔燤鎴朑浠�MB鎴栬�匞B锛�1024MB锛変负鍗曚綅琚�鎸囧畾銆傛枃浠跺昂瀵哥殑鍜岃嚦灏戞槸10MB銆傚湪涓�浜涙搷浣滅郴缁熶笂锛屾枃浠跺繀椤诲皬浜�2GB銆傚�傛灉浣犳病鏈夋寚瀹歩nnodb_data_file_path锛屽紑濮嬬殑榛樿�よ�屼负鏄�鍒涘缓涓�涓�鍗曠嫭鐨勫ぇ灏�10MB鍚嶄负ibdata1鐨勮嚜鎵╁睍鏁版嵁鏂囦欢銆傚湪閭ｄ簺鏀�鎸佸ぇ鏂囦欢鐨勬搷浣滅郴缁熶笂锛屼綘鍙�浠ヨ�剧疆鏂囦欢澶у皬瓒呰繃4GB銆備綘涔熷彲浠ヤ娇鐢ㄥ師濮嬬�佺洏鍒嗗尯浣滀负鏁版嵁鏂囦欢锛岃�峰弬闃�15.2.14.2鑺傦紝鈥滀负琛ㄧ┖闂翠娇鐢ㄥ師濮嬭�惧��”銆�

·         innodb_data_home_dir

鐩�褰曡矾寰勫�规墍鏈塈nnoDB鏁版嵁鏂囦欢鐨勫叡鍚岄儴鍒嗐�傚�傛灉浣犱笉璁剧疆杩欎釜鍊硷紝 榛樿�ゆ槸MySQL鏁版嵁鐩�褰曘�備綘涔熷彲浠ユ寚瀹氳繖涓�鍊间负涓�涓�绌哄瓧绗︿覆锛屽湪杩欑�嶆儏鍐典笅锛屼綘鍙�浠ュ湪innodb_data_file_path涓�浣跨敤缁濆�规枃浠惰矾寰勩��

·         innodb_doublewrite

榛樿�ゅ湴锛孖nnoDB瀛樺偍鎵�鏈夋暟鎹�涓ゆ�★紝绗�涓�娆″瓨鍌ㄥ埌doublewrite缂撳啿锛岀劧鍚庡瓨鍌ㄥ埌纭�瀹炵殑鏁版嵁鏂囦欢銆傝繖涓�閫夐」鍙�浠ヨ��鐢ㄦ潵绂佹�㈣繖涓�鍔熻兘銆傜被浼间簬innodb_checksums锛岃繖涓�閫夐」 榛樿�ゆ槸鍏佽�哥殑锛涘洜涓烘爣鍑嗘��鏌ユ垨鍦ㄥ�归《绾ф�ц兘鐨勯渶瑕佽秴杩囧�规暟鎹�瀹屾暣鎬ф垨鍙�鑳芥晠闅滅殑鍏虫敞涔嬫椂锛岃繖涓�閫夐」鐢�--skip-innodb-doublewrite鏉ュ叧闂�銆�

·         innodb_fast_shutdown

濡傛灉浣犳妸杩欎釜鍙傛暟璁剧疆涓�0锛孖nnoDB鍦ㄥ叧闂�涔嬪墠鍋氫竴涓�瀹屽叏鍑�鍖栧拰涓�涓�鎻掑叆缂撳啿鍚堝苟銆傝繖浜涙搷浣滆�佽姳鍑犲垎閽熸椂闂达紝璁剧疆鍦ㄦ瀬绔�鎯呭喌涓嬭�佸嚑涓�灏忔椂銆傚�傛灉浣犺�剧疆杩欎釜鍙傛暟涓�1锛孖nnoDB鍦ㄥ叧闂�涔嬫椂璺宠繃杩欎簺鎿嶄綔銆� 榛樿�ゅ�间负1銆傚�傛灉浣犺�剧疆杩欎釜鍊间负2 (鍦∟etware鏃犳�ゅ��)锛� InnoDB灏嗗埛鏂板畠鐨勬棩蹇楃劧鍚庡喎鍏虫満锛屼豢浣汳ySQL宕╂簝涓�鏍枫�傚凡鎻愪氦鐨勪簨鍔′笉浼氳��涓㈠け锛屼絾鍦ㄤ笅涓�娆″惎鍔ㄤ箣鏃朵細鍋氫竴涓�宕╂簝鎭㈠�嶃��

·         innodb_file_io_threads

InnoDB涓�鏂囦欢I/O绾跨▼鐨勬暟閲忋�傛�ｅ父鍦帮紝杩欎釜鍙傛暟鏄�鐢� 榛樿�ょ殑锛岄粯璁ゅ�兼槸4锛屼絾鏄�澶ф暟鍊煎�筗indows纾佺洏I/O鏈夌泭銆傚湪Unix涓婏紝澧炲姞杩欎釜鏁版病鏈夋晥鏋滐紝InnoDB鎬绘槸浣跨敤榛樿�ゅ�笺��

·         innodb_file_per_table

杩欎釜閫夐」鑷翠娇InnoDB鐢ㄨ嚜宸辩殑.ibd鏂囦欢涓哄瓨鍌ㄦ暟鎹�鍜岀储寮曞垱寤烘瘡涓�涓�鏂拌〃锛岃�屼笉鏄�鍦ㄥ叡浜�琛ㄧ┖闂翠腑鍒涘缓銆傝�峰弬闃�15.2.6.6鑺傦紝鈥滀娇鐢≒er-Table琛ㄧ┖闂�”銆�

·         innodb_flush_log_at_trx_commit

褰搃nnodb_flush_log_at_trx_commit琚� 璁剧疆涓�0锛屾棩蹇楃紦鍐叉瘡绉掍竴娆″湴琚�鍐欏埌鏃ュ織鏂囦欢锛屽苟涓斿�规棩蹇楁枃浠跺仛鍒扮�佺洏鎿嶄綔鐨勫埛鏂帮紝浣嗘槸鍦ㄤ竴涓�浜嬪姟鎻愪氦涓嶅仛浠讳綍鎿嶄綔銆傚綋杩欎釜鍊间负1锛堥粯璁ゅ�硷級涔嬫椂锛屽湪姣忎釜浜嬪姟鎻愪氦鏃讹紝鏃ュ織缂撳啿琚�鍐欏埌鏃ュ織鏂囦欢锛屽�规棩蹇楁枃浠跺仛鍒扮�佺洏鎿嶄綔鐨� 鍒锋柊銆傚綋璁剧疆涓�2涔嬫椂锛屽湪姣忎釜鎻愪氦锛屾棩蹇楃紦鍐茶��鍐欏埌鏂囦欢锛屼絾涓嶅�规棩蹇楁枃浠跺仛鍒扮�佺洏鎿嶄綔鐨勫埛鏂般�傚敖绠″�傛�わ紝鍦ㄥ�规棩蹇楁枃浠剁殑鍒锋柊鍦ㄥ�间负2鐨勬儏鍐典篃姣忕�掑彂鐢熶竴娆°�傛垜浠�蹇呴』娉ㄦ剰鍒帮紝鍥犱负杩涚▼瀹夋帓闂�棰橈紝姣忕�掍竴娆＄殑 鍒锋柊涓嶆槸100%淇濊瘉姣忕�掗兘鍙戠敓銆備綘鍙�浠ラ�氳繃璁剧疆杩欎釜鍊间笉涓�1鏉ヨ幏寰楄緝濂界殑鎬ц兘锛屼絾闅忎箣浣犱細鍦ㄤ竴娆″穿婧冧腑鎹熷け浜屽垎涔嬩竴浠峰�肩殑浜嬪姟銆傚�傛灉浣犺�剧疆杩欎釜鍊间负0锛岄偅涔堜换浣�mysqld杩涚▼鐨勫穿婧冧細鍒犻櫎宕╂簝鍓嶆渶鍚庝竴绉掔殑浜嬪姟锛屽�傛灉浣犺�剧疆杩欎釜鍊间负2锛岄偅涔堝彧鏈夋搷浣滅郴缁熷穿婧冩垨鎺夌數鎵嶄細鍒犻櫎鏈�鍚庝竴绉掔殑浜嬪姟銆傚敖绠″�傛�わ紝InnoDB鐨勫穿婧冩仮澶嶄笉鍙楀奖鍝嶏紝鑰屼笖鍥犱负杩欐牱宕╂簝鎭㈠�嶅紑濮嬩綔鐢ㄨ�屼笉鑰冭檻杩欎釜鍊笺�傛敞鎰忥紝璁稿�氭搷浣滅郴缁熷拰涓�浜涚�佺洏纭�浠朵細娆洪獥 鍒锋柊鍒扮�佺洏鎿嶄綔銆傚敖绠″埛鏂版病鏈夎繘琛岋紝浣犲彲浠ュ憡璇�mysqld鍒锋柊宸茬粡杩涜�屻�傚嵆浣胯�剧疆杩欎釜鍊间负1锛屼簨鍔＄殑鎸佷箙绋嬪害涓嶈��淇濊瘉锛屼笖鍦ㄦ渶鍧忔儏鍐典笅鎺夌數鐢氳嚦浼氱牬鍧廔nnoDB鏁版嵁搴撱�傚湪SCSI纾佺洏鎺у埗鍣ㄤ腑锛屾垨鍦ㄧ�佺洏鑷�韬�涓�锛屼娇鐢ㄦ湁鍚庡�囩數姹犵殑纾佺洏缂撳瓨浼氬姞閫熸枃浠� 鍒锋柊骞朵笖浣垮緱鎿嶄綔鏇村畨鍏ㄣ�備綘涔熷彲浠ヨ瘯鐫�浣跨敤Unix鍛戒护hdparm鏉ュ湪纭�浠剁紦瀛樹腑绂佹�㈢�佺洏鍐欑紦瀛橈紝鎴栦娇鐢ㄥ叾瀹冧竴浜涘�圭‖浠舵彁渚涘晢涓撶敤鐨勫懡浠ゃ�傝繖涓�閫夐」鐨� 榛樿�ゅ�兼槸1銆�

·         innodb_flush_method

杩欎釜閫夐」鍙�鍦║nix绯荤粺涓婃湁鏁堛�傚�傛灉杩欎釜閫夐」琚�璁剧疆涓�fdatasync 锛堥粯璁ゅ�硷級锛孖nnoDB浣跨敤fsync()鏉ュ埛鏂版暟鎹�鍜屾棩蹇楁枃浠躲�傚�傛灉琚�璁剧疆涓篛_DSYNC锛孖nnoDB浣跨敤O_SYNC鏉ユ墦寮�骞跺埛鏂版棩蹇楁枃浠讹紝浣嗕娇鐢╢sync()鏉� 鍒锋柊鏁版嵁鏂囦欢銆傚�傛灉O_DIRECT琚�鎸囧畾浜嗭紙鍦ㄤ竴浜汫NU/Linux鐗堟湰鍟嗗彲鐢�锛夛紝InnoDB浣跨敤O_DIRECT鏉ユ墦寮�鏁版嵁鏂囦欢锛屽苟浣跨敤fsync()鏉ュ埛鏂版暟鎹�鍜屾棩蹇楁枃浠躲�傛敞鎰忥紝InnoDB浣跨敤fsync()鏉ユ浛浠�fdatasync()锛屽苟涓斿畠 榛樿�や笉浣跨敤O_DSYNC锛屽洜涓鸿繖涓�鍊煎湪璁稿�歎nix鍙樼�嶄笂宸茬粡鍙戠敓闂�棰樸��

·         innodb_force_recovery

璀﹀憡锛氳繖涓�閫夐」浠呭湪涓�涓�绱ф�ユ儏鍐典笅琚�瀹氫箟锛屽綋鏃朵綘鎯宠�佷粠鎹熷潖鐨勬暟鎹�搴撹浆鍌ㄨ〃銆傚彲鑳界殑鍊间负浠�1鍒�6銆傝繖浜涘�肩殑鎰忔�濆湪15.2.8.1鑺傦紝鈥滃己鍒舵仮澶�”涓�鍙欒堪銆備綔涓轰竴涓�瀹夊叏鎺�鏂斤紝褰撹繖涓�閫夐」鍊煎ぇ浜庨浂涔嬫椂锛孖nnoDB闃绘�㈢敤鎴蜂慨鏀规暟鎹�銆�

·         innodb_lock_wait_timeout

InnoDB浜嬪姟鍦ㄨ��鍥炴粴涔嬪墠鍙�浠ョ瓑寰呬竴涓�閿佸畾鐨勮秴鏃剁�掓暟銆侷nnoDB鍦ㄥ畠鑷�宸辩殑 閿佸畾琛ㄤ腑鑷�鍔ㄦ��娴嬩簨鍔℃�婚攣骞朵笖鍥炴粴浜嬪姟銆侷nnoDB鐢↙OCK TABLES璇�鍙ユ敞鎰忓埌閿佸畾璁剧疆銆傞粯璁ゅ�兼槸50绉掋��

涓哄湪涓�涓�澶嶅埗寤虹珛涓�鏈�澶у彲鑳界殑鎸佷箙绋嬪害鍜岃繛璐�鎬э紝浣犲簲璇ュ湪涓绘湇鍔″櫒涓婄殑my.cnf鏂囦欢閲屼娇鐢╥nnodb_flush_log_at_trx_commit=1鍜宻ync-binlog=1銆�

·         innodb_locks_unsafe_for_binlog

杩欎釜閫夐」鍦↖nnoDB鎼滅储鍜岀储寮曟壂鎻忎腑鍏抽棴涓嬩竴閿�閿佸畾銆傝繖涓�閫夐」鐨� 榛樿�ゅ�兼槸鍋囷紙false锛夈��

姝ｅ父鍦帮紝InnoDB浣跨敤涓�涓�琚�绉颁负next-key locking鐨勭畻娉曘�傚綋鎼滅储鎴栨壂鎻忎竴涓�琛ㄧ储寮曚箣鏃讹紝InnoDB浠ヨ繖鏍蜂竴绉嶆柟寮忓疄琛岃�岀骇閿佸畾锛屽畠瀵逛换浣曢亣鍒扮殑绱㈠紩璁板綍璁剧疆鍏变韩鐨勬垨鐙�鍗犵殑閿佸畾銆傚洜姝わ紝琛岀骇閿佸畾瀹為檯鏄�绱㈠紩璁板綍閿佸畾銆侷nnoDB瀵圭储寮曡�板綍璁剧疆鐨勯攣瀹氫篃褰卞搷琚�閿佸畾绱㈠紩璁板綍涔嬪墠鐨�“gap”銆傚�傛灉涓�涓�鐢ㄦ埛瀵规煇涓�绱㈠紩鍐呯殑璁板綍R鍙堝叡浜�鐨勬垨鐙�鍗犵殑閿佸畾锛屽彟涓�涓�鐢ㄦ埛涓嶈兘绔嬪嵆鍦�R涔嬪墠浠ョ储寮曠殑椤哄簭鎻掑叆涓�涓�鏂扮殑绱㈠紩璁板綍銆傝繖涓�閫夐」瀵艰嚧InnoDB涓嶅湪鎼滅储鎴栫储寮曟壂鎻忎腑浣跨敤涓嬩竴 閿�閿佸畾銆備笅涓�閿�閿佸畾浠嶇劧琚�鐢ㄦ潵纭�淇濆�栭敭寮哄埗鍙婇噸澶嶉敭鏍告煡銆傛敞鎰忥紝浣跨敤杩欎釜閫夐」鍙�鑳戒細瀵艰嚧涓�浜涜�″紓鐨勯棶棰橈細鍋囪�句綘鎯宠�佺敤鍊煎ぇ浜�100鐨勬爣璇嗙�︿粠瀛愯〃閲岃�诲彇骞堕攣瀹氭墍鏈夌殑瀛愯�板綍锛屽悓鏃� 鍚戦殢鍚庡湪閫夊畾鐨勮�屾洿鏂颁竴浜涘垪锛�

SELECT * FROM child WHERE id > 100 FOR UPDATE;

鍋囪�惧湪id鍒楁湁涓�涓�绱㈠紩銆傛煡璇�浠巌d澶т簬100鐨勭��涓�涓�璁板綍寮�濮嬫壂鎻忕储寮曘�傚�傛灉鍦ㄧ储寮曡�板綍涓婄殑閿佸畾涓嶆妸鍦ㄩ棿闅欏�勭敓鎴愮殑鎻掑叆鎺掗櫎閿佸畾锛屽悓鏃朵竴涓�鏂拌�岃��鎻掕繘琛ㄤ腑銆傚�傛灉浣犲湪鍚屼竴涓�浜嬪姟涔嬪唴鎵ц�屽悓鏍风殑SELECT锛屼綘浼氬湪鏌ヨ�㈣繑鍥炵殑缁撴灉鍖呴噷鐪嬪埌涓�涓�鏂拌�屻�傝繖涔熸剰鍛崇潃锛屽�傛灉鏂版潯鐩�琚�鍔犺繘鏁版嵁搴擄紝InnoDB涓嶄繚璇佽繛缁�鎬э紱灏界�″�傛�わ紝 瀵瑰簲杩炵画鎬т粛琚�淇濊瘉銆傚洜姝わ紝濡傛灉杩欎釜閫夐」琚�浣跨敤锛孖nnoDB鍦ㄥぇ澶氭暟瀛ょ珛绾у埆淇濊瘉READ COMMITTED銆�

杩欎釜閫夐」鐢氳嚦鏇翠笉瀹夊叏銆侷nnoDB鍦ㄤ竴涓猆PDATE鎴朌ELETE涓�鍙�閿佸畾瀹冩洿鏂版垨鍒犻櫎鐨勮�屻�傝繖澶уぇ鍑忓皯浜嗘�婚攣鐨勫彲鑳芥�э紝浣嗘槸鍙�浠ュ彂鐢熸�婚攣銆傛敞鎰忥紝鍗充娇鍦ㄥ綋绫讳技鐨勬搷浣滃奖鍝嶄笉鍚岃�屾椂鐨勬儏鍐典笅锛岃繖涓�閫夐」浠嶇劧涓嶅厑璁歌�稿�俇PDATE杩欐牱鐨勬搷浣滃帇鍊掔浉浼奸�夐」锛堟瘮濡傚彟涓�涓猆PDATE锛夈�傝�冭檻涓嬪垪渚嬪瓙锛�

CREATE TABLE A(A INT NOT NULL, B INT);

INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2);

COMMIT;

濡傛灉涓�涓�杩炴帴鎵ц�屼竴涓�鏌ヨ��锛�

SET AUTOCOMMIT = 0;

UPDATE A SET B = 5 WHERE B = 3;

骞朵笖鍏跺畠杩炴帴璺熺潃绗�涓�涓�杩炴帴鎵ц�屽叾瀹冩煡璇�锛�

SET AUTOCOMMIT = 0;

UPDATE A SET B = 4 WHERE B = 2;

鎺ョ潃鏌ヨ��2瑕佺瓑鏌ヨ��1鐨勬彁浜ゆ垨鍥炴粴锛屽洜涓烘煡璇�1瀵硅�岋紙2锛�3锛夋湁涓�涓�鐙�鍗犵殑閿佸畾锛屽苟涓旀煡璇�2鍦ㄦ壂鎻忚�岀殑鍚屾椂涔熻瘯鐫�瀵瑰畠涓嶈兘閿佸畾鐨勫悓涓�涓�琛岋紙2锛�3锛夐噰鍙栦竴涓�鐙�鍗犵殑閿佸畾銆傝繖鏄�鍥犱负褰搃nnodb_locks_unsafe_for_binlog閫夐」琚�浣跨敤涔嬫椂锛屾煡璇�2棣栧厛瀵逛竴涓�琛岄噰鍙栦竴涓�鐙�鍗犵殑閿佸畾锛岀劧鍚庣‘瀹氭槸鍚﹁繖涓�琛屽睘浜庣粨鏋滃寘锛屽苟涓斿�傛灉涓嶅睘浜庯紝灏遍噴鏀句笉蹇呰�佺殑閿佸畾銆�

鍥犳�わ紝鏌ヨ��1鎸夊�備笅鎵ц�岋細

x-lock(1,2)

unlock(1,2)

x-lock(2,3)

update(2,3) to (2,5)

x-lock(3,2)

unlock(3,2)

x-lock(4,3)

update(4,3) to (4,5)

x-lock(5,2)

unlock(5,2)

骞朵笖鏌ヨ��2鎸夊�備笅鎵ц�岋細

x-lock(1,2)

update(1,2) to (1,4)

x-lock(2,3) - 绛夊緟鏌ヨ��1鎻愪氦鎴栧洖婊�

·         innodb_log_arch_dir

濡傛灉鎴戜滑浣跨敤鏃ュ織妗ｆ�堬紝琚�瀹屾暣鍐欏叆鐨勬棩蹇楁枃浠舵墍鍦ㄧ殑鐩�褰曚篃琚�褰掓。銆傝繖涓�鍙傛暟鍊煎�傛灉琚�浣跨敤浜嗭紝搴旇�ヨ��璁剧疆寰椾笌innodb_log_group_home_dir涓�鏍枫�傚敖绠″�傛�わ紝瀹冧笉鏄�蹇呴渶鐨勩��

·         innodb_log_archive

杩欎釜鍊煎綋鍓嶈��璁句负0銆傚洜涓篗ySQL浣跨敤瀹冭嚜宸辩殑鏃ュ織鏂囦欢浠庡�囦唤鏉ユ仮澶嶏紝鎵�浠ュ綋鍓嶆病鏈夊繀瑕佹潵褰掓。InnoDB鏃ュ織鏂囦欢銆傝繖涓�閫夐」鐨� 榛樿�ゅ�兼槸0銆�

·         innodb_log_buffer_size

InnoDB鐢ㄦ潵寰�纾佺洏涓婄殑鏃ュ織鏂囦欢鍐欐搷浣滅殑缂撳啿鍖虹殑澶у皬銆傛槑鏅虹殑鍊兼槸浠�1MB鍒�8MB銆� 榛樿�ょ殑鏄�1MB銆備竴涓�澶х殑鏃ュ織缂撳啿鍏佽�稿ぇ鍨嬩簨鍔¤繍琛岃�屼笉闇�瑕佸湪浜嬪姟鎻愪氦涔嬪墠寰�纾佺洏鍐欐棩蹇椼�傚洜姝わ紝濡傛灉浣犳湁澶у瀷浜嬪姟锛屼娇鏃ュ織缂撳啿鍖烘洿澶т互鑺傜害纾佺洏I/O銆�

·         innodb_log_file_size

鍦ㄦ棩蹇楃粍閲屾瘡涓�鏃ュ織鏂囦欢鐨勫ぇ灏忋�傚湪32浣嶈�＄畻鏈轰笂鏃ュ織鏂囦欢鐨勫悎骞跺ぇ灏忓繀椤诲皯浜�4GB銆� 榛樿�ゆ槸5MB銆傛槑鏅虹殑鍊间粠1MB鍒�N鍒嗕箣涓�缂撳啿姹犲ぇ灏忥紝鍏朵腑N鏄�缁勯噷鏃ュ織鏂囦欢鐨勬暟鐩�銆傚�艰秺澶э紝鍦ㄧ紦鍐叉睜瓒婂皯闇�瑕佹��鏌ョ偣鍒锋柊琛屼负锛屼互鑺傜害纾佺洏I/O銆備絾鏇村ぇ鐨勬棩蹇楁枃浠朵篃鎰忓懗杩欏湪宕╂簝鏃舵仮澶嶅緱鏇存參銆�

·         innodb_log_files_in_group

鍦ㄦ棩蹇楃粍閲屾棩蹇楁枃浠剁殑鏁扮洰銆侷nnoDB浠ュ惊鐜�鏂瑰紡鍐欒繘鏂囦欢銆傞粯璁ゆ槸2锛堟帹鑽愶級銆�

·         innodb_log_group_home_dir

鍒癐nnoDB鏃ュ織鏂囦欢鐨勭洰褰曡矾寰勩�傚畠蹇呴』鏈夊拰innodb_log_arch_dir涓�鏍风殑鍊笺�傚�傛灉浣犱笉鎸囧畾浠讳綍InnoDB鏃ュ織鍙傛暟锛� 榛樿�ょ殑鏄�鍦∕ySQL鏁版嵁鐩�褰曢噷鍒涘缓涓や釜5MB澶у皬鍚嶄负ib_logfile0鍜宨b_logfile1鐨勬枃浠躲��

·         innodb_max_dirty_pages_pct

杩欐槸涓�涓�鑼冨洿浠�0鍒�100鐨勬暣鏁般�傞粯璁ゆ槸90銆侷nnoDB涓�鐨勪富绾跨▼璇曠潃浠庣紦鍐叉睜鍐欓〉闈�锛屼娇寰楄剰椤碉紙娌℃湁琚�鍐欑殑椤甸潰锛夌殑鐧惧垎姣斾笉瓒呰繃杩欎釜鍊笺�傚�傛灉浣犳湁SUPER鏉冮檺锛岃繖涓�鐧惧垎姣斿彲浠ュ湪鏈嶅姟鍣ㄨ繍琛屾椂鎸変笅闈㈡潵鏀瑰彉锛�

SET GLOBAL innodb_max_dirty_pages_pct = value;

·         innodb_max_purge_lag

杩欎釜閫夐」鎺у埗鍦ㄥ噣鍖栨搷浣滆��婊炲悗涔嬫椂锛屽�備綍寤惰繜INSERT, UPDATE鍜孌ELETE鎿嶄綔銆傦紙璇峰弬闃�15.2.12鑺傦紝鈥滃�氱増鏈�鐨勫疄鏂�”锛夈�傝繖涓�鍙傛暟鐨� 榛樿�ゅ�兼槸闆讹紝鎰忎负鏃犲欢杩熴�傝繖涓�閫夐」鍙�浠ュ湪杩愯�屾椂浣滀负鍏ㄥ眬绯荤粺鍙橀噺鑰岃��鏀瑰彉銆�

InnoDB浜嬪姟绯荤粺缁存寔涓�涓�浜嬪姟鍒楄〃锛岃�ュ垪琛ㄦ湁琚玌PDATE鎴朌ELETE鎿嶄綔鏍囧織涓哄垹闄ょ殑绱㈠紩璁板綍銆傝�╄繖涓�鍒楄〃鐨勯暱搴︿负purge_lag銆傚綋purge_lag瓒呰繃innodb_max_purge_lag涔嬫椂锛屾瘡涓狪NSERT, UPDATE鍜孌ELETE鎿嶄綔寤惰繜 ((purge_lag/innodb_max_purge_lag)*10)-5姣�绉掋�傚湪鍑�鍖栨壒澶勭悊鐨勫紑濮嬶紝寤惰繜姣忛殧10绉掕�＄畻銆傚�傛灉鍥犱负涓�涓�鏃х殑鍙�浠ョ湅鍒拌�岃��鍑�鍖栫殑涓�鑷寸殑璇绘煡鐪嬶紝 鍒犻櫎鎿嶄綔涓嶈��寤惰繜銆�

瀵规湁闂�棰樼殑宸ヤ綔閲忥紝鍏稿瀷璁剧疆鍙�鑳芥槸1鐧句竾锛屽亣璁炬垜浠�鐨勪簨鍔″緢灏忥紝鍙�鏈�100瀛楄妭澶у皬锛屾垜浠�灏卞彲浠ュ厑璁稿湪鎴戜滑鐨勮〃涔嬩腑鏈�100MB鏈�鍑�鍖栫殑琛屻��

·         innodb_mirrored_log_groups

鎴戜滑涓烘暟鎹�搴撲繚鎸佺殑鏃ュ織缁勫唴鍚屾牱鎷疯礉鐨勬暟閲忋�傚綋鍓嶈繖涓�鍊煎簲璇ヨ��璁句负1銆�

·         innodb_open_files

鍦↖nnoDB涓�锛岃繖涓�閫夐」浠呬笌浣犱娇鐢ㄥ�氳〃绌洪棿鏃舵湁鍏炽�傚畠鎸囧畾InnoDB涓�娆″彲浠ヤ繚鎸佹墦寮�鐨�.ibd鏂囦欢鐨勬渶澶ф暟鐩�銆傛渶灏忓�兼槸10銆� 榛樿�ゅ��300銆�

瀵�.ibd鏂囦欢鐨勬枃浠舵弿杩扮�︽槸浠呭�笽nnoDB鐨勩�傚畠浠�鐙�绔嬩簬閭ｄ簺鐢�--open-files-limit鏈嶅姟鍣ㄩ�夐」鎸囧畾鐨勬弿杩扮�︼紝涓斾笉褰卞搷琛ㄧ紦瀛樼殑鎿嶄綔銆�

·         innodb_status_file

杩欎釜閫夐」璁㊣nnoDB涓哄懆鏈熺殑SHOW INNODB STATUS杈撳嚭鍒涘缓涓�涓�鏂囦欢<datadir>/innodb_status.<pid>銆�

·         innodb_support_xa

褰撹��璁剧疆涓篛N鎴栬��1锛堥粯璁ゅ湴锛夛紝杩欎釜鍙橀噺鍏佽�窱nnoDB鏀�鎸佸湪XA浜嬪姟涓�鐨� 鍙屽悜鎻愪氦銆傚厑璁竔nnodb_support_xa瀵艰嚧涓�涓�棰濆�栫殑瀵逛簨鍔″噯澶囩殑纾佺洏鍒锋柊銆傚�傛灉浣犲�逛娇鐢╔A骞朵笉鍏冲績锛屼綘鍙�浠ラ�氳繃璁剧疆杩欎釜閫夐」涓篛FF鎴�0鏉ョ�佹�㈣繖涓�鍙橀噺锛屼互鍑忓皯纾佺洏 鍒锋柊鐨勬�℃暟骞惰幏寰楁洿濂界殑InnoDB鎬ц兘銆�

·         innodb_table_locks

InnoDB閲嶈�哃OCK TABLES锛岀洿鍒版墍鏈夊叾瀹冪嚎绋嬪凡缁忛噴鏀句粬浠�鎵�鏈夊�硅〃鐨勯攣瀹氾紝MySQL鎵嶄粠LOCK TABLE .. WRITE杩斿洖銆傞粯璁ゅ�兼槸1锛岃繖鎰忎负LOCK TABLES璁㊣nnoDB鍐呴儴閿佸畾涓�涓�琛ㄣ�傚湪浣跨敤AUTOCOMMIT=1鐨勫簲鐢ㄩ噷锛孖nnoDB鐨勫唴閮ㄨ〃閿佸畾浼氬�艰嚧姝婚攣銆備綘鍙�浠ュ湪my.cnf鏂囦欢锛圵indows涓婃槸my.ini鏂囦欢锛夐噷璁剧疆innodb_table_locks=0 鏉� 娑堥櫎杩欎釜闂�棰樸��

·         innodb_thread_concurrency

InnoDB璇曠潃鍦↖nnoDB鍐呬繚鎸佹搷浣滅郴缁熺嚎绋嬬殑鏁伴噺灏戜簬鎴栫瓑浜庤繖涓�鍙傛暟缁欏嚭鐨勯檺鍒躲�傚�傛灉鏈夋�ц兘闂�棰橈紝骞朵笖SHOW INNODB STATUS鏄剧ず璁稿�氱嚎绋嬪湪绛夊緟淇″彿锛屽彲浠ヨ�╃嚎绋�“thrashing” 锛屽苟涓旇�剧疆杩欎釜鍙傛暟鏇村皬鎴栨洿澶с�傚�傛灉浣犵殑璁＄畻鏈烘湁澶氫釜澶勭悊鍣ㄥ拰纾佺洏锛屼綘鍙�浠ヨ瘯鐫�杩欎釜鍊兼洿澶т互鏇村ソ鍦板埄鐢ㄨ�＄畻鏈虹殑璧勬簮銆備竴涓�鎺ㄨ崘鐨勫�兼槸绯荤粺涓婂�勭悊鍣ㄥ拰纾佺洏鐨勪釜鏁颁箣鍜屻�傚�间负500鎴栨瘮500澶т細绂佹�� 璋冪敤骞跺彂妫�鏌ャ�傞粯璁ゅ�兼槸20锛屽苟涓斿�傛灉璁剧疆澶т簬鎴栫瓑浜�20锛屽苟鍙戞��鏌ュ皢琚�绂佹��銆�

·         innodb_status_file

杩欎釜閫夐」璁㊣nnoDB涓哄懆鏈熺殑SHOW INNODB STATUS杈撳嚭鍒涘缓涓�涓�鏂囦欢<datadir>/innodb_status.<pid>銆�

15.2.5.1. 澶勭悊InnoDB鍒濆�嬪寲闂�棰�

濡傛灉InnoDB鍦ㄤ竴涓�鏂囦欢鎿嶄綔涓�鎵撳嵃涓�涓�鎿嶄綔绯荤粺閿欒��锛岄�氬父闂�棰樻槸濡備笅涓�鐨勪竴涓�锛�

褰揑nnoDB璇曠潃鍒濆�嬪寲瀹冪殑琛ㄧ┖闂存垨鏃ュ織鏂囦欢涔嬫椂锛屽�傛灉鍑洪敊浜嗭紝浣犲簲璇ュ垹闄�InnoDB鍒涘缓鐨勬墍鏈夋枃浠躲�傝繖鎰忓懗鐫�鏄�鎵�鏈塱bdata鏂囦欢鍜屾墍鏈塱b_logfiles鏂囦欢銆備竾涓�浣犲垱寤轰簡涓�浜汭nnoDB琛�锛屼负杩欎簺琛ㄤ篃浠嶮ySQL鏁版嵁搴撶洰褰曞垹闄ょ浉搴旂殑.frm鏂囦欢(濡傛灉浣犱娇鐢ㄥ�氶噸琛ㄧ┖闂寸殑璇濓紝涔熷垹闄や换浣�.ibd鏂囦欢锛夈�傜劧鍚庝綘鍙�浠ヨ瘯鐫�鍐嶆�″垱寤篒nnoDB鏁版嵁搴撱�傛渶濂芥槸浠庡懡浠ゆ彁绀虹�﹀惎鍔∕ySQL鏈嶅姟鍣� 锛屼互渚夸綘鍙�浠ユ煡鐪嬪彂鐢熶簡浠�涔堛��

15.2.6.1. 濡備綍鍦�InnoDB鐢ㄤ笉鍚孉PI鏉ヤ娇鐢ㄤ簨鍔�

15.2.6.2. 杞�鎹�MyISAM琛ㄥ埌InnoDB

15.2.6.3. AUTO_INCREMENT鍒楀�備綍鍦�InnoDB涓�宸ヤ綔

15.2.6.4.澶�閿�绾︽潫

15.2.6.5. InnoDB鍜�MySQL澶嶅埗

15.2.6.6. 浣跨敤Per-Table琛ㄧ┖闂�

榛樿�ゅ湴锛屾瘡涓�杩炴帴鍒癕ySQL鏈嶅姟鍣ㄧ殑瀹㈡埛绔�寮�濮嬩箣鏃舵槸鍏佽�歌嚜鍔ㄦ彁浜ゆā寮忕殑锛岃繖涓�妯″紡鑷�鍔ㄦ彁浜や綘杩愯�岀殑姣忎釜SQL璇�鍙ャ�傝�佷娇鐢ㄥ�氳��鍙ヤ簨鍔★紝浣犲彲浠ョ敤SQL璇�鍙�SET AUTOCOMMIT = 0绂佹�㈣嚜鍔ㄦ彁浜わ紝骞朵笖鐢–OMMIT鍜孯OLLBACK鏉ユ彁浜ゆ垨鍥炴粴浣犵殑浜嬪姟銆� 濡傛灉浣犳兂瑕乤utocommit淇濇寔鎵撳紑鐘舵�侊紝鍙�浠ュ湪START TRANSACTION涓嶤OMMIT鎴朢OLLBACK涔嬮棿灏佽�呬綘鐨勪簨鍔°�備笅鍒楃殑渚嬪瓙婕旂ず涓や釜浜嬪姟銆傜��涓�涓�鏄�琚�鎻愪氦鐨勶紝绗�浜屼釜鏄�琚� 鍥炴粴鐨勶細

shell> mysql test

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 5 to server version: 3.23.50-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))

    -> ENGINE=InnoDB;

Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');

Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

mysql> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO CUSTOMER VALUES (15, 'John');

Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM CUSTOMER;

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

| A    | B      |

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

|   10 | Heikki |

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

1 row in set (0.00 sec)

mysql>

鍦ㄧ被浼糚HP, Perl DBI/DBD, JDBC, ODBC, 鎴栬�匨ySQL鐨勬爣鍑咰璋冪敤鎺ュ彛杩欐牱鐨凙PI涓婏紝浣犺兘澶熶互瀛楃�︿覆褰㈠紡鍙戦�佷簨鍔℃帶鍒惰��鍙ワ紝濡侰OMMIT锛屽埌MySQL鏈嶅姟鍣�锛屽氨鍍忓叾瀹冧换浣曠殑SQL璇�鍙� 閭ｆ牱锛岃�稿�係ELECT鎴朓NSERT銆備竴浜汚PI涔熸彁渚涘崟鐙�鐨勪笓闂ㄧ殑浜嬪姟鎻愪氦鍜屽洖婊氬嚱鏁版垨鑰呮柟娉曘��

瑕佺偣锛氫綘涓嶅簲璇ュ湪mysql鏁版嵁搴擄紙姣斿�傦紝user鎴栬�卙ost锛夐噷鎶奙ySQL绯荤粺琛ㄨ浆鎹�涓篒nnoDB绫诲瀷銆傜郴缁熻〃鎬绘槸MyISAM鍨嬨��

濡傛灉浣犳兂瑕佹墍鏈夛紙闈炵郴缁燂級琛ㄩ兘琚�鍒涘缓鎴怚nnoDB琛�锛屼綘鍙�浠ョ畝鍗曞湴鎶奷efault-table-type=innodb琛屾坊鍔犲埌my.cnf鎴杕y.ini鏂囦欢鐨刐mysqld]鑺傞噷銆�

InnoDB瀵筂yISAM瀛樺偍寮曟搸閲囩敤鐨勫崟鐙�绱㈠紩鍒涘缓鏂规硶娌℃湁鍋氫笓闂ㄧ殑浼樺寲銆傚洜姝わ紝瀹冧笉鍊煎緱瀵煎嚭鎴栧�煎叆琛ㄤ互鍙婇殢鍚庡垱寤虹储寮曘�傛敼鍙樹竴涓�琛ㄤ负InnoDB鍨嬫渶蹇�鐨勫姙娉曞氨鏄�鐩存帴鎻掑叆杩涗竴涓狪nnoDB琛ㄣ�傚嵆锛屼娇鐢ˋLTER TABLE ... ENGINE=INNODB锛屾垨鐢ㄧ浉鍚岀殑瀹氫箟鍒涘缓涓�涓�绌篒nnoDB琛�锛屽苟涓旂敤INSERT INTO ... SELECT * FROM ...鎻掑叆琛屻��

濡傛灉浣犲�圭��浜屼釜閿�鏈塙NIQUE绾︽潫锛屼綘鍙�浠ュ湪瀵煎叆闃舵�佃�剧疆锛歋ET UNIQUE_CHECKS=0锛屼互涓存椂鍏虫帀鍞�涓�鎬ф��鏌ュソ鍔犻�熻〃鐨勫�煎叆銆傚�逛簬澶ц〃锛岃繖鑺傜渷浜嗗ぇ閲忕殑纾佺洏I/O锛屽洜涓篒nnoDB闅忓悗鍙�浠ヤ娇鐢ㄥ畠鐨勬彃鍏ョ紦鍐插尯鏉ョ��浜屼釜绱㈠紩璁板綍浣滀负涓�鎵规潵鍐欏叆銆�

涓鸿幏寰楀�规彃鍏ヨ繘绋嬬殑鏇村ソ鎺у埗锛屽垎娈垫彃鍏ュぇ琛ㄥ彲鑳芥瘮杈冨ソ锛�

INSERT INTO newtable SELECT * FROM oldtable

   WHERE yourkey > something AND yourkey <= somethingelse;

鎵�鏈夎�板綍宸茬粡鏈�鎻掑叆涔嬪悗锛屼綘鍙�浠ラ噸鍛藉悕琛ㄣ��

鍦ㄥぇ琛ㄧ殑杞�鎹�涓�锛屼綘搴旇�ュ�炲姞InnoDB缂撳啿姹犵殑澶у皬鏉ュ噺灏戠�佺洏I/O銆傚敖绠″�傛�わ紝涓嶈�佷娇鐢ㄨ秴杩�80%鐨勫唴閮ㄥ唴瀛樸�備綘涔熷彲浠ュ�炲姞InnoDB鏃ュ織鏂囦欢鍜屾棩蹇楁枃浠剁殑澶у皬銆�

纭�淇′綘娌℃湁濉�婊¤〃绌洪棿锛欼nnoDB琛ㄦ瘮MyISAM琛ㄩ渶瑕佸ぇ寰楀�氱殑纾佺洏绌洪棿銆傚�傛灉涓�涓狝LTER TABLE鑰楀敖浜嗙┖闂达紝瀹冨氨寮�濮嬩竴涓� 鍥炴粴锛屽苟涓斿�傛灉瀹冩槸纾佺洏缁戝畾鐨勶紝鍥炴粴鍙�鑳借�佸嚑涓�灏忔椂銆傚�逛簬鎻掑叆锛孖nnoDB浣跨敤鎻掑叆缂撳啿鍖烘潵浠ユ垚鎵瑰湴鍚堝苟绗�浜屼釜绱㈠紩璁板綍鍒扮储寮曚腑銆傞偅鏍疯妭鐪佷簡澶ч噺纾佺洏I/O銆傚湪鍥炴粴涓�锛屾病鏈変娇鐢ㄨ繖鏍风殑鏈哄埗锛岃�屽洖婊氳�佽姳姣旀彃鍏ラ暱30鍊嶇殑鏃堕棿鏉ュ畬鎴愩��

鍦ㄥけ鎺х殑鍥炴粴鎯呭喌涓嬶紝濡傛灉浣犲湪鏁版嵁搴撲腑娌℃湁鏈変环鍊肩殑鏁版嵁锛屾瘮杈冩槑鏅虹殑鏄�鏉�鎺夋暟鎹�搴撹繘绋嬭�屼笉鏄�绛夊嚑鐧句竾涓�纾佺洏I/O琚�瀹屾垚銆� 瀹屾暣鐨勮繃绋嬶紝璇峰弬闃�15.2.8.1鑺傦紝鈥滃己鍒舵仮澶�”銆�

濡傛灉浣犱负涓�涓�琛ㄦ寚瀹欰UTO_INCREMENT鍒楋紝鍦ㄦ暟鎹�璇嶅吀閲岀殑InnoDB琛ㄥ彞鏌勫寘鍚�涓�涓�鍚嶄负鑷�鍔ㄥ�為暱璁℃暟鍣ㄧ殑璁℃暟鍣�锛屽畠琚�鐢ㄥ湪涓鸿�� 鍒楄祴鏂板�笺�傝嚜鍔ㄥ�為暱璁℃暟鍣ㄤ粎琚�瀛樺偍鍦ㄤ富鍐呭瓨涓�锛岃�屼笉鏄�瀛樺湪纾佺洏涓娿��

InnoDB浣跨敤涓嬪垪绠楁硶鏉ヤ负鍖呭惈涓�涓�鍚嶄负ai_col鐨凙UTO_INCREMENT鍒楃殑琛═鍒濆�嬪寲鑷�鍔ㄥ�為暱璁℃暟鍣�锛氭湇鍔″櫒鍚�鍔ㄤ箣鍚庯紝褰撲竴涓�鐢ㄦ埛瀵硅〃T鍋氭彃鍏ヤ箣鏃讹紝InnoDB鎵ц�岀瓑浠峰�備笅璇�鍙ョ殑鍔ㄤ綔锛�

SELECT MAX(ai_col) FROM T FOR UPDATE;

璇�鍙ュ彇鍥炵殑鍊奸�愭�″姞涓�锛屽苟琚�璧嬬粰鍒楀拰鑷�鍔ㄥ�為暱璁℃暟鍣ㄣ�傚�傛灉琛ㄦ槸绌虹殑锛屽��1琚�璧嬩簣璇ュ垪銆傚�傛灉鑷�鍔ㄥ�為暱璁℃暟鍣ㄦ病鏈夎��鍒濆�嬪寲锛岃�屼笖鐢ㄦ埛璋冪敤涓鸿〃T鏄剧ず杈撳嚭鐨凷HOW TABLE STATUS璇�鍙ワ紝鍒欒�℃暟鍣ㄨ��鍒濆�嬪寲锛堜絾涓嶆槸澧炲姞璁℃暟锛夊苟琚�瀛樺偍浠ヤ緵闅忓悗鐨勬彃鍏ヤ娇鐢ㄣ�傛敞鎰忥紝鍦ㄨ繖涓�鍒濆�嬪寲涓�锛屾垜浠�瀵硅〃鍋氫竴涓�姝ｅ父鐨勭嫭鍗�璇�閿佸畾锛岃繖涓�閿佹寔缁�鍒颁簨鍔＄殑缁撴潫銆�

InnoDB瀵逛负鏂板垱寤鸿〃鐨勫垵濮嬪寲鑷�鍔ㄥ�為暱璁℃暟鍣ㄥ厑璁稿悓鏍风殑杩囩▼銆�

娉ㄦ剰锛屽�傛灉鐢ㄦ埛鍦↖NSERT涓�涓篈UTO_INCREMENT鍒楁寚瀹歂ULL鎴栬��0锛孖nnoDB澶勭悊琛岋紝灏变豢浣涘�艰繕娌℃湁琚�鎸囧畾锛屼笖涓哄畠鐢熸垚涓�涓�鏂板�笺��

鑷�鍔ㄥ�為暱璁℃暟鍣ㄨ��鍒濆�嬪寲涔嬪悗锛屽�傛灉鐢ㄦ埛鎻掑叆涓�涓�鏄庣‘鎸囧畾璇ュ垪鍊肩殑琛岋紝鑰屼笖璇ュ�煎ぇ浜庡綋鍓嶈�℃暟鍣ㄥ�硷紝鍒欒�℃暟鍣ㄨ��璁剧疆涓烘寚瀹� 鍒楀�笺�傚�傛灉娌℃湁鏄庣‘鎸囧畾涓�涓�鍊硷紝InnoDB缁欒�℃暟鍣ㄥ�炲姞涓�锛屽苟涓旇祴鏂板�肩粰璇ュ垪銆�

褰撹�块棶鑷�鍔ㄥ�為暱璁℃暟鍣ㄤ箣鏃讹紝InnoDB浣跨敤涓撶敤鐨勮〃绾х殑AUTO-INC閿佸畾锛岃�ラ攣鎸佺画鍒板綋鍓峉QL璇�鍙ョ殑缁撴潫鑰屼笉鏄�鍒颁笟鍔＄殑缁撴潫銆� 寮曞叆浜嗕笓鐢ㄩ攣閲婃斁绛栫暐锛屾潵涓哄�逛竴涓�鍚獳UTO_INCREMENT鍒楃殑琛ㄧ殑鎻掑叆鏀瑰杽閮ㄧ讲銆備袱涓�浜嬪姟涓嶈兘鍚屾椂瀵瑰悓涓�琛ㄦ湁AUTO-INC閿佸畾銆�

娉ㄦ剰锛屽�傛灉浣犲洖婊氫粠璁℃暟鍣ㄨ幏寰楁暟鐨勪簨鍔★紝浣犲彲鑳戒細鍦ㄨ祴缁橝UTO_INCREMENT鍒楃殑鍊肩殑搴忓垪涓�鍙戠幇闂撮殭銆�

濡傛灉鐢ㄦ埛缁欏垪璧嬩竴涓�璧嬪�硷紝鎴栬�咃紝濡傛灉鍊煎ぇ杩囧彲琚�浠ユ寚瀹氭暣鏁版牸寮忓瓨鍌ㄧ殑鏈�澶ф暣鏁帮紝鑷�鍔ㄥ�為暱鏈哄埗鐨勮�屼负涓嶈��瀹氫箟銆�

鍦–REATE TABLE鍜孉LTER TABLE璇�鍙ヤ腑锛孖nnoDB鏀�鎸丄UTO_INCREMENT = n 琛ㄩ�夐」鏉ヨ�剧疆璁℃暟鍣ㄥ垵濮嬪�兼垨鍙樻洿褰撳墠璁℃暟鍣ㄥ�笺�傚洜鍦ㄦ湰鑺傛棭鍏堣�ㄨ�虹殑鍘熷洜锛岃繖涓�閫夐」鐨勫奖鍝嶅湪鏈嶅姟鍣ㄩ噸鍚�鍚庡氨鏃犳晥浜嗐��

InnoDB涔熸敮鎸佸�栭敭绾︽潫銆侷nnoDB涓�瀵瑰�栭敭绾︽潫瀹氫箟鐨勮��娉曠湅璧锋潵濡備笅锛�

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

    REFERENCES tbl_name (index_col_name, ...)

    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

澶栭敭瀹氫箟鏈嶄粠涓嬪垪鎯呭喌锛�

InnoDB鎷掔粷浠讳綍璇曠潃鍦ㄥ瓙琛ㄥ垱寤轰竴涓�澶栭敭鍊艰�屼笉鍖归厤鍦ㄧ埗琛ㄤ腑鐨勫�欓�夐敭鍊肩殑INSERT鎴朥PDATE鎿嶄綔銆備竴涓�鐖惰〃鏈変竴浜涘尮閰嶇殑琛� 鐨勫瓙琛�锛孖nnoDB瀵逛换浣曡瘯鍥炬洿鏂版垨鍒犻櫎璇ョ埗琛ㄤ腑鍊欓�夐敭鍊肩殑UPDATE鎴朌ELETE鎿嶄綔鏈夋墍鍔ㄤ綔锛岃繖涓�鍔ㄤ綔鍙栧喅浜庣敤FOREIGN KEY瀛愬彞鐨凮N UPDATE鍜孫N DETETE瀛愬彞鎸囧畾鐨�referential action銆傚綋鐢ㄦ埛璇曞浘浠庝竴涓�鐖惰〃鍒犻櫎鎴栨洿鏂颁竴琛屼箣鏃讹紝涓斿湪瀛愯〃涓�鏈変竴涓�鎴栧�氫釜鍖归厤鐨勮�岋紝InnoDB鏍规嵁瑕侀噰鍙栫殑鍔ㄤ綔鏈変簲涓�閫夋嫨锛�

褰撶埗琛ㄤ腑鐨勫�欓�夐敭琚�鏇存柊鐨勬椂鍊欙紝InnoDB鏀�鎸佸悓鏍烽�夋嫨銆�閫夋嫨CASCADE锛屽湪瀛愯〃涓�鐨勫�栭敭 鍒楄��璁剧疆涓虹埗琛ㄤ腑鍊欓�夐敭鐨勬柊鍊笺�備互鍚屾牱鐨勬柟寮忥紝濡傛灉鍦ㄥ瓙琛ㄦ洿鏂扮殑鍒楀弬鑰冨湪鍙︿竴涓�琛ㄤ腑鐨勫�栭敭锛屾洿鏂扮骇鑱斻��

娉ㄦ剰锛孖nnoDB鏀�鎸佸�栭敭鍦ㄤ竴涓�琛ㄥ唴寮曠敤锛屽湪杩欎簺鎯呭喌涓嬶紝瀛愯〃瀹為檯涓婃剰鍛宠繖鍦ㄨ〃鍐呴檮灞炵殑璁板綍銆�

InnoDB闇�瑕佸�瑰�栭敭鍜岃��寮曠敤閿�鐨勭储寮曚互渚垮�栭敭妫�鏌ュ彲浠ュ揩閫熻繘琛屼笖涓嶉渶瑕佷竴涓�琛ㄦ壂鎻忋�傚�瑰�栭敭鐨勭储寮曡��鑷�鍔ㄥ垱寤恒�傝繖鏄�鐩稿�逛簬涓�浜涜�佺増鏈�锛屽湪鑰佺増鏈�涓�绱㈠紩蹇呴』鏄庣‘鍒涘缓锛屽惁鍒欏�栭敭绾︽潫鐨勫垱寤轰細澶辫触銆�

鍦↖nnoDB鍐咃紝澶栭敭閲屽拰琚�寮曠敤鍒楅噷鐩稿簲鐨勫垪蹇呴』鏈夌被浼肩殑鍐呴儴鏁版嵁绫诲瀷锛屼互渚垮畠浠�涓嶉渶绫诲瀷杞�鎹㈠氨鍙�琚�姣旇緝銆傛暣鏁扮被鍨嬬殑澶у皬鍜岀�﹀彿蹇呴』鐩稿悓銆傚瓧绗︿覆绫诲瀷鐨勯暱搴︿笉闇�瑕佺浉鍚屻�傚�傛灉浣犳寚瀹氫竴涓猄ET NULL鍔ㄤ綔锛岃�风‘璁や綘娌℃湁鍦ㄥ瓙琛ㄤ腑瀹ｅ憡璇� 鍒椾负涓�NOT NULL銆�

濡傛灉MySQL浠嶤REATE TABLE璇�鍙ユ姤鍛婁竴涓�閿欒��鍙�1005锛屽苟涓旈敊璇�淇℃伅瀛楃�︿覆鎸囧悜errno 150锛岃繖鎰忔�濇槸鍥犱负涓�涓�澶栭敭绾︽潫琚�涓嶆�ｇ‘褰㈡垚锛岃〃鍒涘缓澶辫触銆傜被浼煎湴锛屽�傛灉ALTER TABLE澶辫触锛屼笖瀹冩寚鍚慹rrno 150锛� 閭ｆ剰鍛崇潃瀵瑰凡鍙樻洿鐨勮〃锛屽�栭敭瀹氫箟浼氳��涓嶆�ｇ‘鐨勫舰鎴愩�備綘鍙�浠ヤ娇鐢⊿HOW INNODB STATUS鏉ユ樉绀轰竴涓�瀵规湇鍔″櫒涓婃渶杩戠殑InnoDB澶栭敭閿欒��鐨勮�︾粏瑙ｉ噴銆�

娉ㄩ噴锛�InnoDB涓嶅�归偅浜� 澶栭敭鎴栧寘鍚玁ULL鍒楃殑琚�寮曠敤閿�鍊兼��鏌ュ�栭敭绾︽潫銆�

瀵筍QL鏍囧噯鐨勮儗绂伙細濡傛灉鍦ㄧ埗琛ㄥ唴鏈夋暟涓�琛岋紝鍏朵腑鏈夌浉鍚岀殑 琚�寮曠敤閿�鍊硷紝鐒跺悗InnoDB鍦ㄥ�栭敭妫�鏌ヤ腑閲囧彇鍔ㄤ綔锛屽氨浠夸經鍏跺畠鏈夌浉鍚岄敭鍊肩殑鐖惰�屼笉瀛樺湪涓�鏍枫�備緥濡傦紝濡傛灉浣犲凡瀹氫箟涓�涓猂ESTRICT绫诲瀷鐨勭害鏉燂紝骞朵笖鏈変竴涓�甯︽暟涓�鐖惰�岀殑瀛愯�岋紝InnoDB涓嶅厑璁镐换浣曞�硅繖浜涚埗琛岀殑鍒犻櫎銆�

灞呬簬瀵瑰簲澶栭敭绾︽潫鐨勭储寮曞唴鐨勮�板綍锛孖nnoDB閫氳繃娣卞害浼樺厛閫夋硶鏂借�岀骇鑱旀搷浣溿��

瀵筍QL鏍囧噯鐨勮儗绂伙細 濡傛灉ON UPDATE CASCADE鎴朞N UPDATE SET NULL閫掑綊鏇存柊鐩稿悓鐨勮〃锛屼箣鍓嶅湪绾ц仈杩囩▼涓�璇ヨ〃涓�琚�鏇存柊杩囷紝瀹冨氨璞�RESTRICT涓�鏍峰姩浣溿�傝繖鎰忓懗鐫�浣犱笉鑳戒娇鐢ㄨ嚜寮曠敤ON UPDATE CASCADE鎴栬�匫N UPDATE SET NULL鎿嶄綔銆傝繖灏嗛樆姝㈢骇鑱旀洿鏂板�艰嚧鐨勬棤闄愬惊鐜�銆傚彟涓�鏂归潰锛屼竴涓�鑷�寮曠敤鐨凮N DELETE SET NULL鏄�鏈夊彲鑳界殑锛屽氨鍍忎竴涓�鑷�寮曠敤ON DELETE CASCADE涓�鏍枫�� 绾ц仈鎿嶄綔涓嶅彲浠ヨ��宓屽�楄秴杩�15灞傛繁銆�

瀵筍QL鏍囧噯鐨勮儗绂伙細 绫讳技涓�鑸�鐨凪ySQL锛屽湪涓�涓�鎻掑叆锛屽垹闄ゆ垨鏇存柊璁稿�氳�岀殑SQL璇�鍙ュ唴锛孖nnoDB閫愯�屾��鏌�UNIQUE鍜孎OREIGN KEY绾︽潫銆傛寜鐓�SQL鐨勬爣鍑嗭紝 榛樿�ょ殑琛屼负搴旇��寤惰繜妫�鏌ワ紝鍗崇害鏉熶粎鍦ㄦ暣涓猄QL璇�鍙ヨ��澶勭悊涔嬪悗鎵嶈��妫�鏌ャ�傜洿鍒癐nnoDB瀹炵幇寤惰繜鐨勭害鏉熸��鏌ヤ箣鍓嶏紝涓�浜涗簨鎯呮槸涓嶅彲鑳界殑锛屾瘮濡傚垹闄や竴涓�閫氳繃澶栭敭鍙傝�冨埌鑷�韬�鐨勮�板綍銆�

娉ㄩ噴锛�褰撳墠锛岃Е鍙戝櫒涓嶈��绾ц仈澶栭敭鐨勫姩浣滄縺娲汇��

涓�涓�閫氳繃鍗曞垪澶栭敭鑱旂郴璧风埗琛ㄥ拰瀛愯〃鐨勭畝鍗曚緥瀛愬�備笅锛�

CREATE TABLE parent(id INT NOT NULL,

                    PRIMARY KEY (id)

) TYPE=INNODB;

CREATE TABLE child(id INT, parent_id INT,

                   INDEX par_ind (parent_id),

                   FOREIGN KEY (parent_id) REFERENCES parent(id)

                     ON DELETE CASCADE

) TYPE=INNODB;

濡備笅鏄�涓�涓�鏇村�嶆潅鐨勪緥瀛愶紝鍏朵腑涓�涓猵roduct_order琛ㄥ�瑰叾瀹冧袱涓�琛ㄦ湁澶栭敭銆備竴涓�澶栭敭寮曠敤涓�涓猵roduct琛ㄤ腑鐨勫弻鍒楃储寮曘�傚彟涓�涓� 寮曠敤鍦╟ustomer琛ㄤ腑鐨勫崟琛岀储寮曪細

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,

                      price DECIMAL,

                      PRIMARY KEY(category, id)) TYPE=INNODB;

CREATE TABLE customer (id INT NOT NULL,

                      PRIMARY KEY (id)) TYPE=INNODB;

CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,

                      product_category INT NOT NULL,

                      product_id INT NOT NULL,

                      customer_id INT NOT NULL,

                      PRIMARY KEY(no),

                      INDEX (product_category, product_id),

                      FOREIGN KEY (product_category, product_id)

                        REFERENCES product(category, id)

                        ON UPDATE CASCADE ON DELETE RESTRICT,

                      INDEX (customer_id),

                      FOREIGN KEY (customer_id)

                        REFERENCES customer(id)) TYPE=INNODB;

InnoDB鍏佽�镐綘鐢ˋLTER TABLE寰�涓�涓�琛ㄤ腑娣诲姞涓�涓�鏂扮殑 澶栭敭绾︽潫锛�

ALTER TABLE yourtablename

    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

    REFERENCES tbl_name (index_col_name, ...)

    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

璁颁綇鍏堝垱寤洪渶瑕佺殑绱㈠紩銆備綘涔熷彲浠ョ敤ALTER TABLE寰�涓�涓�琛ㄦ坊鍔犱竴涓�鑷�寮曠敤澶栭敭绾︽潫銆�

InnoDB涔熸敮鎸佷娇鐢�ALTER TABLE鏉ョЩ闄� 澶栭敭锛�

ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

褰撳勾鍒涘缓涓�涓�澶栭敭涔嬫椂锛屽�傛灉FOREIGN KEY瀛愬彞鍖呮嫭涓�涓狢ONSTRAINT鍚嶅瓧锛屼綘鍙�浠ュ紩鐢ㄩ偅涓�鍚嶅瓧鏉ョЩ闄� 澶栭敭銆傚彟澶栵紝褰撳�栭敭琚�鍒涘缓涔嬫椂锛宖k_symbol鍊艰�獻nnoDB鍐呴儴淇濊瘉銆傚綋浣犳兂瑕佺Щ闄や竴涓�澶栭敭涔嬫椂锛岃�佹壘鍑烘爣璁帮紝璇蜂娇鐢⊿HOW CREATE TABLE璇�鍙ャ�備緥瀛愬�備笅锛�

mysql> SHOW CREATE TABLE ibtest11c\G

*************************** 1. row ***************************

       Table: ibtest11c

Create Table: CREATE TABLE `ibtest11c` (

  `A` int(11) NOT NULL auto_increment,

  `D` int(11) NOT NULL default '0',

  `B` varchar(200) NOT NULL default '',

  `C` varchar(175) default NULL,

  PRIMARY KEY  (`A`,`D`,`B`),

  KEY `B` (`B`,`C`),

  KEY `C` (`C`),

  CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)

REFERENCES `ibtest11a` (`A`, `D`)

ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)

REFERENCES `ibtest11a` (`B`, `C`)

ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=INNODB CHARSET=latin1

1 row in set (0.01 sec)

mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;

InnoDB瑙ｆ瀽绋嬪簭鍏佽��浣犲湪FOREIGN KEY ... REFERENCES ...瀛愬彞涓�鐢�`锛坆ackticks)鎶婅〃鍜�鍒楀悕鍚嶅瓧鍥磋捣鏉ャ�侷nnoDB瑙ｆ瀽绋嬪簭涔熻�冭檻鍒發ower_case_table_names绯荤粺鍙橀噺鐨勮�剧疆銆�

InnoDB杩斿洖涓�涓�琛ㄧ殑澶栭敭瀹氫箟浣滀负SHOW CREATE TABLE璇�鍙ヨ緭鍑虹殑涓�閮ㄥ垎锛�

SHOW CREATE TABLE tbl_name;

浠庤繖涓�鐗堟湰璧凤紝mysqldump涔熷皢琛ㄧ殑姝ｇ‘瀹氫箟鐢熸垚鍒拌浆鍌ㄦ枃浠朵腑锛屼笖骞朵笉蹇樿�� 澶栭敭銆�

浣犲彲浠ュ�備笅瀵逛竴涓�琛ㄦ樉绀哄�栭敭绾︽潫锛�

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

澶栭敭绾︽潫琚�鍒楀湪杈撳嚭鐨凜omment鍒椼��

褰撴墽琛屽�栭敭妫�鏌ヤ箣鏃讹紝InnoDB瀵瑰畠鐓х湅鐫�鐨勫瓙鎴栫埗璁板綍璁剧疆鍏变韩鐨勮�岀骇閿併�侷nnoDB绔嬪嵆妫�鏌ュ�栭敭绾︽潫锛屾��鏌ヤ笉瀵逛簨鍔℃彁浜ゅ欢杩熴��

瑕佷娇寰楀�规湁澶栭敭鍏崇郴鐨勮〃閲嶆柊杞藉叆杞�鍌ㄦ枃浠跺彉寰楁洿瀹规槗锛�mysqldump鑷�鍔ㄥ湪杞�鍌ㄨ緭鍑轰腑鍖呮嫭涓�涓�璇�鍙ヨ�剧疆FOREIGN_KEY_CHECKS涓�0銆傝繖閬垮厤鍦ㄨ浆鍌ㄨ��閲嶆柊瑁呰浇涔嬫椂锛屼笌涓嶅緱涓嶈��浠ョ壒鍒�椤哄簭閲嶆柊瑁呰浇鐨勮〃鐩稿叧鐨勯棶棰樸�備篃鍙�浠ユ墜鍔ㄨ�剧疆杩欎釜鍙橀噺锛�

mysql> SET FOREIGN_KEY_CHECKS = 0;

mysql> SOURCE dump_file_name;

mysql> SET FOREIGN_KEY_CHECKS = 1;

濡傛灉杞�鍌ㄦ枃浠跺寘鍚�瀵瑰�栭敭鏄�涓嶆�ｇ‘椤哄簭鐨勮〃锛岃繖灏变互浠讳綍椤哄簭瀵煎叆璇ヨ〃銆傝繖鏍蜂篃鍔犲揩瀵煎叆鎿嶄綔銆傝�剧疆FOREIGN_KEY_CHECKS涓�0锛屽�逛簬鍦↙OAD DATA鍜孉LTER TABLE鎿嶄綔涓�蹇界暐澶栭敭闄愬埗涔熸槸闈炲父鏈夌敤鐨勩��

InnoDB涓嶅厑璁镐綘鍒犻櫎涓�涓�琚獸OREIGN KEY琛ㄧ害鏉� 寮曠敤鐨勮〃锛岄櫎闈炰綘鍋氳�剧疆SET FOREIGN_KEY_CHECKS=0銆傚綋浣犵Щ闄や竴涓�琛ㄧ殑鏃跺�欙紝鍦ㄥ畠鐨勫垱寤鸿��鍙ラ噷瀹氫箟鐨勭害鏉熶篃琚�绉婚櫎銆�

濡傛灉浣犻噸鏂板垱寤轰竴涓�琚�绉婚櫎鐨勮〃锛屽畠蹇呴』鏈変竴涓�閬典粠浜庝篃寮曠敤瀹冪殑澶栭敭绾︽潫鐨勫畾涔夈�傚畠蹇呴』鏈夋�ｇ‘鐨勫垪鍚嶅拰绫诲瀷锛屽苟涓斿�傚墠鎵�杩帮紝瀹冨繀椤诲�硅�� 寮曠敤鐨勯敭鏈夌储寮曘�傚�傛灉杩欎簺涓嶈��婊¤冻锛孧ySQL杩斿洖閿欒��鍙�1005 骞跺湪閿欒��淇℃伅瀛楃�︿覆涓�鎸囧悜errno 150銆�

MySQL澶嶅埗灏卞儚瀵筂yISAM琛ㄤ竴鏍凤紝涔熷�笽nnoDB琛ㄨ捣浣滅敤銆備互鏌愮�嶆柟寮忎娇鐢ㄥ�嶅埗涔熸槸鍙�鑳界殑锛屽湪杩欑�嶆柟寮忎腑浠庢湇鍔″櫒涓婅〃鐨勭被鍨嬩笉鍚屼簬涓绘湇鍔″櫒涓� 鍘熷�嬭〃鐨勭被鍨嬨�備緥濡傦紝浣犲彲浠ュ�嶅埗淇�鏀瑰埌涓绘湇鍔″櫒涓婁竴涓狪nnoDB琛�锛屽埌浠庢湇鍔″櫒涓婁竴涓狹yISAM琛ㄩ噷銆�

瑕佷负涓�涓�涓绘湇鍔″櫒寤虹珛涓�涓�鏂版湇鍔″櫒锛屼綘涓嶅緱涓嶅�嶅埗InnoDB琛ㄧ┖闂村拰鏃ュ織鏂囦欢锛屼互鍙奍nnoDB琛ㄧ殑.frm鏂囦欢锛屽苟涓旂Щ鍔ㄥ�嶄欢鍒颁粠鏈嶅姟鍣ㄣ�� 鍏充簬鍏舵伆褰撴�ラ�よ�峰弬闃�15.2.9鑺傦紝鈥滅Щ鍔↖nnoDB鏁版嵁搴撳埌鍙︿竴鍙版満鍣�”銆�

濡傛灉浣犲彲浠ュ叧闂�涓绘湇鍔″櫒鎴栬�呬竴涓�瀛樺湪鐨勪粠鏈嶅姟鍣ㄣ�備綘鍙�浠ラ噰鍙朓nnoDB琛ㄧ┖闂村拰鏃ュ織鏂囦欢鐨勫喎澶囦唤锛屽苟鐢ㄥ畠鏉ュ缓绔嬩竴涓�浠庢湇鍔″櫒銆傝�佷笉鍏抽棴浠讳綍鏈嶅姟鍣ㄦ潵寤虹珛涓�涓�鏂扮殑浠庢湇鍔″櫒锛屼綘涔熷彲浠ヤ娇鐢ㄩ潪鍏嶈垂锛堝晢涓氱殑锛�InnoDB鐑�澶囦唤宸ュ叿銆�

InnoDB澶嶅埗閲屼竴涓�灏忕殑闄愬埗鏄疞OAD TABLE FROM MASTER涓嶅�笽nnoDB绫诲瀷琛ㄨ捣浣滅敤銆傛湁涓ょ�嶅彲鑳界殑宸ヤ綔鍖猴細

鍦ㄤ富鏈嶅姟鍣ㄥけ璐ョ殑浜嬪姟鏍规湰涓嶅奖鍝嶅�嶅埗銆侻ySQL澶嶅埗鍩轰簬浜岃繘鍒舵棩蹇楋紝MySQ鍦ㄩ偅閲屽啓淇�鏀规暟鎹�鐨凷QL璇�鍙ャ�備粠鏈嶅姟鍣ㄨ�讳富鏈嶅姟鍣ㄧ殑浜岃繘鍒舵棩蹇楋紝骞舵墽琛屽悓鏍风殑SQL璇�鍙ャ�傚敖绠″�傛�わ紝鍦ㄤ簨鍔￠噷鍙戠敓鐨勮��鍙ヤ笉琚�鍐欒繘浜岃繘鍒舵棩蹇楃洿鍒颁簨鍔℃彁浜わ紝鍦ㄩ偅涓�鍒伙紝浜嬪姟閲屾墍鏈夎��鍙ヨ��涓�娆℃�у啓杩涙棩蹇椼�傚�傛灉璇�鍙ュけ璐ヤ簡锛屾瘮濡傦紝鍥犱负澶栭敭杩濅緥锛屾垨鑰咃紝濡傛灉浜嬪姟琚�鍥炴粴锛屾病鏈塖QL璇�鍙ヨ��鍐欒繘浜岃繘鍒舵棩蹇楋紝骞朵笖浜嬪姟鏍规湰涓嶅湪浠庢湇鍔″櫒涓婃墽琛屻��

浣犲彲浠ュ瓨鍌ㄦ瘡涓狪nnoDB琛ㄥ拰瀹冪殑绱㈠紩鍦ㄥ畠鑷�宸辩殑鏂囦欢鍦ㄤ腑锛岃繖涓�鐗瑰緛琚�绉颁负“澶氳〃绌洪棿” 锛屽洜涓哄疄闄呬笂姣忎釜琛ㄦ湁瀹冭嚜宸辩殑琛ㄧ┖闂淬��

瀵归偅浜涙兂鎶婄壒瀹氳〃鏍肩Щ鍒板垎绂荤墿鐞嗙�佺洏鐨勭敤鎴凤紝鎴栬�呴偅浜涘笇鏈涘揩閫熸仮澶嶅崟涓�琛ㄧ殑澶囦唤鑰屾棤椤绘墦鏂�鍏朵綑InnoDB琛ㄧ殑浣跨敤鐨勭敤鎴凤紝浣跨敤澶氳〃绌洪棿浼氭槸鏈夌泭鐨勩��

浣犲彲浠ュ線my.cnf鐨刐mysqld]鑺傛坊鍔犱笅闈㈣�屾潵鍏佽�稿�氳〃绌洪棿锛�

[mysqld]

innodb_file_per_table

閲嶅惎鏈嶅姟鍣ㄤ箣鍚庯紝InnoDB瀛樺偍姣忎釜鏂板垱寤虹殑琛ㄥ埌琛ㄦ牸鎵�灞炰簬鐨勬暟鎹�搴撶洰褰曚笅瀹冭嚜宸辩殑鏂囦欢tbl_name.ibd閲屻�傝繖绫讳技浜嶮yISAM瀛樺偍寮曟搸鎵�鍋氱殑锛屼絾MyISAM 鎶婅〃鍒嗘垚鏁版嵁鏂囦欢tbl_name.MYD鍜岀储寮曟枃浠�tbl_name.MYI銆傚�逛簬InnoDB锛屾暟鎹�鍜屾墍浠ヨ��涓�璧峰瓨鍒�.ibd鏂囦欢銆�tbl_name.frm鏂囦欢鐓ф棫渚濈劧琚�鍒涘缓銆�

濡傛灉浣犱粠my.cnf鏂囦欢鍒犻櫎innodb_file_per_table琛岋紝骞堕噸鍚�鏈嶅姟鍣�锛孖nnoDB鍦ㄥ叡浜�鐨勮〃绌洪棿鏂囦欢閲屽啀娆″垱寤鸿〃銆�

innodb_file_per_table鍙�褰卞搷琛ㄧ殑鍒涘缓銆傚�傛灉浣犵敤杩欎釜閫夐」鍚�鍔ㄦ湇鍔″櫒锛屾柊琛ㄨ��鐢�.ibd鏂囦欢鏉ュ垱寤猴紝浣嗘槸浣犱粛鏃ц兘璁块棶鍦ㄥ叡浜�琛ㄧ┖闂撮噷鐨勮〃銆傚�傛灉浣犲垹鎺夎繖涓�閫夐」锛屾柊琛ㄥ湪鍏变韩琛ㄧ┖闂村唴鍒涘缓锛屼絾浣犱粛鏃у彲浠ヨ�块棶浠讳綍鐢ㄥ�氳〃绌洪棿鍒涘缓鐨勮〃銆�

InnoDB鎬绘槸闇�瑕佸叡浜�鏍囩┖闂淬��.ibd鏂囦欢瀵笽nnoDB涓嶈冻浠ュ幓杩愯�岋紝鍏变韩琛ㄧ┖闂村寘鍚�鐔熸倝鐨刬bdata鏂囦欢锛孖nnoDB鎶婂唴閮ㄦ暟鎹�璇嶅吀鍜屾湭浣滄棩蹇楁斁鍦ㄨ繖涓�鏂囦欢涓�銆�

娉ㄩ噴锛�浣犱笉鑳� 鍍忓�筂yISAM涓�鏍凤紝鍦ㄦ暟鎹�鐩�褰曚箣闂撮殢鎰忓湴绉诲姩.ibd鏂囦欢銆傝繖鏄�鍥犱负琛ㄥ畾涔夎��瀛樺湪InnoDB鍏变韩琛ㄧ┖闂村唴锛岃�屼笖InnoDB蹇呴』淇濇寔浜嬪姟ID鍜屾棩蹇楅『搴忓彿鐨勪竴鑷存�с��

鍦ㄤ竴涓�缁欏畾鐨凪ySQL瀹夎�呴噷锛屼綘鍙�浠ョ敤RENAME TABLE璇�鍙ユ妸.ibd鏂囦欢鍜屽叧鑱旂殑琛ㄤ粠涓�涓�鏁版嵁搴撶Щ鍒板彟涓�涓�鏁版嵁搴擄細

RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;

濡傛灉浣犳湁.ibd鏂囦欢鐨勪竴涓�骞插噣鐨勫�囦唤锛屼綘鍙�浠ユ寜濡備笅鎿嶄綔浠庤��璧锋簮鐨勫湴鏂规仮澶嶅畠鍒癕ySQL瀹夎�呬腑锛�

2.      ALTER TABLE tbl_name DISCARD TABLESPACE;

5.      ALTER TABLE tbl_name IMPORT TABLESPACE;

鍦ㄤ笂涓嬫枃涓�锛屼竴涓�.ibd鏂囦欢骞插噣鐨勫�囦唤鎰忎负锛�

浣犲彲浠ョ敤涓嬪垪鏂规硶鐢熸垚涓�涓�.ibd鏂囦欢鐨勫共鍑�澶囦唤锛�

鐢熸垚涓�涓�.ibd鏂囦欢鐨勫共鍑�澶嶅埗鐨勫彟涓�涓�鏂规硶鏄�浣跨敤鍟嗕笟鐨�InnoDB鐑�澶囦唤宸ュ叿锛�

1.    浣跨敤mysqldump杞�鍌ㄦ墍鏈塈nnoDB琛ㄣ��

2.    鍋滄�㈡湇鍔″櫒銆�

3.    鍒犻櫎鎵�鏈夊凡瀛樺湪鐨勮〃绌洪棿鏂囦欢銆�

4.    閰嶇疆鏂拌〃绌洪棿銆�

5.    閲嶅惎鏈嶅姟鍣ㄣ��

6.    瀵煎叆杞�鍌ㄦ枃浠躲��

15.2.8.1. 寮哄埗鎭㈠��

15.2.8.2. 妫�鏌ョ偣

1.    鍏抽棴MySQL鏈嶅姟鍣�锛岀‘淇″畠鏄�鏃犻敊璇�鍏抽棴銆�

2.  澶嶅埗浣犳墍鏈夋暟鎹�鏂囦欢锛坕bdata鏂囦欢鍜�.ibd鏂囦欢锛夊埌涓�涓�瀹夊叏鐨勫湴鏂广�� 

3.   澶嶅埗浣犳墍鏈塱b_logfile鏂囦欢鍒颁竴涓�瀹夊叏鐨勫湴鏂广��

4.    澶嶅埗my.cnf閰嶇疆鏂囦欢鎴栨枃浠跺埌涓�涓�瀹夊叏鐨勫湴鏂广��

5.    涓轰綘InnoDB琛ㄥ�嶅埗.frm鏂囦欢鍒颁竴涓�瀹夊叏鐨勫湴鏂广��

濡傛灉鏁版嵁搴撻〉琚�鐮村潖锛屼綘鍙�鑳芥兂瑕佺敤SELECT INTO OUTFILE浠庝粠鏁版嵁搴撹浆鍌ㄤ綘鐨勮〃锛岄�氬父浠ヨ繖绉嶆柟娉曡幏鍙栫殑澶у�氭暟鏁版嵁鏄�瀹屽ソ鐨勩�傚嵆浣胯繖鏍凤紝鎹熷潖鍙�鑳藉�艰嚧SELECT * FROM tbl_name鎴栬�匢nnoDB鍚庡彴鎿嶄綔宕╂簝鎴栨柇瑷�锛屾垨鑰呯敋鑷充娇寰桰nnoDB鍓嶆粴鎭㈠�嶅穿婧冦�� 灏界�″�傛�わ紝浣犲彲浠ョ敤瀹冩潵寮哄埗InnoDB瀛樺偍寮曟搸鍚�鍔ㄥ悓鏃堕樆姝㈠悗鍙版搷浣滆繍琛岋紝浠ヤ究浣犺兘杞�鍌ㄤ綘鐨勮〃銆備緥濡傦細浣犲彲浠ュ湪閲嶅惎鏈嶅姟鍣ㄤ箣鍓嶏紝鍦ㄩ�夐」鏂囦欢鐨刐mysqld]鑺傛坊鍔犲�備笅鐨勮�岋細

[mysqld]

innodb_force_recovery = 4

innodb_force_recovery琚�鍏佽�哥殑闈為浂鍊煎�備笅銆備竴涓�鏇村ぇ鐨勬暟瀛楀寘鍚�鎵�鏈夋洿灏忔暟瀛楃殑棰勯槻鎺�鏂姐�傚�傛灉浣犺兘澶熺敤涓�涓�澶氭暟鏄�4鐨勯�夐」鍊兼潵杞�鍌ㄤ綘鐨勮〃锛岄偅涔堜綘鏄�姣旇緝瀹夊叏鐨勶紝鍙�鏈変竴浜涘湪鎹熷潖鐨勫崟鐙�椤甸潰涓婄殑鏁版嵁浼氫涪澶便�備竴涓�涓�6鐨勫�兼洿澶稿紶锛屽洜涓烘暟鎹�搴撻〉琚�鐣欏湪涓�涓�闄堟棫鐨勭姸鎬侊紝杩欎釜鐘舵�佸弽杩囨潵鍙�浠ュ紩鍙戝�笲鏍戝拰鍏跺畠鏁版嵁搴撶粨鏋勭殑鏇村�氱牬鍧忋��

鏁版嵁搴撲笉鑳藉彟澶栧湴甯︾潃杩欎簺閫夐」涓�琚�鍏佽�哥殑閫夐」鏉ヤ娇鐢ㄣ��浣滀负涓�涓�瀹夊叏鎺�鏂斤紝褰搃nnodb_force_recovery琚�璁剧疆 涓哄ぇ浜�0鐨勫�兼椂锛孖nnoDB闃绘�㈢敤鎴锋墽琛孖NSERT, UPDATE鎴朌ELETE鎿嶄綔.

鍗充娇寮哄埗鎭㈠�嶈��浣跨敤锛屼綘涔熷彲浠�DROP鎴朇REATE琛ㄣ�傚�傛灉浣犵煡閬撲竴涓�缁欏畾鐨勮〃姝ｅ湪瀵艰嚧鍥炴粴宕╂簝锛屼綘鍙�浠ョЩ闄ゅ畠銆備綘涔熷彲浠ョ敤杩欎釜鏉ュ仠姝㈢敱澶辫触鐨勫ぇ瀹楀�煎叆鎴栧け璐ョ殑ALTER TABLE瀵艰嚧鐨勫け鎺� 鍥炴粴銆備綘鍙�浠ユ潃鎺�mysqld杩涚▼锛岀劧鍚庤�剧疆innodb_force_recovery涓�3锛屼娇寰楁暟鎹�搴撹�� 鎸傝捣鑰屼笉闇�瑕佸洖婊氾紝鐒跺悗鑸嶅純瀵艰嚧澶辨帶鍥炴粴鐨勮〃銆�

InnoDB瀹炵幇涓�绉嶈��璁よ瘑涓�“妯＄硦”妫�鏌ョ偣璁剧疆鐨勬��鏌ョ偣鏈哄埗銆侷nnoDB浠ュ皬鎵归噺浠庣紦鍐叉睜 鍒锋柊宸蹭慨鏀圭殑鏁版嵁搴撻〉銆傛病蹇呰�佷互鍗曚釜鎵规�″埛鏂扮紦鍐叉睜锛屽崟鎵规�″埛鏂板疄闄呮搷浣滀腑鍙�鑳戒細鍦ㄦ��鏌ョ偣璁剧疆杩涚▼涓�鍋滄�㈢敤鎴稴QL璇�鍙ョ殑澶勭悊銆�

鍦ㄥ穿婧冩仮澶嶄腑锛孖nnoDB鎵惧�昏��鍐欒繘鏃ュ織鐨勬��鏌ョ偣鏍囩�俱�傚畠鐭ラ亾鎵�鏈夊湪璇ユ爣绛句箣鍓嶅�规暟鎹�搴撶殑淇�鏀硅��鍛堢幇鍦ㄦ暟鎹�搴撶殑纾佺洏鏄犲儚涓�銆傜劧鍚嶪nnoDB浠庢��鏌ョ偣寰�鍓嶆壂鎻忔棩蹇楁枃浠讹紝瀵规暟鎹�搴撳簲鐢ㄥ凡鍐欏叆鏃ュ織鐨勪慨鏀广��

InnoDB浠ュ惊鐜�鏂瑰紡鍐欐棩蹇楁枃浠躲�傛墍鏈変娇寰楃紦鍐叉睜閲岀殑鏁版嵁搴撻〉涓庣�佺洏涓婄殑鏄犲儚涓嶅悓鐨勫凡鎻愪氦淇�鏀瑰繀椤诲嚭鐜板湪鏃ュ織鏂囦欢涓� 锛屼互澶囦竾涓�InnoDB闇�瑕佸仛涓�涓�鎭㈠�嶃�傝繖鎰忓懗鐫�锛屽綋InnoDB寮�濮嬮噸鏂颁娇鐢ㄤ竴涓�鏃ュ織鏂囦欢锛屽畠闇�瑕佺‘璁ゅ湪纾佺洏涓婄殑鏁版嵁搴撻〉鏄犲儚鍖呭惈宸插啓杩汭nnoDB鍑嗗�囬噸鏂颁娇鐢ㄧ殑鏃ュ織鏂囦欢閲岀殑淇�鏀广�傛崲鍙ヨ瘽 璇达紝InnoDB蹇呴』鍒涘缓涓�涓�妫�鏌ョ偣锛岃繖缁忓父娑夊強宸蹭慨鏀� 鏁版嵁搴撻〉鍒扮�佺洏鐨勫埛鏂般��

鍓嶉潰鐨勫彊杩拌В閲婁簡涓轰粈涔堜娇浣犵殑鏃ュ織鏂囦欢闈炲父澶т細鍦ㄨ�剧疆妫�鏌ョ偣涓�鑺傜害纾佺洏I/O銆傝�剧疆鏃ュ織鏂囦欢鎬荤殑澶у皬鍜岀紦鍐叉睜涓�鏍峰ぇ鎴栬�呯敋鑷虫瘮缂撳啿姹犲ぇ閫氬父鏄�鏈夋剰涔夌殑銆傚ぇ鏃ュ織鏂囦欢鐨勭己鐐规槸宕╂簝鎭㈠�嶈�佽姳鏇撮暱鐨勬椂闂达紝鍥犱负鏈夋洿澶氬啓鍏ユ棩蹇楃殑淇℃伅瑕佸簲鐢ㄥ埌鏁版嵁搴撲笂銆�

15.2.10.1. InnoDB閿佸畾妯″紡

15.2.10.2. InnoDB鍜�AUTOCOMMIT

15.2.10.3. InnoDB鍜�TRANSACTION ISOLATION LEVEL

15.2.10.4. 鎸佺画闈為攣瀹氳��

15.2.10.5. 閿佸畾璇籗ELECT ... FOR UPDATE鍜孲ELECT ... LOCK IN SHARE MODE

15.2.10.6. Next-Key閿佸畾锛氶伩鍏嶅尓澶锋墍鎬濈殑闂�棰�

15.2.10.7. 鎸佺画璇诲�備綍鍦�InnoDB涓�浣滅敤鐨勪緥瀛�

15.2.10.8. 鍦�InnoDB涓�鐢ㄤ笉鍚岀殑SQL璇�鍙ヨ�惧畾閿�

15.2.10.9. MySQL浠�涔堟椂鍊欐彁浜ゆ垨鍥炴粴涓�涓�浜嬪姟锛�

15.2.10.10. 姝婚攣妫�娴嬶紗鍥炴粴

15.2.10.11. 濡備綍搴斿�规�婚攣

InnoDB瀹炵幇鏍囧噯琛岀骇閿佸畾锛屽湪杩欓噷鏈変袱绉嶇被鍨嬬殑閿侊細 locks:

濡傛灉浜嬪姟A 鍦╰uple t涓婃寔鏈夌嫭鍗犻攣瀹氾紝鏉ヨ嚜涓嶅悓浜嬪姟B鐨勫��t涓婁换涓�绫诲瀷鐨勯攣鐨勮�锋眰涓嶈��椹�涓婅�稿彲锛屽彇鑰屼唬涔嬪湴锛屼簨鍔�B 涓嶅緱涓嶇瓑寰呬簨鍔�t閲婃斁鍦╰uple t涓婄殑閿併��

濡傛灉浜嬪姟 A 鍦╰uple t涓婃寔鏈変竴涓�鍏变韩鐨勯攣(S)锛岄偅涔�

涓嶄粎濡傛�わ紝InnoDB鏀�鎸�澶氶棿闅斿昂瀵搁攣瀹�锛屽畠鍏佽�歌�板綍閿佸拰瀵规暣涓�琛ㄧ殑閿佸叡瀛樸�傝�佷娇寰楀�氶棿闅斿昂瀵哥骇鍒�鐨勯攣瀹氬疄闄呭寲锛岄�濆�栫被鍨嬬殑閿侊紝琚�绉颁负intention locks琚�浣跨敤銆傚湪InnoDB涓�锛屾剰鍥鹃攣瀹氭槸琛ㄩ攣瀹氥�� 瀵逛簬涓�涓�浜嬪姟锛屾剰鍥鹃攣瀹氫箣鍚庣悊鎯崇殑鏄�鎸囨槑鍦ㄨ�ヨ〃涓�瀵逛竴涓�琛岄殢鍚庨渶瑕佸摢涓�绫诲瀷鐨勯攣瀹氾紙鍏变韩杩樻槸鐙�鍗狅級銆傛湁涓ょ�嶆剰鍥鹃攣琚�鐢ㄥ湪InnoDB涓�锛堝亣璁句簨鍔�T 鍦ㄨ〃R涓�瑕佹眰涓�涓�宸叉寚鍑虹殑绫诲瀷鐨勯攣锛夛細

鎰忓浘閿佸崗璁�濡備笅锛�

杩欎簺缁撴灉鍙�浠ユ柟渚垮湴鐢ㄤ竴涓�閿佺被鍨嬪吋瀹圭煩闃垫潵鎬荤粨锛�

濡傛灉涓�涓�閿佸畾涓庣幇鍦ㄩ攣瀹氬吋瀹圭殑璇濓紝瀹冭��鎺堢粰涓�涓�濮旀墭浜嬪姟銆傚�傛灉涓�涓�閿佸畾涓庣幇瀛橀攣瀹氬啿绐侊紝瀹冨氨涓嶈��鎺堜簣涓�涓�濮旀墭浜嬪姟銆備簨鍔＄瓑寰呯潃鐩村埌鍐茬獊鐨勭幇瀛橀攣瀹氳��閲婃斁鎺夈�傚�傛灉涓�涓�閿佸畾璇锋眰涓庣幇瀛橀攣瀹氱浉鍐茬獊锛屼笖涓嶈兘琚�鎺堜簣锛屽洜涓哄畠鍙�鑳戒細瀵艰嚧姝婚攣锛屼竴涓�閿欒��浜х敓銆�

鍥犳�わ紝鎰忓浘閿佸畾涓嶉樆纰嶄换浣曚笢瑗匡紝闄や簡瀹屽叏琛ㄨ�锋眰锛堟瘮濡侺OCK TABLES ... WRITE锛夈��IX 鍜�IS閿佸畾鐨勪富瑕佺洰鐨勬槸鏄剧ず鏌愪汉姝ｉ攣瀹氫竴琛岋紝鎴栧皢瑕佸湪琛ㄤ腑閿佸畾涓�琛屻��

涓嬪垪鐨勪緥瀛愭紨绀哄綋閿佸畾璇锋眰鍙�鑳戒細瀵艰嚧姝婚攣涔嬫椂涓�涓�閿欒��浼氬�備綍鍙戠敓銆備緥瀛愪腑鍖呮嫭涓や釜瀹㈡埛绔疉鍜孊銆�

棣栧厛瀹㈡埛绔疉鍒涘缓涓�涓�鍖呭惈涓�涓�琛岀殑琛�锛岀劧鍚庡紑濮嬩竴涓�浜嬪姟銆傚湪杩欎釜浜嬪姟鍐咃紝A閫氳繃鍦ㄥ叡浜�妯″紡閫夋嫨琛岃幏寰楀�硅�岀殑S 閿佸畾锛�

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;

Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);

Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;

+------+

| i    |

+------+

|    1 |

+------+

1 row in set (0.10 sec)

鎺ョ潃锛屽�㈡埛绔疊寮�濮嬩竴涓�浜嬪姟骞跺皾璇曚粠璇ヨ〃鍒犻櫎琛岋細

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

鍒犻櫎鎿嶄綔瑕佹眰涓�涓�X 閿佸畾銆傚洜涓鸿繖涓�閿佸畾涓嶅吋瀹瑰�㈡埛绔疉鎸佹湁鐨�S閿佸畾锛屾墍浠�X 閿佸畾涓嶈�� 鍏佽�革紝鎵�浠ヨ�锋眰杩涘叆瀵硅�屽強瀹㈡埛绔�闃绘尅鐨勯攣瀹氳�锋眰闃熷垪銆�

鏈�鍚庯紝瀹㈡埛绔疉涔熻瘯鍥句粠琛ㄤ腑鍒犻櫎璇ヨ�岋細

mysql> DELETE FROM t WHERE i = 1;

ERROR 1213 (40001): Deadlock found when trying to get lock;

try restarting transaction

鍥犱负瀹㈡埛绔疉闇�瑕佷竴涓�X 閿佸畾鏉ュ垹闄よ�ヨ�岋紝鎵�浠ュ湪杩欓噷鍙戠敓姝婚攣銆傚敖绠″�傛�わ紝閿佸畾璇锋眰涓嶈��鍏佽�革紝鍥犱负瀹㈡埛绔疊宸茬粡鏈変竴涓�瀵�X閿佸畾鐨勮�锋眰骞朵笖瀹冩�ｇ瓑寰呭�㈡埛绔疉閲婃斁S閿佸畾銆傚洜涓哄�㈡埛绔疊涔嬪墠瀵�X 閿佸畾鐨勮�锋眰锛岃��瀹㈡埛绔疉鎸佹湁鐨�S閿佸畾涔熶笉鑳藉崌绾у埌X閿佸畾銆傚洜姝わ紝InnoDB瀵瑰�㈡埛绔疉浜х敓涓�涓�閿欒��锛屽苟涓旈噴鏀惧畠鐨勯攣瀹氥�傚湪閭ｄ竴鐐逛笂锛屽�㈡埛绔疊鐨勯攣瀹氳�锋眰鍙�浠ヨ��璁稿彲锛屽苟涓斿�㈡埛绔疊浠庤〃涓�鍒犻櫎琛屻��

鍦↖nnoDB涓�锛屾墍鏈夌敤鎴疯�屼负閮藉湪浜嬪姟鍐呭彂鐢熴�傚�傛灉鑷�鍔ㄦ彁浜ゆā寮忚��鍏佽�革紝姣忎釜SQL璇�鍙ュ湪瀹冭嚜宸变笂褰㈡垚涓�涓�鍗曠嫭鐨勪簨鍔°�侻ySQL鎬绘槸甯︾潃鍏佽�歌嚜鍔ㄦ彁浜ゆ潵寮�濮嬩竴涓�鏂拌繛鎺ャ��

濡傛灉鑷�鍔ㄦ彁浜ゆā寮忚��鐢⊿ET AUTOCOMMIT = 0鍏抽棴锛岄偅涔堟垜浠�鍙�浠ヨ�や负涓�涓�鐢ㄦ埛鎬绘槸鏈変竴涓�浜嬪姟鎵撳紑鐫�銆備竴涓猄QL COMMIT鎴朢OLLBACK璇�鍙ョ粨鏉熷綋鍓嶄簨鍔″苟涓斾竴涓�鏂颁簨鍔″紑濮嬨�備袱涓�璇�鍙ラ兘閲婃斁鎵�鏈夊湪褰撳墠浜嬪姟涓�琚�璁剧疆鐨処nnoDB閿佸畾銆備竴涓狢OMMIT璇�鍙ユ剰鍛崇潃鍦ㄥ綋鍓嶄簨鍔′腑鍋氱殑鏀瑰彉琚�鐢熸垚涓烘案涔呯殑锛屽苟涓斿彉鎴愬叾瀹冪敤鎴峰彲瑙佺殑銆備竴涓猂OLLBACK璇�鍙ワ紝鍦ㄥ彟涓�鏂归潰锛屾挙閿�鎵�鏈夊綋鍓嶄簨鍔″仛鐨勪慨鏀广��

濡傛灉杩炴帴鏈夎��鍏佽�哥殑鑷�鍔ㄦ彁浜わ紝閫氳繃鐢ㄦ槑纭�鐨凷TART TRANSACTION鎴朆EGIN璇�鍙ユ潵寮�濮嬩竴涓�浜嬪姟锛屽苟鐢–OMMIT鎴栬�匯OLLBACK璇�鍙ユ潵缁撴潫瀹冿紝杩欐牱鐢ㄦ埛浠嶆棫鍙�浠ユ墽琛屼竴涓�澶氶噸璇�鍙ヤ簨鍔°��

鎸夌収SQL:1992 浜嬪姟闅旂�荤骇鍒�锛孖nnoDB榛樿�ゆ槸鍙�閲嶅�嶈�荤殑锛圧EPEATABLE READ锛夈�侻ySQL/InnoDB 鎻愪緵SQL鏍囧噯鎵�鎻忚堪鐨勬墍鏈夊洓涓�浜嬪姟闅旂�荤骇鍒�銆備綘鍙�浠ュ湪鍛戒护琛岀敤--transaction-isolation閫夐」锛屾垨鍦ㄩ�夐」鏂囦欢閲岋紝涓烘墍鏈夎繛鎺ヨ�剧疆 榛樿�ら殧绂荤骇鍒�銆備緥濡傦紝浣犲彲浠ュ湪my.inf鏂囦欢鐨刐mysqld]鑺傞噷绫讳技濡備笅璁剧疆璇ラ�夐」锛歡lobally

[mysqld]

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED

                         | REPEATABLE-READ | SERIALIZABLE}

鐢ㄦ埛鍙�浠ョ敤SET TRANSACTION璇�鍙ユ敼鍙樺崟涓�浼氳瘽鎴栬�呮墍鏈夋柊杩涜繛鎺ョ殑闅旂�荤骇鍒�銆傚畠鐨勮��娉曞�備笅锛�

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL

                       {READ UNCOMMITTED | READ COMMITTED

                        | REPEATABLE READ | SERIALIZABLE}

娉ㄦ剰锛屽��--transaction-isolation閫夐」鐨勭骇鍒�鍚嶄腑鏈夎繛瀛楃�︼紝浣嗗湪瀵筍ET TRANSACTION璇�鍙ョ殑绾у埆鍚嶄腑娌℃湁銆�

榛樿�ょ殑琛屼负鏄�涓轰笅涓�涓�锛堟湭寮�濮嬶級浜嬪姟璁剧疆闅旂�荤骇鍒�銆傚�傛灉浣犱娇鐢℅LOBAL鍏抽敭瀛楋紝璇�鍙ュ湪鍏ㄥ眬瀵逛粠閭ｇ偣寮�濮嬪垱寤虹殑鎵�鏈夋柊杩炴帴锛堥櫎浜嗕笉瀛樺湪鐨勮繛鎺ワ級璁剧疆 榛樿�や簨鍔＄骇鍒�銆備綘闇�瑕丼UPER鍏ㄥ幙鏉ュ仛杩欎釜銆備娇鐢⊿ESSION 鍏抽敭瀛楅泦涓哄皢鏉ュ湪褰撳墠杩炴帴涓婃墽琛岀殑浜嬪姟璁剧疆榛樿�や簨鍔＄骇鍒� 銆�

浠讳綍瀹㈡埛绔�閮借兘鑷�鐢辨敼鍙樹細璇濋殧绂荤骇鍒�锛堢敋鑷冲湪浜嬪姟鐨勪腑闂达級锛屾垨鑰呬负涓嬩竴涓�浜嬪姟璁剧疆闅旂�荤骇鍒�銆�

浣犲彲浠ョ敤涓嬪垪璇�鍙ユ煡璇㈠叏灞�鍜屼細璇濅簨鍔￠殧绂荤骇鍒�锛�

SELECT @@global.tx_isolation;

SELECT @@tx_isolation;

鍦ㄨ�岀骇閿佸畾涓�锛孖nnoDB浣跨敤next-key閿佸畾銆傝繖鎰忓懗鐫�闄や簡绱㈠紩璁板綍锛孖nnoDB涔熷彲浠ユ妸绱㈠紩璁板綍鍓嶇殑闂撮殭閿佸畾鍒板叾瀹冪敤鎴锋墍鍋氱殑绱ф帴璇ョ储寮曡�板綍涔嬪墠鐨勫潡鎻掑叆涓娿�備竴涓猲ext-key閿佸畾鎸囧悜涓�涓�閿佸畾浣忎竴涓�绱㈠紩璁板綍鍜屽畠涔嬪墠鐨勯棿闅欑殑閿佸畾銆備竴涓�闂撮殭閿佸畾鎸囦粎閿佷綇涓�浜涚储寮曡�板綍涔嬪墠鐨勯棿闅欑殑閿佸畾銆�

InnoDB涓�姣忎釜闅旂�荤骇鍒�鐨勮�︾粏鎻忚堪濡備笅锛�

鎸佺画璇绘剰鍛崇潃InnoDB浣跨敤瀹冪殑澶氱増鏈�鍖栨潵缁欎竴涓�鏌ヨ�㈠睍绀烘煇涓�鏃堕棿鐐瑰�勬暟鎹�搴撶殑蹇�鐓с�傛煡璇㈢湅鍒板湪閭ｄ釜鏃堕棿鐐逛箣鍓嶈��鎻愪氦鐨勯偅浜涚‘鍒囦簨鍔″仛鐨勬洿鏀癸紝骞朵笖娌℃湁鍏跺悗鐨勪簨鍔℃垨鏈�鎻愪氦浜嬪姟鍋氱殑鏀瑰彉銆傝繖涓�瑙勫垯鐨勪緥澶栨槸锛屾煡璇㈢湅鍒板彂甯冭�ユ煡璇㈢殑浜嬪姟鏈�韬�鎵�鍋氱殑鏀瑰彉銆�

濡傛灉浣犺繍琛屽湪榛樿�ょ殑REPEATABLE READ闅旂�荤骇鍒�锛屽垯鍦ㄥ悓涓�浜嬪姟鍐呯殑鎵�鏈夋寔缁�璇昏�诲彇鐢辫�ヤ簨鍔′腑绗�涓�涓�杩欐牱鐨勮�绘墍纭�绔嬬殑蹇�鐓с�備綘鍙�浠ラ�氳繃鎻愪氦褰撳墠浜嬪姟骞跺湪鍙戝竷鏂版煡璇㈢殑浜嬪姟涔嬪悗锛屼负浣犵殑鏌ヨ�㈣幏寰椾竴涓�鏇存柊椴滅殑蹇�鐓с��

鎸佺画璇绘槸榛樿�ゆā寮忥紝鍦ㄥ叾涓璉nnoDBzai鍦≧EAD COMMITTED鍜孯EPEATABLE READ闅旂�荤骇鍒�澶勭悊SELECT璇�鍙ャ�傛寔缁�璇讳笉鍦ㄤ换浣曞畠璁块棶鐨勮〃涓婅�剧疆閿佸畾锛屽洜姝わ紝鍏跺畠鐢ㄦ埛鍙�鑷�鐢卞湴鍦ㄦ寔缁�璇诲湪涓�涓�琛ㄤ笂鎵ц�岀殑鍚屼竴鏃堕棿淇�鏀硅繖浜涜〃銆�

娉ㄦ剰锛屾寔缁�璇讳笉鍦―ROP TABLE鍜孉LTER TABLE涓婁綔鐢ㄣ�傛寔缁�璇讳笉鍦―ROP TABLE涓婁綔鐢�锛屽洜涓篗ySQL涓嶈兘浣跨敤宸茬粡琚�绉婚櫎鐨勮〃锛屽苟涓擨nnoDB 鐮村潖浜嗚�ヨ〃銆傛寔缁�璇讳笉鍦ˋLTER TABLE涓婁綔鐢�锛屽洜涓哄畠鍦ㄦ煇浜嬪姟鍐呮墽琛岋紝璇ヤ簨鍔″垱寤轰竴涓�鏂拌〃锛屽苟涓斾粠鏃ц〃寰�鏂拌〃涓�鎻掑叆琛屻�傜幇鍦�锛屽綋浣犻噸鏂板彂鍑烘寔缁�璇讳箣鏃讹紝瀹冧笉鑳藉湪鏂拌〃涓�鐪嬭�佷换浣曡�岋紝鍥犱负瀹冧滑琚�鎻掑叆鍒颁竴涓�鍦ㄦ寔缁�璇昏�诲彇鐨勫揩鐓т腑涓嶅彲瑙佺殑浜嬪姟 閲屻��

鍦ㄤ竴浜涚幆澧冧腑锛屼竴涓�鎸佺画璇绘槸涓嶆柟渚跨殑銆傛瘮濡傦紝浣犲彲鑳芥兂瑕佸線琛ㄧ殑瀛愯〃閲屾坊鍔犱竴涓�鏂拌�岋紝骞剁‘淇¤�ュ瓙琛ㄥ湪鐖惰〃涓�鏈変竴涓�鏍广�備笅鍒椾緥瀛愭樉绀哄�備綍鍦ㄤ綘搴旂敤绋嬪簭浠ｇ爜涓�瀹炵幇鍙傝�冪殑瀹屾暣鎬с��

鍋囪�句綘浣跨敤涓�涓�鎸佺画璇诲幓璇诲彇鐖惰〃骞朵笖鐪嬪埌琛ㄤ腑瀛愯〃鐨勬牴銆備笉鑳藉畨鍏ㄥ湴寰�瀛愯〃娣诲姞瀛愯�屽悧锛熶笉锛屽洜涓哄彲鑳藉悓鏃跺彂鐢熶竴浜涘叾瀹冪敤鎴蜂粠鐖惰〃鍒犻櫎鐖惰�岋紝鑰屼綘娌℃湁娉ㄦ剰鍒板畠鐨勬儏鍐点��

瑙ｅ喅鍔炴硶鏄�鍦ㄤ娇鐢↙OCK IN SHARE MODE鐨勯攣瀹氭ā寮忔墽琛孲ELECT锛�

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

鍦ㄥ叡浜�妯″紡鎵ц�屼竴涓�璇绘剰鍛崇潃鎴戜滑璇绘渶鏂扮殑鍙�鐢ㄦ暟鎹�锛屽苟鍦ㄦ垜浠�璇荤殑琛岃�剧疆涓�涓�鍏变韩閿佸畾銆傚叡浜�妯″紡閿侀槻姝㈠叾瀹冧汉鏇存柊鎴栧垹闄ゆ垜浠�宸茶�荤殑琛屻�傚悓鏃讹紝濡傛灉鏈�鏂扮殑鏁版嵁灞炰簬鍏跺畠瀹㈡埛绔�灏氭湭鎻愪氦鐨勪簨鍔★紝鎴戜滑绛夌潃鐭ラ亾閭ｄ釜浜嬪姟琚�鎻愪氦銆傛垜浠�鐪嬪埌鍓嶈堪鐨勬煡璇㈣繑鍥炵埗'Jones'锛屾垜浠�鍙�浠ュ畨鍏ㄥ湴 寰�瀛愯〃娣诲姞瀛愯�板綍骞舵彁浜ゆ垜浠�鐨勪簨鍔°��

璁╂垜浠�鐪嬪彟澶栦竴涓�渚嬪瓙锛氭垜浠�鍦ㄨ〃child_codes 涓�鏈変竴涓�鏁存暟璁℃暟鍣ㄥ煙锛屾垜浠�鐢ㄨ�ヨ〃缁欐瘡涓�娣诲姞鍒板瓙琛ㄩ噷鐨勫瓙椤规寚娲句竴涓�鍞�涓�鐨勮瘑鍒�绗︺�傛樉鐒讹紝浣跨敤鎸佺画璇绘垨鑰呭叡浜�妯″紡璇诲幓璇诲彇褰撳墠璁℃暟鍣ㄧ殑鍊煎苟鏄�涓�涓�濂戒富鎰忥紝 鍥犱负鏁版嵁搴撶殑涓や釜鐢ㄦ埛鍙�鑳界湅鍒拌�℃暟鍣ㄧ殑鍚屼竴涓�鍊硷紝濡傛灉涓や釜鐢ㄦ埛璇曠潃鐢ㄥ悓涓�璇嗗埆绗﹀線璇ヨ〃娣诲姞瀛愰」锛屽氨浼氬彂鐢熶竴涓�閲嶅�嶉敭锛坉uplicate-key锛夐敊璇�銆�

鍦ㄨ繖閲岋紝濡傛灉涓や釜鐢ㄦ埛鍚屾椂璇昏�℃暟鍣�锛屽綋璇曞浘鏇存柊璁℃暟鍣ㄤ箣鏃讹紝鑷冲皯瀹冧滑涓�鏈変竴涓�浼氬彂鐢熸�婚攣閿欒��骞剁粓姝�锛屽洜姝�LOCK IN SHARE MODE骞朵笉鏄�涓�涓�濂界殑瑙ｅ喅鏂规硶銆�

鍦ㄨ繖绉嶆儏鍐典笅锛屾湁涓や釜濂芥柟娉曞幓瀹炵幇璇昏�℃暟鍣ㄥ拰澧為暱璁℃暟鍣ㄥ�硷細(1) 鍏堟洿鏂拌�℃暟鍣�锛岃�╄�℃暟鍣ㄥ�煎��1锛屼箣鍚庤�昏�℃暟鍣�锛屾垨鑰�(2)鐢ㄩ攣瀹氭ā寮廎OR UPDATE鍏堣�昏�℃暟鍣�锛屼箣鍚庤�℃暟鍣ㄥ�煎�炲姞銆傚悗涓�涓�閫斿緞鍙�琚�濡備笅瀹炵幇锛�

SELECT counter_field FROM child_codes FOR UPDATE;

UPDATE child_codes SET counter_field = counter_field + 1;

SELECT ... FOR UPDATE璇绘渶鏂扮殑鍙�瑙佹暟鎹�锛屽湪姣忎釜瀹冭�诲彇鐨勮�岃�剧疆鐙�鍗犻攣瀹氥�傚洜姝わ紝瀹冭�剧疆涓庢悳绱㈢殑SQL UPDATE鍙�鑳戒細鍦ㄨ�屼笂璁剧疆鐨勯攣瀹氬悓鏍风殑閿佸畾銆�

璇锋敞鎰忥紝浠ヤ笂浠呮槸涓�涓猄ELECT ... FOR UPDATE濡備綍璧蜂綔鐢ㄧ殑渚嬪瓙銆傚湪MySQL涓�锛屼簨瀹炰笂鐢熸垚涓�涓�鍞�涓�璇嗗埆绗︾殑鐗规畩浠诲姟鍙�琚�鐢ㄥ�硅�ヨ〃鐨勫崟鐙�璁块棶鏉ュ畬鎴愶細

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);

SELECT LAST_INSERT_ID();

SELECT璇�鍙ヤ粎浠呭彇鍥炶瘑鍒�绗︿俊鎭�锛堜笓闂ㄥ�瑰綋鍓嶈繛鎺ワ級銆傚畠涓嶈�块棶浠讳綍琛ㄣ��

鍦ㄨ�岀骇閿佸畾涓�锛孖nnoDB 浣跨敤涓�涓�鍚嶄负next-key locking鐨勭畻娉曘�侷nnoDB浠ヨ繖鏍蜂竴绉嶆柟寮忔墽琛岃�岀骇閿佸畾锛氬綋瀹冩悳绱㈡垨鎵�鎻忚〃鐨勭储寮曚箣鏃讹紝瀹冨�归亣鍒扮殑绱㈠紩璁板綍璁剧疆鍏变韩鎴栫嫭鍗犻攣瀹氥�傚洜姝わ紝琛岀骇閿佸畾浜嬪疄涓婃槸绱㈠紩璁板綍閿佸畾銆�

InnoDB瀵圭储寮曡�板綍璁剧疆鐨勯攣瀹氫篃鏄犲儚绱㈠紩璁板綍涔嬪墠鐨�“闂撮殭”銆傚�傛灉涓�涓�鐢ㄦ埛瀵逛竴涓�绱㈠紩涓婄殑璁板綍R鏈夊叡浜�鎴栫嫭鍗犵殑閿佸畾锛屽彟涓�涓�鐢ㄦ埛 涓嶈兘绱ф帴鍦≧涔嬪墠浠ョ储寮曠殑椤哄簭鎻掑叆涓�涓�鏂扮储寮曡�板綍銆傝繖涓�闂撮殭鐨勯攣瀹氳��鎵ц�屾潵闃叉�㈡墍璋撶殑“骞界伒闂�棰�”銆傚亣璁句綘鎯宠�佷粠鏈変竴涓�鏍囪瘑绗﹀�煎ぇ浜�100鐨勫瓙琛ㄨ�诲苟閿佸畾鎵�鏈夊瓙璁板綍锛屽苟鎯崇潃闅忓悗鍦ㄩ�夊畾琛屼腑鏇存柊涓�浜� 鍒楋細

SELECT * FROM child WHERE id > 100 FOR UPDATE;

鍋囪�惧湪id鍒楁湁涓�涓�绱㈠紩銆傛煡璇�浠巌d澶т簬100鐨勭��涓�涓�璁板綍寮�濮嬫壂鎻忋�傚�傛灉璁剧疆鍦ㄧ储寮曡�板綍涓婄殑閿佸畾涓嶆妸鍦ㄩ棿闅欑敓鎴愮殑鎻掑叆鎺掗櫎鍦ㄥ�栵紝涓�涓�鏂拌�屽彲鑳戒笌姝ゅ悓鏃惰��鎻掕繘琛ㄤ腑銆傚�傛灉浣犲湪鍚屼竴浜嬪姟鍐呮墽琛屽悓鏍风殑SELECT锛屼綘鍙�鑳戒細鍦ㄨ�ユ煡璇㈣繑鍥炵殑缁撴灉鍖呴噷鐪嬪埌涓�涓�鏂拌�屻�傝繖涓庝簨鍔＄殑闅旂�诲師鍒欐槸鐩稿弽鐨勶細涓�涓�浜嬪姟搴旇�ヨ兘澶熻繍琛岋紝浠ヤ究瀹冨凡缁忚�荤殑鏁版嵁鍦ㄤ簨鍔¤繃绋嬩腑涓嶆敼鍙樸�傚�傛灉鎴戜滑鎶婁竴濂楄�岃�嗕负鏁版嵁椤癸紝鏂扮殑“骞界伒”瀛愯�板綍鍙�鑳戒細杩濆弽杩欎竴闅旂�诲師鍒欍��

褰揑nnoDB鎵�鎻忎竴涓�绱㈠紩涔嬫椂锛屽畠涔熼攣瀹氭墍浠ヨ�板綍涓�鏈�鍚庝竴涓�璁板綍涔嬪悗鐨勯棿闅欍�傚垰鍦ㄥ墠涓�涓�渚嬪瓙涓�鍙戠敓锛欼nnoDB璁剧疆鐨勯攣瀹氶槻姝�浠讳綍鎻掑叆鍒癷d鍙�鑳藉ぇ杩�100鐨勮〃銆�

浣犲彲浠ョ敤next-key閿佸畾鍦ㄤ綘鐨勫簲鐢ㄧ▼搴忎笂瀹炵幇涓�涓�鍞�涓�鎬ф��鏌ワ細濡傛灉浣犱互鍏变韩妯″紡璇绘暟鎹�锛� 骞朵笖娌℃湁鐪嬪埌浣犲皢瑕佹彃鍏ョ殑琛岀殑閲嶅�嶏紝鍒欎綘鍙�浠ュ畨鍏ㄥ湴鎻掑叆浣犵殑琛岋紝骞朵笖鐭ラ亾鍦ㄨ�昏繃绋嬩腑瀵逛綘鐨勮�岀殑缁ф壙鑰呰�剧疆鐨刵ext-key閿佸畾涓庢�ゅ悓鏃堕樆姝�浠讳綍浜哄�逛綘鐨� 琛屾彃鍏ヤ竴涓�閲嶅�嶃�傚洜姝わ紝the next-key閿佸畾鍏佽�镐綘閿佷綇鍦ㄤ綘鐨勮〃涓�骞朵笉瀛樺湪鐨勪竴浜涗笢瑗裤��

鍋囪�句綘姝ｈ繍琛屽湪榛樿�ょ殑REPEATABLE READ闅旂�荤骇鍒�銆傚綋浣犲彂鍑轰竴涓�鎸佺画璇讳箣鏃讹紝鍗充竴涓�鏅�閫氱殑SELECT璇�鍙ワ紝InnoDB鎸夌収浣犵殑鏌ヨ�㈢湅鍒扮殑鏁版嵁搴擄紝缁欎綘鐨勪簨鍔′竴涓�鏃堕棿鐐广�傚�傛灉鍙︿竴涓�浜嬪姟鍦ㄤ綘鐨勬椂闂寸偣琚�鎸囧畾涔嬪悗鍒犻櫎涓�琛屽苟鎻愪氦锛屼綘涓嶄細鐪嬭�佸凡琚�鍒犻櫎鐨勮�屻�傛彃鍏ュ拰鏇存柊琚�鐩镐技鍦板�勭悊銆�

浣犲彲浠ラ�氳繃鎻愪氦浣犵殑浜嬪姟鏉ユ彁鍓嶄綘鐨勬椂闂寸偣锛岀劧鍚庡仛鍙︿竴涓猄ELECT銆�

杩欒��绉颁负澶氱増鏈�骞跺彂鎺у埗锛坢ulti-versioned concurrency control锛�銆�

               User A                 User B

           SET AUTOCOMMIT=0;      SET AUTOCOMMIT=0;

time

|          SELECT * FROM t;

|          empty set

|                                 INSERT INTO t VALUES (1, 2);

|

v          SELECT * FROM t;

           empty set

                                  COMMIT;

           SELECT * FROM t;

           empty set

           COMMIT;

           SELECT * FROM t;

           ---------------------

           |    1    |    2    |

           ---------------------

           1 row in set

鍦ㄨ繖涓�渚嬪瓙涓�锛屽彧鏈夊綋鐢ㄦ埛B宸茬粡鎻愪氦鎻掑叆锛屽苟涓旂敤鎴稟涔熷凡缁忔彁浜や箣鏃讹紝鐢ㄦ埛A鍙�鐪嬭�佽��鐢ㄦ埛B鎻掑叆鐨勮�岋紝鍥犳�ゆ椂闂寸偣 鏄�鍦ㄧ敤鎴稡鎻愪氦涔嬪墠銆�

濡傛灉浣犳兂鐪嬫暟鎹�搴撶殑鏈�鏂扮姸鎬侊紝浣犲簲璇ョ敤READ COMMITTED闅旂�荤骇鍒�鎴栫敤涓�涓�閿佸畾璇伙細

SELECT * FROM t LOCK IN SHARE MODE;

鍦⊿QL鏌ヨ�㈠�勭悊涓�锛屼竴涓�閿佸畾璇伙紝涓�涓猆PDATE鎴栦竴涓狣ELETE涓�鑸�鍦板�硅��鎵�鎻忕殑姣忎釜绱㈠紩璁板綍璁剧疆璁板綍閿佸畾銆傚�傛灉鍦ㄦ煇鏌ヨ��涓�鏈変竴涓猈HERE鏉′欢鏄�娌′粈涔堝叧绯荤殑锛岃�岃繖涓�鏌ヨ�㈠彲鑳戒粠鏌ヨ�㈢殑缁撴灉鍖呬腑鎺掗櫎琛屻�侷nnoDB涓嶈�板緱纭�鍒囩殑WHERE鏉′欢锛屼絾鏄�浠呯煡閬撳摢涓�绱㈠紩鑼冨洿琚�鎵�鎻忋�傝�板綍閿佸畾鏄�姝ｅ父鐨刵ext-key閿佸畾锛屽畠涔熼樆姝㈠�圭揣鎺ョ潃璁板綍涔嬪墠鐨勯棿闅欑殑鎻掑叆銆�

濡傛灉閿佸畾琚�璁剧疆涓虹嫭鍗狅紝鍒橧nnoDB鎬绘槸鍙栧洖闆嗘潫鐨勭储寮曠洰褰曞苟瀵瑰叾璁剧疆閿佸畾銆�

濡傛灉浣犳病鏈夐�傚悎鏌ヨ�㈢殑绱㈠紩锛孧ySQL涓嶅緱涓嶆壂鎻忔暣涓�琛ㄦ潵澶勭悊鏌ヨ��锛岃〃鐨勬瘡琛屽彉鎴愯��閿佸畾鐨勶紝杩欐牱鍙嶈繃鏉ラ樆姝㈠叾瀹冪敤鎴风殑鎵�鏈夊�硅〃鐨勬彃鍏ャ�傚垱寤轰竴涓�濂界殑绱㈠紩璁╀綘鐨勬煡璇�涓嶅繀瑕佹壂鎻忓緢澶氳�屾槸寰堥噸瑕佺殑銆�

MySQL浠ラ粯璁ゅ厑璁竌utocommit妯″紡鏉ュ紑濮嬫瘡涓�涓�瀹㈡埛绔�杩炴帴銆傚綋autocommit琚�鍏佽�镐箣鏃讹紝濡傛灉SQL璇�鍙ヤ笉杩斿洖閿欒��鐨勮瘽锛孧ySQL鍦ㄦ瘡涓猄QL璇�鍙ヤ箣鍚庯紝鍋氫竴涓�鎻愪氦銆�

濡傛灉浣犲叧闂璦utocommit妯″紡骞朵笖鍏抽棴涓�涓�杩炴帴鑰屼笉璋冪敤浣犵殑浜嬪姟鐨勬槑纭�鎻愪氦锛屽垯MySQL鍥炴粴浣犵殑浜嬪姟銆�

濡傛灉SQL璇�鍙ヨ繑鍥炰竴涓�閿欒��锛屾彁浜�/鍥炴粴琛屼负鍙栧喅浜庤繖涓�閿欒��銆傝�峰弬闃�15.2.15鑺傦紝鈥淚nnoDB閿欒��澶勭悊”銆�

涓嬪垪姣忎竴涓�璇�鍙ワ紙浠ュ強瀹冧滑鐨勪换浣曞悓涔夎瘝锛夐殣寮忕粨鏉熶竴涓�浜嬪姟锛屽氨濂藉儚鍦ㄦ墽琛岃繖涓�璇�鍙ヤ箣鍓嶄綘鍋氫簡涓�涓狢OMMIT锛�

浜嬪姟涓嶈兘琚�宓屽�椼�傚綋浣犲彂鍑篠TART TRANSACTION璇�鍙ユ垨涓庝箣鍚屼箟鐨勮��鍙ヤ箣鏃讹紝杩欐槸瀵逛换浣曞綋鍓嶄簨鍔� 闅愬紡鎻愪氦鐨勪竴涓�缁撴灉銆�

InnoDB鑷�鍔ㄦ��娴嬩簨鍔＄殑姝婚攣锛屽苟鍥炴粴涓�涓�鎴栧嚑涓�浜嬪姟鏉ラ槻姝㈡�婚攣銆侷nnoDB璇曠潃鎸戦�夊皬浜嬪姟鏉ュ洖婊氾紝浜嬪姟鐨勫ぇ灏忛�氳繃琚�鎻掑叆銆佹洿鏂版垨鍒犻櫎鐨勮�岀殑鏁伴噺鏉ョ‘瀹氥��

濡傛灉innodb_table_locks=1 锛�1鏄�榛樿�ゅ�硷級锛孖nnoDB鎰忚瘑鍒拌〃閿佸畾锛屽叾涓婄殑MySQL灞傜煡閬搑ow-level閿佸畾銆傚彟澶朓nnoDB涓嶈兘鍦∕ySQL LOCK TABLES璁惧畾琛ㄩ攣瀹氱殑鍦版柟鎴栬�呮秹鍙奍nnoDB涔嬪�栫殑瀛樺偍寮曟搸璁剧疆閿佸畾鐨勫湴鏂规��娴嬫�婚攣銆備綘蹇呴』閫氳繃璁惧畾innodb_lock_wait_timeout绯荤粺鍙橀噺鐨勫�兼潵瑙ｅ喅杩欎簺鎯呭喌銆�

褰揑nnoD鎵ц�屽畬鍏ㄧ殑浜嬪姟鍥炴粴涔嬫椂锛岃�ヤ簨鍔＄殑鎵�鏈夐攣瀹氳��閲婃斁銆傚敖绠″�傛�わ紝濡傛灉鍗曚釜SQL璇�鍙ヨ��鍥犱负閿欒��鐨勫師鍥犺�� 鍥炴粴锛岃��SQL璇�鍙ヨ�惧畾鐨勯儴鍒嗛攣瀹氬彲鑳借��淇濈暀銆傝繖鏄�鍥犱负InnoDB浠ヤ竴绉嶆柟寮忓瓨鍌ㄨ�岄攣瀹氾紝鍦ㄨ繖绉嶆柟寮忎腑瀹冧笉鑳界煡閬撻殢鍚庣殑鍝�涓�閿佸畾鏄�琚�鍝�涓猄QL璇�鍙ヨ�惧畾鐨勩��

姝婚攣鏄�浜嬪姟鍨嬫暟鎹�搴撳吀鍨嬬殑闂�棰橈紝浣嗘槸闄ら潪瀹冧滑棰戠箒鍑虹幇浠ヨ嚦浜庝綘鏇存湰涓嶈兘杩愯�屾煇涓�浜嬪姟锛屽畠浠�涓�鑸�鏄�涓嶅嵄闄╃殑銆傛�ｅ父鍦帮紝浣犲繀椤荤紪鍐欎綘鐨勫簲鐢ㄧ▼搴忎娇寰楀畠浠�鎬绘槸鍑嗗�囧�傛灉鍥犱负姝婚攣鑰� 鍥炴粴涓�涓�浜嬪姟灏遍噸鏂板彂鍑轰竴涓�浜嬪姟銆�

InnoDB浣跨敤鑷�鍔ㄨ�岀骇閿佸畾銆傚嵆浣垮湪鍙�鎻掑叆鎴栧垹闄ゅ崟涓�琛岀殑浜嬪姟鐨勬儏鍐典笅锛屼綘鍙�浠ラ亣鍒版�婚攣銆傝繖鏄�鍥犱负杩欎簺鎿嶄綔涓嶆槸鐪熸�ｇ殑“鏋佸皬鐨�”锛屽畠浠�鑷�鍔ㄥ�规彃鍏ユ垨鍒犻櫎鐨勮�岀殑锛堝彲鑳芥槸鏁颁釜锛夌储寮曡�板綍璁剧疆閿佸畾銆�

浣犲彲浠ョ敤涓嬪垪鎶�鏈�瀵逛粯姝婚攣鍑忓皯瀹冧滑鍙戠敓鐨勫彲鑳芥�э細

·                SET AUTOCOMMIT=0;

·                LOCK TABLES t1 WRITE, t2 READ, ...;

·                [do something with tables t1 and t2 here];

·                COMMIT;

·                UNLOCK TABLES;

15.2.11.1. SHOW INNODB STATUS鍜孖nnoDB Monitors

·         濡傛灉Unix椤跺眰宸ュ叿鎴栬�匴indows浠诲姟绠＄悊鍣ㄦ樉绀猴紝浣犵殑鏁版嵁搴撶殑宸ヤ綔璐熻嵎鐨凜PU浣跨敤鐜囧皬浜�70%锛屽垯浣犵殑宸ヤ綔璐熻嵎鍙�鑳芥槸纾佺洏缁戝畾鐨勶紝鍙�鑳戒綘姝ｇ敓鎴愬お澶氱殑浜嬪姟鍜屾彁浜わ紝鎴栬�呯紦鍐叉睜澶�灏忋�備娇寰楃紦鍐叉睜鏇村ぇ 涓�浜涗細鏈夊府鍔╃殑锛屼絾涓嶈�佽�剧疆缂撳啿姹犵瓑浜庢垨瓒呰繃鐗╃悊鍐呭瓨鐨�80%.

·         鎶婃暟涓�淇�鏀硅９杩涗竴涓�浜嬪姟閲屻�傚�傛灉浜嬪姟瀵规暟鎹�搴撲慨鏀癸紝InnoDB鍦ㄨ�ヤ簨鍔℃彁浜ゆ椂蹇呴』鍒锋柊鏃ュ織鍒扮�佺洏銆傚洜涓虹�佺洏鏃嬭浆鐨勯�熷害鑷冲��167杞�/绉掞紝濡傛灉纾佺洏娌℃湁楠楁搷浣滅郴缁熺殑璇濓紝杩欏氨闄愬埗鎻愪氦鐨勬暟鐩�涓哄悓鏍风殑姣忕��167娆°��

·         濡傛灉浣犲彲浠ユ帴鍙楁崯澶变竴浜涙渶杩戠殑宸叉彁浜や簨鍔★紝浣犲彲浠ヨ�剧疆my.cnf鏂囦欢閲岀殑鍙傛暟innodb_flush_log_at_trx_commit涓�0銆� 鏃犺�哄�備綍InnoDB璇曠潃姣忕�� 鍒锋柊涓�娆℃棩蹇楋紝灏界�″埛鏂颁笉琚�璁稿彲銆�

·         浣跨敤澶х殑鏃ュ織鏂囦欢锛岃�╁畠鐢氳嚦涓庣紦鍐叉睜涓�鏍峰ぇ銆傚綋InnoDB鍐欐弧鏃ュ織鏂囦欢鏃讹紝瀹冧笉寰椾笉鍦ㄤ竴涓�妫�鏌ョ偣鎶婄紦鍐叉睜宸蹭慨鏀圭殑鍐呭�瑰啓杩涚�佺洏銆傚皬鏃ュ織鏂囦欢瀵艰嚧璁稿�氫笉蹇呰�佺殑鍚哥洏鍐欐搷浣溿�傚ぇ鏃ュ織鏂囦欢鐨勭己鐐规椂鎭㈠�嶆椂闂存洿闀裤��

·         涔熻�╂棩蹇楃紦鍐茬浉褰撳ぇ锛堜笌8MB鐩镐技鐨勬暟閲�)銆�

·         濡傛灉浣犲瓨鍌ㄥ彉闀垮害瀛楃�︿覆锛屾垨鑰呭垪鍙�鑳藉寘鍚�寰堝�歂ULL鍊硷紝鍒欎娇鐢╒ARCHAR鍒楃被鍨嬭�屼笉鏄疌HAR绫诲瀷銆備竴涓狢HAR(N)鍒楁�绘槸鍗犳嵁N 瀛楄妭鏉ュ瓨鍌�锛屽嵆浣垮瓧绗︿覆鏇寸煭鎴栧瓧绗︿覆鐨勫�兼槸NULL銆傝秺灏忕殑琛ㄨ秺濂藉湴閫傚悎缂撳啿姹犲苟涓斿噺灏戠�佺洏I/O銆�

褰撲娇鐢╮ow_format=compact 锛圡ySQL 5.1涓� 榛樿�ょ殑InnoDB璁板綍鏍煎紡锛夊拰鍙�鍙橀暱搴﹀瓧绗﹂泦锛屾瘮濡俇TF-8鎴杝jis锛孋HAR(N)灏嗗崰鎹�鍙�鍙樻暟閲忕殑绌洪棿锛岃嚦灏戜负N 瀛楄妭銆�

·         鍦ㄤ竴浜涚増鏈�鐨凣NU/Linux鍜孶nix涓婏紝鐢║nix鐨刦sync()锛圛nnoDB榛樿�や娇鐢ㄧ殑锛夋妸鏂囦欢鍒锋柊鍒扮�佺洏锛屽苟涓斿叾浠栫浉浼肩殑鏂规硶鏄�鎯婁汉鐨勬參銆傚�傛灉浣犱笉婊℃剰鏁版嵁搴撶殑鍐欐�ц兘锛屼綘鍙�浠ヨ瘯鐫�璁剧疆my.cnf閲岀殑innodb_flush_method涓篛_DSYNC锛岃櫧鐒禣_DSYNC鍦ㄥ�氭暟绯荤粺涓婄湅璧锋潵鏇存參銆�

·         褰撳湪Solaris 10涓婏紝涓簒86_64鏋舵瀯锛圓MD Opteron)浣跨敤InnoDB瀛樺偍寮曟搸锛岄噸瑕佺殑鏄�浣跨敤forcedirectio閫夐」鏉ュ畨瑁呬换浣曚负瀛樺偍涓嶪nnoDB鐩稿叧鐨勬枃浠惰�屼娇鐢ㄧ殑鏁版嵁绯荤粺銆傦紙榛樿�ゅ湪Solaris 10/x86_64涓婁笉浣跨敤杩欎釜鏂囦欢绯荤粺瀹夎�呴�夐」 锛夈�備娇鐢╢orcedirectio 澶辫触浼氬�艰嚧InnoDB鍦ㄨ繖涓�骞冲彴涓婄殑閫熷害鍜屾�ц兘涓ラ噸閫�鍖栥��

·         褰撳�煎叆鏁版嵁鍒癐nnoDB涓�涔嬫椂锛岃�风‘淇�MySQL娌℃湁鍏佽�竌utocommit妯″紡锛屽洜涓哄厑璁竌utocommit妯″紡浼氶渶瑕佹瘡娆℃彃鍏ラ兘瑕� 鍒锋柊鏃ュ織鍒扮�佺洏銆傝�佸湪瀵煎叆鎿嶄綔瑙勭▼涓�绂佹��autocommit妯″紡锛岀敤SET AUTOCOMMIT鍜孋OMMIT璇�鍙ユ潵鍖呬綇瀵煎叆璇�鍙ワ細

·                SET AUTOCOMMIT=0;

·                /* SQL import statements ... */

·                COMMIT;

濡傛灉浣犱娇鐢�mysqldump 閫夐」--opt锛屽嵆浣夸笉鐢⊿ET AUTOCOMMIT鍜孋OMMIT璇�鍙ユ潵鍖呰９锛屼綘涔熶娇寰楀揩閫熺殑杞�鍌ㄦ枃浠惰��瀵煎叆鍒癐nnoDB琛ㄤ腑銆�

·         灏忓績澶у畻鎻掑叆鐨勫ぇ鍥炴粴锛欼nnoDB鍦ㄦ彃鍏ヤ腑浣跨敤鎻掑叆缂撳啿鏉ヨ妭绾︾�佺洏I/O锛� 浣嗘槸鍦ㄧ浉搴旂殑鍥炴粴涓�娌℃湁浣跨敤杩欐牱鐨勬満鍒躲�備竴涓�纾佺洏缁戝畾鐨勫洖婊氬彲浠ョ敤鐩稿簲鎻掑叆鑺辫垂鏃堕棿鐨�30鍊嶆潵鎵ц�屻�傛潃鎺夋暟鎹�搴撹繘绋嬫病鏈夋槸甯�鍔╃殑锛屽洜涓哄洖婊氬湪鏈嶅姟鍣ㄥ惎鍔ㄦ椂 浼氬啀娆″惎鍔ㄣ�傞櫎鎺変竴涓�澶辨帶鐨勫洖婊氱殑鍞�涓�鏂规硶鏄�澧炲ぇ缂撳啿姹犱娇寰楀洖婊氬彉鎴怌PU缁戝畾涓旇窇寰楀揩锛屾垨鑰呬娇鐢ㄤ笓鐢ㄦ�ラ�わ紝璇峰弬闃�15.2.8.1鑺傦紝鈥滃己鍒舵仮澶�”銆�

·         涔熻�佸皬蹇冨叾瀹冨ぇ鐨勭�佺洏缁戝畾鎿嶄綔銆傜敤DROP TABLE鎴朇REATE TABLE鏉ユ竻绌轰竴涓�琛�锛岃�屼笉鏄�鐢―ELETE FROM tbl_name銆�

·         濡傛灉浣犻渶瑕佹彃鍏ヨ�稿�氳�岋紝鍒欎娇鐢ㄥ�氳�屾彃鍏ヨ��娉曟潵鍑忓皯瀹㈡埛绔�鍜屾湇鍔″櫒涔嬮棿鐨勯�氳��寮�鏀�锛�

·                INSERT INTO yourtable VALUES (1,2), (5,5), ...;

杩欎釜鎻愮ず瀵瑰埌浠讳綍琛ㄧ被鍨嬬殑鎻掑叆閮芥槸鍚堟硶鐨勶紝涓嶄粎浠呮槸瀵笽nnoDB绫诲瀷銆�

·         濡傛灉浣犲湪绗�浜屼釜閿�涓婃湁UNIQUE绾︽潫锛屼綘鍙�浠ュ湪瀵煎叆浼氳瘽涓�鏆傛椂鍏抽棴鍞�涓�鎬ф��鏌ヤ互鍔犻�熻〃鐨勫�煎叆锛�

·                SET UNIQUE_CHECKS=0;

瀵逛簬澶ц〃锛岃繖鑺傜害浜嗗ぇ閲忕�佺洏I/O锛屽洜涓篒nnoDB鍙�浠ヤ娇鐢ㄥ畠鐨勬彃鍏ョ紦鍐叉潵鍦ㄤ竴鎵瑰唴鍐欑��浜屼釜绱㈠紩璁板綍銆�

·         濡傛灉浣犲�逛綘鐨勮〃鏈塅OREIGN KEY绾︽潫锛屼綘鍙�浠ュ湪瀵煎叆浼氳瘽杩囩▼涓�閫氳繃鍏抽棴澶栭敭妫�鏌ユ潵鎻愰�熻〃鐨勫�煎叆锛�

·                SET FOREIGN_KEY_CHECKS=0;

瀵逛簬澶ц〃锛岃繖鍙�浠ヨ妭绾﹀ぇ閲忕殑纾佺洏I/O銆�

·         濡傛灉浣犵粡甯告湁瀵逛笉缁忓父鏇存柊鐨勮〃鐨勯噸鍙戞煡璇�锛岃�蜂娇鐢ㄦ煡璇㈢紦瀛橈細

·                [mysqld]

·                query_cache_type = ON

·                query_cache_size = 10M

InnoDB鍖呭惈InnoDB Monitors锛屽畠鎵撳嵃鏈夊叧InnoDB鍐呴儴鐘舵�佺殑淇℃伅銆備綘鍙�浠ヤ娇鐢⊿QL璇�鍙�SHOW INNODB STATUS鏉ュ彇鏍囧噯InnoDB Monitor鐨勮緭鍑洪�佸埌浣犵殑SQL瀹㈡埛绔�銆傝繖涓�淇℃伅鍦ㄦ�ц兘璋冭妭鏃舵湁鐢ㄣ�傦紙濡傛灉浣犳�ｄ娇鐢�mysql浜嬪姟SQL瀹㈡埛绔�锛屽�傛灉浣犵敤\G鏇夸唬閫氬父鐨勮��鍙ョ粓姝㈢�﹀垎鍙凤紝杈撳嚭缁撴灉浼氭洿鍙�璇� 锛夈�傚叧浜嶪nnoDB閿佸畾妯″紡鐨勮�ㄨ�猴紝璇峰弬闃�15.2.10.1鑺傦紝鈥淚nnoDB閿佸畾妯″紡”銆�

mysql> SHOW INNODB STATUS\G

鍙︿竴涓�浣跨敤InnoDB Monitors鐨勬柟娉曟椂璁╁畠浠�涓嶆柇鍐欐暟鎹�鍒�mysqld鏈嶅姟鍣ㄧ殑鏍囧噯杈撳嚭銆傚湪杩欑�嶆儏鍐典笅锛屾病鏈夎緭鍑鸿��閫佸埌瀹㈡埛绔�銆傚綋琚�鎵撳紑鐨勬椂鍊欙紝InnoDB Monitors姣�15绉掓墦鍗颁竴娆℃暟鎹�銆傛湇鍔″櫒杈撳嚭閫氬父琚�瀹氬悜鍒癕ySQL鏁版嵁鐩�褰曢噷鐨�.err鏃ュ織銆傝繖涓�鏁版嵁鍦ㄦ�ц兘璋冭妭鏃舵湁鐢ㄣ�傚湪Windows涓婏紝濡傛灉浣犳兂瀹氬悜杈撳嚭鍒扮獥鍙� 鑰屼笉鏄�鍒版棩蹇楁枃浠讹紝浣犲繀椤讳粠鎺у埗鍙扮獥鍙ｇ殑鍛戒护琛屼娇鐢�--console閫夐」鏉ュ惎鍔ㄦ湇鍔″櫒銆�

鐩戣�嗗櫒杈撳嚭鍖呮嫭涓嬪垪绫诲瀷鐨勪俊鎭�锛�

瑕佽�╂爣鍑咺nnoDB Monitor鍐欏埌mysqld鐨勬爣鍑嗚緭鍑猴紝璇蜂娇鐢ㄤ笅鍒桽QL璇�鍙ワ細

CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

鐩戣�嗗櫒鍙�浠ラ�氳繃鍙戝嚭涓嬪垪璇�鍙ユ潵琚�鍋滄��锛�

DROP TABLE innodb_monitor;

CREATE TABLE璇�娉曟�ｆ槸閫氳繃MySQL鐨凷QL瑙ｆ瀽绋嬪簭寰�InnoDB寮曟搸浼犻�掑懡浠ょ殑鏂规硶锛氬敮涓�鏈夐棶棰樼殑浜嬫儏鏄�琛ㄥ悕瀛梚nnodb_monitor鍙婂畠鏄�涓�涓狪nnoDB琛ㄣ�傚�逛簬InnoDB Monitor, 琛ㄧ殑缁撴瀯鏍规湰涓嶇浉鍏炽�傚�傛灉浣犲湪鐩戣�嗗櫒姝ｈ繍琛屾椂鍏抽棴鏈嶅姟鍣�锛屽苟涓斾綘鎯宠�佸啀娆″惎鍔ㄧ洃瑙嗗櫒锛屼綘蹇呴』鍦ㄥ彲浠ュ彂鍑烘柊CREATE TABLE璇�鍙ュ惎鍔ㄧ洃瑙嗗櫒涔嬪墠绉婚櫎琛ㄣ�傝繖涓�璇�娉曞湪灏嗘潵鐨勫彂琛岀増鏈�涓�鍙�鑳戒細鏀瑰彉銆�

浣犲彲浠ヤ互鐩镐技鐨勬柟寮忎娇鐢╥nnodb_lock_monitor銆傞櫎浜嗗畠涔熸彁渚涘ぇ閲忛攣瀹氫俊鎭�涔嬪�栵紝瀹冧笌innodb_monitor鐩稿悓銆備竴涓�鍒嗙�荤殑 innodb_tablespace_monitor鎵撳嵃瀛樺湪浜庤〃绌洪棿涓�宸插垱寤烘枃浠剁墖鏂�鐨勪竴涓�鍒楄〃锛屽苟涓旂‘璁よ〃绌洪棿鍒嗛厤鏁版嵁缁撴瀯銆傛�ゅ�栵紝鏈塱nnodb_table_monitor锛岀敤瀹冧綘鍙�浠ユ墦鍗癐nnoDB鍐呴儴鏁版嵁璇嶅吀鐨勫唴瀹广��

InnoDB Monitor杈撳嚭鐨勪竴涓�渚嬪瓙锛�

mysql> SHOW INNODB STATUS\G

*************************** 1. row ***************************

Status:

=====================================

030709 13:00:59 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 18 seconds

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 413452, signal count 378357

--Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the semaphore:

X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135

a writer (thread id 32782) has reserved it in mode wait exclusive

number of readers 1, waiters flag 1

Last time read locked in file btr0sea.c line 731

Last time write locked in file btr0sea.c line 1347

Mutex spin waits 0, rounds 0, OS waits 0

RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits 375485

------------------------

LATEST FOREIGN KEY ERROR

------------------------

030709 13:00:59 Transaction:

TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 inser

ting

15 lock struct(s), heap size 2496, undo log entries 9

MySQL thread id 25, query id 4668733 localhost heikki update

insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')

Foreign key constraint fails for table test/ibtest11a:

,

  CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`, `D`)

 ON DELETE CASCADE ON UPDATE CASCADE

Trying to add in child table, in index PRIMARY tuple:

 0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2: len 4;

 hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4: len 7; hex

 00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;;

But in parent table test/ibtest11b, in index PRIMARY,

the closest match we can find is record:

RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex 80000005; a

sc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex 0000111ef3eb; asc ......

;; 4: len 7; hex 800001001e0084; asc .......;; 5: len 3; hex 6b6864; asc khd;;

------------------------

LATEST DETECTED DEADLOCK

------------------------

030709 12:59:58

*** (1) TRANSACTION:

TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733 inser

ting

LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146

MySQL thread id 21, query id 4553379 localhost heikki update

INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t','e187358f'

,'g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),7

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole

trx id 0 290252780 lock mode S waiting

Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a

a35818;; 1:

*** (2) TRANSACTION:

TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782 inser

ting

130 lock struct(s), heap size 11584, undo log entries 437

MySQL thread id 23, query id 4554396 localhost heikki update

REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','', NULL,'

h396', NULL, NULL, 7.31,7.31,7.31,200)

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole

trx id 0 290251546 lock_mode X locks rec but not gap

Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a

a35818;; 1:

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole

trx id 0 290251546 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230; asc aa

35720;; 1:

*** WE ROLL BACK TRANSACTION (1)

------------

TRANSACTIONS

------------

Trx id counter 0 290328385

Purge done for trx's n:o < 0 290315608 undo n:o < 0 17

Total number of lock structs in row lock hash table 70

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002

MySQL thread id 32, query id 4668737 localhost heikki

show innodb status

---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id 38929 in

serting

1 lock struct(s), heap size 320

MySQL thread id 29, query id 4668736 localhost heikki update

insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlhh

gghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh

---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id 28684 co

mmitting

1 lock struct(s), heap size 320, undo log entries 1

MySQL thread id 19, query id 4668734 localhost heikki update

insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlh

hgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf

---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id 36880 st

arting index read

LOCK WAIT 2 lock struct(s), heap size 320

MySQL thread id 27, query id 4668644 localhost heikki Searching rows for update

update ibtest11a set B = 'kHdkkkk' where A = 89572

------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index PRIM

ARY trx id 0 290328327 lock_mode X waiting

Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc

 supremum.;;

------------------

---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 ro

llback of SQL statement

ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9

MySQL thread id 25, query id 4668733 localhost heikki update

insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')

---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id 32782

58 lock struct(s), heap size 5504, undo log entries 159

MySQL thread id 23, query id 4668732 localhost heikki update

REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t','e200498f

','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),

---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id 30733 in

serting

4 lock struct(s), heap size 1024, undo log entries 165

MySQL thread id 21, query id 4668735 localhost heikki update

INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','', NULL,

'h321', NULL, NULL, 7.31,7.31,7.31,200)

--------

FILE I/O

--------

I/O thread 0 state: waiting for i/o request (insert buffer thread)

I/O thread 1 state: waiting for i/o request (log thread)

I/O thread 2 state: waiting for i/o request (read thread)

I/O thread 3 state: waiting for i/o request (write thread)

Pending normal aio reads: 0, aio writes: 0,

 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0

Pending flushes (fsync) log: 0; buffer pool: 0

151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs

25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf for space 0: size 1, free list len 19, seg size 21,

85004 inserts, 85004 merged recs, 26669 merges

Hash table size 207619, used cells 14461, node heap has 16 buffer(s)

1877.67 hash searches/s, 5121.10 non-hash searches/s

---

LOG

---

Log sequence number 18 1212842764

Log flushed up to   18 1212665295

Last checkpoint at  18 1135877290

0 pending log writes, 0 pending chkp writes

4341 log i/o's done, 1.22 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total memory allocated 84966343; in additional pool allocated 1402624

Buffer pool size   3200

Free buffers       110

Database pages     3074

Modified db pages  2674

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages read 171380, created 51968, written 194688

28.72 reads/s, 20.72 creates/s, 47.55 writes/s

Buffer pool hit rate 999 / 1000

--------------

ROW OPERATIONS

--------------

0 queries inside InnoDB, 0 queries in queue

Main thread process no. 3004, id 7176, state: purging

Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779

1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

============================

1 row in set (0.05 sec)

鍏充簬杩欎釜杈撳嚭涓�浜涜�佹敞鎰忕殑锛�

InnoDB鍙戦�佽瘖鏂�杈撳嚭鍒皊tderr鎴栨枃浠讹紝鑰屼笉鏄�鍒皊tdout鎴栬�呭浐瀹氬昂瀵稿唴瀛樼紦鍐诧紝浠ラ伩鍏� 搴曞眰缂撳啿婧㈠嚭銆備綔涓轰竴涓�鍓�鏁堟灉锛孲HOW INNODB STATUS鐨勮緭鍑烘瘡15绉掗挓鍐欏埌涓�涓�鐘舵�佹枃浠躲�傝繖涓�鏂囦欢鐨勫悕瀛楁槸innodb_status.pid锛屽叾涓�pid 鏄�鏈嶅姟鍣ㄨ繘绋婭D銆傝繖涓�鏂囦欢鍦∕ySQL鏁版嵁鐩�褰曢噷鍒涘缓銆傛�ｅ父鍏虫満涔嬫椂锛孖nnoDB鍒犻櫎杩欎釜鏂囦欢銆傚�傛灉鍙戠敓涓嶆�ｅ父鐨勫叧鏈猴紝 杩欎簺鐘舵�佹枃浠剁殑瀹炰緥鍙�鑳借��灞曠ず锛岃�屼笖蹇呴』琚�鎵嬪姩鍒犻櫎銆傚湪绉婚櫎瀹冧滑涔嬪墠锛屼綘鍙�鑳芥兂瑕佹��鏌ュ畠浠�鏉ョ湅瀹冧滑鏄�鍚﹀寘鍚�鏈夊叧涓嶆�ｅ父鍏虫満鐨勫師鍥犵殑鏈夌敤淇℃伅銆備粎鍦ㄩ厤缃�閫夐」innodb_status_file=1琚�璁剧疆涔嬫椂锛宨nnodb_status.pid鏂囦欢琚�鍒涘缓銆�

15.2.13.1. 绱㈠紩鐨勭墿鐞嗙粨鏋�

15.2.13.2. 缂撳啿鎻掑叆

15.2.13.3. 閫傚簲鐨勫搱甯岀储寮�

15.2.13.4. 鐗╃悊璁板綍缁撴瀯

鎵�鏈塈nnoDB鐨勭储寮曟槸B鏁帮紝鍏朵腑绱㈠紩璁板綍琚�瀛樺偍鍦ㄦ爲鐨勬爲鍙堕〉銆備竴涓�绱㈠紩椤电殑榛樿�ゅぇ灏忔槸16KB銆傚綋鏂拌�板綍琚�鎻掑叆锛孖nnoDB璇曠潃涓哄皢鏉ョ储寮曡�板綍鐨勬彃鍏ュ拰鏇存柊鐣欎笅鍗佸叚鍒嗕箣涓�鐨勭┖鐧介〉銆�

濡傛灉绱㈠紩璁板綍浠ヨ繛缁�鐨勯『搴忚��鎻掑叆锛堝崌搴忔垨鑰呴檷搴忥級锛岀粨鏋滅储寮曢〉澶х害鏄�15/16婊°�傚�傛灉璁板綍琚�浠ラ殢鏈虹殑椤哄簭琚�鎻掑叆锛岄〉闈㈡槸浠�1/2鍒� 15/16婊°�傚�傛灉绱㈠紩椤电殑濉�鍏呭洜瀛愰檷鍒颁綆浜�1/2锛孖nnoDB璇曠潃鎼滅储绱㈠紩鏍戞潵閲婃斁椤点��

鍦ㄦ暟鎹�搴撳簲鐢ㄤ腑锛屼富閿�鏄�涓�涓�鍞�涓�鐨勮瘑鍒�绗︼紝骞朵笖鏂拌�岃��浠ヤ富閿�鐨勫崌搴忔潵鎻掑叆锛岃繖鏄�涓�甯歌�佺殑鎯呭喌銆傚洜姝わ紝鍒伴泦鏉熺储寮曠殑鎻掑叆涓嶉渶瑕佷粠涓�涓�纾佺洏闅忔満璇汇��

鍙︿竴鏂归潰锛岀��浜岀储寮曢�氬父鏄�闈炲敮涓�鐨勶紝鍒扮��浜岀储寮曠殑鎻掑叆浠ョ浉瀵归殢鏈烘�″簭鍙戠敓銆傝繖鍙�鑳戒細瀵艰嚧澶ч噺鐨勯殢鏈虹�佺洏I/O鎿嶄綔锛岃�屾病鏈変竴涓�琚�鐢ㄥ湪InnoDB涓�鐨勪笓鐢ㄦ満鍒躲��

濡傛灉涓�涓�绱㈠紩璁板綍搴旇�ヨ��鎻掑叆鍒颁竴涓�闈炲敮涓�绗�浜岀储寮曪紝InnoDB妫�鏌ョ��浜岀储寮曢〉鏄�鍚﹀湪缂撳啿姹犱腑銆傚�傛灉鏄�锛孖nnoDB鐩存帴鎻掑叆鍒扮储寮曢〉銆傚�傛灉绱㈠紩椤垫病鏈夊湪缂撳啿姹犱腑琚�鍙戠幇锛孖nnoDB鎻掑叆璁板綍鍒颁竴涓�涓撻棬鐨勬彃鍏ョ紦鍐茬粨鏋勩�傛彃鍏ョ紦鍐茶��淇濇寔寰楀�傛�ゅ皬浠ヨ嚦浜庡畠瀹屽叏閫傚悎鍦ㄧ紦鍐叉睜锛屽苟涓斿彲浠ラ潪甯稿揩鍦板仛鎻掑叆銆�

鎻掑叆缂撳啿鍛ㄦ湡鍦拌��鍚堝苟鍒版暟鎹�搴撲腑绗�浜岀储寮曟爲閲屻�傛妸鏁颁釜鎻掑叆鍚堝苟鍒扮储寮曟爲鐨勫悓涓�椤碉紝鑺傜渷纾佺洏I/O鎿嶄綔锛岀粡甯稿湴杩欐槸鏈夊彲鑳界殑銆傛嵁娴嬮噺锛屾彃鍏ョ紦鍐插彲浠ユ彁楂樺埌琛ㄧ殑鎻掑叆閫熷害杈�15鍊嶃��

鍦ㄦ彃鍏ヤ簨鍔¤��鎻愪氦涔嬪悗锛屾彃鍏ョ紦鍐插悎骞跺彲鑳借繛缁�鍙戠敓銆傚疄闄呬笂锛屾湇鍔″櫒鍏抽棴鍜岄噸鍚�涔嬪悗锛岃繖浼氳繛缁�鍙戠敓銆傦紙璇峰弬闃�15.2.8.1鑺傦紝鈥滃己鍒舵仮澶�”锛夈��

褰撹�稿�氱��浜岀储寮曞繀椤昏��鏇存柊涔嬫椂锛屽苟涓旇�稿�氳�屽凡琚�鎻掑叆涔嬫椂锛屾彃鍏ョ紦鍐插悎骞跺彲鑳介渶瑕佹暟涓�灏忔椂銆傚湪杩欎釜鏃堕棿鍐咃紝纾佺洏I/O灏嗕細澧炲姞锛岃繖鏍蜂細瀵艰嚧纾佺洏缁戝畾鏌ヨ�㈡槑鏄剧紦鎱�銆傚彟涓�涓�鏄庢樉鐨勫悗鍙癐/O鎿嶄綔鏄�鍑�鍖栫嚎绋嬶紙璇峰弬闃�15.2.12鑺傦紝鈥滃疄鐜板�氱増鏈�鍖�”锛夈��

濡傛灉涓�涓�琛ㄥ嚑涔庡畬鍏ㄩ厤鍚堜富鍐呭瓨锛屽湪鍏朵笂鎵ц�屾煡璇㈡渶蹇�鐨勬柟娉曞氨鏄�浣跨敤鍝堝笇绱㈠紩銆侷nnoDB鏈変竴涓�鑷�鍔ㄦ満鍒讹紝瀹冪洃瑙嗗�逛负涓�涓�琛ㄥ畾涔夌殑绱㈠紩鐨勭储寮曟悳绱�銆傚�傛灉InnoDB娉ㄦ剰鍒版煡璇�浼氫粠寤虹珛涓�涓�鍝堝笇绱㈠紩涓�鑾风泭锛屽畠浼氳嚜鍔ㄥ湴杩欎箞鍋氥��

娉ㄦ剰锛屽搱甯岀储寮曟�绘槸鍩轰簬琛ㄤ笂宸插瓨鍦ㄧ殑B鏍戠储寮曟潵寤虹珛銆傛牴鎹甀nnoDB瀵笲鏍戠储寮曡�傚療鐨勬悳绱㈡柟寮忥紝InnoDB浼氬湪涓鸿��B鏍戝畾涔夌殑浠讳綍闀垮害鐨� 閿�鐨勪竴涓�鍓嶇紑涓婂缓绔嬪搱甯岀储寮曘�� 鍝堝笇绱㈠紩鍙�浠ユ槸閮ㄥ垎鐨勶細瀹冧笉瑕佹眰鏁翠釜B鏍戠储寮曡��缂撳瓨鍦ㄧ紦鍐叉睜銆侷nnoDB鏍规嵁闇�瑕佸�硅��缁忓父璁块棶鐨勭储寮曠殑閭ｄ簺椤甸潰寤虹珛鍝堝笇绱㈠紩銆�

鍦ㄦ煇绉嶆剰涔変笂锛孖nnoDB閫氳繃閽堝�逛赴瀵屼富鍐呭瓨鐨勯�傚簲鐨勫搱甯岀储寮曟満鍒舵潵鍓�瑁佽嚜宸憋紝鏇村姞闈犺繎涓诲唴瀛樻暟鎹�搴撶殑鏋舵瀯銆�

InnoDB琛ㄤ腑鐨勮�板綍鏈夊�備笅鐗瑰緛锛�

15.2.14.1. 纾佺洏I/O

15.2.14.2. 涓鸿〃绌洪棿浣跨敤鍘熷�嬭�惧��

15.2.14.3.鏂囦欢绌洪棿绠＄悊

15.2.14.4. 鏁寸悊琛ㄧ�庣墖

InnoDB浣跨敤妯℃嫙寮傛�ョ�佺洏I/O锛欼nnoDB鍒涘缓璁稿�氱嚎绋嬫潵澶勭悊I/O鎿嶄綔锛屾瘮濡俽ead-ahead.

鍦↖nnoDB涓�鏈変袱涓�read-ahead璇曟帰锛�

InnoDB浣跨敤涓�涓�琚�绉颁负doublewrite鐨勬柊棰栫殑鏂囦欢 鍒锋柊鎶�鏈�銆傚畠缁欐搷浣滀綋缁熷穿婧冩垨鎺夌數鍚庣殑鎭㈠�嶆坊鍔犱簡瀹夊叏锛屽苟涓旈�氳繃鍑忓皯瀵筬sync()鎿嶄綔鐨勯渶瑕侊紝瀹冨湪澶氭暟Unix鍙樼�嶄笂鏀瑰杽浜嗘�ц兘銆�.

Doublewrite鎰忎负鍦ㄥ悜涓�涓�鏁版嵁鏂囦欢鍐欓〉涔嬪墠锛孖nnoDB棣栧厛鎶婂畠浠�鍐欏埌涓�涓�姣楅偦鐨勮〃绌洪棿鍖哄煙锛岀О涓篸oublewrite缂撳啿銆備粎鍦ㄥ啓鐒跺悗 鍒锋柊鍒癲oublewrite宸茬粡瀹屾垚涔嬪墠锛孖nnoDB鍐欓〉闈㈠埌瀹冧滑鍦ㄨ〃绌洪棿涓�鎭板綋鐨勪綅缃�銆傚�傛灉鎿嶄綔绯荤粺鍦ㄥ啓椤甸潰鐨勪腑闂村穿婧冿紝鍦ㄦ仮澶嶈繃绋嬩腑锛孖nnoDB鍙�浠ュ湪闅忓悗浠巇oublewrite缂撳啿涓�鎵惧埌椤甸潰鐨勪竴涓�鑹�濂藉�嶅埗銆�

浣犱篃鍙�浠ヤ娇鐢ㄥ師濮嬬�佺洏鍒嗗尯浣滀负琛ㄧ┖闂存暟鎹�鏂囦欢銆傞�氳繃浣跨敤鍘熷�嬬�佺洏锛屼綘鍙�浠ュ湪Windows鍜屼竴浜沀nix绯荤粺涓婃墽琛宯on-buffered I/O 鑰屾棤椤绘枃浠剁郴缁熷紑鏀�锛岃繖鏍峰彲浠ユ敼鍠勬�ц兘

褰撲綘鍒涘缓涓�涓�鏂版暟鎹�鏂囦欢涔嬫椂锛屼綘蹇呴』鍦╥nnodb_data_file_path閲岀揣鎺ョ潃鏁版嵁鏂囦欢灏哄�镐箣鍚庢斁缃� 鍏抽敭瀛梟ewraw銆傚垎鍖哄繀椤昏嚦灏戝拰浣犳寚瀹氱殑灏哄�镐竴鏍峰ぇ锛屾敞鎰忥紝鍦↖nnoDB涓�锛�1MB鏄�1024x1024瀛楄妭锛� 浣嗘槸鍦ㄧ�佺洏瑙勬牸涓�锛�1MB閫氬父鎰忎负1,000,000瀛楄妭銆�

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

涓嬫�′綘鍚�鍔ㄦ湇鍔″櫒涔嬫椂锛孖nnoDB娉ㄦ剰鍒板叧閿�瀛梟ewraw骞跺垵濮嬪寲鏂板垎鍖恒�備絾鏄�浠嶇劧骞朵笉鍒涘缓鎴栨敼鍙樹换浣旾nnoDB琛ㄣ�傚彟澶栵紝褰撲綘閲嶅惎鏈嶅姟鍣ㄤ箣鏃讹紝InnoDB閲嶆柊鍒濆�嬪寲鍒嗗尯锛屼綘鐨勬敼鍙樹細涓㈠け銆傦紙浠�3.23.44鍚�鍔�锛屼綔涓轰竴涓�瀹夊叏鎺�鏂斤紝 褰撶敤newraw鎸囧畾浠讳綍鍒嗗尯涔嬫椂锛孖nnoDB闃绘�㈢敤鎴蜂慨鏀规暟鎹�锛夈��

InnoDB鍒濆�嬪寲鏂板垎鍖轰箣鍚庯紝鍋滄�㈡湇鍔″櫒锛屽湪瀵硅�岀殑鏁版嵁鏂囦欢瑙勬牸涓�鏀瑰彉newraw锛�

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw

鐒跺悗閲嶅惎鍔ㄦ湇鍔″櫒锛屽苟涓擨nnoDB鍏佽�稿仛鏀瑰彉銆�

鍦╓indows涓婏紝浣犲彲浠ヨ薄杩欐牱鍒嗛厤纾佺洏鍒嗗尯涓轰竴涓�鏁版嵁鏂囦欢锛�

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=//./D::10Gnewraw

瀵逛簬璁块棶鐗╃悊椹卞姩鍣�锛�//./ 鐩稿綋浜嶹indows璇�娉曠殑\\.\ 銆�

褰撲綘浣跨敤鍘熷�嬬�佺洏鍒嗗尯涔嬫椂锛岀‘淇″畠浠�鏈夊厑璁歌��鐢ㄦ潵杩愯�孧ySQL鏈嶅姟鍣ㄧ殑甯愬彿璇诲拰鍐欒�块棶鐨勮�稿彲銆�

浣犲湪閰嶇疆鏂囦欢涓�瀹氫箟鐨勬暟鎹�鏂囦欢褰㈡垚InnoDB鐨勮〃绌洪棿銆傛枃浠惰��绠�鍗曞湴杩炶捣鏉ヤ互褰㈡垚琛ㄧ┖闂淬�傛病鏈夋潯绾瑰湪浣跨敤銆傚綋鍓嶄綘涓嶈兘瀹氫箟浣犵殑琛ㄨ��鍒嗛厤鍒拌〃绌洪棿涓�鐨勪綅缃�銆備絾鏄�锛屽湪涓�涓�鏂板垱寤虹殑琛ㄤ腑闂翠腑锛孖nnoDB 浠庣��涓�涓�鏁版嵁鏂囦欢寮�濮嬪垎閰嶇┖闂淬��

琛ㄧ┖闂村寘鍚�鏁版嵁搴撻〉锛岄粯璁ゅぇ灏忔槸16KB銆傝繖浜涢〉琚�鍒嗙粍鎴�64涓�杩炵画椤电殑鑼冨洿銆傝〃绌洪棿鍐呯殑鏂囦欢鍦↖nnoDB涓�琚�绉颁负鐗囨柇銆傛湳璇�“rollback segment”鏈変竴浜涙贩娣嗭紝鍥犱负瀹冪‘鍒囧湴鍖呭惈璁稿�氳〃绌洪棿鐗囨柇銆�

鍦↖nnoDB涓�锛屾瘡涓�绱㈠紩鍒嗛厤涓や釜鐗囨柇銆備竴涓�鏄�缁橞鏍戠殑闈炴爲鍙惰妭鐐圭殑锛屽彟涓�涓�鏄�缁欐爲鍙惰妭鐐圭殑銆傚湪杩欓噷锛岀悊鎯崇殑鏄�涓哄寘鍚�鏁版嵁鐨勬爲鍙惰妭鐐硅揪鍒版洿濂界殑鏈夊簭鎬с��

褰撲竴涓�鐗囨柇鍦ㄨ〃绌洪棿鍐呴暱澶э紝InnoDB鍗曠嫭鍦板垎閰嶆渶鍏堢殑32椤电粰瀹冦�傛�ゅ悗InnoDB寮�濮嬪垎閰嶆暣涓�鑼冨洿缁欒�ョ墖鏂�銆侷nnoDB鍙�浠ヤ竴娆＄粰涓�涓�澶х墖鏂�娣诲姞澶氳揪4涓�鑼冨洿浠ョ‘淇濇暟鎹�鑹�濂界殑杩炵画鎬с��

鍦ㄨ〃绌洪棿涓�鐨勪竴浜涢〉鍖呭惈鍏跺畠椤电殑浣嶅浘锛屽洜姝ゅ湪涓�涓狪nnoDB琛ㄧ┖闂翠腑鐨勪竴浜涜寖鍥翠笉鑳借��鏁翠釜鍦板垎閰嶇粰鐗囨柇锛屽彧鑳戒綔涓哄崟涓�椤佃��鍒嗛厤銆�

褰撲綘鍙戝嚭SHOW TABLE STATUS璇㈤棶琛ㄧ┖闂撮噷鍙�鐢ㄧ殑鑷�鐢辩┖闂翠箣鏃讹紝InnoDB鎶ュ憡鍦ㄨ〃绌洪棿涓�瀹屽叏鑷�鐢辩殑鑼冨洿銆侷nnoDB鎬绘槸涓烘壂闄ゅ拰鍏跺畠鍐呴儴鐩�鐨勪繚鐣欎竴浜涜寖鍥达紝杩欎簺淇濈暀鐨勮寖鍥翠笉鍖呮嫭鍦ㄨ嚜鐢辩┖闂撮噷銆�

褰撲綘浠庝竴涓�琛ㄤ腑鍒犻櫎鏁版嵁涔嬫椂锛孖nnoDB鑱旂郴鐩稿簲鐨凚鏍戠储寮曘�傛槸鍚﹂噴鏀惧崟鐙�椤垫垨鏄�鑼冨洿鍒拌〃绌洪棿鍙栧喅鍒犻櫎鐨勬柟寮忥紝鍥犳�よ��閲婃斁鐨勭┖闂村彉鎴愬�瑰叾瀹冪敤鎴峰彲鐢�锛屼絾鏄�璁颁綇锛屽凡鍒犻櫎鐨勮�屼粎鍦ㄨ�ヨ�屼笉鍐嶈��浜嬪姟 鍥炴粴鎴栨寔缁�璇绘墍闇�瑕佷箣鍚庣殑涓�涓�锛堣嚜鍔�锛夊噣鍖栨搷浣滀腑琚�鐗╃悊鍒犻櫎銆�

濡傛灉鏈夐殢鏈烘彃鍏ュ埌琛ㄧ殑绱㈠紩鎴栦粠琛ㄧ殑绱㈠紩闅忔満鍒犻櫎锛岀储寮曞彲鑳藉彉鎴愮�庣墖鐨勩�傜�庣墖鎰忔�濇槸绱㈠紩椤靛湪纾佺洏涓婄殑鐗╃悊鎺掑簭骞朵笉鎺ヨ繎椤典笂璁板綍鐨勭储寮曟帓搴忥紝鎴栬�呭湪鍒嗛厤缁欑储寮曠殑64椤靛潡涓婃湁璁稿�氭病鏈夎��浣跨敤鐨勯〉銆�

纰庣墖鐨勪竴涓�“鍚屼箟璇�”鏄�涓�涓�琛ㄥ崰鎹�鐨勭┖闂磋秴杩囧畠搴旇�ュ崰鎹�鐨勭┖闂寸殑澶� 灏忋�傜‘鍒囨槸澶氬皯锛岃繖鏄�寰堥毦鍘荤‘瀹氱殑銆傛墍鏈塈nnoDB鏁版嵁鍜岀储寮曡��瀛樺湪B鏍戜腑锛屽苟涓斿畠浠�鐨勫～鍏呭洜瀛愬彲鑳戒粠50%鍒�100%銆傜�庣墖鐨勫彟涓�涓�“鍚� 涔夎瘝”鏄�涓�涓�琛ㄦ壂鎻忎緥濡傦細

SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345;

鑺变簡瓒呰繃瀹冨簲璇ヨ姳鐨勬椂闂淬�傦紙鍦ㄤ笂闈㈢殑鏌ヨ��涓�鎴戜滑“娆洪獥”SQL浼樺寲鍣ㄦ潵鎵�鎻忛泦鏉熺储寮曪紝鑰屼笉鏄�涓�涓�绗�浜岀储寮� 锛夈�傚�氭暟纾佺洏鍙�浠ヨ��10MB/s鍒�50MB/s锛岃繖鍙�浠ヨ��鐢ㄦ潵璇勪及涓�涓�琛ㄦ壂鎻忓彲浠ュ�氬揩鍦拌繍琛屻��

濡傛灉浣犲懆鏈熷湴鎵ц��“null” ALTER TABLE鎿嶄綔锛屽畠灏卞彲浠ュ姞閫熺储寮曟壂鎻忥細 

ALTER TABLE tbl_name ENGINE=INNODB

杩欏�艰嚧MySQL閲嶅缓琛ㄣ�傚彟涓�涓�鎵ц�岀�庣墖鏁寸悊鎿嶄綔鐨勫姙娉曟槸浣跨敤mysqldump鏉ヨ浆鍌ㄤ竴涓�琛ㄥ埌涓�涓�鏂囨湰鏂囦欢锛岀Щ闄よ〃锛屽苟閲嶆柊浠庤浆鍌ㄦ枃浠堕噸瑁呰浇瀹冦��

濡傛灉鍒颁竴涓�绱㈠紩鐨勬彃鍏ユ�绘槸鍗囧簭鐨勶紝骞朵笖璁板綍浠呬粠鏈�灏捐��鍒犻櫎锛孖nnoDB鏂囦欢绌洪棿绠＄悊淇濊瘉鍦ㄧ储寮曚腑鐨勭�庣墖涓嶄細鍙戠敓銆�

15.2.15.1. InnoDB閿欒��浠ｇ爜

15.2.15.2. 鎿嶄綔绯荤粺閿欒��浠ｇ爜

·         濡傛灉浣犺�楀敖琛ㄧ┖闂翠腑鐨勬枃浠剁┖闂达紝浣犱娇寰桵ySQL琛ㄥ畬鍏ㄩ敊璇�锛屽苟涓擨nnoDB杩斿洖SQL璇�鍙ャ��

·         涓�涓�浜嬪姟姝婚攣瀵艰嚧InnoDB鍥炴粴鏁翠釜浜嬪姟锛屽湪閿佸畾绛夊緟瓒呮椂鐨勬儏鍐碉紝InnoDB浠呭洖婊氭渶杩戠殑SQL璇�鍙ャ��

褰撲竴涓�浜嬪姟鍥炴粴鍥犱负姝婚攣鎴栭攣瀹氱瓑寰呰秴鏃惰�屽彂鐢燂紝瀹冨湪浜嬪姟涓�鎾ら攢璇�鍙ョ殑浣滅敤銆備絾鏄�濡傛灉浜嬪姟鏄�鐢⊿TART TRANSACTION鎴朆EGIN璇�鍙ュ紑濮嬬殑锛屽畠灏变笉鎾ら攢璇ヨ��鍙ャ�傝繘涓�姝ワ紝SQL璇�鍙ュ彉鎴愪簨鍔＄殑涓�閮ㄥ垎鐩村埌COMMIT, ROLLBACK鎴栬�呭�艰嚧鏆楀湴鎻愪氦鐨凷QL璇�鍙ュ彂鐢熴��

·         濡傛灉浣犳病鏈夊湪璇�鍙ヤ腑鎸囧畾IGNORE閫夐」锛岄噸澶嶉敭閿欒��鍥炴粴SQL璇�鍙ャ��

·         琛屽お闀块敊璇�鍥炴粴SQL璇�鍙ャ��

·         鍏跺畠閿欒��涓昏�佽��浠ｇ爜鐨凪ySQL灞傦紙鍦↖nnoDB瀛樺偍寮曟搸绾у埆浠ヤ笂锛夋帰娴嬶紝瀹冧滑鍥炴粴鐩稿簲鐨凷QL璇�鍙ャ�傚湪鍗曚釜SQL璇�鍙ヤ腑鐨勫洖婊氫腑閿佸畾涓嶈��閲婃斁銆�

涓嬮潰鐨勮繖涓�涓嶅畬鍏ㄥ垪琛ㄦ槸浣犲彲鑳介亣瑙佺殑甯歌�佺殑InnoDB涓撴湁閿欒��锛屽甫鐫�涓轰粈涔堝彂鐢熺殑鍘熷洜浠ュ強濡備綍璇ヨВ鍐抽棶棰樼殑鐩稿叧淇℃伅

瑕佹墦鍗颁竴涓�鎿嶄綔绯荤粺閿欒��鍙风殑鎰忔�濓紝璇蜂娇鐢∕ySQL鍒嗗彂鐗�閲岀殑perror绋嬪簭銆�

涓嬮潰琛ㄦ彁渚涗竴浜涘父鐢↙inux绯荤粺閿欒��浠ｇ爜銆傛洿瀹屾暣鐨勫垪琛ㄨ�峰弬闃�Linux source code銆�

涓嬪垪琛ㄦ彁渚涗竴鍒楀父鐢╓indows绯荤粺閿欒��浠ｇ爜銆傚畬鏁村垪琛ㄨ�疯�块棶Microsoft website銆�

·         涓�涓�琛ㄤ笉鑳藉寘鍚�瓒呰繃1000鍒椼��

·         鍐呴儴鏈�澶ч敭闀垮害鏄�3500瀛楄妭锛屼絾MySQL鑷�宸遍檺鍒惰繖涓�鍒�1024瀛楄妭銆�

·         闄や簡VARCHAR, BLOB鍜孴EXT鍒楋紝鏈�澶ц�岄暱搴︾◢寰�灏忎簬鏁版嵁搴撻〉鐨勪竴鍗娿�傚嵆锛屾渶澶ц�岄暱搴﹀ぇ绾�8000瀛楄妭銆侺ONGBLOB鍜孡ONGTEXT鍒楀繀椤诲皬浜�4GB, 鎬荤殑琛岄暱搴︼紝椤靛寘鎷珺LOB鍜孴EXT鍒楋紝蹇呴』灏忎簬4GB銆侷nnoDB鍦ㄨ�屼腑瀛樺偍VARCHAR锛孊LOB鎴朤EXT鍒楃殑鍓�768瀛楄妭锛屼綑涓嬬殑瀛樺偍鐨勫垎鏁ｇ殑椤甸潰涓�銆�

·         铏界劧InnoDB鍐呴儴鍦版敮鎸佽�屽昂瀵稿ぇ浜�65535锛屼綘涓嶈兘瀹氫箟涓�涓�鍖呭惈VARCHAR鍒楃殑锛屽悎骞跺昂瀵稿ぇ浜�65535鐨勮�屻��

·                mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),

·                    -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),

·                    -> f VARCHAR(10000), g VARCHAR(10000));

·                ERROR 1118 (42000): Row size too large. The maximum row size for the

·                used table type, not counting BLOBs, is 65535. You have to change some

·                columns to TEXT or BLOBs

·         鍦ㄤ竴浜涙洿鑰佺殑鎿嶄綔绯荤粺涓婏紝鏁版嵁鏂囦欢蹇呴』灏忎簬2GB銆�

·         InnoDB鏃ュ織鏂囦欢鐨勫悎骞跺昂瀵稿繀椤诲皬浜�4GB銆�

·         鏈�灏忕殑琛ㄧ┖闂村昂瀵告槸10MB銆傛渶澶х殑琛ㄧ┖闂村昂瀵告槸4,000,000,000涓�鏁版嵁搴撻〉锛�64TB锛夈�傝繖涔熸槸涓�涓�琛ㄧ殑鏈�澶у昂瀵搞��

·         InnoDB琛ㄤ笉鏀�鎸�FULLTEXT绱㈠紩銆�

·         ANALYZE TABLE 閫氳繃瀵规瘡涓�绱㈠紩鏍戝仛鍏�娆￠殢鏈烘繁鍏ュ苟鐩稿簲鍦版洿鏂扮储寮曢泦浼板�硷紝杩欐牱鏉ヨ�℃暟闆嗐�傛敞鎰忥紝鍥犱负杩欐槸浠呮湁鐨勪及鍊硷紝鍙嶅�嶈繍琛孉NALYZE TABLE浼氫骇鐢熶笉鍚屾暟銆傝繖浣垮緱 ANALYZE TABLE 鍦� InnoDB 琛ㄤ笂寰堝揩锛屼笉鏄�鐧惧垎鐧惧噯纭�锛屽洜涓哄畠娌℃湁鑰冭檻鎵�鏈夌殑琛屻��

MySQL 涓嶄粎鍦ㄦ眹鍚堜紭鍖栦腑浣跨敤绱㈠紩闆嗕及鍊笺�傚�傛灉涓�浜涙眹鍚堟病鏈変互姝ｇ‘鐨勬柟寮忎紭鍖栵紝浣犲彲浠ヨ瘯涓�涓� ANALYZE TABLE 銆傚緢灏戞湁鎯呭喌锛孉NALYZE TABLE 娌℃湁浜х敓瀵逛綘鐗瑰畾鐨勮〃瓒冲�熷ソ鐨勫�硷紝浣犲彲浠ヤ娇鐢� FORCE INDEX 鍦ㄤ綘鏌ヨ��涓�鏉ュ己鍒朵娇鐢ㄧ壒瀹氱储寮曪紝鎴栬�呰�剧疆 max_seeks_for_key 鏉ョ‘淇滿ySQL鍦�琛ㄦ壂鎻忎箣涓婅繍琛岀储寮曟煡鎵俱��璇峰弬闃�5.3.3鑺傦紝鈥滄湇鍔″櫒绯荤粺鍙橀噺”銆傝�峰弬闃�A.6鑺傦紝鈥滀紭鍖栧櫒鐩稿叧鐨勯棶棰�”銆�

·         鍦╓indows涓婏紝InnoDB鎬绘槸鍐呴儴鍦扮敤灏忓啓瀛楁瘝瀛樺偍鏁版嵁搴撳拰琛ㄥ悕瀛椼�傝�佹妸鏁版嵁搴撲互浜岃繘鍒跺舰寮忎粠Unix 绉诲埌Windows锛屾垨鑰呬粠Windows绉诲埌Unix锛屼綘搴旇�ヨ�╂墍鏈夋暟鎹�搴撳拰琛ㄧ殑鍚嶅瓧閮芥槸灏忓啓銆�

·         璀﹀憡: 涓嶈�佸湪MySQL鏁版嵁搴撳唴鐨勬妸MySQL绯荤粺琛ㄤ粠MyISAM杞�涓�InnoDB琛�锛佽繖鏄�涓�涓�涓嶈��鏀�鎸佺殑鎿嶄綔銆傚�傛灉浣犺繖涔堝仛浜嗭紝MySQL鐩村埌浣犱粠澶囦唤鎭㈠�嶆棫绯荤粺琛�锛屾垨鐢�mysql_install_db鑴氭湰閲嶅缓绯荤粺琛ㄦ墠閲嶅惎鍔ㄣ��

·         InnoDB鍦ㄨ〃鍐�涓嶄繚鐣欒�岀殑鍐呴儴璁℃暟銆傦紙鍥犱负澶氱増鏈�鍖栵紝杩欏彲鑳界‘瀹炴湁浜涘�嶆潅 锛夈�傝�佸�勭悊涓�涓猄ELECT COUNT(*) FROM t璇�鍙ワ紝InnoDB蹇呴』鎵�鎻忚〃鐨勪竴涓�绱㈠紩锛屽�傛灉杩欎釜绱㈠紩涓嶅湪缂撳啿姹犱腑锛屾壂鎻忛渶瑕佽姳涓�浜涙椂闂淬�傝�佽幏寰楀揩閫熻�℃暟锛屼綘涓嶅緱涓嶄娇鐢ㄤ竴涓�鑷�宸卞垱寤虹殑璁℃暟鍣ㄨ〃锛屽苟璁╀綘鐨勫簲鐢ㄦ寜鐓у畠鍋氱殑鎻掑叆鍜屽垹闄ゆ潵鏇存柊瀹冦�傚�傛灉浣犵殑琛ㄦ牸涓嶇粡甯告敼鍙橈紝浣跨敤MySQL鏌ヨ�㈢紦瀛樻椂涓�涓�濂界殑瑙ｅ喅鏂规�堛�傚�傛灉澶ц嚧鐨勮�屾暟灏辫冻澶熶簡锛屽垯SHOW TABLE STATUS涔熷彲琚�浣跨敤銆傝�峰弬闃�15.2.11鑺傦紝鈥淚nnoDB鎬ц兘璋冭妭鎻愮ず”銆�

·         瀵逛簬AUTO_INCREMENT鍒楋紝浣犲繀椤绘�绘槸涓鸿〃瀹氫箟涓�涓�绱㈠紩锛屽苟涓旂储寮曞繀椤诲寘鍚獳UTO_INCREMENT鍒椼�傚湪MyISAM琛ㄤ腑锛孉UTO_INCREMENT鍒楀彲鑳芥椂澶� 鍒楃储寮曠殑涓�閮ㄥ垎銆�

·         褰撲綘閲嶅惎MySQL鏈嶅姟鍣ㄤ箣鏃讹紝InnoDB鍙�鑳戒负涓�涓狝UTO_INCREMENT鍒楅噸浣跨敤涓�涓�鏃у�硷紙鍗筹紝涓�涓�琚�璧嬬粰涓�涓�鑰佺殑宸� 鍥炴粴鐨勪簨鍔＄殑鍊硷級銆�

·         褰撲竴涓狝UTO_INCREMENT鍒楃敤瀹屽�硷紝InnoDB闄愬埗涓�涓狟IGINT鍒帮紞9223372036854775808浠ュ強BIGINT UNSIGNED鍒�1銆傚敖绠″�傛�わ紝BIGINT鍊兼湁鐢�64浣嶏紝鎵�浠ユ敞鎰忓埌锛屽�傛灉浣犺�佷竴绉掕緭鍏�100涓囦釜琛岋紝鍦˙IGINT鍒拌揪瀹冧笂闄愪箣鍓嶏紝鍙�鑳借繕闇�瑕佸皢杩�30涓囧勾銆傜敤鎵�鏈夊叾瀹冩暣鏁扮被鍨� 鍒楋紝浜х敓涓�涓�閲嶅�嶉敭閿欒��銆傝繖绫讳技浜嶮yISAM濡備綍宸ヤ綔鐨勶紝鍥犱负瀹冧富瑕佹槸涓�鑸琈ySQL琛屼负锛屽苟涓嶇壒鍒�鍏充簬浠讳綍瀛樺偍寮曟搸銆�

·         DELETE FROM tbl_name涓嶉噸鏂扮敓鎴愯〃锛屼絾鍙栬�屼唬涔嬪湴鍒犻櫎鎵�鏈夎�岋紝涓�涓�鎺ヤ竴涓�鍦板垹闄ゃ��

·         TRUNCATE tbl_name涓篒nnoDB鑰岃��鏄犲皠鍒癉ELETE FROM tbl_name 骞朵笖涓嶉噸缃瓵UTO_INCREMENT璁℃暟鍣ㄣ��

·         SHOW TABLE STATUS涓嶈兘缁欏嚭鍏充簬InnoDB琛ㄥ噯纭�鐨勭粺璁℃暟鎹�锛岄櫎浜嗚��琛ㄤ繚鐣欑殑鐗╃悊灏哄�搞�傝�岃�℃暟浠呮槸鍦⊿QL浼樺寲涓�绮楃暐鐨勪及璁°��

·         鍦∕ySQL 5.1涓�锛屽�傛灉innodb_table_locks=1(1鏄�榛樿�ゅ�硷級 MySQL LOCK TABLES鎿嶄綔鍦ㄦ瘡涓�涓�琛ㄤ笂鑾峰彇涓や釜閿佸畾銆傞櫎浜嗗湪MySQL灞傜殑琛ㄩ攣瀹氾紝瀹冧篃鑾峰緱涓�涓狪nnoDB琛ㄩ攣瀹氥�傛棫鐗堢殑MySQL涓嶈幏鍙朓nnoDB琛ㄩ攣瀹氾紝鏃ц�屼负鍙�浠ラ�氳繃璁剧疆innodb_table_locks=0 鏉ラ�夋嫨銆傚�傛灉娌℃湁InnoDB琛ㄩ攣瀹氳��鑾峰緱锛屽嵆浣胯〃鐨勪竴浜涜�板綍琚�鍏跺畠浜嬪姟閿佸畾锛孡OCK TABLES瀹屾垚銆� 

·         鎵�鏈夎��涓�涓�浜嬪姟鎸佹湁鐨処nnoDB閿佸畾鍦ㄨ�ヤ簨鍔¤��鎻愪氦鎴栦腑姝�涔嬫椂琚�閲婃斁銆傚洜姝ゅ湪AUTOCOMMIT=1妯″紡锛屽湪InnoDB琛ㄤ笂璋冪敤鏄�娌℃湁澶�澶氭剰涔夌殑锛屽洜涓鸿��闇�姹傜殑InnoDB琛ㄩ攣瀹氬彲鑳戒細琚�绔嬪嵆閲婃斁銆�

·         鏈夋椂锛屽湪浜嬪姟鐨勮繃绋嬩腑閿佸畾鏇村�氱殑琛ㄥ彲鑳芥槸鏈夌敤鐨勩�備笉骞稿湴锛孧ySQL涓�鐨凩OCK TABLES鎵ц�屼竴涓�鏆楀湴鐨凜OMMIT鍜孶NLOCK TABLES銆侺OCK TABLES鐨勪竴涓狪nnoDB鍙橀噺宸茬粡琚�璁″垝锛� 璇ヨ�″垝鍦ㄤ簨鍔＄殑涓�闂磋��鎵ц�屻��

·         涓哄缓绔嬪�嶅埗浠庢湇鍔″櫒鐨凩OAD TABLE FROM MASTER璇�鍙ュ��InnoDB琛ㄤ笉璧蜂綔鐢ㄣ�備竴涓�宸ヤ綔鍖哄湪涓绘湇鍔″櫒涓婃洿鎹㈣〃涓�MyISAM鐨勶紝鐒跺悗鍋氳礋杞斤紝涔嬪悗鏇存崲涓绘湇鍔″櫒琛ㄥ洖鍒癐nnoDB涓�銆�

·         鍦↖nnoDB涓�榛樿�ゆ暟鎹�搴撻〉鐨勫ぇ灏忔槸16KB銆傞�氳繃缂栬瘧浠ｇ爜锛屼綘鍙�浠ュ湪8KB鍒�64KB涔嬮棿鏉ヨ�剧疆杩欎釜鍊笺�備綘涓嶅緱涓嶆洿鏂板湪univ.i婧愭枃浠朵腑鐨刄NIV_PAGE_SIZE鍜孶NIV_PAGE_SIZE_SHIFT鐨勫�笺��

·         鍦∕ySQL 5.1涓�锛岃Е鍙戝櫒涓嶈��绾ц仈鐨勫�栭敭琛屼负婵�娲汇��

15.2.17.1. InnoDB鏁版嵁璇嶅吀鎿嶄綔鐨勯敊璇�璇婃柇鍜屾帓闄�

·         涓�涓�鎬荤殑瑙勫垯鏄�锛屽綋涓�涓�鎿嶄綔澶辫触鎴栬繖浣犳��鐤戞湁涓�涓�缂洪櫡銆備綘搴旇�ユ煡鐪婱ySQL鏈嶅姟鍣ㄧ殑閿欒��鏃ュ織锛岃�ユ棩蹇楀吀鍨嬪湴鏈変竴涓�鏈変簺璞�hostname.err杩欐牱鐨勫悕瀛楋紝鎴栬�呭湪Windows涓婃槸mysql.err杩欐牱鐨勩��

·         鏁呴殰璇婃柇涓庢帓闄や箣鏃讹紝閫氬父鏈�濂戒粠鍛戒护鎻愮ず绗﹁繍琛孧ySQL鏈嶅姟鍣�锛岃�屼笉鏄�浠�mysqld_safe鍖呰繍琛岋紝鎴栦笉浣滀负涓�涓猈indows鏈嶅姟鏉ヨ繍琛屻�備綘鍙�浠ョ湅mysqld鎵撳嵃鍒版帶鍒跺彴涓婄殑鍐呭�癸紝鍥犳�ゆ洿濂芥帉鎻″彂鐢熶簡浠�涔堛�傚湪Windows涓婏紝浣犲繀椤荤敤--console閫夐」鍚�鍔ㄦ湇鍔″櫒灏嗚緭鍑哄畾鍚戝埌鎺у埗鍙扮獥鍙�

·         浣跨敤InnoDB Monitors鑾峰彇鍏充簬鏌愰棶棰樼殑淇℃伅銆傚�傛灉闂�棰樻槸鎬ц兘鐩稿叧鐨勶紝鎴栬�呬綘鐨勬湇鍔″櫒鐪嬭捣鏉ヨ��鎸傝捣锛屼綘搴旇�ヤ娇鐢╥nnodb_monitor鏉ユ墦鍗癐nnoDB鍐呴儴鐘舵�佺殑淇℃伅锛屽�傛灉闂�棰樻槸鍏充簬閿佸畾锛屽垯浣跨敤innodb_lock_monitor銆傚�傛灉闂�棰樻槸鍦ㄨ〃鐨勫垱寤烘垨鍏跺畠鏁版嵁璇嶅吀鎿嶄綔锛屼娇鐢╥nnodb_table_monitor鏉ユ墦鍗癐nnoDB鍐呴儴鏁版嵁璇嶅吀鐨勫唴瀹广��

·         濡傛灉浣犵寽娴嬩竴涓�琛ㄨ��鐮村潖锛屽垯鍦ㄨ�ヨ〃涓婅繍琛孋HECK TABLE銆�

琛ㄧ殑涓�涓�鐗规畩闂�棰樻槸MySQL鏈嶅姟鍣ㄤ互.frm鏂囦欢鏉ヤ繚瀛樻暟鎹�璇嶅吀淇℃伅锛屽畠琚�鏀惧湪鏁版嵁搴撶洰褰曪紝鐒惰�孖nnoDB涔熷瓨鍌ㄤ俊鎭�鍒拌〃绌洪棿鏂囦欢閲屽畠鑷�宸辩殑鏁版嵁璇嶅吀閲屻�傚�傛灉浣犳妸.frm鏂囦欢绉绘潵绉诲幓 锛涙垨鑰咃紝濡傛灉鏈嶅姟鍣ㄥ湪鏁版嵁璇嶅吀鎿嶄綔鐨勪腑闂村穿婧冿紝.frm鏂囦欢鍙�鑳界粨鏉熶笌InnoDB鍐呴儴鏁版嵁璇嶅吀鐨勫悓姝ャ��

涓�涓�涓嶅悓姝ョ殑鏁版嵁璇嶅吀鐨勭棁鐘舵槸CREATE TABLE璇�鍙ュけ璐ャ�傚�傛灉鍙戠敓杩欑�嶆儏鍐碉紝浣犲簲璇ユ煡鐪嬫湇鍔″櫒鐨勯敊璇�鏃ュ織銆傚�傛灉鏃ュ織璇磋〃宸茬粡瀛樺湪浜嶪nnoDB鍐呴儴鏁版嵁璇嶅吀褰撲腑锛屼綘鍦↖nnoDB琛ㄧ┖闂存枃浠跺唴鏈変竴涓�瀛よ〃锛屽畠娌℃湁瀵瑰簲鐨�.frm鏂囦欢銆傞敊璇�淇℃伅鐪嬭捣鏉ヨ薄濡備笅鐨勶細

InnoDB: Error: table test/parent already exists in InnoDB internal

InnoDB: data dictionary. Have you deleted the .frm file

InnoDB: and not used DROP TABLE? Have you used DROP DATABASE

InnoDB: for InnoDB tables in MySQL version <= 3.23.43?

InnoDB: See the Restrictions section of the InnoDB manual.

InnoDB: You can drop the orphaned table inside InnoDB by

InnoDB: creating an InnoDB table with the same name in another

InnoDB: database and moving the .frm file to the current database.

InnoDB: Then MySQL thinks the table exists, and DROP TABLE will

InnoDB: succeed.

浣犲彲浠ユ寜鐓ч敊璇�鏃ュ織閲岀粰鐨勬寚绀虹Щ闄や竴涓�瀛よ〃銆傚�傛灉杩樻槸涓嶈兘鎴愬姛鍦颁娇鐢―ROP TABLE锛岄棶棰樺彲鑳芥槸鍥犱负鍦�mysql瀹㈡埛绔�閲岀殑鍚嶅瓧瀹屾垚銆傝�佽В鍐宠繖涓�闂�棰橈紝鐢�--disable-auto-rehash閫夐」鏉ュ惎鍔�mysql瀹㈡埛绔�骞跺啀娆″皾璇旸ROP TABLE 銆傦紙鏈夊悕瀛楀畬鎴愭墦寮�鐫�锛�mysql璇曠潃鏋勫缓涓�琛ㄥ悕瀛楃殑鍒楄〃锛屽綋涓�涓�姝ｅ�傛弿杩扮殑闂�棰樺瓨鍦ㄤ箣鏃讹紝杩欎釜鍒楄〃灏变笉璧蜂綔鐢�锛夈��

涓嶅悓姝ユ暟鎹�璇嶅吀鐨勫彟涓�涓�“鍚屼箟璇�”鏄疢ySQL鎵撳嵃涓�涓�涓嶈兘鎵撳紑.InnoDB鏂囦欢鐨勯敊璇�锛�

ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)

鍦ㄩ敊璇�鏃ュ織浣犲彲浠ュ彂鐜颁竴涓�绫讳技浜庢�ょ殑淇℃伅锛�

InnoDB: Cannot find table test/child2 from the internal data dictionary

InnoDB: of InnoDB though the .frm file for the table exists. Maybe you

InnoDB: have deleted and recreated InnoDB data files but have forgotten

InnoDB: to delete the corresponding .frm files of InnoDB tables?

杩欐剰鍛宠繖鏈変竴涓�瀛ゅ崟鐨�.frm鏂囦欢锛屽湪InnoDB鍐呮病鏈夌浉瀵瑰簲鐨勮〃銆備綘鍙�浠ラ�氳繃鎵嬪姩鍒犻櫎鏉ョЩ闄よ繖涓�瀛ゅ崟鐨�.frm鏂囦欢銆�

濡傛灉MySQL鍦ㄤ竴涓� ALTER TABLE鎿嶄綔鐨勪腑闂村穿婧冿紝浣犲彲浠ョ敤InnoDB琛ㄧ┖闂村唴涓存椂瀛よ〃鏉ョ粨鏉熴�備綘鍙�浠ョ敤innodb_table_monitor鐪嬩竴涓�鍒楀嚭鐨勮〃锛屽悕涓�#sql-...銆傚�傛灉浣犳妸琛ㄧ殑鍚嶅瓧鍖呭湪`(backticks)閲岋紝浣犲彲浠ュ湪鍚嶅瓧鍖呭惈“#”瀛楃�︾殑琛ㄤ笂鎵ц�孲QL璇�鍙ャ�傚洜姝わ紝浣犲彲浠ョ敤鍓嶈堪鐨勭殑鏂规硶璞＄Щ闄ゅ叾瀹冨�よ〃涓�鏍风Щ闄よ繖鏍蜂竴涓�瀛よ〃銆傛敞鎰忥紝瑕佸湪Unix澶栧３閲屽�嶅埗鎴栭噸鍛藉悕涓�涓�鏂囦欢锛屽�傛灉鏂囦欢鍚嶅寘鍚�"#"瀛楃�︼紝浣犻渶瑕佹妸鏂囦欢鍚嶆斁鍦ㄥ弻寮曞彿閲屻��

·         濡傛灉浣犱娇鐢ˋLTER TABLE 鏉ユ妸MERGE琛ㄥ彉涓哄叾瀹冭〃绫诲瀷锛屽埌 搴曞眰琛ㄧ殑鏄犲皠灏辫��涓㈠け浜嗐�傚彇鑰屼唬涔嬬殑锛屾潵鑷�搴曞眰MyISAM琛ㄧ殑琛岃��澶嶅埗鍒板凡鏇存崲鐨勮〃涓�锛岃�ヨ〃闅忓悗琚�鎸囧畾鏂扮被鍨嬨��

·         REPLACE涓嶈捣浣滅敤銆�

·         娌℃湁WHERE瀛愬彞锛屾垨鑰呭湪浠讳綍琚�鏄犲皠鍒颁竴涓�鎵撳紑鐨凪ERGE琛ㄤ笂鐨勪换浣曚竴涓�琛ㄤ笂鐨凴EPAIR TABLE锛孴RUNCATE TABLE, OPTIMIZE TABLE鎴朅NALYZE TABLE锛屼綘涓嶈兘浣跨敤DROP TABLE, ALTER TABLE, DELETE FROM銆傚�傛灉浣犺繖涔堝仛浜嗭紝MERGE琛ㄥ皢浠嶆棫鎸囧悜鍘熷�嬭〃锛岃繖鏍蜂骇鐢熸剰澶栫粨鏋溿�傝В鍐宠繖涓�涓嶈冻鏈�绠�鍗曠殑鍔炴硶鏄�鍦ㄦ墽琛屼换浣曚竴涓�杩欎簺鎿嶄綔涔嬪墠鍙戝嚭涓�涓狥LUSH TABLES璇�鍙ユ潵纭�淇濇病鏈塎ERGE琛ㄤ粛鏃т繚鎸佹墦寮�銆�

·         涓�涓狹ERGE琛ㄤ笉鑳藉湪鏁翠釜琛ㄤ笂缁存寔UNIQUE绾︽潫銆傚綋浣犳墽琛屼竴涓狪NSERT, 鏁版嵁杩涘叆绗�涓�涓�鎴栬�呮渶鍚庝竴涓狹yISAM琛�锛堝彇鍐充簬INSERT_METHOD閫夐」鐨勫�硷級銆侻ySQL纭�淇濆敮涓� 閿�鍊煎湪閭ｄ釜MyISAM琛ㄩ噷淇濇寔鍞�涓�锛屼絾涓嶆槸璺ㄩ泦鍚堥噷鎵�鏈夌殑琛ㄣ��

·         褰撲綘鍒涘缓涓�涓狹ERGE琛ㄤ箣鏃讹紝娌℃湁妫�鏌ュ幓纭�淇濆簳灞傝〃鐨勫瓨鍦ㄤ互鍙婃湁鐩稿悓鐨勬満鏋勩�傚綋MERGE琛ㄨ��浣跨敤涔嬫椂锛孧ySQL妫�鏌ユ瘡涓�琚�鏄犲皠鐨勮〃鐨勮�板綍闀垮害鏄�鍚︾浉绛夛紝浣嗚繖骞朵笉鍗佸垎鍙�闈犮�傚�傛灉浣犱粠涓嶇浉浼肩殑MyISAM琛ㄥ垱寤轰竴涓狹ERGE琛�锛屼綘闈炲父鏈夊彲鑳芥挒瑙佸�囨��鐨勯棶棰樸��

·         鍦∕ERGE琛ㄤ腑鐨勭储寮曠殑椤哄簭鍜屽畠鐨� 搴曞眰琛ㄤ腑鐨勭储寮曞簲璇ヤ竴鏍枫�傚�傛灉浣犱娇鐢ˋLTER TABLE缁欎竴涓�琚�鐢ㄥ湪MERGE琛ㄤ腑鐨勮〃娣诲姞涓�涓猆NIQUE绱㈠紩锛岀劧鍚庝娇鐢ˋLTER TABLE鍦∕ERGE琛ㄤ笂娣诲姞涓�涓�闈炲敮涓�绱㈠紩锛屽�傛灉鍦� 搴曞眰琛ㄤ笂宸茬粡鏈変竴涓�闈炲敮涓�绱㈠紩锛屽�硅〃鐨勭储寮曟帓搴忔槸涓嶅悓鐨勩�傦紙杩欐槸鍥犱负ALTER TABLE鎶奤NIQUE绱㈠紩鏀惧湪闈炲敮涓�绱㈠紩涔嬪墠浠ュ埄浜庨噸澶嶉敭鐨勫揩閫熸��娴� 锛夈�傚洜姝ゅ�逛娇鐢ㄨ繖鏍风储寮曠殑琛ㄧ殑鏌ヨ�㈠彲鑳借繑鍥炰笉鏈熸湜鐨勭粨鏋溿��

·         鍦╓indows涓�锛屽湪涓�涓�琚�MERGE琛ㄤ娇鐢ㄧ殑琛ㄤ笂DROP TABLE涓嶈捣浣滅敤锛屽洜涓�MERGE寮曟搸鐨勮〃鏄犲皠瀵筂ySQL鐨勬洿涓婂眰闅愯棌銆傚洜涓篧indows涓嶅厑璁稿凡鎵撳紑鏂囦欢鐨勫垹闄わ紝浣犻�栧厛蹇呴』 鍒锋柊鎵�鏈塎ERGE琛�锛堜娇鐢‵LUSH TABLES锛夋垨鍦ㄧЩ闄よ�ヨ〃涔嬪墠绉婚櫎MERGE琛ㄣ��

-        濡傛灉涓�涓�鍐呴儴琛ㄥ彉寰楀お澶э紝鏈嶅姟鍣ㄨ嚜鍔ㄦ妸瀹冭浆鎹�涓轰竴涓�纾佺洏琛ㄣ�傚昂瀵搁檺鍒剁敱tmp_table_size绯荤粺鍙橀噺鐨勫�兼潵纭�瀹氥��

-        MEMORY琛ㄥ喅涓嶄細杞�鎹㈡垚纾佺洏琛ㄣ�傝�佺‘淇濅綘涓嶄細鍋跺皵鍋氱偣鍌讳簨锛屼綘鍙�浠ヨ�剧疆max_heap_table_size绯荤粺鍙橀噺缁橫EMORY琛ㄥ姞浠ユ渶澶у昂瀵搞�傚�逛簬鍗曚釜鐨勮〃锛屼綘涔熷彲浠ュ湪CREATE TABLE璇�鍙ヤ腑鎸囧畾涓�涓狹AX_ROWS琛ㄩ�夐」銆�

·         Linux 2.x Intel

·         Sun Solaris (SPARC and x86)

·         FreeBSD 4.x/5.x (x86, sparc64)

·         IBM AIX 4.3.x

·         SCO OpenServer

·         SCO UnixWare 7.1.x

·         Windows NT/2000/XP

·         Linux 2.x Alpha

·         Linux 2.x AMD64

·         Linux 2.x IA-64

·         Linux 2.x s390

·         Mac OS X

·         --bdb-home=path

BDB琛ㄧ殑鍩虹��鐩�褰曘�傝繖搴旇�ュ拰浣犱负--datadir浣跨敤鐨勭洰褰曠浉鍚屻��

·         --bdb-lock-detect=method

BDB 閿佸畾妫�娴嬫柟寮忋�傞�夐」鍊煎簲璇ヤ负DEFAULT, OLDEST, RANDOM鎴朰OUNGEST銆�

·         --bdb-logdir=path

BDB鏃ュ織鏂囦欢鐩�褰曘��

·         --bdb-no-recover

涓嶅湪鎭㈠�嶆ā寮忓惎鍔˙erkeley DB銆�

·         --bdb-no-sync

涓嶅悓姝ュ埛鏂癇DB鏃ュ織銆傝繖涓�閫夐」涓嶈��璧炴垚锛屽彇鑰屼唬涔嬪湴浣跨敤--skip-sync-bdb-logs锛堣�峰弬闃呭��--sync-bdb-logs鐨勬弿杩�)銆�

·         --bdb-shared-data

浠ュ�氬�勭悊妯″紡鍚�鍔˙erkeley DB銆傦紙鍒濆�嬪寲Berkeley DB涔嬫椂锛屼笉瑕佷娇鐢―B_PRIVATE锛夈��

·         --bdb-tmpdir=path

BDB涓存椂鏂囦欢鐩�褰曘��

·         --skip-bdb

绂佹��BDB瀛樺偍寮曟搸銆�

·         --sync-bdb-logs

鍚屾�ュ埛鏂癇DB鏃ュ織銆傝繖涓�閫夐」榛樿�よ��鍏佽�革紝璇蜂娇鐢�--skip-sync-bdb-logs鏉ョ�佹�㈠畠銆�