dz̸MySQLÂý²éѯÈÕÖ¾¼°microslow patch(msl patch)
mysql(°üÀ¨ËùÓÐ5.*ϵÁÐ,5.1.21֮ǰ)µÄÂý²éѯÈÕ־ĬÈÏÊÇÒÔÃëΪµ¥Î»µÄ,¿ÉÒÔͨ¹ýset [session|global] long_query_time=1Õâ¸ö×îµÍÒ²¾ÍÊÇ¿ÉÒÔÉèÖõ½1Ãë,¶ÔÓÚ0.5,0.005Ö®Àà¾ÍÎÞÄÜΪÁ¦ÁË,Õâʱºò¿ÉÒÔÓÐmicroslow patch(msl patch)²¹¶¡À´Íê³ÉÕâÒ»¹¤×÷,Õâ¸ö¹¦Äܵĺô¦×ÔÈ»²»ÑÔ¶øÓ÷. ÔÚmysql5.1.21¼°ÒÔºó°æ±¾¿ÉÒÔͨ¹ýset [session|global] long_query_time=0.01µÈÀ´ÉèÖüǼ0.01ÃëÒÔÉϵÄsqlÓï¾ä. ÔÚ5.1.6Ö®ºóϵÁвúÆ·Öл¹ÓÐ--log-output=[FILE|TABLE|NONE]ÌØÐÔ,Õâ¸öÊÇÑ¡Ôñgeneral_logºÍslow_logµÄ´æ´¢·½Ê½,Îļþ,Êý¾Ý±í,»¹ÊDz»¼Ç¼,5.1.6-5.1.20ĬÈÏÊÇÊý¾Ý±í(mysql.slow_log±í),ÆäÓàĬÈÏÊÇÎļþ. --log-queries-not-using-indexesÒ²ÊÇ5.1µÄÐÂÑ¡Ïî,¿ÉÒÔûÓÐʹÓÃindexµÄsqlÓï¾ä¼Ç¼µ½Âý²éѯÈÕÖ¾ÖÐ --log-slow-admin-statements¸ÃÃüÁîÑ¡Ïî¿ÉÒÔ½«OPTIMIZE TABLE, ANALYZE TABLE, ÒÔ¼°ALTER TABLE µÈ¹ÜÀí²Ù×÷дµÀÂý²éѯÈÕÖ¾ÖÐ. ÔÚ5.0ϵÁвúÆ·¿ÉÒÔͨ¹ý°²×°microslow patch(msl patch)À´¸Ä½øÂý²éѯÈÕÖ¾. ÕâЩѡÏî¿ÉÒÔ¼ÓÔÚÆô¶¯ÃüÁîÉÏÒ²¿ÉÒԼǼÔÚmy.cnfÀïÃæ ÖÁÓڲ鿴ÈÕÖ¾,¿ÉÒÔÖ±½Ó²é¿´Ò²¿ÉÒÔʹÓù¤¾ß,¶ÔÓڱȽ϶àµÄÈÕÖ¾»¹ÊÇÈÕÖ¾·½±ãЩ.³£¼ûµÄ¹¤¾ßÓÐmysql×Ô´øµÄmysqldumpslowÒÔ¼°µÚÈý·½¹¤¾ßmysqlslaµÈ. msl patchÊÇͨ¹ýÐÞ¸ÄmysqlÔ´´úÂëµÄ·½Ê½À´ÔöÇ¿mysqlµÄ¹¦ÄÜ,ËùÒÔÖ»ÄÜÊÇÔ´Âë°²×°²Å¿ÉÒÔÀ©Õ¹Õâ¸ö¹¦ÄÜ,ÆäÔÚwww.mysqlperformanceblog.comÉÏÓÐÏêϸ½éÉÜ(ÆäʵӢÓïºÜ¼òµ¥,ºÙºÙ) ÓÐƪÎÄÕÂÏêϸ˵Ã÷ÁËmicroslow patchµÄ°²×°ºÍʹÓÃÕª¼¸¸öÑ¡Ïî·ÅÔÚÕâ·½±ãÒÔºó²é¿´ Configuration There are several parameters related to slow log you can set with patch applied. All filter-type options work in conjunction meaning that in order for query to be logged it must match long_query_time AND min_examined_row_limit AND log_slow_filter.
log-slow-queries[=name]
Log slow queries to this log file. Defaults logging to hostname-slow.log file. Must be enabled to activate other slow log options. This is the most important one as it enables the logging. If you don¡¯t specify it in my.cnf file, the remaining part of the configuration will not matter, because the log file won¡¯t be created. This is also the only option which you cannot change at runtime from MySQL console wit SET or SET GLOBAL com
Ïà¹ØÎĵµ£º
L1¡¢HTML¾²Ì¬»¯
Æäʵ´ó¼Ò¶¼ÖªµÀ£¬Ð§ÂÊ×î¸ß¡¢ÏûºÄ×îСµÄ¾ÍÊÇ´¿¾²Ì¬»¯µÄhtmlÒ³Ã棬ËùÒÔÎÒÃǾ¡¿ÉÄÜʹÎÒÃǵÄÍøÕ¾ÉϵÄÒ³Ãæ²É Óþ²Ì¬Ò³ÃæÀ´ÊµÏÖ£¬Õâ¸ö×î¼òµ¥µÄ·½·¨ÆäʵҲÊÇ×îÓÐЧµÄ·½·¨¡£µ«ÊǶÔÓÚ´óÁ¿ÄÚÈݲ¢ÇÒƵ·±¸üеÄÍøÕ¾£¬ÎÒÃÇÎÞ·¨È«²¿ÊÖ¶¯È¥°¤¸öʵÏÖ£¬ÓÚÊdzöÏÖÁËÎÒÃdz£¼ûµÄÐÅÏ¢ ·¢²¼ÏµÍ³CMS£¬ÏñÎÒÃdz£·ÃÎʵĸ÷¸öÃÅ»§Õ¾µãµÄÐ ......
InnoDBÊÇmysqlÊý¾Ý¿âÖÐΨһһ¸öÖ§³ÖÍâ¼üµÄÒýÇ棬µ«ÊDzîÖµµÄʱºòÈ´µÃ×¢ÒâÒ»ÏÂ
±ÈÈçÓÐÁ½ÕÅ±í£¬album(id,name,path) £¬picture(id,name,path,parent_album)£¬ÆäÖбí2ÖÐparent_albumÊÇÍâ¼ü
´Ëʱ²îÖµinsert into picture (name,path,parent_album) values ("pic","path",68) (68ÊÇÒ»¸öÏà²áµÄid)
mysql»á±¨´í"Failed to read a ......
ŪÁËÒ»ÍíÉÏ£¬²éÁËn¶àÎÄÕ£¬³¢ÊÔÁËn¶à¸´Ôӵķ½·¨£¬×îºóÖÕÓÚÔÚһƪÎÄÕÂÖÐÕÒµ½ÁËÒ»¸ö³¬¼òµ¥µÄ·½·¨£¬ÕæÊÇ̤ÆÆÌúЬÎÞÃÙ´¦°¡£¡²»Óð´Õչܷ¨µÄ×ö·¨£¬Ö»Òª°´ÕÕÏÂÎĵķ½·¨±£¹ÜÄã¿ÉÒԳɹ¦ÅäÖÃtomcatµÄÁ¬½Ó³Ø¡£
½«tomcat/conf/context.xmlÎļþÖÃΪÒÔÏÂÄÚÈÝ¡£
<Context reloadable="true">
< ......
ÓкܶàÅóÓÑËäÈ»°²×°ºÃÁËmysqlµ«È´²»ÖªÈçºÎʹÓÃËü¡£ÔÚÕâƪÎÄÕÂÖÐÎÒÃǾʹÓÁ¬½ÓMYSQL¡¢ÐÞ¸ÄÃÜÂë¡¢Ôö¼ÓÓû§µÈ·½ÃæÀ´Ñ§Ï°Ò»Ð©MYSQLµÄ³£ÓÃÃüÁî¡£
Ò»¡¢Á¬½ÓMYSQL¡£
¸ñʽ£º mysql -hÖ÷»úµØÖ· -uÓû§Ãû £pÓû§ÃÜÂë
1¡¢Àý1£ºÁ¬½Óµ½±¾»úÉϵÄMYSQL¡£
Ê×ÏÈÔÚ´ò¿ªDOS´°¿Ú£¬È»ºó½øÈëĿ¼ mysqlbin£¬ÔÙ¼üÈëÃüÁîmysql -uroot -p£¬»Ø³µ ......
Analyze Table
MySQL µÄOptimizer£¨ÓÅ»¯Ôª¼þ£©ÔÚÓÅ»¯SQLÓï¾äʱ£¬Ê×ÏÈÐèÒªÊÕ¼¯Ò»Ð©Ïà¹ØÐÅÏ¢£¬ÆäÖоͰüÀ¨±íµÄcardinality£¨¿ÉÒÔ·ÒëΪ“É¢Áг̶Ȕ£©£¬Ëü±íʾij¸öË÷Òý¶ÔÓ¦µÄÁаüº¬¶àÉÙ¸ö²»Í¬µÄÖµ——Èç¹ûcardinality´ó´óÉÙÓÚÊý¾ÝµÄʵ¼ÊÉ¢Áг̶ȣ¬ÄÇôË÷Òý¾Í»ù±¾Ê§Ð§ÁË¡£
ÎÒÃÇ¿ÉÒÔʹÓÃSHOW& ......