· KLDP.org · KLDP.net · KLDP Wiki · KLDP BBS ·
MySQL½ºÅ丮Áö¿£Áø

MySQL Storage Engine

¼ÛÀº¿µ(f405@sds.co.kr)-ÀÛ¼º,¹æâÇö(winchild@sds.co.kr)-Á¤¸®¹×µî·Ï / (ÁÖ)»ïÁ¤µ¥ÀÌÅͼ­ºñ½º ¿¬±¸¼Ò


ÃÖÁ¾°»½ÅÀÏ: 2006³â2¿ù4ÀÏ 11½Ã00ºÐ

MyISAM

DB´Â µð·ºÅ丮º°·Î »ý¼º/°ü¸®µÈ´Ù. Å×À̺íÀº µð·ºÅ丮 ³» ÆÄÀÏ·Î »ý¼º/°ü¸®µÇ°í, ÇϳªÀÇ Å×À̺í´ç 3°³ÀÇ µ¥ÀÌÅÍÆÄÀÏ·Î ±¸¼ºµÈ´Ù.

dbakorea.frm : Å×ÀÌºí±¸Á¶Á¤º¸(½ºÅ°¸¶Á¤º¸)
dbakorea.myd : ¼ø¼ö µ¥ÀÌÅÍ
dbakorea.myi : À妽º Á¤º¸

Å×À̺íÀ» ±¸¼ºÇÏ´Â row´Â ´ÙÀ½°ú °°ÀÌ 3°¡Áö Çü½ÄÀ¸·Î ºÐ·ùÇÒ ¼ö ÀÖ´Ù.


  1. °íÁ¤ Æ÷¸Ë(fixed row format) Ä÷³ ŸÀÔÀ¸·Î varchar, text, blobÀ» »ç¿ëÇÏÁö ¾ÊÀ» ¶§. °¡´ÉÇÏ´Ù¸é °¡±ÞÀû °íÁ¤Æ÷¸ËÀ» »ç¿ëÇÏ´Â °ÍÀÌ ÁÁ´Ù. µ¿ÀûÆ÷¸Ëº¸´Ù ¸Þ¸ð¸®»ç¿ëÀÌ Àû°í, À妽ºÆÄÀÏÅ©±âµµ ÀÛ¾ÆÁø´Ù. ´ç¿¬È÷ ¼Óµµ ¶ÇÇÑ Çâ»óµÈ´Ù. ÆÄÀϱ¸Á¶ÀÇ °íÁ¤±æÀÌ·¹Äڵ尡 °íÁ¤Æ÷¸Ë, °¡º¯±æÀÌ·¹Äڵ尡 µ¿ÀûÆ÷¸ËÀ̶ó°í »ý°¢ÇØÁÖ¸é ÀÌÇØÇϱ⠽¬¿ï °ÍÀÌ´Ù.

  2. µ¿Àû Æ÷¸Ë(dynamic row format) Ä÷³ ŸÀÔÀ¸·Î varchar, text, blobÀ» »ç¿ëÇÒ ¶§.

    ÁÖÀÇ> varchar(3)º¸´Ù ÀÛ´Ù¸é °íÁ¤Æ÷¸ËÀÌ »ç¿ëµÊ °íÁ¤Æ÷¸Ë¿¡ ºñÇØ µð½ºÅ©»ç¿ë¿¡ ÀÖ¾î È¿À²¼ºÀ» °¡Áö³ª, ¼Óµµ´Â »ó´ëÀûÀ¸·Î ´À¸®´Ù. Å×ÀÌºí¿¡ ºó¹øÇÑ ·¹ÄÚµåÀÇ ¼öÁ¤/»èÁ¦°¡ ÀÌ·ç¾îÁö¸é ´ÜÆíÈ­°¡ À¯¹ßÇϹǷΠÁÖ±âÀûÀÎ optimize tableÀÌ ¿ä±¸µÈ´Ù. text, blobÀº º°µµ·Î ÀúÀåµÇ¹Ç·Î, optimize tableÀ» ¼öÇàÇÒ Çʿ伺ÀÌ ¾ø´Ù.

  3. ¾ÐÃà Æ÷¸Ë(compressed row format) ÀбâÀü¿ëÀÌ´Ù. myisampack ¸í·É¾î·Î ¸¸µé ¼ö ÀÖ´Ù. µð½ºÅ©°ø°£À» Àû°Ô Â÷ÁöÇϹǷΠCD¿¡ ¹é¾÷ÇÒ ¶§ »ç¿ëÇÏ¸é µÇ°Ú´Ù.


