µؼ/span>15ւ£º´洢ӽȦºͱэ

·         --myisam-recover=mode

ʨ׃Ϊ±@£MyISAM±¶¯»ָ´µń£ʽ¡£

·         --delay-key-write=ALL

¶Ոκ͍yISAM±���д²ڗ�䲻Ҫˢт¼��span>

עˍ£ºɧ¹�֢ôض¡£µ±±ʹԃא֮ʱ£¬ţӦ¸ò»ʹԃ4ؔ­һ¸�򶃍yISAM±���±Ɉ絓­һ¸�QL·�󔁼strong>myisamchk£©¡£֢ôض»ᵼ̷ׂӽ±»dž»µ¡£

¶Պ¹ԃ--delay-key-writeµı���ʹԃ--external-lockingûԐ°𐗺¡£

·         bulk_insert_buffer_size

ԃ՚¿鳥ɫԅ»¯אµŊ�戸µĴ�£עˍ£º֢ˇһ¸�>per threadµŏߖơ£

·         (OBSOLETE) myisam_max_extra_sort_file_size

֢¸��»՚MySQLאʹԃ¡£

·         myisam_max_sort_file_size

ɧ¹�τ¼�ó¬¹�¬²»Ҫʹԃ¿팙ƅѲ̷ӽ·½·¨4´´½¨һ¸��rong>עˍ£º֢¸��ڵŐϊ½¸�

·         myisam_sort_buffer_size

ʨ׃»ָ´±ʱʹԃµĻº³戸µijߴ硣

·         ±���²鵭¡£

·         ɧ¹�Ƿ·¢Жһ¸�󣭋��ٱ¸´£¨ƅѲȒ²»טт´´½¨˽¾ގļ�

·         ɧ¹�ӲΪ˽¾ގļ�»¸�󷹊§°ܣ¨}ɧ£¬һ¸�´¼�©£¬·�╙´γ¢˔ў¸´£¬֢һ´ϖؽ¨˽¾ގļ�:p>

·         ɧ¹�ɔȻʧ°ܣ¬·�þʐ޸´ѡЮ·½·¨ՙט˔һ´ϐ޸´£¨һѐ½Ԓ»ѐµِ´£¬²»ƅѲ£©¡£֢¸�¨Ӧ¸Ąݐ޸´ɎºπᑍµĴ£¬²¢ȒѨҪºܵ͵Ĵƅ̿ռ䡣

15.1.3.1. ¾²̬£¨¹̶¨³¤¶ȣ©±ַ

15.1.3.2. ¶¯̬±ַ

15.1.3.3. ӑѹ̵±ַ

¾²̬¸򊾊ƍyISAM±���Ĭɏ´洢¸򊽡£µ±±���°�¿³¤¶ɁУ¨VARCHAR, BLOB, »񓄘T£©ʱ£¬ʹԃ֢¸�½¡£ÿһѐԃ¹̶¨ؖ½ۊ�£

MyISAMµň�¢¸򊾖У¬¾²̬¸򊽾Ηµ¥Ҳˇخ°²ȫµģ¨ׁʙ¶Փؼ/span>±@£¶�¡£¾²̬¸򊾒²ˇخ¿춄on-disk¸򊽡£¿팙4ؔԚ˽¾ގļ�єڴƅ͉ϱ»֒µ½µňޒ׷½ʽ£ºµ±°´̷֕ӽאµŐкŲꖒһ¸�±£¬ԃѐ³¤¶ȳ̒Րкš£ͬҹ£¬µ±ɨĨһ¸� ʱº򣬺݈ޒؓă¿¸�̶rڗ�»¶¨˽µļȂ¼¡£

µ±MySQL·���¨¸򊼍yISAMτ¼�±º򣭈纻¼Nj㼺±@£‹£¬°²ȫˇДȻµġ£՚֢זȩ¿�¬myisamchk¿ʒՈޒ׵ؾ�¿ѐ´Ԅŀ￪ʼµ½ńo½⋸£¬̹Ӕ̼ͨ³£¿ʒՊջً�¼£¬³�̒»²¿·ֵļȂ¼¡£עӢ£¬»�¾ސУ¬MyISAM±ӽ¿ʒՒ»ֱ±»טт¹¹½¨¡£

¾²̬¸򊽱���һ°䍘ַ£º

ɧ¹�MyISAM±���º¬Ɏºοɱ䳤¶ƍ £¨VARCHAR, BLOB»񓄘TDynamic£©£¬»󖠈纻һ¸�»ԃROW_FORMAT=DYNAMICѡЮ4´´½¨£¬¶¯̬´洢¸򊽱»ʹԃ¡£

֢¸�½¸�Ԓ»µ㣬ӲΪÿѐԐһ¸��ж೤µō·¡£µ±һ¸�¼ӲΪ¸�ẻ±»±䶃¸�üȂ¼Ҳ¿ʒՔڳ¬¹�»׃´¦½⋸¡£

ţ¿ʒՊ¹ԃOPTIMIZE TABLE»𻲴rong>myisamchk4¶Ւ»¸��Ƭ¡£ɧ¹�¸�ѓф䆵·±·Ďʻ򹄱䶄¹̶¨³¤¶ƍ £¬±ҲԐһЩ¿ɱ䳤¶ɁУ¬½�݃㌩Ƭ¶�Щ¿ɱ䳤¶ɁђƵ½Ǥ̼±Ŝˇһ¸��£

¶¯̬¸򊽱���һ°䍘ַ£º

·                3

·                + (number of columns + 7) / 8

·                + (number of char columns)

·                + (packed size of numeric columns)

·                + (length of strings)

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

ӑѹ̵´洢¸򊾊ȓǼstrong>myisampack¹¤¾ߴ´½¨µŖ»¶x򊽡£

̹ԐMySQL·ַ¢°灯¶¼Ĭɏ°�rong>myisampack¡£ӑѹ̵±Ӕԃmyisamchk4½㑹̵¡£

ӑѹ̵±Ђַ͘£º

15.1.4.1. ̰»µµ¼/span>MyISAM±뺯span>

15.1.4.2. δ±»ˊµ±¹رյı���ϊ͢

¼´ʹMyISAM±���ʽ·dz£¿ɿ¿£¨SQLԯ¾䷔±µŋ�±啚ԯ¾䷵»ٖ®ǰ±»дЂ£©£¬ɧ¹�Ɏºϊ¼�¬ţӀȻ¿ʒԻ񶄋𻵵ı���

һ¸�µµı���µ呍֢״ɧЂ£º

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

ţ¿ʒՓƒHECK TABLE statementԯ¾倴¼쳩MyISAM±뺯span>µĽ¡¿µ£¬²¢ԃREPAIR TABLEў¸´һ¸􋋋𻵵ÍyISAM±���µ±mysqld²»Ջѐ֮ʱ£¬ţҲ¿ʒՓ|strong>myisamchkļ®¼쳩»󑟀¸�£ȫ²ϔ¼a href="http://book.chinaz.com/mysql5/sql-syntax.html#check-table" title="13.5.2.3. CHECK TABLE Syntax">13.5.2.3½ڣ¬¡°CHECK TABLEԯ·¨”£¬ 13.5.2.6½ڣ¬¡°REPAIR TABLEԯ·¨”£¬º˼a href="http://book.chinaz.com/mysql5/database-administration.html#myisamchk" title="5.9.5. myisamchk — MyISAM Table-Maintenance Utility">5.9.5½ڣ¬¡°myisamchk — MyISAM±»¤¹¤¾ݦrdquo;¡£

ɧ¹�±���µĆµ·±̰»µ£¬ţӦ¸Ċ՗ƈ·¶¨Ϊʲô»▢ҹµŔ­Ӳ¡£Ҫķ°׵ŗҪµŊÊDZ���µċ𻶊Dz»ˇӲΪ·�@£µĽẻ¡£ţ¿ʒՔڴɕ־א²ꖒخ½�tarted mysqldлϢ4էǚҩ֤֢¸�纻´畚֢ҹһ¸�¢£¬ղ±»µˇ·�5�»¸��Ԑ¿ʄܵġ£·򕲣¬̰»µ¿ʄݔە��¢ʺ¡£֢ˇһ¸�ݡ£ţӦ¸Ċ՗Ŵ´½¨һ¸�¾֢¸�ⶄ¿ʖظ´ʺ³ɵIJ㋔°¸}¡£ȫ²ϔ¼a href="http://book.chinaz.com/mysql5/problems.html#crashing" title="A.4.2. What to Do If MySQL Keeps Crashing">A.4.2½ڣ¬¡°ɧ¹�L±£³ֱ@££¬¸Ĕ��quo; ¼°E.1.6½ڣ¬¡°ɧ¹�±���#£¬ȫʺ³ɲ㋔°¸}”¡£

ÿ¸�SAM̷ӽτ¼�)՚ͷԐһ¸���»ԃ4¼쳩һ¸�Ƿ񱼇¡µ±µعرա£ɧ¹�CHECK TABLE»𻲴rong>myisamchkµõ½Ђ¾¯¸棬Ӣζ؅֢¸��­²»ͬ²½‹£º

clients are using or haven't closed the table properly

֢¸�沢²»ˇΪȫӢζ؅±±»dž»µ£¬µ«ţׁʙӦ¸ü쳩±���

¼NJ�¤ط·½ʽɧЂ£º

»»¾仰4˵£¬¼NJ�єۏÁчꀶ»᲻ͬ²½£º

·         ֻӦ¸ô´½¨һ¸�cnf»󏄼�pan>

·         my.cnfτ¼�»·Ɣكƌ¸�¡£

·         my.iniτ¼�»·ƖĔٗINDIRĿ¼£»}ɧC:\WINDOWS»񁹜WINNT¡£ţ¿ʒՔٗindows¿ٖnj¨µŃ�¾·�SETļ®4´󓠗INDIRµŖµ£º

·                C:\> SET WINDIR

·                windir=C:\WINNT

·         ɧ¹�PC՚Cƌ²»ˇǴ¶¯ƌµĵط½ʹԃǴ¶¯װ՘»�Ψһµő¡ձˇʹԃmy.iniτ¼�:p>

·         ɧ¹�ԃ°²װº΅嗃вµ¼°²װµÍySQL£¬my.iniτ¼�ٍySQLµİ²װĿ¼¡£ȫ²ϔ¼a href="http://book.chinaz.com/mysql5/installing.html#mysql-config-wizard-file-location" title="2.3.5.14. The Location of the my.ini File">2.3.5.14½ڣ¬¡°my.iniτ¼�frdquo;¡£

·         /etc/my.cnf

ȫ¾ב¡Ю¡£

·         $MYSQL_HOME/my.cnf

·�¨ԃѡЮ¡£

·         defaults-extra-file

--defaults-extra-fileѡЮָ¶¨µŎļ�pan>

·         ~/.my.cnf

ԃ»§רԃѡЮ¡£

·         innodb_additional_mem_pool_size

InnoDBԃ4´洢˽¾ބ¿¼хϢ£¦Ǥ̼Ś²¿˽¾ݽṹµńڴ洘µĴ�£ţӦԃ³͐󁯵ı¶࣬ţѨҪ՚֢o·ׅ唽¶඄Ś´档ɧ¹�DBԃ¹る֢¸�ڵńڴ棬InnoDB¿ªʼ´Ӳڗ�³·ׅ党´棬²¢ȒιMySQL´ɕ־д¾¯¸瑅Ϣ¡£ Ĭɏֵˇ1MB¡£

·         innodb_autoextend_increment

µ±ؔ¶¯)չ±¼䱻ͮú֮ʱ£¬Ϊ)չ¶�µijߴ磨MBΪµ¥λ£©¡£ Ĭɏֵˇ8¡£֢¸�Ӕ՚ՋѐʱطΪȫ¾׏µͳ±偿¶�¡£

·         innodb_buffer_pool_awe_mem_mb

ɧ¹�³ر»·Ɣس2λWindowsµÁWEŚ´灯£¬֢¸��º³崘µĴ�¨MBΪµ¥λ£©¡£(½�2λWindowsʏР¹أ©ɧ¹�32λWindows²ڗ�³ʹԃ̹νµ¦ldquo;µٖ·´°¿ۀ©չ£¨AWE)”֧³ֳ¬¹�´棬ţ¿ʒՓĕ⹶²ϊ�oDB»º³崘·ׅ侸AWEϯmŚ´档֢¸��Ŀʄݖµˇ64000¡£ɧ¹�²ϊ�¨‹£¬innodb_buffer_pool_sizeˇ՚32λµٖ·¿ռ䶄mysqldŚµĴ°¿ڣ¬InnoDB°҄Ǹ�Ś´瓳ʤʏȥ¡£¶өnnodb_buffer_pool_size²ϊ��ϺõŖµˇ500MB¡£

·         innodb_buffer_pool_size

InnoDBԃ4»º´猼µŊ��ńڴ滺³戸µĴ�£ţ°ҕ⹶ֵʨµĔ½¸ߣ¬·Ďʱ˽¾ސ钪µôƅˉ/OԽʙ¡£՚һ¸�õŊ��ϣ¬ţ¿ʒՉ闃֢¸���´浳Сµ¸0%¡£¾¡¹݈絋£¬»¹ˇ²»Ҫ°ҋ�Č«´󣭒󎪶Վ𐁭Ś´涄¾ºֹ¿ʄݔڲڗ�³ʏµ¼ׂŚ´涷¶ȡ£

·         innodb_checksums

InnoDB՚̹Ԑ¶Դƅ̵Œ³Ħ¶ˆ¡ʏʹԃУҩºΑꖤӔȷ±£¶ɝ´���ֹӲ¼�󋽾ގļ�݈絋£¬՚һЩʙ¼�¿�¨±Ɉ蕋ѐ±뗼¼쳩֮ʱ£©֢¸�ⶄ°²ȫַ͘ˇ²»±ْªµġ£՚֢Щȩ¿�¬֢¸� ĬɏˇՊѭµģ©¿ʒՓm-skip-innodb-checksums4¹رա£

·         innodb_data_file_path

µ½µ¥¶J��dzߴ綄·¾¶¡£ͨ¹�odb_data_home_dirl½ӵ½֢oָ¶¨µŃ¿¸�¶£¬µ½ÿ¸�ގļ��·¾¶¿ɱ»»񶃡£τ¼�¨¹�薵β¼ҍ»񈓔MB»󖞇B£¨1024MB£©Ϊµ¥λ±»ָ¶¨¡£τ¼�ĺΖ‰ڊű0MB¡£՚һЩ²ڗ�³ʏ£¬τ¼�¡Ԛ2GB¡£ɧ¹�Ԑָ¶¨innodb_data_file_path£¬¿ªʼµń¬ɏѐΪˇ´´½¨һ¸�5Ĵ�0MBĻΪibdata1µŗՀ©չ˽¾ގļ�Ȑ©֧³ִ􏄼��³ʏ£¬ţ¿ʒՉ闃τ¼�¬¹�ţҲ¿ʒՊ¹ԃԭʼ´ƅ̷ׇ�˽¾ގļ�ϔ¼a href="http://book.chinaz.com/mysql5/storage-engines.html#innodb-raw-devices" title="15.2.14.2. Using Raw Devices for the Tablespace">15.2.14.2½ڣ¬¡°Ϊ±¼効ԃԭʼʨ±¸”¡£

·         innodb_data_home_dir

Ŀ¼·¾¶¶Ջ�noDB˽¾ގļ�¬²¿·֡£ɧ¹�ʨ׃֢¸�¬ ĬɏˇMySQL˽¾ބ¿¼¡£ţҲ¿ʒՖ¸¶¨֢¸�ªһ¸�ַ�՚֢זȩ¿�¬ţ¿ʒՔ٩nnodb_data_file_pathאʹԃ¾�¼�£

·         innodb_doublewrite

