快速生成数字辅助表

        数字辅助表只有一个整数列,包含从1到N个整数序列值,N通常很大。对MySQL来讲,数字辅助表是一个强大的工具,编写SQL语句时经常用数据表与数字辅助表做笛卡尔积来创建额外的行。建议创建一个持久的数据辅助表,并根据需要填充一定数据量的值。        实际上如何填充数字辅助表无关紧要,因为只需要运行这个过程一次,不过还是可以对此过程进行优化。假设需要为如下数字辅助表生成数据:

create table nums (a bigint unsigned not null primary key) engine=innodb;

方案一:平凡低效

drop procedure if exists pcreatenums;
delimiter //
create procedure pcreatenums(cnt bigint)
begin
    declare s int default 1;
    set session autocommit=0;
    while s<=cnt do
        insert into nums values(s);
        set s=s+1;
    end while;
    commit;
end;
//

        这个存储过程没很简单,就是一个循环,每次插入一条数据,以生成的数据行数作为循环次数。在我的环境中执行这个过程生成1000000行需要执行接近1分24秒。效率不高的原因在于insert语句被执行了1000000次。
 

mysql> call pcreatenums(1000000);
Query OK, 0 rows affected (1 min 24.39 sec)

方法二:高效迭代

drop procedure if exists pcreatenums;
delimiter //
create procedure pcreatenums(cnt int)
begin
    declare s int default 1;
    set session autocommit=0;
    insert into nums select s;
    while s<=cnt do
        insert into nums select a+s from nums where a+s <=cnt;
        set s=s*2;
    end while;
    commit;
end;
//

        这次执行只用了不到不到17秒。

mysql> call pcreatenums(1000000);
Query OK, 0 rows affected (16.53 sec)

        在这个存储过程中,变量 s 保存插入nums表的行数。循环开始前先插入 1 条数据,然后当 s 小于等于所要生成的数据行数时执行循环。在每次迭代中,该过程把nums表当前所有行的值加上 s 后再插nums表中。这样每次循环插入的行数以2的幂次方递增,insert语句只被执行了21次,其中还包括作为种子数据的第一次插入。因此这个过程的执行速度很快。

方法三:一次生成

set session cte_max_recursion_depth=1000000;
insert into nums 
with recursive temp (n) as (select 1 union all select n+1 from temp where n < 1000000) select n from temp;

        这种方法利用MySQL 8 提供的CTE(Common Table Expressions)功能,用递归一次性生成所有数据,只需要不到13秒,性能进一步提高了四分之一。

mysql> insert into nums 
    -> with recursive temp (n) as (select 1 union all select n+1 from temp where n < 1000000) select n from temp;
Query OK, 1000000 rows affected (12.28 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

        CTE可以实现类似Oracle中connect by的递归功能,但功能更强大,能够解决非常复杂的查询问题。https://dev.mysql.com/doc/refman/8.0/en/with.html是MySQL官方文档对CTE的说明。

发布了360 篇原创文章 · 获赞 570 · 访问量 210万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览