Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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È«ÎÄË÷Òý¸ÉÈÅ´ÊʾÀý

´¦ÀíÍøÕ¾²éѯ°üº¬”Ö®”×Ö³öÏ֔ȫÎÄËÑË÷Ìõ¼þÖаüº¬¸ÉÈÅ´Ê”ÏÖÏóµÄ×ܽ᣺
author:perfectaction
Sql server 2008È«ÎÄË÷ÒýµÄ¸ÉÈŴʱíĬÈÏÔÚResource¿âϵͳ±íÄÚ£¬ÎÞ·¨¸ü¸Ä£¬µ«sql2008ÌṩÁË×Ô¶¨Òå¸ÉÈŴʱíµÄ¹¦ÄÜ£¬¿É°ó¶¨µ½Ä³¸öÈ«ÎÄË÷ÒýÉÏ¡£
Ïà¹Ø²Ù×÷ÈçÏ£º
--sql server 2008 È«ÎÄË÷Òý½¨Á¢¼°´´½¨È«ÎÄ ......

SQL SERVERÐÔÄÜÓÅ»¯ TempdbÏà¹ØÎÊÌâ

TEMPDB²»Ö§³Ö×Ô¶¯Ñ¹Ëõ£¬ÇÒËüµÄÊý¾Ý¿âºÍÎļþѹËõÄÜÁ¦ÊÇÓÐÏÞÖÆµÄ£»
Èç²»µÃ²»Ñ¹ËõTEMPDBµÄ´óС£¬½¨ÒéʹÓÃÀëÏß¹¦ÄÜ£¬ÈçϲÙ×÷£º
1.Í£Ö¹SQL SERVER·þÎñ£»
2.ÕÒµ½sqlservr.exeÔÚ´ÅÅÌÖеÄλÖã»
3.ʹÓÃsqlservr.exeÊäÈëÃüÁîsqlservr -c -f,ÕâÑùÆô¶¯SQL SERVERʱ£¬TEMPDBµÄ´óСÄܹ»±£³Ö×îС£»
4.ʹÓÃTSQLÃüÁALTER DATA ......

SQL SERVER ÖÐÈçºÎʹÓÃÐÐËø

¸øÄã¸ö×îÏêϸµÄ°É ¿ÉÄÜÓÐÄãÒªµÄÄÚÈÝ
ËøµÄ¸ÅÊö
Ò». ΪʲôҪÒýÈëËø
¶à¸öÓû§Í¬Ê±¶ÔÊý¾Ý¿âµÄ²¢·¢²Ù×÷ʱ»á´øÀ´ÒÔÏÂÊý¾Ý²»Ò»ÖµÄÎÊÌâ:
¶ªÊ§¸üÐÂ
A,BÁ½¸öÓû§¶ÁͬһÊý¾Ý²¢½øÐÐÐÞ¸Ä,ÆäÖÐÒ»¸öÓû§µÄÐ޸Ľá¹ûÆÆ»µÁËÁíÒ»¸öÐ޸ĵĽá¹û,±ÈÈ綩Ʊϵͳ
Ôà¶Á
AÓû§ÐÞ¸ÄÁËÊý¾Ý,ËæºóBÓû§ÓÖ¶Á³ö¸ÃÊý¾Ý,µ«AÓû§ÒòΪijЩԭÒòÈ¡Ï ......

SQL ServerË÷ÒýÔ­Ôò

ÈçºÎÈÃÄãµÄSQLÔËÐеøü¿ì
---- ÈËÃÇÔÚʹÓÃSQLʱÍùÍù»áÏÝÈëÒ»¸öÎóÇø£¬¼´Ì«¹Ø×¢ÓÚËùµÃµÄ½á¹ûÊÇ·ñÕýÈ·£¬¶øºöÂÔÁ˲»Í¬µÄʵÏÖ·½·¨Ö®¼ä¿ÉÄÜ´æÔÚµÄÐÔÄܲîÒ죬ÕâÖÖÐÔÄܲîÒìÔÚ´óÐ͵ĻòÊǸ´ÔÓµÄÊý¾Ý¿â»·¾³ÖУ¨ÈçÁª»úÊÂÎñ´¦ÀíOLTP»ò¾ö²ßÖ§³ÖϵͳDSS£©ÖбíÏÖµÃÓÈΪÃ÷ÏÔ¡£±ÊÕßÔÚ¹¤×÷ʵ¼ùÖз¢ÏÖ£¬²»Á¼µÄSQLÍùÍùÀ´×ÔÓÚ²»Ç¡µ±µÄË÷ÒýÉè¼Æ¡¢ ......

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

ÎÒÃÇÏÈ¿´ NestedLoop ºÍ MergeJoin µÄËã·¨£¨ÒÔÏÂΪÒýÓ㬼û RicCC µÄ¡¶ ͨÍùÐÔÄÜÓÅ»¯µÄÌìÌà - µØÓü JOIN ·½·¨ËµÃ÷ ¡· ):
==================================
NestedLoop:
   foreach rowA in tableA where tableA.col2=?
    {
    search rowsB from tableB where tableB.c ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