Ĭɏµأ¬InnoDB´洢̹Ԑ˽¾ށ½´Σ¬µے»´δ洢µ½doublewrite»º³壬Ȼº󵦴¢µ½ȷʵµŊ���Ӕ±»ԃ4½�¸�ܡ£`̆Ԛinnodb_checksums£¬֢¸�싊ĬɏˇՊѭµģ»ӲΪ±뗼¼쳩»󕚶Զ¥¼¶єŜµŐ钪³¬¹�ލ떻є»󀊄ܹ˕ϵĹٗ¢֮ʱ£¬֢¸�--skip-innodb-doublewrite4¹رա£

·         innodb_fast_shutdown

ɧ¹�֢¸��ª0£¬InnoDB՚¹ر֖®ǰضһ¸�«¾»»¯ºΒ»¸�뻺³廏²¢¡£֢Щ²ڗ�¨¼¸·זԊ±¼䣬ʨ׃՚¼«¶̇ꀶЂҪ¼¸¸�±¡£ɧ¹�׃֢¸��InnoDB՚¹ر֖®ʱ͸¹�ڗ� ĬɏֵΪ1¡£ɧ¹�׃֢¸�ª2 (՚NetwareϞ´̖µ)£¬ InnoDB½«ˢт̼µň֖¾Ȼº􁤹ػ�·�QL±@£һҹ¡£ӑ͡½»µŊÎ񲻻ᱻ¶ªʧ£¬µ«՚Ђһ´φ�®ʱ»☶һ¸�£»ָ´¡£

·         innodb_file_io_threads

InnoDBאτ¼�³̵Ŋ��¬֢¸�� Ĭɏµģ¬Ĭɏֵˇ4£¬µ«ˇ´􋾖µ¶ӗindows´ƅˉ/OԐӦ¡£՚Unixʏ£¬ն¼ԕ⹶˽ûԐЧ¹�noDB؜ˇʹԃĬɏֵ¡£

·         innodb_file_per_table

֢¸�ʹInnoDBԃؔ¼ºµ®ibdτ¼�¢˽¾ݺ΋�´½¨ÿһ¸����¶�՚¹²Э±¼嗐´´½¨¡£ȫ²ϔ¼a href="http://book.chinaz.com/mysql5/storage-engines.html#multiple-tablespaces" title="15.2.6.6. Using Per-Table Tablespaces">15.2.6.6½ڣ¬¡°ʹԃPer-Table±¼⥲dquo;¡£

·         innodb_flush_log_at_trx_commit

µ±innodb_flush_log_at_trx_commit±» ʨ׃Ϊ0£¬ɕ־»º³惿īһ´εر»дµ½ɕ־τ¼�ҶՈ֖¾τ¼�ƅ̲ڗ�¢т£¬µ«ˇ՚һ¸�򍡽»²»ضɎºβڗ�±֢¸�ª1£¨Ĭɏֵ£©֮ʱ£¬՚ÿ¸�򍡽»ʱ£¬ɕ־»º³屻дµ½ɕ־τ¼�֖¾τ¼�ƅ̲ڗ� ˢт¡£µ±ʨ׃Ϊ2֮ʱ£¬՚ÿ¸�»£¬ɕ־»º³屻дµ½τ¼�»¶Ո֖¾τ¼�ƅ̲ڗ�¢т¡£¾¡¹݈絋£¬՚¶Ո֖¾τ¼�ÔۖµΪ2µŇꀶҲÿī·¢ʺһ´Ρ£ϒć±ِ엢Ӣµ½£¬ӲΪ½�ƅϊ͢£¬ÿīһ´ε ˢт²»ˇ100%±£֤ÿī¶¼·¢ʺ¡£ţ¿ʒՍ¨¹�⹶ֵ²»Ϊ14»񶃽ϺõŐՄܣ¬µ«̦֮ţ»╚һ´α@£א̰ʧ¶�»¼ܖµµŊÎ񡤈纻ţʨ׃֢¸�ª0£¬ŇôɎº̼span>mysqld½�±@£»≾³�°خº�분˂ϱ£¬ɧ¹�׃֢¸�ª2£¬ŇôֻԐ²ڗ�³±@£»򶴵糅»≾³�»īµŊÎ񡣾¡¹݈絋£¬InnoDBµı@£»ָ´²»˜ӰЬ£¬¶�Ϊ֢ҹ±@£»ָ´¿ªʼطԃ¶�Ç֢¸�£עӢ£¬ѭ¶೙طϵͳºΒ»Щ´ƅ͓²¼�­ ˢтµ½´ƅ̲ڗ�¡¹݋¢тûԐ½�ţ¿ʒԸ猟mysqldˢтӑ¾­½�¼´ʹʨ׃֢¸�ª1£¬˂ϱµij־ó̶Ȳ»±»±£֤£¬Ȓ՚خ»µȩ¿���⇆»µInnoDB˽¾ݿ⡣՚SCSI´ƅٖ̿dž�¬»󕚴ƅ͗Չ£¬ʹԃԐº󱸵紘µĴƅ̻º´漡¼ԋڎļ�²¢Ȓʹµòڗ�²ȫ¡£ţҲ¿ʒՊ՗Ɗ¹ԃUnixļ®hdparm4՚Ӳ¼�н�ƌд»º´棬»󊺓Ć匼һЩ¶Փ²¼�͗¨ԃµŃ�⹶ѡЮµ Ĭɏֵˇ1¡£

·         innodb_flush_method

֢¸�՚UnixϵͳʏԐЧ¡£ɧ¹�ѡЮ±»ʨ׃Ϊfdatasync £¨Ĭɏֵ£©£¬InnoDBʹԃfsync()4ˢт˽¾ݺΈ֖¾τ¼��׃ΪO_DSYNC£¬InnoDBʹԃO_SYNC4´򿪲¢ˢтɕ־τ¼�¹ԃfsync()4 ˢт˽¾ގļ��RECT±»ָ¶¨‹£¨՚һЩGNU/Linux°汾ʌ¿ʓã©£¬InnoDBʹԃO_DIRECT4´򿫊��¹ԃfsync()4ˢт˽¾ݺΈ֖¾τ¼�⣬InnoDBʹԃfsync()4ͦ´�async()£¬²¢Ȓ̼ Ĭɏ²»ʹԃO_DSYNC£¬ӲΪ֢¸�ې���Unix±嗖ʏӑ¾­·¢ʺϊ͢¡£

·         innodb_force_recovery

¾¯¸棺֢¸�՚һ¸�±ȩ¿�»¶¨ӥ£¬µ±ʱţЫҪ´ԋ𻵵Ŋ�ª´¢±���¿ʄܵŖµΪ´ѱµ½6¡£֢ЩֵµŒ㋼՚15.2.8.1½ڣ¬¡°ǿ׆»ָ´”אѰ˶¡£طΪһ¸�«´슩£¬µ±֢¸�´�䖮ʱ£¬InnoDBبֹԃ»§ў¸Ŋ�

·         innodb_lock_wait_timeout

InnoDB˂ϱ՚±»»ع�°¿ʒԵȴ��³¬ʱī˽¡£InnoDB՚̼ؔ¼ºµ ̸¶¨±ؔ¶¯¼쳢˂ϱ̸̀²¢Ȓ»ع�񡢉nnoDBԃLOCK TABLESԯ¾嗢Ӣµ½̸¶¨ʨ׃¡£Ĭɏֵˇ50ī¡£

Ϊ՚һ¸�ƽ¨bאخ´􀊄ܵij־ó̶Ⱥ΁¬¹⑔£¬ţӦ¸Ĕۖ�򇸉ϵíy.cnfτ¼�©nnodb_flush_log_at_trx_commit=1º̳ync-binlog=1¡£

·         innodb_locks_unsafe_for_binlog

֢¸�InnoDB̷̑º΋�¨Ĩא¹ر֏Ò»¼�£֢¸� Ĭɏֵˇ¼٣¨false£©¡£

ֽ³£µأ¬InnoDBʹԃһ¸�ǎªnext-key lockingµŋ㷨¡£µ±̷̑»󉩃钻¸��®ʱ£¬InnoDBӔ֢ҹһז·½ʽʵѐѐ¼¶̸¶¨£¬̼¶Ոκϓ�ŋ�Ȃ¼ʨ׃¹²ЭµĻ򷁕¼µŋ�Ӳ´ˣ¬ѐ¼¶̸¶¨ʵ¼ˊȋ�Ȃ¼̸¶¨¡£InnoDB¶Ջ�Ȃ¼ʨ׃µŋ�ӰЬ±»̸¶¨̷ӽ¼Ȃ¼֮ǰµ¦ldquo;gap”¡£ɧ¹�ԃ»§¶Մ³һ̷ӽŚµļȂ¼RԖ¹²ЭµĻ򷁕¼µŋ�­һ¸�§²»Ŝb¼´՚R֮ǰӔ̷ӽµŋ³Ѳ²扫һ¸�ŋ�Ȃ¼¡£֢¸�ׂInnoDB²»՚̷̑»󌸒�ъ¹ԃЂһ ¼�£Ђһ¼�Ո»±»ԃ4ȷ±£΢¼�°ט¸´¼�£עӢ£¬ʹԃ֢¸�Ŝ»ᵼׂһЩ¹µŎˌ⣺¼ډ酣ЫҪԃֵ´�00µı늶·�±¶ˆ¡²¢̸¶¨̹ԐµŗӼȂ¼£¬ͬʱ в̦º�¡¶¨µŐи�©£º

SELECT * FROM child WHERE id > 100 FOR UPDATE;

¼ډ镚idԐһ¸��¯´ҩd´�00µĵے»¸�¼¿ªʼɨĨ̷ӽ¡£ɧ¹�ӽ¼Ȃ¼ʏµŋ�°Ҕڼ叶´¦ʺ³ɵIJ扫ƅ³�¬ͬʱһ¸�б»²徸±¡£ɧ¹�ͬһ¸�򖯄ۖ´ѐͬҹµÓELECT£¬ţ»╚²ꑯ·µ»صĽẻ°�½һ¸�С£֢ҲӢζ؅£¬ɧ¹�Ŀ±»¼ӽ�¿⣬InnoDB²»±£֤lѸє£»¾¡¹݈絋£¬ ¶Փ¦lѸєɔ±»±£֤¡£Ӳ´ˣ¬ɧ¹�ѡЮ±»ʹԃ£¬InnoDB՚´󷡊�¶±𱤖¤READ COMMITTED¡£

֢¸�ׁ¸�«¡£InnoDB՚һ¸�ATE»񃄌ETEא̸ֻ¶¨̼¸�¾³�£֢´󵳼�̋K�Ŝє£¬µ«ˇ¿ʒԷ¢ʺ̸̀¡£עӢ£¬¼´ʹ՚µ±`̆µIJڗ�첻ͬѐʱµŇꀶЂ£¬֢¸�Ȼ²»Պѭ׮ɧUPDATE֢ҹµIJڗ�¹Р̆ѡЮ£¨±Ɉ肭һ¸�ATE£©¡£¿¼ÇЂ}ؓ£º

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

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

COMMIT;

ɧ¹�l½Ԗ´ѐһ¸�¯£º

SET AUTOCOMMIT = 0;

UPDATE A SET B = 5 WHERE B = 3;

²¢ȒǤ̼l½Ӹ�һ¸�Ԗ´ѐǤ̼²ꑯ£º

SET AUTOCOMMIT = 0;

UPDATE A SET B = 4 WHERE B = 2;

½ԗŲꑯ2ҪµȲꑯ1µŌύ»򼘹�󎪲ꑯ1¶ՐУ¨2£¬3£©Ԑһ¸�¼µŋ�²¢Ȓ²ꑯ2՚ɨĨѐµō¬ʱҲ˔؅¶Ջ��ͬһ¸�¨2£¬3£©²ʈ¡һ¸�¼µŋ�֢ˇӲΪµ±innodb_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) - µȴ�͡½»»򼘹�pan>

·         innodb_log_arch_dir

ɧ¹�ʹԃɕ־µµ°¸£¬±»Ϊֻдɫµň֖¾τ¼�ń¿¼Ҳ±»¹鵵¡£֢¸���ԃ‹£¬Ӧ¸ñ»ʨ׃µē麯span>innodb_log_group_home_dirһҹ¡£¾¡¹݈絋£¬̼²»ˇ±ِ趄¡£

·         innodb_log_archive

֢¸�±ǰ±»ʨΪ0¡£ӲΪMySQLʹԃ̼ؔ¼ºµň֖¾τ¼�ހ´»ָ´£¬̹Ӕµ±ǰûԐ±ْª4¹鵵InnoDBɕ־τ¼�� Ĭɏֵˇ0¡£

·         innodb_log_buffer_size

InnoDBԃ4ι´ƅ͉ϵň֖¾τ¼��º³戸µĴ�£ķׇµŖµˇ´ѱMBµ½8MB¡£ ĬɏµŊűMB¡£һ¸�ň֖¾»º³敊ѭ´�Î򕌐ж�Ҫ՚˂ϱ͡½»֮ǰι´ƅ͐´ɕ־¡£Ӳ´ˣ¬ɧ¹�´�Î񣭊¹ɕ־»º³戸¸�۔¼´ƅˉ/O¡£

·         innodb_log_file_size

՚ɕ־ةoÿ¸�¾τ¼�¡¡£՚32λ¼Nj㼺ʏɕ־τ¼�¢´�ِ슙Ԛ4GB¡£ Ĭɏˇ5MB¡£ķׇµŖµ´ѱMBµ½N·ז®һ»º³崘´�¬ǤאNˇةoɕ־τ¼�¿¡£ֵԽ´󣭔ڻº³崘ԽʙѨҪ¼쳩µ䋢тѐΪ£¬Ӕ½۔¼´ƅˉ/O¡£µ«¸�֖¾τ¼�¶֢՚±@£ʱ»ָ´µø�/span>

·         innodb_log_files_in_group

՚ɕ־ةoɕ־τ¼�¿¡£InnoDBӔѭ»··½ʽд½�¡£Ĭɏˇ2£¨Ά¼�£

·         innodb_log_group_home_dir

µ½InnoDBɕ־τ¼�¼·¾¶¡£̼±ِ씐º̩nnodb_log_arch_dirһҹµŖµ¡£ɧ¹�ָ¶¨Ɏº͉nnoDBɕ־²ϊ�¬ɏµŊȔٍySQL˽¾ބ¿¼o´´½¨}¸�´��_logfile0º̩b_logfile1µŎļ�t1:chmetcnv>

·         innodb_max_dirty_pages_pct

֢ˇһ¸�§´Ѱµ½100µŕ�Ĭɏˇ90¡£InnoDBאµŖ�͊՗Ŵӻº³崘дҳĦ£¬ʹµĔᒳ£¨ûԐ±»дµŒ³Ħ£©µİٷֱȲ»³¬¹�µ¡£ɧ¹�SUPERȨО£¬֢¸�ֱȿʒՔڷ�̐ъ±°´ЂĦ4¸ı䣺

SET GLOBAL innodb_max_dirty_pages_pct = value;

·         innodb_max_purge_lag

֢¸�׆՚¾»»¯²ڗ�ͺ�±£¬ɧºϑӳ؉NSERT, UPDATEº̄ELETE²ڗ�¨ȫ²ϔ¼a href="http://book.chinaz.com/mysql5/storage-engines.html#innodb-multi-versioning" title="15.2.12. Implementation of Multi-Versioning">15.2.12½ڣ¬¡°¶౦±¾µŊµʩ”£©¡£֢¸��¬ɏֵˇ££¬ӢΪϞғ³١£֢¸�Ӕ՚ՋѐʱطΪȫ¾׏µͳ±偿¶�±䡣

InnoDB˂ϱϵͳά³ג»¸�򂐱���¸āб±»UPDATE»񃄌ETE²ڗ�¾Ϊɾ³��£Ƀ֢¸����³¤¶Ɏªpurge_lag¡£µ±purge_lag³¬¹�b_max_purge_lag֮ʱ£¬ÿ¸�ERT, UPDATEº̄ELETE²ڗ�נ((purge_lag/innodb_max_purge_lag)*10)-5ºƒ롣՚¾»»¯ƺ´¦mµĿªʼ£¬ғ³ڃ¿¸򯱃뽆̣¡£ɧ¹�һ¸�ĿʒԿ´µ½ѐ±»¾»»¯µŒ»ׂµĶr鿴£¬ ɾ³�»±»ғ³١£

¶Փюˌⶄ¹¤ط£¬µ呍ʨ׃¿ʄ݊ű°ڍ򣬼ډ鏒ćµŊÎ񻝐¡£¬ֻԐ100ؖ½ڴ�¬ϒć¾ͿʒՔːϒćµıאԐ100MBδ¾»»¯µŐС£

·         innodb_mirrored_log_groups

ϒćΪ˽¾ݿⱣ³ֵň֖¾ةŚͬҹ¿½±´µŊ�±ǰ֢¸�¦¸ñ»ʨΪ1¡£

·         innodb_open_files

՚InnoDBא£¬֢¸�ԫţʹԃ¶ಭ¿ռ励Ԑ¹ء£ָ̼¶¨InnoDBһ´οʒԱ£³ִ򿪵®ibdτ¼�􋾄¿¡£خСֵˇ10¡£ Ĭɏֵ300¡£

¶Үibdτ¼���¶ӉnnoDBµġ£̼ć¶A¢ԚŇЩԉ--open-files-limit·�¡Юָ¶¨µŃ鋶·�²»ӰЬ±���´涄²ڗ�/span>

·         innodb_status_file

֢¸�InnoDBΪלǚµÓHOW INNODB STATUSˤ³�¨һ¸��t;datadir>/innodb_status.<pid>¡£

·         innodb_support_xa

µ±±»ʨ׃ΪON»󖝱£¨Ĭɏµأ©£¬֢¸�¿ՊѭInnoDB֧³ה٘A˂ϱאµ ˫в͡½»¡£Պѭinnodb_support_xaµ¼ׂһ¸�ⶄ¶ՊÎ򗼱¸µĴƅ͋¢т¡£ɧ¹�ʹԃXA²¢²»¹ِģ¬ţ¿ʒՍ¨¹�⹶ѡЮΪOFF»𱀴½�¸�¿£¬Ӕ¼�ƅʍ ˢтµĴϊ�ø�nnoDBєŜ¡£

·         innodb_table_locks

InnoDBט˓LOCK TABLES£¬ֱµ½̹ԐǤ̼П³͒Ѿ­ˍ·Ƌ�Ԑ¶Ա���̸¶¨£¬MySQL²ŴҌOCK TABLE .. WRITE·µ»ء£Ĭɏֵˇ1£¬֢ӢΪLOCK TABLESɃInnoDBŚ²¿̸¶¨һ¸�£՚ʹԃAUTOCOMMIT=1µœ¦ԃo£¬InnoDBµńڲ¿±¶¨»ᵼ̸ׂ̀¡£ţ¿ʒՔ٭y.cnfτ¼�dowsʏˇmy.iniτ¼�闃innodb_table_locks=0 4 л³�ˌ⡣

·         innodb_thread_concurrency

InnoDB˔؅՚InnoDBŚ±£³ֲڗ�³П³̵Ŋ�ڻ򶉓ە⹶²ϊ�ŏߖơ£ɧ¹�Ŝϊ͢£¬²¢ȒSHOW INNODB STATUSДʾѭ¶ᐟ³͔ڵȴ�¬¿ʒՈď߳ʦldquo;thrashing” £¬²¢Ȓʨ׃֢¸��򹼴󡤈纻ţµļNj㼺Ԑ¶๶´¦mǷºʹƅ̣¬ţ¿ʒՊ՗ƕ⹶ֵ¸���̣»�Դ¡£һ¸��µˇϵͳʏ´¦mǷºʹƅ̵ĸ�®º͡£ֵΪ500»򲆵00´󼡽�µ�¢·¢¼쳩¡£Ĭɏֵˇ20£¬²¢Ȓɧ¹�´�򶉓ز0£¬²¢·¢¼쳩½«±»½�

·         innodb_status_file

֢¸�InnoDBΪלǚµÓHOW INNODB STATUSˤ³�¨һ¸��t;datadir>/innodb_status.<pid>¡£

15.2.5.1. ´¦mInnoDB³�¯ϊ͢

ɧ¹�DB՚һ¸��д󓢒»¸��³´£¬ͨ³£ϊ͢ˇɧЂאµŒ»¸�

µ±InnoDB˔؅³�¯̼µı¼伲ɕ־τ¼�¬ɧ¹�‹£¬ţӦ¸ĉ¾³�B´´½¨µŋ�¼�㎶؅ˇ̹Ԑibdataτ¼�ϩb_logfilesτ¼�»ţ´´½¨‹һЩInnoDB±���Ϊ֢Щ±´ҍySQL˽¾ݿㄿ¼ɾ³�®frmτ¼�ţʹԃ¶ᗘ±¼䶄»°£¬Ҳɾ³�ibdτ¼�»º􅣿ʒՊ՗Ɣٴδ´½¨InnoDB˽¾ݿ⡣خºĊǴԃ�¾·�MySQL·� £¬Ӕ±䅣¿ʒԲ鿴·¢ʺ‹ʲô¡£

15.2.6.1. ɧºϔؼ/span>InnoDBԃ²»ͬAPI4ʹԃ˂ϱ

15.2.6.2. ת»»MyISAM±���InnoDB

15.2.6.3. AUTO_INCREMENTɧºϔؼspan class="section">InnoDBא¹¤ط

15.2.6.4.΢¼�/span>

15.2.6.5. InnoDBº˼/span>MySQL¸´׆

15.2.6.6. ʹԃPer-Table±¼⺯span>

Ĭɏµأ¬ÿ¸�ӵ½MySQL·�Ŀͻ§¶˿ªʼ֮ʱˇՊѭؔ¶¯͡½»ģʽµģ¬֢¸�½ؔ¶¯͡½»ţՋѐµŃ¿¸�ԯ¾䡣Ҫʹԃ¶ᔯ¾勂ϱ£¬ţ¿ʒՓ“QLԯ¾㒅T AUTOCOMMIT = 0½�¶¯͡½»£¬²¢ȒԃCOMMITº̒OLLBACK4͡½»»򼘹�ŊÎ񡡍 ɧ¹�Ҫautocommit±£³ִ򿫗´̬£¬¿ʒՔٓTART TRANSACTIONԫCOMMIT»񑎌LBACK֮¼丢װţµŊÎ񡤏Áеŀ�¾}¸�񡣵ے»¸�»͡½»µģ¬µڶ�» »ع�º

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>

՚`̆PHP, Perl DBI/DBD, JDBC, ODBC, »󖞍ySQLµı뗼Cµ�ӿە㒹µÁPIʏ£¬ţŜ¹»Ӕؖ·�ʽ·¢̍˂ϱ¿ٖǓ￤£¬ɧCOMMIT£¬µ½MySQL·�¬¾Ώ򇥋�ÓQLԯ¾⋊Ňҹ£¬׮ɧSELECT»񈍓ERT¡£һЩAPIҲ͡¹©µ¥¶5ŗ¨ąµŊÎ򍡽»ºͻع��½·¨¡£

Ҫµắspan>£ºţ²»Ӧ¸Ĕ٭ysql˽¾ݿ⣨±Ɉ磬user»󖞨ost£©o°ЍySQLϵͳ±»»ΪInnoDB`э¡£ϵͳ±ˇMyISAMэ¡£

ɧ¹�Ҫ̹Ԑ£¨·ȏµͳ£©±���±»´´½¨³ȉnnoDB±���ţ¿ʒԼ򵥵ذФefault-table-type=innodbѐͭ¼ӵ½my.cnf»񬷮iniτ¼�sqld]½ۀ

InnoDB¶ӍyISAM´洢ӽȦ²ʓõĵ¥¶K�´½¨·½·¨ûԐضרąµœŻ¯¡£Ӳ´ˣ¬̼²»ֵµõ¼³�¼ɫ±¼°̦º󴴽¨̷ӽ¡£¸ı咻¸�ªInnoDBэخ¿춄°취¾ΊȖ±½Ӳ扫½�InnoDB±���¼´£¬ʹԃALTER TABLE ... ENGINE=INNODB£¬»󔄏፬µĶ¨ӥ´´½¨һ¸�nnoDB±���²¢ȒԃINSERT INTO ... SELECT * FROM ...²扫ѐ¡£

ɧ¹�µڶ�ϕNIQUEԼ˸£¬ţ¿ʒՔڵ¼ɫ½׶ω闃£ºSET UNIQUE_CHECKS=0£¬Ӕ™ʱ¹ص�»є¼쳩ºüԋٱ���µ¼ɫ¡£¶Փڴ󲭣¬֢½ۊ¡‹´􁿵Ĵƅˉ/O£¬ӲΪInnoDB̦º􀊒Պ¹ԃ̼µIJ扫»º³戸4µڶ���»ƺ4дɫ¡£

Ϊ»񶃶Բ扫½�¸�ƣ¬·ֶβ扫´󲭿ʄܱȽϺãº

INSERT INTO newtable SELECT * FROM oldtable

   WHERE yourkey > something AND yourkey <= somethingelse;

̹Ԑ¼Ȃ¼ӑ¾­±¾²扫֮º󣭄䀉ӔטļĻ±���

՚´󲭵ŗª»»א£¬ţӦ¸Ĕ�nnoDB»º³崘µĴ�´¼�ƅˉ/O¡£¾¡¹݈絋£¬²»Ҫʹԃ³¬¹�Ś²¿Ś´档ţҲ¿ʒՔ�nnoDBɕ־τ¼�¾τ¼�¡¡£

ȷхţûԐͮú±¼䣺InnoDB±���MyISAM±Ҫ´󶃶඄´ƅ̿ռ䡣ɧ¹�ALTER TABLEºľ¡‹¿ռ䣬̼¾Ϳªʼһ¸􋊻ع�¢Ȓɧ¹�´ƅ̰󶨵ģ¬»ع�ݒª¼¸¸�±¡£¶Փڲ扫£¬InnoDBʹԃ²扫»º³戸4Ӕ³ʅ�²¢µڶ��½̷ӽא¡£Ňҹ½ۊ¡‹´􁿴ƅˉ/O¡£՚»ع�¬ûԐʹԃ֢ҹµĻ�¶�Ҫ»¨±Ȳ扫³¤30±¶µŊ±¼倴Ϊ³ɡ£

՚ʧ¿صĻع��¬ɧ¹�˽¾ݿ㗐ûԐԐ¼ܖµµŊ�ȽЃ�Ŋȉ±µ�ݿ⾸³̶�µȼ¸°ڍ򹶴ƅˉ/O±»Ϊ³ɡ£ ΪֻµĹ�볎Մ15.2.8.1½ڣ¬¡°ǿ׆»ָ´”¡£

ɧ¹�һ¸�¸¶¨AUTO_INCREMENT£¬՚˽¾ݴʵ偯µÉnnoDB±���±�һ¸�ªؔ¶¯ն³¤¼NJ�NJ��ێª¸M ¸³тֵ¡£ؔ¶¯ն³¤¼NJ�»´洢՚׷Ś´痐£¬¶�´畚´ƅ͉ϡ£

InnoDBʹԃЂ̣·¨4Ϊ°��ªai_colµÁUTO_INCREMENTµı씳�¯ؔ¶¯ն³¤¼NJ���®º󣬵±һ¸�§¶Ա앗�얮ʱ£¬InnoDBִѐµȼ܈萂ԯ¾䶄¶¯ط£º

SELECT MAX(ai_col) FROM T FOR UPDATE;

ԯ¾刡»صŖµװ´μԒ»£¬²¢±»¸³¸�ؔ¶¯ն³¤¼NJ�纻±¿յģ¬ֵ1±»¸³Ԩ¸āС£ɧ¹�ն³¤¼NJ�б»³�¯£¬¶�»§µ�ª±앏Պ¾ˤ³�HOW TABLE STATUSԯ¾䣬ղ¼NJ��¯£¨µ«²»ˇն¼ӼNJ�»´洢Ӕ¹©̦º󶄲扫ʹԃ¡£עӢ£¬՚֢¸�¼»¯א£¬ϒć¶Աһ¸�£µĶU¼¶�/span≯¶¨£¬֢¸�א�ϱµĽ⋸¡£

InnoDB¶Վªт´´½¨±���³�¯ؔ¶¯ն³¤¼NJ�ҹµĹ�

עӢ£¬ɧ¹�՚INSERTאΪAUTO_INCREMENTָ¶¨NULL»󖝰£¬InnoDB´¦mѐ£¬¾ͷ·񖵻¹ûԐ±»ָ¶¨£¬ȒΪ̼ʺ³ʒ»¸�µ¡£

ؔ¶¯ն³¤¼NJ��¯֮º󣭈纻ԃ»§²扫һ¸�·ָ¶¨¸āіµµŐУ¬¶�ֵ´�±ǰ¼NJ�¬ղ¼NJ�闃Ϊָ¶¨ ֵ¡£ɧ¹�ķȷָ¶¨һ¸�¬InnoDB¸�Ƿն¼Ԓ»£¬²¢Ȓ¸³тֵ¸�¡£

µ±·Ď˗Զ¯ն³¤¼NJ�±£¬InnoDBʹԃרԃµı���µÁUTO-INC̸¶¨£¬¸ċ�µ½µ±ǰSQLԯ¾䶄½⋸¶�µ½ҵϱµĽ⋸¡£ ӽɫ‹רԃ̸ˍ·Ųࠂԣ¬4Ϊ¶Ւ»¸�UTO_INCREMENTµı���²扫¸ʼnƲ¿˰¡£}¸�񲼄ݍ¬ʱ¶Ս¬һ±AUTO-INC̸¶¨¡£

עӢ£¬ɧ¹�¹�NJ�Ċ�񣭄䀉Ŝ»╚¸³¸�_INCREMENTµŖµµŐ󂑖з¢Ж¼叶¡£

ɧ¹�¸�һ¸�µ£¬»󖟣¬ɧ¹�¹�Ֆ¸¶¨ֻ˽¸򊽴洢µŗֻ˽£¬ؔ¶¯ն³¤»�ѐΪ²»±»¶¨ӥ¡£

՚CREATE TABLEº́LTER TABLEԯ¾嗐£¬InnoDB֧³ՁUTO_INCREMENT = n ±Ю4ʨ׃¼NJ�¼ֵ»򲤸�NJ�£Ӳ՚±¾½۔萈͖ÛµŔ­Ӳ£¬֢¸�ӰЬ՚·�ن�Ύߐ§‹¡£

InnoDBҲ֧³׍⽼Լ˸¡£InnoDBא¶Ս⽼Լ˸¶¨ӥµœ﷨¿´ǰ4ɧЂ£º

[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¾ܾ�˔؅՚ؓ±���½¨һ¸��»ƥƤ՚¸¸±µĺ󑡼�NSERT»񔏄ATE²ڗ�»¸�һЩƥƤµŐ΍ µŗӱ���InnoDB¶ՈκϊՍ¼¸�¾³�º󑡼�PDATE»񃄌ETE²ڗ��£¬֢¸���†OREIGN KEYؓ¾䶄ON UPDATEº̏N DETETEؓ¾喸¶¨µ¼em>referential action¡£µ±ԃ»§˔ͼ´Ԓ»¸�³�Ò»ѐ֮ʱ£¬Ȓ՚ؓ±Ԑһ¸�๶ƥƤµŐУ¬InnoDB¸�²ʈ¡µĶ¯طԐϥ¸�񣸼/span>

µ±¸¸±µĺ󑡼�µŊ±º򣫉nnoDB֧³׍¬ҹѡձ¡£ѡձCASCADE£¬՚ؓ±µō⽼ ±»ʨ׃Ϊ¸¸±º󑡼�µ¡£Ӕͬҹµķ½ʽ£¬ɧ¹�±���тµŁвο¼՚­һ¸�еō⽼£¬¸�ª¡£

עӢ£¬InnoDB֧³׍⽼՚һ¸�ے�ە㐩ȩ¿�¬ؓ±¼ˉВ㎶֢՚±¸½˴µļȂ¼¡£

InnoDBѨҪ¶Ս⽼ºͱ»ӽԃ¼��⽼¼쳩¿ʒԿ팙½�²»ѨҪһ¸�¨Ĩ¡£¶Ս⽼µŋ�»ؔ¶¯´´½¨¡£֢ˇР¶Փے»ЩO°汾£¬՚O°汾א̷ӽ±ِ섷ȷ´´½¨£¬·򕳍⽼Լ˸µĴ´½¨»⊧°ܡ£

՚InnoDBŚ£¬΢¼�»ӽԃoРӦµŁбِ씐`̆µńڲ¿˽¾ހᑍ£¬Ӕ±䌼ć²»Ѩ`эת»»¾Ϳɱ»±Ƚϡ£ֻ˽`эµĴ�ͷ�ѫРͬ¡£ؖ·�эµij¤¶Ȳ»ѨҪРͬ¡£ɧ¹�¶¨һ¸� NULL¶¯ط£¬ȫȷɏţûԐ՚ؓ±ѻ¸湃 ΪΪNOT NULL¡£

ɧ¹�L´҃REATE TABLEԯ¾䱨¸璻¸�󻃱005£¬²¢Ȓ´хϢؖ·�вerrno 150£¬֢Ӣ˼ˇӲΪһ¸��»²»ֽȷю³ɣ¬±���½¨ʧ°ܡ£`̆µأ¬ɧ¹�R TABLEʧ°ܣ¬Ȓָ̼вerrno 150£¬ ŇӢζ؅¶Ւѱ乼µı���΢¼�ᱻ²»ֽȷµŐγɡ£ţ¿ʒՊ¹ԃSHOW INNODB STATUS4Дʾһ¸��ЗµÉnnoDB΢¼�ŏ돸½㋍¡£

עˍ£ºInnoDB²»¶ՄȐ© ΢¼�¬NULLµı»ӽԃ¼�ꎢ¼�£

¶ӓQL±뗼µı³k£ºɧ¹�±Ԑ˽¸�¬ǤאԐРͬµ ±»ӽԃ¼�»º򈭮oDB՚΢¼�вʈ¡¶¯ط£¬¾ͷ·񇥋�¬¼�¸ѐ²»´畚һҹ¡£}ɧ£¬ɧ¹�¶¨ӥһ¸�TRICT`эµŔ¼˸£¬²¢ȒԐһ¸��еŗԐУ¬InnoDB²»ՊѭɎºζՕ㐩¸¸ѐµʼn¾³�:p>

¾ԓڶՓ¦΢¼�ŋ�ڵļȂ¼£¬InnoDBͨ¹�Əɑ¡·¨ʩѐ¼¶j²ڗ�/span>

¶ӓQL±뗼µı³k£º ɧ¹�PDATE CASCADE»񎌍 UPDATE SET NULLµݹ鹼тРͬµı���֮ǰ՚¼¶j¹�ñ±»¸�¬̼¾Ώ򑄓TRICTһҹ¶¯ط¡£֢Ӣζ؅ţ²»ŜʹԃؔӽԃON UPDATE CASCADE»󖞏N UPDATE SET NULL²ڗ�⽫بֹ¼¶j¸�µŎߏߑ­»·¡£­һ·½Ħ£¬һ¸��N DELETE SET NULLˇԐ¿ʄܵģ¬¾Ώ򒻸��DELETE CASCADEһҹ¡£ ¼¶j²ڗ�ʒԱ»Ƕ͗³¬¹�

¶ӓQL±뗼µı³k£º `̆һ°㶄MySQL£¬՚һ¸�룬ɾ³�Ð���ѐµÓQLԯ¾党£¬InnoDBװѐ¼쳩UNIQUEº̆OREIGN KEYԼ˸¡£°´֕SQLµı뗼£¬ ĬɏµŐюªӦ±»ғ³ټ쳩£¬¼´Լ˸½��Lԯ¾䱻´¦m֮º󳅱»¼쳩¡£ֱµ½InnoDBʵЖғ³ٵŔ¼˸¼쳩֮ǰ£¬һЩ˂ȩˇ²»¿ʄܵģ¬±Ɉ艾³�¨¹�ο¼µ½ؔʭµļȂ¼¡£

עˍ£ºµ±ǰ£¬´¥·¢Ƿ²»±»¼¶j΢¼��

һ¸��⽼jϵǰ¸¸±���ؓ±���¼򵦀�£º

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;

ɧЂˇһ¸�´Փµŀ�嗐һ¸�duct_order±���Ǥ̼}¸�э⽼¡£һ¸��»¸�duct±µŋ«̷ӽ¡£­һ¸􋋒�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ՊѭţԃALTER 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}]

¼ȗ¡Ј´´½¨ѨҪµŋ�/span>¡£ţҲ¿ʒՓLTER TABLEιһ¸����һ¸���£

InnoDBҲ֧³׊¹ԃALTER TABLE4ӆ³��:p>

ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

