Ó°ÏìSQL serverÐÔÄܵĹؼüÈý¸ö·½Ãæ

¡¶Ó°ÏìSQL serverÐÔÄܵĹؼüÈý¸ö·½Ãæ¡·ÕªÒª£º 1 Âß¼­Êý¾Ý¿âºÍ±íµÄÉè¼Æ Êý¾Ý¿âµÄÂß¼­Éè¼Æ¡¢°üÀ¨±íÓë±íÖ®¼äµÄ¹ØÏµÊÇÓÅ»¯¹ØÏµÐÍÊý¾Ý¿âÐÔÄܵĺËÐÄ¡£Ò»¸öºÃµÄÂß¼­Êý¾Ý¿âÉè¼Æ¿ÉÒÔΪÓÅ»¯Êý¾Ý¿âºÍÓ¦ÓóÌÐò´òÏÂÁ¼ºÃµÄ»ù´ £ ±ê×¼»¯µÄÊý¾Ý¿âÂß¼­Éè¼Æ°ü¡­

1 Âß¼­Êý¾Ý¿âºÍ±íµÄÉè¼Æ

Êý¾Ý¿âµÄÂß¼­Éè¼Æ¡¢°üÀ¨±íÓë±íÖ®¼äµÄ¹ØÏµÊÇÓÅ»¯¹ØÏµÐÍÊý¾Ý¿âÐÔÄܵĺËÐÄ¡£Ò»¸öºÃµÄÂß¼­Êý¾Ý¿âÉè¼Æ¿ÉÒÔΪÓÅ»¯Êý¾Ý¿âºÍÓ¦ÓóÌÐò´òÏÂÁ¼ºÃµÄ»ù´¡¡£

±ê×¼»¯µÄÊý¾Ý¿âÂß¼­Éè¼Æ°üÀ¨ÓöàµÄ¡¢ÓÐÏ໥¹ØÏµµÄÕ­±íÀ´´úÌæºÜ¶àÁеij¤Êý¾Ý±í¡£ÏÂÃæÊÇһЩʹÓñê×¼»¯
±íµÄһЩºÃ´¦¡£

A:ÓÉÓÚ±íÕ­£¬Òò´Ë¿ÉÒÔʹÅÅÐòºÍ½¨Á¢Ë÷Òý¸üΪѸËÙ

B:ÓÉÓÚ¶à±í£¬ËùÒÔ¶àïßµÄË÷Òý³ÉΪ¿ÉÄÜ

C:¸üÕ­¸ü½ô´ÕµÄË÷Òý

D:ÿ¸ö±íÖпÉÒÔÓÐÉÙһЩµÄË÷Òý£¬Òò´Ë¿ÉÒÔÌá¸ßinsert update deleteµÈµÄËÙ¶È£¬ÒòΪÕâЩ²Ù×÷ÔÚË÷Òý¶àµÄÇé¿öÏ»á¶ÔϵͳÐÔÄܲúÉúºÜ´óµÄÓ°Ïì

E:¸üÉٵĿÕÖµºÍ¸üÉٵĶàÓàÖµ£¬Ôö¼ÓÁËÊý¾Ý¿âµÄ½ô´ÕÐÔ

ÓÉÓÚ±ê×¼»¯£¬ËùÒÔ»áÔö¼ÓÁËÔÚ»ñÈ¡Êý¾ÝʱÒýÓñíµÄÊýÄ¿ºÍÆä¼äµÄÁ¬½Ó¹ØÏµµÄ¸´ÔÓÐÔ¡£Ì«¶àµÄ±íºÍ¸´ÔÓµÄÁ¬½Ó¹ØÏµ»á½µµÍ·þÎñÆ÷µÄÐÔÄÜ£¬Òò´ËÔÚÕâÁ½ÕßÖ®¼äÐèÒª×ۺϿ¼ÂÇ¡£¶¨Òå¾ßÓÐÏà¹Ø¹ØÏµµÄÖ÷¼üºÍÍâÀ´¼üʱӦ¸Ã×¢ÖØµÄÊÂÏîÖ÷ÒªÊÇ£ºÓÃÓÚÁ¬½Ó¶à±íµÄÖ÷¼üºÍ²Î¿¼µÄ¼üÒªÓÐÏàͬµÄÊý¾ÝÀàÐÍ¡£

2 Ë÷ÒýµÄÉè¼Æ

A:¾¡Á¿±ÜÃâ±íɨÃè
¼ì²éÄãµÄ²éѯÓï¾äµÄwhere×Ӿ䣬ÒòΪÕâÊÇÓÅ»¯Æ÷ÖØÒª¹Ø×¢µÄµØ·½¡£°üº¬ÔÚwhereÀïÃæµÄÿһÁУ¨column)¶¼ÊÇ¿ÉÄܵĺîÑ¡Ë÷Òý£¬ÎªÄÜ´ïµ½×îÓŵÄÐÔÄÜ£¬¿¼ÂÇÔÚÏÂÃæ¸ø³öµÄÀý×Ó£º¶ÔÓÚÔÚwhere×Ó¾äÖиø³öÁËcolumn1Õâ¸öÁС£

ÏÂÃæµÄÁ½¸öÌõ¼þ¿ÉÒÔÌá¸ßË÷ÒýµÄÓÅ»¯²éѯÐÔÄÜ£¡
µÚÒ»£ºÔÚ±íÖеÄcolumn1ÁÐÉÏÓÐÒ»¸öµ¥Ë÷Òý
µÚ¶þ£ºÔÚ±íÖÐÓжàË÷Òý£¬µ«ÊÇcolumn1ÊǵÚÒ»¸öË÷ÒýµÄÁÐ
±ÜÃⶨÒå¶àË÷Òý¶øcolumn1Êǵڶþ¸ö»òºóÃæµÄË÷Òý£¬ÕâÑùµÄË÷Òý²»ÄÜÓÅ»¯·þÎñÆ÷ÐÔÄÜ

ÀýÈ磺ÏÂÃæµÄÀý×ÓÓÃÁËpubsÊý¾Ý¿â¡£
SELECT au_id, au_lname, au_fname FROM authors
WHERE au_lname = 'White'
°´ÏÂÃæ¼¸¸öÁÐÉϽ¨Á¢µÄË÷Òý½«»áÊǶÔÓÅ»¯Æ÷ÓÐÓõÄË÷Òý
au_lname
au_lname, au_fname
¶øÔÚÏÂÃæ¼¸¸öÁÐÉϽ¨Á¢µÄË÷Òý½«²»»á¶ÔÓÅ»¯Æ÷Æðµ½ºÃµÄ×÷ÓÃ
au_address
au_fname, au_lname
¿¼ÂÇʹÓÃÕ­µÄË÷ÒýÔÚÒ»¸ö»òÁ½¸öÁÐÉÏ£¬Õ­Ë÷Òý±È¶àË÷ÒýºÍ¸´ºÏË÷Òý¸üÄÜÓÐЧ¡£ÓÃÕ­µÄË÷Òý£¬ÔÚÿһҳÉÏ
½«»áÓиü¶àµÄÐк͸üÉÙµÄË÷Òý¼¶±ð£¨Ïà¶ÔÓë¶àË÷ÒýºÍ¸´ºÏË÷Òý¶øÑÔ£©£¬Õâ½«ÍÆ½øÏµÍ³ÐÔÄÜ¡£
¶ÔÓÚ¶àÁÐË÷Òý£¬SQL Serverά³ÖÒ»¸öÔÚËùÓÐÁеÄË÷ÒýÉϵÄÃܶÈͳ¼Æ£¨ÓÃÓÚÁªºÏ£©ºÍÔÚµÚÒ»¸öË÷ÒýÉϵÄ
histogram£¨Öù״ͼ£©Í³¼Æ¡£¸ù¾Ýͳ¼Æ½á¹û£¬¼ÙÈçÔÚ¸´ºÏË÷ÒýÉϵĵÚÒ»¸öË÷ÒýºÜÉÙ±»Ñ¡ÔñʹÓã¬ÄÇôÓÅ»¯Æ÷¶ÔºÜ¶à²éѯÇëÇ󽫲»»áʹÓÃË÷Òý¡£

