查看: 13983|回复: 13

[原创] 【求解】大数据量高并发的Insert语句的设计和优化

[复制链接]
论坛徽章:
11
2010新春纪念徽章
日期:2010-03-01 11:19:072014年新春福章
日期:2014-02-18 16:42:02优秀写手
日期:2014-02-09 06:00:122011新春纪念徽章
日期:2011-02-18 11:43:34数据库板块每日发贴之星
日期:2010-12-22 01:01:01数据库板块每日发贴之星
日期:2010-11-26 01:01:012010广州亚运会纪念徽章:拳击
日期:2010-11-22 15:26:49ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2010-07-10 01:01:04数据库板块每日发贴之星
日期:2010-07-07 01:01:01
发表于 2010-3-22 15:15 | 显示全部楼层 |阅读模式
目前正在上的一个项目,主要数据表是一张基础PV表(见图),保存每个网站的流量统计数据,如:PV,UV,访客环境等数据,该表有以下业务

和性能要求(由项目的业务和性能要求而决定)


1、该表是整个项目的最基础的数据表,仅一张表,每天的数据量是1000W,进行了Sharding切分后,目前分到两个库中,每个库中的PV基础表

   保持500W记录/天。

2、每30分钟会定时执行Job,对基础数据表中的数据进行聚合分析统计,将分析统计的数据插入到多个(17张)中间表中,中间表的数据供前台

   展现使用。

3、插入数据的频率比较频率,500W数据(按单库)不可能24小时都在插入,也就是说可能是在10个小时或12个小时内就已经全部插入到表里了

   因为晚上半夜的时候统计的网站是不可能有人还访问的,因此也就没记录。数据的录入肯定都集中在白天


插入基础PV表的存储过程如下(附件中在文本文档中也有),表的创建脚本在附件中


create or replace procedure pv_insert
(
  m_id number,  --自增ID
  m_siteid number,  --网站ID
  m_visitorid varchar2,  --访客ID
  m_visitid varchar2, --访问ID
  m_visitnum number,  --第几次访问
  m_referdomain varchar2, --来路域名
  m_referurl varchar2,   --来路页面
  m_pagedomain varchar2, --受访域名
  m_pageurl varchar2,    --受访页面
  m_pagetitle varchar2,  --页面标题
  m_visittime date,  --访问时间
  m_interval number, --访问时长
  m_uniquepv number, --唯一浏览量
  m_prevpage varchar2,  --上一页
  m_nextpage varchar2,  --下一页
  m_enterflag number, --进入标记
  m_jumpflag number,  --跳出标记
  m_exitflag number,  --退出标记
  m_source number,  --来路分类
  m_iscost number,  --付费流量标记
  m_seid number,  --搜索引擎ID
  m_se varchar2,  --搜索引擎名
  m_keyword varchar2,  --关键字
  m_isnew number,  --新访客标记
  m_ip number,  --IP地址
  m_isp number, --网络接入商
  m_areaid number,  --地区ID
  m_brwpid number,  --浏览器ID
  m_brwpname varchar2,  --浏览器名
  m_brwid number,  --浏览器版本ID
  m_brw varchar2,  --浏览器版本名
  m_ospid number,  --操作系统ID
  m_ospname varchar2,  --操作系统名
  m_osid number,  --操作系统版本ID
  m_os varchar2,  --操作系统版本名
  m_pixel varchar2, --分辨率
  m_lang varchar2,  --语言
  m_java number,  --是否支持Java
  m_flash number, --是否支持Flash
  m_client number,  --终端类型
  m_createtime date, --创建时间
  m_delflag number,  --删除标记
  m_verflag number   --版本标记
)
is
begin
    insert into pd_pv

