如何在SQL中优化查询？EXPLAIN与查询优化的技巧

<blockquote>使用EXPLAIN分析SQL执行计划，通过type、key、rows等列判断查询效率，结合索引优化、避免全表扫描和常见陷阱，提升查询性能。</blockquote> <p><img src="https://img.php.cn/upload/article/001/503/042/175706064121561.jpeg" alt="如何在sql中优化查询？explain与查询优化的技巧"></p> <p>SQL查询优化，简单来说，就是让你的数据库更快地返回结果。这不仅仅是让你的网站感觉更流畅，还能节省服务器资源，尤其是在数据量巨大的情况下。</p> <p>EXPLAIN与查询优化技巧</p> <h3>如何使用EXPLAIN分析SQL查询？</h3> <p>EXPLAIN 语句是 SQL 中一个强大的<a style="color:#f60; text-decoration:underline;" title="工具" href="https://www.php.cn/zt/16887.html" target="_blank">工具</a>，它能告诉你数据库是如何执行你的查询的。与其说是“如何使用”，不如说是“如何理解” EXPLAIN 的输出。</p> <p>首先，在你的 SQL 查询前加上 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div> 关键字，例如：<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN SELECT * FROM users WHERE age > 25;</pre></div></p> <p>执行这个语句后，你会得到一张表，每一行代表查询计划中的一个步骤。这张表里有很多列，但最关键的几个是：</p> <ul> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">id</pre></div></strong>: 查询的标识符，数字越大，执行优先级越高（但也有例外，后面说）。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">select_type</pre></div></strong>: 查询的类型，例如 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SIMPLE</pre></div>（简单查询，不包含子查询或 UNION），<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">PRIMARY</pre></div>（最外层的 SELECT），<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SUBQUERY</pre></div>（子查询）等等。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">table</pre></div></strong>: 涉及的表名。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">type</pre></div></strong>: 访问类型，这是最重要的列之一。它告诉你数据库是如何找到表中符合条件的行的。常见的类型有：<ul> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">system</pre></div>: 表中只有一行记录，这是理想情况，速度非常快。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">const</pre></div>: 使用主键或唯一索引进行等值查询，也是非常快的。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">eq_ref</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;">range</pre></div>: 使用索引进行范围查询，例如 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">BETWEEN</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>。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">index</pre></div>: 扫描整个索引树。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ALL</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>: 数据库可能使用的索引。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">key</pre></div></strong>: 数据库实际使用的索引。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">key_len</pre></div></strong>: 索引的长度，可以用来判断索引的使用情况。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ref</pre></div></strong>: 显示索引的哪一列被用于查找值。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">rows</pre></div></strong>: 数据库估计需要扫描的行数。</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 index</pre></div>（表示使用了覆盖索引，不需要回表查询），<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Using where</pre></div>（表示需要使用 WHERE 子句过滤结果），<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;">Using filesort</pre></div>（表示需要进行文件排序，速度较慢）。</li> </ul> <p>理解了这些列的含义，你就可以分析 EXPLAIN 的输出了。例如，如果 <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;">rows</pre></div> 很大，<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;">Using temporary</pre></div>，那就说明你的查询需要优化。</p> <div class="aritcle_card"> <a class="aritcle_card_img" href="/xiazai/code/10800"> <img src="https://img.php.cn/upload/webcode/000/000/004/176379660214943.gif" alt="HiShop网店代理分销系统"> </a> <div class="aritcle_card_info"> <a href="/xiazai/code/10800">HiShop网店代理分销系统</a> <p>Hishop.5.2.BETA2版主要更新： [修改] 进一步优化了首页打开速度 [修改] 美化了默认模板 [修改] 优化系统架构,程序标签及SQL查询效率,访问系统页面的速度大大提高 [修改] 采用了HTML模板机制,实现了前台模板可视化编辑,降低模板制作与修改的难度. [修改] 全新更换前后台AJAX技术框架,提升了用户操作体验. 店铺管理 [新增] 整合TQ在线客服 [修改] 后台广告位增加</p> <div class=""> <img src="/static/images/card_xiazai.png" alt="HiShop网店代理分销系统"> <span>0</span> </div> </div> <a href="/xiazai/code/10800" class="aritcle_card_btn"> <span>查看详情</span> <img src="/static/images/cardxiayige-3.png" alt="HiShop网店代理分销系统"> </a> </div> <p>另外，关于 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">id</pre></div> 列的优先级，如果 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">id</pre></div> 相同，则从上到下执行；如果 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">id</pre></div> 不同，则 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">id</pre></div> 值越大，优先级越高。但是，如果 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">id</pre></div> 相同，并且 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">select_type</pre></div> 是 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DERIVED</pre></div>（派生表），则派生表会先执行。</p> <h3>如何通过索引优化SQL查询？</h3> <p>索引就像书的目录，可以帮助数据库快速找到数据。但索引不是越多越好，过多的索引会增加数据库的负担。</p> <ul> <li> <strong>选择合适的列创建索引</strong>：通常，你应该在 WHERE 子句中经常使用的列上创建索引。例如，如果你经常根据 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">age</pre></div> 和 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">city</pre></div> 查询用户，可以考虑创建 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">age</pre></div> 和 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">city</pre></div> 的联合索引。</li> <li> <strong>注意索引的顺序</strong>：对于联合索引，索引的顺序很重要。应该将选择性高的列放在前面。选择性是指，该列的不同值的数量与总行数的比例。例如，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">gender</pre></div> 的选择性很低，而 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">email</pre></div> 的选择性很高。</li> <li> <strong>避免在索引列上使用函数或表达式</strong>：例如，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE YEAR(birthday) = 2000</pre></div> 就无法使用 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">birthday</pre></div> 上的索引。应该改为 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE birthday BETWEEN '2000-01-01' AND '2000-12-31'</pre></div>。</li> <li> <strong>尽量使用覆盖索引</strong>：覆盖索引是指，查询需要的所有列都包含在索引中。这样可以避免回表查询，提高查询效率。例如，如果你的查询是 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SELECT age, city FROM users WHERE age > 25</pre></div>，可以创建一个包含 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">age</pre></div> 和 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">city</pre></div> 的联合索引。</li> <li> <strong>定期维护索引</strong>：随着数据的增加和删除，索引可能会变得碎片化，影响查询效率。可以使用 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">OPTIMIZE TABLE</pre></div> 命令来优化表，重建索引。</li> </ul> <h3>如何避免SQL查询中的常见陷阱？</h3> <p>除了索引，还有一些常见的陷阱需要避免：</p> <ul> <li>*<em>避免使用 `SELECT </em>`**：应该只选择需要的列，减少数据传输量。</li> <li> <strong>避免在 WHERE 子句中使用 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">OR</pre></div></strong>：<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;">UNION</pre></div> 或分解成多个查询。</li> <li> <strong>避免在 WHERE 子句中使用 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">NOT IN</pre></div></strong>：<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">NOT IN</pre></div> 可能会导致全表扫描。可以尝试使用 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">NOT EXISTS</pre></div> 或 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LEFT JOIN ... WHERE ... IS NULL</pre></div>。</li> <li> <strong>避免在 WHERE 子句中使用模糊查询 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">%keyword%</pre></div></strong>：这种查询无法使用索引。可以考虑使用全文索引或<a style="color:#f60; text-decoration:underline;" title="搜索引擎" href="https://www.php.cn/zt/20588.html" target="_blank">搜索引擎</a>。</li> <li> <strong>避免使用子查询</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;">LIMIT</pre></div></strong>：<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIMIT</pre></div> 可以限制返回的行数，提高查询效率。但要注意，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIMIT</pre></div> 只有在排序后才能生效。</li> </ul> <h3>如何优化复杂的SQL查询？</h3> <p>对于复杂的 SQL 查询，可以尝试以下方法：</p> <ul> <li> <strong>分解查询</strong>：将复杂的查询分解成多个简单的查询，然后将结果组合起来。</li> <li> <strong>使用临时表</strong>：将中间结果存储在临时表中，可以避免重复计算。</li> <li> <strong>使用物化视图</strong>：物化视图是指，将查询结果预先计算并存储起来，可以大大提高查询效率。但要注意，物化视图需要定期刷新。</li> <li> <strong>优化 JOIN</strong>：<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;">STRAIGHT_JOIN</pre></div> 强制指定 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">JOIN</pre></div> 的顺序。</li> <li> <strong>使用查询提示（Query Hints）</strong>：查询提示可以告诉数据库如何执行查询。例如，可以使用 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">USE INDEX</pre></div> 强制指定使用的索引，可以使用 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">IGNORE INDEX</pre></div> 忽略某些索引。但要注意，查询提示可能会导致数据库无法选择最优的执行计划。</li> </ul> <p>记住，SQL 优化是一个持续的过程，需要不断地学习和实践。没有什么万能的解决方案，只有最适合你的解决方案。</p>

以上就是如何在SQL中优化查询？EXPLAIN与查询优化的技巧的详细内容，更多请关注其它相关文章！