ÓÐÓõÄË÷Òý»áÌá¸ßselectÓï¾äµÄÐÔÄÜ£¬°üÀ¨insert,uodate,delete¡£
µ«ÊÇ£¬ÓÉÓڸıäÒ»¸ö±íµÄÄÚÈÝ£¬½«»áÓ°ÏìË÷Òý¡£Ã¿Ò»¸öinsert,update,deleteÓï¾ä½«»áʹÐÔÄÜϽµÒ»Ð©¡£ÊµÑé±íÃ÷£¬²»ÒªÔÚÒ»¸öµ¥±íÉÏÓôóÁ¿µÄË÷Òý£¬²»ÒªÔÚ¹²ÏíµÄÁÐÉÏ£¨Ö¸ÔÚ¶à±íÖÐÓÃÁ˲ο¼Ô¼Êø£©Ê¹ÓÃÖØµþµÄË÷Òý¡£
ÔÚijһÁÐÉϼì²éΨһµÄÊý¾ÝµÄ¸öÊý£¬±È½ÏËüÓë±íÖÐÊý¾ÝµÄÐÐÊý×öÒ»¸ö±È½Ï¡£Õâ¾ÍÊÇÊý¾ÝµÄÑ¡ÔñÐÔ£¬Õâ±È½Ï½á¹û½«»á°ïÖúÄã¾ö¶¨ÊÇ·ñ½«Ä³Ò»ÁÐ×÷ΪºîÑ¡µÄË÷ÒýÁУ¬¼ÙÈçÐèÒª£¬½¨ÄÄÒ»ÖÖË÷Òý¡£Äã¿ÉÒÔÓÃÏÂÃæµÄ²éѯÓï¾ä·µ»ØÄ³Ò»ÁеIJ»Í¬ÖµµÄÊýÄ¿¡£

select count(distinct cloumn_name) from table_name
¼ÙÉècolumn_nameÊÇÒ»¸ö10000ÐÐµÄ±í£¬Ôò¿´column_name·µ»ØÖµÀ´¾ö¶¨ÊÇ·ñÓ¦¸ÃʹÓ㬼°Ó¦¸ÃʹÓÃʲôË÷Òý¡£
Unique values Index
5000 Nonclustered index
20 Clustered index
3 No index

ïßË÷ÒýºÍ·ÇïßË÷ÒýµÄÑ¡Ôñ

<1>ïßË÷ÒýÊÇÐеÄÎïÀí˳ÐòºÍË÷ÒýµÄ˳ÐòÊÇÒ»Öµġ£Ò³¼¶£¬µÍ²ãµÈË÷ÒýµÄ¸÷¸ö¼¶±ðÉ϶¼°üº¬Êµ¼ÊµÄÊý¾ÝÒ³¡£Ò»¸ö±íÖ»ÄÜÊÇÓÐÒ»¸öïßË÷Òý¡£ÓÉÓÚupdate,deleteÓï¾äÒªÇóÏà¶Ô¶àһЩµÄ¶Á²Ù×÷£¬Òò´ËïßË÷Òý¾­³£ÄܼÓËÙÕâÑùµÄ²Ù×÷¡£ÔÚÖÁÉÙÓÐÒ»¸öË÷ÒýµÄ±íÖУ¬ÄãÓ¦¸ÃÓÐÒ»¸öïßË÷Òý¡£

ÔÚÏÂÃæµÄ¼¸¸öÇé¿öÏ£¬Äã¿ÉÒÔ¿¼ÂÇÓÃïßË÷Òý£º
ÀýÈ磺 ijÁаüÀ¨µÄ²»Í¬ÖµµÄ¸öÊýÊÇÓÐÏ޵썵«ÊDz»ÊǼ«Éٵģ©
¹Ë¿Í±íµÄÖÝÃûÁÐÓÐ50¸ö×óÓҵIJ»Í¬ÖÝÃûµÄËõдֵ£¬¿ÉÒÔʹÓÃïßË÷Òý¡£

