查看: 4612|回复: 30

【大话IT】有哪些方法能比PK查询更快

[复制链接]
论坛徽章:
9
授权会员
日期:2005-10-30 17:05:33奥运会纪念徽章:自行车
日期:2008-10-24 13:07:492010新春纪念徽章
日期:2010-03-01 11:20:052010数据库技术大会纪念徽章
日期:2010-05-13 09:34:23蜘蛛蛋
日期:2011-12-28 11:33:332012新春纪念徽章
日期:2012-01-04 11:49:54优秀写手
日期:2014-11-22 06:00:132015年新春福章
日期:2015-03-04 14:19:112015年新春福章
日期:2015-03-06 11:57:31
发表于 2014-12-8 09:36 | 显示全部楼层 |阅读模式
前提:只考虑纯Oralce DB的解决方案,不考虑Nosql(包括Oracle自己的)等。
假设的SQL:select * from table where pk=xxx,表的数据量是持续增长的。
如果已经是走PK的index unique scan了,但仍然不够快,还有哪些优化方式?
IOT?如果对于表Insert速度没有太多要求的情况下,可以考虑使用。
Hash聚簇?如果表的数据量可以预估出来的情况下,可以考虑使用。
还有哪些方案?不知道有没有在实际中去使用的,效果如何?
论坛徽章:
22
2014年世界杯参赛球队: 哥斯达黎加
日期:2014-06-06 15:02:04沸羊羊
日期:2015-06-17 14:51:07天枰座
日期:2015-07-17 09:51:12金牛座
日期:2016-03-21 21:10:40摩羯座
日期:2016-03-29 15:07:30娜美
日期:2016-06-22 19:49:29奥运会纪念徽章:篮球
日期:2016-09-02 09:45:25奥运会纪念徽章:现代五项
日期:2016-09-23 16:25:05山治
日期:2018-01-04 21:26:162015年新春福章
日期:2015-04-27 10:18:48
发表于 2014-12-8 09:43 | 显示全部楼层
关注。。。

使用道具 举报

回复
认证徽章
论坛徽章:
223
2010新春纪念徽章
日期:2010-03-01 11:20:51ITPUB元老
日期:2019-04-25 13:46:07至尊黑钻
日期:2015-08-13 13:38:12至尊黑钻
日期:2015-02-15 09:47:472015年中国系统架构师大会纪念徽章
日期:2015-07-31 17:48:20管理团队2007贡献徽章
日期:2015-01-19 09:48:272015中国数据库技术大会纪念徽章
日期:2015-05-15 14:08:23海蓝宝石
日期:2015-02-03 10:23:39红宝石
日期:2015-02-03 10:26:04会员2007贡献徽章
日期:2015-02-03 10:26:41
发表于 2014-12-8 09:53 | 显示全部楼层
LZ  可以考虑一下 IOT , 索引组织表。

使用道具 举报

回复
论坛徽章:
9
授权会员
日期:2005-10-30 17:05:33奥运会纪念徽章:自行车
日期:2008-10-24 13:07:492010新春纪念徽章
日期:2010-03-01 11:20:052010数据库技术大会纪念徽章
日期:2010-05-13 09:34:23蜘蛛蛋
日期:2011-12-28 11:33:332012新春纪念徽章
日期:2012-01-04 11:49:54优秀写手
日期:2014-11-22 06:00:132015年新春福章
日期:2015-03-04 14:19:112015年新春福章
日期:2015-03-06 11:57:31
 楼主| 发表于 2014-12-8 09:54 | 显示全部楼层
本帖最后由 netfairy 于 2014-12-8 09:55 编辑

在类似的设计中,使用过IOT,查询速度是加快了,对Insert性能的损伤比想象的要大。
根据应用的反馈,批量Insert的速度慢了5倍以上,远超出了我预计的值。

使用道具 举报

