查看: 18375|回复: 56

【话题讨论】大数据量下的数据库查询与插入如何优化?

[复制链接]
认证徽章
论坛徽章:
24
技术图书徽章
日期:2013-08-16 14:31:52问答徽章
日期:2013-11-04 08:53:14目光如炬
日期:2013-12-23 06:00:11目光如炬
日期:2013-12-30 06:00:11明星写手
日期:2014-02-22 06:00:12马上有钱
日期:2014-03-31 14:09:05沸羊羊
日期:2015-05-20 12:42:59秀才
日期:2015-06-24 13:05:36秀才
日期:2015-07-13 09:48:14
发表于 2013-7-17 14:56 | 显示全部楼层 |阅读模式
大家都知道数据库经常要做一些查询与插入,但是如果查询和插入的数据量过大的时候就会引发数据库性能问题,降低数据库工作效率。因此性能调优是大家在工作中都能够预见的问题,大到世界五百强的核心系统,小到超市的库存系统,几乎都会有要调优的时候。面对形形色色的系统,林林总总的需求,调优的手段也是丰富多彩。

今天的就是和大家讨论下: 大数据量下的数据库查询与插入如何优化?大家可以描述一下自己日常工作中用到的优化手段,不仅仅限于海量数据。

讨论时间:2013.7.17——2013.7.31

讨论奖励:活动结束后将会抽取3位会员赠送九州风神X6笔记本散热器一件。
QQ截图20130717145427.jpg

jimn1982   vikou    ses19828  
论坛徽章:
42
ITPUB14周年纪念章
日期:2015-10-26 17:23:44马上有车
日期:2014-11-04 11:10:45马上有房
日期:2014-10-31 08:36:54itpub13周年纪念徽章
日期:2014-10-07 08:42:18青年奥林匹克运动会-七人制橄榄球
日期:2014-09-17 10:39:112014年世界杯参赛球队:克罗地亚
日期:2014-07-11 16:42:38马上有对象
日期:2014-04-21 15:30:55马上有钱
日期:2014-04-14 13:12:42马上有钱
日期:2014-03-11 16:30:41马上有钱
日期:2014-12-12 08:40:36
发表于 2013-7-17 15:25 | 显示全部楼层
1.尽量使语句符合查询优化器的规则避免全表扫描而使用索引查询
2.避免频繁创建和删除临时表,以减少系统表资源的消耗。
3.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。
4.建立高效的索引


使用道具 举报

回复
论坛徽章:
5
ITPUB社区12周年站庆徽章
日期:2013-10-08 17:44:42秀才
日期:2015-05-29 15:34:28秀才
日期:2015-06-03 15:59:46秀才
日期:2015-06-11 11:28:35秀才
日期:2015-06-12 11:20:09
发表于 2013-7-17 15:36 | 显示全部楼层
SQL语句的Select部分只写必要的列;尽量将In子查询重写为Exists子查询;
去除在谓词列上编写的任何数学运算;尽可能不用Distinct;
由于优化工具处理“或”逻辑可能有问题,所以尽量采用其他方式重写;
确保所处理的表中数据分布和其他统计信息正确,并反映当前状况;尽可能用UNION ALL取代UNION;
尽可能减少DB2的SQL请求;尽量将区间谓词重写为Between谓词;不要只是为了排序而选择某一列;

使用道具 举报

回复
认证徽章
论坛徽章:
25
奥运会纪念徽章:射击
日期:2013-01-28 09:12:182014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-03-20 16:13:24马上有房
日期:2014-03-20 16:14:11马上有钱
日期:2014-03-20 16:14:11马上有对象
日期:2014-03-20 16:14:11马上加薪
日期:2014-03-20 16:14:11喜羊羊
日期:2015-04-09 18:46:34秀才
日期:2016-03-24 09:20:52
发表于 2013-7-17 16:09 | 显示全部楼层
我目前所在的系统就是这么一个有实时插入又需要大数据的查询的一个系统。
采用了如下手段:
1,当天的记录会放在一个独立的表中.主要是针对实时的插入的记录,记录不要太多以免插入的时候维护索引的开销稳定在一个范围内。
2,历史的记录会按天分区的形式保存在历史表中。这个表一天只会批量的插入一次数据(用的是分区交换的方法)。
3,分区的索引对我的业务性能不好,因为要跨天 查询。历史查询最长时间段是一个月的时间,如果按照一个月一个分区的话,一个分区差不多是一个亿的记录,
就算是按月分区的话,再创建分区的本地索引,如果是时间段跨了月份的话估计分区的本地索引性能估计也不行。
4,后来采用一个方案,DB层上面再放了一个缓冲层,就是我最近在测试的Timesten关系型内存数据库,按照时间的老化策略缓冲一个月的数据。具体不展开说了。涉及的内容很多。

