技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置首页 --> Oracle --> 如何建立合适的索引?

如何建立合适的索引?

浏览:5335次  出处信息

    当你接手一个系统时,通常是从检查系统负载,cpu、内存使用率开始,查看statspack top5等待事件,逻辑读、物理读排名靠前的sql语句等等,然后进行初步的优化。而随着业务的深入了解,你开始从系统的角度去考虑据库设计,考虑应用实现的合理性,是否有更好的改进方案等。假设通过statspack报表找到了很耗资源的sql,表分析过,执行计划也是走索引,这种情况下怎么去判断 sql是优化的呢?

    1.提取逻辑读排名靠前的sql

     6,813,699 336 20,278.9 10.1 66.72 80.45 3039661161

    module: java@admin1 (tns v1-v3)

     select b.biz_source, count(*) as counts from tb_hanzgs_de

    tail a, tb_business_info b where a.id = b.hanzgs_id

    and a.status = :1 and a.deal_id = :2 and a.create_date

    >= to_date(:3,\'yyyy-mm-dd hh24:mi:ss\') and a.create_date < to

    _date(:4,\'yyyy-mm-dd hh24:mi:ss\') group by b.biz_source

    2.查看执行计划

    sql> explain plan for

     2 select b.biz_source, count(*) as counts

     3 from tb_hanzgs_detail a, tb_business_info b

     4 where a.id = b.hanzgs_id

     5 and a.status = :1

     6 and a.deal_id = :2

     7 and a.create_date >= to_date(:3, \'yyyy-mm-dd hh24:mi:ss\')

     8 and a.create_date < to_date(:4, \'yyyy-mm-dd hh24:mi:ss\')

     9 group by b.biz_source;

    Explained.

    SQL> @?/rdbms/admin/utlxpls

    Plan hash value: 1387434542

    -----------------------------------------------------------------------

    id | operation | name | rows | bytes | cost |

    -----------------------------------------------------------------------

     0 | select statement | | 1 | 31 |215

     1 | sort group by | | 1 | 31 |215

     2 | filter | | | |

     3 | nested loops | | 1 | 31 |199

     4 | table access by index rowid| tb_hanzgs_detail | 1| 21 |198

     5 | index range scan | ind_tb_hanzgs_create | 231| |397

     6 | index range scan | ind_tb_business_info_biz | 1| 10 |1

    -----------------------------------------------------------------------

    索引定义

    create index ind_tb_hanzgs_create on tb_hanzgs_detail (create_date, deal_id,status, id)tablespace tbs_tb_ind online compute statistics;

    3.查看语句执行时间

    sql>select b.biz_source, count(*) as counts

     3 from tb_hanzgs_detail a, tb_business_info b

     4 where a.id = b.hanzgs_id

     5 and a.status = 1

     6 and a.deal_id = 0

     7 and a.create_date >= to_date(sysdate-10, \'yyyy-mm-dd hh24:mi:ss\')

     8 and a.create_date < to_date(sysdate-5, \'yyyy-mm-dd hh24:mi:ss\')

     9 group by b.biz_source;

    biz_source counts

    ---------- ----------

     102 712

     501 7881

     701 1465

    3 rows selected.

    elapsed: 00:00:17.03

    sql> /

    biz_source counts

    ---------- ----------

     102 713

     501 7882

     701 1465

    3 rows selected.

    elapsed: 00:00:05.32

    这个语句查询时间在5.3秒左右,对于查询频繁的oltp系统中,毫无疑问全表扫描的代价是最高的,按时间索引扫描数据效率也是很低的,毕竟一个时间段的数据也是不少的。考虑到上面sql正好使用时间列索引,如果status,rule_id列稀疏读很高的话,这些列建立索引性能应该会有很大的提高。

    4.查看表数据分布

    sql> select status, count(*) as counts

     2 from tb_hanzgs_detail

     3 where create_date >= to_date(sysdate - 50, \'yyyy-mm-dd hh24:mi:ss\')

     4 and create_date < to_date(sysdate - 49, \'yyyy-mm-dd hh24:mi:ss\') + 1

     5 group by status;

     status counts

    --------- ----------

     0 2

     1 286

     2 3567

     3 123477

    根据随机抽取几天数据分布结构,这个表中97%以上数据的status都等于3,如果status为3的查询应该会走全表扫描,消耗大量资源,查询频繁的话DBA是不允许开发部署到生产系统的,数据库压力太大。而status不等于3的数据量很少很少,以status列来建立索引,性能应该会有很大的提高。分析完就可以尝试的进行优化了。

    5.重新建立索引

    create index ind_tb_hanzgs_de_sta on tb_hanzgs_detail (status, deal_id, create_date,id)tablespace tbs_tb_ind online compute statistics;

    新执行计划

    sql> explain plan for

     2 select b.biz_source, count(*) as counts

     3 from tb_hanzgs_detail a, tb_business_info b

     4 where a.id = b.hanzgs_id

     5 and a.status = :1

     6 and a.deal_id = :2

     7 and a.create_date >= to_date(:3, \'yyyy-mm-dd hh24:mi:ss\')

     8 and a.create_date < to_date(:4, \'yyyy-mm-dd hh24:mi:ss\') + 1

     9 group by b.biz_source;

    explained.

    elapsed: 00:00:00.01

    14:11:48 sql> @?/rdbms/admin/utlxpls

    ------------------------------------------------------------------

    id | operation | name | rows | bytes | cost |

    ------------------------------------------------------------------

     0 | select statement | |1 |31 |19 |

     1 | sort group by | |1 |31 |19 |

     2 | filter | | | | |

     3 | nested loops | |1 |31 | 3 |

     4 | index range scan | ind_tb_hanzgs_de_sta |1 |21 | 4 |

     5 | index range scan | ind_tb_business_info_biz |1 |10 | 1 |

    ------------------------------------------------------------------

    6.重新执行该语句

    sql>select b.biz_source, count(*) as counts

     3 from tb_hanzgs_detail a, tb_business_info b

     4 where a.id = b.hanzgs_id

     5 and a.status = 1

     6 and a.deal_id = 0

     7 and a.create_date >= to_date(sysdate-10, \'yyyy-mm-dd hh24:mi:ss\')

     8 and a.create_date < to_date(sysdate-5, \'yyyy-mm-dd hh24:mi:ss\')

     9 group by b.biz_source;

    biz_source counts

    ---------- ----------

     102 713

     501 7881

     701 1465

    3 rows selected.

    elapsed: 00:00:00.24

    sql> /

    biz_source counts

    ---------- ----------

     102 713

     501 7882

     701 1465

    3 rows selected.

    elapsed: 00:00:00.23

    调整后的查询时间在0.2秒左右,速度提高了100倍左右,我只是简单的把索引列位置调换一下,性能就有了很大提高,statspack看不到这条语句了。总的来说,索引不是说create就可以了,通常需要考虑以下几点。

    1.结合实际的应用。

    2.考虑索引列的数据分布,如果distinct值很少且数据分布均匀的话,可能就不适合放在联合索引的最前面。

    3.考虑索引的大小,在字段长度32的列和长度为7的列上建立索引大小肯定是不一样的,索引越大扫描的代价就越高。

    4.考虑索引列冗余,可能你在索引中多冗余一个小字段,select就只走索引而不需要去扫描原表的数据。

    5.考虑索引对其他sql的影响,是否其他的sql也可以使用这个索引。

    6.考虑对是否可以对原有索引进行合并。

建议继续学习:

  1. 由浅入深探究mysql索引结构原理、性能分析与优化    (阅读:14945)
  2. 浅谈MySQL索引背后的数据结构及算法    (阅读:9861)
  3. 由浅入深理解索引的实现(2)    (阅读:6315)
  4. HBase二级索引与Join    (阅读:5764)
  5. InnODB和MyISAM索引统计集合    (阅读:5161)
  6. Innodb 表和索引结构    (阅读:4775)
  7. mysql查询中利用索引的机制    (阅读:4686)
  8. MySQL索引背后的数据结构及算法原理    (阅读:4402)
  9. 多维度分类排行榜应用:用位图索引    (阅读:4003)
  10. mysql索引浅析    (阅读:4062)
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
© 2009 - 2024 by blogread.cn 微博:@IT技术博客大学习

京ICP备15002552号-1