ת£º³£ÓÃSQLÓï¾äÊéд¼¼ÇÉ
³£ÓÃSQLÓï¾äÊéд¼¼ÇÉ
×÷Õߣº À´Ô´£ºjavaeye²©¿Í ·¢²¼Õߣºadmin
ʱ¼ä£º2009-05-11 10:07:41 µã»÷£º2010
SQL½á¹¹»¯²éѯ×Ö·û´®µÄ¸Äд£¬ÊÇʵÏÖÊý¾Ý¿â²éѯÐÔÄÜÌáÉýµÄ×îÏÖʵ¡¢×îÓÐЧµÄÊֶΣ¬ÓÐʱÉõÖÁÊÇΨһµÄÊֶΣ¬±ÈÈçÔÚ²»ÔÊÐí´ó·ù¶ÈÐÞ¸ÄÏÖÓÐÊý¾Ý¿â½á¹¹µÄÇé¿öÏ¡£
ͨ¹ýÓÅ»¯SQLÓï¾äÌá¸ß²éѯÐÔÄܵĹؼüÊÇ£º
? ¸ù¾Ýʵ¼ÊÐèÇóÇé¿ö£¬½¨Á¢ºÏÊʵÄË÷Òý£»
? ʹÓÃÒ»ÇпÉÄܵķ½Ê½È¥ÀûÓúÃË÷Òý£¬±ÜÃâÈ«±íɨÃ裻
? ¾¡Á¿¼õÉÙÄÚ´æ¼°Êý¾ÝI/O·½ÃæµÄ¿ªÏú
Ò»¡¢ ½¨Á¢Ë÷Òý
£¨Ò»£©½¨Á¢“Êʵ±”µÄË÷Òý£¬ÊÇ¿ìËÙ²éѯµÄ»ù´¡¡£
Ë÷Òý£¨index£©Êdzý±íÖ®ÍâÁíÒ»ÖØÒªµÄ¡¢Óû§¶¨ÒåµÄ´æ´¢ÔÚÎïÀí½éÖÊÉϵÄÊý¾Ý½á¹¹¡£µ±¸ù¾ÝË÷ÒýÂëµÄÖµËÑË÷Êý¾Ýʱ£¬Ë÷ÒýÌṩÁ˶ÔÊý¾ÝµÄ¿ìËÙ·ÃÎÊ¡£ÊÂʵÉÏ£¬Ã»ÓÐË÷Òý£¬Êý¾Ý¿âÒ²Äܸù¾ÝSELECTÓï¾ä³É¹¦µØ¼ìË÷µ½½á¹û£¬µ«Ëæ×űí±äµÃÔ½À´Ô½´ó£¬Ê¹ÓÓÊʵ±”µÄË÷ÒýµÄЧ¹û¾ÍÔ½À´Ô½Ã÷ÏÔ¡£×¢Ò⣬ÔÚÕâ¾ä»°ÖУ¬ÎÒÃÇÓÃÁË“Êʵ±”Õâ¸ö´Ê£¬ÕâÊÇÒòΪ£¬Èç¹ûʹÓÃË÷Òýʱ²»ÈÏÕ濼ÂÇÆäʵÏÖ¹ý³Ì£¬Ë÷Òý¼È¿ÉÒÔÌá¸ßÒ²»áÆÆ»µÊý¾Ý¿âµÄ¹¤×÷ÐÔÄÜ¡£
Ë÷Òýʵ¼ÊÉÏÊÇÒ»ÖÖÌØÊâµÄĿ¼£¬SQL SERVERÌṩÁËÁ½ÖÖË÷Òý£º
? ¾Û¼¯Ë÷Òý£¨clustered index£¬Ò²³Æ¾ÛÀàË÷Òý¡¢´Ø¼¯Ë÷Òý£©
ÎÒÃÇ°ÑÕâÖÖÕýÎÄÄÚÈݱ¾Éí¾ÍÊÇÒ»ÖÖ°´ÕÕÒ»¶¨¹æÔòÅÅÁеÄĿ¼³ÆΪ“¾Û¼¯Ë÷Òý”¡£
ÀýÈ磺
ººÓï×ÖµäÖа´Æ´Òô²éijһ¸ö×Ö£¬¾ÍÊÇʹÓÓ¾Û¼¯Ë÷Òý”£¬Êµ¼ÊÉÏ£¬Äã¸ù±¾Óò»×ŲéĿ¼£¬Ö±½ÓÔÚ×ÖµäÕýÎÄÀïÕÒ£¬¾ÍÄܺܿìÕÒµ½ÐèÒªµÄºº×Ö£¨¼ÙÉèÄãÖªµÀ·¢Òô£©¡£
? ·Ç¾Û¼¯Ë÷Òý£¨nonclustered index£¬Ò²³Æ·Ç¾ÛÀàË÷Òý¡¢·Ç´Ø¼¯Ë÷Òý£©
ÎÒÃÇ°ÑĿ¼´¿´âÊÇĿ¼£¬ÕýÎÄ´¿´âÊÇÕýÎĵÄÅÅÐò·½Ê½³ÆΪ“·Ç¾Û¼¯Ë÷Òý”¡£
ÀýÈ磺
ººÓï×ÖµäÖа´²¿Êײéijһ¸ö×Ö£¬²¿Ê×Ŀ¼ºÍÕýÎÄÒ»¶¨Òª¿ÌÒâµÄͨ¹ýÒ³Âë²ÅÄÜÁªÏµµ½Ò»Æð£¬Æä˳Ðò²»ÊÇÌìȻһÖµġ£
¾Û¼¯Ë÷ÒýÓë·Ç¾Û¼¯Ë÷ÒýµÄ×î´óÇø±ð¾ÍÔÚÓÚ£º¾Û¼¯Ë÷ÒýÊǶÔÔÊý¾Ý±í½øÐÐÅÅÐò£¬Òò´ËÖ»Òª·ûºÏË÷ÒýÌõ¼þ£¬¾ÍÄܹ»Ö±½ÓÁ¬ÐøµÄ¶ÁÈ¡Êý¾Ý¼Ç¼£¬¼¸ºõ¿ÉÒÔ´ïµ½¶ÔÊý¾Ý±íµÄÁãɨÃ裻¶ø·Ç¾Û¼¯Ë÷Òý½ö½öÖ»ÊÇÁíÍ⽨ÁËÒ»ÕÅË÷Òý±í£¬È¡Êý¾ÝµÄʱºò£¬´ÓË÷Òý±íÈ¡µÃ½á¹ûºó£¬»¹ÐèÒªµ½Ö¸ÕëËùÖ¸µÄÊý¾ÝÐжÁÈ¡ÏàÓ¦Êý¾Ý£¬Òò´Ë£¬ÔÚÐÔÄÜÉÏ£¬¾Û¼¯Ë÷Òý»á´ó´óÓÅÓڷǾۼ¯Ë÷Òý¡£
¡¡¡¡µ«ÊÇÔÚÒ»ÕűíÖУ¬¾Û¼¯Ë÷ÒýÖ»ÔÊÐíÒ»¸ö£¬ÊDZȽϱ¦¹óµÄ£¬Òò´ËÒª¾¡¿ÉÄܵÄÓÃÓÚÄÇЩʹÓÃƵÂÊ×î¸ßµÄË÷ÒýÉÏ¡£ ÁíÍ⣬²éѯʱ±ØÐèÒªÓõ½Ë÷ÒýµÄÆðʼÁУ¬·ñÔòË÷ÒýÎÞЧ¡£ÁíÍ⣬Æ
Ïà¹ØÎĵµ£º
USE tempdb
GO
CREATE TABLE AuctionItems
(
itemid INT NOT NULL PRIMARY KEY NONCLUSTERED,
itemtype NVARCHAR(30) NOT NULL,
whenmade INT&nb ......
µÈ´ýÀàÐÍ ËµÃ÷
ASYNC_DISKPOOL_LOCK
µ±³¢ÊÔͬ²½²¢ÐеÄỊ̈߳¨Ö´Ðд´½¨»ò³õʼ»¯ÎļþµÈÈÎÎñ£©Ê±³öÏÖ¡£
ASYNC_IO_COMPLETION
µ±Ä³ÈÎÎñÕýÔڵȴý I/O Íê³Éʱ³öÏÖ¡£
ASYNC_NETWORK_IO
µ±ÈÎÎñ±»×èÖ¹ÔÚÍøÂçÖ®ºóʱ³öÏÖÔÚÍøÂçдÈëÖС£
BACKUP
µ±ÈÎÎñ×÷Ϊ±¸·Ý´¦Àí ......
¡¡¡¡
¡¡¡¡¡ô¾¡Á¿²»ÒªÔÚwhereÖаüº¬×Ó²éѯ;
¡¡¡¡¹ØÓÚʱ¼äµÄ²éѯ£¬¾¡Á¿²»ÒªÐ´³É£ºwhere to_char(dif_date,'yyyy-mm-dd')=to_char('2007-07-01','yyyy-mm-dd');
¡¡¡¡¡ôÔÚ¹ýÂËÌõ¼þÖУ¬¿ÉÒÔ¹ýÂ˵ô×î´óÊýÁ¿¼Ç¼µÄÌõ¼þ±ØÐë·ÅÔÚwhere×Ó¾äµÄĩβ;
¡¡¡¡from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í£¬driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖа ......
UPDATe [Sale_Goods] SET [Catalog33_ID] = [Catalog].[ID]
from [Sale_Goods]
LEFT OUTER JOIN [chdy222] ON [Sale_Goods].[Code] = [chdy222].[ID]
LEFT OUTER JOIN [Catalog] ON [chdy222].[ufidaname] = [Catalog].[Name] and [Catalog].[Type]=33
WHERE [Catalog].[ID] IS NOT NULL ......
ÓÐÕâÑùÒ»¸ö×Ö·û´®(ÖÐ,¹ú,ÖÐ,ÈËÃñ,¹ú,ÏÖ´ú¹¤Òµ»¯½ø³Ì,¹ú)
´¦Àí¹ýÖ®ºó±ä³É(ÖÐ,¹ú,ÈËÃñ,ÏÖ´ú¹¤Òµ»¯½ø³Ì)
ÒÔÏÂÊÇÎҵķ½·¨,ÓÐʲô²»¶ÔµÄµØ·½»¹Çë¸÷λָµã
------------------ɾ³ýÖظ´µÄ×Ö·û
declare
@v1 nvarchar
......