只是对于这个系统,我总结一下有以下需要注意的地方:
1,对于一个系统来说,如果查询性能反应不好的话,第一个调整的地方是思考业务的需求是否是合理的?
一个查询既要分页获取前面一页或者几页的数据,又要根据条件获取总的记录数,如果符合的记录总数是上亿条的话,感觉就是一个不合理的要求。
2,市场需求调研人员业务水平根本不合格。
3,前台开发人员写的SQL差,根本没有调优的基本概念,术业有专攻啊。
4,如果业务需求合理,SQL的调整无非是从执行计划开始,如果是ORACLE10g,开了cbo,可以用 SQL优化器 (SQL Tuning Advisor :STA)
分析你的sql。
5,最近的nosq和海量分布式的数据库概念很热。公司也在考虑HBASE了。

oracle是越来越智能了。

使用道具 举报

回复
论坛徽章:
9
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:002013年新春福章
日期:2013-02-25 14:51:24迷宫蛋
日期:2013-03-06 17:43:59鲜花蛋
日期:2013-04-26 22:57:09蛋疼蛋
日期:2013-06-05 15:38:56林肯
日期:2013-08-16 16:46:322015年新春福章
日期:2015-03-04 14:53:162015年新春福章
日期:2015-03-06 11:58:39
发表于 2013-7-17 16:31 | 显示全部楼层
分区,
读写分离。

使用道具 举报

回复
论坛徽章:
3
2012新春纪念徽章
日期:2012-01-04 11:50:44鲜花蛋
日期:2012-01-31 22:23:392013年新春福章
日期:2013-02-25 14:51:24
发表于 2013-7-17 18:20 | 显示全部楼层
细节的优化手段大家都说了很多,我说些几个粗略的方面:
1.使用分区表
2.并行查询
3.定期的数据信息采集
4.可以考虑使用sql hint
欢迎其他同学补充

使用道具 举报

回复
论坛徽章:
11
奥运纪念徽章
日期:2012-11-28 09:37:30马上加薪
日期:2014-03-20 16:14:11马上有对象
日期:2014-03-20 16:14:11马上有钱
日期:2014-03-20 16:14:11马上有房
日期:2014-03-20 16:14:11马上有车
日期:2014-03-20 16:13:24ITPUB社区12周年站庆徽章
日期:2013-10-17 13:56:39ITPUB社区12周年站庆徽章
日期:2013-10-08 17:44:422013年新春福章
日期:2013-02-25 14:51:24ITPUB官方微博粉丝徽章
日期:2012-12-11 17:06:47
发表于 2013-7-17 18:58 | 显示全部楼层
1 数据量上亿时我都会使用分区,具体的分区方案看业务需求,如果查询数据是按时间来的且时间跨度小,那么我会按天分
2 读写分离,说实话这个对我来说只是理想方案,实现起来往往有许多困难。磁盘阵列能上则上。
3 SQL语句的优化,实话说很多程序员写出来的SQL真的不忍直视。
4 内存数据库,这个我还没有尝试过,但用过的人说还好。

使用道具 举报

回复
认证徽章
论坛徽章:
25
奥运会纪念徽章:射击
日期:2013-01-28 09:12:182014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-03-20 16:13:24马上有房
日期:2014-03-20 16:14:11马上有钱
日期:2014-03-20 16:14:11马上有对象
日期:2014-03-20 16:14:11马上加薪
日期:2014-03-20 16:14:11喜羊羊
日期:2015-04-09 18:46:34秀才
日期:2016-03-24 09:20:52
发表于 2013-7-17 19:14 | 显示全部楼层
squall3128 发表于 2013-7-17 18:20
细节的优化手段大家都说了很多,我说些几个粗略的方面:
1.使用分区表
2.并行查询

生产库上我个人认为还是少指定 HINT,可以考虑用 SQL_PROFILE
固定执行计划

使用道具 举报

回复
论坛徽章:
76
山治
日期:2019-03-27 22:55:03秀才
日期:2016-01-25 15:02:04双子座
日期:2016-01-19 20:35:54秀才
日期:2016-01-13 12:14:26秀才
日期:2015-12-25 15:31:10秀才
日期:2015-12-18 09:28:57秀才
日期:2015-12-14 14:56:09秀才
日期:2015-12-14 14:51:16秀才
日期:2015-11-30 09:13:06处女座
日期:2015-11-27 12:27:01
发表于 2013-7-17 20:06 | 显示全部楼层
插入还好吧,开了parallel走direct path,比delete/update好多了。
除非存储太烂。

使用道具 举报

回复
论坛徽章:
76
山治
日期:2019-03-27 22:55:03秀才
日期:2016-01-25 15:02:04双子座
日期:2016-01-19 20:35:54秀才
日期:2016-01-13 12:14:26秀才
日期:2015-12-25 15:31:10秀才
日期:2015-12-18 09:28:57秀才
日期:2015-12-14 14:56:09秀才
日期:2015-12-14 14:51:16秀才
日期:2015-11-30 09:13:06处女座
日期:2015-11-27 12:27:01
发表于 2013-7-17 20:17 | 显示全部楼层
插入还好吧,开了parallel走direct path,比delete/update好多了。
除非存储太烂。

使用道具 举报

回复

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

本版积分规则 发表回复

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