ÀýÈ磺 ¶Ô·µ»ØÒ»¶¨·¶Î§ÄÚÖµµÄÁпÉÒÔʹÓÃïßË÷Òý£¬±ÈÈçÓÃbetween,>,>=,<,<=µÈµÈÀ´¶ÔÁнøÐвÙ×÷µÄÁÐÉÏ¡£
select * from sales where ord_date between '5/1/93' and '6/1/93'
ÀýÈ磺 ¶Ô²éѯʱ·µ»Ø´óÁ¿½á¹ûµÄÁпÉÒÔʹÓÃïßË÷Òý¡£
SELECT * FROM phonebook WHERE last_name = 'Smith'

µ±ÓдóÁ¿µÄÐÐÕýÔÚ±»²åÈë±íÖÐʱ£¬Òª±ÜÃâÔÚ±¾±íÒ»¸ö×ÔÈ»Ôö³¤£¨ÀýÈ磬identityÁУ©µÄÁÐÉϽ¨Á¢ïßË÷Òý¡£¼ÙÈçÄ㽨Á¢ÁËïßµÄË÷Òý£¬ÄÇôinsertµÄÐÔÄܾͻá´ó´ó½µµÍ¡£ÒòΪÿһ¸ö²åÈëµÄÐбØÐëµ½±íµÄ×îºó£¬±íµÄ×îºóÒ»¸öÊý¾ÝÒ³¡£
µ±Ò»¸öÊý¾ÝÕýÔÚ±»²åÈ루ÕâʱÕâ¸öÊý¾ÝÒ³ÊDZ»Ëø¶¨µÄ£©£¬ËùÓÐµÄÆäËû²åÈëÐбØÐëµÈ´ýÖ±µ½µ±Ç°µÄ²åÈëÒѾ­½áÊø¡£
Ò»¸öË÷ÒýµÄÒ¶¼¶Ò³ÖаüÀ¨Êµ¼ÊµÄÊý¾ÝÒ³£¬²¢ÇÒÔÚÓ²ÅÌÉϵÄÊý¾ÝÒ³µÄ´ÎÐòÊǸúïßË÷ÒýµÄÂß¼­´ÎÐòÒ»ÑùµÄ¡£

<2>Ò»¸ö·ÇïßµÄË÷Òý¾ÍÊÇÐеÄÎïÀí´ÎÐòÓëË÷ÒýµÄ´ÎÐòÊDz»Í¬µÄ¡£Ò»¸ö·ÇïßË÷ÒýµÄÒ¶¼¶°üº¬ÁËÖ¸ÏòÐÐÊý¾ÝÒ³µÄÖ¸Õë¡£
ÔÚÒ»¸ö±íÖпÉÒÔÓжà¸ö·ÇïßË÷Òý£¬Äã¿ÉÒÔÔÚÒÔϼ¸¸öÇé¿öÏ¿¼ÂÇʹÓ÷ÇïßË÷Òý¡£

ÔÚÓкܶ಻ֵͬµÄÁÐÉÏ¿ÉÒÔ¿¼ÂÇʹÓ÷ÇïßË÷Òý
ÀýÈ磺һ¸öpart_idÁÐÔÚÒ»¸öpart±íÖÐ
select * from employee where emp_id = 'pcm9809f'
²éѯÓï¾äÖÐÓÃorder by ×Ó¾äµÄÁÐÉÏ¿ÉÒÔ¿¼ÂÇʹÓÃïßË÷Òý

3 ²éѯÓï¾äµÄÉè¼Æ

SQL ServerÓÅ»¯Æ÷ͨ¹ý·ÖÎö²éѯÓï¾ä£¬×Ô¶¯¶Ô²éѯ½øÐÐÓÅ»¯²¢¾ö¶¨×îÓÐЧµÄÖ´Ðз½°¸¡£ÓÅ»¯Æ÷·ÖÎö²éѯÓï¾äÀ´¾ö¶¨ÄǸö×Ó¾ä¿ÉÒÔ±»ÓÅ»¯£¬²¢Õë¶Ô¿ÉÒÔ±»ÓÅ»¯²éѯµÄ×Ó¾äÀ´Ñ¡ÔñÓÐÓõÄË÷Òý¡£×îºóÓÅ»¯Æ÷±È½ÏËùÓпÉÄܵÄÖ´Ðз½°¸²¢Ñ¡Ôñ×îÓÐЧµÄÒ»¸ö·½°¸³öÀ´¡£

