如何建立合适的索引?
当你接手一个系统时,通常是从检查系统负载,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.考虑对是否可以对原有索引进行合并。
建议继续学习:
- 由浅入深探究mysql索引结构原理、性能分析与优化 (阅读:14994)
- 浅谈MySQL索引背后的数据结构及算法 (阅读:9885)
- 由浅入深理解索引的实现(2) (阅读:6341)
- HBase二级索引与Join (阅读:5788)
- InnODB和MyISAM索引统计集合 (阅读:5190)
- Innodb 表和索引结构 (阅读:4793)
- mysql查询中利用索引的机制 (阅读:4716)
- MySQL索引背后的数据结构及算法原理 (阅读:4418)
- 多维度分类排行榜应用:用位图索引 (阅读:4011)
- mysql索引浅析 (阅读:4077)
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:Incessant 来源: Incessant
- 标签: 索引
- 发布时间:2009-10-11 22:31:21
- [55] IOS安全–浅谈关于IOS加固的几种方法
- [55] Oracle MTS模式下 进程地址与会话信
- [54] 如何拿下简短的域名
- [53] android 开发入门
- [52] Go Reflect 性能
- [52] 图书馆的世界纪录
- [49] 读书笔记-壹百度:百度十年千倍的29条法则
- [47] 【社会化设计】自我(self)部分――欢迎区
- [38] 程序员技术练级攻略
- [33] 视觉调整-设计师 vs. 逻辑