一道面试题
1.有A-Z 26张表，表结构均一样，有id（int），username（varchar），createtime（datatime）三个健，数据存储时是随机向A-Z其中一张表存储，每张表的数据量均在100万以上请构造一个sql语句实现：分页，每页20条，将26张表数据综合，以createtime倒序排列
--------------------编程问答-------------------- 有点意思 期待大牛给出优雅解决方案 --------------------编程问答-------------------- 写肯定能写出来,效率嘛,再想想
那个倒序太要命了... --------------------编程问答--------------------
…不会…期待答案…
--------------------编程问答--------------------
我现在能想到的最好的结果也就是取每张表的按CREATETIME倒数20条,全部UNION起来,再取
前20条…
那个倒序太要命了…应该有取巧的办法
--------------------编程问答--------------------
同求答案一直没有解决方案 --------------------编程问答--------------------
推荐吧
--------------------编程问答--------------------
--------------------编程问答--------------------
应该搞到SQL版
--------------------编程问答--------------------
2600百万以上的数据，排序效率可想而知，期待大牛...
--------------------编程问答--------------------
--------------------编程问答--------------------
表示压力很大 要命的倒叙排列呀 不会 坐等大牛~~
--------------------编程问答--------------------
是啊 数据太多 等大牛
--------------------编程问答--------------------
createtime，也许就是关键，解决了就好！还得想想！！
--------------------编程问答--------------------
这想知道 这不用 UNION能解决吗....
--------------------编程问答--------------------
id int 主键是自增的话不用管createtime --------------------编程问答-------------------- 1.先汇总到一个表排序分页。
2.排序好每个表，排序好的有序队列，这复杂度为线性的，然后在归并排序 、分页，我想的方案，语句期待！ --------------------编程问答-------------------- 是啊 数据太多 等大牛
--------------------编程问答-------------------- 增加一个烈 变id（int），username（varchar），createtime（datatime）+Tname(char(1))
insert into newtable(select*from a-z)
这样就有了一个大表了
然后排序在出新表
insert into bytable(select * from newtable order by datetime desc)
触发器 更新bytable
每次查询就用bytable
我说的符合您的要求吗!
--------------------编程问答-------------------- 要不就是读内存 --------------------编程问答-------------------- 高高高高高手请出来吧！
--------------------编程问答--------------------
--------------------编程问答--------------------
围观
--------------------编程问答--------------------
聚集索引搞到createtime列上吧 --------------------编程问答-------------------- 期待MVP的出现，体现MVP价值的时候到了，拭目以待啊！ --------------------编程问答-------------------- --------------------编程问答-------------------- 小弟也不会优化，就照你的意思写出来，不知对不对，请指正：
--------------------编程问答-------------------- 坐等大牛 。。。 --------------------编程问答-------------------- --------------------编程问答-------------------- --------------------编程问答-------------------- 一个语句...........是一条？还是能用存储过程？ --------------------编程问答--------------------
select id，username，createtime ,Row_Nomber() over(order by createtime desc) as 'rn' from
(
select id，username，createtime from A
union all
select id，username，createtime from B
union all
select id，username，createtime from c
...
) as 'AllTable'
where (( AllTable.rn>=(@page)*20 )and(AllTable.rn<(@page+1)*20));
请问下 @page是个啥 内置变量？ --------------------编程问答-------------------- 有点难度！ --------------------编程问答-------------------- --------------------编程问答-------------------- 参数 --------------------编程问答-------------------- 1.有A-Z 26张表，表结构均一样，有id（int），username（varchar），createtime（datatime）三个健，数据存储时是随机向A-Z其中一张表存储，每张表的数据量均在100万以上
请构造一个sql语句实现：分页，每页20条，将26张表数据综合，以createtime倒序排列
-------------------------------------------------------------------------------------------
这种存储模式设计上面有问题
1.分表存储,是为了解决单表数据过大的问题.
将数据按照一定的规则,进行分表存储,并限制每张表的数据量.这里却将数据随机插入每张表中
问:这种随机插入,有必要吗?为什么要随机插入?
2.表名命名太随意化.
既然有CreateTime,应该按数据的时间粒度,对表名进行规范化存储.
对于这么大规模的数据,一定要按时间分片查询,对这么大规模的数据,一次性查,倒序,没意义,用户也不会这么看.
-------------------------------------------------
有意义的做法:表名按合适的时间粒度进行创建,根据时间,选择不同的表,进行数据聚合,减少因不确定查询条件带来的union开销.
这样做完之后,对数据倒序的要求,20条数据的查询,将会在最多两张表内完成,至于分页,当前表数据不够,按时间去查另外一张表里补数据就可以了.
需要增加的设计:需要增加一张表:存储表名与时间的对对应关系.和每张表存储的数据量大小
--------------------编程问答-------------------- --------------------编程问答-------------------- --------------------编程问答-------------------- --------------------编程问答-------------------- 个人思路如下，大概需要测试实用性：
1。分别取A-Z表 前20条 order by desc。 并加 'a' as TableName 自定义字段，以表示记录来自哪张表 组成临时数据集.
2。此临时数据集被简化为：20 * 26 = 520条记录。查询此临时数据信 前20条 order by desc. 为页数据。（因为有可能某表即包含前20条，或只包含零条，所以要每个表都取前20条以组成最大520条的临时记录集。）
3。循环查询此临时数据集每个表的最大一条记录的关键字段（或者id, 或者datetime字段）
准备放入条件1，作为下一分页提取数据用。即：改变提取条件, 取每张表的条件为 datediff(.....) 或者 top 20 * from ... id < xxx 的前20条记录以组成下一个临时数据集。
4。以此循环，直到分页至最后一页。 --------------------编程问答-------------------- 3。循环查询此临时数据集前20条记录中每个表的最大一条记录的关键字段，不包含，则不改变初始判断值。 --------------------编程问答--------------------
那就改成：
select id，username，createtime ,Row_Nomber() over(order by createtime desc) as 'rn' from
(
select top 20 id，username，createtime from A order by createtime desc
union all
select top 20 id，username，createtime from B order by createtime desc
union all
select top 20 id，username，createtime from C order by createtime desc
...
) as 'AllTable'
where (( AllTable.rn>=(@page)*20 )and(AllTable.rn<(@page+1)*20));
？？？ 结果也许要好就，但也不可避免 全部排序！ --------------------编程问答--------------------
你说的太对了，
这种表，绝对是设计出问题，如果是我自已来搞也是不会这样做的，
直接 另键一个表， 然后 查这A——Z 26个表， order by createtime desc
插入到另表中，那么我以后用时就不会要重新排列了！ --------------------编程问答-------------------- 这题大概是要考如何才能更高效组合SQL？对于是否有这实际26张表其实不是重点吧。A-Z也容易组合。
while(@num > 0)
begin
char(@num + 64) --> 65 = A 循环也能组合表名。
end --------------------编程问答-------------------- 1。确定一个必然超过20个数据的时间间隔
2.查出最大时间
3.按页面，及时间间隔，及上页最小id，下页最大id，进行展示。 --------------------编程问答-------------------- 可以多查几个时间间隔内的进行处理，避免数据不足 --------------------编程问答-------------------- --------------------编程问答-------------------- --------------------编程问答-------------------- --------------------编程问答-------------------- 用LINQ是不是能好点。 --------------------编程问答-------------------- 数据库，要我的命啊 --------------------编程问答-------------------- --------------------编程问答-------------------- --------------------编程问答-------------------- --------------------编程问答-------------------- 排序是扫整张表，无论使用什么语言，还是什么方法，这个操作都注定是慢的。
他压根不懂，或者不想招聘你。 --------------------编程问答-------------------- 对于是否有这实际26张表其实不是重点吧。A-Z也容易组合。
--------------------编程问答-------------------- 既然是面试题，就不要考虑设计的问题。记得小学的时候有个题目，说一个池子上面放水，下面漏水（漏水速递小于放水速递），问池子什么时候灌满，你能说这个池子设计有问题吗？
这题除了把数据合到一起再排序输出，我也想不出什么好办法。 --------------------编程问答-------------------- --------------------编程问答-------------------- 每个表取20条，合并，然后排序 输出？ --------------------编程问答--------------------
可以建索引吧! --------------------编程问答-------------------- 期待答案！ --------------------编程问答-------------------- --------------------编程问答-------------------- 能想到的只能是在creattime上面建索引，再利用缓冲显示分页，这样效果可能好点 --------------------编程问答-------------------- 不然如果一次全取出来那就悲剧鸟
再加个多线程 --------------------编程问答-------------------- 有点意思 期待大牛给出优雅解决方案 --------------------编程问答-------------------- 有点意思解决方案 --------------------编程问答-------------------- 这道题，不管如何写，都不可避免 全部排序！ --------------------编程问答-------------------- 表示同情，这题想不出来 --------------------编程问答-------------------- --------------------编程问答-------------------- 同意35楼观点,既然要优化查询时间,那么就增加索引摘要信息表,这个表应该只生成一次就够了,必要的话,可以把摘要信息读到内存来检索.
--------------------编程问答-------------------- 这不就是跟oracle的散列分区表原理类似么，可以参考下oracle 的处理方式。 --------------------编程问答-------------------- 可以 createtime字段上创建一个逆向索引，然后取出每个表的前20条，将这26张表通过union all连接起来（不要使用union 他会去比对相同的数据，效率比union all块），然后对这5200条数据进行排序取出前20条
--------------------编程问答-------------------- 我觉得这表的ID反而是关键
如第一条数据插入A表则 ID为1
第二条数据插入到B表 ID为2
createtime 倒序取时 查询条件 id 在26万到25万9980之间
这就比较简单了 效率也比createtime高
--------------------编程问答--------------------
楼主的题说了，这个表的数据是随机插入的，没有规律的， 所以，不管如何，都要排序：
就排序来说，39楼：xie_yanke 的方法最好，速度最快，但也要很长时间！
你说，id 快，其实，一样的， 只要建了 索引，不管用哪个字段，结果都一样，除非，插入时不是随机的。
--------------------编程问答-------------------- 根据CreateTime 建索引，索引就已经实现了排序.SQL SERVER 2005以上的可以对视图建立索引，视图UNION 所有表记录，再对视图的CreateTime建立索引，直接分页查询。试试吧。需要先做数据。 --------------------编程问答-------------------- 取第二页的时候咋办？没有办法保证第二页的20条也在这5200条记录里 --------------------编程问答-------------------- 数据随机的插入到数据表，这TMD的谁设计这样的功能啊。 --------------------编程问答-------------------- --------------------编程问答-------------------- --------------------编程问答-------------------- 如果是用Sqlserver2008的话，在上面有个函数按一定的规定进行等级划分...,这样就能够很简单做分页的效果 --------------------编程问答--------------------
--------------------编程问答--------------------
还有在每一张表上有聚合函数的限制，一般是用某种规律，例如年限，或者字母的开头，通过选择可以确定使用那几张表进行搜索...
--------------------编程问答--------------------
这么设计的有嘛好处？
--------------------编程问答--------------------
--------------------编程问答--------------------
一般要求排序都不会要求全排序输出的，那样的输出没意义（如果真是这样的话，只能所有表先做排序然后归并），一般是要求只输出一小部分数据，这样的排序就看要求的准确率了，例如要求到第10页的准确率为95%，然后是每个表只取前20条（左右）的数据，然后归并。这个有点类似于搜索引擎中的匹配查询输出。
--------------------编程问答--------------------
不知道怎么弄啊
--------------------编程问答--------------------
不知道耶
--------------------编程问答--------------------
create table A
(
id int identity(1,1) primary key,
username varchar(36),
createTime datetime
)
;
create table B
(
id int identity(1,1) primary key,
username varchar(36),
createTime datetime
)
create table C
(
id int identity(1,1) primary key,
username varchar(36),
createTime datetime
)
select * from B;
truncate table A;
declare @i int ;
set @i=100;
while(@i >0)
begin
insert into A
select CAST(@i as varchar),SYSDATETIME();
insert into B
select CAST(@i as varchar),SYSDATETIME();
insert into C
select CAST(@i as varchar),SYSDATETIME();
set @i-=1;
end
;
select * from A;
select * from B;
select * from C;
alter PROCEDURE [dbo].uspGetEmployees1
@PageIndex int,
@PageSize int
AS
begin
select * from (
select rowid=ROW_NUMBER() over(order by abc.createTime desc ), * from (
select * from (select top (@PageSize) * from dbo.A order by a.createTime desc) AA union all
select * from (select top (@PageSize) * from dbo.B order by b.createTime desc) BB union all
select * from (select top (@PageSize) * from dbo.C order by c.createTime desc) CC )ABC
)ABCRow
where rowid>(@PageIndex-1)*(@PageSize) and rowid<=(@PageIndex)*(@PageSize);
return ;
end
exec uspGetEmployees1 2,10
rowid id username createTime
-------------------- ----------- ------------------------------------ -----------------------
11 97 4 2012-03-02 17:17:08.507
12 95 6 2012-03-02 17:17:08.507
13 96 5 2012-03-02 17:17:08.507
14 97 4 2012-03-02 17:17:08.507
15 95 6 2012-03-02 17:17:08.507
16 96 5 2012-03-02 17:17:08.507
17 97 4 2012-03-02 17:17:08.507
18 94 7 2012-03-02 17:17:08.503
19 94 7 2012-03-02 17:17:08.503
20 94 7 2012-03-02 17:17:08.503
(10 行受影响)
--------------------编程问答-------------------- 个人认为，既然表结构有id且类型为INT,按照通用的设计原则来说，一般ID都是主键，且是自增的，在满足这个通用设计原则下。每个表
select * from (select a.*,row_number() over(order by a.id desc) rn from a where a.created_date>minDate )aa where aa.rn<=20
再26张表union排序取出20条数据集A。
取下一页时，只需要从A中取出created_date最大的那一条数据作为参数minDate就可以了。
当然，如果要考虑created_date相同的情况就需要更加详细考虑了。 --------------------编程问答-------------------- 这怎么越看越迷茫呢 --------------------编程问答-------------------- 思路1
每张表取TOP 10,然后UNION起来再取TOP 20
第二页则每张表取TOP 20,
第三页每张表取TOP 30
假设分了1万页,在中点的那一页效率会最底
因为超过了5000页的时候可以用正序了...
思路2
(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
一个查询的执行顺序…那个ORDER BY 几乎在最后了…
如果能提前WHERE然后再ORDER BY 效率会提高不少…
假设已知每天新增约100万条数据
那么可以大致预测出第N页的数据大概出现在几点钟,范围可以略微大一些,那也踢除了大量的无关数据
大致思路还是先WHERE后排序
--------------------编程问答--------------------
索引方向的优化是必不可少的不说了
--------------------编程问答--------------------
--------------------编程问答--------------------
create table D(
id int,
username varchar(36),
createTime date
);
begin
for i in 1..1000000 loop
insert into D values ( i , 'USERNAME'|| i , (select get_date() from dual) );
end loop;
end;
CREATE INDEX INDEX_A_CREATETIME ON A( CREATETIME )
SELECT ID , USERNAME , CREATETIME FROM ( SELECT ID , USERNAME , CREATETIME FROM A WHERE ROWNUM <20 AND CREATETIME < SYSDATE ) ORDER BY CREATETIME DESC
耗时0.031毫秒 UNION 绝对没有问题 测试环境 oracle --------------------编程问答-------------------- 还有一个思路,假设该表的ID自增长且连续
即,最大的ID即是该表的记录总数,例如最大ID是1000000,那么该表就存在1000000条数据,ID自增连续
先看第一页怎么取
第一页中,先说一张表怎么取,例如A表
先计算表总记录数
SELECT @Count = COUNT(1) FROM A
SELECT * FROM A WHERE ID BETWEEN @Count - 20 AND @Count
B表,C表,D表...Z表类似,都这样取
然后将以上数据UNION起来进行CREATETIME倒序
取第一页数据的关键在于ID自增连续
第一页数据出来后,呈现在页面上,假设A表中取了ID为97,98,99,100四条数据(假设A表总记录数为100)
B表中取了0条数据,C表中取了ID为145,146两条数据(此处假设C表总记录数为146)
再看第二页怎么取
在取第二页数据时,需要使用已经呈现在页面上的数据,把每张表的最小ID拿出来,构造一个SQL参数
这个SQL参数可以是VARCHAR(2000),以逗号分隔,每张表取最小ID,没有传-1,编写好就行
按照A-Z的顺序组装成一个字符串,大概类似下边这样
'97,-1,145,....'
把这个参数传给SQL SERVER再进行分隔,约定好第一个数字即A表的最小ID,第三个数字即C表的最小ID
-1表示没有数据在页面上呈现,继续取B表记录总数
其余页面与第二页类似
关键点在于客户端有能力构造这样一个参数
总结下两点
1,ID自增连续
2,客户端有能力将已经呈现的数据构造成需要的参数
这个思路的好处是,避免了TOP ORDER BY,效率应该是比较不错滴
--------------------编程问答-------------------- 没有很多的思路，坐等牛人…… --------------------编程问答--------------------
考虑解决方案的时候想过，只不过，，，记录很可能会不连续。。如果有删除操作，这省劲的方法，立马崩溃。 --------------------编程问答--------------------
赞。 --------------------编程问答--------------------
ID不连续就只能ORDER BY了...
1,CREATETIME建聚集索引,倒序,倒序是第一页快,最后一页慢,正序刚好相反,常规考虑,倒序更合适些
2,每次传最后一条数据的CREATETIME,每张表取前20倒序
SELECT TOP 10 * FROM A-Z WHERE CREATETIME < @CREATETIME
效率肯定比上边那个低些,但也是个思路...
不过这个也有一个限制,,,即某一页的第一条数据的CREATETIME必须小于最后一条数据的CREATETIME
哪怕一毫秒的差距也成,不然就分不了页了,如果连毫秒都完全一样的话,那每次传的CREATETIME参数都是一样的,取的数据也都是一样的 --------------------编程问答-------------------- 楼主的说法是分页，每页20条，将26张表数据综合，以createtime倒序排列。
也就是说在现实第一页的时候我们不必全表扫描，只要找出每个表的createtime倒序排列的前20个，在union起来就可以实现第一张表。
但是效率有问题，有重复。 --------------------编程问答--------------------
可以order by 前 where id < xxx 就不必在乎时间是否相同。初始值这个xxx 可以取某表的max id. 以后循环时，将得到符合条件的此表的max id， 代入 xxx 即可。
补充：.NET技术 , ASP.NET
