文章目录
目录
前言
索引在平时日常开发中经常会遇到，正确合理的索引创建可以提高我们数据库查询的效率和性能；同时，我们也会经常遇到索引失效的问题，对于明明已经创建了索引，却为何没有达到理想的效果，下面是个人对于一些未正确使用所引案例的简单总结分析。
一、统计信息未收集案例
1.问题描述
--1.首先在test表中的name4字段上创建索引
create index idx_name4 on test(name4);
--2.然后按照name4字段来进行数据查询
select * from test where name4>='reiku2171160' and name4 <='wazhi9645963';
--3.查看执行计划
1.1 未正确使用索引原因分析
上述查询在查询字段上创建了索引，使用该字段进行查询，查询执行计划发生未使用该字段的索引，初步分析考虑可能是由于统计信息未收集的原因导致索引失效，可以通过下列语句来查询数据，通过对数据结果来进行判断分析，如果几列数据为空则代表未收集；
--4.查询表的统计信息是否收集
select num_rows,avg_row_len,last_analyzed from dba_tables where table_name='TEST';
1.2 解决方案
经过一系列的分析，基本定位是由于统计信息未收集从而导致查询语句未使用索引；通过统计信息收集来解决类似的问题，具体操作如下：
--5、表的统计信息收集
begin
dbms_stats.gather_table_stats(ownname=>'TOMITY',
tabname=>'TEST',
estimate_percent=>100,
method_opt=>'for all indexed columns' ,
cascade=>true,
degree=>4);
end;
或者：
analyze table 表名 compute statistics
analyze index 索引ID compute statistics
--6.收集后，在查看数据字典表中的信息是否已经更新
select num_rows,avg_row_len,last_analyzed from dba_tables where table_name='TEST';
--7.重新查看执行计划
select * from test where name4>='reiku2171160' and name4 <='wazhi9645963';
二、索引设计不合理案例
1.问题描述
--1.在test表中的name4字段上创建索引
create index idx_name4 on test(name4);
--2.在test表中的name5字段上创建索引；
create index idx_name5 on test(name5);
--3.查看执行计划
select * from test where name4='reiku2171160' and name5='wazhi7487843'
1.1 未正确使用索引原因分析
查询条件name4,name5已经可以确认经常大量使用（首先需要确认这两个字段是否使用频繁，并且是否会经常组合出现），但是开发人员在两个字段上分别添加了索引，执行时只能被使用到其中一个索引，导致执行效率下降，并且加大索引维护的成本。
1.2 解决方案
针对该类问题，应使用组合索引，删除原有的两个索引。具体操作如下：
--4.删除name4,name5上的两个索引
drop index idx_name4;
drop index idx_name5;
--5.创建name4,name5的组合索引
create index idx_name4name5 on test(name4,name5);
--6. 查看执行计划
select * from test where name4='reiku2171160' and name5='wazhi7487843'
三、强制索引案例
1.问题描述
--1.在test表中的name4字段上创建索引
create index idx_name4 on test(name4);
--2.在test表中的name5字段上创建索引
create index idx_name5 on test(name5);
--3.查看执行计划
select * from test where name4='reiku2171160' and name5='wazhi7487843'
1.1 未正确使用索引原因分析
可以看出表中有两个索引idx_name4，idx_name5，在了解当下实际业务的情况下，我们发现通过索引idx_name5执行，速度会更快，但由于ORACLE内部规划和机制，导致我们使用了错误的索引执行，进而使执行速度变慢，执行效率变低。
1.2 解决方案
通过强制索引的方式，强制使用索引idx_name5,具体方法如下：
--3.强制走索引
select /*+index(t idx_name5) */ * from test t where name4='reiku2171160' and name5='wazhi7487843'
总结
以上就是今天分享的索引优化案例，在开发过程中可能会还会遇到更多其他的情况，对于索引的优化也不仅仅只有这些方式，想掌握的更全面同时也需要自己不断的积累经验和学会分析判断。