数字类经典SQL编程问题：数字辅助表

上一篇 / 下一篇 2012-11-30 10:22:32 / 个人分类：数据库

　数字辅助表是一个只包含从1到N的N个整数的简单表，N通常很大。因为数字辅助表是一个非常强大的工具，可能经常需要在解决方案中用到它，笔者建议创建一个持久的数字辅助表，并根据需要填充一定数据量的值。

　　实际上如何填充数字辅助表无关紧要，因为只需要运行这个过程一次。不过还可以对填充语句进行优化。一般的SQL编程人员会想到用如下方法来生成1～N的数。

s5el/J/Y-~L8LM'r0CREATE TABLE Nums(
a INT UNSIGNED NOT NULL PRIMARY KEY
'Q$p2V*@QOIS0)ENGINE=InnoDB;

owio3RT0CREATE 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;
END;

　　这个方法没有任何的问题，只是效率不高。例如要插入100 000行的数据，在笔者的四核苹果电脑上至少需要1分钟。

mysql> CALL pCreateNums (100000);
Query OK, 1 row affected (1 min 11.56 sec)

　　这个方法的开销主要在于INSERT语句被执行了100 000次。我们可以通过下面这个方法来创建数字辅助表。

CREATE PROCEDURE pFastCreateNums (cnt INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
TRUNCATE TABLE Nums;
INSERT INTO Nums SELECT s;
WHILE s*2 <= cnt DO
BEGIN
INSERT INTO Nums SELECT a+s FROM Nums;
SET s = s*2;
END;
END WHILE;
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行数据，情况如下：

mysql> CALL pFastCreateNums (200000);
Query OK, 65536 rows affected (1.00 sec)

　　可以看到执行时间缩短到了1秒钟，性能提高了70多倍。究其原因，是因为实际执行INSERT的次数少了。这里我们是按照2的指数次进行插入的，实际只执行了17次插入操作。这个解决方案的唯一缺点是，数字辅助表是按照2的指数次进行插入的，因此上述实际的插入行数是131 072，而不是200 000行。查询一下刚才插入的数据，结果如下：

mysql> SELECT COUNT(1) FROM Nums;
+----------+
| count(1) |
+----------+
| 131072 |
+----------+
1 row in set (0.03 sec)

　　不过这不是一个很大的问题，因为我们可以在取出数据时使用<=来截取指定的行数，如：

SELECT * FROM Nums WHERE a <= 100000

|%bW\ f^0　　有了这张辅助表，用户可以通过它来辅助很多其他应用。例如，在数据仓库中，通常需要生成某个时间范围内的时间维度表，这时使用数字辅助表会非常简单和快捷，示例如下：

CREATE PROCEDURE pCreateDimTime(start DATE, end DATE)
BEGIN
SELECT DATE_ADD(start,INTERVAL a-1 DAY)
FROM Nums WHERE a<=DATEDIFF(end,start)+1;
END;

收藏举报

TAG:

查看全部评论