MyISAMÀº µ¿½Ã¼ºÁ¦¾î¸¦ À§ÇØ Å×À̺í´ÜÀ§ ¶ôÅ·(table-level locking)À» »ç¿ëÇÑ´Ù. Âü°í·Î, ´ëºÎºÐÀÇ »ó¿ë DBMSµéÀº Çà ´ÜÀ§ ¶ôÅ·(row-level locking)À» »ç¿ëÇÑ´Ù. Çà ´ÜÀ§ ¶ôÅ·ÀÌ ´õ ¼¼¹ÐÇÏ°í Á¤¹ÐÇÑ Á¦¾î°¡ °¡´ÉÇÑ ¹Ý¸é, Å×À̺í´ÜÀ§ ¶ôÅ·Àº ´Ü¼øÇÏ´Ù.

´ÙÀ½Àº »ç¿ëµÇ´Â 3°¡Áö ¶ô

  1. READ LOCAL lock query¹®(select)¿¡¼­¸¸ »ç¿ëµÊ. °»½ÅÀÛ¾÷µéÀ» ºí·°. ´Ù¸¥ query¹®µéÀº ºí·Ï ¾È µÊ. insert¹®¿¡¼­ .mydÆÄÀÏÀÇ ³¡¿¡ µ¥ÀÌÅ͸¦ Ãß°¡ÇÏ´Â °æ¿ì¿¡´Â ºí·°µÇÁö ¾ÊÀ½.

  2. READ, or shared locks ¸ðµç °»½ÅÀÛ¾÷µé(insert´Â ¸ðµÎ Àû¿ëµÊ)ÀÌ ºí·°µÊ. myisamcheck´Â ÀÌ ¶ôÀ» »ç¿ë.

  3. WRITE, or exclusive locks insert(¸î¸î Á¾·ù¸¸), update, delete½Ã »ç¿ëµÊ. ´Ù¸¥ ¸ðµç ÀбâÀÛ¾÷/¾²±âÀÛ¾÷ÀÌ ºí·°µÊ.

À妽º : key buffer¿¡ ij½ÌµÇ¾î ¸ðµç ³ðµé(MySQL ½º·¹µåµé)ÀÌ °øÀ¯ µ¥ÀÌÅÍ : OSÀÇ Ä³½Ì¿¡ ÀÇÁ¸.

ÁÖÀÇ> ij½Ì¿¡ ´ëÇØ InnoDB°¡ À妽º/µ¥ÀÌÅÍ Ä³½Ì ¸ðµÎ¸¦ °ü¸®ÇÏ´Â °Í¿¡ ºñÇØ(MySQL¼­¹ö°¡ °ü¸®ÇÑ´Ù´Â ÀǹÌ) MyISAMÀº, À妽º¸¸ MySQL¼­¹ö°¡ °ü¸®ÇÏ°í, µ¥ÀÌÅÍ´Â °ü¸®ÇÏÁö ¾Ê´Â´Ù. (µ¥ÀÌÅÍ´Â OS ij½Ì¿¡ ÀÇÁ¸ÇÑ´Ù´Â ÀǹÌ) InnoDB´Â innodb_buffer_pool_size, MyISAMÀº key_buffer_sizeº¯¼ö¸¦ »ç¿ëÇÑ´Ù. º¯¼ö°ªº¸±â: show variables º¯¼ö°ª¼³Á¤: my.ini(À©µµ¿ì) Unix°è¿­Àº (/etc/my.cnf)¿¡ set-variable=key_buffer=16M ÀÌ·± ½ÄÀ¸·Î ¼³Á¤

ÀÚÁÖ »ç¿ëµÇ´Â Å×À̺íµéÀÇ .myiÆÄÀÏÅ©±â¸¦ ÇÕÇÏ¸é ´ë·«ÀûÀº À妽º ij½ÌÅ©±â¸¦ ±¸ÇÒ ¼ö ÀÖ´Ù.

