如何应付表数据过大的查询问题?
一般来说,对于做B/S架构的朋友来说,更有机会遇到高并发的数据库访 问情况,因为现在WEB的普及速度就像火箭升空,同时就会因为高访问量带来一系列性能问题,而数据库一直是用户与商人之间交流的重要平台。用户是没有耐心 忍受一个查询需要用上10秒以上的,或者更少些,如果经常出现服务器死机或者是报查询超时,我想那将是失败的项目。做了几年的WEB工作,不才,一直没有 遇到过大访问量或者是海量数据的情况。这里并不是说没有海量数据的项目就不是好项目,要看项目的应用场合。
最近做项目时,偶然得到了这个机会,在我工作过程中,本人发现的单表最大记录数高达9位数。像订单表什么的也有8位数。在查询订单的时候往往不能通过单表查询就能解决,还要和其它相关表进行关联查询。如此关联的表数据不大还好,一旦发生大表关联大表,在查询时就有可能出现慢长的等待。
主旨: 如何避免这种情况的发生呢?既然有了这样的数据,需求还是要实现,这里就我最近针对数据库的优化过程,我分两篇文章来说明下。
第一篇:如何尽量避免大表关联。
第二篇:对大表进行分区。
背景:有两张表:
1:订单表:记录用户订单的详细信息。order,其中有一个会员卡号字段cardNo,订单产生时间。
2:会员表:记录会员相关信息。member,一个会员有一个代理号:proxyID,代理下面有许多的会员卡:cardNo,它们共用一个代理号。
两表通过cardNo来相关联。
需求:查询一个用户或者某些用户某一时间段所有会员卡产生的订单情况。
实现SQL:
select 字段 from order
inner join member on
order.cardNo=member.cardNo
and member.proxyID in(a-01,代理号二)
and 时间 between 20080101 and 20080131
本人见解:我想一般的朋友看到这样的需求大多会写出这样的查询SQL,如果不喜欢用in或者认为in的性能不好的朋友可用union all 代替。SQL语句可以说简单的不能再简单了,本身并无问题,只是如果两表的数据都在百万以上,而且字段都特别多。此时如果只有索引的帮忙下并不一定能达到 预期的效果。
解决方案一:利用表变量来替换大表关联,表变量的作用域为一个批处理,批处理完了,表变量也会随之失效,比起临时表有它独特的优点:不用手动去删除表变量以释放内存。
可行性:因为需求中的输出字段大多来自订单表,member表只起到数据约束的作用,和查询用户会员卡号的作用,所有可以先把代理的会员卡号先取到表变量中,然后利用带有卡号的表变量和订单表相关联查询。
declare @t table
(cardNo int)
insert @t
select cardNo from member where in(a-01,代理号二)
select 字段 from order
inner join @t on
order.cardNo=@t.cardNoand 时间 between 20080101 and 20080131
这里我就不贴性能比较图了,有兴趣的朋友可以自己尝试下。这种方法在查询人员比较多的时候特别有帮助。它要开发员根据实际情况详细比较,结果并不是统一的,不同的环境结果可能不一样。希望大家理解。
解决方案二:利用索引视图来提高大表关联的性能。
可行性:一般在大表关联时,我们的输出列都远小于两表的字段合,像上面的member表只用到了其中的两个字段(cardNo,proxyID)。设想一下,此时的member表如果只有这两个字段情况会不会好些呢?答案不言而喻。
视图这个名词在我以前对它的印象中,从来没有认为视图能优化查询,因为我认为视图对于数据库来说就是一个虚假表,在数据库中并无实际物理位置来存储数 据。对于用户来说无非就是通过不同的视角来观看结果。视图数据的产生都是实时的,即当调用视图时,自动扩展视图,去运行里面相应的select语句。后来 才知道在2000后的版本中视图分一般视图和索引视图,一般视图就是没有创建索引的我印象中的视图。而创建了视图后就称为索引视图。索引视图是物理存在 的,可在视图上首先创建一个唯一的聚集索引,其它字段上也可创建非聚集索引。在不改变基础表的情况下,起到了优化的效果。
CREATE VIEW memberView
WITH SCHEMABINDING
AS
SELECT cardNo,proxyID from member
GO
--以会员卡号创建一个唯一聚集索引
CREATE UNIQUE CLUSTERED INDEX ix_member_cardNo
ON member (cardNo);
GO
注意:创建索引视图要点:
1: CREATE VIEW memberView后面要跟上WITH SCHEMABINDING
理由:
● 使用 schemaname。objectname 明确识别视图所引用的所有对象,而不管是哪个用户访问该视图。
● 不会以导致视图定义非法或强制 SQL Server 在该视图上重新创建索引的方式,更改视图定义中所引用的对象。
2:视图上的第一个索引必须为 CLUSTERED 和 UNIQUE。
理由:必须为 UNIQUE 以便在维护索引视图期间,轻松地按键值查找视图中的记录,并阻止创建带有重复项目的视图(要求维护特殊的逻辑)。必须为 CLUSTERED,因为只有聚集索引才能在强制唯一性的同时存储行。
3:以下情况可考虑创建索引视图:
● 可预先计算聚合并将其保存在索引中,从而在查询执行时,最小化高成本的计算。
● 可预先联接各个表并保存最终获得的数据集。
● 可保存联接或聚合的组合。
4:基础表的更新会引发索引视力的更新。
5:索引视图的创建同时会带来维护上的开销。
理由:
1)因为索引视图是物理存在的。
2)要额外的维护索引。
实现:SQL:select 字段 from order
inner join memberView on
order.cardNo=member.cardNo
and member.proxyID=in(a-01,代理号二)
and 时间 between 20080101 and 20080131
总结:两种解决方案来看,各有所长,一般可以优先考虑使用索引视图来优化大表关联。以上是本人对于如何尽量避免发生大表关联所采取的措施,望大家指教。
本文出自seven的测试人生公众号最新内容请见作者的GitHub页:http://qaseven.github.io/
为什么索引可以让查询变快?终于有人说清楚了! 上表是一张真实的数据库表,其中每一行是一条记录,每条记录都有字段。假设上面的数据库是一个有10万条记录的大数据库。现在,我们想从10万条记录中搜索一些内容,那么挨着一个一个搜索无疑将花费很长的时间,这个时候我们在数据结构与算法里学的二分查找法就派上了用场。
相关文章
- MySql LAST_INSERT_ID 【插入多条数据时】
- 【华为云技术分享】云图说|第三方云厂商数据如何迁移至华为云OBS?真相在这里......
- Python如何爬取实时变化的WebSocket数据
- 如何使用modelarts训练海量数据
- 支撑EB级规模的大数据平台深度揭秘
- 如何选择大数据应用程序
- 浅谈探索性数据分析的方法—如何下手处理一堆繁杂的数据
- 如何将select所选择的数据传入到input中
- 如何使用PLSQL Developer从oracle数据库 导入导出数据
- 阿里如何实现海量数据实时分析技术-AnalyticDB
- 数据该如何真正驱动业务增长
- uni-app:使用uni-list显示列表数据之二:下拉刷新(hbuilderx 3.6.18)
- 数据中台如何建设,如何选型?
- 南大《探索数据的奥秘》课件示例代码笔记13
- 机器学习笔记 - 如何像数据科学家一样思考
- AngularJS集合数据遍历显示
- 如何自行查找出SAP CRM产品主数据页面的assignment block基于的settype类型
- 如何将csv包含的数据导入SAP Cloud Platform HANA MDC里
- Angular应用从Component到Html的数据绑定是如何实现的 -数据流的讨论
- WebRTC 如何在安卓系统上采集视频数据
- 50亿海量数据如何高效存储和分析? GaussDB (for Cassandra) 3个秘诀搞定
- 如何实现数据跨链交互?
- 上万规模数据湖如何在实验室测试
- leaflet 加载数据,显示热力图(063)
- 如何保证缓存与数据库的数据一致性
- 阿里巴巴云原生大数据运维平台 SREWorks 正式开源