(pv_id,pv_siteid,pv_visitorid,pv_visitid,pv_visitnum,pv_referdomain,pv_referurl,pv_pagedomain,pv_pageurl,pv_pagetitle,
                     

pv_visittime,pv_interval,pv_uniquepv,pv_prevpage,pv_nextpage,pv_enterflag,pv_jumpflag,pv_exitflag,pv_source,pv_iscost,
                     

pv_seid,pv_se,pv_keyword,pv_isnew,pv_ip,pv_isp,pv_areaid,pv_brwpid,pv_brwpname,pv_brwid,pv_brw,pv_ospid,pv_ospname,
                      pv_osid,pv_os,pv_pixel,pv_lang,pv_java,pv_flash,pv_client,pv_createtime,pv_delflag,pv_verflag)
           values(m_id,m_siteid,m_visitorid,m_visitid,m_visitnum,m_referdomain,m_referurl,m_pagedomain,m_pageurl,m_pagetitle,
                  m_visittime,m_interval,m_uniquepv,m_prevpage,m_nextpage,m_enterflag,m_jumpflag,m_exitflag,m_source,m_iscost,
                  m_seid,m_se,m_keyword,m_isnew,m_ip,m_isp,m_areaid,m_brwpid,m_brwpname,m_brwid,m_brw,m_ospid,m_ospname,
                  m_osid,m_os,m_pixel,m_lang,m_java,m_flash,m_client,m_createtime,m_delflag,m_verflag);
    commit;
end;


目前的困惑和难点

1、插入一条记录就提交一次,在大数据量数据插入的情况下,性能效率会比较低吧,毕竟一天有500W记录入库(按单个库来算),如何改进可以

   提高插入数据的效率呢?

2、如果遇到同时对这个PV表插入记录,也就是并发插入的情况,同一个时间同时插入100条记录的话,如何保证并发插入的时候,不会插入失败

   或者会锁住表呢,保证数据的顺利插入?

3、由于每30分钟的定时统计分析也是要基于这个PV基础表进行Select查询操作,可能会建索引在该表上,但是Insert频繁肯定影响索引,索引本

   身肯定也会影响Insert的效率,因为要重新构造索引了,如何保证有索引的情况下,插入数据的性能效率呢?


自己的思路

1.  是不是采用加append nologging的方式好一点,用这样的Insert/*+append nologging*/ into 表名,是不是会提高插入的性能效率

2.  批量提交,每100条提交一次,类似

    declare row_num number := 0;

    begin
      loop
        Insert into pd_pv 。。。。(Insert语句);
        row_num := row_num + 1;
      
      if mod(row_num,100)=0 then
        commit;
      endif;
      end loop;
      commit;
    end;

    但是如果采用这种,我这还没提交的Insert数据会放到哪里呢,会不会发生锁表的情况,就是前面一个没插入进去,后面的记录肯定不能进去

    约束上不担心,用了sequence保证记录的ID是唯一而且自增的


希望各位高手指点
基础PV表.jpg

基础PV表.rar

2.53 KB, 下载次数: 27

插入基础PV表的SQL.txt

2.47 KB, 下载次数: 35

论坛徽章:
22
2010新春纪念徽章
日期:2010-03-01 11:08:33马上有对象
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有车
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:08:092012新春纪念徽章
日期:2012-02-13 15:08:092012新春纪念徽章
日期:2012-02-13 15:08:092012新春纪念徽章
日期:2012-02-13 15:08:092012新春纪念徽章
日期:2012-02-13 15:08:09
发表于 2010-3-22 15:49 | 显示全部楼层
1. 如果你的表结构涉及到别人(自己公司)的业务, 最好不要写这么详细..

2. 提高插入性能,,主要有2招.
1). 分别插入不同的表, 甚至是不同的库, 这样可以最大化降低争用.
2). 延迟插入, 比如10(xxx)秒插入一次数据, 不插入的时候将数据缓存在队列或者内存中(这样对于队列的要求较高,,还可能导致数据丢失).

使用道具 举报

回复
论坛徽章:
1
复活蛋
日期:2012-03-20 18:41:28
发表于 2010-3-22 15:52 | 显示全部楼层
因为对实时数据的准确性要求不高,可以考虑通过写日志文件(比如十分钟一个文件),然后sqlldr方法把日志文件append到表的方法。

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2010-3-22 15:58 | 显示全部楼层
1.你统计的维度是什么,能否确定一个维度做分区
2.如果不能确定一个比较合适的维度做分区,可以试试做hash partition (减少buffer busy wait和hw enqueue)
3.使用异步提交,避免并发造成log file sync
4.对sequence设置比较大的cache

[ 本帖最后由 棉花糖ONE 于 2010-3-22 16:04 编辑 ]

使用道具 举报

