Where条件导致查询不走索引的情况

最新推荐文章于 2023-12-30 09:38:01 发布
转载 最新推荐文章于 2023-12-30 09:38:01 发布 · 2.5k 阅读 · 1
文章标签：

#数据库

本文通过四个具体场景探讨了SQL查询中索引无法被有效利用的情况，包括不等于操作、空值判断、函数调用及类型不匹配等，并提供了详细的执行计划对比。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

创建测试用表:test,test2,test3

SQL> create table test as select object_id, owner, object_name, created from dba_objects;

Table created
SQL >create table test2 as select * from test where owner in ('SYS','OAK');

Table created.

SQL >create index test_ind1 on test(object_id);

Index created.
SQL >create index  test2_ind1 on test2(owner);

SQL >exec dbms_stats.gather_table_stats(user,'test');

PL/SQL procedure successfully completed.


SQL >exec dbms_stats.gather_index_stats(user,'test_ind1');

PL/SQL procedure successfully completed.

SQL >exec dbms_stats.gather_table_stats(user,'test2');

PL/SQL procedure successfully completed.


SQL >exec dbms_stats.gather_index_stats(user,'test2_ind1');

PL/SQL procedure successfully completed.
SQL >exec dbms_stats.gather_table_stats(user,'test3');

PL/SQL procedure successfully completed.

SQL >exec dbms_stats.gather_index_stats(user,'test3_ind1');

PL/SQL procedure successfully completed.

SQL >select count(*) from test;

  COUNT(*)
----------
     61684

SQL >select count(*) from test where object_id is null;

  COUNT(*)
----------
        40

SQL >select count(*),owner from test2 group by owner;

  COUNT(*) OWNER
---------- ------------------------------
     23225 SYS
         2 OAK

SQL >create table test3(object_id varchar2(20), owner varchar2(30));

Table created.

SQL >insert into test3 select object_id,owner from test;

61684 rows created.

SQL >create index test3_ind1 on test3(object_id);

Index created.

情况一:
Where条件中存在不等于操作(<>,!=)
SQL >select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID  9zfw20r27fb6j, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test2 where wner='OAK'

Plan hash value: 2486100180

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2      |     1 |    40 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST2_IND1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='OAK')

SQL >select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  1zpaqum6fznd8, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test2 where owner !='SYS'

Plan hash value: 300966803

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |    35 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST2 |     1 |    40 |    35   (3)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"<>'SYS')


情况二:
Where条件中存在is null 或 is not null

SQL >select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  fyqmqv95dy7yx, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test where object_id is null

Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    90 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST |    40 |  1640 |    90   (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID" IS NULL)


情况三:
使用函数,而没有基于函数的索引
SQL >select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  2apc04k69zvww, child number 0
-------------------------------------
select /* gather_plan_statistics */ * from test2 where
lower(owner)='oak'

Plan hash value: 300966803

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |    35 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST2 |   232 |  9280 |    35   (3)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(LOWER("OWNER")='oak')


情况四:比较条件类型字段类型不匹配
SQL >select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  fjj3yqs8dcq7z, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test3 where object_id=60

Plan hash value: 3306317399

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |    36 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST3 |     1 |    11 |    36   (9)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("OBJECT_ID")=60)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7419833/viewspace-675169/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7419833/viewspace-675169/

