数字类经典SQL编程问题:数字辅助表
上一篇 /
下一篇 2012-11-30 10:22:32
/ 个人分类:数据库
数字辅助表是一个只包含从1到N的N个整数的简单表,N通常很大。因为数字辅助表是一个非常强大的工具,可能经常需要在解决方案中用到它,笔者建议创建一个持久的数字辅助表,并根据需要填充一定数据量的值。51Testing软件测试网}v5F)AB+Hk 实际上如何填充数字辅助表无关紧要,因为只需要运行这个过程一次。不过还可以对填充语句进行优化。一般的SQL编程人员会想到用如下方法来生成1~N的数。
b[8URoJMs0+M8iS2JP"Csi0CREATE TABLE Nums( v
uo-G+o,s|Q0 a INT UNSIGNED NOT NULL PRIMARY KEY51Testing软件测试网m
SR fC.o;gE )ENGINE=InnoDB; *h T)kRKW9tB09y4R7Ns'}'g.V0CREATE PROCEDURE pCreateNums (cnt INT UNSIGNED)51Testing软件测试网h
y#F0I+YC x {R BEGIN #gz'p4]E0DECLARE s INT UNSIGNED DEFAULT 1;51Testing软件测试网
jg9X7K(w TRUNCATE TABLE Nums;51Testing软件测试网
tq@-@s)W.R!O;?`&f WHILE s <= cnt DO51Testing软件测试网a]Fa?+Y2we BEGIN :HF_0v Y'J
m)^CN0INSERT INTO Nums SELECT s; /}S/eb!Qj1])c9Y?0SET s = s+1; "\j_!\g?0END;51Testing软件测试网 a
N'g\9PW~ END WHILE;51Testing软件测试网
JT#[T za9l END;51Testing软件测试网j*U;u$K
LfQD |
RF`Gvm&?0 这个方法没有任何的问题,只是效率不高。例如要插入100 000行的数据,在笔者的四核苹果电脑上至少需要1分钟。
}(Y1yj4u,zEa0mysql> CALL pCreateNums (100000); 1W+s#|bxGTK0Query OK, 1 row affected (1 min 11.56 sec) |
BR;F)i~k
n0 这个方法的开销主要在于INSERT语句被执行了100 000次。我们可以通过下面这个方法来创建数字辅助表。51Testing软件测试网3j#x3IJt0m#KN!g
CREATE PROCEDURE pFastCreateNums (cnt INT UNSIGNED) ^5Z)Lt g7J0BEGIN }[]N~
I0DECLARE s INT UNSIGNED DEFAULT 1;51Testing软件测试网d-E&s-N+L{:_/t1j TRUNCATE TABLE Nums;51Testing软件测试网g0U%`H'C[njx INSERT INTO Nums SELECT s;51Testing软件测试网8kt(D9NF;D WHILE s*2 <= cnt DO 9O*{ to:N0BEGIN R:Too
Y JZA\vu0INSERT INTO Nums SELECT a+s FROM Nums; [o!Q,pm(Y0SET s = s*2;51Testing软件测试网)X!s:\BL'u#\:rS END; U$Xeo"k0END WHILE;51Testing软件测试网.i'GSrU
? t END; |
51Testing软件测试网$X.j%TG({F I
在这个存储过程中,变量s保存插入该表的行数。该过程先把1插入数字辅助表,然后当s*2<=cnt成立时执行循环。在每次迭代中,该过程把数字
辅助表当前所有行的值加上s后再插入数字辅助表中,即先插入{1},然后是{2},{3,4},{5,6,7,8},
{9,10,11,12,13,14,15,16},以此类推。因此这个存储过程的执行时间非常之快。要插入200 000行数据,情况如下:
0gC&lT3XnyX T'U0mysql> CALL pFastCreateNums (200000);51Testing软件测试网
y}yVq Query OK, 65536 rows affected (1.00 sec) |
51Testing软件测试网#_R3k&c#^@
可以看到执行时间缩短到了1秒钟,性能提高了70多倍。究其原因,是因为实际执行INSERT的次数少了。这里我们是按照2的指数次进行插入的,实际只
执行了17次插入操作。这个解决方案的唯一缺点是,数字辅助表是按照2的指数次进行插入的,因此上述实际的插入行数是131 072,而不是200
000行。查询一下刚才插入的数据,结果如下:51Testing软件测试网"f5u/hG#luz.@
mysql> SELECT COUNT(1) FROM Nums; ,N
rF(c5|#_&H0+----------+ '}l#|,e:\s0| count(1) | "\G!URh B!t*T9c6g0+----------+ 2` YWDH0| 131072 |51Testing软件测试网7M.L| k!x5G1VN +----------+51Testing软件测试网%Y'A1xlj+F 1 row in set (0.03 sec) |
xCWj!{ GK0 不过这不是一个很大的问题,因为我们可以在取出数据时使用<=来截取指定的行数,如:
*W@0d
p/N0SELECT * FROM Nums WHERE a <= 100000 |
(y#G&seU"v&~1_0 有了这张辅助表,用户可以通过它来辅助很多其他应用。例如,在数据仓库中,通常需要生成某个时间范围内的时间维度表,这时使用数字辅助表会非常简单和快捷,示例如下:51Testing软件测试网J+C.};c,b(}f1jpA
CREATE PROCEDURE pCreateDimTime(start DATE, end DATE)
I4fj]o;Y0BEGIN51Testing软件测试网wQ6G&i)E'YN*`0X1^
SELECT DATE_ADD(start,INTERVAL a-1 DAY)
jxpseZX^V:dN0FROM Nums WHERE a<=DATEDIFF(end,start)+1;
e!fw
rE-Y!JO0END;
收藏
举报
TAG: