Oracle优化——LIKE与索引（以%开头的LIKE会不走索引或走索引全扫描）

最新推荐文章于 2025-10-24 16:46:30 发布
原创 最新推荐文章于 2025-10-24 16:46:30 发布 · 7k 阅读 · 3 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#oracle #sql #性能优化

本文通过示例介绍了Oracle中LIKE操作符如何影响索引的使用。以%开头的LIKE表达式会导致全表扫描或不使用索引，而强制使用索引可能导致全索引扫描，效率较低。 
这样会走索引范围扫描，因为这个表达式有前导性。
SH@ prod> set autotrace on
SH@ prod> select max(cust_credit_limit) , count(*) from customers_ne where cust_last_name like 'Vaugh%' ;

MAX(CUST_CREDIT_LIMIT)   COUNT(*)
---------------------- ----------
                 11000         81


Execution Plan
----------------------------------------------------------
Plan hash value: 3473995898

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    16 |    13   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |              |     1 |    16 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS_NE |    61 |   976 |    13   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN
最低0.47元/天 解锁文章
警惕 Oracle 索引优化时陷阱之无效的索引范围扫描(INDEX RANGE SCAN)导致的全表扫描
Defonds 的专栏
07-06 3173
生产环境慢查询统计中，发现表 STATUS 的 MILESTONE 字段条件查询时进行了全表扫描。表 STATUS 的 MILESTONE 字段定义如下： 针对上述问题创建索引： create index STATUS_MILESTONE on STATUS("MILESTONE") tablespace DFS_INDEX2; 分析执行计划发现问题语句依然走的是全表扫描，新建的索引 STATUS_MILESTONE 没有用到。第一反应是索引是不是没有创建成功？ 查看索引库发现该索引确实存在并且生效：
like百分号在前走不走索引
vonlinee的博客
09-25 580
MOBILE FROM t_user WHERE MOBILE LIKE '%12’这种情况没走是因为查了非索引字段，需要回表查询name字段值，如果使用INDEX_MOBILE索引，则要进行一次全索引扫描，同时还要进行一次聚簇索引扫描（即全表扫描），不如直接进行全表扫描。通过EXPLAIN ANALYZE可以看到，过滤成本是13484.35，扫描了14865行，整张表的数据150369条，而符合条件的有1493条，也就是说为了找出这1493条数据，扫描了133801行，大约占全表的89%左右。
以通配符（%）开始的like字符串，走索引
weixin_33957648的博客
11-13 239
在对oracle的SQL优化过程中经常会遇到【like'%abc'】破坏索引的问题，但是如果真有此类需求，该如何在不破坏索引的基础上进行查询呢。 [sql] view plain copy sys@mescp> select reverse('123') from dual; REVERSE('123') -------------------------------- ...
Oracle 索引
Warren专栏
04-14 701
oracle like 上强制使用索引当where子句对某一列使用函数时，除非利用这个简单的技术强制索引，否则Oracle优化器不能在查询中使用索引。通常情况下，如果在WHERE子句中不使用诸如UPPER、REPLACE 或SUBSTRD等函数，就不能对指定列建立特定的条件。但如果使用了这些函数，则会出现一个问题：这些函数会阻碍Oracle优化器对列使用索引，因而与采用索引的情况相比较，查询会花费更多的时间。庆幸的是，如果在使用函数的这些列中包含了字符型数据，可以用这样一种方法修改查询语句，以达到强制性使用
【备战大厂JAVA面试MySQL篇】大白话解释一下为什么LIKE以%开头索引会失效，秒懂
最新发布
不朽的博客
10-24 934
LIKE查询以%开头（如LIKE'%abc'）会导致索引失效，原因是B+树索引只能利用从左到右的有序性进行前缀匹配查询。当查询条件跳过前缀（如后缀匹配或中间匹配）时，索引无法定位起始位置，只能全表扫描。虽然覆盖索引可以部分优化性能，但建议尽量使用前缀匹配（LIKE'abc%'），或采用全文索引、数据预处理（反转存储）等替代方案。索引失效的本质在于破坏了B+树的有序性，就像字典索引无法查找以指定结尾的单词一样。
Oracle like 走索引
GeorgeChan_的博客
08-26 4233
like语句也能走索引
oracle中索引的使用
03-22
NULL 博文链接：https://wxz2832840.iteye.com/blog/973305
ORACLE like 也可以走索引
zhouyong19871123的专栏
07-04 9546
通常情况下，l摸个索引字段 like '%xxx' 这种是不走索引的，但是like 'xxx%' 这种是走索引的 但是有时候查询的时候需要使用like '%xxx' 这种方式，但是不走索引，影响查询效率 后来发现oracle 可以建立一个反向索引，于是再这个列上再建立一个反向索引 于是任何一种like 都可以走索引了 create index CRM_LTE_2.IDX_REVERSE_P...
oracle索引列like走索引吗,Oracle优化——LIKE与索引（以%开头的LIKE会不走索引或走索引全扫描）...
weixin_29801567的博客
04-14 1419
这样会走索引范围扫描，因为这个表达式有前导性。SH@ prod> set autotrace onSH@ prod> select max(cust_credit_limit) , count(*) from customers_ne where cust_last_name like 'Vaugh%' ;MAX(CUST_CREDIT_LIMIT) COUNT(*)-------...
oracle右模糊查询不使用索引,oracle like模糊查询不能走索引？
weixin_29025501的博客
04-03 967
这里要纠正一个网上很多教程说的模糊匹配不能走索引的说法，因为在看《收获，不止SQL优化》一书，里面举例说到了，并且自己也跟着例子实践了一下，确实like一些特殊情况也是可以走索引的例子来自《收获，不止SQL优化》一书，实践准备：//建表，注意要非空数据drop table t purge;create table t as select * from dba_objects where objec...
MySQL Like模糊查询速度太慢如何解决
12-14
- 在Oracle中，对于`LIKE '%%'`应避免使用，因为这会导致全表扫描。 - 对于`LIKE '%...%'`（不以%结尾），可以利用`REVERSE`函数和反向索引来优化为`LIKE '%'`的形式。 - 对于非`'%%'`的`LIKE`查询，可以考虑使用...
oracle 全文索引优化like
数据库
04-27 615
最近一个业务，本来调的差不多了，但是新问题又来了，发现两条LIKE '%XXX%',看到这个，心碎了，表记录现在大约11W吧，全表扫描啊，你妹的，这种SQL其实业务就不改让上，直接打回去重写好了。 可是，现在只能从我这边做优化了，问了问开发，只能是完全模糊查询，'%xxx'和'xxx%'都不行啊，'xxx%'大家知道，一般都可以用到索引，'%xxx'这个其实也好优化，用reverse呗，之后又...
ORACLE实现类似LIKE运算符的模糊匹配走索引-全文索引
weixin_44248903的博客
01-17 1116
ORACLE实现类似LIKE运算符的模糊匹配走索引-全文索引
Oracle like '%...%' 优化
xuefengJ的专栏
04-18 1311
Oracle like '%...%' 优化 1。尽量不要使用 like '%%' 2。对于 like '%' (不以 % 开头)，Oracle可以应用 colunm上的index 3。对于 like '%…' 的 (不以 % 结尾)，可以利用reverse + function index 的形式，变化成 like '%' 4.非用like'%%'不可时，使用Oracle内部函数：...
oracle会隐式转换吗,Oracle like、不等于、隐式转换走索引与不走索引情况
weixin_36160969的博客
04-02 1178
1. 概述#like(1)当使用like查询时，后模糊匹配，则走索引，如like'test%'(2)当使用like查询时，前模糊匹配，则不走索引，如like'%test'#<>不走索引因为不等于，即等于大量数据，所以不走索引#隐式转换，当发生在索引列时，不走索引，发生在条件值列时，走索引(1)如果隐式转换发生在值列，则走索引，例如查询使用日期查询时，select*fro...
Oracle like使用索引
wtopps的专栏
09-22 3261
Oracle 中 like 是否可以使用索引当我们扫描一个特别大的表的时候，如果不使用索引，那么查询的效率会非常的慢，当我们使用like条件的时候，是否会触发索引？我们来做一下实验：SELECT * FROM t_ka02 where aka060 like '%86903318000482%'其中aka060是t_ka02的索引，查看下执行效率我们可以看到，查询对全表进行了扫描，并没有进行触发索引
Oracle（一）-Oracle中的Like语句与索引
DK的专栏
10-27 1871
索引的使用    能使用索引的情况：Like前导字符，比如c like 'DBA%'，这样可以使用c列的索引;    不能能使用索引的情况：Like非前导字符，比如c like '%DBA'，这样不可以使用c列的索引   原因     因为索引是顺序的是按照字母的顺序储存的。比如：a11 a21 d11 a31在索引中储存的顺序为 a11 a21 a31 d11，所以用like匹配，只能
like %str%优化
lixiaohong890316的博客
05-14 1037
Oracle like '%...%' 优化 1。尽量不要使用 like '%%' 2。对于 like '%' (不以 % 开头)，Oracle可以应用 colunm上的index 3。对于 like '%…' 的 (不以 % 结尾)，可以利用reverse + function index 的形式，变化成 like '%' 4.非用like'%%'不可时，使用...
oracle索引列like走索引吗,sql – Oracle查询在索引号列上使用“like”,性能不佳
weixin_32866779的博客
04-14 254
在查询1上,正在执行全表扫描,即使id是索引列.查询2实现了相同的结果,但更快.如果查询1运行返回索引列,那么它将快速返回,但如果返回非索引列,或者整个行是查询需要更长时间.在查询3中,它运行速度很快,但是“代码”列是VARCHAR2(10)而不是NUMBER(12),并且与“id”相同.为什么Query 1不能拿起它应该使用索引？有什么应该改变,以允许索引号码列执行更快？[查询1]select ...
如果要like '%1%'这样的能不能走索引
02-21
<think>好的，我现在需要解决的问题是，当SQL查询中使用`LIKE '%1%'`这样的条件时，是否可以利用索引来优化查询性能。用户之前已经询问过关于`LIKE '1%'`的优化方法，现在转向更广泛的情况，即中间有通配符的情况。我需要先回忆一下数据库索引的基本原理，特别是关于字符串前缀、后缀和中间匹配的情况。 首先，数据库索引，尤其是B树索引，通常最有效的是前缀匹配。例如，`LIKE '1%'`可以利用索引，因为索引是按顺序存储的，查询可以快速定位到以'1'开头的字符串。但是，当使用前导通配符如`LIKE '%1%'`时，情况就不同了，因为数据库无法有效利用索引来快速定位，可能需要全表扫描，这会降低查询性能。 接下来，我需要考虑是否有其他类型的索引可以处理这种情况。例如，全文索引或者反向索引。比如，在MySQL中，如果使用全文索引（FULLTEXT），可以处理更复杂的文本搜索，包括中间匹配。但用户的问题可能是在普通B树索引的情况下，是否可行。 另外，某些数据库支持函数索引，比如在字段上创建反向索引，将字符串反转后存储，这样查询`LIKE '%1'`可以转换为`LIKE '1%'`在反转后的字段上，从而利用索引。但这种方法需要预先处理数据，并且查询也需要相应调整。 还需要考虑覆盖索引的可能性，即索引包含所有需要的列，避免回表操作。但即使如此，如果查询条件本身无法有效使用索引，覆盖索引可能也无法带来性能提升。 此外，用户可能想知道，当必须使用`LIKE '%1%'`时，有哪些优化策略，比如使用更高效的全文检索工具（如Elasticsearch），或者数据预处理，比如将字符串分词存储，或者使用触发器维护一个反向的字段。 另外，需要提到数据库的具体实现差异，比如MySQL、PostgreSQL等对LIKE子句和索引的支持可能不同，可能需要具体分析。但用户的问题没有指定数据库类型，所以需要一般性的回答。 最后，总结可能的优化方法，包括使用全文索引、反向索引、函数索引（如果支持），或者应用层的优化，如缓存结果、分页查询减少数据量等。同时，需要指出即使无法有效使用索引，合理的索引策略和查询优化仍能部分提升性能，比如减少查询涉及的字段，避免不必要的连接等。 需要注意的是，用户之前的问题已经涉及了LEFT JOIN改为INNER JOIN的优化，所以在这里可能需要强调，当LIKE条件无法利用索引时，其他优化手段的重要性，比如减少JOIN的数量或优化WHERE子句中的其他条件。 另外，要提醒用户，EXPLAIN命令是分析查询执行计划的关键工具，通过查看执行计划可以确认索引是否被使用，以及是否存在全表扫描等问题。 最后，要确保回答结构清晰，分点说明，让用户能够逐步理解不同的优化策略及其适用场景。</think>当 SQL 查询中使用 **`LIKE '%1%'`**（即模糊匹配字符串中任意位置包含 `1` 的情况），能否利用索引取决于数据库的类型、索引的设计以及字段的具体特性。以下是详细的解释和优化建议： -- ### **1. 为什么 `LIKE '%1%'` 通常无法直接利用索引？** - **B树索引的局限性** 大多数数据库（如 MySQL、PostgreSQL）默认使用 B树索引，其优势在于**前缀匹配**（如 `LIKE '1%'`）。但 `LIKE '%1%'` 需要扫描整个字符串的任意位置是否包含 `1`，B树索引无法高效支持这种模式。 - **全表扫描的风险** 如果没有合适的索引，数据库可能需要对整个字段值逐行扫描，导致性能下降（尤其是大表）。 --- ### **2. 可能的优化方法** #### **(1) 使用全文索引（FULLTEXT）** - **适用场景**：高频的模糊匹配查询（如搜索功能）。 - **实现方式**： ```sql -- 创建全文索引（以 MySQL 为例） ALTER TABLE table_name ADD FULLTEXT INDEX idx_content (column_name); -- 查询使用全文索引 SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('+1' IN BOOLEAN MODE); -- 匹配包含 '1' 的内容 ``` - **优势**：全文索引专为文本搜索设计，支持高效的关键词匹配。 - **限制**：仅适用于文本字段，且需数据库支持（如 MySQL 的 `FULLTEXT`，PostgreSQL 的 `GIN` 索引）。 --- #### **(2) 反向索引（Reverse Index）** - **适用场景**：后缀匹配（如 `LIKE '%1'`），但也可辅助中间匹配。 - **实现方式**： 1. 存储字段的反向值（如 `'abc123'` 存为 `'321cba'`）。 2. 对反向字段创建索引，查询时反转条件： ```sql -- 原查询：LIKE '%1%' -- 反转后查询：LIKE '1%' OR LIKE '%1'（需结合业务逻辑拆分） SELECT * FROM table_name WHERE original_column LIKE '%1%'; -- 改为反向字段查询（示例）： SELECT * FROM table_name WHERE reversed_column LIKE '1%' -- 匹配原字段中以 '1' 结尾的内容 OR reversed_column LIKE '%1'; -- 匹配原字段中以 '1' 开头的内容 ``` - **优势**：将部分中间匹配转为前缀或后缀匹配，可能利用索引。 - **限制**：需要额外存储和更新反向字段，逻辑复杂。 --- #### **(3) 函数索引（Function-Based Index）** - **适用场景**：数据库支持函数索引时（如 Oracle、PostgreSQL）。 - **实现方式**： ```sql -- PostgreSQL 示例：创建小写化索引 CREATE INDEX idx_lower_column ON table_name (LOWER(column_name)); -- 查询时使用函数匹配 SELECT * FROM table_name WHERE LOWER(column_name) LIKE '%1%'; ``` - **优势**：可对字段处理后的值建立索引，支持复杂查询。 - **限制**：依赖数据库支持，且需匹配函数逻辑。 --- #### **(4) 覆盖索引 + 减少回表** - **适用场景**：查询仅需索引中的字段，无需回查主表。 - **实现方式**： 创建包含查询所需字段的联合索引，如： ```sql -- 假设需要返回 column_a 和 column_b CREATE INDEX idx_covering ON table_name (target_column, column_a, column_b); -- 查询时仅访问索引 SELECT column_a, column_b FROM table_name WHERE target_column LIKE '%1%'; ``` - **优势**：避免回表操作，减少 I/O。 - **限制**：需查询字段全部在索引中，且索引体积可能较大。 --- ### **3. 其他优化策略** - **分页缓存**：对高频查询结果缓存，减少数据库压力。 - **数据预处理**： 将字段拆分为分词（如 `'abc123'` 拆为 `['abc', '123']`），单独存储并建立索引。 - **使用外部搜索引擎**： 对海量模糊匹配场景，将数据同步到 Elasticsearch 等搜索引擎，利用倒排索引加速查询。 --- ### **4. 执行计划验证** 无论采用哪种方法，务必使用 `EXPLAIN` 分析执行计划，确认索引是否生效： ```sql EXPLAIN SELECT * FROM table_name WHERE column LIKE '%1%'; ``` 重点关注： - `type` 列是否为 `index`（索引扫描）或 `range`（范围扫描）。 - `Extra` 列是否出现 `Using where; Using index`（覆盖索引生效）。 --- ### **总结** - **`LIKE '%1%'` 默认难以利用 B树索引**，但可通过全文索引、反向索引、函数索引或覆盖索引间接优化。 - **优先考虑业务需求**：是否真的需要任意位置模糊匹配？能否改用前缀匹配（`LIKE '1%'`）？ - **权衡成本和收益**：复杂的优化方案可能增加维护成本，需结合查询频率和数据规模选择。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值