数据库优化知识

上周听了公司内部 DBA 罗玄的分享，还是收获了不少。有概念，同时也结合日常的案例进行了分析，解除了之前的一些疑惑。以下内容主要是罗玄 ppt 上的内容，我做了一下自己的笔录和理解，方便自己回顾。

 

数据库访问优化法则：

• 减少数据访问

• 返回更少数据

• 减少交互次数

• 减少 CPU 消耗

• 充分利用数据库资源                 

 

索引的优缺点：

优点：

1， 扫描更少的数据块，及时返回需要的数据

 

缺点：

1 、 DML(DELEL\UPDATE\INSERT) 速度变慢 – 在做 DML 操作时，会 rebuild 索引，所以会带来一定的性能损失

2 、增加额外空间存储 --- 当然了索引也要存储，需要空间的

3 、建立过多的索引，会导致 oracle 判断执行计划错误，导致 sql 执行异常 --- 过多的索引，会执行计划有时候会判断错误，从而会引起问题， oracle 中可以用 hint 进行指定走的索引。所以说索引不是越多越好，这种想法需要转变。

 

索引的分类 ：

之前有看过一些索引的知识，刚好听分享，又回顾了下，加深下印象。

• B-TREE 索引：索引组织表、降序索引、反向索引、组合索引

• 位图索引

• 函数索引

 

索引组织表： mysql 中的表默认就是索引组织表

一般情况下，表与索引数据分别存放在表段和索引段中。但索引组织表（ IOT ）比较特殊，它将表的数据和索引数据存储在一起，即以 B 树索引的方式来组织表中的数据。

 

降序索引的应用场景是：当某些查询经常以某个字段进行降序 (DESC) 进行查询时，针对该字段建立降序索引，对速度会有所提升。比如查询最近的一段时间的数据，就可以针对 gmt_create 建立降序索引

 

反向索引： reverse( 字节反向，并不是值的反向 )

只是大概了解了下，平时用的不多，在特定的场景下才会用。

 

组合索引：多个字段上建立索引。

 

位图索引：基数比较低的字段，如： status 。适合读密集，不适合写密集。

这个解释下 ：位图索引，比如针对某个字段 status ，它的值很少，比如只会存 1,2,3 这么几个值的情况。这样的索引，当 update 语句中包含该字段 ( 比如 status=1) 时，会导致 status=1 的记录被锁住，因为本身 status = 1 的记录肯定会很多的，所以会导致其他的 update 语句无法执行。

 

函数索引： to_number

 

另外玄哥也分享了 SQL 是否会使用索引的案例，有些我之前大概清楚的。总之的原则就是不要破坏之前的索引，不能对索引字段进行额外计算 ( 对于运算后的索引可能就不是正确的索引了 ) 。

 

INDEX_COLUMN in (?,?,...,?)  (inlist 不能太大，如果太大，可能会走全表扫描 )

这点我关注了下， oracle 里接受到一条 sql ，会有一个解析的过程，进行计算执行这条 sql 的代价，如果最终计算出来，走索引的代价比走全表的代价还大，那么会选择走全表扫描。

 

还是列一下不会走索引的例子吧：

• INDEX_COLUMN <> ?

• INDEX_COLUMN not in (?,?,...,?)---not in 的方式是不会走索引的

• function(INDEX_COLUMN) = ?

• INDEX_COLUMN + 1 = ?

• INDEX_COLUMN || 'a' = ?

• INDEX_COLUMN like '%'||?

• INDEX_COLUMN like '%'||?||'%'

• INDEX_COLUMN is null --- 因为对 null 的数据无法建立索引 (null 无法比较 ) ，所以也不走索引，但是对于 not null ，是会走索引的

• NUMBER_INDEX_COLUMN='12345' – 数据类型不一致，虽然写法上数据库可能会帮你做转换成数值型，但是这样就不走索引了

• CHAR_INDEX_COLUMN=12345

• a.INDEX_COLUMN=a.COLUMN_1(:X 绑定变量 ) – 绑定变量是在执行时才知道值的，所以也无法走所以走索引。 - 举个例子，比如条件里是 status = rand(), 这样的话，是不会走索引的。

 

 

索引的设计：

• 字段出现在查询条件里

• Sql 的执行频率，表数据量的大小

• 选择性比较好字段上

• 是否有必要回表 ( 索引是双刃刀 )

• 利用索引的有序性 --- 就是前面提到的降序索引之类的

