数据库优化之一（索引，分区，语句改写）

索引

ORACLE 采用两种访问表中记录的方式:

• 全表扫描
  全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.

• 通过ROWID访问表
  你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息…ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.

语法：
CREATE [UNIQUE] INDEX index_name ON table_name(column_list) [TABLESPACE tablespace_name];

（1）唯一索引;设置某列为主键时会在该列添加一个唯一索引，根据该主键查找时不会再遍历整张表,唯一索引适合建立在值唯一的列
alter table teacher add constraint pk_tno primary key on (tno);

(2) btree索引（普通索引），适合建立在重复值比较少的列上
create index i_tname on teacher(tname);

(3) 位图索引，适合建立在重复值较多的列上
create bitmap index i_tjob on teacher(tjob);

(4) 反向键索引，适合建立在类似序列增长的列上，可防止某个数据块访问过热（比如批量插入）
create index i_tseq on teacher(tseq) reverse

(5)函数索引
create index i_tname2 on teacher(upper(tname))

(6) 组合索引（可以分开创建）
create bitmap index i_g_j on teacher(tgender,tjob)

创建索引的原则：

1. 频繁搜索的列
2. 经常排序、分组的列
3. 经常连接的列
4. 指定单独的表空间
5. 数据量小、增删改数据特别频繁、增删改性能要求高于查询的表不适合建立索引

表分区

把表中数据分成若*分，并存储在不同的位置（适合在千万级以上数据的表建立分区）

为什么要做表分区？

1. 改善查询性能
2. 使表更容易管理
3. 便于备份和恢复
4. 提高数据安全性

分区类型：

1. 范围分区：将数据基于范围映射到不同分区，常用于时间
   PARTITION p1 VALUES LESS THAN (TO_DATE(‘2018-09-01’,‘yyyy-MM-dd’))
2. 列表分区：某列的值只有几个，采用列表分区
   PARTITION p2 VALUES(‘深圳’)

散列分区：不指定分区条件，数据库自动将数据平均分配（根据列的hash值）
复合分区：做了一个分区后，每个分区里面再次做分区
给带分区的表创建索引，需要在创建语句后面加local关键字
create bitmap index i_sale_date on sale(sale_date) local；

参考链接：oracle的分表详解

sql语句优化

• 对查询进行优化，要尽量避免全表扫描，在where及order by涉及的列上建立索引； 尽量避免在where子句中进行null值判断；
• 尽量避免在where子句中使用 != 或 <> 操作符； 尽量避免在where子句使用or来连接条件； 慎用 in 和 not in；
• 尽量避免在where子句中使用参数，可改为强制使用索引：with(index(索引名))； 尽量避免在where子句中对字段进行函数操作；
• 尽量避免在where子句 “=” 左边进行函数运算、算数运算及其他运算（从第2条至此条都是为了避免引擎放弃使用索引）；
• 对于多张大数据量的表JOIN，要先分页再JOIN，否则逻辑读会很高；
  索引会降低增改的效率，建立索引需慎重考虑，一张表的索引最好不要超过6个；
• 尽量使用数值型字段，若只含数值信息的字段尽量不要设计为字符型，因为引擎在处理查询和连接时会逐个比较字符串中的每一个字符，而对于数字型只需要比较一次；
• 尽可能使用 varchar/nvarchar 代替 char/nchar ，因为变长字段存储空间小； 任何地方不要使用 select * from t ，用具体字段代替 ” * ”； 尽量避免向客户端返回大数据量，若数据量过大，则考虑相应需求是否合理；
• 删除重复记录，最高效的删除重复记录方法 ( 因为使用了ROWID)；

DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID) 
 FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

本文地址：https://blog.csdn.net/zhangenyuan/article/details/107163465