SQL SERVER ÈçºÎ¶ÁÈ¡Ò³ÃæÉϵÄÊý¾Ý
CSDNÉÏÓÐÆªÌû×Óhttp://topic.csdn.net/u/20100223/15/644e6212-9fdc-42de-81ad-785d28ed71d3.html
ÌÖÂÛ²éѯ¼Æ»®¶ÁÈ¡Ë÷ÒýÒ³µÄÎÊÌâ¡£
Ö÷ÒªÎÊÌâÊÇ£º ÔÚÒ»ÕŽ¨Óоۼ¯Ë÷Òý£¨Ã»ÓÐÆäËü·Ç¾Û¼¯Ë÷Òý£©µÄ±íÖ´ÐÐselect count(*) ¡£²éѯÏÔʾɨÃèÁËËùÓеÄË÷ÒýÖмä²ãÒ¶¡£´ÓÀíÂÛÉϽ²£¬ÓÉÓÚÿ¸öÊý¾ÝÒ³¶¼¼Ç¼×ÅËüµÄÉÏÒ»¸öpageºÍÏÂÒ»¸öpage,ÄÇô×îСµÄio¶ÁȡӦ¸ÃÊÇ:
¶Á¸ùÒ³--->¶Á×îСµÄÖмäÒ³--->¶ÁÊý¾ÝÒ¶×Ó---->ÒÀ´ÎÍùºó¶ÁÈ«²¿Êý¾ÝÒ³.
µ«Êµ¼ÊÉÏÈ´ÊǶÁȡȫ²¿µÄË÷ÒýÖмäÒ³¡£
Ìû×ÓÖбȽϾ«²ÊµÄ»Ø¸´£º
Ê×ÏÈÂ¥Ö÷µÄ˵·¨²»¹»×¼È·£¬¸ù¾ÝÎҵIJâÊÔ½á¹ûÀ´¿´£¬¸ü׼ȷµÄ˵·¨Ó¦¸ÃÊÇsql serverÔÚ×öË÷Òýȫɨ(index full scan)ʱ£¬»áÏÈɨÃèÈ«²¿µÄlevel 1µÄ½Úµã£¬È»ºóÔÚɨÃè´¦ÓÚlevel 0µÄÒ¶½Úµã¡£Ò²¾ÍÊÇ˵һ´ÎË÷ÒýȫɨµÄ˳ÐòÊÇ£¨Õâ¸ö˳Ðò¶ÔÓÚclustered indexºÍÆÕͨindex¶¼ÊÇÒ»ÑùµÄ£©£º
¶Á¸ùÒ³--->¶Á×îСµÄÖмäÒ³£¨level 1ÒÔÉϵIJ㣩--->ÒÀ´Î¶Álevel 1µÄËùÓнڵãµÄË÷ÒýÒ³---->¶ÁÈ«²¿Êý¾ÝÒ³
ÉÏÊö˳Ðò¿ÉÒÔÔÚ½«Êý¾Ý±íË÷Òý²ãÊýÔö¼Óµ½3²ãÒÔÉÏʱµÃµ½ÑéÖ¤¡£
ʵ¼ÊÉÏÎÒ¾õµÃsql serverÕâÑù×öÊǺÜÓеÀÀíµÄ£¬Â¥Ö÷ÈÏΪµÄÊÇËù¶ÁµÄÒ³Ô½ÉÙ£¬io¾ÍԽС£¬ÕâÊDz»¶ÔµÄ¡£¶ÔÓÚsql serverÀ´Ëµ£¬Ò»´Îio²¢²»Ò»¶¨Ö»¶Áȡһ¸öÒ³£¬ºÜ¶àʱºòÌØ±ðÊÇ×ö±íȫɨ»òË÷Òýȫɨʱ£¬sql server¶¼»á¾¡Á¿µÄÈÃÒ»´Îio¶ÁÈ¡¾¡¿ÉÄܶàµÄÊý¾ÝÒ³£¬ÕâÑù²ÅÄܽÏÉÙioµÄ´ÎÊý¡£Îª´ïµ½Õâ¸öÄ¿µÄ£¬sql serverÊ×ÏȾÍÒªÖªµÀËüµ½µ×ÐèÒª¶ÁÈ¡ÄÇЩҳ£¬ÖªµÀÒª¶ÁȡҳµÄ·Ö²¼Ö®ºó£¬¾Í¿ÉÒÔ°²ÅÅioµ÷¶ÈÆ÷¾¡¿ÉÄܵĽ«ÁÙ½üµÄÊý¾ÝÒ³ÓÃÒ»´Îio¶ÁÈ¡ÉÏÀ´£¬¶øÒªÖªµÀÐèÒª¶ÁÈ¡Êý¾ÝÒ³µÄ·Ö²¼¾ÍÐèÒªÏȶÁÈ¡µ½level 1ÉϵÄËùÓÐÒ³£¬ÒÔÕÒ³öÒª¶ÁÈ¡ioÒ³µÄ·Ö²¼Çé¿ö£¬È»ºóÔÙ×ö×îÓŵÄio¶ÁÈ¡°²ÅÅ¡£
ͬÑùµÄµÀÀí£¬Èç¹ûsql serverÊÇÏȶ¨Î»µ½Ò¶½ÚµãµÄÆðʼÊý¾ÝÒ³£¬È»ºó´ÓÒÀ´ÎµÄ¶ÁÈ¡ËùÓеÄÊý¾ÝÒ³µÄ»°£¬ÄÇsql server¾ÍÖ»ÄÜÒ»´Îio¶Áȡһ¸öÊý¾ÝÒ³£¬È»ºóÕÒ³ö¶ÔÓ¦µÄÏÂÒ»Ò³µÄÖ¸Õ룬ÔÙÈ¥¶ÁÈ¡ÏÂÒ»¸öÊý¾ÝÒ³£¬ÕâÑùʵ¼ÊÉÏ»¨·ÑµÄio»áÔ¶±ÈÅúÁ¿¶ÁÈ¡Êý¾ÝÒ³¶àºÜ¶àµÄ¡£Õâ¾ÍºÃ±ÈÎÒÃÇÔÚ×ö±íµÄlookupʱ£¬Èç¹ûÐèÒªlookupµÄÐзdz£Ö®¶àµÄ»°sql server»áת¶øÑ¡Ôñ±íɨÃèÀ´´ïµ½Ä¿µÄµÄ¡£
Ô¤¶Á
ÎÒ¾õµÃSQL ServerÄÚ²¿´æÔÚÕâÑùÒ»¸ö»úÖÆ:
Ëü»á¾¡Á¿½«µ±Ç°Ê¹Óõļ°Ê¹ÓÃ×îÆµ·±µÄ±íµÄÊý¾Ý¶¼´øµ½bufferÖÐÀ´£¬Õâ¸úbufferµÄËã·¨ÓÐÒ»¶¨µÄ¹ØÏµ¡£
ÇåµôbufferáᣬÄã²»×öÈκζ¯×÷£¬¸ô¼¸ÃëÖÓ£¬sql»á×Ô¶¯½«master¿âÖÐһЩ±È½ÏÖØÒªµÄ±í¶¼´øµ½bufferÖУ¬»òÕß
Äãselect * from tb where 1 <>1 £¬¾¡¹
Ïà¹ØÎĵµ£º
1:replace º¯Êý
µÚÒ»¸ö²ÎÊýÄãµÄ×Ö·û´®£¬µÚ¶þ¸ö²ÎÊýÄãÏëÌæ»»µÄ²¿·Ö£¬µÚÈý¸ö²ÎÊýÄãÒªÌæ»»³Éʲô
select replace('lihan','a','b')
&nb ......
detachedCriteria.add(Restrictions.or(
Restrictions.like("chengBanDanWeiIds", rForm.getChengBanDanWeiIds()+(char)13, MatchMode.START),
Restrictions.or(
&n ......
±à¼Ç°ÑÔ£ºÕâ¸öÎÄÕÂÎÒûÓвâÊÔ£¬µ«Ç°ÌáÌõ¼þ»¹ÊǺܶ࣬±ÈÈçÒ»¶¨ÒªÓбðµÄ³ÌÐò´æÔÚ£¬¶øÇÒÒ²ÒªÓÃͬһ¸öSQLSERVER¿â£¬»¹µÃ¼ÙÉèÓÐ×¢Èë©¶´¡£Ëµµ½µ×ºÍ¶¯ÍøÃ»ÓÐʲô¹ØÏµ£¬µ«ÒòΪ¶¯ÍøÂÛ̳µÄ¿ª·ÅÐÔ£¬ÈÃÈËÊìϤÁËÆäÊý¾Ý¿â½á¹¹£¬ºÍ³ÌÐòÔË×÷·½·¨¡£ÔÚÒ»²½²½µÄ¹¥»÷ÖÐÈ¡µÃ¹ÜÀíȨÏÞ£¬ÔÙÒ»²½²½µÄÌáÉýȨÏÞ£¬Èç¹ûÕýºÃÊý¾Ý¿âÓõÄÊÇSAÕʺţ¬¾Í¸üÊÇ ......
sql2000º¯Êý´óÈ«1 µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ
ÀýÈ磺ÏòÈÕÆÚ¼ÓÉÏ2Ìì
select dateadd(day,2,'2004-10-15') --·µ»Ø£º2004-10-17 00:00:00.000
3. datediff ·µ»Ø¿çÁ½¸öÖ¸¶¨ÈÕÆÚµÄÈ ......
Íâ¼ü
======================
Íâ¼üÊÇÏà¶ÔÓÚÖ÷¼ü˵µÄ£¬Êǽ¨Á¢±íÖ®¼ä µÄÁªÏµµÄ±ØÐëµÄǰÌá¡£
±ÈÈ磺ѧÉú±í ¡¢Ñ§Éú³É¼¨±íÒ»Ò»¶ÔÓ¦ÊÇÒòΪ ËûÃǶ¼¾ßÓÐÏàͬµÄ×ֶΣºÑ§ºÅ£¬°ÑѧÉú±í×÷ΪÖ÷±í£¬Ñ§ºÅÊÇËûµÄÖ÷¼ü£¬Ïà¶ÔÓÚÖ÷±íÀ´Ëµ£¬Ñ§Éú³É¼¨µÄ×ֶΠѧºÅ¾ÍÊÇѧÉú±íµÄÍâ¼ü¡£
ûÓÐÍâ¼ü£¬Á½¸ö±í¾Íû°ì·¨½¨Á¢ÁªÏµ°¡£¡ ......