<link rel="stylesheet" href="https://csdnimg.cn/release/phoenix/template/css/ck_htmledit_views-833878f763.css">
<link rel="stylesheet" href="https://csdnimg.cn/release/phoenix/template/css/ck_htmledit_views-833878f763.css">
<div class="htmledit_views" id="content_views">
<div class="art_desc mt10" style="margin:10px 5px;padding:10px;color:rgb(51,51,51);border-left-width:3px;border-left-style:solid;border-left-color:rgb(59,176,219);font-size:14px;background:rgb(246,251,255);"><div style="font-family:tahoma, arial, 'Microsoft YaHei';margin:0px;padding:0px;"><p style="background-color:rgb(255,255,255);"><span style="font-weight:700;">MySQL中的日志包括</span>：错误日志、二进制日志、通用查询日志、慢查询日志等等。这里主要介绍下比较常用的两个功能：通用查询日志和慢查询日志。</p><p style="background-color:rgb(255,255,255);"><span style="font-weight:700;">1）通用查询日志</span>：记录建立的客户端连接和执行的语句。</p><p style="background-color:rgb(255,255,255);"><span style="font-weight:700;">2）慢查询日志</span>：记录所有执行时间超过long_query_time秒的所有查询或者不使用索引的查询</p><p style="background-color:rgb(255,255,255);"><span style="font-family:'Courier New';font-size:18px;color:rgb(0,0,102);background-color:rgb(255,255,255);">MySQL日志文件系统的组成</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> a、</span><a href="http://blog.csdn.net/leshami/article/details/39759849" rel="nofollow" style="font-family:'Courier New';background-color:rgb(255,255,255);">错误日志</a><span style="font-family:'Courier New';background-color:rgb(255,255,255);">：记录启动、运行或停止mysqld时出现的问题。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> <strong> b</strong>、</span><a href="http://blog.csdn.net/leshami/article/details/39779225" rel="nofollow" style="font-family:'Courier New';background-color:rgb(255,255,255);">通用日志</a><span style="font-family:'Courier New';background-color:rgb(255,255,255);">：记录建立的客户端连接和执行的语句。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> c、更新日志：记录更改数据的语句。该日志在MySQL 5.1中已不再使用。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> <strong>d</strong>、</span><a href="http://blog.csdn.net/leshami/article/details/39801867" rel="nofollow" style="font-family:'Courier New';background-color:rgb(255,255,255);">二进制日志</a><span style="font-family:'Courier New';background-color:rgb(255,255,255);">：记录所有更改数据的语句。还用于复制。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> <strong>e</strong>、</span><a href="http://write.blog.csdn.net/postedit/39829605" rel="nofollow" style="font-family:'Courier New';background-color:rgb(255,255,255);">慢查询日志</a><span style="font-family:'Courier New';background-color:rgb(255,255,255);">：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> f、Innodb日志：innodb redo log</span><br></p><p style="background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> 缺省情况下，所有日志创建于mysqld数据目录中。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> 可以通过刷新日志，来强制mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> 当你执行一个FLUSH LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时，则日志被老化。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> 对于存在MySQL复制的情形下，从复制服务器将维护更多日志文件，被称为接替日志。</span><br></span></p></div><div style="margin:0px;padding:0px;"><span style="font-family:'Microsoft YaHei';"><span style="background-color:rgb(255,255,255);">同大多数关系型数据库一样，日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件，通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。这些日志可以帮助我们定位mysqld内部发生的事件，数据库性能故障，记录数据的变更历史，用户恢复数据库等等。</span>今天我们要讲的是如何分析和查询mysql慢日志（主要影响程序调用和前端数据呈现）。</span></div><div style="margin:0px;padding:0px;"><br></div><div style="font-family:tahoma, arial, 'Microsoft YaHei';margin:0px;padding:0px;">mysql slow log 是用来记录执行时间较长(超过long_query_time秒)的sql的一种日志工具</div></div><div class="lbd clearfix" style="margin:0px auto;padding:0px;width:700px;text-align:center;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';font-size:14px;line-height:21px;"><span class="jbTestPos"></span></div><div style="margin:0px auto;padding:0px;width:700px;text-align:left;line-height:25.2px;clear:both;"><h3 style="font-family:tahoma, arial, 'Microsoft YaHei';font-size:12pt;color:rgb(34,34,34);margin-top:0px;margin-bottom:0px;padding:0px;">启用 slow log<br></h3><div style="font-family:tahoma, arial, 'Microsoft YaHei';font-size:14px;color:rgb(34,34,34);margin:0px;padding:0px;line-height:25.2px;">有两种启用方式:</div><span style="color:#222222;font-family:tahoma, arial, 'Microsoft YaHei';font-size:14px;">1, 在my.cnf 里 通过 log-slow-queries[=file_name]</span><div style="font-family:tahoma, arial, 'Microsoft YaHei';font-size:14px;color:rgb(34,34,34);margin:0px;padding:0px;line-height:25.2px;"></div><div style="font-family:tahoma, arial, 'Microsoft YaHei';font-size:14px;color:rgb(34,34,34);margin:0px;padding:0px;line-height:25.2px;">2, 在mysqld进程启动时,指定--log-slow-queries[=file_name]选项</div><h3 style="font-family:tahoma, arial, 'Microsoft YaHei';font-size:12pt;color:rgb(34,34,34);margin-top:0px;margin-bottom:0px;padding:0px;">比较的五款常用工具<br></h3><p style="font-family:tahoma, arial, 'Microsoft YaHei';font-size:14px;"><span style="color:#ff0000;">mysqldumpslow</span><span style="color:#222222;">, mysqlsla, myprofi, mysql-explain-slow-log, mysqllogfilter</span></p><p><span style="color:#222222;"><strong><span style="font-family:'Microsoft YaHei';font-size:16px;">开源的MySQL慢日志查询监控工具</span></strong></span></p><p><span style="font-family:'Microsoft YaHei';font-size:14px;"><span style="color:rgb(34,34,34);background-color:rgb(255,255,255);">pmm server、Navicat、mysqltop（天兔</span><span style="line-height:42px;text-align:center;background-color:rgb(255,255,255);"><span style="color:#000000;">Lepus</span></span><span style="color:rgb(34,34,34);background-color:rgb(255,255,255);">）、</span><span style="color:rgb(204,0,0);line-height:20.02px;background-color:rgb(255,255,255);">Box</span><span style="color:rgb(51,51,51);line-height:20.02px;background-color:rgb(255,255,255);"> </span></span><span style="color:rgb(204,0,0);line-height:20.02px;background-color:rgb(255,255,255);"><span style="font-family:'Microsoft YaHei';font-size:14px;">Anemometer、explain、</span></span><span style="color:rgb(51,51,51);line-height:37.7143px;font-family:'Microsoft YaHei';font-size:14px;background-color:rgb(255,255,255);"><strong>pt-query-digest</strong></span></p><p style="font-family:tahoma, arial, 'Microsoft YaHei';font-size:14px;color:rgb(34,34,34);"><span style="font-family:'Courier New';font-size:18px;color:rgb(0,0,102);background-color:rgb(255,255,255);">慢查询日志<br></span><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> 慢查询日志是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件，通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> 通过使用--slow_query_log[={0|1}]选项来启用慢查询日志。所有执行时间超过long_query_time秒的SQL语句都会被记录到慢查询日志。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> 缺省情况下hostname-slow.log为慢查询日志文件安名，存放到数据目录，同时缺省情况下未开启慢查询日志。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> 缺省情况下数据库相关管理型SQL(比如OPTIMIZE TABLE、ANALYZE TABLE和ALTER TABLE)不会被记录到日志。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> 对于管理型SQL可以通过--log-slow-admin-statements开启记录管理型慢SQL。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> mysqld在语句执行完并且所有锁释放后记入慢查询日志。记录顺序可以与执行顺序不相同。获得初使表锁定的时间不算作执行时间。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> </span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> 可以使用mysqldumpslow命令获得日志中显示的查询摘要来处理慢查询日志。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> 用查询缓存处理的查询不加到慢查询日志中，表有零行或一行而不能从索引中受益的查询也不写入慢查询日志。</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> MySQL服务器按以下顺序记录SQL是否写入到慢查询日志</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> a. The query must either not be an administrative statement, or --log-slow-adminstatements must have been specified.</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> b. The query must have taken at least long_query_time seconds, or log_queries_not_using_indexes must be enabled and the query used no indexes for row lookups.</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> c. The query must have examined at least min_examined_row_limit rows.</span><br style="font-family:'Courier New';background-color:rgb(255,255,255);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"> d. The query must not be suppressed according to the log_throttle_queries_not_using_indexes setting.</span></p><p style="font-family:tahoma, arial, 'Microsoft YaHei';font-size:14px;color:rgb(34,34,34);"><span style="font-family:'Courier New';background-color:rgb(255,255,255);"></span></p><p style="border-width:0px;margin-bottom:8px;list-style:none;text-indent:2em;color:rgb(51,51,51);font-family:Simsun;font-size:14px;line-height:28px;background-color:rgb(249,249,249);"><a href="https://www.2cto.com/database/MySQL/" rel="nofollow" class="keylink" style="color:rgb(31,58,135);">mysql</a>dumpslow是mysql自带的用来分析慢查询的工具，当然不止这一种工具，还有percona-toolkit是percona公司出的一组命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的mysql相关任务，包含以下内容：</p><p style="border-width:0px;margin-bottom:8px;list-style:none;text-indent:2em;color:rgb(51,51,51);font-family:Simsun;font-size:14px;line-height:28px;background-color:rgb(249,249,249);">检查master和slave数据一致性/记录有效的归档/服务器信息汇总/分析和统计日志,为了省事这块使用mysqldumpslow命令做分析。</p><p style="border-width:0px;margin-bottom:8px;list-style:none;text-indent:2em;color:rgb(51,51,51);font-family:Simsun;font-size:14px;line-height:28px;background-color:rgb(249,249,249);">需要开启mysql的慢查询日志，否则无法进行统计分析，开启mysql慢查询日志需要在mysql的配置文件中进行如下配置：</p><div style="border-width:0px;padding:0px;margin:0px;list-style:none;color:rgb(51,51,51);font-family:Simsun;font-size:14px;line-height:28px;background-color:rgb(249,249,249);"><div class="syntaxhighlighter bash" style="border-width:0px;padding:0px;margin:1em 0px;list-style:none;width:613px;font-size:1em;background-color:#FFFFFF;"><table border="0" cellpadding="0" cellspacing="0" style="list-style:none;width:613px;border:0px;padding:0px;margin:0px;float:none;height:auto;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;background:none;"><tbody style="float:none;height:auto;line-height:1.1em;margin:0px;padding:0px;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;background:none;"><tr style="list-style:none;border-top-width:0px;padding:0px;margin:0px;float:none;height:auto;line-height:1.1em;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;background:none;"><td style="list-style:none;border:0px;padding:0px;margin:0px;float:none;height:auto;line-height:1.1em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;color:rgb(175,175,175);background:none;"><div style="list-style:none;border-width:0px 3px 0px 0px;padding:0px .5em 0px 1em;margin:0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.1em;text-align:right;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;white-space:pre;background-image:none;">1</div><div style="list-style:none;border-width:0px 3px 0px 0px;padding:0px .5em 0px 1em;margin:0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.1em;text-align:right;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;white-space:pre;background-image:none;">2</div><div style="list-style:none;border-width:0px 3px 0px 0px;padding:0px .5em 0px 1em;margin:0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.1em;text-align:right;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;white-space:pre;background-image:none;">3</div></td><td style="list-style:none;width:581px;border:0px;padding:0px;margin:0px;float:none;height:auto;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;background:none;"><div style="list-style:none;border:0px;padding:0px;margin:0px;float:none;height:auto;line-height:1.1em;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;background:none;"><div style="list-style:none;border:0px;padding:0px 1em;margin:0px;float:none;height:auto;line-height:1.1em;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;white-space:pre;background-image:none;"><code class="bash plain" style="border:0px;float:none;height:auto;line-height:1.1em;margin:0px;padding:0px;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;color:#000000;background:none;">slow_query_log=1</code><code class="bash comments" style="border:0px;float:none;height:auto;line-height:1.1em;margin:0px;padding:0px;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;color:rgb(0,130,0);background:none;">#定义超过1秒的查询计数到变量Slow_queries</code></div><div style="list-style:none;border:0px;padding:0px 1em;margin:0px;float:none;height:auto;line-height:1.1em;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;white-space:pre;background-image:none;"><code class="bash plain" style="border:0px;float:none;height:auto;line-height:1.1em;margin:0px;padding:0px;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;color:#000000;background:none;">slow-query-log-</code><code class="bash functions" style="border:0px;float:none;height:auto;line-height:1.1em;margin:0px;padding:0px;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;color:rgb(255,20,147);background:none;">file</code><code class="bash plain" style="border:0px;float:none;height:auto;line-height:1.1em;margin:0px;padding:0px;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;color:#000000;background:none;">=mysql-slow.log</code></div><div style="list-style:none;border:0px;padding:0px 1em;margin:0px;float:none;height:auto;line-height:1.1em;vertical-align:baseline;width:auto;font-size:1em;min-height:auto;white-space:pre;background-image:none;"><code class="bash plain" style="border:0px;float:none;height:auto;line-height:1.1em;margin:0px;padding:0px;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;min-height:auto;color:#000000;background:none;">long_query_time=1</code></div></div></td></tr></tbody></table></div></div><br><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);"><strong>1.1 简介</strong></p><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);">开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。</p><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);"><strong>1.2 登录数据库查看</strong></p><div style="margin:0px;padding:0px;line-height:25.2px;font-size:14px;width:660px;clear:both;text-align:left;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';background-color:rgb(255,255,255);"><div><div id="highlighter_606116" ><div ><span><a href="#" >?</a></span></div><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td ><div >1</div></td><td ><div ><div ><code class="bash plain">[root@localhost lib]</code><code class="bash comments"># mysql –uroot</code></div></div></td></tr></tbody></table></div></div></div><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);">因为没有设置设置密码，有密码的在 mysql –uroot –p 接密码</p><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);"><strong>1.2.1 进入MySql 查询是否开了慢查询</strong></p><div style="margin:0px;padding:0px;line-height:25.2px;font-size:14px;width:660px;clear:both;text-align:left;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';background-color:rgb(255,255,255);"><pre><code class="language-sql hljs"><ol style="width:647px"><li><div ><div data-line-number="1"></div></div><div ><div >mysql> <span ><span >show</span></span> <span ><span >variables</span></span> <span ><span >like</span></span> <span ><span >'slow_query%'</span></span>;</div></div></li><li><div ><div data-line-number="2"></div></div><div ><div > </div></div></li><li><div ><div data-line-number="3"></div></div><div ><div >+<span ><span >---------------------+--------------------------------------------+</span></span></div></div></li><li><div ><div data-line-number="4"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="5"></div></div><div ><div ><span >| Variable_name | Value |</span></div></div></li><li><div ><div data-line-number="6"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="7"></div></div><div ><div ><span >+</span><span ><span >---------------------+--------------------------------------------+</span></span></div></div></li><li><div ><div data-line-number="8"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="9"></div></div><div ><div ><span >| slow_query_log | OFF |</span></div></div></li><li><div ><div data-line-number="10"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="11"></div></div><div ><div ><span >| slow_query_log_file | /application/mysql/data/localhost-slow.log |</span></div></div></li><li><div ><div data-line-number="12"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="13"></div></div><div ><div ><span >+</span><span ><span >---------------------+--------------------------------------------+</span></span></div></div></li><li><div ><div data-line-number="14"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="15"></div></div><div ><div ><span >2 rows in </span><span ><span >set</span></span><span > (</span><span ><span >0.00</span></span><span > sec)</span></div></div></li></ol></code><div data-title="登录后复制" onclick="hljs.signin(event)"></div></pre></div><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);">参数说明：</p><ol style="margin-bottom:0px;margin-left:25px;padding-top:.3em;padding-bottom:.3em;line-height:25px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';font-size:14px;text-align:left;background-color:rgb(255,255,255);"><li style="margin:0px;padding:0px;list-style:disc;">slow_query_log 慢查询开启状态 OFF 未开启 ON 为开启</li><li style="margin:0px;padding:0px;list-style:disc;">slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）</li></ol><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);"><strong>1.2.2 查看慢查询超时时间</strong></p><div style="margin:0px;padding:0px;line-height:25.2px;font-size:14px;width:660px;clear:both;text-align:left;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';background-color:rgb(255,255,255);"><pre><code class="language-sql hljs"><ol ><li><div ><div data-line-number="1"></div></div><div ><div >mysql> <span ><span >show</span></span> <span ><span >variables</span></span> <span ><span >like</span></span> <span ><span >'long%'</span></span>;</div></div></li><li><div ><div data-line-number="2"></div></div><div ><div > </div></div></li><li><div ><div data-line-number="3"></div></div><div ><div >+<span ><span >-----------------+-----------+</span></span></div></div></li><li><div ><div data-line-number="4"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="5"></div></div><div ><div ><span >| Variable_name | Value |</span></div></div></li><li><div ><div data-line-number="6"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="7"></div></div><div ><div ><span >+</span><span ><span >-----------------+-----------+</span></span></div></div></li><li><div ><div data-line-number="8"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="9"></div></div><div ><div ><span >| long_query_time | 10.000000 |</span></div></div></li><li><div ><div data-line-number="10"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="11"></div></div><div ><div ><span >+</span><span ><span >-----------------+-----------+</span></span></div></div></li><li><div ><div data-line-number="12"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="13"></div></div><div ><div ><span >1 row in </span><span ><span >set</span></span><span > (</span><span ><span >0.00</span></span><span > sec)</span></div></div></li></ol></code><div data-title="登录后复制" onclick="hljs.signin(event)"></div></pre></div><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);">long_query_time 查询超过多少秒才记录 默认10秒 修改为1秒</p><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);"><strong>1.3 修改方法1：（不推荐）</strong></p><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);">方法一：优点临时开启慢查询，不需要重启数据库 缺点：MySql 重启慢查询失效</p><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);">推荐：根据业务需求，建议使用第二种，临时可以用第一种</p><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);">默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启，如下所示：：是否开启慢查询日志，1表示开启，0表示关闭。</p><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);"><strong>1.3.1 查看是否开启慢查询</strong></p><div style="margin:0px;padding:0px;line-height:25.2px;font-size:14px;width:660px;clear:both;text-align:left;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';background-color:rgb(255,255,255);"><pre><code class="language-sql hljs"><ol style="width:647px"><li><div ><div data-line-number="1"></div></div><div ><div >mysql> <span ><span >show</span></span> <span ><span >variables</span></span> <span ><span >like</span></span> <span ><span >'%slow_query_log%'</span></span>;</div></div></li><li><div ><div data-line-number="2"></div></div><div ><div > </div></div></li><li><div ><div data-line-number="3"></div></div><div ><div >+<span ><span >---------------------+--------------------------------------------+</span></span></div></div></li><li><div ><div data-line-number="4"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="5"></div></div><div ><div ><span >| Variable_name | Value |</span></div></div></li><li><div ><div data-line-number="6"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="7"></div></div><div ><div ><span >+</span><span ><span >---------------------+--------------------------------------------+</span></span></div></div></li><li><div ><div data-line-number="8"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="9"></div></div><div ><div ><span >| slow_query_log | OFF |</span></div></div></li><li><div ><div data-line-number="10"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="11"></div></div><div ><div ><span >| slow_query_log_file | /application/mysql/data/localhost-slow.log |</span></div></div></li><li><div ><div data-line-number="12"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="13"></div></div><div ><div ><span >+</span><span ><span >---------------------+--------------------------------------------+</span></span></div></div></li><li><div ><div data-line-number="14"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="15"></div></div><div ><div ><span >2 rows in </span><span ><span >set</span></span><span > (</span><span ><span >0.01</span></span><span > sec)</span></div></div></li></ol></code><div data-title="登录后复制" onclick="hljs.signin(event)"></div></pre></div><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);">输入 语句修改（重启后失效，建议在/etc/my.cnf中修改永久生效）</p><div style="margin:0px;padding:0px;line-height:25.2px;font-size:14px;width:660px;clear:both;text-align:left;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';background-color:rgb(255,255,255);"><pre><code class="language-sql hljs"><ol ><li><div ><div data-line-number="1"></div></div><div ><div >mysql> <span ><span >set</span></span> <span ><span >global</span></span> slow_query_log=<span ><span >1</span></span>;</div></div></li><li><div ><div data-line-number="2"></div></div><div ><div > </div></div></li><li><div ><div data-line-number="3"></div></div><div ><div >Query OK, 0 rows affected (0.11 sec)</div></div></li></ol></code><div data-title="登录后复制" onclick="hljs.signin(event)"></div></pre></div><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);"><strong>1.3.2 再次查看</strong></p><div style="margin:0px;padding:0px;line-height:25.2px;font-size:14px;width:660px;clear:both;text-align:left;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';background-color:rgb(255,255,255);"><pre><code class="language-sql hljs"><ol style="width:647px"><li><div ><div data-line-number="1"></div></div><div ><div >mysql> <span ><span >show</span></span> <span ><span >variables</span></span> <span ><span >like</span></span> <span ><span >'%slow_query_log%'</span></span>;</div></div></li><li><div ><div data-line-number="2"></div></div><div ><div > </div></div></li><li><div ><div data-line-number="3"></div></div><div ><div >+<span ><span >---------------------+--------------------------------------------+</span></span></div></div></li><li><div ><div data-line-number="4"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="5"></div></div><div ><div ><span >| Variable_name | Value |</span></div></div></li><li><div ><div data-line-number="6"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="7"></div></div><div ><div ><span >+</span><span ><span >---------------------+--------------------------------------------+</span></span></div></div></li><li><div ><div data-line-number="8"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="9"></div></div><div ><div ><span >| slow_query_log | ON |</span></div></div></li><li><div ><div data-line-number="10"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="11"></div></div><div ><div ><span >| slow_query_log_file | /application/mysql/data/localhost-slow.log |</span></div></div></li><li><div ><div data-line-number="12"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="13"></div></div><div ><div ><span >+</span><span ><span >---------------------+--------------------------------------------+</span></span></div></div></li><li><div ><div data-line-number="14"></div></div><div ><div ><span > </span></div></div></li><li><div ><div data-line-number="15"></div></div><div ><div ><span >2 rows in </span><span ><span >set</span></span><span > (</span><span ><span >0.00</span></span><span > sec)</span></div></div></li></ol></code><div data-title="登录后复制" onclick="hljs.signin(event)"></div></pre></div><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);"><strong>1.4 修改方法2：（推荐）</strong></p><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);">修改 MySql 慢查询，好多人不知道my.cnf 路径，可以用 find 查找</p><p style="margin-bottom:0px;padding-top:5px;padding-bottom:5px;font-size:14px;line-height:30px;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';text-align:left;background-color:rgb(255,255,255);">备注：我的MySQL 是编译的 路径为 /etc/my.cnf （一般都是这里）</p><div style="margin:0px;padding:0px;line-height:25.2px;font-size:14px;width:660px;clear:both;text-align:left;color:rgb(34,34,34);font-family:tahoma, arial, 'Microsoft YaHei';background-color:rgb(255,255,255);"><pre><code class="language-perl hljs">[root@localhost <span ><span >log</span></span>]<span ><span ># find / -type f -name "my.cnf"</span></span>
/application/mysql-5.5.51/mysql-test/suite/rpl/my.cnf
/application/mysql-5.5.51/mysql-test/suite/federated/my.cnf
/application/mysql-5.5.51/mysql-5.5.51-linux2.6-x86_64/mysql-test/suite/rpl/my.cnf
/application/mysql-5.5.51/mysql-5.5.51-linux2.6-x86_64/mysql-test/suite/federated/my.cnf
/etc/my.cnf ###（一般都是这里）
1.4.1.1 修改
1 | |
找到 [mysqld] 下面添加
slow_query_log =1slow_query_log_file=/application/mysql/data/localhost-slow.log
long_query_time = 1
参数说明：
- slow_query_log 慢查询开启状态 1 为开启
- slow_query_log_file 慢查询日志存放的位置
- long_query_time 查询超过多少秒才记录 默认10秒 修改为1秒
修改完重启MySQL
1.5 查看、测试
1.5.1.1 插入一条测试慢查询
1.
2.
mysql> select sleep(2);
3.
4.
+----------+
5.
6.
| sleep(2) |
7.
8.
+----------+
9.
10.
| 0 |
11.
12.
+----------+
13.
14.
1 row in set (2.00 sec)
15.
1.5.1.2 查看慢查询日志
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
|
1.5.1.3 通过MySQL命令查看有多少慢查询
1.
2.
mysql> show global status like '%Slow_queries%';
3.
4.
+---------------+-------+
5.
6.
| Variable_name | Value |
7.
8.
+---------------+-------+
9.
10.
| Slow_queries | 1 |
11.
12.
+---------------+-------+
13.
14.
1 row in set (0.00 sec)
15.
16.
1.6 日志分析工具mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow
MySQL 慢查询日志分析
mysql> show variables like '%slow%';
+---------------------+-------------------------+
| Variable_name | Value |
+---------------------+-------------------------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | D:/log/slow.txt |
+---------------------+-------------------------+
其中,各参数说明如下:
slow_launch_time: 慢查询超过的执行时间值
slow_query_log: 是否打开慢查询日志功能
show_query_log_file:慢查询日志目录
开启慢查询日志功能:
mysql配置文件(win mysql.ini linux mysql.conf)下,
[mysqld]
log-slow-queries ="D:/xampp/mysql/long.txt"
long_query_time = 1
log-slow-queries=/var/lib/mysql/slowquery.log (指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log)
long_query_time=2 (记录超过的时间，默认为10s)
log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)
log-long-format (如果设置了，所有没有使用索引的查询也将被记录)
mysqldumpslow, mysql官方提供的慢查询日志分析工具. 输出图表如下:
主要功能是, 统计不同慢sql的
出现次数(Count),
执行最长时间(Time),
累计总耗费时间(Time),
等待锁的时间(Lock),
发送给客户端的行总数(Rows),
扫描的行总数(Rows),
用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示).
mysqlsla, hackmysql.com推出的一款日志分析工具(该网站还维护了 mysqlreport, mysqlidxchk 等比较实用的mysql工具)
整体来说, 功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等.
格式说明如下:
总查询次数 (queries total), 去重后的sql数量 (unique)
输出报表的内容排序(sorted by)
最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数.
Count, sql的执行次数及占总的slow log数量的百分比.
Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比.
95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.
Lock Time, 等待锁的时间.
95% of Lock , 95%的慢sql等待锁时间.
Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量.
Rows examined, 扫描的行数量.
Database, 属于哪个数据库
Users, 哪个用户,IP, 占到所有用户执行的sql百分比
Query abstract, 抽象后的sql语句
Query sample, sql语句
除了以上的输出, 官方还提供了很多定制化参数, 是一款不可多得的好工具.
mysql-explain-slow-log, 德国人写的一个perl脚本.
http://www.willamowius.de/mysql-tools.html
功能上有点瑕疵, 不仅把所有的 slow log 打印到屏幕上, 而且统计也只有数量而已. 不推荐使用.
mysql-log-filter, google code上找到的一个分析工具.提供了 python 和 php 两种可执行的脚本.
http://code.google.com/p/mysql-log-filter/
功能上比官方的mysqldumpslow, 多了查询时间的统计信息(平均,最大, 累计), 其他功能都与 mysqldumpslow类似.
特色功能除了统计信息外, 还针对输出内容做了排版和格式化, 保证整体输出的简洁. 喜欢简洁报表的朋友, 推荐使用一下.
myprofi, 纯php写的一个开源分析工具.项目在 sourceforge 上.
http://myprofi.sourceforge.net/
功能上, 列出了总的慢查询次数和类型, 去重后的sql语句, 执行次数及其占总的slow log数量的百分比.
从整体输出样式来看, 比mysql-log-filter还要简洁. 省去了很多不必要的内容. 对于只想看sql语句及执行次数的用户来说, 比较推荐.
总结
工具/功能 | 一般统计信息 | 高级统计信息 | 脚本 | 优势 |
mysqldumpslow | 支持 | 不支持 | perl | mysql官方自带 |
mysqlsla | 支持 | 支持 | perl | 功能强大,数据报表齐全,定制化能力强. |
mysql-explain-slow-log | 支持 | 不支持 | perl | 无 |
mysql-log-filter | 支持 | 部分支持 | python or php | 不失功能的前提下,保持输出简洁 |
myprofi | 支持 | 不支持 | php | 非常精简 |
<div class="person-messagebox">
</div>
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