回复
论坛徽章:
5
会员2007贡献徽章
日期:2007-09-26 18:42:10奥运会纪念徽章:水球
日期:2008-10-24 13:17:39生肖徽章2007版:蛇
日期:2009-11-13 10:54:51ITPUB学员
日期:2009-11-30 11:23:13ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2010-3-22 16:40 | 显示全部楼层
这么大的并发插入,估计建立拆除连接都比较耗资源,可能要考虑中间缓存技术

使用道具 举报

回复
论坛徽章:
25
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:442010世博会纪念徽章
日期:2010-07-30 12:07:232011新春纪念徽章
日期:2011-02-18 11:43:332010广州亚运会纪念徽章:高尔夫球
日期:2011-04-11 18:22:37蜘蛛蛋
日期:2011-08-17 08:44:40ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15复活蛋
日期:2011-12-15 09:06:552012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB 11周年纪念徽章
日期:2012-10-09 18:06:202013年新春福章
日期:2013-02-25 14:51:24
发表于 2010-3-22 17:48 | 显示全部楼层
1000W每天量也不是特别大,还有就是在应用层错CACHE,每5000写一次DB

使用道具 举报

回复
论坛徽章:
0
发表于 2010-3-22 18:42 | 显示全部楼层
不能每次都提交,这样肯定比较慢,楼主可以采用外部表,批量查询

外部表比sqlldr的方式都快

使用道具 举报

回复
论坛徽章:
11
2010新春纪念徽章
日期:2010-03-01 11:19:072014年新春福章
日期:2014-02-18 16:42:02优秀写手
日期:2014-02-09 06:00:122011新春纪念徽章
日期:2011-02-18 11:43:34数据库板块每日发贴之星
日期:2010-12-22 01:01:01数据库板块每日发贴之星
日期:2010-11-26 01:01:012010广州亚运会纪念徽章:拳击
日期:2010-11-22 15:26:49ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2010-07-10 01:01:04数据库板块每日发贴之星
日期:2010-07-07 01:01:01
 楼主| 发表于 2010-3-22 19:10 | 显示全部楼层

回复 #7 ojuju10 的帖子

外部表? 能不能详细解释下呢

这个基础数据的获得是由系统的程序代码去获得,然后再调用数据库存储过程插入数据,不是查询其他表的数据再插入

所以这个有没有提升效率性能的实现方式呢

使用道具 举报

回复
论坛徽章:
22
2010新春纪念徽章
日期:2010-03-01 11:08:33马上有对象
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有车
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:08:092012新春纪念徽章
日期:2012-02-13 15:08:092012新春纪念徽章
日期:2012-02-13 15:08:092012新春纪念徽章
日期:2012-02-13 15:08:092012新春纪念徽章
日期:2012-02-13 15:08:09
发表于 2010-3-22 20:45 | 显示全部楼层
原帖由 fan0124 于 2010-3-22 19:10 发表
外部表? 能不能详细解释下呢

这个基础数据的获得是由系统的程序代码去获得,然后再调用数据库存储过程插入数据,不是查询其他表的数据再插入

所以这个有没有提升效率性能的实现方式呢


http://www.psoug.org/reference/externaltab.html

具体如何使用可以参考上述链接..

外部表可以支持直接做转化以及并行处理, 所以性能确实可以很好..

SQLLDR如果打开Direct Path Load与并行效率也可以非常高,,如果你的应用只是需要将数据加载到数据库中, 外部表确实是个很不错的选择..

使用道具 举报

回复
论坛徽章:
14
2009新春纪念徽章
日期:2009-01-04 14:52:282013年新春福章
日期:2013-02-25 14:51:24ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042011新春纪念徽章
日期:2011-01-04 10:35:172010广州亚运会纪念徽章:马术
日期:2010-11-22 15:29:06ITPUB9周年纪念徽章
日期:2010-10-08 09:31:212010系统架构师大会纪念
日期:2010-09-03 16:39:572010年世界杯参赛球队:希腊
日期:2010-06-22 12:15:492010数据库技术大会纪念徽章
日期:2010-05-13 09:34:232010新春纪念徽章
日期:2010-03-01 11:06:13
发表于 2010-3-22 22:36 | 显示全部楼层
外部表是一个选择,但生成的数据文件也得考虑效率问题

使用道具 举报

回复

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

本版积分规则 发表回复

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