mysql如何通过日志优化查询性能

<blockquote>答案是通过开启慢查询日志、使用pt-query-digest分析日志、结合EXPLAIN执行计划优化索引和SQL语句，可系统性提升MySQL查询性能。</blockquote> <p><img src="https://img.php.cn/upload/article/000/969/633/175801716277898.jpeg" alt="mysql如何通过日志优化查询性能"></p> <p>MySQL查询性能优化，说到底就是一场侦探游戏，而日志，尤其是慢查询日志，就是我们最重要的线索来源。它能直接指出哪些查询耗时过长、是性能瓶颈，然后我们才能有针对性地去分析和改进，而不是盲目地猜测。</p> <h3>解决方案</h3> <p>要通过日志优化MySQL查询性能，核心在于“发现-分析-改进”的循环。我们首先需要开启并配置好慢查询日志，让MySQL自动记录下那些执行时间超过我们预设阈值的SQL语句。接着，利用日志分析<a style="color:#f60; text-decoration:underline;" title="工具" href="https://www.php.cn/zt/16887.html" target="_blank">工具</a>对这些慢查询进行聚合和统计，找出其中最频繁、最耗时的“罪魁祸首”。一旦定位到具体的慢查询，我们就需要借助<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>命令深入分析其执行计划，理解MySQL是如何处理这条SQL的，包括它是否使用了索引、扫描了多少行数据、连接类型等。最后，根据<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>的分析结果，我们可以采取一系列优化措施，例如创建或调整索引、重写SQL语句、优化数据库结构，甚至是调整MySQL服务器配置。这个过程不是一蹴而就的，往往需要多次迭代和验证。</p> <h3>如何开启和配置MySQL慢查询日志以捕获性能瓶颈？</h3> <p>开启和配置MySQL慢查询日志，其实并不复杂，但里面的“门道”却不少。我通常会在<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">my.cnf</pre></div>（或者<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">my.ini</pre></div>，取决于你的<a style="color:#f60; text-decoration:underline;" title="操作系统" href="https://www.php.cn/zt/16016.html" target="_blank">操作系统</a>）<a style="color:#f60; text-decoration:underline;" title="配置文件" href="https://www.php.cn/zt/21155.html" target="_blank">配置文件</a>中进行设置，这是最推荐的方式，因为它能保证MySQL服务重启后配置依然生效。</p> <p>首先，你需要确保以下几行配置被正确添加或修改：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:ini;toolbar:false;'>[mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = 1</pre></div><p>这里面有几个关键点：</p> <ul> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">slow_query_log = 1</pre></div>：这行是开启慢查询日志的开关。设为<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">0</pre></div>就是关闭。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">slow_query_log_file</pre></div>：指定慢查询日志文件的路径和名称。务必确保MySQL用户对这个路径有写入权限，否则日志可能无法生成。我个人习惯放在<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">/var/log/mysql/</pre></div>目录下，方便集中管理。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">long_query_time = 1</pre></div>：这是慢查询的阈值，单位是秒。任何执行时间超过1秒的查询都会被记录下来。这个值非常重要，设置得太低，日志会变得非常庞大，充斥着大量“假性”慢查询，难以聚焦；设置得太高，又可能错过一些潜在的性能问题。我通常从1秒开始，根据实际业务情况和服务器负载进行调整，比如0.5秒甚至0.1秒。这需要一些经验和对业务的理解。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">log_queries_not_using_indexes = 1</pre></div>：这行配置也很有用。它会记录那些没有使用索引的查询，即使它们的执行时间没有超过<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">long_query_time</pre></div>。这对于发现潜在的索引优化机会非常有帮助，因为全表扫描通常是性能杀手。</li> </ul> <p>如果你不想重启MySQL服务，也可以通过SQL命令动态设置，但要注意，这种方式在服务重启后会失效：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SET GLOBAL slow_query_log = 'ON'; SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log'; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = 'ON';</pre></div><p>配置完成后，别忘了检查日志文件是否正在生成，以及内容是否符合预期。这能避免你花时间去排查一个根本没开启日志的环境。</p> <h3>发现慢查询后，如何利用日志数据进行深度分析和定位问题？</h3> <p>当慢查询日志开始记录数据后，直接打开一个巨大的日志文件去阅读，那简直是噩梦。原始的慢查询日志往往是密密麻麻的SQL语句，人工分析几乎不可能。这时候，我们就需要一些工具来帮助我们“消化”这些数据，从中提取出最有价值的信息。</p> <p>我最常用的工具是Percona Toolkit中的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">pt-query-digest</pre></div>。它比MySQL自带的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">mysqldumpslow</pre></div>强大得多，能提供更详细、更易读的报告。</p> <p>使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">pt-query-digest</pre></div>的基本命令通常是这样的：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:bash;toolbar:false;'>pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_report.txt</pre></div><p>运行后，它会生成一个详细的报告文件<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">slow_query_report.txt</pre></div>。这份报告会把日志中的SQL语句进行“指纹化”（即去除参数、格式化），然后按照总执行时间、执行次数等维度进行聚合和排序。</p> <div class="aritcle_card"> <a class="aritcle_card_img" href="/ai/1327"> <img src="https://img.php.cn/upload/ai_manual/000/000/000/175680183323472.jpg" alt="爱改写"> </a> <div class="aritcle_card_info"> <a href="/ai/1327">爱改写</a> <p>AI写作和改写润色工具</p> <div class=""> <img src="/static/images/card_xiazai.png" alt="爱改写"> <span>87</span> </div> </div> <a href="/ai/1327" class="aritcle_card_btn"> <span>查看详情</span> <img src="/static/images/cardxiayige-3.png" alt="爱改写"> </a> </div> <p>从<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">pt-query-digest</pre></div>的报告中，我主要关注以下几点：</p> <ol> <li> <strong>Top Queries (按总耗时排序)</strong>：这通常是报告的开头部分，列出了最耗费总时间的查询。一个查询可能单次执行不慢，但如果它被频繁调用，累积起来的总耗时就非常可观。这部分能帮我发现那些“积少成多”的性能杀手。</li> <li> <strong>Query Fingerprint (查询指纹)</strong>：<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">pt-query-digest</pre></div>会将具有相同结构但参数不同的SQL语句归为一类。这能让我一眼看出是哪种类型的查询模式导致了问题，而不是纠结于具体的某个参数值。</li> <li> <strong>Total Time (总时间)</strong>：指纹化后的查询类型，其所有实例的总执行时间。这是衡量其对系统整体性能影响的关键指标。</li> <li> <strong>Avg Time (平均时间)</strong>：单次执行的平均时间。如果这个值很高，说明查询本身效率低下。</li> <li> <strong>Exec Count (执行次数)</strong>：该查询类型在日志中出现的次数。高执行次数和高总时间结合，往往意味着优化潜力巨大。</li> <li> <strong>Lock Time (锁时间)</strong>：如果这个值很高，可能意味着表级锁或行级锁竞争激烈，需要考虑事务隔离级别、索引覆盖或更细粒度的锁策略。</li> <li> <strong>Rows Examined / Rows Sent (扫描行数 / 返回行数)</strong>：这是非常重要的指标。如果扫描行数远大于返回行数，说明MySQL做了很多无用功，可能存在索引缺失、索引失效或者查询条件不够精确的问题。</li> </ol> <p>通过这些聚合数据，我能够迅速定位到那些对系统性能影响最大的具体SQL语句。一旦定位，下一步就是针对这些具体的SQL语句，使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>进行更细致的分析。日志分析工具帮我从“大海捞针”变成了“精准打击”。</p> <h3>结合<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>结果，有哪些常见的优化策略和技巧可以提升查询性能？</h3> <p>定位到具体的慢查询后，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>命令就成了我们的“X光片”，它能揭示MySQL执行这条SQL语句的内部机制。理解<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>的输出是优化查询性能的关键一步。</p> <p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>的输出有很多列，我通常会重点关注以下几项：</p> <ul> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">type</pre></div></strong>：这是最重要的列之一，它表示了MySQL如何查找表中的行。<ul> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ALL</pre></div>：全表扫描，性能最差，通常是需要优化的首要目标。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">index</pre></div>：全索引扫描，比<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ALL</pre></div>好，但仍然扫描了整个索引。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">range</pre></div>：索引范围扫描，通常是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE</pre></div>子句中使用了范围条件（如<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">></pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;"><</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">BETWEEN</pre></div>）。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ref</pre></div>：非唯一索引扫描，或唯一索引的前缀扫描。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">eq_ref</pre></div>：唯一索引扫描，常用于<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">JOIN</pre></div>操作中，效率很高。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">const</pre></div>/<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">system</pre></div>：当查询优化器能将查询转换为一个常量时，效率最高。</li> </ul> </li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">possible_keys</pre></div></strong>：MySQL认为可能用于查找的索引。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">key</pre></div></strong>：MySQL实际选择使用的索引。如果<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">possible_keys</pre></div>有值而<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">key</pre></div>为<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">NULL</pre></div>，说明MySQL没选择任何索引，或者索引选择不当。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">key_len</pre></div></strong>：MySQL使用的索引的长度。越短越好，说明索引利用效率高。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">rows</pre></div></strong>：MySQL估计需要扫描的行数。这个值越小越好。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Extra</pre></div></strong>：提供了额外的信息，比如<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Using filesort</pre></div>（需要外部排序，通常意味着没有使用索引进行排序）、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Using temporary</pre></div>（需要创建临时表，通常意味着<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">GROUP BY</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DISTINCT</pre></div>操作没有合适的索引）、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Using index</pre></div>（使用了覆盖索引，效率很高）。</li> </ul> <p>根据<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>的分析结果，我可以采取以下常见的优化策略和技巧：</p> <ol> <li> <p><strong>创建或调整索引</strong>：</p> <ul> <li> <strong>缺失索引</strong>：如果<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">type</pre></div>是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ALL</pre></div>，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">key</pre></div>是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">NULL</pre></div>，那么通常需要在<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">JOIN</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ORDER BY</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">GROUP BY</pre></div>子句中涉及的列上创建索引。</li> <li> <strong>复合索引</strong>：对于多列查询条件，考虑创建复合索引。例如，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE col1 = 'A' AND col2 = 'B'</pre></div>，创建<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">INDEX(col1, col2)</pre></div>。注意索引列的顺序很重要，最左前缀原则。</li> <li> <strong>覆盖索引</strong>：如果<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Extra</pre></div>显示<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Using index</pre></div>，说明查询所需的所有列都包含在索引中，MySQL可以直接从索引中获取数据，无需回表查询，效率极高。尽量让查询成为覆盖索引。</li> <li> <strong>避免索引失效</strong>：<ul> <li>在索引列上使用函数（如<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE DATE(create_time) = '...'</pre></div>）会导致索引失效。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%keyword%'</pre></div>（前缀模糊匹配）通常也会导致索引失效。</li> <li>使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">OR</pre></div>连接条件时，如果<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">OR</pre></div>两边的列都没有索引或只有部分有索引，可能导致索引失效。</li> </ul> </li> </ul> </li> <li> <p><strong>重写SQL语句</strong>：</p> <ul> <li>*<em>避免`SELECT </em>`**：只选择需要的列，减少数据传输和内存消耗，也更有利于使用覆盖索引。</li> <li> <strong>优化<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIMIT OFFSET</pre></div></strong>：对于大偏移量的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIMIT OFFSET</pre></div>（如<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIMIT 100000, 10</pre></div>），性能会很差，因为它需要扫描100010行然后丢弃前100000行。可以考虑通过子查询或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">JOIN</pre></div>来优化：<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SELECT t1.* FROM table t1 JOIN (SELECT id FROM table WHERE condition ORDER BY id LIMIT 100000, 10) AS t2 ON t1.id = t2.id;</pre></div></li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">JOIN</pre></div> vs. 子查询</strong>：在某些情况下，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">JOIN</pre></div>的性能可能优于子查询，尤其是当子查询的结果集很大时。需要具体分析。</li> <li> <strong>减少<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ORDER BY</pre></div>和<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">GROUP BY</pre></div>的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">filesort</pre></div></strong>：如果<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Extra</pre></div>显示<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Using filesort</pre></div>，尝试在<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ORDER BY</pre></div>和<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">GROUP BY</pre></div>的列上创建合适的索引，或者调整索引顺序。</li> <li> <strong>使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">UNION ALL</pre></div>代替<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">UNION</pre></div></strong>：如果不需要去重，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">UNION ALL</pre></div>的性能会更好，因为它不需要额外的去重操作。</li> </ul> </li> <li> <p><strong>优化数据库结构</strong>：</p> <ul> <li> <strong>选择合适的数据类型</strong>：例如，能用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">INT</pre></div>就不用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">BIGINT</pre></div>，能用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">VARCHAR(100)</pre></div>就不用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">VARCHAR(255)</pre></div>。更小的数据类型意味着更小的索引和更快的处理速度。</li> <li> <strong>合理范式化/反范式化</strong>：根据业务需求，在查询性能和数据完整性之间做权衡。有时适当的反范式化（冗余数据）可以减少<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">JOIN</pre></div>操作，提升查询速度。</li> </ul> </li> <li> <p><strong>调整MySQL服务器配置</strong>（虽然不是直接通过日志优化查询，但对整体性能有影响）：</p> <ul> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">innodb_buffer_pool_size</pre></div></strong>：这是InnoDB最重要的配置项，用于缓存数据和索引。设置得足够大，可以减少磁盘I/O。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">tmp_table_size</pre></div>和<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">max_heap_table_size</pre></div></strong>：影响内存临时表的大小。如果<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">GROUP BY</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DISTINCT</pre></div>操作需要创建临时表，且内存临时表不够大，MySQL会将其转换为磁盘临时表，性能会急剧下降。</li> </ul> </li> </ol> <p>优化是一个持续的过程，往往需要反复地“发现-分析-改进-监控”循环。没有一劳永逸的解决方案，只有不断地学习和实践。</p>

以上就是mysql如何通过日志优化查询性能的详细内容，更多请关注php中文网其它相关文章！