使用LIKE '%通配符'会因B-tree索引无法支持后缀匹配而导致全表扫描，解决方法包括：1. 采用全文检索（如MySQL FULLTEXT、PostgreSQL GIN索引）高效处理任意子串查询；2. 对后缀查询使用逆序存储并创建索引；3. 优化业务逻辑，优先前缀匹配或精确查询；4. 在数据量小或低频场景下可容忍全表扫描；5. 避免在索引列使用函数、隐式类型转换、OR条件等导致索引失效的操作；选择方案需结合查询模式、数据规模、数据库能力及维护成本，并通过EXPLAIN验证执行计划。
SQL语句中使用
LIKE '%通配符'开头进行查询，确实是个性能杀手，因为它几乎总是导致索引失效，进而引发全表扫描。要避免这个问题，核心思路就是想办法让查询条件能够利用到索引，或者干脆绕开传统索引的限制。
解决
LIKE '%通配符'导致索引失效的问题，主要有以下几种策略，每种都有其适用场景和考量：
1. 利用全文检索（Full-Text Search）
这是处理模糊查询，特别是包含任意位置子串查询的最优解之一。主流数据库如MySQL、PostgreSQL、SQL Server都有内置的全文检索功能，或者可以集成Elasticsearch、Solr等外部搜索引擎。
MySQL的全文索引： 适用于
MyISAM和
InnoDB存储引擎（MySQL 5.6+）。你需要为要查询的列创建
FULLTEXT索引。
ALTER TABLE your_table ADD FULLTEXT(your_column);
SELECT * FROM your_table WHERE MATCH(your_column) AGAINST('keyword' IN NATURAL LANGUAGE MODE);它的工作原理是为文本内容建立倒排索引，能高效地查找包含特定词语的文档。对于中文，可能需要额外的分词插件。
PostgreSQL的tsvector
和tsquery
： PostgreSQL的全文检索功能非常强大和灵活。
-- 创建索引
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');它允许你定义不同的语言配置和词典，处理多语言文本能力出色。
2. 逆序存储与查询（Reverse Indexing）
这是一种比较巧妙的技巧，但有其局限性。如果你的查询模式主要是
LIKE '%keyword'（即以某个词结尾），你可以考虑在表中增加一个额外的列，存储原始列内容的逆序字符串，并在这个逆序列上创建索引。
例如，如果原列是
'apple'，逆序列就存
'elppa'。当你想查找
LIKE '%ple'时，实际上查询逆序列的
LIKE 'elp%'，这样就能利用到索引了。
-- 增加逆序列
ALTER TABLE your_table ADD COLUMN your_column_reversed VARCHAR(255);
-- 插入或更新时同步逆序数据
UPDATE your_table SET your_column_reversed = 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%这种方法适用于你明确知道需要进行后缀匹配的场景，但对于任意位置的子串匹配（
%keyword%），它就无能为力了。而且，它增加了存储开销和数据同步的复杂性。
3. 优化业务逻辑，避免前置通配符
这听起来有点像“废话”，但很多时候，我们确实可以从业务层面重新审视需求。用户真的需要查找任意位置的子串吗？
LIKE 'keyword%'，这就能很好地利用B-tree索引。
AND条件？
4. 牺牲性能，但确保查询能跑
在数据量不大，或者查询频率极低的情况下，直接使用
LIKE '%keyword%'可能不是最坏的选择。但前提是你真的评估过，它的性能影响在可接受范围内。
使用INSTR()
或LOCATE()
函数： 这些函数可以查找子串的位置，但它们本质上也是全表扫描。
SELECT * FROM your_table WHERE INSTR(your_column, 'keyword') > 0;
相比
LIKE '%keyword%'，在某些数据库和特定场景下，它们的执行计划可能会略有不同，但通常不会带来质的提升。
考虑数据结构调整： 如果你的业务场景就是频繁进行这类模糊查询，并且数据量巨大，那么可能需要重新思考数据存储结构，比如将需要模糊查询的文本内容独立出来，存储到专门的搜索引擎中。
LIKE '%通配符'会导致索引失效？
这个问题，说白了，就是B-tree索引的特性决定的。B-tree索引是一种平衡树结构，它能高效地处理等值查询（
=）和范围查询（
>、
<、
BETWEEN），以及前缀匹配的
LIKE查询（
LIKE 'keyword%'）。
当你创建了一个B-tree索引，比如在
product_name列上，数据库会把
product_name的所有值按字母顺序排列好，并存储在索引中。
LIKE 'apple%'
： 数据库可以很轻松地在索引中找到以“apple”开头的第一条记录，然后顺着索引的叶子节点往后扫描，直到遇到不以“apple”开头的记录为止。这个过程是利用索引的有序性进行的，非常高效。
LIKE '%apple'
： 问题来了。数据库不知道以“apple”结尾的字符串在索引的哪个位置开始。它无法利用索引的排序特性来快速定位。想象一下，索引里有“banana”、“orange”、“pineapple”，你要找以“apple”结尾的，它得从头到尾把所有索引项（或者更糟糕，直接是表数据）都看一遍，看看哪个字符串的末尾是“apple”。这本质上就是全表扫描。
LIKE '%apple%'
： 同理，如果通配符在两边，那更没办法利用索引了。数据库无法判断“apple”这个子串会出现在哪个位置，只能遍历所有数据。
所以，核心原因就是B-tree索引的有序性无法满足后缀或中缀匹配的需求，导致查询优化器不得不放弃索引，转而进行代价更高的全表扫描。
SQL查询性能的坑远不止
LIKE '%通配符'这一种，很多时候，一些看似无害的操作，都可能让你的查询效率直线下降。在我看来，以下几点是特别常见的“陷阱”：
在索引列上使用函数或进行表达式操作： 当你在
WHERE子句中对索引列应用函数（如
DATE(),
YEAR(),
SUBSTRING(),
UPPER(),
LOWER()等）或者进行算术运算时，数据库同样无法直接利用索引。
-- 索引失效：对索引列使用了函数 SELECT * FROM orders WHERE YEAR(order_date) = 2025; -- 优化方式：将函数作用于常量，而不是列 SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';
数据库需要先计算出每一行的函数结果，然后才能进行比较，这使得它无法在索引树中直接查找。
隐式类型转换： 如果你的查询条件中的数据类型与列的数据类型不匹配，数据库可能会尝试进行隐式类型转换。这个转换过程可能导致索引失效。
-- 假设user_id是INT类型，但你用字符串比较 SELECT * FROM users WHERE user_id = '123';
数据库可能会把
user_id列的每个值都转换为字符串再进行比较，或者把
'123'转换为数字，但如果转换发生在列上，就可能导致问题。
OR
条件：
当
WHERE子句中包含多个
OR条件，并且这些条件涉及不同的列时，优化器可能难以有效地使用索引，甚至可能导致全表扫描。
SELECT * FROM products WHERE product_name LIKE 'A%' OR category_id = 10;
虽然某些数据库的优化器在特定情况下能处理好，但通常来说，如果两个条件都能使用各自的索引，优化器可能会选择合并索引扫描的结果，或者干脆进行全表扫描。有时拆分成
UNION ALL可以改善性能，但也要具体分析。
NOT IN
或<>
（不等于）操作：
NOT IN和
<>操作通常难以利用索引，因为它们表示的是“不包含”或“不等于”某个值，数据库需要扫描大量数据来确认哪些是不符合条件的。
SELECT * FROM users WHERE status <> 'inactive';
对于这种场景，如果“不等于”的值是少数，而“等于”的值是多数，可以考虑将查询条件反过来写，或者使用
NOT EXISTS或
LEFT JOIN ... WHERE ... IS NULL。
对NULL值的处理： 在某些数据库中，索引默认不包含NULL值。因此，涉及到
IS NULL或
IS NOT NULL的查询可能无法有效利用索引。
SELECT * FROM orders WHERE delivery_address IS NULL;
这取决于索引类型和数据库配置。例如，MySQL的B-tree索引可以包含NULL值。但无论如何，查询NULL值通常意味着需要扫描索引中的所有NULL项，或者扫描表数据。
这些“陷阱”的共同点是，它们都可能阻止数据库有效地利用已有的索引结构，从而将查询的复杂度从对数级别（索引查找）提升到线性级别（全表扫描）。
选择一个合适的SQL优化方案，绝不是拍脑袋决定的事，它需要我们像个侦探一样，深入挖掘问题的本质，并权衡各种利弊。没有银弹，只有最适合当前业务场景和数据特点的方案。
明确你的业务需求和查询模式： 这是最关键的一步。用户到底想怎么搜？是精确匹配？前缀匹配？后缀匹配？还是任意位置的模糊匹配？是搜索商品名还是文章内容？
LIKE 'keyword%'），那么普通的B-tree索引就足够了。
LIKE '%keyword%'），且数据量大，全文检索几乎是唯一的正解。
分析数据量和增长趋势：
评估现有数据库的能力和资源： 你的数据库版本支持哪些高级特性？例如，MySQL 5.6+才支持
InnoDB的全文索引，PostgreSQL的全文检索功能非常强大。
权衡开发成本与维护成本：
使用EXPLAIN
（或等效工具）分析查询计划：
这是优化SQL的“诊断仪”。在任何优化之前和之后，都应该使用
EXPLAIN来查看你的SQL语句是如何执行的。
EXPLAIN的输出，你可以验证你的优化方案是否真的起作用了，而不是凭空猜测。例如，看到
type: ALL通常就意味着全表扫描，而
type: ref或
type: range则表示使用了索引。
最终，选择优化方案是一个迭代的过程。先从最简单、最直接的方案开始，用
EXPLAIN验证效果，如果不够再考虑更复杂的方案。始终以业务需求为导向，以实际性能提升为目标。