MysqlË÷ÒýÏêϸ½éÉÜ

ʱ¼ä£º2016.04.18 ·¢²¼ÈË£ºÄãºÃ1996520

MysqlË÷ÒýÏêϸ½éÉÜ

Òѽâ¾öÎÊÌâ

¹È¸èÄãºÃ1996520Óû§ÔÚ2016.04.18Ìá½»Á˹ØÓÚ¡°Ê¹Í½ÐÐÕßMysqlË÷ÒýÏêϸ½éÉÜ¡±µÄÌáÎÊ£¬»¶Ó­´ó¼ÒÓ¿Ô¾·¢±í×Ô¼ºµÄ¹Ûµã¡£Ä¿Ç°¹²ÓÐ1¸ö»Ø´ð£¬×îºó¸üÐÂÓÚ2025-02-25T09:55:35¡£Ï£Íû´ó¼ÒÄܹ»°ïÖúËý¡£

ÏêϸÎÊÌâÃèÊö¼°ÒÉÎÊ£ºÆÚ´ýÄúµÄ´ð°¸£¬ÄãÎÞÒìÓÚÑ©ÖÐËÍÌ¿,ÈÃÎҸм¤ÌéÁã £¡

Ï£ÍûÒÔϵĻشð£¬Äܹ»°ïÖúÄã¡£

µÚ1¸ö»Ø´ð

Óû§Ãû£ºÇàɬµÄ´Ð°è¶¹¸¯  

MysqlË÷Òý¸ÅÊö

ËùÓÐMySQLÁÐÀàÐÍ¿ÉÒÔ±»Ë÷Òý¡£¶ÔÏà¹ØÁÐʹÓÃË÷ÒýÊÇÌá¸ß**LECT²Ù×÷ÐÔÄܵÄ×î¼Ñ;¾¶¡£¸ù¾Ý´æ´¢ÒýÇæ¶¨Òåÿ¸ö±íµÄ×î´óË÷ÒýÊýºÍ×î´óË÷Òý³¤¶È¡£ËùÓд洢ÒýÇæÖ§³Öÿ¸ö±íÖÁÉÙ16¸öË÷Òý£¬×ÜË÷Òý³¤¶ÈÖÁÉÙΪ256×Ö½Ú¡£´ó¶àÊý´æ´¢ÒýÇæÓиü¸ßµÄÏÞÖÆ¡£

ÔÚMySQL5À´×Ô.1ÖУ¬¶ÔÓÚMyISAMºÍInnoDB±í£¬Ç°×º¿ÉÒÔ´ïµ½1000×Ö½Ú³¤¡£Çë×¢Òâǰ׺µÄÏÞÖÆÓ¦ÒÔ×Ö½ÚΪµ¥Î»½øÐвâÁ¿£¬¶øCREATETABLEÓï¾äÖеÄǰ׺³¤¶È½âÊÍΪ×Ö·ûÊý¡£µ±ÎªÊ¹Óöà×Ö½Ú×Ö·û¼¯µÄÁÐÖ¸¶¨Ç°×º³¤¶ÈÊ»¶¨Òª¼ÓÒÔ¿¼ÂÇ¡£

»¹¿ÉÒÔ´´½¨FULLTEXTË÷Òý¡£¸ÃË÷Òý¿ÉÒÔÓÃÓÚÈ«ÎÄËÑË÷¡£Ö»ÎÊ´ðÓÐMyISAM´æ´¢ÒýÇæÖ§³ÖFULLTEXTË÷Òý£¬²¢ÇÒֻΪCh**AR¡¢VARCh**ARºÍTEXTÁС£Ë÷Òý×ÜÊǶÔÕû¸öÁнøÐУ¬²»Ö§³Ö¾Ö²¿(ǰ׺)Ë÷Òý¡£Ò²¿ÉÒÔΪ¿Õ¼äÁÐÀàÐÍ´´½¨Ë÷Òý¡£Ö»ÓÐMyISAM´æ´¢ÒýÇæÖ§³Ö¿Õ¼äÀàÐÍ¡£¿Õ¼äË÷ÒýʹÓÃR-Ê÷¡£Ä¬ÈÏÇé¿öMEMORY(h**EAP)»¤´æ´¢ÒýÇæÊ¹ÓÃhashË÷Òý£¬µ«Ò²Ö§³ÖB-Ê÷Ë÷Òý¡£