回复
论坛徽章:
78
生肖徽章2007版:牛
日期:2012-08-02 22:43:00紫蛋头
日期:2012-12-08 09:43:38鲜花蛋
日期:2012-11-17 12:02:07鲜花蛋
日期:2013-02-05 21:53:34复活蛋
日期:2012-11-17 12:02:07SQL极客
日期:2013-12-09 14:13:35SQL数据库编程大师
日期:2013-12-06 13:59:43SQL大赛参与纪念
日期:2013-12-06 14:10:50ITPUB季度 技术新星
日期:2012-11-27 10:16:10最佳人气徽章
日期:2013-03-19 17:24:25
发表于 2014-12-8 10:35 | 显示全部楼层
select * from table where pk=xxx还不够快,现在是多快?你期望是多快?

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2014-12-8 10:46 | 显示全部楼层
多大啊???hash分区+unique index行不??

使用道具 举报

回复
论坛徽章:
9
授权会员
日期:2005-10-30 17:05:33奥运会纪念徽章:自行车
日期:2008-10-24 13:07:492010新春纪念徽章
日期:2010-03-01 11:20:052010数据库技术大会纪念徽章
日期:2010-05-13 09:34:23蜘蛛蛋
日期:2011-12-28 11:33:332012新春纪念徽章
日期:2012-01-04 11:49:54优秀写手
日期:2014-11-22 06:00:132015年新春福章
日期:2015-03-04 14:19:112015年新春福章
日期:2015-03-06 11:57:31
 楼主| 发表于 2014-12-8 10:56 | 显示全部楼层
dingjun123 发表于 2014-12-8 10:46
多大啊???hash分区+unique index行不??

数据量预估最大在2千万级别。
Hash分区的话,虽然减少了索引段大小,理论上来说,多了一次分区的计算(虽然能很快,但是避免不了cpu的消耗);如果PK已经每天在业务低谷期rebuild的话。Hash分区能将查询性能提升多少,有经验值吗?
另外,多谢版主的回复!

使用道具 举报

回复
论坛徽章:
9
授权会员
日期:2005-10-30 17:05:33奥运会纪念徽章:自行车
日期:2008-10-24 13:07:492010新春纪念徽章
日期:2010-03-01 11:20:052010数据库技术大会纪念徽章
日期:2010-05-13 09:34:23蜘蛛蛋
日期:2011-12-28 11:33:332012新春纪念徽章
日期:2012-01-04 11:49:54优秀写手
日期:2014-11-22 06:00:132015年新春福章
日期:2015-03-04 14:19:112015年新春福章
日期:2015-03-06 11:57:31
 楼主| 发表于 2014-12-8 15:16 | 显示全部楼层
本帖最后由 netfairy 于 2014-12-8 16:24 编辑

执行速度比较:
普通的PK:

create table TEST_PK
(
  owner          VARCHAR2(30),
  object_name    VARCHAR2(128),
  subobject_name VARCHAR2(30),
  object_id      NUMBER);
alter table TEST_PK add constraint pk_test_pk primary key(object_id);
INSERT INTO test_pk
  SELECT t.owner, t.object_name, t.subobject_name,rownum
    FROM test01 t
   WHERE rownum < 100001;
插入100000笔用时:3.219秒
PK做HASH 32分区:减少了索引段大小,理论上来说,多了一次分区的计算(虽然能很快,但是避免不了cpu的消耗)
create table TEST_PK_HASH
(
  owner          VARCHAR2(30),
  object_name    VARCHAR2(128),
  subobject_name VARCHAR2(30),
  object_id      NUMBER);
CREATE UNIQUE INDEX PK_TEST_PK_HASH ON TEST_PK_HASH (OBJECT_ID)
  global partition by hash(OBJECT_ID) partitions 32;
ALTER TABLE TEST_PK_HASH ADD CONSTRAINT PK_TEST_PK_HASH PRIMARY KEY (OBJECT_ID) USING INDEX PK_TEST_PK_HASH;
插入100000笔用时:6.671秒
单表Hash聚簇:
CREATE CLUSTER hash_obid(object_id NUMBER)
  SIZE 512
  SINGLE TABLE
  HASHKEYS 100000;
