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

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

´óÐÍϵͳµÄÉú²ú»·¾³£¬Ò»°ãÇé¿öÏ£¬ÎÒÃÇÆÀ¼ÛÒ»Ìõ²éѯÊÇ·ñÓÐЧÂÊ£¬¸ü¶àµÄÊǹØ×¢Âß¼­ IO( ÖÁÓÚΪʲô£¬»ØÍ·²¹Ò»Æª ) ¡£ÎÒÃdz£Ëµ£¬“Òª½¨±ëº·µÄË÷Òý”¡¢“Ҫд¸ßЧµÄ SQL ”£¬Æäʵ×îÖÕÄ¿µÄ¾ÍÊÇÔÚÏàͬ½á¹û¼¯Çé¿öÏ£¬¾¡¿ÉÄܼõÉÙÂß¼­ IO ¡£
1.1      where Ìõ¼þµÄÁÐÉ϶¼µÃÓÐͳ¼ÆÐÅÏ¢¡£
ûͳ¼ÆÐÅÏ¢ SQLServer ¾ÍÎÞ·¨¹ÀË㲻ͬ²éѯ¼Æ»®¿ªÏúÓÅÁÓ£¬¶øÖ»ÄܲÉÓÃ×îÎÈÍ×µÄ Scan £¨²»¹ÜÊÇ table scan »¹ÊÇ clustered index scan £©¡£Ò»°ãÇé¿öÏÂÎÒÃDz»»á·¸ÕâÖÖ´íÎó—— where Ìõ¼þÀﲻʹÓ÷ÇË÷ÒýÁÐÊǸö³£Ê¶¡£Ë÷ÒýÉϵÄͳ¼ÆÐÅÏ¢ÊÇÎÞ·¨É¾³ýµÄ¡£
1.2      ¾¡Á¿²»Ê¹Óò»µÈÓÚ£¨ != £©»òÕß NOT Âß¼­ÔËËã·û¡£
ÕâÌõ¹æÔò±»¹ãΪ´«ËÌ£¬Ô­Òò¾ÝÁª»úÎĵµºÍ°Ù¾´Í¬Ñ§µÄÊé½²£¬Ò²ÊÇ SQLServer ÎÞ·¨ÆÀ¹À²»Í¬²éѯ¼Æ»®¿ªÏúµÄÓÅÁÓ¡£µ«ÊÇ SqlServer2k5 ´ÏÃ÷Á˺ܶ࣬ÊÔÑé·¢ÏÖ¾¡¹ÜÓÃÁË != »òÕß not £¬²éѯ»¹ÊǻᱻÓÅ»¯¡£ÈçÏ£º
create table tb1
(
    col1 int identity ( 1, 1) primary key ,
    col2 int not null,
    col3 varchar ( 64) not null
)
create index ix_tb1_col2 on tb1
(
    col2
)
create index ix_tb1_col3 on tb1
(
    col3
)
declare @f int
set @f = 0
while @f < 9999
begin
    insert into tb1 ( col2, col3) values ( 1, 'ssdd' £©
    set @f = @f + 1
end
insert into tb1 ( col2, col3) values ( 0, 'aadddd' )
insert into tb1 ( col2, col3) values ( 2, 'bbddd' )
insert into tb1 ( col2, col3) values ( 3, 'bbaaddddddaa' )
ͨ¹ýÉÏÊö´úÂ룬¸÷λ¿ÉÒÔ¿´µ½Êý¾Ý·Ö²¼¡£ col2 ֵΪ 1 µÄÓÐ 9999 Ìõ£» col2 ֵΪ 0 ¡¢ 2 ¡¢ 3 µÄ·Ö±ðÓÐ 1 Ìõ¡£
°´ÕÕ±¾Ìõ¹æÔò£¬ != ºÍ NOT ´øÀ´µÄÓ¦¸ÃÊǸö scan ²Ù×÷£¬µ«Êµ¼ÊÇé¿öÊÇ£º
SQL2k5 ºÜ´ÏÃ÷£¬ËüÒÀ¾Ýͳ¼ÆÐÅÏ¢·ÖÎöµÃ³öÀ´£¬Ó¦¸Ã²ÉÓà index seek ¶ø²»ÊÇ index scan ¡££¨ÉÔ΢½âÊͽâÊÍ index seek ºÍ index scan £ºË÷ÒýÊÇÒ»¿Å B Ê÷£¬ index seek ÊDzéÕÒ´Ó B Ê÷µÄ¸ù½Úµã¿ªÊ¼£¬Ò»¼¶Ò»¼¶ÕÒµ½Ä¿±êÐС£ index scan ÔòÊÇ´Ó×óµ½ÓÒ£¬°ÑÕû¸ö B Ê÷±éÀúÒ»±é¡£¼ÙÉèΨһµÄÄ¿±êÐÐλÓÚË÷ÒýÊ÷£¨¼ÙÉèÊǷǾۼ¯Ë÷Òý£¬Ê÷Éî¶È 2 £¬Ò¶½ÚµãÕ¼Óà k Ò³ÎïÀí´æ´¢£©×îÓÒµÄÒ¶½ÚµãÉÏ£¨ÈçÉÏÀý£©¡£


Ïà¹ØÎĵµ£º

[SQL Server 2008]SQL Server 2008 °²×°Ê¾ÀýÊý¾Ý¿â

ÏÂÔØµØÖ·£ºhttp://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=19353
ÎÒÏÂÔØµÄÊÇSQL2008.AdventureWorks_All_Databases.x86.msi£¬±¾²»ÏëÏÂÔØÕâÖÖ°²×°Îļþ£¬µ«½Å±¾Îļþ×ÜÊÇÖ´Ðгö´í£¬Ô­Òòû¾ßÌåÉ¡£ÕâÖÖ°²×°Îļþ°²×°ºó»á´´½¨Áù¸ö¿âAdventureWorks¡¢AdventureWorks2008¡¢AdventureWorksDW¡¢Ad ......

¹ØÓÚsql¸ü¸Ä¼ÆËã»úÃûºÍ·þÎñÆ÷ÃûÒ»Ö»ò"´íÎó 18483

½ñÌìÔÚÅäÖÃÊý¾Ý¿â·¢²¼ºÍ·Ö·¢Ê±×ÜÊDZ¨³öÏÖ 18483 ´íÎó
Ìáʾ˵£º´íÎó 18483:δÄÜÁ¬½Óµ½·þÎñÆ÷ "XXX"£¬ÒòΪ 'distributor_admin'δÔڸ÷þÎñÆ÷É϶¨ÒåΪԶ³ÌµÇ½¡£
Îҵķ¢²¼ºÍ·Ö·¢ÊÇͬһ¸ö·þÎñÆ÷£¬"XXX" ΪÎҵĻúÆ÷Ãû£¬·Ö·¢Êý¾Ý¿âÊÇĬÈϵÄÃû³Æ£¬¶øÎÒÔÚÁíÍâһ̨»úÆ÷ÉÏ×öʱ¾ÍÕý³£¡£
1¡¢ÉèÖù²Ïí¸´ÖÆÄ¿Â¼:
      ......

ÇåÀíSQL Server 2008Êý¾Ý¿âÈÕÖ¾

µÚÒ»²½, ÔÚÊÕËõǰÏȲ鿴ÈÕÖ¾µÄ´óС:
SELECT *
from sysfiles
WHERE name LIKE ' % LOG %'
GO
µÚ¶þ²½, °ÑÊý¾Ý¿âµÄ»Ö¸´Ä£Ê½Éè³É”¼òµ¥”:
ALTER DATABASE ¿âÃû SET RECOVERY SIMPLE
GO
µÚÈý²½, ÔËÐÐcheckpointÖ¸Áî, °Ñdirty pageд½øÊý¾Ý¿â:
CHECKPOINT
GO
µÚËIJ½, ½Ø¶ÏÈÕÖ¾: 
BACKUP ......

SQL SERVERÐÔÄÜ·ÖÎö ËÀËø¼ì²âÊý¾Ý¿â×èÈûÓï¾ä

×÷ÖÐÊý¾Ý¿â¾­³£³ö´íËÀËø£¬²¢ÇÒ»¹ÒªÒªÇó½â¾öµ±Ç°µÄËÀËø£¬ÎÊÌâ¶à¶à£»
²ÎÕÕCSDN£¬Öйú·ç(Roy)һƪËÀËøÎÄÕ²¢¸Ä½øÁËÏ£»
/***********************************************************************************************************************
ÕûÀíÈË£ººÚľÑÂÉϵÄÎÏÅ£(lenolotus) ÈÕÆÚ:2009.04.28
************ ......

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

ÉÏ»ØÎÒÃÇ˵µ½ÆÀ¹ÀÒ»ÌõÓï¾äÖ´ÐÐЧÂÊÖ÷Òª¿´Âß¼­ IO £¨É¶ÊÇÂß¼­ IO £¬É¶ÊÇÎïÀí IO ¼ûÁª»úÎĵµ£©£¬Õâ´ÎÎÒÃǼÌÐø¡£
ÎÒÃÇÏÈ˵˵£¬·µ»Ø¶àÐнá¹ûʱ£¬ÎªÊ²Ã´ SQLServer ÓÐʱ»áÑ¡Ôñ index seek £¬ÓÐʱ»áÑ¡Ôñ index scan ¡£
ÒÔ nonclustered index ΪÀý˵Ã÷¡£
ÏñËùÓеÄË÷Òý B Ê÷Ò»Ñù£¬·Ç¾Û¼¯Ë÷ÒýÊ÷Ò²°üÀ¨ÍêÈ«ÓÉË÷ÒýÊý¾Ý×é³ÉµÄ¸ù½ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