Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

¸ßЧSQL²éѯ֮Ë÷Òý¸²¸Ç(index coverage)

½ñÌì×öSQL ÓÅ»¯£¬²éÕÒÖ´Ðмƻ®Ê±£¬Ö´Ðмƻ®£¬·¢ÏÖ´ËÖ´Ðмƻ®ÓëÒÔÍùµÄ¼Æ»®ÓÐËùÇø±ð£»ÕÒ¼»¥ÁªÍø£¬ÖÕÓÚÕÒһƪÓйØÑо¿±È½ÏÉîÈëµÄÎÄÕ£»
Ô­Ö´Ðмƻ®Ê¹ÓõÄÊÇË÷ÒýɨÃ裬ͻȻһÏ»áʹÓÃË÷Òý¸²¸Ç¼¼Êõ£¬Ð§ÂÊ´óÔö£»
SELECT * µÄÕæÏࣺË÷Òý¸²¸Ç(index coverage)
SELECT *µÄЧÂʺÜÔã¸âÂ𣿵±È»£¬ËùÓÐÈ˶¼ÖªµÀÕâÒ»µã£¬µ«ÊÇÎªÊ²Ã´ÄØ£¿
ÊÇÒòΪ·µ»ØÁËÌ«¶àµÄÊý¾Ý£¿
ÕâÊÇÒ»¸öÆÕ±éµÄ»Ø´ð£¬µ«ÎÒ²»ÕâÑùÈÏΪ¡£Èç¹ûÄãµÄÊý¾Ý¿âÉè¼Æ¹æ·¶ºÏÀí£¬ÄÇô´ø¿íÕ¼ÓÃʵ¼ÊÉϷdz£µÄС¡£
ÈÃÎÒÃÇ¿´¿´ÏÂÃæµÄÀý×Ó¡£ÏÂÃæµÄ²éѯ½«»á´ÓAdventureWorks.dbo.TransactionHistoryArchive£¨×ܹ²´óÔ¼Óнü9ÍòÐÐÊý¾Ý£©ÖÐÑ¡Ôñ³ö326ÐÐÊý¾Ý¡£µÚÒ»¸öʹÓÃÁËSELECT * ²éѯ£¬ºóÒ»¸ö²éѯÔòÓÐÃ÷È·µÄ×ֶΡ£
SELECT   *   from  Production.TransactionHistoryArchive 
WHERE  ReferenceOrderID  <   100
SELECT  ReferenceOrderLineID  from  Production.TransactionHistoryArchive 
WHERE  ReferenceOrderID  <   100
ÔÚÕâÖÖÇé¿öÏ£¬Á½ÕßÔÚÍøÂç´ø¿íµÄÇø±ðÖ»ÓÐ15K£¨180K-165K£©£¬´óÔ¼10%µÄ´ø¿í²îÒì¡£µÄÈ·ÖµµÃÈ¥ÓÅ»¯£¬µ«²»»áÓкܴóµÄЧ¹û¡£
SELECT * ½«Ôì³É±í/Ë÷ÒýɨÃè
SELECT * µÄ×î´óÎÊÌâÊǽ«Ó°Ïì²éѯ¼Æ»®¡£SQL ServerÖ÷ҪʹÓÃË÷ÒýÈ¥²éѯÄãÐèÒªµÄÊý¾Ý£¬µ±Ë÷Òý°üÀ¨ËùÓеÄÄãÇëÇó²éѯµÄ×ֶΣ¬SQL Server½«²»ÐèҪȥÔÚ±íÖвéѯ¡£Õâ¸ö¸ÅÄî³Æ×öË÷Òý¸²¸Ç¡£ÔÚÉÏÃæµÄÀý×ÓÖУ¬µÚÒ»¸ö²éѯ½á¹ûÊÇÔÚ¾Û¼¯Ë÷ÒýɨÃèÖУ¬·´¹ýÀ´£¬µÚ¶þ¸öÀý×ÓʹÓÃÁ˸ü¶à¸üÓÐЧÂʵÄË÷ÒýɨÃè¡£ÔÚÕâ¸ö°¸ÀýÖУ¬Ë÷ÒýɨÃè±È¾Û¼¯Ë÷ÒýɨÃè¿ì100±¶ ¡£

³ý·ÇÄãÒѾ­½«ÎªÃ¿¸ö×ֶν¨Á¢ÁËË÷Òý£¨ÏÔÈ»²»ÊǸöºÃÖ÷Ò⣩£¬SELECT *ÊDz»Äܹ»ÀûÓõ½Ë÷Òý¸²¸Ç£¬ÄãÖ»ÄÜÈ¥×öɨÃè²Ù×÷£¨·Ç³£µÄûÓÐЧÂÊ£©¡£
Èç¹ûÄãÖ»ÊDzéѯÄãËùÐèÒªµÄ×ֶΣ¬ÄÇÄã¸ü¿ÉÄܵĸ²¸Çµ½ÄãµÄË÷Òý¡£ÎÒÏëÕâ¾ÍÊDz»ÍƼöʹÓÃSELECT *µÄÖ÷ÒªµÄÔ­Òò¡£
Îȶ¨ÐÔ·½Ãæ
ÔÚά»¤Ò»¸öÓ¦ÓóÌÐòʱ£¬SELECT *Ò²»á´øÀ´Ò»Ð©ÒâÏë²»´óµÄÎÊÌâ¡£Ëü»áÒýÆðÄãµÄ´úÂë·¢ÉúһЩ²»È·¶¨ÐÔ¡£Èç¹ûÄãÔö¼ÓÁËÒ»¸öÐУ¨Òë×¢£ºÎÒ¾õµÃÕâÀïÓ¦¸ÃÊÇ×ֶΣ©µ½Ò»¸ö±íÖУ¬ÄÇôSELECT * ·µ»ØµÄ½á¹ûµ½ÄãµÄÓ¦ÓóÌÐòÖн«»áÔڽṹÉÏ·¢Éú±ä»¯¡£Á¼ºÃµÄÓ¦ÓóÌÐòÓ¦¸ÃÊÇʹÓÃ×Ö¶ÎÃû³ÆµÄ£¬¶ø²»Ó¦¸ÃÊÜ´ËÓ°Ïì¡£µ±Íâ½ç·¢Éú±ä»¯Ê±£¬Á¼ºÃµÄÓ¦ÓóÌÐòÉè¼ÆÒ²Ó¦¸Ã×îС»¯µÄ¸ü¸Ä¡£
Ó¢ÎÄÔ­¸å: http://weblogs.asp.net/jgalloway/archive/2007/07/18/the-real-reason-select-queries


Ïà¹ØÎĵµ£º

º½¿Õ¹«Ë¾¹ÜÀíϵͳ(VC++ ÓëSQL 2005)

ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
      ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......

SQLÓÅ»¯34Ìõ

ÎÒÃÇÒª×öµ½²»µ«»áдSQL,»¹Òª×öµ½Ð´³öÐÔÄÜÓÅÁ¼µÄSQL,ÒÔÏÂΪ±ÊÕßѧϰ¡¢ÕªÂ¼¡¢²¢»ã×ܲ¿·Ö×ÊÁÏÓë´ó¼Ò·ÖÏí£¡
£¨1£©      Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLE µÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom× ......

¾«ÃîSqlÓï¾ä

1£® ÅжÏa±íÖÐÓжøb±íÖÐûÓеļǼ
select a.* from tbl1 a
left join tbl2 b
on a.key = b.key
where b.key is null
         ËäȻʹÓÃinÒ²¿ÉÒÔʵÏÖ£¬µ«ÊÇÕâÖÖ·½·¨µÄЧÂʸü¸ßһЩ
2£® н¨Ò»¸öÓëij¸ö±íÏàͬ½á¹¹µÄ±í
select * into b
from a where 1<>1
3£®betwee ......

ÔÚSQL SERVER 2005Öе÷ÓÃWeb Service

¡¾IT168 ¼¼ÊõÎĵµ¡¿
½éÉÜ
    SQL SERVER2005ÌṩÁËÔÚ.NETÖÐ×Ô¶¨Ò庯Êý¡¢´æ´¢¹ý³Ì¡¢¾ÛºÏ¡¢´¥·¢Æ÷ÒÔ¼°ÀàÐ͵ŦÄÜ¡£ÓÚÊÇ£¬ÎÒÃÇ¿ÉÒÔÔÚÊý¾Ý¿âµÄº¯Êý¡¢´æ´¢¹ý³Ì»òÀàÐÍÕûºÏ.NET¶ÔÏó·½·¨µÈ¡£ÔÚ֮ǰµÄһƪÎÄÕ£¬ÎÒÃÇÑÝʾÁËÈçºÎ½«ÕýÔò±í´ïʽ¹¦ÄÜÕûºÏµ½SQL CLRÖС£ÔÚ±¾ÎÄÖУ¬»¹ÊÇÑÝʾÕâÖÖÕûºÏ£¬µ«ÊÇÕâ´ÎÊÇ´ÓSQL CLRÖ ......

¸ßЧSQL²éѯ֮Ë÷Òý£¨V£©

ÏÈÕ¾ÔÚÓ¦ÓóÌÐòµÄ½Ç¶È˵˵ËüÃǵIJ»Í¬¡£
1¡¢  Ö±½ÓÆ´ SQL
¾ÍÏñ´ó¼ÒÁ˽âµÄÄÇÑù£¬Ö±½ÓÆ´ SQL ´øÀ´ÁË SQL ×¢Èë¹¥»÷£¬´øÀ´ÁËÆ´Ê±Ð©ÐíµÄÐÔÄÜËðʧ£¬µ«ÊÇÆ´²»ÓÃÌí¼Ó SqlParameter £¬»áÉÙдºÜ¶à´úÂë——ºÜ¶àÈËϲ»¶Ö±½ÓÆ´£¬Ò²Ðí¾ÍÒòΪÕâµã¡£ÕâÖÖ×ö·¨»á°ÑÄãÆ´ºÃµÄ SQL Ô­ÑùÖ±½Ó·¢Ë͵½ DB ·þÎñÆ÷È¥Ö´ÐС££¨×¢ÒâÀàËÆ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