数字类经典SQL编程问题:数字辅助表
上一篇 /
下一篇 2012-11-30 10:22:32
/ 个人分类:数据库
数字辅助表是一个只包含从1到N的N个整数的简单表,N通常很大。因为数字辅助表是一个非常强大的工具,可能经常需要在解决方案中用到它,笔者建议创建一个持久的数字辅助表,并根据需要填充一定数据量的值。51Testing软件测试网bID&F5y-| 实际上如何填充数字辅助表无关紧要,因为只需要运行这个过程一次。不过还可以对填充语句进行优化。一般的SQL编程人员会想到用如下方法来生成1~N的数。
D[.@9pV VG#\ \b0
s5el/J/Y-~L8LM'r0CREATE TABLE Nums(51Testing软件测试网;vpmw Sf7Hi]
yy~ a INT UNSIGNED NOT NULL PRIMARY KEY 'Q$p2V*@QOIS0)ENGINE=InnoDB; gUBQP
[*yE0owi o3RT0CREATE PROCEDURE pCreateNums (cnt INT UNSIGNED) gF8L`UWBx0BEGIN f)WE[,MR)ihP*Y"X0DECLARE s INT UNSIGNED DEFAULT 1; Dth]5{(JG0TRUNCATE TABLE Nums; W1E$]C2{;Hl&h'vm0WHILE s <= cnt DO51Testing软件测试网zHjX6OS-J BEGIN U
yqB!N8O0INSERT INTO Nums SELECT s; Ybm?xmMXJ6u0SET s = s+1;51Testing软件测试网Zl^:C4g KZ END;51Testing软件测试网/_(['n&p1W r^ END WHILE;51Testing软件测试网6fx.\:E1SA0zJ$t8`$U END;51Testing软件测试网+{4Wcgg8^Gu |
51Testing软件测试网cE kuWRB 这个方法没有任何的问题,只是效率不高。例如要插入100 000行的数据,在笔者的四核苹果电脑上至少需要1分钟。
l]cX'O?z(sM1S0mysql> CALL pCreateNums (100000);51Testing软件测试网1P5I_b4T0_c Query OK, 1 row affected (1 min 11.56 sec) |
51Testing软件测试网B"G3K2v2w.Y 这个方法的开销主要在于INSERT语句被执行了100 000次。我们可以通过下面这个方法来创建数字辅助表。
-Z|\2p9\0CREATE PROCEDURE pFastCreateNums (cnt INT UNSIGNED)51Testing软件测试网f-jl0Urp5W BEGIN ^)fR(g'H0DECLARE s INT UNSIGNED DEFAULT 1;51Testing软件测试网,dJ8\%I%k
Oj
d8R$E TRUNCATE TABLE Nums; :| eM1cb{C*D0INSERT INTO Nums SELECT s;51Testing软件测试网/n'`R(P#M
q4r WHILE s*2 <= cnt DO 9] F`.Fgv~9I0BEGIN51Testing软件测试网@R1Z?LL @ INSERT INTO Nums SELECT a+s FROM Nums;51Testing软件测试网cX'P3N.zOn C;l SET s = s*2;51Testing软件测试网$O;Uq.r7y3BZ{x END;51Testing软件测试网`Xy:Gm,[ai4v2f END WHILE;51Testing软件测试网MQ*z g(n END; |
&C'H*l@$E
d?0
在这个存储过程中,变量s保存插入该表的行数。该过程先把1插入数字辅助表,然后当s*2<=cnt成立时执行循环。在每次迭代中,该过程把数字
辅助表当前所有行的值加上s后再插入数字辅助表中,即先插入{1},然后是{2},{3,4},{5,6,7,8},
{9,10,11,12,13,14,15,16},以此类推。因此这个存储过程的执行时间非常之快。要插入200 000行数据,情况如下:
L,ne0_R)q;H.\$fO+e0mysql> CALL pFastCreateNums (200000);51Testing软件测试网f5k#?@&?n Query OK, 65536 rows affected (1.00 sec) |
51Testing软件测试网f&[0S&D9YGuu
可以看到执行时间缩短到了1秒钟,性能提高了70多倍。究其原因,是因为实际执行INSERT的次数少了。这里我们是按照2的指数次进行插入的,实际只
执行了17次插入操作。这个解决方案的唯一缺点是,数字辅助表是按照2的指数次进行插入的,因此上述实际的插入行数是131 072,而不是200
000行。查询一下刚才插入的数据,结果如下:
$ST8d}6`C0mysql> SELECT COUNT(1) FROM Nums;51Testing软件测试网7sKQ&_7S +----------+51Testing软件测试网k5q$t0A
PIX1}Uh | count(1) | /lAc&E*`|k's0+----------+51Testing软件测试网 p
c4x7TS(}qRP | 131072 | E"Q#a E*a&^xu]0+----------+51Testing软件测试网TFB:Ua*ot 1 row in set (0.03 sec) |
51Testing软件测试网"Q r*C"[DItV4| 不过这不是一个很大的问题,因为我们可以在取出数据时使用<=来截取指定的行数,如:
v
x$W\3M*q0SELECT * FROM Nums WHERE a <= 100000 |
|%bW\
f^0 有了这张辅助表,用户可以通过它来辅助很多其他应用。例如,在数据仓库中,通常需要生成某个时间范围内的时间维度表,这时使用数字辅助表会非常简单和快捷,示例如下:
-YkMM\Rv${0CREATE PROCEDURE pCreateDimTime(start DATE, end DATE)51Testing软件测试网f
E(M|i _,k(C_
BEGIN51Testing软件测试网j
yc.be-U[i
SELECT DATE_ADD(start,INTERVAL a-1 DAY)
D(u}F*q/Uwh;`
k0FROM Nums WHERE a<=DATEDIFF(end,start)+1;51Testing软件测试网W(~h({3R#?
END;
收藏
举报
TAG: