常用优化知识点，后续持续更新

1、count(*) 优化

问：count(*)什么情况会用到索引，有索引

答：不一会走索引。1.索引字段，有不为null的约束，会走

2.加where条件 where id is not null;

3.该表有主键，因为主键不为空。

问：什么时候在表的非空列建有索引时，COUNT（*）语句用索引效率不如全表扫描

答：这张表只有一个字段，索引加rowid会比全表扫描还浪费资源

走全表扫描，cost 42

hint强制走索引，cost 213,明显比全表扫描大很多。

2、avg(),sum() 优化

问：avg(),sum() 优化什么情况会用到索引，有索引

答：不一会走索引。1.索引字段，有不为null的约束，会走

2.加where条件 where id is not null;

跟count(*)优化思路一致。所以建议设计表结构一定要注意null值。

3、索引回表优化

TABLE ACCESS BY INDEX ROWID：回表

根据业务优化返回字段，如果返回值不多，可以考虑创建组全索引

4、主外键三大特点

1.主键本身也是索引

2.可以保证表中主键所在列的唯一性

3.可以有效地限制外键依赖表记录的完整性

外键级联删除：

添加外键基础上增加on delete cascade;

外键表外键字段不创建索引会导致主表delete删除主键时卡住。

问：一张大表有一个字段创建为普通索引(没有重复值)，如何快速把这个字段变为主键

答：1.删除索引，创建主键，显然对于大表效率很低。

2.直接在该字段上创建

alter table t add constraint t_id_pk primary key(id);

原理：主键就是生成一个与外键相关的约束，产生一个能保证唯一性的索引。

过程如下：

SQL> create table a as select * from dba_objects;

Table created.

SQL> create index idx_a on a(object_id);

SQL> alter table a add constraint a_id_pk primary key(object_id);

Table altered.

如果该字段索引不唯一，会怎么样

SQL> create table b as select * from dba_objects;

Table created.

SQL> insert into b select * from dba_objects;

89346 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_b on b(object_id);

Index created.

SQL> alter table b add constraint b_id_pk primary key(object_id);

alter table b add constraint b_id_pk primary key(object_id)

*

ERROR at line 1:

ORA-02437: cannot validate (T1.B_ID_PK) - primary key violated

5、组合索引特点

1.合适的场景可以避免回表

2.组合列返回数据量越少越高效。比如a=1,ab=2 跟a=1 and b=2返回条数差不多，就不推荐创建组合索引，反之可。

3.组合索引不建议创建的列太多，建议最多三个字段。

4.组合两列谁在谁在前更合适，

在等值查询的情况下，组合索引的列无论哪一列在前，性能都一样

组合索引的两列，当一列是范围查询，一列是等值查询的情况下，等值查询列在前，范围查询列在后，这样的索引才最高效！

5.如果单列的查询列和联合索引的前置列一样，那单列可以不建索引，直接利用联合索引来进行检索数据

6、索引过多对insert，update,delete的影响

对 insert 语句的负面影响最大，百害而无一利，只要有索引，插入就慢，索引越多越慢！

对 delete 语句来说，有好有坏。在海量数据库中定位删除少数记录时，这个条件列是索引列显然是必要的，但是过多列有索引还是会有明显影响，因为其他列的索引也要因此被更新。在经常要删除大量记录的时候，危害加剧！

对update语句的负面影响最小，快速定位少量记录并更新的场景和delete类似，但是具体修改某列时却有差别，不会触及其他索引列的维护。

7、如何知道索引有没有被使用

对需要跟踪索引进行监控

alter index 索引名 monitoring usage;

取消监控

alter index 索引名 nomonitoring usage;

通过观察v$OBJECT_USAGE进行跟踪

select * from v$object_usage;

注：12c版本以上直接查v$index_usage_info

8、位图索引适用场景

1.位图索引列大量重复

2.该表极少更新，表有大量update操作，不要使用

3.位图很容易锁表，警慎使用

对技术感兴趣的同学，可以加入钉钉一起学习探讨

https://qr.dingtalk.com/action/joingroup?code=v1,k1,9UBMCUkULzTxs1hqtUB2ajiR1deBIRgHXDvzOaVVwQI=&_dt_no_comment=1&origin=11? 刘开杰邀请你加入钉钉群聊一起探讨数据库知识，点击进入查看详情