SQL优化之统计信息和索引

大部分情况下的sql优化的场景，都是发生在我们想让我们的sql语句运行的更快一点，这个时候我们往往会采取一些“措施”调整sql的执行路径以便它们能更快的运行结束。

创新互联长期为近千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为虎丘企业提供专业的网站制作、网站设计，虎丘网站改版等技术服务。拥有十年丰富建站经验和众多成功案例,为您定制开发。

刚入dba行业，常常听到导师和我说的一句话“20%的sql优化技能就能优化80%的sql性能问题”。大部分情况下sql优化并不复杂，而很关键的一步是我们能不能发现问题，然后才能对症下药。今天我们先从sql优化中的统计信息、索引这2个方面来学习了解一下sql。

对于cbo模式，统计信息极为重要，这是影响sql语句执行的一个重要的因素。对于数据变化量大的数据库，会很容易出现统计信息过旧的问题即系统记录的表信息和实际表的信息不一致。那么优化器在选择最优执行路径的时候就可能选择不合适的执行路径（非最优路径）。所以我们一线运维常常需要定期的收集统计信息。那么问题来了，统计信息收集依据呢？

既然要收集，那么很多时候我们可以通过dba_tables视图中的LAST_ANALYZED字段看看上一次的收集日期，做个是否有必要做统计收集的预判断。

sys@RAC>alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
Session altered.    
sys@RAC>select owner,TABLE_NAME,LAST_ANALYZED from dba_tables where table_name='T1';
OWNER                TABLE_NAME           LAST_ANALYZED
-------------------- -------------------- -------------------
TEST                 T1                   2017-10-28 15:08:25

既然有近一年没有收集过统计信息，那么这类表统计信息不准的嫌疑就会很大。

还有一点就是当我们在看sql执行计划最后有输出dynamic sampling used for thisstatement ，那么说明本次sql执行发生了 动态采样 。动态采样是指在段（表，索引，分区）没有分析的情况下，为了使CBO 优化器得到足够的信息以保证做出正确的执行计划而发明的一种技术，可以把它看做系统自动收集统计信息的一种手段。当对象没有统计信息时（即没有做分析），动态采样技术可以通过直接从需要分析的对象抽样数据块来抽象出CBO需要的统计信息。

动态采样需要额外的消耗数据库资源，所以如果SQL执行频率很高，这部分资源的消耗可能对sql执行效率的影响也会很大，这一点需要我们注意，在这样一个环境中，是不宜使用动态采样的。所以为了防止动态采样带来的系统性能的额外消耗，所以我们建议定期对数据库中的缺失或者过旧的统计信息表做定期的统计信息收集。

在上述两种情况中，我们一般需要收集统计信息。那么，如何收集又是另外一个问题。

示例：

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'xxxxx',
                            TABNAME => 'SR_PROBLEM_xxxx',
                            PARTNAME=>'P201802',  
                            ESTIMATE_PERCENT => 1,
                            METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
                            NO_INVALIDATE=> FALSE,
                            FORCE=>TRUE,
                            CASCADE => TRUE,
                            DEGREE  => 10);
END;
/

参数说明:

·  ownname:要分析表的拥有者

·  tabname:要分析的表名

·  partname:分区的名字，只对分区表或分区索引有用

·  estimate_percent:采样行的百分比，取值范围[0.000001,100]

·  method_opt:决定histograms信息是怎样被统计的，method_opt的取值如下(默认值为FOR ALL COLUMNS SIZE AUTO)

·  for columns SIZE |REPEAT | AUTO | SKEWONLY:统计指定列的histograms，N的取值范围[1,254]

·  force:即使表锁住了也收集统计信息

·  cascade:是收集索引的信息，默认为FALSE

·  degree:决定并行度，默认值为null

那么只有上述两种情况需要收集统计信息么？答案显然是否定的。而且统计信息收集的参数丰富，这里也只是示例，够日常场景下使用而已。很多情况下还需要根据实际情况动态调整的。

下面我们看看索引，在大多数场景下合理的使用索引会使sql语句的执行会有指数级的性能提升，但是索引虽然好，但是千万不能过度使用，因为维护索引需要消耗一定的资源的。

如果一个数据表中存有海量的数据记录，当对表执行指定条件的查询时，常规的查询方法会将所有的记录都读取出来，然后再把读取的每一条记录与查询条件进行对比，最后返回满足条件的记录。这样进行操作的时间开销和I/O开销都很大。对于这种情况，就可以考虑通过建立索引来减小系统开销。

如果要在表中查询指定的记录，在没有索引的情况下，必须遍历整个表，而有了索引之后，只需要在索引中找到符合查询条件的索引字段值，就可以通过保存在索引中的ROWID快速找到表中对应的记录。形象的描述就是类似字典目录的作用，能快速的查询到相关资料。

现在我们常用的索引技术按照索引数据的存储方式可以将索引分为B树索引、位图索引、反向键索引和基于函数的索引等；按照索引列的唯一性可以分为唯一索引和非唯一索引；创建唯一性索引还能保证相关列内的数据的唯一性。按照索引列的个数可以分为单列索引和复合索引。

建立和规划一个合理的能提速sql访问效率的索引需注意以下几点：

1. 索引应该建立在WHERE子句频繁引用列表上，如果在大表上频繁使用某列或某几个列作为条件执行索引操作，并且检索行数低于总行数的15%（经验值），那么可以考虑在这些列上建立组合索引。

2. 如果经常需要基于某列或者某几个列排序操作，那么应该在这些列上建立索引可以加快数据排序速度。

3. 限制表的索引个数。索引主要用于加快查询速度，但会降低DML操作的速度。索引越多，DML操作速度越慢，尤其会极大地影响INSERT和DELETE操作的速度。因此，规划索引时，必须仔细权衡查询和DML的需求。

4. 不适合建立索引的列的特点：很少进行搜索的列上；列取值比较少的列上；blob类型的列上等。

5. 在创建过程中，千万不要中断，因为会导致oracle数据字典内已经包含了该索引的信息，但是却实际没有为该索引分配段，导致重新建立索引，并且删除索引报错。

|  作者简介

李誉军，沃趣科技数据库工程师

主要参与公司产品实施、测试、维护以及优化。