模拟海量数据查询的优化方法

[hrb2008]

1.现table有记录一千五百万条以上(测试数据,实际数据量更大).含有province(省份),city(城市)字段,并以这两个字段进行查询(在这两个字段上已建非聚集索引)
2.当前优化方法,将table分成5个表table1..table5,每表300万条记录,在province(省份),city(城市)字段上建立非聚集索引
3.查询符合省份为"广东省"的记录一万四千条,数据库已在高级中设置并行数为64
执行
select * from table where province = '广东省' --影响行数14000条 耗时3 秒
执行
select * from table1 where province = '广东省' union all
select * from table2 where province = '广东省' union all
select * from table3 where province = '广东省' union all
select * from table4 where province = '广东省' union all
select * from table5
--影响行数14000条 耗时26 秒
执行
select * from table1 where province = '广东省' --影响行数14000条 耗时20 秒

问题:
1.为什么查询三百万条记录(table1)中的14000条在一千五百万条记录(table)中查询14000条要慢(两表中province中带有同样的索引)
2.是否采用union all方法会有问题,如不用union all 该用什么方式
3.拆表时分区是根据地区进行分区,table1中包括广东省,河南省,河北省table2中包括江西省,福建省...分区时是否合理
4.因为是按照province及city进行查询,所以没有想采取分区视图,因为分区键中check列只能有一列,是否还有其它的优化方法.

[sunylf]

頂一下，數據庫高手來啊。

[feixiangVB]

搬个凳子过来，我们一起等高手

[zjcxc]

select * from table where province = '广东省' --影响行数14000条 耗时3 秒
select * from table1 where province = '广东省' --影响行数14000条 耗时20 秒


这个是同样的环境和同样的结构下查询的结果吗? 多查询几次的结果一样吗? 它们的执行计划有差异吗?

理论上应该不会有些情况出现才对.

[zjcxc]

为什么查询三百万条记录(table1)中的14000条在一千五百万条记录(table)中查询14000条要慢(两表中province中带有同样的索引)
======================================

province是单独的索引吗?
如果是, 测试下面两句的速度, 如果后者快于前者, 说明表结构应该有一些差异(或者其他原因)
select province from table where province = '广东省' --影响行数14000条 耗时3 秒
select province from table1 where province = '广东省' --影响行数14000条 耗时20 秒

[澫大醉]

留个记号

[wpuuuu]

记号

[hrb2008]

两个语句是单独的索引,结构是同样的结构,就是表名改了一下,索引是这样建的,province单独建一非聚集索引,city单建一非聚集索引,现在苦恼的就是为什么结构一样的情况下,在小表中查出一条时间与大表中查出一条记录时间基本相同,但查邮一万多条的时候就出问题.另:
这个是同样的环境和同样的结构下查询的结果吗? 多查询几次的结果一样吗? 是在同样的环境和同样的结构下查询的结果.查了多次结果一样

[baggio785]

建议看一下2条语句的执行计划以及跟踪结果，应该可以看出问题在哪里的

把执行计划以及跟踪结果贴出来看看吧

[hrb2008]

to:zjcxc(邹建)
它们的执行计划有差异吗?
在查询时查看了一下执行计划,现在情况是
大表table中含一千五百万条记录含有字段:
iden, --(自增主键,为插入测试数据时准备)
startip, --varchar(50)
endip, --varchar(50)
province,--varchar(50)(加索引IX_Datatable非唯一索引)
city --varchar(50)(加索引IX_Datatable_1非唯一索引)
小表table1,table2,table3,table4,table5中包含三百万条记录
iden, --*主键,但此处非自增
startip, --varchar(50)
endip, --varchar(50)
province,--varchar(50)(加索引IX_Datatable1非唯一索引)
city --varchar(50)(加索引IX_Datatable1_1非唯一索引)
查找单一记录时执行
select * from table where province = '广西省' --影响行数1条 耗时0 秒
执行计划中:聚集索引查找100%非聚集索引0%
select * from table1 where province = '广西省' --影响行数1条 耗时0 秒
执行计划中:聚集索引查找97%非聚集索引3%
查找批量数据执行
select * from table where province = '广东省' --影响行数14000条 耗时3秒
执行计划中:聚集索引查找100%非聚集索引0%
select * from table1 where province = '广东省' --影响行数14000条 耗时26 秒
执行计划中:聚集索引扫描100%
问题:
1.索引建在同样的列上,为什么会有的执行非聚集索引查找,有的不执行非聚集索引查找
跟设自增列是否有关系
2.为何table1中取出批量数据时会执行聚集索引扫描?
3.如何优化或设计

[---涛声依旧---]

1.跟自增列沒有關系

[L5160729]

有邹大哥的参与这帖一定得顶.正好学习一下.

[hrb2008]

select * from table where province = '广东省'
select * from table1 where province = '广东省'
--------------------------------------------------------------------
(14015 行受影响)
表 'datable'。扫描计数 1，逻辑读取 56991 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(14015 行受影响)
表 'datable1'。扫描计数 1，逻辑读取 34168 次，物理读取 0 次，预读 34085 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

[guoyg226]

学习。
对于分区视图而言，子表中数据量的大小是否与硬件环境有关系?
在设定制约条件时，子表PK顺序对查询速度是否有关呢？