3°¡Áö À妽º »ç¿ë°¡´É: btree, rtree(Áö¸®ÇÐ µ¥ÀÌÅÍ), fulltext (Æ®·£Àè¼Ç Áö¿ø ¾È µÊ) mysqldump(SQL¹®À¸·Î »ý¼º), mysqlhotcopy(ÀÌÁøÇü½ÄÀ¸·Î »ý¼º)·Î ¹é¾÷°¡´É

  1. MyISAM MERGE Å×À̺íµéÀ» unionÀ¸·Î ¹­Àº ÀÏÁ¾ÀÇ ºä. ½ÇÁ¦ µ¥ÀÌÅÍ´Â ±â¹ÝÅ×À̺íµé¿¡ ÀÖÀ½. º¸Åë È÷½ºÅ丮 µ¥ÀÌÅͳª ·Î±×¸¦ °¡Áö´Â Å×À̺íµé¿¡¼­ »ç¿ëµÊ. ¿À¶óŬÀÇ ÆÄƼ¼Å´×°ú ±× °³³äÀÌ À¯»ç.

  2. InnoDB ACID Æ®·£Àè¼Ç, multi-versioning, row-level locking, foreign keyÁ¦¾àÁ¶°Ç Áö¿øµÊ. Å©·¡½¬ ÈÄ ÀÚµ¿º¹±¸ Áö¿ø µ¥ÀÌÅÍ¿Í À妽º°¡ ¸ðµÎ ÀúÀåµÇ´Â Å×ÀÌºí½ºÆäÀ̽º °³³äÀÌ »ç¿ëµÊ. ¿À¶óŬÀÇ Å×ÀÌºí½ºÆäÀ̽º¿Í °°ÀÌ ¿©·¯ °³ÀÇ ÆÄÀϵé·Î ±¸¼ºµÉ ¼ö ÀÖ´Ù. select´Â ¶ôÅ·ÀÌ ÇÊ¿äÄ¡ ¾ÊÀ¸¸ç, °»½ÅÀÛ¾÷µéÀº Çà ´ÜÀ§ ¶ôÅ·À» »ç¿ë. ³ôÀº µ¿½Ã¼ºÀ» Á¦°øÇÏÁö¸¸, MyISAM¿¡ ºñÇØ 3¹èÁ¤µµÀÇ µð½ºÅ© »ç¿ë·®À» ¿ä±¸ÇÔ. ÃÖÀûÀÇ ¼º´ÉÀ» À§ÇØ ¸¹Àº ¸Þ¸ð¸®°¡ InnoDB buffer pool¿¡ ÇÒ´çµÇ¾î¾ß ÇÔ. Ŭ·¯½ºÅÍµÈ ÇÁ¶óÀ̸Ӹ®Å°·Î btreeÀ妽º »ç¿ë commitµÈ Æ®·£Àè¼ÇÀº redo log¿¡ ±â·ÏµÇ°í, ÀÌ´Â ÀûÁ¤ÇÑ ½Ã°£¿¡ Å×ÀÌºí½ºÆäÀ̽º¿¡ ±â·ÏµÈ´Ù. mysqldump·Î ¹é¾÷°¡´É. ¾Æ·¡ ÁúÀǹ®À¸·Î InnoDB»ç¿ë°¡´É ¿©ºÎ¸¦ ¾Ë ¼ö ÀÖ´Ù.

     show variables like 'have_innodb'
     


    Âü°í> show variables´Â ¼­¹ö º¯¼ö°ªÀ» ÆÄ¾Ç ½Ã »ç¿ëÇÑ´Ù. ¿À¶óŬ¿¡¼­ÀÇ show parameter¿Í À¯»çÇÏ´Ù. ƯÁ¤ º¯¼ö°ª¸¸À» ¾Ë¾Æ³¾ ¶§´Â like¸¦ »ç¿ëÇØ ÇØ´ç º¯¼ö¸¸À» ÁöÁ¤ÇÏ¸é µÈ´Ù. SQL¹®ÀÇ like¹®¹ýÀÌ Àû¿ëµÇ¹Ç·Î %, _¸¦ »ç¿ëÇÏ¿© ƯÁ¤¿µ¿ªÀÇ º¯¼öµé¸¸À» ÁöÁ¤ÇÒ ¼öµµ ÀÖ´Ù. Âü°í·Î ¿À¶óŬÀÇ °æ¿ì like¸¦ ÁöÁ¤ÇÏÁö ¾Ê°í ±×³É Æз¯¹ÌÅ͸¦ ÁöÁ¤ÇÒ ¼ö ÀÖ´Ù.

  3.  mysql> show variables like 'have%';
     +-------------------------+---------+
     | Variable_name     | Value |
     +-------------------------+--------+
     | have_bdb            | NO    |
     | have_crypt          | NO    |
     | have_compress    | YES |
     | have_innodb      | YES |
     | have_isam           | NO   |
     | have_raid            | NO   |
     | have_symlink       | YES |
     | have_openssl      | NO   |
     | have_query_cache | YES |
     +-------------------------+--------+
     9 rows in set (0.00 sec)
    
     SQL> show parameter timed
    
     NAME                                 TYPE        VALUE
     ------------------------------------ ----------- ------------------------------
     timed_os_statistics                  integer     0
     timed_statistics                     boolean     TRUE
     SQL>
    
     


  4. MEMORY(HEAP) ¸ðµç µ¥ÀÌÅ͵éÀº ¸Þ¸ð¸®¿¡ ÀúÀåµÊ. ´Ù¸¥ Å×À̺íŸÀԵ鿡 ºñÇØ ¼Óµµ°¡ ¿ùµîÈ÷ ºü¸£Áö¸¸, ¼­¹ö°¡ ¼Ë´Ù¿î½Ã µ¥ÀÌÅÍ´Â ¸ðµÎ ¼Ò½ÇµÈ´Ù. µ¿µîÁ¶°Ç( ex) where a=10) °Ë»ö¿¡ HASH±â¹Ý °Ë»öÀ» Á¦°øÇÑ´Ù. ÀÌ´Â ¹üÀ§ °Ë»ö½Ã À妽º°¡ »ç¿ëµÇÁö ¸øÇÑ´Ù´Â °ÍÀ» ÀǹÌÇÑ´Ù. varchar, blob, text Ä÷³ »ç¿ë ¸ø ÇÔ. 4.1¹öÀüºÎÅÍ´Â Æ®¸®±â¹Ý À妽ºµµ »ç¿ë °¡´ÉÇÔ.

