索引使用及索引失效总结

1.在索引列上使用函数。比如substr,decode,instr等,对索引列进行计算。需要建立函数索引就可以了。
  如:select * from staff where trunc(birthdate)='01-MAY-82'。---失效
  select * from staff where birthday < (to_date('01-MAY-82') + 0.9999)。
  注意,对于MAX,MIN函数,Oracle仍然使用索引。
2.新建的表还没来得及生成统计信息,分析一下就好了。
3.基于cost的成本分析,访问的表过小,使用全表扫描的消耗小于使用索引。
4.使用<>、not in、not exist,对于这三种情况。大多数情况下认为结果集很大,一般大于5%-15%就不走索引而直接走FTS。
5.单独的>、<。
6.like "%_"百分号在前。
7.单独引用复合索引里非第一位置的索引。
8.字符型字段为数字时在where条件里不加引号。
  如:dept_id是一个varchar2型的字段,在这个字段上有索引。
  select * from dept where dept_id = 1234---索引失效。这是因为oracle会自动把where子句装换成to_number(dept_id)=1234,
  相当于使用函数。这样就限制了索引的使用。正确的使用方式是:select * from dept where dept_id = '1234'。
9.当变量采用的是times变量,而表的字段采用的是date变量时,或相反情况。
10.索引失效,可以考虑重建索引。
11.B-tree索引is null不会走,is not null会走;位图索引is null,is not null都会走;
   联合索引is not null只要在建立的索引列(不分先后)都会走。
12.没有where子句。
13.where子句中使用不等于操作
   不等于操作包括:<>,!=。对于这个限制条件可以通过OR代替,例如;column<>0 ===> column > 0 OR column < 0
14.等于和返回索引同时出现在where子句中,并不会合并使用。
   select emp_id,emp_m,salary_q from emp where job = 'manager' and deptno > 10.
   job和deptno都是非唯一索引,这种条件下oracle不会合并索引,它只会使用第一个索引。
15.如果条件中带有or,即使其中条件带有索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or,又想让索引生效,需要
   在or条件中的每一列上都加上索引。
