Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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Êý¾Ý¿â³£ÓõÄT SQLÃüÁî

1. ²é¿´Êý¾Ý¿âµÄ°æ±¾
select @@version
2.²é¿´Êý¾Ý¿âËùÔÚ»úÆ÷²Ù×÷ϵͳ²ÎÊý
exec master..xp_msver
3. ²é¿´Êý¾Ý¿âÆô¶¯µÄ²ÎÊý
sp_configure
4.²é¿´Êý¾Ý¿âÆô¶¯Ê±¼ä
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
²é¿´Êý¾Ý¿â·þÎñÆ÷ÃûºÍʵÀýÃû
print ''Server Name.... ......

ÇåÀí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²éѯ֮Ë÷Òý£¨II£©

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