Éè¼ÆË÷ÒýµÄÔ­Ôò

1)ËÑË÷µÄË÷ÒýÁУ¬²»Ò»¶¨ÊÇËùҪѡÔñµÄÁС£

»»¾ä»°Ëµ£¬×îÊʺÏË÷ÒýµÄÁÐÊdzöÏÖÔÚWh**ERE×Ó¾äÖеÄÁУ¬»òÁ¬½Ó×Ó¾äÖÐÖ¸¶¨µÄÁУ¬¶ø²»ÊdzöÏÖÔÚ**LECT¹Ø¼ü×ÖºóµÄÑ¡ÔñÁбíÖеÄÇë´ÅÅú¿Ú´ÓµÄ¿ÌÒÑÁС£

2)ʹÓÃΩһË÷Òý¡£

¿¼ÂÇijÁÐÖÐÖµµÄ·Ö²¼¡£¶ÔÓÚΩһֵµÄÁУ¬Ë÷ÒýµÄЧ¹û×îºÃ£¬¶ø¾ßÓжà¸öÖØ¸´ÖµµÄÁУ¬ÆäË÷ÒýЧ¹û×î²î¡£ÀýÈ磬´æ·ÅÄêÁäµÄÁоßÓв»Í¬Öµ£¬ºÜÈÝÒ×Çø·Ö¸÷ÐС£¶øÓÃÀ´¼Ç¼ÐÔ±ðµÄÁУ¬Ö»º¬ÓС°M¡±ºÍ¡°F¡±£¬Ôò¶Ô´ËÁнøÐÐË÷ÒýûÓжà´óÓô¦£¨²»¹ÜËÑË÷ÄĸöÖµ£¬¶¼»áµÃ³ö´óÔ¼Ò»°ëµÄÐУ©¡£

3)ʹÓöÌË÷Òý¡£

Èç¹û»î·¨ºÎ¿ÉÊÖҽɱ·ÉËïÕÆ±³¶Ô´®ÁнøÐÐË÷Òý£¬Ó¦¸ÃÖ¸¶¨Ò»¸öǰ׺³¤¶È£¬Ö»ÒªÓпÉÄܾÍÓ¦¸ÃÕâÑù×ö¡£ÀýÈ磬Èç¹ûÓÐÒ»¸öCh**AR(200)ÁУ¬Èç¹ûÔÚǰ10¸ö»ò20¸ö×Ö·ûÄÚ£¬¶àÊýֵ֪ǰ¸úÓ¡ÊÇΩһµÄ£¬ÄÇô¾Í²»Òª¶ÔÕû¸öÁнøÐÐË÷Òý¡£¶Ôǰ10¸ö»ò20¸ö×Ö·û½øÐÐË÷ÒýÄܹ»½ÚÊ¡´óÁ¿Ë÷Òý¿Õ¼ä£¬Ò²¿ÉÄÜ»áÊ**éѯ¸ü¿ì¡£½ÏСµÄË÷ÒýÉæ¼°µÄ´ÅÅÌI/O½ÏÉÙ£¬½Ï¶ÌµÄÖµ±È½ÏÆðÀ´¸ü¿ì¡£¸üÎªÖØÒªµÄÊÇ£¬¶ÔÓڽ϶̵ļüÖµ£¬Ë÷Òý¸ßËÙ»º´æÖеĿéÄÜÈÝÄɸü¶àµÄ¼üÖµ£¬Òò´Ë£¬MySQLÒ²¿ÉÒÔÔÚÄÚ´æÖÐÈÝÄɸü¶àµÄÖµ¡£ÕâÔö¼ÓÁËÕÒµ½Ðжø²»ÓöÁÈ¡Ë÷ÒýÖн϶à¿éµÄ¿ÉÄÜÐÔ¡££¨µ±½ø»òÊаÙÊÓ³ªÇÒÊܼÇǧȻ£¬Ó¦¸ÃÀûÓÃһЩ³£Ê¶¡£Èç½öÓÃÁÐÖµµÄµÚÒ»¸ö×Ö·û½øÐÐË÷ÒýÊDz»¿ÉÄÜÓжà»Ó×ó¸ÕÖ°ËõÊ®Íþ´óºÃ´¦µÄ£¬ÒòΪÕâ¸öË÷ÒýÖв»»áÓÐÐí¶à²»Í¬µÄÖµ¡££©

