mysql Âý²éѯ²éѯºÍÒýÆðÔÒò·ÖÎö
MySQLÓÐÒ»¸ö¹¦ÄܾÍÊÇ¿ÉÒÔlogÏÂÀ´ÔËÐеıȽÏÂýµÄsqlÓï¾ä£¬Ä¬ÈÏÊÇûÓÐÕâ¸ölogµÄ£¬ÎªÁË¿ªÆôÕâ¸ö¹¦ÄÜ£¬ÒªÐÞ¸Ämy.cnf»òÕßÔÚMySQLÆô¶¯µÄʱºò¼ÓÈëһЩ²ÎÊý¡£
Èç¹ûÔÚmy.cnfÀïÃæÐ޸ģ¬ÐèÔö¼ÓÈçϼ¸ÐÐ
long_query_time = 1
log-slow-queries =
log-queries-not-using-indexes
long_query_time ÊÇÖ¸Ö´Ðг¬¹ý¶à¾ÃµÄsql»á±»logÏÂÀ´£¬ÕâÀïÊÇ1Ãë¡£
log-slow-queries ÉèÖðÑÈÕ־дÔÚÄÇÀ¿ÉÒÔΪ¿Õ£¬ÏµÍ³»á¸øÒ»¸öȱʡµÄÎļþlog-queries-not-using-indexes ¾ÍÊǼͼûʹÓÃË÷ÒýµÄsql
·ÖÎö£º
mysqldumpslow –helpÒÔÏ£¬Ö÷ÒªÓõÄÊÇ
-s ORDER what to sort by (t, at, l, al, r, ar etc), ‘at’ is default
-t NUM just show the top n queries
-g PATTERN grep: only consider stmts that include this string
-s£¬ÊÇorderµÄ˳Ðò£¬ËµÃ÷дµÄ²»¹»Ïêϸ£¬°³ÓÃÏÂÀ´£¬°üÀ¨¿´ÁË´úÂ룬Ö÷ÒªÓÐ
c,t,l,rºÍac,at,al,ar£¬·Ö±ðÊǰ´ÕÕquery´ÎÊý£¬Ê±¼ä£¬lockµÄʱ¼äºÍ·µ»ØµÄ¼Ç¼ÊýÀ´ÅÅÐò£¬Ç°Ãæ¼ÓÁËaµÄʱµ¹Ðð
-t£¬ÊÇtop nµÄÒâ˼£¬¼´Îª·µ»ØÇ°Ãæ¶àÉÙÌõµÄÊý¾Ý
-g£¬ºó±ß¿ÉÒÔдһ¸öÕýÔòÆ¥Åäģʽ£¬´óСд²»Ãô¸ÐµÄ
mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log
ÉÏÊöÃüÁî¿ÉÒÔ¿´³ö·ÃÎÊ´ÎÊý×î¶àµÄ20¸ösqlÓï¾äºÍ·µ»Ø¼Ç¼¼¯×î¶àµÄ20¸ösql¡£
mysqldumpslow -t 10 -s t -g “left join” host-slow.log
Õâ¸öÊǰ´ÕÕʱ¼ä·µ»ØÇ°10ÌõÀïÃæº¬ÓÐ×óÁ¬½ÓµÄsqlÓï¾ä¡£
Time: 060908 22:17:43
# Query_time: 12 Lock_time: 0 Rows_sent: 86345 Rows_examined: 580963
Q:Õâ¸öÊÇÂý²éµÄÈÕÖ¾,¶¼ÊÇЩʲôÒâ˼?
A:²éѯÓÃÁË12Ã·µ»Ø86345ÐУ¬Ò»¹²²éÁË580963ÐÐ
²éѯËÙ¶ÈÂýµÄÔÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ£º
¡¡¡¡1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
¡¡¡¡2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
¡¡¡¡3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
¡¡¡¡4¡¢ÄÚ´æ²»×ã
¡¡¡¡5¡¢ÍøÂçËÙ¶ÈÂý
¡¡¡¡6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó(¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäËûµÄ·½·¨½µµÍÊý¾ÝÁ¿)
¡¡¡¡7¡¢Ëø»òÕßËÀËø(ÕâÒ²ÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
¡¡¡¡8¡¢sp_lock,sp_who,»î¶¯µÄÓû§²é¿´,ÔÒòÊǶÁд¾ºÕù×ÊÔ´¡£
¡¡¡¡9¡¢·µ»ØÁ˲»±ØÒªµÄÐкÍÁÐ
¡¡¡¡10¡¢²éѯÓï¾ä²»ºÃ£¬Ã»ÓÐÓÅ»¯
Ïà¹ØÎĵµ£º
À´Ô´£ºhttp://blog.csdn.net/ldb2741/archive/2010/02/25/5325161.aspx
×öÏîĿʱÓÉÓÚÒµÎñÂß¼µÄÐèÒª£¬±ØÐë¶ÔÊý¾Ý±íµÄÒ»Ðлò¶àÐмÓÈëÐÐËø£¬¾Ù¸ö×î¼òµ¥µÄÀý×Ó£¬Í¼Êé½èÔÄϵͳ¡£¼ÙÉè id=1 µÄÕâ±¾Êé¿â´æÎª 1 £¬µ«ÊÇÓÐ 2 ¸öÈËͬʱÀ´½èÕâ±¾Ê飬´Ë´¦µÄÂ߼Ϊ
view plaincopy to clipboardprint?
Select restnum&nbs ......
Twitter¹«Ë¾Ò»Î»Ãû½ÐRyan KingµÄ¹¤³ÌʦÈÕǰÏò²©¿ÍMyNoSQL͸¶£¬¹«Ë¾¼Æ»®´ÓMySQLÇ¨ÒÆµ½CassandraÊý¾Ý¿â£¬ÒòΪºóÕß¾ßÓиü´óµÄµ¯ÐÔ¡¢¿ÉÀ©Õ¹ÐԺʹóÁ¿µÄÉçÇøÍøÂ翪Դ¿ª·¢ÈËÔ±¡£
ÎÒÃÇÓдóÁ¿µÄÊý¾Ý£¬ÔÚÊý¾Ý¾Þ´ó£¬Ôö³¤ÂÊÕýÔÚ¼ÓËÙµÄÇé¿öÏ£¬ÎÒÃÇÐèÒªÒ»¸öϵͳ£¬Ëü¿ÉÒÔ¸üΪ×Ô¶¯»¯£¬²¢¸ß¶È¿É¿¿¡¢¿ÉÓá£Ryan K ......
×öÏîĿʱÓÉÓÚÒµÎñÂß¼µÄÐèÒª£¬±ØÐë¶ÔÊý¾Ý±íµÄÒ»Ðлò¶àÐмÓÈëÐÐËø£¬¾Ù¸ö×î¼òµ¥µÄÀý×Ó£¬Í¼Êé½èÔÄϵͳ¡£¼ÙÉè id=1 µÄÕâ±¾Êé¿â´æÎª 1 £¬µ«ÊÇÓÐ 2 ¸öÈËͬʱÀ´½èÕâ±¾Ê飬´Ë´¦µÄÂ߼Ϊ
view plaincopy to clipboardprint?
Select restnum from book where id =1 ; ......
1.²é¿´MySQL·ÖÇø±íµÄÖ´Ðмƻ®:
×¢Òâ:¶ÔMySQL·ÖÇø±í°´·ÖÇø²éѯ,²»ÓÃÏñOracleÄÇÑù,ÔÚSQLÖÐÌí¼Ó“PARTITION”×Ö·û´®¡£
Ìáʾ£ºÊ¹Ó÷ÖÇø¹¦ÄÜÖ®ºó£¬Ïà¹Ø²éѯ×îºÃ¶¼ÓÃEXPLAIN PARTITIONS¹ýÒ»±é£¬È·ÈÏ·ÖÇøÊÇ·ñÉúЧ¡£ÀýÈç:
EXPLAIN PARTITIONS SELECT * from tab_test WHERE created = '2008-01-02';
2.²é¿´MySQLÆÕͨ± ......
Ò»ÏîÄ¿¿ª·¢
ÐèÇó·ÖÎö¡¢ÏµÍ³·ÖÎö¡¢ÏµÍ³Éè¼Æ¡¢³ÌÐò¿ª·¢¡¢¼¯³ÉºÍ²âÊÔ
¶þ¡¢ÏîÄ¿·Ö¹¤
³ÌÐòÉè¼Æ¡¢¹²ÓÃ×é¼þÉè¼Æ¡¢Êý¾Ý¿â¡¢ÃÀ¹¤¡¢Îļþ±àд¡¢Êý¾ÝÊäÈë¡¢°²×°Î¬»¤¿Í·þ
Èý¡¢MySQL
Ëٶȿ죬֧³Ö¶àÏß³ÌÓë¶àÓû§Í¬Ê±ÔÚÏߵĹØÏµÊý¾Ý¿â¹ÜÀíϵͳ
»ù±¾µÄMySQLÃüÁîµÄ¼ÇÒä
ËÄ¡¢HTML
»ù±¾htmlµÄÓï¾ä ......