新闻中心 
sql语句怎样避免因like查询使用通配符开头导致的索引失效 sql语句like通配符开头致索引失效的常见问题解决
2025-08-20
浏览次数：次
返回列表
<blockquote>使用LIKE '%通配符'会因B-tree索引无法支持后缀匹配而导致全表扫描，解决方法包括：1. 采用全文检索（如MySQL FULLTEXT、PostgreSQL GIN索引）高效处理任意子串查询；2. 对后缀查询使用逆序存储并创建索引；3. 优化业务逻辑，优先前缀匹配或精确查询；4. 在数据量小或低频场景下可容忍全表扫描；5. 避免在索引列使用函数、隐式类型转换、OR条件等导致索引失效的操作；选择方案需结合查询模式、数据规模、数据库能力及维护成本，并通过EXPLAIN验证执行计划。</blockquote>
<p><img src="https://img.php.cn/upload/article/001/503/042/175567530776347.jpeg" alt="sql语句怎样避免因like查询使用通配符开头导致的索引失效 sql语句like通配符开头致索引失效的常见问题解决"></p>
<p>SQL语句中使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%通配符'</pre></div>开头进行查询，确实是个性能杀手，因为它几乎总是导致索引失效，进而引发全表扫描。要避免这个问题，核心思路就是想办法让查询条件能够利用到索引，或者干脆绕开传统索引的限制。</p>
<h3>解决方案</h3>
<p>解决<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%通配符'</pre></div>导致索引失效的问题，主要有以下几种策略，每种都有其适用场景和考量：</p>
<p><strong>1. 利用全文检索（Full-Text Search）</strong></p>
<p>这是处理模糊查询，特别是包含任意位置子串查询的最优解之一。主流数据库如MySQL、PostgreSQL、SQL Server都有内置的全文检索功能，或者可以集成Elasticsearch、Solr等外部搜索引擎。</p>
<ul>
<li>
<p><strong>MySQL的全文索引：</strong> 适用于<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">MyISAM</pre></div>和<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">InnoDB</pre></div>存储引擎（MySQL 5.6+）。你需要为要查询的列创建<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">FULLTEXT</pre></div>索引。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>ALTER TABLE your_table ADD FULLTEXT(your_column);
SELECT * FROM your_table WHERE MATCH(your_column) AGAINST('keyword' IN NATURAL LANGUAGE MODE);</pre></div><p>它的工作原理是为文本内容建立倒排索引，能高效地查找包含特定词语的文档。对于中文，可能需要额外的分词插件。</p>
</li>
<li>
<p><strong>PostgreSQL的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">tsvector</pre></div>和<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">tsquery</pre></div>：</strong> PostgreSQL的全文检索功能非常强大和灵活。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>-- 创建索引
CREATE INDEX idx_your_column_gin ON your_table USING GIN(to_tsvector('english', your_column));
-- 查询
SELECT * FROM your_table WHERE to_tsvector('english', your_column) @@ to_tsquery('english', 'keyword');</pre></div><p>它允许你定义不同的语言配置和词典，处理多语言文本能力出色。</p>
</li>
</ul>
<p><strong>2. 逆序存储与查询（Reverse Indexing）</strong></p>
<p>这是一种比较巧妙的技巧，但有其局限性。如果你的查询模式主要是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%keyword'</pre></div>（即以某个词结尾），你可以考虑在表中增加一个额外的列，存储原始列内容的逆序字符串，并在这个逆序列上创建索引。</p>
<p>例如，如果原列是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">'apple'</pre></div>，逆序列就存<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">'elppa'</pre></div>。当你想查找<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%ple'</pre></div>时，实际上查询逆序列的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE 'elp%'</pre></div>，这样就能利用到索引了。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>-- 增加逆序列
ALTER TABLE your_table ADD COLUMN your_column_reversed VARCHAR(255);
-- 插入或更新时同步逆序数据
UPDATE your_table SET your_column_rever
sed = REVERSE(your_column);
-- 为逆序列创建索引
CREATE INDEX idx_your_column_reversed ON your_table (your_column_reversed);
-- 查询时
SELECT * FROM your_table WHERE your_column_reversed LIKE REVERSE('keyword%'); -- 注意这里的keyword%</pre></div><p>这种方法适用于你明确知道需要进行后缀匹配的场景，但对于任意位置的子串匹配（<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">%keyword%</pre></div>），它就无能为力了。而且，它增加了存储开销和数据同步的复杂性。</p>
<p><strong>3. 优化业务逻辑，避免前置通配符</strong></p>
<p>这听起来有点像“废话”，但很多时候，我们确实可以从业务层面重新审视需求。用户真的需要查找任意位置的子串吗？</p>
<ul>
<li>
<strong>能否只允许前缀匹配？</strong> 如果业务允许，强制用户输入前缀，例如搜索“<a style="color:#f60; text-decoration:underline;" title="苹果" href="https://www.php.cn/zt/16994.html" target="_blank">苹果</a>”相关的商品，用户输入“苹果”，而不是“果”。这样就可以用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE 'keyword%'</pre></div>，这就能很好地利用B-tree索引。</li>
<li>
<strong>拆分搜索词？</strong> 如果用户输入“red apple”，能否将“red”和“apple”拆开，分别作为前缀进行多次查询，或者结合<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">AND</pre></div>条件？</li>
<li>
<strong>精确匹配优先？</strong> 很多搜索场景下，用户可能更倾向于精确匹配，模糊查询只是辅助。可以先尝试精确匹配，如果无结果再进行模糊匹配。</li>
</ul>
<p><strong>4. 牺牲性能，但确保查询能跑</strong></p>
<p>在数据量不大，或者查询频率极低的情况下，直接使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%keyword%'</pre></div>可能不是最坏的选择。但前提是你真的评估过，它的性能影响在可接受范围内。</p>
<ul>
<li>
<p><strong>使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">INSTR()</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LOCATE()</pre></div>函数：</strong> 这些函数可以查找子串的位置，但它们本质上也是全表扫描。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM your_table WHERE INSTR(your_column, 'keyword') > 0;</pre></div><p>相比<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%keyword%'</pre></div>，在某些数据库和特定场景下，它们的执行计划可能会略有不同，但通常不会带来质的提升。</p>
</li>
<li><p><strong>考虑数据结构调整：</strong> 如果你的业务场景就是频繁进行这类模糊查询，并且数据量巨大，那么可能需要重新思考数据存储结构，比如将需要模糊查询的文本内容独立出来，存储到专门的搜索引擎中。</p>
<div class="aritcle_card">
<a class="aritcle_card_img" href="/ai/753">
<img src="https://img.php.cn/upload/ai_manual/001/503/042/68b6dbb7df33c256.png" alt="Project IDX">
</a>
<div class="aritcle_card_info">
<a href="/ai/753">Project IDX</a>
<p>Google推出的一个实验性的AI辅助开发平台</p>
<div class="">
<img src="/static/images/card_xiazai.png" alt="Project IDX">
<span>166</span>
</div>
</div>
<a href="/ai/753" class="aritcle_card_btn">
<span>查看详情</span>
<img src="/static/images/cardxiayige-3.png" alt="Project IDX">
</a>
</div>
</li>
</ul>
<h3>为什么<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%通配符'</pre></div>会导致索引失效？</h3>
<p>这个问题，说白了，就是B-tree索引的特性决定的。B-tree索引是一种平衡树结构，它能高效地处理等值查询（<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>），以及前缀匹配的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE</pre></div>查询（<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE 'keyword%'</pre></div>）。</p>
<p>当你创建了一个B-tree索引，比如在<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">product_name</pre></div>列上，数据库会把<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">product_name</pre></div>的所有值按字母顺序<a style="color:#f60; text-decoration:underline;" title="排列" href="https://www.php.cn/zt/56129.html" target="_blank">排列</a>好，并存储在索引中。</p>
<ul>
<li><p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE 'apple%'</pre></div>：</strong> 数据库可以很轻松地在索引中找到以“apple”开头的第一条记录，然后顺着索引的叶子节点往后扫描，直到遇到不以“apple”开头的记录为止。这个过程是利用索引的有序性进行的，非常高效。</p></li>
<li><p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%apple'</pre></div>：</strong> 问题来了。数据库不知道以“apple”结尾的字符串在索引的哪个位置开始。它无法利用索引的排序特性来快速定位。想象一下，索引里有“banana”、“orange”、“pineapple”，你要找以“apple”结尾的，它得从头到尾把所有索引项（或者更糟糕，直接是表数据）都看一遍，看看哪个字符串的末尾是“apple”。这本质上就是全表扫描。</p></li>
<li><p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%apple%'</pre></div>：</strong> 同理，如果通配符在两边，那更没办法利用索引了。数据库无法判断“apple”这个子串会出现在哪个位置，只能遍历所有数据。</p></li>
</ul>
<p>所以，核心原因就是B-tree索引的有序性无法满足后缀或中缀匹配的需求，导致查询优化器不得不放弃索引，转而进行代价更高的全表扫描。</p>
<h3>除了全表扫描，还有哪些潜在的性能陷阱？</h3>
<p>SQL查询性能的坑远不止<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%通配符'</pre></div>这一种，很多时候，一些看似无害的操作，都可能让你的查询效率直线下降。在我看来，以下几点是特别常见的“陷阱”：</p>
<ol>
<li>
<p><strong>在索引列上使用函数或进行表达式操作：</strong>
当你在<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;">DATE()</pre></div>, <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">YEAR()</pre></div>, <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SUBSTRING()</pre></div>, <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>等）或者进行算术运算时，数据库同样无法直接利用索引。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>-- 索引失效：对索引列使用了函数
SELECT * FROM orders WHERE YEAR(order_date) = 2025;
-- 优化方式：将函数作用于常量，而不是列
SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';</pre></div><p>数据库需要先计算出每一行的函数结果，然后才能进行比较，这使得它无法在索引树中直接查找。</p>
</li>
<li>
<p><strong><a style="color:#f60; text-decoration:underline;" title="隐式类型转换" href="https://www.php.cn/zt/63333.html" target="_blank">隐式类型转换</a>：</strong>
如果你的查询条件中的数据类型与列的数据类型不匹配，数据库可能会尝试进行隐式类型转换。这个转换过程可能导致索引失效。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>-- 假设user_id是INT类型，但你用字符串比较
SELECT * FROM users WHERE user_id = '123';</pre></div><p>数据库可能会把<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;">'123'</pre></div>转换为数字，但如果转换发生在列上，就可能导致问题。</p>
</li>
<li>
<p><strong><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;">WHERE</pre></div>子句中包含多个<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">OR</pre></div>条件，并且这些条件涉及不同的列时，优化器可能难以有效地使用索引，甚至可能导致全表扫描。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM products WHERE product_name LIKE 'A%' OR category_id = 10;</pre></div><p>虽然某些数据库的优化器在特定情况下能处理好，但通常来说，如果两个条件都能使用各自的索引，优化器可能会选择合并索引扫描的结果，或者干脆进行全表扫描。有时拆分成<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">UNION ALL</pre></div>可以改善性能，但也要具体分析。</p>
</li>
<li>
<p><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;"><></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;"><></pre></div>操作通常难以利用索引，因为它们表示的是“不包含”或“不等于”某个值，数据库需要扫描大量数据来确认哪些是不符合条件的。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM users WHERE status <> 'inactive';</pre></div><p>对于这种场景，如果“不等于”的值是少数，而“等于”的值是多数，可以考虑将查询条件反过来写，或者使用<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>。</p>
</li>
<li>
<p><strong>对NULL值的处理：</strong>
在某些数据库中，索引默认不包含NULL值。因此，涉及到<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">IS NULL</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">IS NOT NULL</pre></div>的查询可能无法有效利用索引。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM orders WHERE delivery_address IS NULL;</pre></div><p>这取决于索引类型和数据库配置。例如，MySQL的B-tree索引可以包含NULL值。但无论如何，查询NULL值通常意味着需要扫描索引中的所有NULL项，或者扫描表数据。</p>
</li>
</ol>
<p>这些“陷阱”的共同点是，它们都可能阻止数据库有效地利用已有的索引结构，从而将查询的复杂度从对数级别（索引查找）提升到线性级别（全表扫描）。</p>
<h3>如何选择适合的优化方案？</h3>
<p>选择一个合适的SQL优化方案，绝不是拍脑袋决定的事，它需要我们像个侦探一样，深入挖掘问题的本质，并权衡各种利弊。没有银弹，只有最适合当前业务场景和数据特点的方案。</p>
<ol>
<li>
<p><strong>明确你的业务需求和查询模式：</strong>
这是最关键的一步。用户到底想怎么搜？是精确匹配？前缀匹配？后缀匹配？还是任意位置的模糊匹配？是搜索商品名还是文章内容？</p>
<ul>
<li>如果大部分是前缀匹配（<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE 'keyword%'</pre></div>），那么普通的B-tree索引就足够了。</li>
<li>如果频繁需要任意位置的子串匹配（<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%keyword%'</pre></div>），且数据量大，全文检索几乎是唯一的正解。</li>
<li>如果只是偶尔的后缀匹配，且数据量不大，逆序索引可能是一个轻量级的选择。
搞清楚这些，能帮你排除掉很多不必要的复杂方案。</li>
</ul>
</li>
<li>
<p><strong>分析数据量和增长趋势：</strong></p>
<ul>
<li>
<strong>数据量小（几千到几万）：</strong> 很多时候，即使是全表扫描，性能也可能在可接受范围内。过度优化反而会增加开发和维护成本。</li>
<li>
<strong>数据量中等（几十万到几百万）：</strong> 这时性能问题会逐渐显现，需要开始考虑索引优化。</li>
<li>
<strong>数据量大（千万上亿）：</strong> 此时，任何导致全表扫描的查询都是灾难性的。必须使用高效的索引策略，如全文检索或分库分表。
同时，也要考虑数据是否持续增长，以及增长的速度。一个今天看起来没问题的方案，明天可能就扛不住了。</li>
</ul>
</li>
<li>
<p><strong>评估现有数据库的能力和资源：</strong>
你的数据库版本支持哪些高级特性？例如，MySQL 5.6+才支持<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">InnoDB</pre></div>的全文索引，PostgreSQL的全文检索功能非常强大。</p>
<ul>
<li>
<strong>数据库内置功能：</strong> 优先考虑利用数据库自带的优化功能，它们通常与数据库集成度最高，维护成本相对较低。</li>
<li>
<strong>外部搜索引擎：</strong> 如果数据库内置的全文检索功能无法满足需求（例如，需要更复杂的排名、高亮、多语言分词等），或者数据量非常庞大，那么集成Elasticsearch或Solr等外部搜索引擎是更好的选择。但这会增加系统架构的复杂性，需要额外的部署和维护成本。</li>
</ul>
</li>
<li>
<p><strong>权衡开发成本与维护成本：</strong></p>
<ul>
<li>
<strong>逆序存储：</strong> 虽然能解决特定问题，但它增加了额外的列，需要额外的逻辑来维护数据一致性（插入、更新时都需要同步逆序列），增加了开发和维护的复杂度。</li>
<li>
<strong>全文检索：</strong><ul>
<li>
<strong>数据库内置：</strong> 相对简单，配置和使用都比较直接。</li>
<li>
<strong>外部搜索引擎：</strong> 部署、配置、数据同步（ETL）、监控、故障排查等都需要投入资源。但它能提供更强大的搜索能力。
永远不要为了所谓的“极致性能”而过度设计，除非业务场景真的需要。</li>
</ul>
</li>
</ul>
</li>
<li>
<p><strong>使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>（或等效<a style="color:#f60; text-decoration:underline;" title="工具" href="https://www.php.cn/zt/16887.html" target="_blank">工具</a>）分析查询计划：</strong>
这是优化SQL的“诊断仪”。在任何优化之前和之后，都应该使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>来查看你的SQL语句是如何执行的。</p>
<ul>
<li>它会告诉你查询是否使用了索引，使用了哪个索引。</li>
<li>它会显示扫描了多少行数据。</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;">type: ref</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">type: range</pre></div>则表示使用了索引。</li>
</ul>
</li>
</ol>
<p>最终，选择优化方案是一个迭代的过程。先从最简单、最直接的方案开始，用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>验证效果，如果不够再考虑更复杂的方案。始终以业务需求为导向，以实际性能提升为目标。</p>
sed = REVERSE(your_column);
-- 为逆序列创建索引
CREATE INDEX idx_your_column_reversed ON your_table (your_column_reversed);
-- 查询时
SELECT * FROM your_table WHERE your_column_reversed LIKE REVERSE('keyword%'); -- 注意这里的keyword%</pre></div><p>这种方法适用于你明确知道需要进行后缀匹配的场景，但对于任意位置的子串匹配（<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">%keyword%</pre></div>），它就无能为力了。而且，它增加了存储开销和数据同步的复杂性。</p>
<p><strong>3. 优化业务逻辑，避免前置通配符</strong></p>
<p>这听起来有点像“废话”，但很多时候，我们确实可以从业务层面重新审视需求。用户真的需要查找任意位置的子串吗？</p>
<ul>
<li>
<strong>能否只允许前缀匹配？</strong> 如果业务允许，强制用户输入前缀，例如搜索“<a style="color:#f60; text-decoration:underline;" title="苹果" href="https://www.php.cn/zt/16994.html" target="_blank">苹果</a>”相关的商品，用户输入“苹果”，而不是“果”。这样就可以用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE 'keyword%'</pre></div>，这就能很好地利用B-tree索引。</li>
<li>
<strong>拆分搜索词？</strong> 如果用户输入“red apple”，能否将“red”和“apple”拆开，分别作为前缀进行多次查询，或者结合<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">AND</pre></div>条件？</li>
<li>
<strong>精确匹配优先？</strong> 很多搜索场景下，用户可能更倾向于精确匹配，模糊查询只是辅助。可以先尝试精确匹配，如果无结果再进行模糊匹配。</li>
</ul>
<p><strong>4. 牺牲性能，但确保查询能跑</strong></p>
<p>在数据量不大，或者查询频率极低的情况下，直接使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%keyword%'</pre></div>可能不是最坏的选择。但前提是你真的评估过，它的性能影响在可接受范围内。</p>
<ul>
<li>
<p><strong>使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">INSTR()</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LOCATE()</pre></div>函数：</strong> 这些函数可以查找子串的位置，但它们本质上也是全表扫描。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM your_table WHERE INSTR(your_column, 'keyword') > 0;</pre></div><p>相比<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%keyword%'</pre></div>，在某些数据库和特定场景下，它们的执行计划可能会略有不同，但通常不会带来质的提升。</p>
</li>
<li><p><strong>考虑数据结构调整：</strong> 如果你的业务场景就是频繁进行这类模糊查询，并且数据量巨大，那么可能需要重新思考数据存储结构，比如将需要模糊查询的文本内容独立出来，存储到专门的搜索引擎中。</p>
<div class="aritcle_card">
<a class="aritcle_card_img" href="/ai/753">
<img src="https://img.php.cn/upload/ai_manual/001/503/042/68b6dbb7df33c256.png" alt="Project IDX">
</a>
<div class="aritcle_card_info">
<a href="/ai/753">Project IDX</a>
<p>Google推出的一个实验性的AI辅助开发平台</p>
<div class="">
<img src="/static/images/card_xiazai.png" alt="Project IDX">
<span>166</span>
</div>
</div>
<a href="/ai/753" class="aritcle_card_btn">
<span>查看详情</span>
<img src="/static/images/cardxiayige-3.png" alt="Project IDX">
</a>
</div>
</li>
</ul>
<h3>为什么<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%通配符'</pre></div>会导致索引失效？</h3>
<p>这个问题，说白了，就是B-tree索引的特性决定的。B-tree索引是一种平衡树结构，它能高效地处理等值查询（<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>），以及前缀匹配的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE</pre></div>查询（<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE 'keyword%'</pre></div>）。</p>
<p>当你创建了一个B-tree索引，比如在<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">product_name</pre></div>列上，数据库会把<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">product_name</pre></div>的所有值按字母顺序<a style="color:#f60; text-decoration:underline;" title="排列" href="https://www.php.cn/zt/56129.html" target="_blank">排列</a>好，并存储在索引中。</p>
<ul>
<li><p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE 'apple%'</pre></div>：</strong> 数据库可以很轻松地在索引中找到以“apple”开头的第一条记录，然后顺着索引的叶子节点往后扫描，直到遇到不以“apple”开头的记录为止。这个过程是利用索引的有序性进行的，非常高效。</p></li>
<li><p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%apple'</pre></div>：</strong> 问题来了。数据库不知道以“apple”结尾的字符串在索引的哪个位置开始。它无法利用索引的排序特性来快速定位。想象一下，索引里有“banana”、“orange”、“pineapple”，你要找以“apple”结尾的，它得从头到尾把所有索引项（或者更糟糕，直接是表数据）都看一遍，看看哪个字符串的末尾是“apple”。这本质上就是全表扫描。</p></li>
<li><p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%apple%'</pre></div>：</strong> 同理，如果通配符在两边，那更没办法利用索引了。数据库无法判断“apple”这个子串会出现在哪个位置，只能遍历所有数据。</p></li>
</ul>
<p>所以，核心原因就是B-tree索引的有序性无法满足后缀或中缀匹配的需求，导致查询优化器不得不放弃索引，转而进行代价更高的全表扫描。</p>
<h3>除了全表扫描，还有哪些潜在的性能陷阱？</h3>
<p>SQL查询性能的坑远不止<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%通配符'</pre></div>这一种，很多时候，一些看似无害的操作，都可能让你的查询效率直线下降。在我看来，以下几点是特别常见的“陷阱”：</p>
<ol>
<li>
<p><strong>在索引列上使用函数或进行表达式操作：</strong>
当你在<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;">DATE()</pre></div>, <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">YEAR()</pre></div>, <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SUBSTRING()</pre></div>, <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>等）或者进行算术运算时，数据库同样无法直接利用索引。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>-- 索引失效：对索引列使用了函数
SELECT * FROM orders WHERE YEAR(order_date) = 2025;
-- 优化方式：将函数作用于常量，而不是列
SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';</pre></div><p>数据库需要先计算出每一行的函数结果，然后才能进行比较，这使得它无法在索引树中直接查找。</p>
</li>
<li>
<p><strong><a style="color:#f60; text-decoration:underline;" title="隐式类型转换" href="https://www.php.cn/zt/63333.html" target="_blank">隐式类型转换</a>：</strong>
如果你的查询条件中的数据类型与列的数据类型不匹配，数据库可能会尝试进行隐式类型转换。这个转换过程可能导致索引失效。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>-- 假设user_id是INT类型，但你用字符串比较
SELECT * FROM users WHERE user_id = '123';</pre></div><p>数据库可能会把<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;">'123'</pre></div>转换为数字，但如果转换发生在列上，就可能导致问题。</p>
</li>
<li>
<p><strong><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;">WHERE</pre></div>子句中包含多个<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">OR</pre></div>条件，并且这些条件涉及不同的列时，优化器可能难以有效地使用索引，甚至可能导致全表扫描。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM products WHERE product_name LIKE 'A%' OR category_id = 10;</pre></div><p>虽然某些数据库的优化器在特定情况下能处理好，但通常来说，如果两个条件都能使用各自的索引，优化器可能会选择合并索引扫描的结果，或者干脆进行全表扫描。有时拆分成<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">UNION ALL</pre></div>可以改善性能，但也要具体分析。</p>
</li>
<li>
<p><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;"><></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;"><></pre></div>操作通常难以利用索引，因为它们表示的是“不包含”或“不等于”某个值，数据库需要扫描大量数据来确认哪些是不符合条件的。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM users WHERE status <> 'inactive';</pre></div><p>对于这种场景，如果“不等于”的值是少数，而“等于”的值是多数，可以考虑将查询条件反过来写，或者使用<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>。</p>
</li>
<li>
<p><strong>对NULL值的处理：</strong>
在某些数据库中，索引默认不包含NULL值。因此，涉及到<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">IS NULL</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">IS NOT NULL</pre></div>的查询可能无法有效利用索引。</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>SELECT * FROM orders WHERE delivery_address IS NULL;</pre></div><p>这取决于索引类型和数据库配置。例如，MySQL的B-tree索引可以包含NULL值。但无论如何，查询NULL值通常意味着需要扫描索引中的所有NULL项，或者扫描表数据。</p>
</li>
</ol>
<p>这些“陷阱”的共同点是，它们都可能阻止数据库有效地利用已有的索引结构，从而将查询的复杂度从对数级别（索引查找）提升到线性级别（全表扫描）。</p>
<h3>如何选择适合的优化方案？</h3>
<p>选择一个合适的SQL优化方案，绝不是拍脑袋决定的事，它需要我们像个侦探一样，深入挖掘问题的本质，并权衡各种利弊。没有银弹，只有最适合当前业务场景和数据特点的方案。</p>
<ol>
<li>
<p><strong>明确你的业务需求和查询模式：</strong>
这是最关键的一步。用户到底想怎么搜？是精确匹配？前缀匹配？后缀匹配？还是任意位置的模糊匹配？是搜索商品名还是文章内容？</p>
<ul>
<li>如果大部分是前缀匹配（<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE 'keyword%'</pre></div>），那么普通的B-tree索引就足够了。</li>
<li>如果频繁需要任意位置的子串匹配（<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%keyword%'</pre></div>），且数据量大，全文检索几乎是唯一的正解。</li>
<li>如果只是偶尔的后缀匹配，且数据量不大，逆序索引可能是一个轻量级的选择。
搞清楚这些，能帮你排除掉很多不必要的复杂方案。</li>
</ul>
</li>
<li>
<p><strong>分析数据量和增长趋势：</strong></p>
<ul>
<li>
<strong>数据量小（几千到几万）：</strong> 很多时候，即使是全表扫描，性能也可能在可接受范围内。过度优化反而会增加开发和维护成本。</li>
<li>
<strong>数据量中等（几十万到几百万）：</strong> 这时性能问题会逐渐显现，需要开始考虑索引优化。</li>
<li>
<strong>数据量大（千万上亿）：</strong> 此时，任何导致全表扫描的查询都是灾难性的。必须使用高效的索引策略，如全文检索或分库分表。
同时，也要考虑数据是否持续增长，以及增长的速度。一个今天看起来没问题的方案，明天可能就扛不住了。</li>
</ul>
</li>
<li>
<p><strong>评估现有数据库的能力和资源：</strong>
你的数据库版本支持哪些高级特性？例如，MySQL 5.6+才支持<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">InnoDB</pre></div>的全文索引，PostgreSQL的全文检索功能非常强大。</p>
<ul>
<li>
<strong>数据库内置功能：</strong> 优先考虑利用数据库自带的优化功能，它们通常与数据库集成度最高，维护成本相对较低。</li>
<li>
<strong>外部搜索引擎：</strong> 如果数据库内置的全文检索功能无法满足需求（例如，需要更复杂的排名、高亮、多语言分词等），或者数据量非常庞大，那么集成Elasticsearch或Solr等外部搜索引擎是更好的选择。但这会增加系统架构的复杂性，需要额外的部署和维护成本。</li>
</ul>
</li>
<li>
<p><strong>权衡开发成本与维护成本：</strong></p>
<ul>
<li>
<strong>逆序存储：</strong> 虽然能解决特定问题，但它增加了额外的列，需要额外的逻辑来维护数据一致性（插入、更新时都需要同步逆序列），增加了开发和维护的复杂度。</li>
<li>
<strong>全文检索：</strong><ul>
<li>
<strong>数据库内置：</strong> 相对简单，配置和使用都比较直接。</li>
<li>
<strong>外部搜索引擎：</strong> 部署、配置、数据同步（ETL）、监控、故障排查等都需要投入资源。但它能提供更强大的搜索能力。
永远不要为了所谓的“极致性能”而过度设计，除非业务场景真的需要。</li>
</ul>
</li>
</ul>
</li>
<li>
<p><strong>使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>（或等效<a style="color:#f60; text-decoration:underline;" title="工具" href="https://www.php.cn/zt/16887.html" target="_blank">工具</a>）分析查询计划：</strong>
这是优化SQL的“诊断仪”。在任何优化之前和之后，都应该使用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>来查看你的SQL语句是如何执行的。</p>
<ul>
<li>它会告诉你查询是否使用了索引，使用了哪个索引。</li>
<li>它会显示扫描了多少行数据。</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;">type: ref</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">type: range</pre></div>则表示使用了索引。</li>
</ul>
</li>
</ol>
<p>最终，选择优化方案是一个迭代的过程。先从最简单、最直接的方案开始，用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>验证效果，如果不够再考虑更复杂的方案。始终以业务需求为导向，以实际性能提升为目标。</p>以上就是sql语句怎样避免因like查询使用通配符开头导致的索引失效 sql语句like通配符开头致索引失效的常见问题解决的详细内容，更多请关注其它相关文章！
# 不等于
# 厦门seo蜘蛛屯
# 最新的seo技术
# 武汉便宜的网站推广
# 医疗网站建设平台价格
# 广告营销推广总结报告
# 上海网站优化哪家好
# 石家庄晋州网络推广营销
# 丰县优化网站关键词
# 曲靖全渠道营销推广方案
# seo采集站项目
# 而不是
# 也要
# 你可以
# 都有
# 使用了
# sql应用
# 它会
# 是一个
# 隐式
# 这是
# 为什
# 隐式类型转换
# 排列
# sql语句
# sql优化
# 解决方法
# ai
# 苹果
# 工具
# mysql
# sql语句如何处理
相关栏目：
【
科技资讯46185 】
【
网络学院92790 】
相关推荐：
漫蛙2漫画入口 漫蛙正版网页漫画直达网址
composer的"require-dev"部分是用来做什么的？
漫蛙漫画官方首页 漫蛙2漫画在线阅读入口
优化MinIO list_objects_v2 操作的性能瓶颈与最佳实践
poki免费入口快捷访问 poki人气小游戏直接玩站点
C++如何实现一个装饰器模式_C++设计模式之动态地给对象添加额外职责
c++如何使用std::memory_order控制原子操作顺序_c++ C++11内存模型详解
Excel Power Pivot如何处理XML数据源 构建高级数据模型
c++如何使用Meson构建系统_c++比CMake更快的构建工具
支付宝如何设置安全保护_支付宝安全设置的全面教程
QQ邮箱官方网站登录入口_QQ邮箱网页版在线使用
QQ邮箱网页版邮箱入口 QQ邮箱官方登录平台
Adobe PDF表单中利用J*aScript解析与格式化日期组件的教程
在VS Code中配置和运行Dart程序的完整步骤
优化Django表单：提交验证失败后保留用户输入
CSS子选择器：如何区分并样式化嵌套列表的子层级
age动漫网站入口 age动漫官网直接访问入口
谷歌邮箱网页版官方页面入口 谷歌邮箱网页端快速访问
PPT平滑切换怎么做 PPT炫酷“平滑”切换动画制作教程【必学】
win11开机启动修复循环怎么办 Win11无法进入系统高级启动解决方法【修复】
AO3最新入口2025公告_AO3中文官网合集
斑马英语APP如何开启夜间护眼阅读_斑马英语APP夜间模式与低蓝光设置教程
c++中为什么推荐使用using替代typedef_c++现代化类型别名
夸克浏览器网页版最新地址 夸克浏览器官方入口合集
Sublime Text怎么显示空格和制表符_Sublime显示不可见字符设置
如何高效处理PHP中的Excel数据导入导出？PortPHP/Spreadsheet助你轻松搞定！
如何在CSS中使用浮动制作导航栏_float实现水平菜单
126邮箱网页版官方入口 126邮箱账号在线登录平台
如何仅使用CSS更改登录界面背景图像图标的颜色
163邮箱登录密码 163邮箱忘记密码找回
XML中包含HTML标签导致解析错误？ 正确嵌入非XML数据的两种方法
极速漫画官方主页网址 极速漫画漫画在线浏览官网链接
学习通在线学习平台 学习通网页版直接进入课程中心
Win11怎么开启省电模式_Win11电池节电模式自动开启
处理动态列数据：J*a ArrayList的正确初始化与字符累加教程
Golang如何优雅处理error_Golang error处理最佳实践总结
sublime如何优雅地处理行尾空格_sublime自动清理多余空白字符配置
Python实时数据流中的动态最值查找策略
Win11怎么设置开机NumLock亮 Win11修改注册表InitialKeyboardIndicators值
树莓派传感器触发：通过Twilio API发送WhatsApp消息教程
Lar*el用户头像管理：实现图片缩放、存储与旧文件安全删除的最佳实践
Bing引擎入口最新2025 Bing搜索免费官方登录
Windows10怎么开启存储感知 Windows10系统设置自动清理临时文件释放C盘空间【教程】
sublime如何配置Go语言开发环境_sublime搭建Golang编译运行系统
css链接悬停下划线样式如何自定义_使用::after结合content和transition
解决Django多数据库/多Schema环境下外键迁移问题
马斯克：Optimus 人形机器人复数形式为 Optimi
Typer应用中动态命令行参数的解析与处理
php源码怎么看淘宝客系统_看php源码淘宝客系统技巧
J*a递归快速排序中静态变量的状态管理与陷阱