µ±Ū´´½¨һ¸��¬ɧ¹�IGN KEYؓ¾䱼(һ¸�STRAINTĻؖ£¬ţ¿ʒՒ��׀´ӆ³��⣬µ±΢¼�¨֮ʱ£¬fk_symbolֵ±»InnoDBŚ²¿±£֤¡£µ±ţЫҪӆ³�⽼֮ʱ£¬Ҫ֒³�ǣ¬ȫʹԃSHOW 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½㏶³͐𺮳pan>Պѭţ՚FOREIGN KEY ... REFERENCES ...ؓ¾嗐ԃ`£¨backticks)°ѱ���ĻĻؖΧǰ4¡£InnoDB½㏶³͐󒲿¼Çµ½lower_case_table_namesϵͳ±偿µʼn闃¡£

InnoDB·µ»ْ»¸�ō⽼¶¨ӥطΪSHOW CREATE TABLEԯ¾勤³�»²¿·֣º

SHOW CREATE TABLE tbl_name;

´ԕ⹶°汾ǰ£¬mysqldumpҲ½«±���ֽȷ¶¨ӥʺ³ɵ½ת´¢τ¼�Ҳ¢²»μ¼ō ΢¼�:p>

ţ¿ʒՈ萂¶Ւ»¸�Պ¾΢¼�º

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

΢¼�»՚ˤ³�omment¡£

µ±ִѐ΢¼�®ʱ£¬InnoDB¶Ջ�ŵŗӻ򸸼Ȃ¼ʨ׃¹²ЭµŐм¶̸¡£InnoDBb¼´¼쳩΢¼�¬¼쳩²»¶ՊÎ򍡽»ғ³١£

ҪʹµöՓэ⽼¹ُµµıт՘ɫת´¢τ¼��¬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²ڗ��⽼О׆Ҳˇ·dz£Ԑԃµġ£

InnoDB²»Պѭţɾ³�»FOREIGN KEY±˸ ӽԃµı���³��“ET FOREIGN_KEY_CHECKS=0¡£µ±ţӆ³����ʱº򣭔ۋ�¨ԯ¾偯¶¨ӥµŔ¼˸Ҳ±»ӆ³�:p>

ɧ¹�т´´½¨һ¸�Ƴ�¬̼±ِ씐һ¸�ԓے²ӽԃ̼µō⽼Լ˸µĶ¨ӥ¡£̼±ِ씐ֽȷµŁу�э£¬²¢Ȓɧǰ̹˶£¬̼±ِ뷔±» ӽԃµļ���²»±»úأ£¬MySQL·µ»شºñ005 ²¢՚´хϢؖ·�ָвerrno 150¡£

MySQL¸´׆¾Ώ񷓍yISAM±ҹ£¬Ҳ¶ӉnnoDB±طԃ¡£Ӕijז·½ʽʹԃ¸´׆Ҳˇ¿ʄܵģ¬՚֢ז·½ʽא´ӷ�ϱ���`э²»ͬԚ׷·�͍ ԭʼ±���`э¡£}ɧ£¬ţ¿ʒԸ´׆ў¸ĵ½׷·�В»¸�oDB±���µ½´ӷ�В»¸�SAM±¡£

ҪΪһ¸��¨bһ¸��¬ţ²»µò»¸´׆InnoDB±¼仍ɕ־τ¼�°InnoDB±���.frmτ¼�Ӓƶ¯¸´¼��£ ¹ٓۆ凡µ±²½רȫ²ϔ¼a href="http://book.chinaz.com/mysql5/storage-engines.html#moving" title="15.2.9. Moving an InnoDB Database to Another Machine">15.2.9½ڣ¬¡°ӆ¶¯InnoDB˽¾ݿ⵽­һ̨»�dquo;¡£

ɧ¹�Ӕ¹ر֖�򇷻󖠒»¸�ڵĴӷ�£ţ¿ʒԲʈ¡InnoDB±¼仍ɕ־τ¼�¸·ݣ¬²¢ԃ̼4½¨bһ¸��£Ҫ²»¹رֈκη�´½¨bһ¸�Ĵӷ�¬ţҲ¿ʒՊ¹ԃ·ȃ⸑£¨ʌҵµģ©InnoDBɈ±¸·ݹ¤¾ݼ/a>¡£

ţ¿ʒԴ洢ÿ¸�oDB±���̼µŋ�ۋ�Ŏļ�¬֢¸��ǎª“¶ಭ¿ռ⥲dquo; £¬ӲΪʵ¼ˉЃ¿¸�ы�ı¼䡣

¶ՄȐ©Ы°Ҍض¨±���ӆµ½·׀쏯m´ƅ̵œû§£¬»󖠄Ȑ©ϣλ¿팙»ָ´µ¥¸�ı¸·ݶ�´򷐆唠InnoDB±���ʹԃµœû§£¬ʹԃ¶ಭ¿ռ伡ˇԐӦµġ£

ţ¿ʒՍ�nfµÛmysqld]½ی���ЂĦѐ4Պѭ¶ಭ¿ռ䣺

[mysqld]

innodb_file_per_table

טǴ·�®º󣫉nnoDB´洢ÿ¸�´½¨µı���±���̹˴ԚµŊ�¿¼Ђ̼ؔ¼ºµŎļ�l_name.ibdo¡£֢`̆ԚMyISAM´洢ӽȦ̹ضµģ¬µ«MyISAM °ѱ���³ʊ��l_name.MYDº΋�ļ�l_name.MYI¡£¶ՓىnnoDB£¬˽¾ݺ΋�һǰ´浽.ibdτ¼�tbl_name.frmτ¼�H»±»´´½¨¡£

ɧ¹�my.cnfτ¼�nnodb_file_per_tableѐ£¬²¢טǴ·�¬InnoDB՚¹²Эµı¼叄¼�δ´½¨±���

innodb_file_per_tableֻӰЬ±���´´½¨¡£ɧ¹�֢¸�¶¯·�¬т±���ԃ.ibdτ¼�¨£¬µ«ˇţɔ¾ʄܷĎ˔ڹ²Э±¼偯µı���ɧ¹�µ��т±¹²Э±¼党´´½¨£¬µ«ţɔ¾ɿʒԷĎˈκϓöಭ¿ռ䴴½¨µı���

InnoDB؜ˇѨҪ¹²Э±뀕¼䡣.ibdτ¼�oDB²»أӔȥՋѐ£¬¹²Э±¼䱼º¬ˬϤµébdataτ¼�oDB°҄ڲ¿˽¾ݴʵ仍δطɕ־·Ɣە⹶τ¼�

עˍ£ºţ²»Ŝ б¶ӍyISAMһҹ£¬՚˽¾ބ¿¼֮¼匦Ӣµْƶ¯.ibdτ¼�Ȓ󎪱���ӥ±»´畚InnoDB¹²Э±¼党£¬¶�noDB±ِ뱣³׊Î脺Έ֖¾˳ѲºŵŒ»ׂє¡£

՚һ¸�¨µÍySQL°²װo£¬ţ¿ʒՓ’ENAME TABLEԯ¾䱑.ibdτ¼�ªµı���һ¸�ݿ㓆µ½­һ¸�ݿ⣺

RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;

ɧ¹�.ibdτ¼��»µı¸·ݣ¬ţ¿ʒ԰´ɧЂ²ڗ�»ǰԴµĵط½»ָ´̼µ½MySQL°²װא£º

2.      ALTER TABLE tbl_name DISCARD TABLESPACE;

5.      ALTER TABLE tbl_name IMPORT TABLESPACE;

՚ʏЂτא£¬һ¸�dτ¼�ı¸·ޒ㎪£º

ţ¿ʒՓďÁз½·¨ʺ³ʒ»¸�dτ¼�»±¸·ݣº

ʺ³ʒ»¸�dτ¼�»¸´׆µŁ¸�¨ˇʹԃʌҵµ¼strong>InnoDBɈ±¸·ݹ¤¾ݼ/span>£º

1.    ʹԃmysqldumpת´¢̹ԐInnoDB±���

2.    ֹͣ·�£

3.    ɾ³�Ѵ畚µı¼叄¼�:p>

4.    Ƥ׃т±¼䡣

5.    טǴ·�£

6.    µ¼ɫת´¢τ¼�:p>

15.2.8.1. ǿ׆»ָ´

15.2.8.2. ¼쳩µắspan>

ɧ¹�¿㒳±»dž»µ£¬ţ¿ʄݏ쒪ԃSELECT INTO OUTFILE´ӴԊ�ª´¢ţµı���ͨ³£Ӕ֢ז·½·¨»򈡵Ĵ󷡊�ȍ껃µġ£¼´ʹ֢ҹ£¬̰»µ¿ʄܵ¼ׂSELECT * FROM tbl_name»󖞉nnoDBº􌨲ڗ�£»򷐑ԣ¬»󖠉�¹µ‰nnoDBǰ¹�´±@£¡£ ¾¡¹݈絋£¬ţ¿ʒՓċ�ʼnnnoDB´洢ӽȦǴ¶¯ͬʱبֹº􌨲ڗ�У¬Ӕ±䅣Ŝת´¢ţµı���}ɧ£ºţ¿ʒՔۖن�򇸖®ǰ£¬՚ѡЮτ¼�sqld]½ی���ɧЂµŐУº

[mysqld]

innodb_force_recovery = 4

innodb_force_recovery±»Պѭµķȁ䖵ɧЂ¡£һ¸�󶅊�¬̹Ԑ¸�ֵŔ¤·4슩¡£ɧ¹�¹»ԃһ¸��ѡЮֵ4ת´¢ţµı���Ňôţˇ±Ƚϰ²ȫµģ¬ֻԐһЩ՚̰»µµĵ¥¶R³ĦʏµŊ�ªʧ¡£һ¸�µŖµ¸�¬ӲΪ˽¾ݿ㒳±»´՚һ¸�ɵŗ´̬£¬֢¸�¬·´¹�Ւ�˷ºΆ匼˽¾ݿ⾡¹¹µĸ�µ¡£

˽¾ݿⲻŜ­΢µش�ЩѡЮא±»Պѭµő¡Ю4ʹԃ¡£طΪһ¸�«´슩£¬µ±innodb_force_recovery±»ʨ׃ Ϊ´�µŖµʱ£¬InnoDBبֹԃ»§ִѐINSERT, UPDATE»񃄌ETE²ڗ�o:p>

¼´ʹǿ׆»ָ´±»ʹԃ£¬ţҲ¿ʒӄROP»񂑅ATE±���ɧ¹�µR»¸�¨µı՚µ¼ׂ»ع�££¬ţ¿ʒՒƳ�䒲¿ʒՓĕ⹶4ֹͣԉʧ°ܵĴ�¼ɫ»󊧰ܵÁLTER TABLEµ¼ׂµŊ§¿֍ »ع�䀉Ӕɱµ򻲴rong>mysqld½�Ȼº􊩖©nnodb_force_recoveryΪ3£¬ʹµĊ�» ¹ӆ𷸲»ѨҪ»ع�»º􊢆�ʧ¿ػع����

InnoDBʵЖһז±»ɏʶΪ“ģº�o;¼꺯span>²鶣ʨ׃µļ쳩µ㼺׆¡£InnoDBӔСƺ´ӻº³崘 ˢтӑў¸ĵŊ�³¡£û±ْªӔµ¥¸�ϋ¢т»º³崘£¬µ¥ƺ´ϋ¢тʵ¼ʲڗ�ʄܻ╚¼쳩µ䊨׃½�ֹͣԃ»§SQLԯ¾䶄´¦m¡£

՚±@£»ָ´א£¬InnoDB֒Ѱ±»д½�µļ쳩µ㲪ǩ¡£̼֪µK�¸ñ뇩֮ǰ¶Պ�Ő޸ı»³ˏהۊ�Ĵƅ͓³бא¡£Ȼº򈭮oDB´Ӽ쳩µ䎹ǰɨĨɕ־τ¼��¦ԃӑдɫɕ־µŐ޸ġ£

InnoDBӔѭ»··½ʽдɕ־τ¼�ъ¹µûº³崘oµŊ�³ԫ´ƅ͉ϵœ³б²»ͬµŒҌύў¸ıِ봶Ж՚ɕ־τ¼�¬Ӕ±¸βһInnoDBѨҪضһ¸�´¡£֢Ӣζ؅£¬µ±InnoDB¿ªʼטтʹԃһ¸�¾τ¼�钪ȷɏ՚´ƅ͉ϵŊ�³ӳб°�´½�DB׼±¸טтʹԃµň֖¾τ¼�޸ġ£»»¾仰 ˵£¬InnoDB±ِ봴½¨һ¸�鶣£¬֢¾­³£ʦ¼°ӑў¸ ˽¾ݿ㒳µ½´ƅ̵ŋ¢т¡£

ǰĦµŐ񋶽㋍‹Ϊʲôʹţµň֖¾τ¼�󼢔ۉ闃¼쳩µ䗐½۔¼´ƅˉ/O¡£ʨ׃ɕ־τ¼��ͻº³崘һҹ´󼳕ࠉ�Ȼº³崘´􍨳£ˇԐӢӥµġ£´􉖖¾τ¼�䋇±@£»ָ´Ҫ»¨¸�±¼䣬ӲΪԐ¸�쉕־µŐƏ¢ҪӦԃµ½˽¾ݿ㊏¡£

15.2.10.1. InnoDB̸¶¨ģʽ

15.2.10.2. InnoDBº˼/span>AUTOCOMMIT

15.2.10.3. InnoDBº˼/span>TRANSACTION ISOLATION LEVEL

15.2.10.4. ³א�¶¨¶�/a>

15.2.10.5. ̸¶¨¶ELECT ... FOR UPDATEº̓ELECT ... LOCK IN SHARE MODE

15.2.10.6. Next-Key̸¶¨£º±݃⸋ӄ̹˼µŎˌຯa>

15.2.10.7. ³א�ºϔؼ/a>InnoDBאطԃµŀ�>

15.2.10.8. ՚InnoDBאԃ²»ͬµÓQLԯ¾动¶¨̸

15.2.10.9. MySQLʲôʱº󍡽»»򼘹��񣽼/a>

15.2.10.10. ̸̀¼쳢£¦»ع�>

15.2.10.11. ɧºϓ¦¶ՋK�

InnoDBʵЖ±뗼ѐ¼¶̸¶¨£¬՚֢oԐ}ז`эµŋ�ocks:

ɧ¹�A ՚tuple tʏ³דжU¼̸¶¨£¬4ؔ²»ͬ˂ϱBµĶҼi>tʏɎһ`эµŋ�ȳ²»±»íʏѭ¿ɣ¬ȡ¶�µأ¬˂ϱB ²»µò»µȴ�ˍ·Ɣٴuple tʏµŋ�o:p>

ɧ¹� A ՚tuple tʏ³דђ»¸����̸(S)£¬Ňô

²»½�ˣ¬InnoDB֧³Լem>¶ཤ¸�茸¶¨£¬̼Պѭ¼Ȃ¼̸ºͶՕ�µŋ�¡£Ҫʹµöཤ¸�缶±𶅋�¼ʻ¯£¬¶`эµŋ�³ǎªintention locks±»ʹԃ¡£՚InnoDBא£¬Ӣͼ̸¶¨ˇ±¶¨¡£ ¶Փے»¸�񣭒㍼̸¶¨֮º􁮏분ˇָķ՚¸ñ¶Ւ»¸�滳ѨҪńһ`эµŋ�¹²Э»¹ˇ¶U¼£©¡£Ԑ}זӢͼ̸±»ԃ՚InnoDBא£¨¼ډ鋂ϱT ՚±뻩>RאҪȳһ¸�¸³�ᑍµŋ�

Ӣͼ̸ЭөɧЂ£º

֢Щ½ẻ¿ʒԷ½±㶘ԃһ¸�ᑍ¼牝¾ٕ􀵗ܽ᣺

ɧ¹�̸¶¨ԫЖ՚̸¶¨¼牝µĻ°£¬̼±»˚¸�ίΐ˂ϱ¡£ɧ¹�̸¶¨ԫЖ´猸¶¨³捻£¬̼¾Ͳ»±»˚Ԩһ¸�ъÎ񡤊Î񶈴�½³捻µŏִ猸¶¨±»ˍ·ŵ�纻һ¸�¨ȫȳԫЖ´猸¶¨Р³捻£¬Ȓ²»Ŝ±»˚Ԩ£¬ӲΪ̼¿ʄܻᵼ̸ׂ̀£¬һ¸�󳻉�o:p>

Ӳ´ˣ¬Ӣͼ̸¶¨²»ب°­Ɏºζ«Ϸ£¬³�«±ȳ£¨±Ɉ拏CK TABLES ... WRITE£©¡£IX º˼i>IS̸¶¨µŖ�¿µŊȏՊ¾ijɋ̸ֽ¶¨һѐ£¬»򽬒ª՚±̸¶¨һѐ¡£

Ђµŀ�¾µ±̸¶¨ȫȳ¿ʄܻᵼ̸ׂ֮̀ʱһ¸�󼢈绎·¢ʺ¡£}ؓא°��§¶ʁº̂¡£

˗Ј¿ͻ§¶ʁ´´½¨һ¸�¬һ¸�ı���Ȼº󿫊¼һ¸�񡤔ە⹶˂ϱŚ£¬Aͨ¹�ʽѡձѐ»񶃶Րе¼i>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 ̸¶¨¡£ӲΪ֢¸�¨²»¼牝¿ͻ§¶ʁ³דе¼i>S̸¶¨£¬̹ӔX ̸¶¨²»±» Պѭ£¬̹Ӕȫȳ½�ѐ¼°¿ͻ§¶̗赲µŋ�ȳ¶ԁС£

خº󣬿ͻ§¶ʁҲ˔ͼ´ӱɾ³�º

mysql> DELETE FROM t WHERE i = 1;

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

try restarting transaction

ӲΪ¿ͻ§¶ʁѨҪһ¸�X ̸¶¨4ɾ³�¬̹Ӕ՚֢o·¢ʺ̸̀¡£¾¡¹݈絋£¬̸¶¨ȫȳ²»±»Պѭ£¬ӲΪ¿ͻ§¶ʂӑ¾­Ԑһ¸�i>X̸¶¨µŇ숳²¢Ȓֽ̼µȴ�ʁˍ·üi>S̸¶¨¡£ӲΪ¿ͻ§¶ʂ֮ǰ¶Ҽi>X ̸¶¨µŇ숳£¬±»¿ͻ§¶ʁ³דе¼i>S̸¶¨Ҳ²»Ŝʽ¼¶µ½X̸¶¨¡£Ӳ´ˣ¬InnoDB¶Կͻ§¶ʁ²�¸�󣬲¢Ȓˍ·Ƌ�¨¡£՚Ňһµ䊏£¬¿ͻ§¶ʂµŋ�ȳ¿ʒԱ»ѭ¿ɣ¬²¢Ȓ¿ͻ§¶ʂ´ӱɾ³�

՚InnoDBא£¬̹Ԑԃ»§ѐΪ¶¼՚˂ϱŚ·¢ʺ¡£ɧ¹�͡½»ģʽ±»Պѭ£¬ÿ¸�ԯ¾啚̼ؔ¼ºʏю³ʒ»¸�5ŊÎ񡢍ySQL؜ˇ´�ѭؔ¶¯͡½»4¿ªʼһ¸�¬½ӡ£

ɧ¹�͡½»ģʽ±»ԃSET AUTOCOMMIT = 0¹رգ¬Ňôϒć¿ʒՈЎªһ¸�§؜ˇԐһ¸�񵲿ª؅¡£һ¸� COMMIT»񑎌LBACKԯ¾価˸µ±ǰ˂ϱ²¢Ȓһ¸�Î񿫊¼¡£}¸�䶼ˍ·Ƌ�µ±ǰ˂ϱא±»ʨ׃µÉnnoDB̸¶¨¡£һ¸�MITԯ¾哢ζ؅՚µ±ǰ˂ϱאضµĸı䱻ʺ³ʎªԀ¾õģ¬²¢Ȓ±䴉Ǥ̼ԃ»§¿ɼ�һ¸�LBACKԯ¾䣬՚­һ·½Ħ£¬³·к̹Ԑµ±ǰ˂ϱضµŐ޸ġ£

