ת£º³£ÓÃ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 ......
CREATE PROC [dbo].[UP_EC_JOB_UpdateAddressType]
(
@Count INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TransactionNumber INT
DECLARE @Cursor CURSOR
SET @C ......
select 1 from ...£¬ sqlÓï¾äÖеÄ1´ú±íʲôÒâ˼£¿²é³öÀ´ÊǸöʲô½á¹û£¿
select 1 from table;Óëselect anycol(Ä¿µÄ±í¼¯ºÏÖеÄÈÎÒâÒ»ÐУ© from table;Óëselect * from table ´Ó×÷ÓÃÉÏÀ´ËµÊÇûÓвî±ðµÄ£¬¶¼ÊDz鿴ÊÇ·ñÓмǼ£¬Ò»°ãÊÇ×÷Ìõ¼þ²éѯÓõġ£select 1 from Ö ......
Äܼà¿ØSQL ServerÊý¾Ý¿âÔËÐеÄÇé¿ö£¬¶Ô·þÎñÆ÷À´Ëµ¶¼ÊÇÒ»¼þ·Ç³£ÖØÒªµÄÊ£¬ÏÂÃ濪ʼ½éÉÜ¡£
Microsoft SQL Server 2005 ÌṩÁËһЩ¹¤¾ßÀ´¼à¿ØÊý¾Ý¿â¡£·½·¨Ö®Ò»ÊǶ¯Ì¬¹ÜÀíÊÓͼ¡£¶¯Ì¬¹ÜÀíÊÓͼ (DMV) ºÍ¶¯Ì¬¹ÜÀíº¯Êý (DMF) ·µ»ØµÄ·þÎñÆ÷״̬ÐÅÏ¢¿ÉÓÃÓÚ¼à¿Ø·þÎñÆ÷ʵÀýµÄÔËÐÐ×´¿ö¡¢Õï¶ÏÎÊÌâºÍÓÅ»¯ÐÔÄÜ¡£
³£¹æ·þÎñÆ÷¶¯Ì¬¹ÜÀí¶ÔÏó°ü ......