ÔÚÖ´ÐÐÒ»¸ö²éѯʱ£¬ÓÃÒ»¸öwhere×Ó¾äÀ´ÏÞÖÆ±ØÐë´¦ÀíµÄÐÐÊý£¬³ý·ÇÍêÈ«ÐèÒª£¬·ñÔòÓ¦¸Ã±ÜÃâÔÚÒ»¸ö±íÖÐÎÞÏÞÖÆµØ¶Á²¢´¦ÀíËùÓеÄÐС£

ÀýÈçÏÂÃæµÄÀý×Ó£¬select qty from sales where stor_id=7131ÊǺÜÓÐЧµÄ±ÈÏÂÃæÕâ¸öÎÞÏÞÖÆµÄ²éѯ
select qty from sales
±ÜÃâ¸ø¿Í»§µÄ×îºóÊý¾ÝÑ¡Ôñ·µ»Ø´óÁ¿µÄ½á¹û¼¯¡£´ðÓ¦SQL ServerÔËÐÐÂú×ãËüÄ¿µÄµÄº¯ÊýÏÞÖÆ½á¹û¼¯µÄ´óСÊǸüÓÐЧµÄ¡£

ÕâÄܼõÉÙÍøÂçI/O²¢ÄÜÌá¸ß¶àÓû§µÄÏà¹Ø²¢·¢Ê±µÄÓ¦ÓóÌÐòÐÔÄÜ¡£ÒòΪÓÅ»¯Æ÷¹Ø×¢µÄ½¹µã¾ÍÊÇwhere×Ó¾äµÄ²éѯ£¬ÒÔÀûÓÃÓÐÓõÄË÷Òý¡£ÔÚ±íÖеÄÿһ¸öË÷Òý¶¼¿ÉÄܳÉΪ°üÀ¨ÔÚwhere×Ó¾äÖеĺîÑ¡Ë÷Òý¡£ÎªÁË×îºÃµÄÐÔÄÜ¿ÉÒÔ×ñÕÕÏÂÃæµÄÓÃÓÚÒ»¸ö¸ø¶¨ÁÐcolumn1µÄË÷Òý¡£

µÚÒ»£ºÔÚ±íÖеÄcolumn1ÁÐÉÏÓÐÒ»¸öµ¥Ë÷Òý
µÚ¶þ£ºÔÚ±íÖÐÓжàË÷Òý£¬µ«ÊÇcolumn1ÊǵÚÒ»¸öË÷ÒýµÄÁв»ÒªÔÚwhere×Ó¾äÖÐʹÓÃûÓÐcolumn1ÁÐË÷ÒýµÄ²éѯÓï¾ä£¬²¢±ÜÃâÔÚwhere×Ó¾äÓÃÒ»¸ö¶àË÷ÒýµÄ·ÇµÚÒ»¸öË÷ÒýµÄË÷Òý¡£Õâʱ¶àË÷ÒýÊÇûÓÐÓõġ£

For example, given a multicolumn index on the au_lname, au_fname columns of the authors table in
the pubs database,

ÏÂÃæÕâ¸öqueryÓï¾äÀûÓÃÁËau_lnameÉϵÄË÷Òý
SELECT au_id, au_lname, au_fname FROM authors
WHERE au_lname = 'White'
AND au_fname = 'Johnson'
SELECT au_id, au_lname, au_fname FROM authors
WHERE au_lname = 'White'

ÏÂÃæÕâ¸ö²éѯûÓÐÀûÓÃË÷Òý£¬ÒòΪËûʹÓÃÁ˶àË÷ÒýµÄ·ÇµÚÒ»¸öË÷ÒýµÄË÷Òý
SELECT au_id, au_lname, au_fname FROM authors
WHERE au_fname = 'Johnson'

(ÔðÈα༭£ºÃúÃú [email protected] TEL£º£¨010£©68476636)


ÄãµÄλÖ㺵çÄÔ¹ÊÕÏÍø >> Êý¾Ý¿â >> SQL Server >> Ó°ÏìSQL serverÐÔÄܵĹؼüÈý¸ö·½Ãæ