ɧ¹�Ԑ±»ՊѭµŗԶ¯͡½»£¬ͨ¹�·µÓTART TRANSACTION»񁄇INԯ¾倴¿ªʼһ¸�񣬲¢ԃCOMMIT»󖞒OLLBACKԯ¾倴½⋸̼£¬֢ҹԃ»§ɔ¾ɿʒՖ´ѐһ¸�ٓ￤˂ϱ¡£

°´֕SQL:1992 ˂ϱ¸�¶±𣫉nnoDBĬɏˇ¿ʖظ´¶uģ¨REPEATABLE READ£©¡£MySQL/InnoDB ͡¹©SQL±뗼̹Ĩ˶µŋ�¸�񹵀뼶±𡤄䀉Ӕ՚ļ®ѐԃ--transaction-isolationѡЮ£¬»󕛑¡Юτ¼�ª̹Ԑl½ԉ闃 Ĭɏ¸�¶±𡤀�䀉Ӕ՚my.infτ¼�sqld]½ۀ𐁠̆ɧЂʨ׃¸đ¡Ю£ºglobally

[mysqld]

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

                         | REPEATABLE-READ | SERIALIZABLE}

ԃ»§¿ʒՓ“ET TRANSACTIONԯ¾乄±䵥¸�°»󖠋�½�µĸ�¶±𡤋�¨ɧЂ£º

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL

                       {READ UNCOMMITTED | READ COMMITTED

                        | REPEATABLE READ | SERIALIZABLE}

עӢ£¬¶ҭ-transaction-isolationѡЮµļ¶±񄼖ѓс¬ؖ·�՚¶ӓET TRANSACTIONԯ¾䶄¼¶±񄼖у»Ԑ¡£

ĬɏµŐюªˇΪЂһ¸�´¿ªʼ£©˂ϱʨ׃¸�¶±𡤈纻ţʹԃGLOBAL¹ؼ�￤՚ȫ¾ֶԴԄǵ㿪ʼ´´½¨µŋ�l½ӣ¨³�畚µŁ¬½ӣ©ʨ׃ Ĭɏ˂ϱ¼¶±𡤄䑨ҪSUPERȫИ4ض֢¸�¹ԃSESSION ¹ؼ�ª½«4՚µ±ǰl½ԉЖ´ѐµŊÎ򊩖Ą¬ɏ˂ϱ¼¶±�£

Ɏºοͻ§¶˶¼Ŝؔԉ¸ı伡»°¸�¶±𣩉�ۊÎ񶅖м䣩£¬»󖠎ªЂһ¸�򊩖ø�¶±𡡠

ţ¿ʒՓďÁѓ￤²ꑯȫ¾ֺͻỰ˂ϱ¸�¶±𣸠

SELECT @@global.tx_isolation;

SELECT @@tx_isolation;

՚ѐ¼¶̸¶¨א£¬InnoDBʹԃnext-key̸¶¨¡£֢Ӣζ؅³��¬InnoDBҲ¿ʒ԰ҋ�Ȃ¼ǰµļ叶̸¶¨µ½Ǥ̼ԃ»§̹ضµĽ�ċ�Ȃ¼֮ǰµĿ鳥ɫʏ¡£һ¸�t-key̸¶¨ָвһ¸�¨סһ¸��΋�ļ叶µŋ�һ¸�¶̸¶¨ָ½�¡һЩ̷ӽ¼Ȃ¼֮ǰµļ叶µŋ�

InnoDBאÿ¸�뼶±𶅏돸Ĩ˶ɧЂ£º

³א�ζ؅InnoDBʹԃ̼µĶ౦±¾»¯4¸�²ꑯչʾij¸�䶣´¦˽¾ݿⶄ¿햕¡£²ꑯ¿´µ½՚Ҹ�䶣֮ǰ±»͡½»µńȐ©ȷȐ˂ϱضµĸ�¢ȒûԐǤº󶅊Î񼳎´͡½»˂ϱضµĸı䡣֢¸�򶅀�¬²ꑯ¿´µ½·¢²¼¸òꑯµŊÎ񱿉ضµĸı䡣

ɧ¹�ѐ՚ĬɏµÒEPEATABLE READ¸�¶±𣭔󕛍¬һ˂ϱŚµŋ�Ѹ¶vˆ¡ԉ¸ĊÎ򗐵ے»¸��̹ȷbµĿ햕¡£ţ¿ʒՍ¨¹�±ǰ˂ϱ²¢՚·¢²¼т²ꑯµŊÎ򖮺󣭎ªţµIJꑯ»񶄒»¸�ÏʵĿ햕¡£

³א�Ĭɏģʽ£¬՚ǤאInnoDBzai՚READ COMMITTEDº̒EPEATABLE READ¸�¶±𴧀쒅LECTԯ¾䡣³א�՚Ɏºϋ�ıʨ׃̸¶¨£¬Ӳ´ˣ¬Ǥ̼ԃ»§¿ʗՓɵٔڳא�һ¸�Ж´ѐµō¬һʱ¼呞¸ŕ㐩±���

עӢ£¬³א�՚DROP TABLEº́LTER TABLEʏطԃ¡£³א�՚DROP TABLEʏطԃ£¬ӲΪMySQL²»Ŝʹԃӑ¾­±»ӆ³�¬²¢ȒInnoDB dž»µ‹¸ñ���³א�՚ALTER TABLEʏطԃ£¬ӲΪ̼՚ij˂ϱŚִѐ£¬¸ĊÎ񴴽¨һ¸����²¢Ȓ´Ӿɱт±²扫ѐ¡£Ж՚£¬µ±ţטт·¢³��ʱ£¬̼²»Ŝ՚т±¿´¼�ѐ£¬ӲΪ̼ć±»²扫µ½һ¸�א�ȡµĿ햕א²»¿ɼ�ϱ o¡£

՚һЩ»·¾³א£¬һ¸��²»·½±㶄¡£±Ɉ磬ţ¿ʄݏ쒪ι±���ؓ±ͭ¼Ԓ»¸�У¬²¢ȷх¸ėӱ¸¸±Ԑһ¸�£Ђ}ؓДʾɧºϔۄ䓦ԃ³͐򵻂에ʵЖ²ο¼µō떻є¡£

¼ډ酣ʹԃһ¸��¶ˆ¡¸¸±���Ȓ¿´µ½±ؓ±���¸�Ŝ°²ȫµٍ�ͭ¼ԗԐт𣿲»£¬ӲΪ¿ʄݍ¬ʱ·¢ʺһЩǤ̼ԃ»§´Ӹ¸±³�¬¶�ԐעӢµ½̼µŇꀶ¡£

½⿶°취ˇ՚ʹԃLOCK IN SHARE MODEµŋ�ʽִѐSELECT£º

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

՚¹²Эģʽִѐһ¸�㎶؅ϒ涗µĿʓĊ�¢՚ϒć¶uŐщ闃һ¸�¶¨¡£¹²Эģʽ̸·V¹Ǥ̼ɋ¸�¾³�ѶuŐС£ͬʱ£¬ɧ¹�µŊ�ۆ匼¿ͻ§¶̉ю´͡½»µŊÎ񣭎ӃǵɗƖªµDǸ�񱼌ύ¡£ϒć¿´µ½ǰ˶µIJꑯ·µ»ظ¸'Jones'£¬ϒć¿ʒ԰²ȫµ֠ιؓ±¼ԗӼȂ¼²¢͡½»ϒćµŊÎ񡡠

Ƀϒć¿´­΢һ¸�ӣºϒć՚±좨ild_codes אԐһ¸���¬ϒćԃ¸ñ���ÿ¸�ӵ½ؓ±µŗԏƉһ¸�»µŊ¶±𸻡£ДȻ£¬ʹԃ³א�֟¹²Эģʽ¶ˆ¥¶ˆ¡µ±ǰ¼NJ�µ²¢ˇһ¸��¬ ӲΪ˽¾ݿⶄ}¸�§¿ʄܿ´µ½¼NJ�¬һ¸�¬ɧ¹�ԃ»§˔؅ԃͬһʶ±𸼍�ͭ¼ԗԏ¾ͻᷢʺһ¸�´¼�licate-key£©´¡£

՚֢o£¬ɧ¹�ԃ»§ͬʱ¶|NJ�±˔ͼ¸��±£¬ׁʙ̼ćאԐһ¸�¢ʺ̸̀´²¢וֹ£¬Ӳ´ʌOCK IN SHARE MODE²¢²»ˇһ¸�Ľ⿶·½·¨¡£

՚֢זȩ¿�¬Ԑ}¸�½·¨ȥʵЖ¶|NJ��NJ�º(1) Ј¸��üNJ��֮º󷁼NJ�󖝨2)ԃ̸¶¨ģʽFOR UPDATEЈ¶|NJ�®º󽇊��£º��¶¿ɱ»ɧЂʵЖ£º

SELECT counter_field FROM child_codes FOR UPDATE;

UPDATE child_codes SET counter_field = counter_field + 1;

SELECT ... FOR UPDATE¶—µĿɼ�£¬՚ÿ¸�ˆ¡µŐщ闃¶U¼̸¶¨¡£Ӳ´ˣ¬̼ʨ׃ԫ̷̑µÓQL UPDATE¿ʄܻ╚ѐʏʨ׃µŋ�ҹµŋ�

ȫעӢ£¬Ӕʏ½�»¸�ECT ... FOR UPDATEɧºφ񘸓õŀ�ٍySQLא£¬˂ʵʏʺ³ʒ»¸�»ʶ±𸻵Ōي㉎ϱ¿ɱ»ԃ¶Ըñ���µ¥¶7Ďˀ´Ϊ³ɣº

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

SELECT LAST_INSERT_ID();

SELECTԯ¾侶½�ي¶±𸼐Ə¢£¨רą¶Ե±ǰl½ӣ©¡£̼²»·Ďˈκα���

՚ѐ¼¶̸¶¨א£¬InnoDB ʹԃһ¸�ªnext-key lockingµŋ㷨¡£InnoDBӔ֢ҹһז·½ʽִѐѐ¼¶̸¶¨£ºµ±̷̼̑»󉩃貭µŋ�®ʱ£¬̼¶Փ�ŋ�Ȃ¼ʨ׃¹²Э»򷁕¼̸¶¨¡£Ӳ´ˣ¬ѐ¼¶̸¶¨˂ʵʏˇ̷ӽ¼Ȃ¼̸¶¨¡£

InnoDB¶Ջ�Ȃ¼ʨ׃µŋ�ӳб̷ӽ¼Ȃ¼֮ǰµ¦ldquo;¼叶”¡£ɧ¹�ԃ»§¶Ւ»¸��Ȃ¼RԐ¹²Э»򷁕¼µŋ�­һ¸�§ ²»Ŝ½�ْ֮ǰӔ̷ӽµŋ³Ѳ²扫һ¸��Ȃ¼¡£֢¸�¶µŋ�ִѐ4·V¹̹νµ¦ldquo;Ԅ©ϊ͢”¡£¼ډ酣ЫҪ´ԓђ»¸�¶·�Ԛ100µŗӱ���²¢̸¶¨̹Ԑؓ¼Ȃ¼£¬²¢Ы؅̦º�¡¶¨ѐא¸�© £º

SELECT * FROM child WHERE id > 100 FOR UPDATE;

¼ډ镚idԐһ¸��¯´ҩd´�00µĵے»¸�¼¿ªʼɨĨ¡£ɧ¹�՚̷ӽ¼Ȃ¼ʏµŋ�°Ҕڼ叶ʺ³ɵIJ扫ƅ³�¬һ¸�пʄݓ뵋ͬʱ±»²徸±¡£ɧ¹�ͬһ˂ϱŚִѐͬҹµÓELECT£¬ţ¿ʄܻ╚¸òꑯ·µ»صĽẻ°�½һ¸�С£֢ԫ˂ϱµĸ�­ղˇР·´µģºһ¸�򓦸Ąܹ»Ջѐ£¬Ӕ±䌼ӑ¾­¶uŊ�Î񺽳͖в»¸ı䡣ɧ¹�°Ғ»͗ѐ˓Ϊ˽¾ޏтµ¦ldquo;Ԅ©”ؓ¼Ȃ¼¿ʄܻ⎥·´֢һ¸�­ղ¡£

µ±InnoDBɨĨһ¸��¬̼Ҳ̸¶¨̹Ӕ¼Ȃ¼אخº��¼֮º󶄼叶¡£¸֔ۇ°һ¸�Ԗз¢ʺ£ºInnoDBʨ׃µŋ�ֹɎºβ扫µ½id¿ʄܴ󺻱00µı���

ţ¿ʒՓ®ext-key̸¶¨՚ţµœ¦ԃ³͐󊐊µЖһ¸�»є¼쳩£ºɧ¹�¹²Эģʽ¶Š� ²¢ȒûԐ¿´µ½ţ½«Ҫ²扫µŐеŖظ´£¬ղţ¿ʒ԰²ȫµز扫ţµŐУ¬²¢Ȓ֪µTڶy�Մ㶄ѐµļ̳ѕࠉ闃µîext-key̸¶¨ԫ´̍¬ʱبֹɎºψ˶Մ㶄 ѐ²扫һ¸�´¡£Ӳ´ˣ¬the next-key̸¶¨Պѭţ̸ס՚ţµı²¢²»´畚µŒ»Щ¶«Ϸ¡£

¼ډ酣ֽՋѐ՚ĬɏµÒEPEATABLE READ¸�¶±𡣵±ţ·¢³���ʱ£¬¼´һ¸�¨µÓELECTԯ¾䣬InnoDB°´֕ţµIJꑯ¿´µ½µŊ�¬¸�˂ϱһ¸�䶣¡£ɧ¹�¸�򕛄㶄ʱ¼䶣±»ָ¶¨֮º􉾳�¢͡½»£¬ţ²»»ῴ¼�ɾ³�£²扫º͸�ጆµش¦m¡£

ţ¿ʒՍ¨¹�㶄˂ϱ4͡ǰţµŊ±¼䶣£¬Ȼº�¸�ECT¡£

֢±»³ǎª¶౦±¾²¢·¢¿ٖƣ¨multi-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Ҳӑ¾­͡½»֮ʱ£¬ԃ»§A¿ɿ´¼�»§B²扫µŐУ¬Ӳ´̊±¼䶣 ˇ՚ԃ»§B͡½»֮ǰ¡£

ɧ¹�¿´˽¾ݿⶄخт״̬£¬ţӦ¸ē’EAD COMMITTED¸�¶±𼳓Ē»¸�¨¶cº

SELECT * FROM t LOCK IN SHARE MODE;

՚SQL²ꑯ´¦mא£¬һ¸�¨¶c¬һ¸�ATE»󒻸�ETEһ°㶘¶Ա»ɨĨµŃ¿¸��闃¼Ȃ¼̸¶¨¡£ɧ¹�²ꑯאԐһ¸�RE͵¼�²ô¹ُµµģ¬¶�²ꑯ¿ʄܴӲꑯµĽẻ°��nnoDB²»¼ǵĈ·Ȑµ×HERE͵¼�ǽ�Dĸ��»ɨĨ¡£¼Ȃ¼̸¶¨ˇֽ³£µîext-key̸¶¨£¬̼Ҳبֹ¶Խ�żȂ¼֮ǰµļ叶µIJ扫¡£

ɧ¹�±»ʨ׃Ϊ¶U¼£¬ղInnoDB؜ˇȡ»ؼ¯˸µŋ�¿¼²¢¶Ն动׃̸¶¨¡£

ɧ¹�Ԑˊºϲꑯµŋ�¬MySQL²»µò»ɨĨֻ¸�´´¦m²ꑯ£¬±���ÿѐ±䴉±»̸¶¨µģ¬֢ҹ·´¹�¹Ǥ̼ԃ»§µŋ�±���²扫¡£´´½¨һ¸�ŋ�Ą㶄²ꑯ²»±ْªɨĨºܶᑐˇºݖْªµġ£

MySQLӔĬɏՊѭautocommitģʽ4¿ªʼÿһ¸�§¶́¬½ӡ£µ±autocommit±»Պѭ֮ʱ£¬ɧ¹�￤²»·µ»شµĻ°£¬MySQL՚ÿ¸�ԯ¾單º󣭗��»¡£

ɧ¹�±ԡutocommitģʽ²¢Ȓ¹ر֒»¸�Ӷ�ԃţµŊÎ񶅃�ύ£¬ղMySQL»ع�ŊÎ񡡠

ɧ¹�￤·µ»ْ»¸�󣭌ύ/»ع�ªȡ¾�⹶´¡£ȫ²ϔ¼a href="http://book.chinaz.com/mysql5/storage-engines.html#innodb-error-handling" title="15.2.15. InnoDB Error Handling">15.2.15½ڣ¬¡°InnoDB´´¦m”¡£

Ђÿһ¸�䣨Ӕ¼°̼ćµňκύ¬ӥ´ʣ©Ӿʽ½⋸һ¸�񣬾ͺď򕛖´ѐ֢¸�單ǰţض‹һ¸�MIT£º

˂ϱ²»Ŝ±»Ƕ͗¡£µ±ţ·¢³�RT TRANSACTIONԯ¾伲ԫ֮ͬӥµœ￤֮ʱ£¬֢ˇ¶Ոκε±ǰ˂ϱ Ӿʽ͡½»µŒ»¸��o:p>

InnoDBؔ¶¯¼쳢˂ϱµŋK�»ع��¸¸�򀴷V¹̸̀¡£InnoDB˔؅ʹѡС˂ϱ4»ع�Î񶄴�¨¹�롢¸�¾³�Ŋ�·¶¨¡£

ɧ¹�db_table_locks=1 £¨1ˇĬɏֵ£©£¬InnoDBӢʶµ½±¶¨£¬ǤʏµÍySQL²䖪µ�ow-level̸¶¨¡£­΢InnoDB²»Ŝ՚MySQL LOCK TABLESʨ¶¨±¶¨µĵط½»󖠉漰InnoDB֮΢µĴ洢ӽȦʨ׃̸¶¨µĵط½¼쳢̸̀¡£ţ±ِ써¹�nnodb_lock_wait_timeoutϵͳ±偿µŖµ4½⿶֢Щȩ¿�

µ±InnoDִѐΪȫµŊÎ񼘹�±£¬¸ĊÎ񶅋�¶¨±»ˍ·š£¾¡¹݈絋£¬ɧ¹�SQLԯ¾䱻ӲΪ´µŔ­Ӳ±» »ع�“QLԯ¾动¶¨µIJ¿·׋�Ŝ±»±£´¡£֢ˇӲΪInnoDBӔһז·½ʽ´洢ѐ̸¶¨£¬՚֢ז·½ʽא̼²»Ŝ֪µK滳µńĸ�¨ˇ±»ń¸�ԯ¾动¶¨µġ£

̸̀ˇ˂ϱэ˽¾ݿⶤэµŎˌ⣬µ«ˇ³�Ȇµ·±³�Ֆ“ۄ㹼±¾²»ŜՋѐij¸�񣭋�䋇²»ΣЕµġ£ֽ³£µأ¬ţ±ِ베дţµœ¦ԃ³͐󊹵ċ�ȗ¼±¸ɧ¹�̸̀¶�¹��񿎖ِ·¢³��񡡠

InnoDBʹԃؔ¶¯ѐ¼¶̸¶¨¡£¼´ʹ՚ֻ²扫»󉾳�еŊÎ񶅇ꀶЂ£¬ţ¿ʒՓ�K�ˇӲΪ֢Щ²ڗ�ȕ疽µ¦ldquo;¼«Сµ¦rdquo;£¬̼ćؔ¶¯¶Բ扫»󉾳�ģ¨¿ʄ݊Ȋ��Ȃ¼ʨ׃̸¶¨¡£

ţ¿ʒՓďÁм¼˵¶Ը¶̸̀¼���Ŝє£º

·                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

·         ɧ¹�¶¥²㹤¾߻󖞗indowsɎϱ¹݀Дʾ£¬ţµŊ�Ťط¸ººɵÃPUʹԃÊСԚ70%£¬ղţµĹ¤ط¸ººɿʄ݊Ǵƅ̰󶨵ģ¬¿ʄ݄䖽ʺ³ʌ«¶඄˂ϱºΌύ£¬»󖟻º³崘̫С¡£ʹµûº³崘¸�»Щ»┐°𐗺µģ¬µ«²»Ҫʨ׃»º³崘µɓڻ򳬹�ڴ涄80%.

·         °Ҋ�Ĺ��򁯡£ɧ¹�¶Պ�޸ģ¬InnoDB՚¸ĊÎ򍡽»ʱ±ِ싢тɕ־µ½´ƅ̡£ӲΪ´ƅ͐�ٶɖv�7ת/ī£¬ɧ¹�ûԐƭ²ڗ�³µĻ°£¬֢¾ΏߖnjύµŊ�¬ҹµŃ¿ī167´Ρ£

·         ɧ¹�Ӕ½Ԋ݋񊨒»Щخ½�ύ˂ϱ£¬ţ¿ʒՉ闃my.cnfτ¼�ϊ�b_flush_log_at_trx_commitΪ0¡£ ϞÛɧº͉nnoDB˔؅ÿī ˢтһ´ψ֖¾£¬¾¡¹݋¢т²»±»ѭ¿ɡ£

·         ʹԃ´󶅈֖¾τ¼��뻺³崘һҹ´󡣵±InnoDBдúɕ־τ¼��»՚һ¸�鶣°ѻº³崘ӑў¸ĵńۈސ´½�¡£Сɕ־τ¼����²»±ْªµŎ�ڗ�􉖖¾τ¼�䊱»ָ´ʱ¼乼³¤¡£

·         ҲɃɕ־»º³搠µ±´󣩓鷍BР̆µŊ�

·         ɧ¹�´¢±䳤¶ɗַ�»󖠁пʄܰ��LLֵ£¬ղʹԃVARCHAR`э¶�CHAR`э¡£һ¸�R(N)؜ˇռ¾ۼi>N ؖ½ۀ´´洢£¬¼´ʹؖ·�¶̻󘖷�ֵˇNULL¡£ԽСµıºõيʺϻº³崘²¢Ȓ¼�ƅˉ/O¡£

µ±ʹԃrow_format=compact £¨MySQL 5.1א ĬɏµÉnnoDB¼Ȃ¼¸򊽣©ºͿɱ䳤¶ɗַ�±Ɉ攔F-8»񲩩s£¬CHAR(N)½«ռ¾ݿɱ勽µĿռ䣬ׁʙΪN ؖ½ڡ£

·         ՚һЩ°汾µÇNU/Linuxº̕nixʏ£¬ԃUnixµæsync()£¨InnoDBĬɏʹԃµģ©°Ҏļ�½´ƅ̣¬²¢ȒǤ̻Р̆µķ½·¨ˇ¾ªɋµł��úӢ˽¾ݿⶄдєŜ£¬ţ¿ʒՊ՗Ɖ闃my.cnfoµénnodb_flush_methodΪO_DSYNC£¬̤ȻO_DSYNC՚¶ዽϵͳʏ¿´ǰ4¸�

·         µ±՚Solaris 10ʏ£¬Ϊx86_64¼ܹ¹£¨AMD Opteron)ʹԃInnoDB´洢ӽȦ£¬טҪµŊȊ¹ԃforcedirectioѡЮ4°²װɎºώª´洢ԫInnoDBР¹صŎļ�õŊ�³¡££¨Ĭɏ՚Solaris 10/x86_64ʏ²»ʹԃ֢¸��²װѡЮ £©¡£ʹԃforcedirectio ʧ°ܻᵼׂInnoDB՚֢¸�¨ʏµŋٶȺΐՄݑЖٍ˻¯¡£

·         µ±µ¼ɫ˽¾ݵ½InnoDBא֮ʱ£¬ȫȷхMySQLûԐՊѭautocommitģʽ£¬ӲΪՊѭautocommitģʽ»⑨Ҫÿ´β扫¶¼Ҫ ˢтɕ־µ½´ƅ̡£Ҫ՚µ¼ɫ²ڗ�͖н�tocommitģʽ£¬ԃSET AUTOCOMMITº̃OMMITԯ¾倴°�씯¾䣺

·                SET AUTOCOMMIT=0;

·                /* SQL import statements ... */

·                COMMIT;

ɧ¹�ԃmysqldump ѡЮ--opt£¬¼´ʹ²»ԃSET AUTOCOMMITº̃OMMITԯ¾倴°�䒲ʹµÿ팙µŗª´¢τ¼�뵽InnoDB±¡£

·         Сф´�扫µĴ󼘹�nnoDB՚²扫אʹԃ²扫»º³怴½۔¼´ƅˉ/O£¬ µ«ˇ՚РӦµĻع�»Ԑʹԃ֢ҹµĻ�һ¸�̰󶨵Ļع�Փďᓦ²扫»¨·Ҋ±¼䶄30±¶4ִѐ¡£ɱµ�ݿ⾸³̓»Ԑˇ°𐗺µģ¬ӲΪ»ع���± »╙´φ�£³��صĻع�¨һ·½·¨ˇն´󻺳崘ʹµûع�ȃPU°󶩇Ӆܵÿ죬»󖠊¹ԃרԃ²½ר£¬ȫ²ϔ¼a href="http://book.chinaz.com/mysql5/storage-engines.html#forcing-recovery" title="15.2.8.1. Forcing Recovery">15.2.8.1½ڣ¬¡°ǿ׆»ָ´”¡£

·         ҲҪСфǤ̼´󶄴ƅ̰󶨲ڗ�„ROP TABLE»񂑅ATE TABLE4ȥ¿֒»¸�¬¶�ԃDELETE FROM tbl_name¡£

·         ɧ¹�Ҫ²扫ѭ¶ᑐ£¬ղʹԃ¶ᑐ²扫ԯ·¨4¼�ͻ§¶˺ͷ�®¼䶄ͨѶ¿ª֧£º

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

֢¸�¾¶Ե½ɎºαэµIJ扫¶¼ˇºϷ¨µģ¬²»½�ǶӉnnoDB`э¡£

·         ɧ¹�µڶ�ГϕNIQUEԼ˸£¬ţ¿ʒՔڵ¼ɫ»Ựא՝ʱ¹ر֎¨һє¼쳩Ӕ¼ԋٱ���µ¼ɫ£º

·                SET UNIQUE_CHECKS=0;

¶Փڴ󲭣¬֢½۔¼‹´􁿴ƅˉ/O£¬ӲΪInnoDB¿ʒՊ¹ԃ̼µIJ扫»º³怴՚һƺŚдµڶ��£

·         ɧ¹�ţµıFOREIGN KEYԼ˸£¬ţ¿ʒՔڵ¼ɫ»Ự¹�¨¹�⽼¼쳩4̙͡±���µ¼ɫ£º

·                SET FOREIGN_KEY_CHECKS=0;

¶Փڴ󲭣¬֢¿ʒԽ۔¼´􁿵Ĵƅˉ/O¡£

·         ɧ¹�³£Ԑ¶Բ»¾­³£¸����ט·¢²ꑯ£¬ȫʹԃ²ꑯ»º´棺

·                [mysqld]

·                query_cache_type = ON

·                query_cache_size = 10M

InnoDB°�oDB Monitors£¬̼´󓢓й׉nnoDBŚ²¿״̬µŐƏ¢¡£ţ¿ʒՊ¹ԃSQLԯ¾㒈OW INNODB STATUS4ȡ±뗼InnoDB MonitorµŊ䴶̍µ½ţµÓQL¿ͻ§¶ˡ£֢¸�¢՚єŜµ�±Ԑԃ¡££¨ɧ¹�ʹԃmysql˂ϱSQL¿ͻ§¶ˣ¬ɧ¹�\Gͦ´�µœ￤וֹ·�£¬ˤ³��¿ɶ� £©¡£¹ٓىnnoDB̸¶¨ģʽµŌׂۣ¬ȫ²ϔ¼a href="http://book.chinaz.com/mysql5/storage-engines.html#innodb-lock-modes" title="15.2.10.1. InnoDB Lock Modes">15.2.10.1½ڣ¬¡°InnoDB̸¶¨ģʽ”¡£

mysql> SHOW INNODB STATUS\G

­һ¸�‰nnoDB Monitorsµķ½·¨ʱɃ̼ć²»¶А´˽¾ݵ½mysqld·�ı뗼ˤ³�ە㗖ȩ¿�¬ûԐˤ³�͵½¿ͻ§¶ˡ£µ±±»´򿪵Ŋ±º򣫉nnoDB Monitorsÿ15ī´󓢒»´ϊ��䴶ͨ³£±»¶¨вµ½MySQL˽¾ބ¿¼oµ®errɕ־¡£֢¸�ޔېՄܵ�±Ԑԃ¡£՚Windowsʏ£¬ɧ¹�¶¨вˤ³�°¿؍ ¶�µ½ɕ־τ¼�ِ뵓¿ٖnj¨´°¿ڵŃ�¹ԃ--consoleѡЮ4Ǵ¶¯·�£

¼ዓǷˤ³�¨Ђ`эµŐƏ¢£º

ҪɃ±뗼InnoDB Monitorдµ½mysqldµı뗼ˤ³�승ԃЂSQLԯ¾䣺

CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

¼ዓǷ¿ʒՍ¨¹�Áѓ￤4±»ֹͣ£º

DROP TABLE innodb_monitor;

CREATE TABLEԯ·¨ֽˇͨ¹�µÓQL½㏶³͐󎸉nnoDBӽȦ´«µރ�½·¨£ºΨһԐϊ͢µŊÇꋇ±ؖinnodb_monitor¼°̼ˇһ¸�oDB±���¶Փؼb>InnoDB Monitor, ±���½ṹ¸�Р¹ء£ɧ¹�¼ዓǷֽՋѐʱ¹رշ�¬²¢ȒţЫҪՙ´φ�ዓǷ£¬ţ±ِ앚¿ʒԷ¢³�REATE TABLEԯ¾凴¶¯¼ዓǷ֮ǰӆ³�⹶ԯ·¨՚½«4µķ¢ѐ°汾א¿ʄܻṄ±䡣

ţ¿ʒՒՏጆµķ½ʽʹԃinnodb_lock_monitor¡£³�²͡¹©´􂀋�Ϣ֮΢£¬̼ԫinnodb_monitorРͬ¡£һ¸�분 innodb_tablespace_monitor´󓡴畚Ԛ±¼嗐ӑ´´½¨τ¼�Œ»¸����²¢Ȓȷɏ±¼世Ƥ˽¾ݽṹ¡£´̍⣬Ԑinnodb_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·¢̍֯¶Њ䴶µ½stderr»󏄼�»ˇµ½stdout»󖟹̶¨³ߴ腚´滺³壬Ӕ±݃ˊµײ㻺³擧³��»¸�§¹�OW INNODB STATUSµŊ䴶ÿ15īדдµ½һ¸�¬τ¼���׊Ʃnnodb_status.pid£¬Ǥאpid ˇ·��¡£֢¸��QL˽¾ބ¿¼o´´½¨¡£ֽ³£¹ػ�£¬InnoDBɾ³�ļ��²»ֽ³£µĹػ�㐩״̬τ¼��»չʾ£¬¶�ѫ±»˖¶¯ɾ³�Ƴ�®ǰ£¬ţ¿ʄݏ쒪¼쳩̼ć4¿´̼桷񱼺¬Ԑ¹ز»ֽ³£¹ػ�ӲµœѓĐƏ¢¡£½�嗃ѡЮinnodb_status_file=1±»ʨ׃֮ʱ£¬innodb_status.pidτ¼�¨¡£

15.2.13.1. ̷ӽµŎ𐁭½ṹ

15.2.13.2. »º³峥ɫ

15.2.13.3. ˊӦµĹ��/span>

15.2.13.4. ϯm¼Ȃ¼½ṹ

̹ԐInnoDBµŋ�Ƃ˽£¬Ǥא̷ӽ¼Ȃ¼±»´洢՚˷µŊ�³¡£һ¸��¬ɏ´�ű6KB¡£µ±т¼Ȃ¼±»²扫£¬InnoDB˔؅Ϊ½«4̷ӽ¼Ȃ¼µIJ扫º͸�Ê®¹·ז®һµĿհؒ³¡£

ɧ¹�¼Ȃ¼ӔlѸµŋ³Ѳ±»²扫£¨ʽѲ»󖟽µѲ£©£¬½ẻ̷ӽҳ´�ű5/16ú¡£ɧ¹�±»Ӕ̦»�Ѳ±»²扫£¬ҳĦˇ´ѱ/2µ½ 15/16ú¡£ɧ¹�ҳµŌӲؓ½µµ½µΓر/2£¬InnoDB˔؅̷̷̑ӽ˷4ˍ·ƒ³¡£

՚˽¾ݿ㓦ԃא£¬׷¼��»µŊ¶±𸻣¬²¢Ȓтѐ±»Ӕ׷¼�󀴲扫£¬֢ˇ¸��¿�򵋣¬µ½¼¯˸̷ӽµIJ扫²»ѨҪ´Ԓ»¸�͋漺¶a£

­һ·½Ħ£¬µڶ�¨³£ˇ·Ȏ¨һµģ¬µ½µڶ�IJ扫ӔР¶Ջ漺´ϐ򷣉�¿ʄܻᵼׂ´􁿵ŋ漺´ƅˉ/O²ڗ��һ¸�ĔىnnoDBאµŗ¨ԃ»�

ɧ¹�̷ӽ¼Ȃ¼Ӧ¸ñ»²扫µ½һ¸�¨һµڶ�¬InnoDB¼쳩µڶ�³ˇ·򕚻º³崘א¡£ɧ¹�InnoDBֱ½Ӳ扫µ½̷ӽҳ¡£ɧ¹�ҳûԐ՚»º³崘א±»·¢Ж£¬InnoDB²扫¼Ȃ¼µ½һ¸�ŵIJ扫»º³御¹¹¡£²扫»º³屻±£³ֵĈ絋СӔׁԚ̼ΪȫˊºДڻº³崘£¬²¢Ȓ¿ʒԷdz£¿춘ض²扫¡£

²扫»º³旜ǚµر»ºϲ¢µ½˽¾ݿ㗐µڶ��£°Ҋ�뻏²¢µ½̷ӽ˷µō¬һҳ£¬½ۊ¡´ƅˉ/O²ڗ�­³£µٕ㋇Ԑ¿ʄܵġ£¾ݲみ£¬²扫»º³怉Ӕ͡¸ߵ½±���²扫̙¶ȴ���±¶¡£

՚²扫˂ϱ±»͡½»֮º󣬲扫»º³廏²¢¿ʄ݁¬Ѹ·¢ʺ¡£ʵ¼ˉϣ¬·�رպΖن�󣭕⼡lѸ·¢ʺ¡££¨ȫ²ϔ¼a href="http://book.chinaz.com/mysql5/storage-engines.html#forcing-recovery" title="15.2.8.1. Forcing Recovery">15.2.8.1½ڣ¬¡°ǿ׆»ָ´”£©¡£

µ±ѭ¶ක¶�ِ뱻¸�±£¬²¢Ȓѭ¶ᑐӑ±»²扫֮ʱ£¬²扫»º³廏²¢¿ʄݐ钪˽¸�±¡£՚֢¸�党£¬´ƅˉ/O½«»╶¼ӣ¬֢ҹ»ᵼׂ´ƅ̰󶨲ꑯķД»ºý¡£­һ¸�Եĺ􌧉/O²ڗ�»»¯П³̣¨ȫ²ϔ¼a href="http://book.chinaz.com/mysql5/storage-engines.html#innodb-multi-versioning" title="15.2.12. Implementation of Multi-Versioning">15.2.12½ڣ¬¡°ʵЖ¶౦±¾»¯”£©¡£