<think>好的,我需要回答用户关于索引失效的原因及解决方案,特别是在使用比较操作时的注意事项。首先,我得回顾用户提供的引用内容,看看有哪些已经提到的原因和解决方案。引用里提到了IN语句、OR条件、LIKE通配符开头、列上使用函数运算等情况会导致索引失效。另外,解决方案包括使用UNION代替OR、避免列上操作等。 接下来,我需要整理这些信息,按照用户的要求分点说明。用户希望结构清晰,逐步解决问题,所以需要将原因和对应的解决方案对应起来。同时,要确保使用正确的中文表达,数学公式用$...$,独立公式用$$...$$,但根据问题内容,可能数学公式较少,但如果有涉及计算的例子,需要注意格式。 然后,生成相关问题部分,需要提出与索引失效相关的扩展问题,帮助用户进一步学习。问题要具体,比如不同数据库的优化差异、联合索引使用等。 最后,检查引用标注是否正确,每个引用来源是否对应正确的内容。例如,用户提供的引用[1]到[4]分别涉及不同的失效原因和注意事项,需要正确关联到回答中的各个点。 在写作过程中,要确保语言通顺,逻辑清晰,避免技术术语使用不当,同时覆盖所有提到的失效原因,并给出对应的解决方案。可能还需要补充一些常见的其他原因,比如类型转换、索引选择性低等,但根据用户提供的引用内容,可能需要限制在已有引用范围内,或者适当扩展但明确标注来源。 现在开始组织内容: 首先,索引失效的常见原因,每个原因对应引用中的例子。例如,IN语句、OR条件、运算、LIKE通配符开头,这些都是引用中提到的。解决方案部分,每个原因对应一个或多个解决方法,如使用UNION代替OR,避免运算等。 然后,比较操作的注意事项,比如避免隐式类型转换,使用正确的操作符,这可能需要结合引用外的知识,但用户的问题允许引用站内资料,但当前提供的引用中没有提到类型转换,所以可能需要谨慎处理,或者询问是否需要加入,但根据要求,尽量保证回答真实可靠,所以可能只基于现有引用内容。 不过,用户的问题是关于比较操作的注意事项,现有的引用中提到了在索引列上使用函数运算,例如引用[2]提到“不要在列上使用函数和进行运算”,这属于比较操作中的一部分。此外,引用[3]提到避免LIKE以通配符开头,这也是一种比较操作。因此,可以归纳为比较操作中的注意事项包括避免在列上使用函数运算,以及避免某些操作符如前置通配符的LIKE。 总结来说,回答的结构应该是: 1. 索引失效的常见原因 - IN语句导致失效 [^1] - OR连接条件导致失效 [^4] - 使用函数运算 [^2] - LIKE以通配符开头 [^3] 2. 解决方案 - 替代IN为多个等值条件 - 用UNION代替OR - 避免列上操作,转为对常量操作 - 使用覆盖索引或全文索引应对LIKE 3. 比较操作的注意事项 - 避免参与运算 - 避免某些操作符如前置% - 注意隐式类型转换(如果引用中有提到的话,但当前没有,可能需省略或谨慎提及) 最后生成相关问题,如不同数据库的差异、联合索引使用、隐式类型转换影响等。 现在检查引用标注是否正确,每个原因对应正确的引用编号。例如,IN语句对应引用[1],OR条件对应引用[4],运算对应引用[2],LIKE对应引用[3]。 然后,确保回答中没有使用Markdown格式,数学公式正确应用,但本问题可能较少涉及数学公式,如果有例子如索引选择性计算,可能需要公式,但如果没有必要,可以省略。例如,索引选择性公式可写为:$$ \text{选择性} = \frac{\text{唯一值数量}}{\text{总记录数}} $$,但这可能超出用户提供的引用内容,所以可能不需要。 最终,确保回答符合用户的要求,结构清晰,解决方案具体,相关问题有针对性。</think>### 索引失效原因及解决方案 #### 一、索引失效的常见原因 1. **使用`IN`语句导致索引失效** 当`IN`包含的值过多时,优化器可能放弃使用索引,转而全表扫描。例如: ```sql SELECT * FROM table WHERE id IN (1, 2, 3, ..., 1000); ``` 若`IN`表过长,索引可能失效。 2. **使用`OR`连接条件** 若多个条件通过`OR`连接且字段未全部建立联合索引,会导致索引失效: ```sql SELECT * FROM news WHERE id = 1 OR author = 'John'; ``` 若`id`有索引而`author`无索引,则索引失效。 3. **对索引使用函数运算** 在索引列上使用函数运算(如`WHERE YEAR(create_time) = 2023`)会导致索引失效,因为索引存储的是原始值而非计算后的值。 4. **`LIKE`以通配符开头** 使用`LIKE '%abc'`或`LIKE '%abc%'`会导致索引失效,因为无法利用索引的有序性。 5. **隐式类型转换** 若索引与比较值类型不一致(如字符串与数字比较),会触发隐式转换,导致索引失效: ```sql SELECT * FROM users WHERE phone = 13800138000; -- phone是字符串类型 ``` #### 二、解决方案与比较操作的注意事项 1. **优化`IN`语句** - 限制`IN`表的长度(如分页查询)。 - 若数据量大,改用`JOIN`或临时表关联。 2. **用`UNION`替代`OR`** 对多个单索引的条件,使用`UNION`合并查询: ```sql SELECT * FROM news WHERE id = 1 UNION SELECT * FROM news WHERE author = 'John'; ``` 确保每个子查询能命中索引。 3. **避免对索引进行运算** 将计算转移到常量端: ```sql -- 错误写法:WHERE YEAR(create_time) = 2023 -- 正确写法:WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31' ``` 4. **合理使用`LIKE`** - 尽量使用前缀匹配(`LIKE 'abc%'`)。 - 若必须使用全模糊匹配,可考虑全文索引(如MySQL的`FULLTEXT`)。 5. **注意类型一致性** 确保比较操作中数据类型一致,例如将数字转换为字符串: ```sql SELECT * FROM users WHERE phone = '13800138000'; ``` 6. **联合索引的最左前缀原则** 若使用联合索引`(a, b, c)`,查询条件需包含最左(如`a`或`a, b`),否则索引失效。 #### 三、总结 索引失效通常由不当的查询写法(如`IN`、`OR`、运算)或隐式类型转换导致。优化时需遵循索引匹配规则,优先使用覆盖索引,并避免破坏索引的有序性。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值