如何应付表数据过大的查询问题？

一般来说，对于做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/