ɧ¹�±���º�«ƤºЖ�棬՚Ǥʏִѐ²ꑯخ¿춄·½·¨¾ΊȊ¹ԃ¹��oDBԐһ¸�¯»�̼¼ዓ¶Վªһ¸�¨ӥµŋ�ŋ�ҋ�纻InnoDBעӢµ½²ꑯ»ᵓ½¨bһ¸�£̷ӽא»򓦣¬̼»☔¶¯µٕヴض¡£

עӢ£¬¹���ʏӑ´畚µÂ˷̷ӽ4½¨b¡£¸�noDB¶ӂ˷̷ӽ¹۲춄̷̑·½ʽ£¬InnoDB»╚Ϊ¸‚˷¶¨ӥµňκ㤶ȵ ¼��ºʏ½¨b¹��ϣ̷ӽ¿ʒՊDz¿·ֵģº̼²»Ҫȳֻ¸�̷ӽ±»»º´畚»º³崘¡£InnoDB¸�Ҫ¶Ա»¾­³£·Ďʵŋ�ńȐ©ҳĦ½¨b¹��:p>

՚ijזӢӥʏ£¬InnoDBͨ¹�ḻ׷Ś´涄ˊӦµĹ��´¼�Լº£¬¸��狽¾ݿⶄ¼ܹ¹¡£

InnoDB±µļȂ¼ԐɧЂַ͘£º

15.2.14.1. ´ƅˉ/O

15.2.14.2. Ϊ±¼効ԃԭʼʨ±¸

15.2.14.3.τ¼�݀뺯a>

15.2.14.4. ֻm±Ƭ

InnoDBʹԃģŢӬ²½´ƅˉ/O£ºInnoDB´´½¨ѭ¶ᐟ³̀´´¦mI/O²ڗ�Ɉ汥ad-ahead.

՚InnoDBאԐ}¸�pan>read-ahead˔̽£º

InnoDBʹԃһ¸�ǎªdoublewriteµŐÓ±µŎļ�¼¼˵¡£̼¸�ͥͳ±@£»򶴵绳µĻָ´ͭ¼ԁ˰²ȫ£¬²¢Ȓͨ¹�Ӧsync()²ڗ�钪£¬̼՚¶ዽUnix±嗖ʏ¸ʼnǁ̐Մܡ£.

DoublewriteӢΪ՚вһ¸�ގļ�®ǰ£¬InnoDB˗Ј°ҋ�½һ¸�ڵı¼券Բ£¬³ǎªdoublewrite»º³塣½�´Ȼº񋋋¢тµ½doublewriteӑ¾­Ϊ³ʖ®ǰ£¬InnoDBдҳĦµ½̼ć՚±¼嗐ǡµ±µŎ»׃¡£ɧ¹�ϵͳ՚дҳĦµŖм䲀#£¬՚»ָ´¹�¬InnoDB¿ʒՔۋ滳´Ҥoublewrite»º³旐֒µ½ҳĦµŒ»¸�ø´׆¡£

ţҲ¿ʒՊ¹ԃԭʼ´ƅ̷ׇ�±¼勽¾ގļ��­ʼ´ƅ̣¬ţ¿ʒՔٗindowsºΒ»ЩUnixϵͳʏִѐnon-buffered I/O ¶�τ¼�ª֧£¬֢ҹ¿ʒԸʼnǐՄڠ

µ±ţ´´½¨һ¸���¬ţ±ِ앚innodb_data_file_patho½�Ɗ��®º󸆖M ¹ؼ�raw¡£·ׇ�ׁʙº΄䖸¶¨µijߴ蒻ҹ´󣭗¢Ӣ£¬՚InnoDBא£¬1MBˇ1024x1024ؖ½ڣ¬ µ«ˇ՚´ƅ̹湱א£¬1MBͨ³£ӢΪ1,000,000ؖ½ڡ£

[mysqld]

innodb_data_home_dir=

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

Ђ´τ䇴¶¯·�®ʱ£¬InnoDBעӢµ½¹ؼ�raw²¢³�¯т·ׇ�ˇɔȻ²¢²»´´½¨»򹄱剎º͉nnoDB±���­΢£¬µ±ţטǴ·�®ʱ£¬InnoDBטт³�¯·ׇ�µĸı伡¶ªʧ¡££¨´Ѽst1:chsdate year="1899" month="12" day="30" islunardate="False" isrocdate="False" w:st="on">3.23.44Ǵ¶¯£¬طΪһ¸�«´슩£¬ µ±ԃnewrawָ¶¨Ɏºηׇ�£¬InnoDBبֹԃ»§ў¸Ŋ�£

InnoDB³�¯т·ׇ�£¬ֹͣ·�¬՚¶ՐеŊ��иı㭥wraw£º

[mysqld]

innodb_data_home_dir=

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

Ȼº���¬²¢ȒInnoDBՊѭض¸ı䡣

՚Windowsʏ£¬ţ¿ʒՏ��´ƅ̷ׇ�¸�ގļ�:p>

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=//./D::10Gnewraw

¶ՓڷĎˎ𐁭Ƚ¶¯Ƿ£¬//./ Рµ±ԚWindowsԯ·¨µÜ\.\ ¡£

µ±ţʹԃԭʼ´ƅ̷ׇ�£¬ȷх̼ćԐՊѭ±»ԃ4ՋѐMySQL·�ŕʺŶzΐ´·ĎʵŐ¡£

ţ՚Ƥ׃τ¼�嶄˽¾ގļ�nnoDBµı¼䡣τ¼�¥µف¬ǰ4Ӕю³ɱ¼䡣ûԐ͵φ՚ʹԃ¡£µ±ǰţ²»Ŝ¶¨ӥţµı���·ׅ䵽±¼嗐µŎ»׃¡£µ«ˇ£¬՚һ¸�´½¨µı¼嗐£¬InnoDB ´ӵے»¸�ގļ�ׅ倕¼䡣

±¼䱼º¬˽¾ݿ㒳£¬Ĭɏ´�ű6KB¡£֢Щҳ±»·ח鴉64¸��·¶Χ¡£±¼党µŎļ�oDBא±»³ǎªƬ¶ϡ£˵ԯ“rollback segment”ԐһЩ»퐽£¬ӲΪ̼ȷȐµذ�ಭ¿ռ冬¶ϡ£

՚InnoDBא£¬ÿ¸��½¸�ϡ£һ¸��ķȊ�ڵ㶄£¬­һ¸��½ڵ㶄¡£՚֢o£¬mЫµŊȎª°�ݵŊ�ڵ㵯µ½¸�ѐ󑔡£

µ±һ¸�Дڱ¼党³¤´󣫉nnoDBµ¥¶5طׅ嘮Јµ³2ҳ¸�´˺򈭮oDB¿ªʼ·ׅ喻¸�§¸�¶ϡ£InnoDB¿ʒՒ»´θ�´􆬶Ќ���¶൯4¸�§Ӕȷ±£˽¾ށ¼ºõŁ¬Ѹє¡£

՚±¼嗐µŒ»Щҳ°��»ͼ£¬Ӳ´̔ے»¸�oDB±¼嗐µŒ»Щ·¶Χ²»Ŝ±»ֻ¸�ׅ乸Ƭ¶ϣ¬ֻŜطΪµ¥¸�»·ׅ䡣

µ±ţ·¢³�W TABLE STATUSѯϊ±¼偯¿ʓõŗՓɿռ單ʱ£¬InnoDB±¨¸畚±¼嗐Ϊȫؔԉµķ¶Χ¡£InnoDB؜ˇΪɨ³��¿µı£´һЩ·¶Χ£¬֢Щ±£´µķ¶Χ²»°�Փɿռ偯¡£

µ±ţ´Ԓ»¸�щ¾³�®ʱ£¬InnoDBjϵРӦµÂ˷̷ӽ¡£ˇ·򋍷ŵ¥¶R³»󋇷¶Χµ½±¼刡¾��½£¬Ӳ´˱»ˍ·ŵĿռ䲤³ɶՆ匼ԃ»§¿ʓ㬵«ˇ¼ȗ¡£¬ӑɾ³��Đв»ՙ±»˂ϱ »ع�א�ѨҪ֮º󶅒»¸�Զ¯£©¾»»¯²ڗ�»ϯmɾ³�:p>

ɧ¹�»�µ½±���̷ӽ»򵓱���̷ӽ̦»�£¬̷ӽ¿ʄܱ䴉̩Ƭµġ£̩ƬӢ˼ˇ̷ӽҳ՚´ƅ͉ϵŎ𐁭ƅѲ²¢²»½ӽ�Ȃ¼µŋ�Ɛ򣬻󖠔ڷׅ乸̷ӽµ¶4ҳ¿ꊏԐѭ¶჻Ԑ±»ʹԃµŒ³¡£

̩ƬµŒ»¸�quo;ͬӥ´Ȧrdquo;ˇһ¸�¼¾ݵĿռ䳬¹�ĕ¼¾ݵĿռ䶄´񋋐¡¡£ȷȐˇ¶ኙ£¬֢ˇº݄҈¥ȷ¶¨µġ£̹ԐInnoDB˽¾ݺ΋�»´畚B˷א£¬²¢Ȓ̼ćµŌӲؓ¿ʄܴѵ0%µ½100%¡£̩ƬµŁ¸�quo;ͬ ӥ´Ȧrdquo;ˇһ¸�¨Ĩ}ɧ£º

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

»¨‹³¬¹�û¨µŊ±¼䡣£¨՚ʏĦµIJꑯאϒć“Ǜƭ”SQLԅ»¯Ƿ4ɨĨ¼¯˸̷ӽ£¬¶�һ¸�� £©¡£¶ዽ´ƅ̿ʒԶ�0MB/sµ½50MB/s£¬֢¿ʒԱ»ԃ4ǀ¹R»¸�¨Ĩ¿ʒԶာµٔ̐С£

ɧ¹�ǚµٖ´ѐ“null” ALTER TABLE²ڗ��Լԋڋ�¨Ĩ£º 

ALTER TABLE tbl_name ENGINE=INNODB

֢µ¼ׂMySQLט½¨±���­һ¸�ыꆬֻm²ڗ�취ˇʹԃmysqldump4ת´¢һ¸�½һ¸�¾τ¼��¢טт´ԗª´¢τ¼�ً�:p>

ɧ¹�¸��옜ˇʽѲµģ¬²¢Ȓ¼Ȃ¼½�©β±»ɾ³�oDBτ¼�݀���֤՚̷ӽאµŋꆬ²»»ᷢʺ¡£

15.2.15.1. InnoDB´´�a>

15.2.15.2. ²ڗ�³´´�a>

ЂĦµŕ⹶²»Ϊȫ±ţ¿ʄݓ�ij£¼�noDBרԐ´£¬´�ʲô·¢ʺµŔ­ӲӔ¼°ɧºθý⿶ϊ͢µŏຘхϢ

Ҫ´󓢒»¸��³´ºŵŒ㋼£¬ȫʹԃMySQL·ַ¢°亯st1:state>oµ¼strong>perror³͐򡡠

ЂĦ±¹©һЩ³£ԃLinuxϵͳ´´�¸�Łб²ϔ¼a href="http://www.iglu.org.il/lxr/source/include/asm-i386/errno.h" target="_top">Linux source code¡£

Ђ±¹©һ³£ԃWindowsϵͳ´´�Ϊֻ±·Ďȼa href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/debug/base/system_error_codes.asp" target="_top">Microsoft website¡£

·         һ¸�»Ŝ°��С£

·         Ś²¿خ´󽼳¤¶Ɋų500ؖ½ڣ¬µ«MySQLؔ¼ºО׆֢¸�024ؖ½ڡ£

·         ³�CHAR, BLOBº̔EXT£¬خ´�¤¶ɉՎ¢СԚ˽¾ݿ㒳µŒ»°롣¼´£¬خ´�¤¶ȴ�000ؖ½ڡ£LONGBLOBº̌ONGTEXT±ِ쐡Ԛ4GB, ؜µŐ㤶ȣ¬ҳ°�Bº̔EXT£¬±ِ쐡Ԛ4GB¡£InnoDB՚ѐא´洢VARCHAR£¬BLOB»񓄘TµŇ°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±���֧³Լ/span>FULLTEXT̷ӽ¡£

·         ANALYZE TABLE ͨ¹���˴ϋ漺ʮɫ²¢РӦµظ��µ£¬֢ҹ4¼NJ�¢Ӣ£¬ӲΪ֢ˇ½�ĹVµ£¬·´¸´ՋѐANALYZE TABLE»ᳺʺ²»ͬ˽¡£֢ʹµ`ANALYZE TABLE ՚ InnoDB ±ºܿ죬²»ˇ°ٷְڗ¼ȷ£¬ӲΪ̼ûԐ¿¼Ç̹ԐµŐС£

MySQL ²»½�㻏ԅ»¯אʹԃ̷ӽ¼¯¹Vµ¡£ɧ¹�»㻏ûԐӔֽȷµķ½ʽԅ»¯£¬ţ¿ʒՊՒ»Ђ ANALYZE TABLE ¡£º݉ړчꀶ£¬ANALYZE TABLE ûԐ²�ţ͘¶¨µı¹»ºõŖµ£¬ţ¿ʒՊ¹ԃ FORCE INDEX ՚ţ²ꑯא4ǿ׆ʹԃ͘¶¨̷ӽ£¬»󖠉闃 max_seeks_for_key 4ȷ±£MySQL՚±Ĩ֮ʏՋѐ̷ӽ²ꖒ¡£ȫ²ϔ¼a href="http://book.chinaz.com/mysql5/database-administration.html#server-system-variables" title="5.3.3. Server System Variables">5.3.3½ڣ¬¡°·�µͳ±偿”¡£ȫ²ϔ¼a href="http://book.chinaz.com/mysql5/problems.html#optimizer-issues" title="A.6. Optimizer-Related Issues">A.6½ڣ¬¡°ԅ»¯ǷР¹صŎˌॲdquo;¡£

·         ՚Windowsʏ£¬InnoDB؜ˇŚ²¿µٓĐ¡дؖĸ´洢˽¾ݿ⻍±ؖ¡£Ҫ°Ҋ�Զ�ϊ½´ҕnix ӆµ½Windows£¬»󖟴җindowsӆµ½Unix£¬ţӦ¸Ĉċ�¾ݿ⻍±���Ļؖ¶¼ˇСд¡£

·         ¾¯¸丼/span> ²»Ҫ՚MySQL˽¾ݿㅚµİЍySQLϵͳ±���MyISAMתΪInnoDB±���֢ˇһ¸�»֧³ֵIJڗ�纻ţ֢ôض‹£¬MySQLֱµ½ţ´ӱ¸·ݻָ´¾ʏµͳ±���»󔁼strong>mysql_install_db½ű¾ט½¨ϵͳ±���טǴ¶¯¡£

·         InnoDB՚±²»±£´ѐµńڲ¿¼NJ�󎪶౦±¾»¯£¬֢¿ʄ݈·ʵԐЩ¸´Փ £©¡£Ҫ´¦mһ¸�ECT COUNT(*) FROM tԯ¾䣬InnoDB±ِ쉨Ĩ±���һ¸���̷ӽ²»՚»º³崘א£¬ɨĨѨҪ»¨һЩʱ¼䡣Ҫ»񶃿팙¼NJ�»µò»ʹԃһ¸�º´´½¨µļNJ�¬²¢Ƀţµœ¦ԃ°´̼֕ضµIJ扫ºΉ¾³�Ë��±���²»¾­³£¸ı䣬ʹԃMySQL²ꑯ»º´犱һ¸�Ľ⿶·½°¸¡£ɧ¹�µŐъ�»‹£¬ղSHOW TABLE STATUSҲ¿ɱ»ʹԃ¡£ȫ²ϔ¼a href="http://book.chinaz.com/mysql5/storage-engines.html#innodb-tuning" title="15.2.11. InnoDB Performance Tuning Tips">15.2.11½ڣ¬¡°InnoDBєŜµ�⊾”¡£

·         ¶ՓفUTO_INCREMENT£¬ţ±ِ옜ˇΪ±���ӥһ¸��Ӌ�ِ뱼º¬AUTO_INCREMENT¡£՚MyISAM±£¬AUTO_INCREMENT¿ʄ݊±¶�̷ӽµŒ»²¿·֡£

·         µ±ţטǴMySQL·�®ʱ£¬InnoDB¿ʄݎªһ¸�O_INCREMENTטʹԃһ¸�µ£¨¼´£¬һ¸�³¸�OµŒύ »ع�Î񶅖µ£©¡£

·         µ±һ¸�O_INCREMENTԃΪֵ£¬InnoDBО׆һ¸�INTµ½£­9223372036854775808Ӕ¼°BIGINT UNSIGNEDµ½1¡£¾¡¹݈絋£¬BIGINTֵԐԉ64룬̹ӔעӢµ½£¬ɧ¹�һīˤɫ100β¸�¬՚BIGINTµ½´𐌼ʏО֮ǰ£¬¿ʄܻ¹ѨҪ½«½�ꡣԃ̹ԐǤֻ̼˽`э £¬²�¸�´¼�£֢`̆ԚMyISAMɧºι¤طµģ¬ӲΪ̼׷Ҫˇһ°⌹SQLѐΪ£¬²¢²»͘±𺙓ۈκδ洢ӽȦ¡£

·         DELETE FROM tbl_name²»טтʺ³ɱ���µ«ȡ¶�µى¾³�У¬һ¸�»¸�¾³�:p>

·         TRUNCATE tbl_nameΪInnoDB¶�ʤµ½DELETE FROM tbl_name ²¢Ȓ²»ט׃AUTO_INCREMENT¼NJ�

·         SHOW TABLE STATUS²»Ŝ¸�ԚInnoDB±ȷµō³¼NJ�����´µŎ𐁭³ߴ硣ѐ¼NJ�ٓQLԅ»¯א´ׂԵĹ<ơ£

·         ՚MySQL 5.1א£¬ɧ¹�db_table_locks=1(1ˇĬɏֵ£© MySQL LOCK TABLES²ڗ�¿һ¸�ϻ򈢁½¸�¨¡£³�ySQL²㶄±¶¨£¬̼Ҳ»񶄒»¸�oDB±¶¨¡£¾ɰ涄MySQL²»»򈠉nnoDB±¶¨£¬¾ʐюª¿ʒՍ¨¹�nnodb_table_locks=0 4ѡձ¡£ɧ¹�InnoDB±¶¨±»»񶃣¬¼´ʹ±���һЩ¼Ȃ¼±»Ǥ̼˂ϱ̸¶¨£¬LOCK TABLESΪ³ɡ£ 

·         ̹Ԑ±»һ¸�񴗓еÉnnoDB̸¶¨՚¸ĊÎ񱼌ύ»󗑖¹֮ʱ±»ˍ·š£Ӳ´̔فUTOCOMMIT=1ģʽ£¬՚InnoDB±µ�ȃ»Ԑ̫¶ᓢӥµģ¬ӲΪ±»ѨȳµÉnnoDB±¶¨¿ʄܻᱻb¼´ˍ·š£

·         Ԑʱ£¬՚˂ϱµĹ��¶඄±ŜˇԐԃµġ£²»ђµأ¬MySQLאµÌOCK TABLESִѐһ¸�صÃOMMITº̕NLOCK TABLES¡£LOCK TABLESµŒ»¸�oDB±偿ӑ¾­±»¼ƻ®£¬ ¸üƻ®՚˂ϱµŖм䱻ִѐ¡£

·         Ϊ½¨b¸´׆´ӷ�ÌOAD TABLE FROM MASTERԯ¾䷔InnoDB±���ǰطԃ¡£һ¸��ۖ�򇸉ϸ�ªMyISAMµģ¬Ȼº�º՘£¬֮º󹼻»׷·����µ½InnoDBא¡£

·         ՚InnoDBאĬɏ˽¾ݿ㒳µĴ�ű6KB¡£ͨ¹��ţ¿ʒՔظKBµ½64KB֮¼倴ʨ׃֢¸�£ţ²»µò»¸�niv.iԴτ¼�NIV_PAGE_SIZEº̕NIV_PAGE_SIZE_SHIFTµŖµ¡£

·         ՚MySQL 5.1א£¬´¥·¢Ƿ²»±»¼¶jµō⽼ѐΪ¼¤»

15.2.17.1. InnoDB˽¾ݴʵ䳙طµĴ֯¶Ϻ΅ų�/span>

±���һ¸�㏊͢ˇMySQL·�Үfrmτ¼�狽¾ݴʵ呅Ϣ£¬̼±»·Ɣۊ�¿¼£¬Ȼ¶�DBҲ´洢хϢµ½±¼叄¼�ԼºµŊ�偯¡£ɧ¹�.frmτ¼�Lj¥ £»»󖟣¬ɧ¹�Ƿ՚˽¾ݴʵ䳙طµŖм䲀#£¬.frmτ¼�⋸ԫInnoDBŚ²¿˽¾ݴʵ䶄ͬ²½¡£

һ¸�¬²½µŊ�䶄֢״ˇCREATE TABLEԯ¾劧°ܡ£ɧ¹�֢זȩ¿�䓦¸ò鿴·�Ĵɕ־¡£ɧ¹�˵±¾­´畚ԚInnoDBŚ²¿˽¾ݴʵ䵱א£¬ţ՚InnoDB±¼叄¼�»¸����̼ûԐ¶Փ¦µ®frmτ¼��¢¿´ǰ4гɧЂµģº

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.

ţ¿ʒ԰´֕´ɕ־o¸�ʾӆ³�±���ɧ¹�²»Ŝ³ɹ¦µي¹ԃDROP TABLE£¬ϊ͢¿ʄ݊Ȓ󎫔ؼstrong>mysql¿ͻ§¶̀ﶄĻؖΪ³ɡ£Ҫ½⿶֢¸�⣬ԃ--disable-auto-rehashѡЮ4Ǵ¶¯mysql¿ͻ§¶˲¢ՙ´γ¢˔DROP TABLE ¡££¨ԐĻؖΪ³ɴ򿫗ţ¬mysql˔؅¹¹½¨¸��±���µ±һ¸�脨˶µŎˌⵦ՚֮ʱ£¬֢¸����²»ǰطԃ£©¡£

²»ͬ²½˽¾ݴʵ䶄­һ¸�quo;ͬӥ´Ȧrdquo;ˇMySQL´󓢒»¸�ܴ򿨮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τ¼�nnoDBŚûԐР¶Փ¦µı���ţ¿ʒՍ¨¹�¾³��µ¥µ®frmτ¼�:p>

ɧ¹�L՚һ¸�TER TABLE²ڗ�м䲀#£¬ţ¿ʒՓ‰nnoDB±¼党™ʱ¹±½⋸¡£ţ¿ʒՓ©nnodb_table_monitor¿´һ¸�����ĻΪ#sql-...¡£ɧ¹�±���Ļؖ°�pan>`(backticks)o£¬ţ¿ʒՔۃ�º¬“#”ؖ·�ʏִѐSQLԯ¾䡣Ӳ´ˣ¬ţ¿ʒՓć°˶µĵķ½·¨гӆ³�±ҹӆ³�»¸����עӢ£¬Ҫ՚Unix΢¿Ȁ︴׆»󗙃����Ļ°�ؖ·�ѨҪ°Ҏļ�ۋ«ӽºƀ

