¸ßЧ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 2005±íµÄ¸´ÖÆÓÐÁ½ÖÖ£ºÒ»ÖÖ¾ÍÊǰÑÕû¸ö±í¸´ÖƹýÈ¥£¬¾ÍºÃÏñ¸´ÖÆÎļþ²¢ÇÒÖØÃüÃû¡£±ðÍâÒ»ÖÖ¾ÍÊǰѱíµÄÄÚÈݸ´Öƹý³ö.
select * into newtable form oldtable;°Ñoldtabel¸´ÖƵ½newtableÇÒnewtable²»´æÔÚ,·ñÔò³ö´í.;
insert into newtable select * from oldtable°ÑoldtableµÄÄÚÈݲåÈëµ½newtable, newtableÒ»¶¨Òª´æÔÚ, ......
TEMPDB²»Ö§³Ö×Ô¶¯Ñ¹Ëõ£¬ÇÒËüµÄÊý¾Ý¿âºÍÎļþѹËõÄÜÁ¦ÊÇÓÐÏÞÖÆµÄ£»
Èç²»µÃ²»Ñ¹ËõTEMPDBµÄ´óС£¬½¨ÒéʹÓÃÀëÏß¹¦ÄÜ£¬ÈçϲÙ×÷£º
1.Í£Ö¹SQL SERVER·þÎñ£»
2.ÕÒµ½sqlservr.exeÔÚ´ÅÅÌÖеÄλÖã»
3.ʹÓÃsqlservr.exeÊäÈëÃüÁîsqlservr -c -f,ÕâÑùÆô¶¯SQL SERVERʱ£¬TEMPDBµÄ´óСÄܹ»±£³Ö×îС£»
4.ʹÓÃTSQLÃüÁALTER DATA ......
¸øÄã¸ö×îÏêϸµÄ°É ¿ÉÄÜÓÐÄãÒªµÄÄÚÈÝ
ËøµÄ¸ÅÊö
Ò». ΪʲôҪÒýÈëËø
¶à¸öÓû§Í¬Ê±¶ÔÊý¾Ý¿âµÄ²¢·¢²Ù×÷ʱ»á´øÀ´ÒÔÏÂÊý¾Ý²»Ò»ÖµÄÎÊÌâ:
¶ªÊ§¸üÐÂ
A,BÁ½¸öÓû§¶ÁͬһÊý¾Ý²¢½øÐÐÐÞ¸Ä,ÆäÖÐÒ»¸öÓû§µÄÐ޸Ľá¹ûÆÆ»µÁËÁíÒ»¸öÐ޸ĵĽá¹û,±ÈÈ綩Ʊϵͳ
Ôà¶Á
AÓû§ÐÞ¸ÄÁËÊý¾Ý,ËæºóBÓû§ÓÖ¶Á³ö¸ÃÊý¾Ý,µ«AÓû§ÒòΪijЩÔÒòÈ¡Ï ......
ÈçºÎÈÃÄãµÄSQLÔËÐеøü¿ì
---- ÈËÃÇÔÚʹÓÃSQLʱÍùÍù»áÏÝÈëÒ»¸öÎóÇø£¬¼´Ì«¹Ø×¢ÓÚËùµÃµÄ½á¹ûÊÇ·ñÕýÈ·£¬¶øºöÂÔÁ˲»Í¬µÄʵÏÖ·½·¨Ö®¼ä¿ÉÄÜ´æÔÚµÄÐÔÄܲîÒ죬ÕâÖÖÐÔÄܲîÒìÔÚ´óÐ͵ĻòÊǸ´ÔÓµÄÊý¾Ý¿â»·¾³ÖУ¨ÈçÁª»úÊÂÎñ´¦ÀíOLTP»ò¾ö²ßÖ§³ÖϵͳDSS£©ÖбíÏÖµÃÓÈΪÃ÷ÏÔ¡£±ÊÕßÔÚ¹¤×÷ʵ¼ùÖз¢ÏÖ£¬²»Á¼µÄSQLÍùÍùÀ´×ÔÓÚ²»Ç¡µ±µÄË÷ÒýÉè¼Æ¡¢ ......
-- ʾÀýÒ», ʹÓÃÖ¤Êé¼ÓÃÜÊý¾Ý.
-- ½¨Á¢²âÊÔÊý¾Ý±í
CREATE TABLE tb(ID int IDENTITY (1,1),data varbinary (8000));
GO
-- ½¨Á¢Ö¤ÊéÒ», ¸ÃÖ¤ÊéʹÓÃÊý¾Ý¿âÖ÷ÃÜÔ¿À´¼ÓÃÜ
CREATE CERTIFICATE Cert_Demo1
WITH
SUBJECT = N'cert1 encryption by database master key' ,
START_DATE = ......