cnyp2016
关注
dblink 不走索引 查询_oracle查询不走索引的一些情况（索引失效）
weixin_30080745的博客
01-17 1418
Oracle建立索引的目的是为了避免全表扫描，提高查询的效率。但是有些情况下，即使建立了索引，但是执行写出来的查询还是很慢，然后通过执行计划会发现是索引失效导致的(不走索引，走全表扫描)。所以需要了解一下有哪些些情况会导致索引失效，即查询不走索引的原因。在写SQL的层面上一些骚操作会导致索引失效没有写WHERE子句或查询条件没有建立索引既然没有WHERE子句，那么就是查询全部数据了，相当于全表扫描...
sql优化中出现的索引合并问题(多条件查询时使用单索引还是联合索引)
12-14
在SQL优化过程中，索引合并问题是一个常见的性能瓶颈，特别是在多条件查询时。索引设计是数据库性能调优的关键因素，正确地选择单索引和联合索引对于提高查询速度和减少I/O操作至关重要。本篇文章将深入探讨索引合并...
有些 where 条件会导致索引无效
weixin_34250709的博客
05-12 1591
在查询中，WHERE 条件也是一个比较重要的因素，尽量少并且是合理的 where条件是徆重要的，尽量在多个条件的时候，把会提取尽量少数据量的条件放在前面，减少后一个 where 条件的查询时间。有些 where 条件会导致索引无效： 1.where 子句的查询条件里有！=，MySQL 将无法使用索引。  2.where 子句使用了 Mysql 函数的时候，索引将无效，比如：select * ...
题目：某些SELECT语句中的WHERE子句不使用索引，以下说法正确的是（）
qq_42449963的博客
05-16 1209
题目：某些SELECT语句中的WHERE子句不使用索引，以下说法正确的是（） A、where语句中使用到"!="，将不使用索引 B、where语句中使用"||"，将不使用索引 C、where语句中使用"+"，将不使用索引 D、以上说法都正确 结果：D 解释： A："!="不会使用索引，因为索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中，例子如下： 不会使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT != 0; 使用索引:
mysql 联合索引的命中规则_mysql隐蔽的索引规则导致数据全表扫描
weixin_39854951的博客
12-05 243
索引是为了加速数据的检索，但是不合理的表结构或适应不当则会起到反作用。我们在项目中就遇到过类似的问题，两个十万级别的数据表，在做连接查询的时候，查询时间达到了7000多秒还没有查出结果。　　首先说明，关联的字段都已经建立了相对应的索引，在执行计划的时候发现另外一张表没有走索引，结果如下图：具体描述为：Range checked for each record (index map: 0x1); N...
索引对查询条件的影响
青春奋斗
02-20 1112
故障表现:表TB中的列COL列是字符型,里面保存有数字和其他数据,在该字段上面建立了索引,当使用下面的语句查询的时候出现了错误:"将 VARCHAR值AA转换为数据类型为int 的列时发生语法错误".select * from tb where isnumeric(Col)=1AND Col=100             ---isnumeric 返回为1的时候,是数值型,否则返
oracle 索引失效的原因
weixin_34174132的博客
01-01 557
1) 没有查询条件，或者查询条件没有建立索引2) 在查询条件上没有使用引导列3) 查询的数量是大表的大部分，应该是30％以上。4) 索引本身失效5) 查询条件使用函数在索引列上（见12）6) 对小表查询7) 提示不使用索引8) 统计数据不真实9) CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。10)隐式转换导致索引失效.这一点应当...
oracle不走索引可能原因.docx
04-29
Oracle数据库在执行SQL查询时，有时会选择不使用已经创建的索引，这可能是由于多种因素导致的。以下是一些常见的原因及其详细解释： 1. **INDEX SKIP SCAN**: 当创建了一个组合索引，但是查询只使用了索引的非第一...
SQL 不走索引的常见情况及应对策略
最新发布
07-29
常见情况九：多列索引不匹配查询条件 如果查询条件涉及多列索引的前缀列，但未完全匹配，索引可能无法被使用。 应对策略：调整查询条件以完全匹配多列索引的列顺序，或考虑调整索引设计。 常见情况十：数据分布不...
Mysql中SQL语句不使用索引的情况
09-09
在MySQL中，SQL语句不使用索引的情况可能会影响数据库查询的性能，...同时，定期审查查询执行计划和监控系统状态，以便发现并解决索引不使用的问题。通过这些方法，你可以确保数据库的高效运行，减少不必要的性能瓶颈。
关于COALESCE()
terryhuang的专栏
05-21 2612
T-SQL的COALESCE()函数能够让你指定多个表达式，而且COALESCE()会返回组里第一个非空的值。要使用下面的形式，其中expression可以是任何数据类型，并能够等于大多数合法的表达式： COALESCE (expression [,…n]) 这个函数没有限制自变量的数量，但是它们必须都是同一个数据类型；这个函数会返回同expression相同的数据类型。
mysql where失去索引情况_Mysql常见索引失效情况
weixin_42298876的博客
01-26 1141
1.被索引字段发生隐式转换Mysql执行器在执行sql查询的时候，会自动将与原字段类型不匹配的值进行类型转换我们创建如下表image-20200427210417688.png分别进行如下sql查询explain select* from t_user where f_phone=139image-20200427210513909.png可以看到，key是null，也就是说索引是没有生效的，再换...
你知道索引失效嘛？举例看看？
weixin_47396087的博客
07-29 406
索引失效，可以从一下几种原因中分析： 1.在where条件的等号左侧中使用函数 where date(create_at) = ‘2019-01-01’ 带有列的函数计算值，导致索引无法使用。 2.表关联字符集格式以及排序方式不一致 关注CHARSET和COLLATION SQL写法错误导致的索引失效比较常见的例子是，我们存储手机号的字段格式为varchar，但是SQL却写的where phone=123; 3.统计信息不准确 在查询中使用 !=、not in、not exists等 4.MySQL支持前缀
mysql哪些情况下不走索引？
heshihu2019的博客
12-30 1071
MySQL是一种常用的关系型数据库，它使用索引来提高查询性能。然而，并非所有的SQL语句都能充分利用索引。在本文中，我们将介绍几个无法使用到索引的MySQL SQL语句。
Mysql查询条件为大于时，不走索引失效问题排查
程序新视界
02-24 9837
我们都知道在数据库查询时，索引可以极大的提高查询效率。通常在使用的时候，都会针对频繁查询的关键字段建立索引。 比如，当以交易日期（trans_date）来查询交易记录时，通常会对该字段添加索引，以便在大量数据的情况下提升查询效率。 针对trans_date字段，创建union_idx_query索引，那么在下面以trans_date为查询条件的语句中，毫无疑问是会走索引的： select count(1) from A; // 40000 EXPLAIN select * from A where tra
where查询条件的字段顺序打乱会影响命中索引吗？
qq_43842093的博客
12-20 931
答案是：不影响我们的where后边条件字段打乱会影响命中索引吗？先来进行下边的实验：可以看到实验结果，where条件字段顺序没有按照索引的字段顺序，依然不影响命中索引。因为Mysql中有查询优化器，会自动优化查询顺序。
Laravel自动拼接where条件
程序猿的世界
11-21 2888
$where = []; $where['user_id'] = $user->id; $where[] = ['created_at', '>=', $date['start']]; $where[] = ['created_at', '<', $date['end']]; $cateDatas = Question::select('category_id', DB::r...
SQL不走索引的几种常见情况
热门推荐
Muscleheng的博客
07-01 1万+
我们写的SQL语句很多时候where条件用到了添加索引的列，但是却没有走索引，在网上找了资料，发现不是很准确，所以自己验证了一下，记一下笔记。 这里实验数据库为 MySQL（oracle也类似）。 查看表的索引的语句： show keys from 表名 查看SQL执行计划的语句（SQL语句前面添加 explain 关键字）：explain select* from users u wher...
oracle 不走索引的原因
wacthamu的专栏
02-01 1万+
create table tb2 as select * from emp; alter table tb2 modify empno number(4) not null; 翻到20W行 create index idxtb21 on tb2(empno); select INDEX_NAME from dba_indexes where table_name='TB2';--验
mysql关联查询什么时候不走索引
05-10
<think>嗯，用户问的是MySQL关联查询中不走索引的场景及原因，还有优化方法。首先，我得回忆一下索引的基本知识，以及关联查询时索引如何起作用。根据引用里的内容，比如引用3提到组合索引不使用第一列会失效，还有数据类型隐式转换会导致索引失效。还有引用2提到优化JOIN语句要确保连接字段有索引，用EXPLAIN检查。 首先，用户可能想知道在哪些情况下JOIN操作不会使用索引，比如连接字段没索引，或者类型不匹配，或者使用了函数导致索引失效。比如，如果连接的两个字段数据类型不同，比如一个INT一个VARCHAR，这时候可能不会走索引。还有，如果在连接字段上用了函数，比如CONVERT或者SUBSTRING，也会导致索引失效。 然后，需要结合引用里的例子，比如test_user和test_order表，如果user_id在test_order里没有索引，那么JOIN的时候可能全表扫描。另外，当使用OR条件或者范围查询时，可能部分情况索引失效，比如引用3里的最佳左前缀法则，组合索引的问题。 优化方法的话，应该包括添加合适的索引，检查数据类型是否一致，避免在连接字段上使用函数或表达式，使用EXPLAIN分析执行计划。引用2提到可以创建索引、调整JOIN的顺序，或者使用临时表来处理子查询。 还要注意，当使用外连接时，比如LEFT JOIN，如果右表的连接字段没有索引，也会导致性能问题。可能用户需要了解这些细节，以及如何通过优化表结构或者查询语句来改善性能。 总结下来，需要列举常见的几种不走索引的场景，比如无索引、类型隐式转换、使用函数、OR条件、外键约束问题，然后针对每个场景给出优化方法。同时，结合引用中的例子和优化建议，确保回答准确且有依据。</think>### MySQL关联查询不走索引的场景及优化方法 #### 一、不走索引的典型场景及原因 1. **连接字段无索引** - **场景**：当`JOIN`操作的连接字段（如`user_id`）在**被驱动表**（如`test_order`）中未建立索引时，查询会进行全表扫描。 - **示例**： ```sql SELECT * FROM test_user u JOIN test_order o ON u.id = o.user_id; -- 若test_order.user_id无索引，则全表扫描 ``` [^4] 2. **隐式类型转换** - **场景**：连接字段的数据类型不一致（如`CHAR`与`INT`），导致索引失效。 - **示例**： ```sql SELECT * FROM test_user u JOIN test_order o ON u.id = CAST(o.user_id AS CHAR); -- 强制类型转换使索引失效 ``` [^3] 3. **函数或表达式操作** - **场景**：对连接字段使用函数（如`CONCAT()`、`SUBSTRING()`）或表达式时，索引无法生效。 - **示例**： ```sql SELECT * FROM test_user u JOIN test_order o ON SUBSTRING(u.user_name,1,3) = o.order_name; -- 函数操作破坏索引 ``` 4. **OR条件导致部分失效** - **场景**：`WHERE`子句中的`OR`条件若涉及非索引字段，可能导致索引部分失效。 - **示例**： ```sql SELECT * FROM test_user u JOIN test_order o ON u.id = o.user_id WHERE u.age > 18 OR o.pay = 0; -- 若o.pay无索引，部分条件无法使用索引 ``` 5. **外键约束缺失** - **场景**：外键关联的表未正确设置索引时，可能影响`JOIN`效率（即使MySQL不强制要求外键索引，但实际查询仍需依赖索引）。 --- #### 二、优化方法 1. **添加必要索引** - **操作**：为被驱动表的连接字段创建索引。例如： ```sql ALTER TABLE test_order ADD INDEX idx_user_id(user_id); -- 优化JOIN性能 ``` [^2] 2. **确保数据类型一致** - **操作**：检查连接字段类型是否一致，例如将`VARCHAR`与`CHAR`统一为相同类型。 3. **避免字段操作** - **原则**：不在连接字段上使用函数或表达式，可通过调整查询逻辑规避。 - **示例**： ```sql -- 原始低效写法 SELECT * FROM test_user u JOIN test_order o ON DATE(u.create_time) = o.order_date; -- 优化后（假设order_date为DATE类型） SELECT * FROM test_user u JOIN test_order o ON u.create_time BETWEEN o.order_date AND o.order_date + INTERVAL 1 DAY; ``` 4. **使用覆盖索引** - **操作**：若查询仅需索引字段，可创建覆盖索引减少回表操作。例如： ```sql ALTER TABLE test_order ADD INDEX idx_covering(user_id, order_name); ``` 5. **调整JOIN顺序** - **策略**：通过`STRAIGHT_JOIN`强制指定驱动表（小表驱动大表），或依赖优化器自动选择。 - **示例**： ```sql SELECT * FROM small_table s STRAIGHT_JOIN large_table l ON s.id = l.small_id; -- 明确指定驱动表 ``` [^2] 6. **减少JOIN层级** - **方法**：拆分复杂查询为多个简单步骤，使用临时表存储中间结果。 - **示例**： ```sql CREATE TEMPORARY TABLE tmp_users SELECT id FROM test_user WHERE age > 18; -- 提前过滤并存储中间结果 SELECT * FROM tmp_users u JOIN test_order o ON u.id = o.user_id; ``` --- #### 三、验证工具：EXPLAIN 使用`EXPLAIN`分析查询计划，重点关注以下字段： - **type**：若为`ALL`，说明全表扫描；若为`ref`或`eq_ref`，说明使用索引。 - **key**：显示实际使用的索引名称。 - **rows**：预估扫描行数，数值越大性能风险越高。 ```sql EXPLAIN SELECT * FROM test_user u JOIN test_order o ON u.id = o.user_id; ``` ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值