MySQL优化之——高效插入0-N这N个整数

插入0-N这N个整数，

首先，创建一个表和一个存储过程：

#创建一个存数据的表
CREATE TABLE tb_nums (a INT NOT NULL PRIMARY KEY) ENGINE = INNODB;

#创建存储过程
CREATE PROCEDURE pCreateNums (x INT UNSIGNED)
BEGIN

DECLARE s INT UNSIGNED DEFAULT (1);

TRUNCATE TABLE tb_nums;
#通过循环迭代每次+1插入到数据库中
WHILE s <= x DO

BEGIN
    INSERT INTO tb_nums SELECT
        s;

SET s = s + 1;

END;

END
WHILE;

END;

这个方法肯定是可以插入的，但效率不高，如图，当插入100000条数据时，大约花费了2分钟或者更多。

---

创建结果为：

这个方法的开销主要在于Insert语句执行了100000次。

#创建存储过程
CREATE PROCEDURE pCreateNumsTwo (x INT UNSIGNED)
BEGIN

DECLARE s INT UNSIGNED DEFAULT 1;

TRUNCATE TABLE tb_nums;

INSERT INTO tb_nums SELECT
    s;  //先把s=1插进去


WHILE s * 2 <= x DO

BEGIN
    INSERT INTO tb_nums SELECT
        a + s
    FROM
        tb_nums;


SET s = s * 2;


END;


END
WHILE;


END;

---

在这个存储过程中，变量s保存插入该表的行数。该过程先把1插入表中，然后当s*2 < x成立时循环，每次迭代中，该过程把表当前行的值加上s后在插入，即先插入{1},然后是{2},然后是{3,4},{5,6,7,8},{9,10,11,12,13,14,15,16},因此这个存储过程更快，200000行数据也只要1秒钟。真正的原因就是Insert次数少了。详细步骤如下：

1. 首先s=1,2*s<200000,此时数据库中的数据为{1}
   执行：INSERT INTO tb_nums SELECT a + 1 ，a是数据库中的字段，就插入了1+1
   s=s*2=2;
2. s=2,2*s<200000,此时数据库中的数据为{1，2}
   执行：INSERT INTO tb_nums SELECT a + 2，a是数据库中的字段，就插入了1+2，2+2
   s=s*2=4;
3. s=4,2*s<200000,此时数据库中的数据为{1，2，3，4}
   执行：INSERT INTO tb_nums SELECT a + 4，a是数据库中的字段，就插入了1+4，2+4，3+4，4+4
   s=s*2=8;
4. s=8,2*s<200000,此时数据库中的数据为{1，2，3，4，5，6，7，8}
   ……

---

这里是按照2的指数次方进行插入的，实际上只执行了17次插入操作。不过缺点是这个只插入了131072条数据（因为2^17=131072 2^18>200000,因为循环判断的条件是s*2<200000，所以18的时候不满足，于是只插入了131072 条数据）。

因此，如果我们需要100000内的数据在查询时加上select * from tb_nums where a<= 100000
或者直接把>100000的删掉也很快速 delete from tb_nums where a>100000