索引优化案例总结

文章目录

目录

文章目录

前言

一、统计信息未收集案例

1.问题描述

1.1 未正确使用索引原因分析

 1.2 解决方案

二、索引设计不合理案例

1.问题描述

1.1 未正确使用索引原因分析

1.2 解决方案

  三、强制索引案例

1.问题描述

 1.1 未正确使用索引原因分析

1.2 解决方案

总结

---

前言

索引在平时日常开发中经常会遇到，正确合理的索引创建可以提高我们数据库查询的效率和性能；同时，我们也会经常遇到索引失效的问题，对于明明已经创建了索引，却为何没有达到理想的效果，下面是个人对于一些未正确使用所引案例的简单总结分析。

---

一、统计信息未收集案例

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'

 

总结

以上就是今天分享的索引优化案例，在开发过程中可能会还会遇到更多其他的情况，对于索引的优化也不仅仅只有这些方式，想掌握的更全面同时也需要自己不断的积累经验和学会分析判断。