关于数字的经典SQL编程问题:数字辅助表-技术开发专区

关于数字的经典SQL编程问题:数字辅助表

作者：姜承尧编辑：王玉圆 2012-12-25 00:05 IT168网站原创

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

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

CREATE TABLE Nums(
a INT UNSIGNED NOT NULL PRIMARY KEY
)ENGINE=InnoDB;

CREATE PROCEDURE pCreateNums (cnt INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
TRUNCATE TABLE Nums;
WHILE s <= cnt DO
BEGIN
INSERT INTO Nums SELECT s;
SET s = s+1;
END;
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;

　　在这个存储过程中，变量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

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

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;

　　作者简介

　　姜承尧（DavidJiang），《MySQL技术内幕：SQL编程》、《MySQL技术内幕：InnoDB存储引擎》作者，资深MySQL数据库专家，MySQL开源分支版本InnoSQL的创始人，独立数据库咨询顾问。不仅擅长于数据库的管理和维护，还擅长于数据库的开发，同时一直致力于MySQL数据库底层实现原理的研究和探索，对高性能数据库和数据仓库也有深刻而独到的理解。目前就职于网易研究院，担任后台技术中心技术经理一职，从事MySQL数据库底层以及云的相关的开发工作。

关注我们