4)ÀûÓÃ×î×óǰ׺¡£

ÔÚ´´½¨Ò»¸önÁеÄË÷ÊÖºó¿ÍÖ²¾à΢ÈÜÅ£ÊéÉçÒýʱ£¬Êµ¼ÊÊÇ´´½¨ÁËMySQL¿ÉÀûÓõÄn¸öË÷Òý¡£¶àÁÐË÷Òý¿ÉÆð¼¸¸öË÷ÒýµÄ×÷Óóַ­Æ¬½ÚÊ×´ýµ¥Èõ»­£¬ÒòΪ¿ÉÀûÓÃË÷ÒýÖÐ×î×ó±ßµÄÁм¯À´Æ¥ÅäÐС£ÕâÑùµÄÁм¯³ÆÎª×î×óǰ׺¡££¨ÕâÓëË÷ÒýÒ»¸öÁеÄǰ׺²»Í¬£¬Ë÷ÒýÒ»¸öÁеÄǰ׺ÊÇÀûÓøõÄǰn¸ö×Ö·û×÷ΪË÷ÒýÖµ¡££©

5)²»Òª¹ý¶ÈË÷Òý¡£

²»ÒªÒÔΪË÷Òý¡°Ô½¶àÔ½ºÃ¡±£¬Ê²Ã´¶«Î÷¶¼ÓÃË÷ÒýÊÇ´íµÄ¡£Ã¿¸ö¶îÍâµÄË÷Òý¶¼ÒªÕ¼ÓöîÍâµÄ´ÅÅ̿ռ䣬²¢½µµÍд²Ù×÷µÄÐÔÄÜ£¬ÕâÒ»µãÎÒÃÇÇ°ÃæÒѾ­½éÉܹý¡£ÔÚÐ޸ıíµÄÄÚÈÝʱ£¬Ãñ´ëѹÈÕ·ÉË÷Òý±ØÐë½øÐиüУ¬ÓÐʱ¿ÉÄÜÐèÒªÖØ¹¹£¬Òò´Ë£¬Ë÷ÒýÔ½¶à£¬Ëù»¨µÄʱ¼äÔ½³¤¡£Èç¹ûÓÐÒ»¸öË÷ÀëËäÔÙÐÞ±ã²ßÃÇÌïÓ³ÌÖ²ìÒýºÜÉÙÀûÓûò´Ó²»Ê¹Óã¬ÄÇô»á²»±ØÒªµØ¼õ»º±íµÄÐÞ¸ÄËÙ¶È¡£´ËÍ⣬MySQLÔÚÉú³ÉÒ»¸öÖ´Ðмƻ®Ê±£¬Òª¿¼ÂǸ÷¸öË÷Òý£¬ÕâÒ²Òª·Ñʱ¼ä¡£´´½¨¶àÓàµÄË÷Òý¸ø²éѯÓÅ»¯´øÀ´Á˸ü¶àµÄ¹¤×÷¡£Ë÷Òý¶ÓÇëÌõËïÖúÇåºìÌ«¶à£¬Ò²¿ÉÄÜ»áʹMySQLÑ¡Ôñ²»µ½ËùҪʹÓõÄ×îÁ£ÕûÀ§ÎÄÌõ¸ÕÎÀºÃË÷Òý¡£Ö»±£³ÖËùÐèµÄË÷ÒýÓÐÀûÓÚ¼äºÁÑÏÔìËû²éѯÓÅ»¯¡£Èç¹ûÏë¸øÒÑË÷ÒýµÄ±íÔö¼ÓË÷Òý£¬Ó¦¸Ã¿¼ÂÇËùÒªÔö¼ÓµÄË÷ÒýÊÇ·ñÊÇÏÖÓжàÁÐË÷ÒýµÄ×î×óË÷Òý¡£Èç¹ûÊÇ£¬Ôò¾Í²»Òª·ÑÁ¦È¥Ôö¼ÓÕâ¸öË÷ÒýÁË£¬ÒòΪÒѾ­ÓÐÁË¡£

