SQL如何高效的实现多表查询？

0 0

SQL如何高效的实现多表查询？30

我有一个mysql数据库stockdb,里面保存了A股所有股票从上市到现在的交易数据（每日，一共大约2500只股票，最早1990年上市，大约有3000个交易日的数据）。我用一个table保存一只股票的数据，每个table都具有相同的结构(简化形式）：
(td date not null, open double, high double,low double, close double, primary key(td))
【td 表示交易日期】

现在我需要查询一批股票在某一个时间段内的（某个）交易数据，比如我想查询股票
s1,s2,...,sn,在 2000-01-01 <= td <= 2012-01-01内的数据，我希望返回这样一个表：
unionTD s1data s2data ... sndata
就是说第一列是所有这些股票交易日期的并集（因为有些股票在一些日期停牌所以缺少某些日期的数据，而另一些股票可能在其它日期没有数据，我希望取包含所有这些日期的公共日期），其它列为相应股票的相应数据

我尝试过以下几种不同的方法(下面以获取收盘价close为例,其中SQL语句只是为了说明问题，不完全符合SQL语法)
方法一：（最笨，但居然是目前最有效的，faint）
n次分别查询：

select td,close from sk where 2000-01-01 <=  td <= 2012-01-01 order by td

【k=1,2...,n】
然后把n次查询得来的数据在程序中处理合并为我需要的格式（实际上我用的是matlab）

方法二：（折腾了N久，结果大失所望）
利用视图和left jion，全部利用SQL语言搞定，返回需要的结果。
首先，建立一个包含所有公共日期的View ud

create or replace view ud as select  td  from s1 where 2000-01-01 <=  td <= 2012-01-01  union select td from s2  where 2000-01-01 <=  td <= 2012-01-01 union... select td from sn  where 2000-01-01 <=  td <= 2012-01-01;

然后用这个ud去left join所需的数据：

 select
    ud.td,s1.close,s2.close,...,sn.close from ud left join s1 on
    s1.td=ud.td left join s2 on s2.td=ud.td left join .... sn on sn.td=ud.td order by ud.td

想出这个语句让我兴奋了好久，以为毕其功于一役了，没想到实际一运行速度巨慢无比，比上面的方法差一个数量级以上，并且mysql对left join有限制，最多只能left join 61次，不合我的要求。

方法三：（这个为什么慢我实在不明白）
因为考虑到我是在matlab里面用jdbc连接mysql，我一直是往着尽可能少的调用jdbc这个方向努力（因为我觉得从matlab调用jdbc这个性能损耗应该会很大，这个纯粹主观臆测），所以我还是想构造一个sql语句一次性把所需要的数据导出来，然后在matlab里面再处理成我想要的。
这次我是这样构造的：

select 1 as id,td,close from s1  where 2000-01-01 <=  td <= 2012-01-01 union all select 2 as id,td,close from s2  where 2000-01-01 <=  td <= 2012-01-01 union all ... select n as id,td,close from sn  where 2000-01-01 <=  td <= 2012-01-01 order by id,td

这个方法有几个问题：
1，数据过大（其实我觉得不大）的时候就会导致java内存溢出，因此不得不在读出数据的时候加个每次读取的最大条数
2，速度还是慢，大概比第一种方法慢3倍以上
3，得到的数据需要处理，而且处理方法比第一、二种的处理方法都麻烦一些。

最后，我的问题：
一：我的数据库的设计是不是可以改进，以满足我快速读取数据的需要。（因为这个设计是我学SQL 不到1小时就动手搞的，应该可以有很多改进,我能想到的是在表里面添加一个stockName的列，然后就可以把所有的股票都放着一个表里，就不需要多表查询了)
二：如何构造一个sql语句来快速读取我上述要求的数据？

问题补充：顺便说一下，目前我的matlab程序读取300只股票从上市起所有的收盘价数据（一共是923629条数据）需要10s左右，我希望能控制到5秒内

2012年8月26日 19:48

Eastsun
2
0 0 1

5个答案按时间排序按投票排序

0 0

我有一个mysql数据库stockdb,里面保存了A股所有股票从上市到现在的交易数据（每日，一共

大约2500只股票，最早1990年上市，大约有3000个交易日的数据）。我用一个table保存一只

股票的数据，每个table都具有相同的结构(简化形式）：
(td date not null, open double, high double,low double, close double, primary key
(td))

2500*3000=750w数据

这个数据库是我自己用来炒股之用，只有一个需求，尽可能的提高“查找一批股票在一个时间

段的交易数据”的速度。

一共2500只股，你查询条件是根据股票查，即股票和股票之间是独立的；

1、建立股票表
id 名字

2、建立股票交易表核心数据若分10个表，每个表如果平均的话将得到75w数据；
分表规则可以是：股票id%10即可。

3、股票交易表
（股票编号日期）作为主键 ……

4、查询
4.1、首先查询所有股票（然后就放内存）
4.2、首先根据股票id%10得到表的编号，然后执行查询

也可以用分区表，分区条件可以是range 根据股票id进行分区。

2012年8月28日 18:54

jinnianshilongnian
7280
2 3 591

添加评论

0 0

1.建议重新考虑表结构，如果表结构都相同，可以直接使用分区表，或者按照时间分表。
2.按股票分组，比如40个表分为4组，每组10个表，通过union 查询数据到一个临时表，最后从临时表中获取数据，不过这是笨方法。

2012年8月27日 15:10

nanjinghhu
30
0 0 1

添加评论

0 0

你分表好象分的不太多啊，你目前的问题我感觉应该是数据量的问题吧，如果是这样的话，你得考虑如何把大数据量给分散开，然后在汇总，所有的分表都根据业务逻辑有关系，你的业务逻辑不带清楚，所以我也不好给答案，建议你分表还不够，还要分库，否则一个库2500多个表。。。感觉有点多

2012年8月27日 11:35

MrLee23
0
0 1 1

添加评论

0 0

你的数据量才90w，不是很多，mysql能搞定。

0、你如何分表的，根据什么条件？
1、td建立索引了吗？
2、查 2000-01-01 <= td <= 2012-01-01 这么大范围是必须的吗？
3、通过explain 分析你的sql，看看索引、返回记录数等。
4、建议记录好sql慢查询，随时分析，为以后找问题做准备。

1、
select td,close from sk where 2000-01-01 <= td <= 2012-01-01 //不要加order
建议一张表一张表的处理（在程序里考虑）。不要一下子全部查不出来。试试这种方案。

2、感觉你分表有问题，或者我不理解你的分表，建议可以根据时间分表（如1年1张表）；

3、数据以每年多少条记录进行增长。

2012年8月27日 06:22

jinnianshilongnian
7280
2 3 591

7条评论

0 0

1. 多次查询，本机内存处理，这种方法一般都是低效的
2. 建立视图不会提高效率的

你这里要做多表查询，按股票进行分表并不合理，新版本的mysql支持分区，利用时间进行分区是最佳方案，如果能清理历史数据，进行分表更佳，比如，最近一年一张表，一年前的所有数据查询得少，可以全部放在一张表。

另外，你问题描述也太长了。。

2012年8月26日 23:01

drougon
30
0 0 1

1条评论

已解决问题

未解决问题

排行榜

查看全部排名>>