什么情况下该建立索引-SQL优化核心思想一

62 阅读 0 评论 41 点赞

我是靠谱客的博主阳光黄蜂，最近开发中收集的这篇文章主要介绍什么情况下该建立索引-SQL优化核心思想一，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

索引前序知识

基数：某个列唯一键的数量叫做基数。例如：主键的基数等于主键数（可以理解为行数），再比如性别列，因为只有男女，所以基数为2
选择性：即 基数/总行数*100% 就是某个列的

创建索引的条件

重点：只有大表才会产生性能问题，一般几百行的数据，如果多次进行DML，导致出现查询慢，这纯属与程序设计问题

根据选择性判断
1.基数/总行数（选择性）<5% 创建索引
2.基数/总行数（选择性）>5% 不创建索引
必需创建索引
当一个列出现在where条件中，该列没有创建索引且选择性大于20%，那么该列就必须创建索引来提高查询性能，如果只有几百条数据就不用

代码一键判断

1.抓取出现在where后面的列，第一种方法是：V$SQL_PLAN. 另外一种则是通过下面的脚本
第一步：刷新数据库监控信息

begin
dbms_stats.flush_database_monitoring_info;
end;

第二步:查看出现在were后面的字段

select r.name owner,
o.name table_name,
c.name column_name,
equality_preds, ---等值过滤
equijoin_preds, ---等值JOIN 比如where a.id=b.id
nonequijoin_preds, ----不等JOIN
range_preds, ----范围过滤次数 > >= < <= between and
like_preds, ----LIKE过滤
null_preds, ----NULL 过滤
timestamp
from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
where o.obj# = u.obj#
and c.obj# = u.obj#
and c.col# = u.intcol#
and r.name = 'SCOTT'
and o.name = 'TEST';

第三步：查询选择性大于20%的列
第四步：确定这些列没有创建索引

 select table_owner, table_name, column_name, index_name
from dba_ind_columns
where table_owner = 'SCOTT'
and table_name = 'TEST';
未选定行

组合起来的脚本如下：

select owner,
column_name,
num_rows,
Cardinality,
selectivity,
'Need index' as notice
from (select b.owner,
a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'SCOTT'
and a.table_name = 'TEST')
where selectivity >= 20
and column_name not in (select column_name
from dba_ind_columns
where table_owner = 'SCOTT'
and table_name = 'TEST')
and column_name in
(select c.name
from sys.col_usage$ u, sys.objo, sys.col$ c, sys.user$ r
where o.obj# = u.obj#
and c.obj# = u.obj#
and c.col# = u.intcol#
and r.name = 'SCOTT'
and o.name = 'TEST');