¿ÎÔ˼û¼×ϸ¹ØÐÐÌì¾Ö6)¿¼ÂÇÔÚÁÐÉϽøÐеıȽÏÀàÐ͹ʡ£

Ë÷Òý¿ÉÓÃÓÚ¡°<¡±¡¢¡°<=Çᡱ¡¢¡°=¡±¡¢¡°>=¡±¡¢¡°>¡±ºÍBETWEENÔËËã¡£ÔÚģʽ¾ßÓÐÒ»¸öÖ±½ÓÁ¿Ç°×ºÊ±£¬Ë÷ÒýÒ²ÓÃÓÚLIKEÔËËã¡£Èç¹ûÖ»½«Ä³¸öÁÐÓÃÓÚÆäËûÀàÐ͵ÄÔËËãʱ£¨ÈçSTRCMP()£©£¬¶ÔÆä½øÐÐË÷ÒýûÓмÛÖµ¡£

btreeË÷ÒýÓëhashË÷Òý

¶ÔÓÚ**REEºÍh**ASh**Ë÷Òý£¬µ±Ê¹ÓÃ=¡¢<=>¡¢IN¡¢IS**LL»òÕßISNOT**LL²Ù×÷·ûʱ£¬¹Ø¼üÔªËØÓë³£Á¿ÖµµÄ±È½Ï¹ØÏÔÓ¦Ò»¸ö·¶Î§Ìõ¼þ¡£h**ashË÷Òý»¹ÓÐһЩÆäËüÌØÕ÷£ºËüÃÇÖ»ÓÃÓÚʹÓÃ=»ò<=>²Ù×÷·ûµÄµÈʽ±È½Ï(µ«ºÜ¿ì)¡£ÓÅ»¯Æ÷²»ÄÜʹÓÃhashË÷ÒýÀ´¼ÓËÙORDERBY²Ù×÷¡£(¸ÃÀàË÷Òý²»ÄÜÓÃÀ´°´Ë³ÐòËÑË÷ÏÂÒ»¸öÌõÄ¿£©¡£MySQL²»ÄÜÈ·¶¨ÔÚÁ½¸öÖµÖ®¼ä´óÔ¼ÓжàÉÙÐÐ(Õâ±»·¶Î§ÓÅ»¯Æ÷ÓÃÀ´È·¶¨Ê¹ÓÃÄĸöË÷Òý)¡£Èç¹ûÄ㽫һ¸öMyISAM±í¸ÄΪhash-Ë÷ÒýµÄMEMORY±í£¬»áÓ°ÏìһЩ²éѯ¡£Ö»ÄÜʹÓÃÕû¸ö¹Ø¼ü×ÖÀ´ËÑË÷Ò»ÐС£(ÓÃB-Ê÷Ë÷Òý£¬Èκιؼü×ÖµÄ×î×óÃæµÄǰ׺¿ÉÓÃÀ´ÕÒµ½ÐÐ)¡£

¶ÔÓÚ**REEË÷Òý£¬µ±Ê¹ÓÃ>¡¢<¡¢>=¡¢<=¡¢BETWEEN¡¢!=»òÕß<>£¬»òÕßLIKE'pattern'(ÆäÖÐ'pattern'²»ÒÔͨÅä·û¿ªÊ¼)²Ù×÷·ûʱ£¬¹Ø¼üÔªËØÓë³£Á¿ÖµµÄ±È½Ï¹ØÏÔÓ¦Ò»¸ö·¶Î§Ìõ¼þ¡£¡°³£Á¿Öµ¡±ÏµÖ¸£º²éѯ×Ö·û´®Öеij£Á¿¡¢Í¬Ò»Áª½ÓÖеÄconst»òsystem±íÖеÄÁС¢ÎÞ¹ØÁª×Ó²éѯµÄ½á¹û¡¢ÍêÈ«´ÓÇ°ÃæÀàÐ͵Ä×Ó±í´ïʽ×é³ÉµÄ±í´ïʽ¡£

ÏÂÃæÊÇһЩWh**ERE×Ó¾äÖÐÓз¶Î§Ìõ¼þµÄ²éѯµÄÀý×Ó¡£

ÏÂÁз¶Î§²éѯÊÊÓÃÓÚbtreeË÷ÒýºÍhashË÷Òý£º
¸´ÖÆ´úÂë´úÂëÈçÏÂ:**LECT*FROMt1Wh**EREkey_col=1ORkey_colIN(15,18,20);
ÏÂÁз¶Î§²éѯÊÊÓÃÓÚbtreeË÷Òý
¸´ÖÆ´úÂë´úÂëÈçÏÂ:**LECT*FROMt1Wh**EREkey_col>1ANDkey_col<10;
**LECT*FROMt1Wh**EREkey_colLIKE'ab%'ORkey_colBETWEEN'bar'AND'foo';

MysqlÈçºÎʹÓÃË÷Òý

Ë÷ÒýÓÃÓÚ¿ìËÙÕÒ³öÔÚij¸öÁÐÖÐÓÐÒ»ÌØ¶¨ÖµµÄÐС£²»Ê¹ÓÃË÷Òý£¬MySQL±ØÐë´ÓµÚ1Ìõ¼Ç¼¿ªÊ¼È»ºó¶ÁÍêÕû¸ö±íÖ±µ½ÕÒ³öÏà¹ØµÄÐС£±íÔ½´ó£¬»¨·ÑµÄʱ¼äÔ½¶à¡£Èç¹û±íÖвéѯµÄÁÐÓÐÒ»¸öË÷Òý£¬MySQLÄÜ¿ìËÙµ½´ïÒ»¸öλÖÃÈ¥ËÑѰµ½**ÎļþµÄÖм䣬ûÓбØÒª¿´ËùÓÐ**¡£Èç¹ûÒ»¸ö±íÓÐ1000ÐУ¬Õâ±È˳Ðò¶ÁÈ¡ÖÁÉÙ¿ì100±¶¡£×¢ÒâÈç¹ûÄãÐèÒª·ÃÎʴ󲿷ÖÐУ¬Ë³Ðò¶ÁȡҪ¿ìµÃ¶à£¬ÒòΪ´ËʱÎÒÃDZÜÃâ´ÅÅÌËÑË÷¡£

´ó¶àÊýMySQLË÷Òý(PRIMARYKEY¡¢UNIQUE¡¢INDEXºÍFULLTEXT)ÔÚBÊ÷Öд洢¡£Ö»ÊǿռäÁÐÀàÐ͵ÄË÷ÒýʹÓÃR-Ê÷£¬²¢ÇÒMEMORY±í»¹Ö§³ÖhashË÷Òý¡£

¹ØÓÚʲôÇé¿öÏÂ**¿â»áʹÓÃË÷ÒýÒÔ¼°Ê²Ã´Çé¿öÏÂ**¿â²»»áʹÓÃË÷ÒýµÄÏêϸ½âÊÍÇë¿´ÓÅ»¯ÆªµÄÏà¹ØÕ½ڣ¬ÕâÀï¾Í²»ÔÙÀÛÊö¡£