最近在补数据库盲点之一:索引的优化,这里做下记录。我看的是《收获,不止Oracel》这本书,说实话,是因为豆瓣读书上评分很高,才选了这本,但是,好像不太适合我的感觉,行文风格有点啰嗦了,看来还是直接切入要害更适合我。
索引优化
索引的三大特点:
- 索引树的高度一般都比较低;
- 索引由索引列存储的值和rowid组成;
- 索引本身是有序的。
插一句,Oracle中追踪索引执行性能的方法:
- set autotrace traceonly(不显示语句返回结果,只跟踪)
- alter session set statistic_level=all;(需要SQL执行返回结果之后才跟踪索引)
高度较低
- 用好分区索引(index_partition):分区索引相对于普通索引,高度变化不大(即IO次数变化不大),但是索引数目由于分区会显著增加,如果搜索用不到分区条件,性能将明显下降;反之,需要使用分区字段的条件,能显著提升性能。
存储列值
COUNT(*)优化:索引列不能存在NULL,注意:列定义的时候申明为可以为空(没有使用not null关键字),则oracle查询不会主动冒险去使用该索引,只会保守地采取全表遍历,所以需要显式地添加条件非空。
- eg:create index idx1_object_id on table(object_id); – 创建索引
- select count(*) from table where object_id is not null; –显式指明为非空
- 当表只有一个字段(即只有一列)时,采用索引会比表还大(多了rowid),所以此时不宜采用索引。
SUM/AVG优化:同COUNT(*)类似,需要指明为非空或列属性为非空
MAX/MIN优化:
- 不需要显式申明为非空,就可以使用索引
- 单个最值的查询,会直接使用INDEX FULL SCAN(MAX/MIN)(即直接读取索引树最底层的最右边块里的最后一行/最左边的块里的第一行,逻辑读仅为2),速度比INDEX FAST FULL SCAN快,而且无论表中记录如何增大,查询性能也不会太慢
- 两个最值同时查询(即max、min在一条sql语句中同时查询),就不会采用INDEX FULL SCAN(MAX/MIN),转而采用全表扫描TABLE ACCESS FULL;此时需要显示申明为非空,则可以初步提升为使用索引查询的INDEX FAST FULL SCAN,但仍然不会使用INDEX FULL SCAN(MAX/MIN),需要进一步改写提升:
- 误区:两个最值分开查询,可以达到优化,但是此时查询到的最值并不对应(max、min先后执行,数据库的记录已经变化)
- 优化版【两次INDEX FULL SCAN(MAX/MIN),逻辑读为4】: select max,min from (select max(object_id) max from table) a,(select min(object_id) min from table) b);
- 查询速度(从快到慢):INDEX FULL SCAN(MAX/MIN) > INDEX FAST FULL SCAN > TABLE ACCESS SCAN
索引回表(TABLE ACCESS BY INDEX ROWID):指索引列没有查询所需的全部列,则需要根据index中的rowid定位到表中对应数据块来获取数据。
- 创建组合索引来消除索引回表(组合索引不宜列过多,一般,最好维持组合列<=3)
- 聚合因子决定了索引回表的速度:索引列与原表中对应列的顺序越一致(相似度越高),则聚合因子越低,速度越快
- 聚合因子:通俗说,就是描述索引列(有序)与原表中对应列(无序)的排列顺序的相似程度
- 所以,选取表中读取频率远高于其他列的列创建索引,可以保证表的排列顺序与索引顺序相似度较高,即聚合因子较小,索引回表速度更快。
索引有序
- ORDER BY(按照某列排序):sql执行会有排序过程,如果对某列创建索引,且尽量避免产生回表操作,查询操作时,能消除排序,提升性能。
插一句,sql语句的执行效率的评判一般重点看cpu的使用率和执行时间两个指标:Cost(%CPU)、Time,逻辑读(consistent gets)指标在90%的情况下,也可以作为参考,逻辑读越少性能越快,但不适用于排序算法。
DISTINCT(消除重复):sql执行会有排序操作(HASH UNIQUE),创建索引,只能提升一点点性能,收效不大。
索引全扫(INDEX FULL SCAN)和索引快速全扫(INDEX FAST FULL SCAN):
- 索引快速全扫比索引全扫速度快
- 索引全扫一次只读一个块,能保证有序;而索引快速全扫一次读取多个块,即减少逻辑读,不能保证有序;
- 当sql执行需要排序时,由Oracle来判断选取那种扫描方式性能更优
UNION(合并):
- UNION合并后没有重复记录,故有排序过程(SORT UNIQUE);UNION ALL只是简单的合并,并没有筛选出重复记录
- UNION是针对两个不同的结果集的筛选,分别创建各自的索引,并不能消除排序。
- 日常常见优化:将UNION改成UNION ALL,因为大部分情况下,两个表根本不可能有重复。
主外键设计
- 外键(外键所在表的外键列取值必须在主表中的主键列有记录)上建立索引,能提升查询性能,避免锁的竞争
- 外键的删除,采用级联删除,即在外键约束上添加 on delete cascade,能在删除主键记录时自动删除外键记录,但为了数据的安全,不推荐使用
组合索引
- 等值查询时,组合索引无论哪一列在前,性能都一样
- 组合索引的两列,一列是等值查询,一列是范围查询,则等值查询在前,性能更好
- 有时候范围查询(>=、<=)改用in(a,b),效率会更高,因为后者是两个等值查询:==a、==b
- 如果单列的查询列与组合索引的前置匹配,可以直接使用组合索引优化查询性能
索引的危害
- 索引越多,插入数据越慢(同时需要维护索引的有序)
- 先把索引删除,无索引插入完毕,再建索引,往往比维护索引来插入性能更优
- 索引过多对insert负面影响最大;对delete有好有坏(分情况);对update的影响最小
建立索引,需要排序,很耗CPU
Oracle可以监控跟踪的索引,返回其一段时间内是否被使用过,有助于删除长期不用的索引
- alter index 索引名 monitoring usage;–对需要跟踪的索引进行监控(取消监:nomonitoring)
- select * from v objectusage;−−观察v object_usage