MySQL Cluster

NDB·Î ĪÇÑ´Ù. (Network DataBase) º°µµÀÇ ¹®¼­ÀÎ "MySQL Ŭ·¯½ºÅÍ" ¿¡¼­ ¾ð±Þ.

»ç¿ëÇϱâ


  1. MyISAM Á¤ÀûÀÎ Å×À̺í, ·Î±× Å×ÀÌºí ¾²±âÀÛ¾÷ÀÌ º°·Î ¾ø´Â select À§ÁÖÀÇ Å×À̺í. current insert±â´ÉÀÌ read½Ã¿¡ insert°¡ °¡´ÉÇÏ°Ô ÇϹǷΠ·Î±× Å×ÀÌºí¿¡ »ç¿ëµÉ ¼ö ÀÖ´Ù.

  2. InnoDB ¹Î°¨ÇÑ Á¤º¸¸¦ °®´Â Å×À̺í(ȸ¿øÅ×À̺í, µ·¿¡ °ü·ÃµÈ Å×À̺í) °»½Å(Àбâ/¾²±â) À§ÁÖÀÇ Æ®·£Àè¼ÇÀÌ ¿ä±¸µÇ´Â Å×À̺í. À妽º°¡ ¸¹ÀÌ °É¸° ´ë·®ÀÇ Å×À̺íÀº ÀÌ ³ðÀ» »ç¿ëÇÏ´Â °ÍÀÌ ÁÁ´Ù.

  3. Heap ÀϽÃÀûÀ¸·Î¸¸ »ç¿ëµÇ´Â Àӽà Å×À̺í.

MySQLÀÌ À¥¿¡¼­ ÀÚÁÖ »ç¿ëµÇ¹Ç·Î ÀÌ¿Í °ü·ÃÇؼ­ ¼³¸íÇϸé, ·Î±ëÀ̳ª °Ë»ö¿¡¼­´Â MyISAMÀ», µî·ÏÁ¤º¸³ª ¹è³Ê½Ã½ºÅÛ¿¡¼­´Â InnoDB¸¦, ÀÓ½ÃÅ×À̺í, ´º½ºÀÇ Çìµå¶óÀÎ, ·Îµå°¡ ¸¹Àº ÆäÀÌÁöÀÇ µ¥ÀÌÅÍ¿¡ ´ëÇؼ± heapÀ» ±ÇÀå »ç¿ëÇÑ´Ù.



ID
Password
Join
Beauty and harmony are as necessary to you as the very breath of life.


sponsored by andamiro
sponsored by cdnetworks
sponsored by HP

Valid XHTML 1.0! Valid CSS! powered by MoniWiki
last modified 2006-03-13 10:34:50
Processing time 0.0066 sec