多库多表联合查询效率优化问题

[oathevil]

题目如下：
表Table1结构
c_id,orderNo,c3,c4,c5

有1000个这样的表（Table1...Table1000，每个表大概100多万笔记录），c_id主键，orderNo不唯一（有建立非聚簇索引）

也就是这1000个表相当于一个大数据，我要依据orderNo查出我想要的数据（查出的结果数据不超过500笔）

有种多库多表联合起来一起查的意思，怎么写个存储过程作查询比较快？

我现在400个表（数据量大概在4亿左右）按orderNo查询的话大概要15分钟左右。

用的是
SELECT orderNo,c3,c4,c5 FROM Table1 WHERE orderNo= 'xxxxxxxx' UNION
SELECT orderNo,c3,c4,c5 FROM Table2 WHERE orderNo= 'xxxxxxxx' UNION
...

数据库方面的高手有没有什么好的建议、?? 或给个示例代码

[LongRui888]

之前也有人提出类似的问题。 你这么 union ，把400多个表union起来，速度肯定是慢的。 能不能考虑把原来的那么多的表合并成一个表呢，建个分区表，按照某个字段分区，这样至少你不用写400行的代码来union了

[shoppo0505]

把 SELECT orderNo,c3,c4,c5 FROM Table1 WHERE orderNo= 'xxxxxxxx' 这个语句作为触发器放在表上，所有选择出来的数据放入目标表格，以后的操作都在目标表格上进行。

[oathevil]

引用 1 楼 yupeigu 的回复:

之前也有人提出类似的问题。 你这么 union ，把400多个表union起来，速度肯定是慢的。 能不能考虑把原来的那么多的表合并成一个表呢，建个分区表，按照某个字段分区，这样至少你不用写400行的代码来union了

把这些表合成单表数据量会不会太大，一个表按100万笔记录算，400个表合起来的话也有4亿笔记录了，这样做会什么依据吗？？ 另外，建分区表的话看起来也不是很适合， 没有合适的字段。 orderNo的话这个是不宜作分区字段的

[發糞塗牆]

你每次查询都要400个表全部关联？试试索引视图

[oathevil]

引用 4 楼 DBA_Huangzj 的回复:

你每次查询都要400个表全部关联？试试索引视图

因为它400个表的记录都是不一样的，所以我要查一些数据都得从这400个表里面查

[LongRui888]

引用 3 楼 oathevil 的回复:

[quote=引用 1 楼 yupeigu 的回复:] 之前也有人提出类似的问题。 你这么 union ，把400多个表union起来，速度肯定是慢的。 能不能考虑把原来的那么多的表合并成一个表呢，建个分区表，按照某个字段分区，这样至少你不用写400行的代码来union了

把这些表合成单表数据量会不会太大，一个表按100万笔记录算，400个表合起来的话也有4亿笔记录了，这样做会什么依据吗？？ 另外，建分区表的话看起来也不是很适合， 没有合适的字段。 orderNo的话这个是不宜作分区字段的[/quote] c_id呢，能不能按照一个范围来分区呢。比如小于400w的一个分区，400w-800w，一个分区，以此类推。

[發糞塗牆]

引用 5 楼 oathevil 的回复:

[quote=引用 4 楼 DBA_Huangzj 的回复:] 你每次查询都要400个表全部关联？试试索引视图

因为它400个表的记录都是不一样的，所以我要查一些数据都得从这400个表里面查[/quote]重新分析数据库设计，看看有没有办法用空间换时间

[發糞塗牆]

每次都要从400个表查的话，估计要分布式计算了

[oathevil]

引用 7 楼 DBA_Huangzj 的回复:

[quote=引用 5 楼 oathevil 的回复:] [quote=引用 4 楼 DBA_Huangzj 的回复:] 你每次查询都要400个表全部关联？试试索引视图

因为它400个表的记录都是不一样的，所以我要查一些数据都得从这400个表里面查[/quote]重新分析数据库设计，看看有没有办法用空间换时间[/quote] 目前的情况看来的话，作分区表好像不行了，那这样的话以空间换时间是否可行 ？？ orderNo其实是有大小的，但不唯一， 是一个13位以内的整数。它如果配合c5的话，可以在这400个表里面保证唯一性 假如现在我把它们合成一个表，再作索引是否可行？？若可， 索引怎么设会比较好

[發糞塗牆]

如果能合表，4亿不大，做下分区也就可以了，不过13位的no做分区列性能不会很好，所以我才说要考虑一下设计上是否存在漏洞或者不合理的地方，比如刚才的空间换时间，假设你每次都从400个表里面查类似的数据，那就先把这400个表中的这些数据加载到一个表，这样数据量应该不会有4亿，然后再对这个表做计算。

[Q315054403]

一定是有“经验”的人士设计的系统，呵呵 若系统有源代码，改造或优化是正道

[oathevil]

其实我的想法是这样的，有4亿笔记录的话， 因为它有一个13位的orderNo，所以我想如果可以把这4亿笔记录按orderNo进行排序，那么按照计算机基本的算法来说，就用简单的二分法的话在较短的时间内找到orderNo为指定值的记录（orderNo为同一个值的记录大多数情况下不超过50笔）的话应该不是问题。 但目前的问题就是不知道这个想法在Sql server中怎么去实现。

[LongRui888]

引用 12 楼 oathevil 的回复:

其实我的想法是这样的，有4亿笔记录的话， 因为它有一个13位的orderNo，所以我想如果可以把这4亿笔记录按orderNo进行排序，那么按照计算机基本的算法来说，就用简单的二分法的话在较短的时间内找到orderNo为指定值的记录（orderNo为同一个值的记录大多数情况下不超过50笔）的话应该不是问题。 但目前的问题就是不知道这个想法在Sql server中怎么去实现。

首先是把数据都和在一个表里，然后按照orderNO字段建个索引就可以了。

[haitao]

最好是转为分区表，几亿没问题 不行就人为实现分区的效果：先按查询对象确定在哪一个表，再查这个表。。。。

[ChinaITOldMan]

关注，学习中，帮顶起来！

[oathevil]

最后做法还是采用， insert into Table SELECT orderNo,c3,c4,c5 FROM Table1 UNION SELECT orderNo,c3,c4,c5 FROM Table2 UNION ... SELECT orderNo,c3,c4,c5 FROM Table400 把400个表（大概4亿笔数据）的数据放在一个表中，然后在orderNo上建立非聚簇索引，后面通过 select * from Table where roderNo = '...' 时间基本在1分钟内就可以查到内容 PS： 表合并的过程比较耗时间

[诺亚的木船]

引用 16 楼 oathevil 的回复:

最后做法还是采用， insert into Table SELECT orderNo,c3,c4,c5 FROM Table1 UNION SELECT orderNo,c3,c4,c5 FROM Table2 UNION ... SELECT orderNo,c3,c4,c5 FROM Table400 把400个表（大概4亿笔数据）的数据放在一个表中，然后在orderNo上建立非聚簇索引，后面通过 select * from Table where roderNo = '...' 时间基本在1分钟内就可以查到内容 PS： 表合并的过程比较耗时间

请问是将400个表名都打上吗？如果不止400张表，而是1000张表怎办？有什么更好的办法？