·         ɧ¹�ԃALTER TABLE 4°ЍERGE±���ΪǤ̼±э£¬µ½ µײ㲭µœ³ʤ¾ͱ»¶ªʧ‹¡£ȡ¶�µģ¬4ؔµײ⌹ISAM±���ѐ±»¸´׆µ½ӑ¸�£¬¸ñº󱼖¸¶¨т`э¡£

·         REPLACE²»ǰطԃ¡£

·         ûԐWHEREؓ¾䣬»󖠔ۈκα»ӳʤµ½һ¸�ªµÍERGE±µňκϒ»¸�ϵÒEPAIR TABLE£¬TRUNCATE TABLE, OPTIMIZE TABLE»񀍁LYZE TABLE£¬ţ²»ŜʹԃDROP TABLE, ALTER TABLE, DELETE FROM¡£ɧ¹�ôض‹£¬MERGE±���ɔ¾ʖ¸вԭʼ±���֢ҹ²�΢½ẻ¡£½⿶֢¸�䘮¼򵥵İ취ˇ՚ִѐɎºϒ»¸�©²ڗ�°·¢³��SH TABLESԯ¾倴ȷ±£ûԐMERGE±¾ɱ£³ִ򿪡£

·         һ¸�GE±���Ŝ՚ֻ¸�Ў¬³ՕNIQUEԼ˸¡£µ±ţִѐһ¸�ERT, ˽¾ݽ�һ¸�ࠗһ¸�SAM±���ȡ¾�NSERT_METHODѡЮµŖµ£©¡£MySQLȷ±£Ψһ ¼�Ǹ�SAM±±£³׎¨һ£¬µ«²»ˇ¿缯ºЀ𐌹Ԑµı���

·         µ±ţ´´½¨һ¸�GE±ʱ£¬ûԐ¼쳩ȥȷ±£µײ㲭µĴ畚Ӕ¼°ԐРͬµĻ�µ±MERGE±���ʹԃ֮ʱ£¬MySQL¼쳩ÿ¸�³ʤµı���¼Ȃ¼³¤¶ɊǷ򐠵ȣ¬µ«֢²¢²»ʮ·ֿɿ¿¡£ɧ¹�²»Р̆µÍyISAM±���½¨һ¸�GE±���ţ·dz£Ԑ¿ʄݗ²¼�µŎˌ⡣

·         ՚MERGE±µŋ�ŋ³Ѳº΋�ײ㲭אµŋ�¦¸Ē»ҹ¡£ɧ¹�ԃALTER TABLE¸�±»ԃ՚MERGE±µı¼Ԓ»¸�QUE̷ӽ£¬Ȼº􊺓LTER TABLE՚MERGE±ͭ¼Ԓ»¸�¨һ̷ӽ£¬ɧ¹�µײ㲭ʏӑ¾­Ԑһ¸�¨һ̷ӽ£¬¶Ա���̷ӽƅѲˇ²»ͬµġ££¨֢ˇӲΪALTER TABLE°ЕNIQUE̷ӽ·ƔڷȎ¨һ̷ӽ֮ǰӔ{Ԛט¸´¼�ټ쳢 £©¡£Ӳ´˶Պ¹ԃ֢ҹ̷ӽµı���²ꑯ¿ʄܷµ»ز»ǚλµĽẻ¡£

·         ՚Windowsא£¬՚һ¸�/span>MERGE±ԃµıDROP TABLE²»ǰطԃ£¬ӲΪMERGEӽȦµıʤ¶ӍySQLµĸ��󎩗indows²»Պѭӑ´򿫎ļ��؏ȱِ鋊ˢт̹ԐMERGE±���ʹԃFLUSH TABLES£©»󕛒Ƴ�®ǰӆ³�±���

-        ɧ¹�Ś²¿±���µČ«´󣬷�Զ¯°ҋ�ªһ¸�̱���³ߴ萞׆ԉtmp_table_sizeϵͳ±偿µŖµ4ȷ¶¨¡£

-        MEMORY±���²»»◪»»³ɴƅ̱���Ҫȷ±£ţ²»»ⅼ¶�ɵ˂£¬ţ¿ʒՉ闃max_heap_table_sizeϵͳ±偿¸�RY±���Ӕخ´󴟴硣¶Փڵ¥¸����ţҲ¿ʒՔكREATE TABLEԯ¾嗐ָ¶¨һ¸�_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»񘎕NGEST¡£

·         --bdb-logdir=path

BDBɕ־τ¼�£

·         --bdb-no-recover

²»՚»ָ´ģʽǴ¶¯Berkeley DB¡£

·         --bdb-no-sync

²»ͬ²½ˢтBDBɕ־¡£֢¸�±»՞³ɣ¬ȡ¶�µي¹ԃ--skip-sync-bdb-logs£¨ȫ²ϔĶҭ-sync-bdb-logsµŃ鋶)¡£

·         --bdb-shared-data

Ӕ¶ദmģʽǴ¶¯Berkeley DB¡££¨³�¯Berkeley DB֮ʱ£¬²»ҪʹԃDB_PRIVATE£©¡£

·         --bdb-tmpdir=path

BDB™ʱτ¼�£

·         --skip-bdb

½�B´洢ӽȦ¡£

·         --sync-bdb-logs

ͬ²½ˢтBDBɕ־¡£֢¸�ɏ±»Պѭ£¬ȫʹԃ--skip-sync-bdb-logs4½�¡£

·         ɧ¹�Ջѐ؅£¬ͬʱ̦؅autocommitµı»Պѭ£¨֢ˇĬɏµģ©£¬¶ӂDB±���¸ı䱻b¼´͡½»²¢Ȓ²»Ŝ±» »ع�

·         ɧ¹�Ջѐ؅£¬ͬʱ̦؅autocommitµı»½�¸ı䲻±䴉Ԁ¾õŖ±µ½ţִѐһ¸�MITԯ¾䡣طΪ͡½»µŌ浺£¬ţ¿ʒՖ´ѐROLLBACK4μ¼Ǹı䡣

ţ¿ʒՓ‚EGIN WORKԯ¾俪ʼһ¸�򀴹ӆ�ocommit£¬»󖠓“ET AUTOCOMMIT=04ķȷ½�tocommit¡£

·         BDB±ӔԐ¶൯ÿ±뱱¸����¬²¢Ȓ1024ؖ½ڵŗ ¼�£

·         MySQL՚ÿ¸�±ѨҪһ¸�MARY KEYӔ±䃿һѐ¿ʒԱ»Ψһµي¶±𡤈纻ţ²»ķȷ´´½¨һ¸�ySQLΪţ´´½¨²¢ά³ג»¸�صÐRIMARY KEY¡£Ӿ²ص ¼��½ڵij¤¶ȣ¬²¢ȒΪÿ¸�분dzͼ¶�¼ӡ£֢¸�»³�ٓHOW CREATE TABLE»񃄓CRIBEµŊ䴶֮א¡£

·         PRIMARY KEY±Ɉκφ匼̷ӽ¶¼Ҫ¿죬ӲΪPRIMARY KEY±»ԫѐµŊ�𵦴¢¡£Ǥ̷̼ӽ±»´洢Ϊ¼�«PRIMARY KEY£¬̹Ӕ±£³ՐRIMARY KEY¾¡¿ʄܵض͒Խ۔¼´ƅ̿ռ䲢»񶃸�ɊȖْªµġ£

֢¸�ª`̆ԚInnoDBµģ¬՚Ǥא½϶̵ðrimary keys²»½���ڶ�۔¼¿ռ⠡£

·         ɧ¹�B±£¬ţ·Ďʵŋ�ˇͬһ̷ӽµŒ»²¿·ֻ󗷼�¿·֣¬MySQL¿ʒՖ´ѐ²ꑯ¶�ϊȷʵµŐС£՚һ¸�SAM±£¬ֻԐ ˇͬһ̷ӽµŒ»²¿·ז®ʱ£¬²ſʒՕヴض¡£

·         lѸɨĨ±ȶӍyISAM±���ɨĨ¸�󎫔قDB±µŊ�洢՚B˷¶�՚·׀분˽¾ގļ�

·        ¼�n>ֵгMyISAM±µ ¼��ǰ׺»򻴗ºѹ̵µġ£»»¾仰˵£¬՚BDB±¼�»±ɔٍyISAM±ʔ΢¶ᕼ¾ޒ»µ䀕¼䡣

·         ՚BDB±¾­³£Ԑ¶´Պѭţ՚̷ӽ˷µŖм䳥ɫтѐ¡£֢¸�‚DB±���MyISAM±΢´�©

·         SELECT COUNT(*) FROM tbl_name¶ӂDB±���ý£¬ӲΪ՚¸ñûԐѐ¼NJ�֡£

·         ԅ»¯ǷѨҪ֪µ1µĴ󹆐ъ�QLͨ¹�扫Ӕ¼°՚ÿ¸�±µĵ¥¶F¬¶Єێ¬³׋��ˌ⡣ɧ¹�·¢³�¿µÄELETE»񑎌LBACKԯ¾䣬֢¸�ӍySQLԅ»¯Ƿ4˵ˇأ¹»¾«ȷ‹¡£¿ʊǣ¬MySQL½�رյŊ±º򳅴洢֢¸�¬̹Ӕ£¬ɧ¹�Ƿ±»Ӣ΢µٖ֖¹£¬֢¸�ʄ݊Dz»ֽȷµġ£¼´ʹ˽²»ˇ100%ֽȷ£¬̼²»ˇ ķȷµġ£ţ¿ʒՊ¹ԃANALYZE TABLE»󖝠OPTIMIZE TABLEԯ¾倴¸�NJ�ϔ¼a href="http://book.chinaz.com/mysql5/sql-syntax.html#analyze-table" title="13.5.2.1. ANALYZE TABLE Syntax">13.5.2.1½ڣ¬¡°ANALYZE TABLEԯ·¨” º˼a href="http://book.chinaz.com/mysql5/sql-syntax.html#optimize-table" title="13.5.2.5. OPTIMIZE TABLE Syntax">13.5.2.5½ڣ¬¡°OPTIMIZE TABLEԯ·¨”¡£

·         BDB±µńڲ¿̸¶¨՚ҳĦ¼¶±񊐗�

·         LOCK TABLES՚BDB±¾Ώ앚Ǥ̼±һҹ¹¤ط¡£ɧ¹�ʹԃLOCK TABLES£¬MySQL¶Ըñ���³��¿¶ᗘд̸¶¨ £¨һ¸�閹Ǥ̼ط֟µŋ�4ȷ±£¼´ʹ­һ¸�̷¢³���¸ñ±»ǡµ±µً�¡£

·         ҪŜ¹»»ع��񣫂DB´洢ӽȦά»¤ɕ־τ¼�µЖخ¸ࠐՄܣ¬ţ¿ʒՊ¹ԃ--bdb-logdirѡЮ4°ЂDBɕ־·Ɣڲ»ͬµĴƅ͉ϣ¬¶�·Ɣۊ�»·ƖõńǸ�̡£

·         ÿ´ϐBDBɕ־τ¼�¯֮ʱ£¬MySQLִѐһ¸�鶣£¬²¢Ȓɾµ�°˂ϱ²»ѨҪµňκ͂DBɕ־τ¼�²¿ʒՔۈκϊ±º󊺓†LUSH LOGS4¸�eley DB±׃¼쳩µ㡣

¶Քׄѻָ´£¬ţӦ¸Ċ¹ԃ±���·ݼҍySQLµĶ�֖¾£¬ȫ²ϔ¼a href="http://book.chinaz.com/mysql5/database-administration.html#backup" title="5.9.1. Database Backups">5.9.1½ڣ¬¡°˽¾ݿⱸ·ۦrdquo;¡£

¾¯¸棺ɧ¹�³�¹ԃאµľʈ֖¾τ¼�¸�Ŝض»ָ´£¬²¢Ȓɧ¹�˂²»¶ԣ¬ţ¿ʄܻᶪʧ˽¾ݡ£

·         Ӧԃ³͐򲙐옜ˇ±»׼±¸4´¦mȩ¿�´BDB±ºεĸı倉Ŝµ¼ׂһ¸�¯»ع�ӈκζʄܻᵸ؅һ¸��¶�¡£

·         ɧ¹�BDB±Զµ½´ƅ͂�µõ½һ¸�󣨿ʄ݊Ǵ28£©£¬²¢Ȓ˂ϱӦ¸ûع�㔫MyISAM±·´£¬¶ՓٍyISAM ±���mysqld՚¼͐�µȴ�ŗՓɴƅ̿ռ䡣

·         ͬʱ´򿫐���BDB±Ŝˇ·dz£ýµġ£ɧ¹�ʹԃBDB±���ţ²»Ӧʹԃ·dz£´󶄱���´棨}ɧ£¬´�56£©²¢Ȓµ±ţʹԃmysql¿ͻ§¶̖®ʱ£¬ţӦ¸Ċ¹ԃ--no-auto-rehashѡЮ¡£

·         SHOW TABLE STATUS ²»ΪBDB±¹©һЩхϢ£º

·                mysql> SHOW TABLE STATUS LIKE 'bdbtest'\G

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

·                           Name: bdbtest

·                         Engine: BerkeleyDB

·                        Version: 10

·                     Row_format: Dynamic

·                           Rows: 154