create table test_hash(
  owner          VARCHAR2(30),
  object_name    VARCHAR2(128),
  subobject_name VARCHAR2(30),
  object_id      NUMBER
)
CLUSTER hash_obid(object_id);
INSERT INTO test_hash
  SELECT t.owner, t.object_name, t.subobject_name,rownum
    FROM test01 t
   WHERE rownum < 100001;
插入100000笔用时:8.437秒
IOT表:
create table test_iot_table(
  owner          VARCHAR2(30),
  object_name    VARCHAR2(128),
  subobject_name VARCHAR2(30),
  object_id      NUMBER,
CONSTRAINT PK_TEST_IOT_TABLE PRIMARY KEY(object_id))
ORGANIZATION INDEX;
INSERT INTO test_iot_table
  SELECT t.owner, t.object_name, t.subobject_name,rownum
    FROM test01 t
   WHERE rownum < 100001;
插入100000笔用时:10.063秒
测试程序:
CREATE OR REPLACE PROCEDURE sp_test AS
  aa  TEST_PK%ROWTYPE;
  bb  NUMBER;
  cc1 TIMESTAMP;
  cc2 TIMESTAMP;
BEGIN
  -- PK
  cc1 := systimestamp;
  FOR i IN 1 .. 100000 LOOP
    bb := trunc(dbms_random.value(2, 99999));
    SELECT * INTO AA FROM TEST_PK WHERE object_id = bb;
  END LOOP;
  cc2 := systimestamp;
  dbms_output.put_line('PK:' || to_char(cc2 - cc1));
  -- PK by hash partition
  cc1 := systimestamp;
  FOR i IN 1 .. 100000 LOOP
    bb := trunc(dbms_random.value(2, 99999));
    SELECT * INTO AA FROM TEST_PK_HASH WHERE object_id = bb;
  END LOOP;
  cc2 := systimestamp;
  dbms_output.put_line('PK Hash:' || to_char(cc2 - cc1));
  -- hash cluster
  cc1 := systimestamp;
  FOR i IN 1 .. 100000 LOOP
    bb := trunc(dbms_random.value(2, 99999));
    SELECT * INTO AA FROM test_hash WHERE object_id = bb;
  END LOOP;
  cc2 := systimestamp;
  dbms_output.put_line('Hash:' || to_char(cc2 - cc1));
  -- iot
  cc1 := systimestamp;
  FOR i IN 1 .. 100000 LOOP
    bb := trunc(dbms_random.value(2, 99999));
    SELECT * INTO AA FROM test_iot_table WHERE object_id = bb;
  END LOOP;
  cc2 := systimestamp;
  dbms_output.put_line('IOT:' || to_char(cc2 - cc1));
END;
执行结果:看起来4者的性能差异非常小。也许是数据不够多,或者Index刚创建的原因。
PK:         +000000000 00:00:07.652509000
PK Hash: +000000000 00:00:07.967591000
Hash:      +000000000 00:00:07.469284000
IOT:        +000000000 00:00:07.497833000

使用道具 举报

回复
认证徽章
论坛徽章:
0
发表于 2014-12-8 16:04 | 显示全部楼层
是不是可以走rowid?

使用道具 举报

回复
论坛徽章:
0
发表于 2014-12-8 19:27 | 显示全部楼层
一般考虑IOT和HASH聚簇,但如楼主所言,均有其局限性。
持续关注,期待达人解惑!

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

DTCC2020中国数据库技术大会 限时8.5折

【架构革新 高效可控】2020年6月4日~6日第十一届中国数据库技术大会将在北京隆重召开。

大会设置2大主会场,20+技术专场,将邀请超百位行业专家,重点围绕数据架构、AI与大数据、传统企业数据库实践和国产开源数据库等内容展开分享和探讨,为广大数据领域从业人士提供一场年度盛会和交流平台。

http://dtcc.it168.com


大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表