我有一个表xx(cbbh,jlsj,hc,jin,wei,qxdm,usecode,isnew),里面的数据可能有上亿条,并且每天增长速度非常的大(日增长大约会是二三万条),实表中部份数据贴出如下:
id cbbh jlsj wei qxdm usecode isnew
1 ZY00085 2005-8-26 9:53:09 110.264236450195 080101 ST0007 0
2 ZY00085 2005-8-26 9:54:19 110.263732910156 080101 ST0007 0
3 ZY00085 2005-8-26 9:55:29 110.262702941895 080101 ST0007 0
4 ZY00085 2005-8-26 9:56:39 110.261848449707 080101 ST0007 0
5 ZY00085 2005-8-26 9:57:49 110.260482788086 080101 ST0007 0
6 ZY00085 2005-8-26 9:58:59 110.259353637695 080101 ST0007 0
7 ZY00085 2005-8-26 10:00:09 110.259185791016 080101 ST0007 0
8 ZY00085 2005-8-26 10:01:19 110.259597778320 080101 ST0007 1
9 ZY00002 2005-8-1 14:54:52 120.010459899902 080101 ST0007 0
10 ZY00002 2005-8-1 13:47:53 119.003585815430 080101 ST0007 0
11 ZY00002 2005-8-1 13:49:03 119.003181457520 080101 ST0007 0
12 ZY00002 2005-8-1 13:50:13 119.003463745117 080101 ST0007 0
13 ZY00002 2005-8-1 13:48:44 119.003585815430 080101 ST0007 0
14 ZY00002 2005-8-1 13:49:54 119.003181457520 080101 ST0007 0
15 ZY00002 2005-8-1 13:51:04 119.003463745117 080101 ST0007 0
16 ZY00002 2005-8-1 13:52:14 119.004318237305 080101 ST0007 1
17 ZY00003 2005-8-2 10:10:31 109.060165405273 080101 ST0002 0
18 ZY00003 2005-8-2 10:11:41 109.059730529785 080101 ST0002 0
19 ZY00003 2005-8-2 10:12:51 109.058998107910 080101 ST0002 0
20 ZY00003 2005-8-2 10:14:01 109.058135986328 080101 ST0002 0
21 ZY00003 2005-8-2 10:15:11 109.057250976563 080101 ST0002 0
22 ZY00003 2005-8-2 10:16:21 109.056182861328 080101 ST0002 0
23 ZY00003 2005-8-2 10:17:31 109.054763793945 080101 ST0002 1
24 ZY00002 2005-8-4 10:06:35 119.003585815430 080101 ST0007 0
25 ZY00002 2005-8-4 10:07:45 119.003181457520 080101 ST0007 1
26 ZY00085 2005-8-26 9:29:49 110.267150878906 101001 ST0007 0
27 ZY00085 2005-8-26 9:30:59 110.267417907715 101001 ST0007 0
28 ZY00085 2005-8-26 9:32:09 110.267700195313 101001 ST0007 0
29 ZY00085 2005-8-26 9:33:19 110.268066406250 101001 ST0007 0
问题:我在使用该表查询的时,有可能以(cbbh,qxdm,usecode,isnew)中的任意一个或多个作为查询条件(例:以isnew=1为条件或以isnew=1 and cbbh=ZY00085....),请问在这种要求下我应该建立什么样的索引,以及要建多少个索引才能最快速的找到相应的记录(注,我没有建任何索引的情况下找出要求的记录要将近三四分钟,我想把其缩短到三四秒或更少,请指教)
id cbbh jlsj wei qxdm usecode isnew
1 ZY00085 2005-8-26 9:53:09 110.264236450195 080101 ST0007 0
2 ZY00085 2005-8-26 9:54:19 110.263732910156 080101 ST0007 0
3 ZY00085 2005-8-26 9:55:29 110.262702941895 080101 ST0007 0
4 ZY00085 2005-8-26 9:56:39 110.261848449707 080101 ST0007 0
5 ZY00085 2005-8-26 9:57:49 110.260482788086 080101 ST0007 0
6 ZY00085 2005-8-26 9:58:59 110.259353637695 080101 ST0007 0
7 ZY00085 2005-8-26 10:00:09 110.259185791016 080101 ST0007 0
8 ZY00085 2005-8-26 10:01:19 110.259597778320 080101 ST0007 1
9 ZY00002 2005-8-1 14:54:52 120.010459899902 080101 ST0007 0
10 ZY00002 2005-8-1 13:47:53 119.003585815430 080101 ST0007 0
11 ZY00002 2005-8-1 13:49:03 119.003181457520 080101 ST0007 0
12 ZY00002 2005-8-1 13:50:13 119.003463745117 080101 ST0007 0
13 ZY00002 2005-8-1 13:48:44 119.003585815430 080101 ST0007 0
14 ZY00002 2005-8-1 13:49:54 119.003181457520 080101 ST0007 0
15 ZY00002 2005-8-1 13:51:04 119.003463745117 080101 ST0007 0
16 ZY00002 2005-8-1 13:52:14 119.004318237305 080101 ST0007 1
17 ZY00003 2005-8-2 10:10:31 109.060165405273 080101 ST0002 0
18 ZY00003 2005-8-2 10:11:41 109.059730529785 080101 ST0002 0
19 ZY00003 2005-8-2 10:12:51 109.058998107910 080101 ST0002 0
20 ZY00003 2005-8-2 10:14:01 109.058135986328 080101 ST0002 0
21 ZY00003 2005-8-2 10:15:11 109.057250976563 080101 ST0002 0
22 ZY00003 2005-8-2 10:16:21 109.056182861328 080101 ST0002 0
23 ZY00003 2005-8-2 10:17:31 109.054763793945 080101 ST0002 1
24 ZY00002 2005-8-4 10:06:35 119.003585815430 080101 ST0007 0
25 ZY00002 2005-8-4 10:07:45 119.003181457520 080101 ST0007 1
26 ZY00085 2005-8-26 9:29:49 110.267150878906 101001 ST0007 0
27 ZY00085 2005-8-26 9:30:59 110.267417907715 101001 ST0007 0
28 ZY00085 2005-8-26 9:32:09 110.267700195313 101001 ST0007 0
29 ZY00085 2005-8-26 9:33:19 110.268066406250 101001 ST0007 0
问题:我在使用该表查询的时,有可能以(cbbh,qxdm,usecode,isnew)中的任意一个或多个作为查询条件(例:以isnew=1为条件或以isnew=1 and cbbh=ZY00085....),请问在这种要求下我应该建立什么样的索引,以及要建多少个索引才能最快速的找到相应的记录(注,我没有建任何索引的情况下找出要求的记录要将近三四分钟,我想把其缩短到三四秒或更少,请指教)
其他列重复数据太多
减小查询时间主要还是优化语句,也许你的查询语句可以优化。
FROM "HY_CBDW",
"HY_CBJBZL",
"HY_SYSINFO"
WHERE ( "HY_CBDW"."CBBH" = "HY_CBJBZL"."CBBH" ) and
( "HY_CBDW"."USECODE" = "HY_SYSINFO"."USECODE" ) and
( "HY_CBDW"."USECODE" = "HY_CBJBZL"."USECODE" ) and
( "HY_CBJBZL"."QXDM" = "HY_CBDW"."QXDM" ) and
( "HY_CBDW"."QXDM" = "HY_SYSINFO"."QXDM" ) and
( hy_cbdw.qxdm='101001' and hy_cbdw.usecode='ST0002' and hy_cbdw.cbbh='浙椒机506' and hy_cbdw.hc='2005-05-10'
or hy_cbdw.qxdm='101001' and hy_cbdw.usecode='ST0002' and hy_cbdw.cbbh='浙椒机506' and hy_cbdw.hc='2005-08-02_14'
or hy_cbdw.cbbh is null )
order by hy_sysinfo.usecode,hy_cbdw.cbbh,hy_cbdw.hc,hy_cbdw.jlsj
这个语句那位高手帮我优化一下?
换成
每个需要的字段(有很多重复字段没有用)
把
(hy_cbdw.qxdm='101001' and hy_cbdw.usecode='ST0002' and hy_cbdw.cbbh='浙椒机506' and hy_cbdw.hc='2005-05-10'
or hy_cbdw.qxdm='101001' and hy_cbdw.usecode='ST0002' and hy_cbdw.cbbh='浙椒机506' and hy_cbdw.hc='2005-08-02_14'
or hy_cbdw.cbbh is null )
分开,查询其中一个条件时间会少多少?估计优化的可能性不太大。