¸ßЧ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 Ò³ÎïÀí´æ´¢£©×îÓÒµÄÒ¶½ÚµãÉÏ£¨ÈçÉÏÀý£©¡£
Ïà¹ØÎĵµ£º
Ò»¡¢ PL/SQLÓïÑÔ¼ò½é
(±¾½²ÒåÖ®ËùÓгÌÐò¾ùµ÷ʽͨ¹ý)
Ê×ÏÈÎÒÃÇ¿´Ò»¸ö¼òµ¥Ö®Àý×Ó,ÏÂÃæÕâ¸öÀý×ÓÊÇͳ¼Æ´Ó1ÖÁ100µÄ×ܺÍ.
declare
i number:=0; /*ÉùÃ÷±äÁ¿¾®¸ø³õÖµ*/
t number:=1;
error_message exception; /*ÉùÃ÷Ò»¸ö³ö´í´¦Àí*/
begin
......
·½·¨(1)
SELECT stuff((select ','+ltrim(ColumnName) from #A for xml path('')
),1,1,'')
/*
102,103,104,105
*/
·½·¨(2)
DECLARE @s NVARCHAR(1000)='';
SELECT @s+=ColumnName+',' from #A;
SELECT @s; ......
½ñÌìÔÚÅäÖÃÊý¾Ý¿â·¢²¼ºÍ·Ö·¢Ê±×ÜÊDZ¨³öÏÖ 18483 ´íÎó
Ìáʾ˵£º´íÎó 18483:δÄÜÁ¬½Óµ½·þÎñÆ÷ "XXX"£¬ÒòΪ 'distributor_admin'δÔڸ÷þÎñÆ÷É϶¨ÒåΪԶ³ÌµÇ½¡£
Îҵķ¢²¼ºÍ·Ö·¢ÊÇͬһ¸ö·þÎñÆ÷£¬"XXX" ΪÎҵĻúÆ÷Ãû£¬·Ö·¢Êý¾Ý¿âÊÇĬÈϵÄÃû³Æ£¬¶øÎÒÔÚÁíÍâһ̨»úÆ÷ÉÏ×öʱ¾ÍÕý³£¡£
1¡¢ÉèÖù²Ïí¸´ÖÆÄ¿Â¼:
......
ÏÈÕ¾ÔÚÓ¦ÓóÌÐòµÄ½Ç¶È˵˵ËüÃǵIJ»Í¬¡£
1¡¢ Ö±½ÓÆ´ SQL
¾ÍÏñ´ó¼ÒÁ˽âµÄÄÇÑù£¬Ö±½ÓÆ´ SQL ´øÀ´ÁË SQL ×¢Èë¹¥»÷£¬´øÀ´ÁËÆ´Ê±Ð©ÐíµÄÐÔÄÜËðʧ£¬µ«ÊÇÆ´²»ÓÃÌí¼Ó SqlParameter £¬»áÉÙдºÜ¶à´úÂë——ºÜ¶àÈËϲ»¶Ö±½ÓÆ´£¬Ò²Ðí¾ÍÒòΪÕâµã¡£ÕâÖÖ×ö·¨»á°ÑÄãÆ´ºÃµÄ SQL ÔÑùÖ±½Ó·¢Ë͵½ DB ·þÎñÆ÷È¥Ö´ÐС££¨×¢ÒâÀàËÆ &rdquo ......
ÉÏ»ØÎÒÃÇ˵µ½ÆÀ¹ÀÒ»ÌõÓï¾äÖ´ÐÐЧÂÊÖ÷Òª¿´Âß¼ IO £¨É¶ÊÇÂß¼ IO £¬É¶ÊÇÎïÀí IO ¼ûÁª»úÎĵµ£©£¬Õâ´ÎÎÒÃǼÌÐø¡£
ÎÒÃÇÏÈ˵˵£¬·µ»Ø¶àÐнá¹ûʱ£¬ÎªÊ²Ã´ SQLServer ÓÐʱ»áÑ¡Ôñ index seek £¬ÓÐʱ»áÑ¡Ôñ index scan ¡£
ÒÔ nonclustered index ΪÀý˵Ã÷¡£
ÏñËùÓеÄË÷Òý B Ê÷Ò»Ñù£¬·Ç¾Û¼¯Ë÷ÒýÊ÷Ò²°üÀ¨ÍêÈ«ÓÉË÷ÒýÊý¾Ý×é³ÉµÄ¸ù½ ......