在很大程度上回表会导致 IO ，如果根据查询的实际情况进行分析，将查询的列字段建立在索引上，则直接查询索引就可以得到想要的结果，无需回表速度会非常快。比如针对 ID 建立了索引，但是往往经常根据 ID 获取 name 值，所以可以针对 ID 和 name 建立索引。

 

 

回表： oracle 扫描索引后，拿到 rowid ，再根据 rowid 去表中取索引中没有的字段数据

 

• 索引的优化，是最能立竿见影，减少磁盘的 IO 消耗；但索引是两刃刀，如果优化过度，往往会影响 DML 的速度，从而导致应用响应变慢；在同一字段上建过多的索引，会导致 oracle 在使用索引时，判断错误，导致执行计划出错。

• 关键在于我们怎么来权衡？ ( 业务，以读为主 or 以写为主 )

 

减少交互次数：

• 批量提交

• Select  in list 方式

• Sql 改写，减少表访问次数

• 能 cache 就 cache

批量提交

   如果要订正 100 万数据，我们做法有，取一个 id 、更新成功、再 commit ，更新一条记录，我们进行了三次交互，那就要 300 万次交互。如果我们每次取 1000 条，一次更新 1000 条，再 commit ， 3000 次交互。当然如果批量没有处理好，表本身 DML 也比较频繁，会导致部分记录 lock( 不要做成大事务，就是这个原因 ) ，会影响到应用，所以合理的一次性更新数，要根据应用特点来决定。

 

Select  in list 方式

     In list 也是两刃刀，如果传的 value 过多，可能索引扫描未必优化全表扫描。

     a 、 for  a in 1..20 loop

       select  字段 from t where id = :1;

      end loop;

      b 、 in list 方式，值多了，绑定变量就很难

         select  /*+ ordered use_nl(a t)*/   字段
  from table(STR2NUMLIST(:1))  a, t
  where a.COLUMN_VALUE = t.id

就查询来讲： a 就交互 20 次， b 就交互 1 次

 

注： /*+ ordered use_nl(a t)*/ 这种在 oracle 里有特殊的用途，对于 sql 的执行有特定的意义。这里是指定了用 nestloop 的方式进行表关联。

 

 

Sql 改写，减少表访问次数

     select adzoneid  from rpt_adzoneskchart  where cntday = trunc(sysdate) - 1
intersect

     select adzoneid from rpt_adzoneskchart where cntday = trunc(sysdate) – 2( 访问两次表 )

select adzoneid from rpt_adzoneskchart
where cntday >= trunc(sysdate) - 2
and cntday <= trunc(sysdate) - 1
group by adzoneid having count(*) > 1( 访问一次表 );

 

能 cache 就 cache

• 配置表 ( 小表， DML 不频繁 )

• 试用中心首页 (cache 时间按需求 )

• 搜索

• 内存数据库 (Times Ten)

 

减少 CPU 消耗

• 使用绑定变量 --– 针对不同的 sql ， oracle 会将 sql 放置到 shared pool 中去，如果本身都是差不多的 sql ，只是条件的值在变化，推荐使用绑定变量的方式。这样数据库只需哟啊缓存一条 sql 就够了。如果不这么做的话， shared pool 很有可能因为缓存的 sql 过多，导致池子空间不足，使用 LRU 算法，将某些 sql 排除出去后，又要不断的对没有命中 shared pool 缓存的 sql 进行编译，放置到 shared pool 操作，很有可能导致数据库速度急速下降。

 

• 较少或消除排序 ----

• 减少运算 --- 能在应用上做的运算，不用非留给数据库去计算。

 

非绑定变量写法：

Select * from employee where id=1234567

绑定变量写法：

Select * from employee where id=?

Preparestatement.setInt(1,1234567)

  绑定变量适用场景 :OLTP and OLAP

SQL执行计划：

 

充分利用数据库资源：

• 全表扫描开启并行 ( 业务底峰时使用 )

  select /*+ parallel(t 8) */ 字段 from t;

• 手工并行

   把表分块 , 表有 1000 万记录，要做数据订正，可手工把 1000 万记录分成 4 块，每个进程负责处理 1 块 (250 万 ) ，根据主机负载情况，动态调整进程数。

 

注意点：

• 如何正确使用 in&not in 、 exists&not exists

• 减少不必要的关联

• 多表关联时尽量使用表的别名来引用表的字段

• 只有小表才可以使用全模糊查询 (like '%xxx%')

• 在结果集合并时尽量用 union all 取代 union