如何优化数据库索引提升查询速度？

<blockquote>优化数据库索引需先分析慢查询日志和执行计划，再根据查询模式设计复合索引，优先考虑等值条件、基数和覆盖索引，避免全表扫描；同时权衡读写性能，防止索引过多导致写开销增大、存储浪费及优化器决策困难，定期清理无效索引以维持系统高效。</blockquote> <p><img src="https://img.php.cn/upload/article/001/253/068/175850334273161.jpg" alt="如何优化数据库索引提升查询速度？"></p> <p>优化数据库索引是提升查询速度最直接、最有效的方法之一，核心在于理解你的查询模式，然后针对性地创建和维护合适的索引。它不是万能药，但用对了地方，效果立竿见影。</p> <h3>解决方案</h3> <p>说实话，优化数据库索引这事儿，没有一劳永逸的方案，它更像是一门艺术，需要你对数据、业务和查询逻辑都有深入的理解。在我看来，最关键的几点是：</p> <p>首先，要搞清楚你的系统里哪些查询是“慢”的。这通常需要借助数据库的慢查询日志或者<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 ANALYZE</pre></div>）<a style="color:#f60; text-decoration:underline;" title="工具" href="https://www.php.cn/zt/16887.html" target="_blank">工具</a>来分析。你得知道，哪些SQL语句在哪些表上花费了大量时间，它们到底是在全表扫描，还是在进行大量的临时表操作，抑或是文件排序。</p> <p>其次，针对这些慢查询，开始思考索引的设计。这不仅仅是简单地在<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE</pre></div>子句的字段上加索引那么粗暴。你需要考虑：</p> <ul> <li> <strong>单列索引还是复合索引？</strong> 如果你的查询条件经常是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE col1 = ? AND col2 = ?</pre></div>，那么一个复合索引<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(col1, col2)</pre></div>往往比两个独立的单列索引效果更好。但要注意复合索引的列顺序，这非常重要。</li> <li> <strong>覆盖索引？</strong> 有时候，如果你的查询只需要索引中的列，而不需要回表去查找原始数据，那么这种“覆盖索引”能极大提升性能。比如<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SELECT col1, col2 FROM table WHERE col1 = ?</pre></div>，如果有一个<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(col1, col2)</pre></div>的复合索引，那么这个查询就可能被覆盖。</li> <li> <strong>索引类型选择？</strong> 大部分情况下，我们用的都是B-Tree索引，它对等值查询、范围查询、排序都非常高效。但如果你有全文搜索需求，可能需要全文索引；如果你有<a style="color:#f60; text-decoration:underline;" title="地理位置" href="https://www.php.cn/zt/36976.html" target="_blank">地理位置</a>查询，则可能需要空间索引。</li> <li> <strong>索引的基数（Cardinality）</strong>。索引列的值越分散（基数越高），索引的效果越好。比如，在一个性别字段上加索引，效果可能就不如在一个用户ID字段上加索引。但这不是绝对的，有时候即使基数不高，如果该列是查询条件的核心部分，索引依然有价值。</li> </ul> <p>最后，别忘了索引也是有成本的。它会占用存储空间，更重要的是，每次数据的插入、更新、删除操作，都需要维护索引，这会增加写操作的开销。所以，并不是索引越多越好。你需要找到一个平衡点，让读写性能达到最优。定期审查和清理不再使用的索引，也是一个好习惯。</p> <h3>如何判断哪些查询需要索引优化？</h3> <p>这其实是优化的第一步，也是最容易被忽视的一步。我们常常凭感觉去加索引，结果发现效果不佳，甚至适得其反。判断哪些查询需要优化，主要有几个关键途径：</p> <ol> <li><p><strong>慢查询日志（Slow Query Log）：</strong> 这是最直接的证据。几乎所有主流数据库都提供了慢查询日志功能，你可以设置一个阈值（比如超过2秒的查询就记录下来）。定期分析这些日志，找出执行时间长、扫描行数多的SQL语句。这些往往是索引优化的重点目标。我个人经验是，别只看执行时间，扫描行数同样重要，有时一个查询虽然总时间不长，但扫描了大量无用数据，这同样是效率低下的表现。</p></li> <li> <p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div> 命令分析：</strong> 这是数据库管理员和开发人员的“瑞士军刀”。当你拿到一个可疑的慢查询时，用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>（MySQL）或者<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN ANALYZE</pre></div>（PostgreSQL）去分析它的执行计划。</p> <ul> <li> <strong>看<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">type</pre></div>（MySQL）或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Plan Type</pre></div>（PostgreSQL）：</strong> 如果看到<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;">index</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;">const</pre></div>、<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;">ref</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">range</pre></div>。</li> <li> <strong>看<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">rows</pre></div>（MySQL）或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Rows Removed by Filter</pre></div>等（PostgreSQL）：</strong> 这表示查询为了找到结果需要检查的行数。如果这个数字非常大，远超实际返回的行数，那就说明查询效率低下。</li> <li> <strong>看<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Extra</pre></div>（MySQL）或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Planning Time</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Execution Time</pre></div>（PostgreSQL）：</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;">ORDER BY</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 where</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Using index</pre></div>（使用了覆盖索引，这是个好兆头）。</li> <li>举个例子，如果我看到一个<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;">type: ALL</pre></div>, <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">rows: 1000000</pre></div>, <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Extra: Using filesort</pre></div>，那我心里就有数了，这绝对是索引的重灾区。</li> </ul> </li> <li><p><strong>数据库性能监控工具：</strong> 许多数据库都提供了内置的性能监控视图（如MySQL的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">performance_schema</pre></div>，PostgreSQL的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">pg_stat_statements</pre></div>）或者第三方监控工具。它们能帮你实时追踪哪些查询消耗了最多的CPU、I/O资源。这些工具能提供更宏观的视角，帮助你发现系统瓶颈。</p></li> </ol> <p>通过这些方法，你能从海量的查询中筛选出那些真正拖累系统性能的“罪魁祸首”，从而有针对性地进行索引优化。</p> <h3>复合索引的列顺序应该如何设计？</h3> <p>设计复合索引的列顺序，这真是一门学问，而且常常是优化效果好坏的关键。核心原则是“最左前缀匹配”，但这背后还有一些更深层次的思考。</p> <div class="aritcle_card"> <a class="aritcle_card_img" href="/xiazai/code/10786"> <img src="https://img.php.cn/upload/webcode/000/000/003/176373900197726.jpg" alt="新快购物系统"> </a> <div class="aritcle_card_info"> <a href="/xiazai/code/10786">新快购物系统</a> <p>新快购物系统是集合目前网络所有购物系统为参考而开发，不管从速度还是安全我们都努力做到最好，此版虽为免费版但是功能齐全，无任何错误，特点有：专业的、全面的电子商务解决方案，使您可以轻松实现网上销售；自助式开放性的数据平台，为您提供充满个性化的设计空间；功能全面、操作简单的远程管理系统，让您在家中也可实现正常销售管理；严谨实用的全新商品数据库，便于查询搜索您的商品。</p> <div class=""> <img src="/static/images/card_xiazai.png" alt="新快购物系统"> <span>0</span> </div> </div> <a href="/xiazai/code/10786" class="aritcle_card_btn"> <span>查看详情</span> <img src="/static/images/cardxiayige-3.png" alt="新快购物系统"> </a> </div> <p><strong>1. 最左前缀匹配原则：</strong> 这是最基本也是最重要的原则。如果你有一个复合索引<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(col1, col2, col3)</pre></div>，那么它可以支持：</p> <ul> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE col1 = ?</pre></div></li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE col1 = ? AND col2 = ?</pre></div></li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE col1 = ? AND col2 = ? AND col3 = ?</pre></div> 但它不能直接支持：</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE col2 = ?</pre></div></li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE col3 = ?</pre></div></li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE col2 = ? AND col3 = ?</pre></div> 简单来说，查询条件必须从索引的最左边的列开始匹配，才能充分利用这个索引。所以，在设计时，那些在<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE</pre></div>子句中经常被用作等值查询的列，应该放在复合索引的最前面。</li> </ul> <p><strong>2. 考虑等值查询与范围查询的组合：</strong> 如果你的查询既有等值条件，也有范围条件（如<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>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE 'prefix%'</pre></div>），那么通常的做法是把等值条件的列放在前面，范围条件的列放在后面。 例如，查询<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE status = 'active' AND create_time > '2023-01-01'</pre></div>。</p> <ul> <li>如果索引是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(create_time, status)</pre></div>，那么索引只能利用到<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">create_time</pre></div>，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">status</pre></div>部分可能就无法有效利用索引了。</li> <li>如果索引是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(status, create_time)</pre></div>，那么索引会先根据<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">status = 'active'</pre></div>快速定位，然后在这些结果中，再根据<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">create_time > '2023-01-01'</pre></div>进行范围查找，效率会高很多。因为一旦遇到范围查询，索引的后续列通常就失效了。</li> </ul> <p><strong>3. 考虑列的基数（Cardinality）：</strong> 虽然最左前缀原则是首要的，但在某些情况下，列的基数也需要考虑。通常，我们会倾向于将基数较高的列放在前面，因为它们能更快地缩小搜索范围。比如，在一个用户表里，用户ID的基数远高于性别。如果查询是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE gender = 'male' AND user_id = 123</pre></div>，那么<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(user_id, gender)</pre></div>的索引可能比<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(gender, user_id)</pre></div>更优，因为<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">user_id</pre></div>能更快地定位到唯一行。但如果查询条件是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE gender = 'male' AND city = 'New York'</pre></div>，那么就需要仔细权衡<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;">city</pre></div>的基数以及它们在查询中的使用频率。</p> <p><strong>4. 考虑<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>：</strong> 如果查询经常需要对某些列进行排序或分组，那么把这些列放在复合索引的末尾，有时也能帮助数据库避免额外的文件排序或临时表操作。例如，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SELECT * FROM users WHERE city = 'Beijing' ORDER BY age</pre></div>，一个<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(city, age)</pre></div>的复合索引就能很好地支持这个查询，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ORDER BY</pre></div>部分可以直接利用索引的有序性。</p> <p>所以，设计复合索引的列顺序，不是一拍脑袋就能决定的，它需要你对业务查询模式有深刻的理解，甚至需要通过<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>命令反复测试不同的索引组合，才能找到最优解。这就像是拼图，每一块都要放对位置。</p> <h3>索引过多或不当会带来哪些负面影响？</h3> <p>很多时候，我们为了追求查询性能，会不自觉地创建大量的索引。但索引并非多多益善，它就像一把双刃剑，用得不好反而会拖累整个数据库系统。在我看来，索引过多或不当，主要会带来以下几个负面影响：</p> <ol> <li><p><strong>写操作性能下降（DML操作变慢）：</strong> 这是最直接也是最显著的影响。每次对表进行<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">INSERT</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">UPDATE</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DELETE</pre></div>操作时，数据库不仅要修改表中的数据，还要同步更新所有相关的索引。索引越多，需要维护的数据结构就越多，这个过程就越慢。尤其是在高并发的写入场景下，这会成为严重的瓶颈。想象一下，你更新一条记录，数据库可能需要更新好几个B-Tree结构，这无疑增加了CPU和I/O的负担。</p></li> <li><p><strong>存储空间消耗：</strong> 索引本身也是数据，需要占用磁盘空间。虽然单个索引可能不大，但当表的数据量非常庞大，并且索引数量众多时，它们占用的存储空间会非常可观。这不仅增加了存储成本，也可能影响备份和恢复的速度。</p></li> <li><p><strong>查询优化器（Optimizer）的负担：</strong> 数据库的查询优化器在执行查询时，需要评估所有可用的索引，然后选择一个它认为最优的执行计划。索引越多，优化器需要考虑的路径就越多，它的决策时间就越长。有时候，优化器甚至可能选择了一个次优的索引，导致查询性能不升反降。这就像你面前有太多条路，反而让你不知道该选哪条。</p></li> <li><p><strong>增加内存消耗（Cache Pressure）：</strong> 数据库通常会将常用的数据和索引块缓存到内存中，以加速访问。索引过多意味着需要缓存的数据量增大，这会给数据库的内存管理带来压力。如果索引不能完全被缓存，那么每次访问磁盘的I/O操作就会增多，从而降低整体性能。</p></li> <li><p><strong>不必要的I/O操作：</strong> 即使一个索引没有被查询使用，但在DML操作时，数据库仍然需要加载其索引页到内存进行更新，这会产生不必要的I/O。</p></li> <li><p><strong>维护成本：</strong> 随着时间的推移，索引可能会出现碎片化，需要定期进行重建或重新组织，以保持其效率。索引越多，维护工作量就越大。</p></li> </ol> <p>所以，在创建索引时，我们必须非常谨慎，只创建那些真正能带来显著性能提升的索引。定期审查和删除那些使用率低、效果不佳的索引，是数据库维护中不可或缺的一环。一个好的索引策略，不是堆砌索引，而是精简高效。</p>

以上就是如何优化数据库索引提升查询速度？的详细内容，更多请关注php中文网其它相关文章！