mysql如何排查索引失效

<blockquote>索引失效指MySQL未使用预期索引，导致查询效率下降甚至全表扫描。通过EXPLAIN分析执行计划，观察type=ALL或key为NULL可判断索引未命中。常见原因包括：WHERE子句中数据类型不匹配、索引列使用函数或表达式、OR连接无索引条件、NOT负向查询、范围查询过大、联合索引不满足最左前缀原则。此外，统计信息不准、索引选择性低、MySQL版本或配置问题也会导致失效。避免方式包括遵循最左前缀原则、避免在索引列上使用函数、确保数据类型一致。模糊查询以%开头、低区分度字段如性别建索引效果差。可使用FORCE INDEX强制走索引，但不推荐作为常规手段。当频繁增删改导致索引碎片、数据量大或查询变慢时，应考虑重建索引，通过ALTER TABLE或OPTIMIZE TABLE整理碎片，建议在低峰期执行。</blockquote> <p><img src="https://img.php.cn/upload/article/000/969/633/175825500266074.jpeg" alt="mysql如何排查索引失效"></p> <p>索引失效，简单来说，就是MySQL在执行查询时，本应该用上的索引没用上，导致查询效率直线下降，甚至全表扫描。</p> <h3>解决方案</h3> <p>排查MySQL索引失效，需要从以下几个方面入手：</p> <ol> <li> <p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div> 你的查询</strong>：这是最基础也是最重要的步骤。在你的SQL查询语句前加上<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;">type</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;">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;">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;">key_len</pre></div>列显示了索引中被使用<a style="color:#f60; text-decoration:underline;" title="字节" href="https://www.php.cn/zt/16298.html" target="_blank">字节</a>的数量，通过这个值可以计算具体使用了索引中的哪些列。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>EXPLAIN SELECT * FROM users WHERE name = 'John' AND age > 25;</pre></div></li> <li> <p><strong>检查WHERE子句</strong>：WHERE子句是索引发挥作用的关键区域。</p> <ul> <li> <strong>数据类型不匹配</strong>：如果列类型是字符串，而你在WHERE子句中使用了数字，MySQL可能会进行<a style="color:#f60; text-decoration:underline;" title="隐式类型转换" href="https://www.php.cn/zt/63333.html" target="_blank">隐式类型转换</a>，导致索引失效。例如，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE phone = 1234567890</pre></div>，如果<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">phone</pre></div>是VARCHAR类型，就可能导致索引失效。</li> <li> <strong>使用了函数或表达式</strong>：在索引列上使用函数（如<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">UPPER()</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LOWER()</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATE()</pre></div>）或表达式，会导致索引失效。例如，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE UPPER(name) = 'JOHN'</pre></div>。</li> <li> <strong>OR条件</strong>：如果OR连接的多个条件中，有一个条件没有索引，那么整个查询都可能导致索引失效。尽量避免在WHERE子句中使用OR。</li> <li> <strong>NOT条件</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;">!=</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;"><></pre></div>等负向查询，通常会导致索引失效。</li> <li> <strong>范围查询</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;">>=</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>）在某些情况下可能导致索引失效，特别是当范围过大时。MySQL会评估使用索引的成本，如果全表扫描更快，它会选择全表扫描。</li> <li> <strong>最左前缀原则</strong>：对于联合索引，必须从索引的最左边的列开始使用，否则索引会失效。例如，如果有一个联合索引<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">INDEX(a, b, c)</pre></div>，那么<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE b = xxx AND c = xxx</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;">ANALYZE TABLE</pre></div>命令来更新表的统计信息。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>ANALYZE TABLE users;</pre></div></li> <li><p><strong>索引选择性</strong>：索引的选择性是指索引列中不同值的数量与表总行数的比率。选择性越高，索引的效果越好。如果索引的选择性很低，MySQL可能认为使用索引的成本高于全表扫描，从而放弃使用索引。</p></li> <li><p><strong>MySQL版本和配置</strong>：不同版本的MySQL在索引优化方面可能有所不同。同时，一些配置参数（如<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">optimizer_switch</pre></div>）也会影响索引的使用。</p></li> </ol> <h3>索引失效的常见场景有哪些？如何避免？</h3> <p>索引失效的场景很多，但归根结底都是因为MySQL的优化器认为使用索引的成本高于全表扫描。</p> <div class="aritcle_card"> <a class="aritcle_card_img" href="/ai/1310"> <img src="https://img.php.cn/upload/ai_manual/001/431/639/68b6d7c3db0e1934.png" alt="AISEO AI Content Detector"> </a> <div class="aritcle_card_info"> <a href="/ai/1310">AISEO AI Content Detector</a> <p>AISEO推出的AI内容检测器</p> <div class=""> <img src="/static/images/card_xiazai.png" alt="AISEO AI Content Detector"> <span>82</span> </div> </div> <a href="/ai/1310" class="aritcle_card_btn"> <span>查看详情</span> <img src="/static/images/cardxiayige-3.png" alt="AISEO AI Content Detector"> </a> </div> <ul> <li> <strong>模糊查询以<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">%</pre></div>开头</strong>：<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE name LIKE '%John'</pre></div>，这种情况下索引无法使用。尽量避免使用前缀模糊查询，或者考虑使用全文索引。</li> <li> <strong>联合索引不满足最左前缀原则</strong>：确保查询条件包含联合索引的最左边的列。</li> <li> <strong>WHERE子句中使用了计算或函数</strong>：避免在索引列上使用计算或函数。可以将计算或函数应用在WHERE子句的右侧。</li> <li> <strong>数据量小</strong>：当表的数据量很小时，MySQL可能认为全表扫描更快。</li> <li> <strong>区分度低的字段</strong>：例如，性别字段，只有男女两种值，建立索引的意义不大。</li> </ul> <h3>如何强制MySQL使用索引？</h3> <p>虽然通常情况下MySQL的优化器会做出正确的选择，但在某些特殊情况下，我们可能需要强制MySQL使用特定的索引。可以使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">FORCE INDEX</pre></div>提示。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM users FORCE INDEX (idx_name) WHERE name = 'John';</pre></div><p>但是，强制使用索引通常不是一个好的做法。它可能会掩盖更深层次的问题，例如索引设计不合理或统计信息不准确。建议先排查问题，优化SQL语句和索引设计，而不是简单地强制使用索引。</p> <h3>什么时候应该重建索引？</h3> <p>索引在长期使用过程中可能会产生碎片，影响查询性能。重建索引可以整理索引碎片，提高查询效率。以下情况可以考虑重建索引：</p> <ul> <li> <strong>频繁的INSERT、UPDATE、DELETE操作</strong>：这些操作会导致索引碎片增多。</li> <li> <strong>表数据量很大</strong>：大表的索引更容易产生碎片。</li> <li> <strong>查询性能下降</strong>：如果发现查询性能明显下降，可以考虑重建索引。</li> </ul> <p>重建索引可以使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ALTER TABLE</pre></div>命令：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>ALTER TABLE users DROP INDEX idx_name, ADD INDEX idx_name (name);</pre></div><p>或者使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">OPTIMIZE TABLE</pre></div>命令，它会重建表并更新统计信息：</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>OPTIMIZE TABLE users;</pre></div><p>注意：重建索引是一个耗时操作，特别是对于大表，应该在业务低峰期进行。</p>

以上就是mysql如何排查索引失效的详细内容，更多请关注php中文网其它相关文章！