MySQL：有效地填充存储过程中的表

Question

我正在 MySQL 服务器上测试性能，并用超过2亿条记录填充表。存储过程生成大的SQL字符串非常慢。任何帮助或评论都非常欢迎。

系统信息：

• 数据库： MySQL 5.6.10 InnoDB数据库（测试）。
• 处理器： AMD Phenom II 1090T X6内核，每个内核3910Mhz。
• 内存： 16GB DDR3 1600Mhz CL8。
• HD： SSD中的Windows 7 64位SP1，SSD中安装了mySQL，日志记录在机械硬盘中。

存储过程使用所有要插入表中的值创建一个INSERT sql查询。

DELIMITER $$
USE `test`$$

DROP PROCEDURE IF EXISTS `inputRowsNoRandom`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `inputRowsNoRandom`(IN NumRows BIGINT)
BEGIN
    /* BUILD INSERT SENTENCE WITH A LOS OF ROWS TO INSERT */
    DECLARE i BIGINT;
    DECLARE nMax BIGINT;
    DECLARE squery LONGTEXT;
    DECLARE svalues LONGTEXT;

    SET i = 1;
    SET nMax = NumRows + 1;
    SET squery = 'INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, DATE) VALUES ';
    SET svalues = '("1", "a1", 100, 1, 500000, "2013-06-14 12:40:45"),';

    WHILE i < nMax DO
        SET squery = CONCAT(squery, svalues);
        SET i = i + 1;
    END WHILE;

    /*SELECT squery;*/
    SET squery = LEFT(squery, CHAR_LENGTH(squery) - 1);
    SET squery = CONCAT(squery, ";");
    SELECT squery;

    /* EXECUTE INSERT SENTENCE */
    /*START TRANSACTION;*/
    /*PREPARE stmt FROM squery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    */

    /*COMMIT;*/
END$$
DELIMITER ;

结果：

1. 串联20000个字符串大约需要45秒的时间：

CALL test.inputRowsNoRandom（20000）;

1. 串联100000个字符串大约需要+5/12分钟O_O：

CALL test.inputRowsNoRandom（100000）;

结果（按持续时间排序）-陈述时间（总和），以秒为单位||
释放项的 百分比 0.00005
50.00000起始0.00002 20.00000
执行0.00001 10.00000
init 0.00001 10.00000
清理0.00001 10.00000
总计0.00010 100.00000

由于执行查询
变量值说明而
导致的 状态变量的变化 Bytes_received从客户端发送到服务器的21个字节Bytes_sent从服务器
发送的97个字节到客户端
Com_select 1已执行的SELECT语句
数问题1服务器执行的语句数

测试：
我已经从12到64个线程不同的MySQL配置测试，和关闭设置高速缓存，移动日志到另一个硬件磁盘…
使用文本，INT ..还测试

其它信息：

• 性能链接：通用和多核，配置，优化IO，Debiancores，最佳配置，配置48GB内存 ..
• 对SQL查询进行性能分析：如何分析查询，检查查询中可能的瓶颈

问题：

• 代码有问题吗？如果我发送100000个字符串以构建最终的SQL字符串，则结果 SELECT squery; 为NULL字符串。发生了什么？（错误一定存在，但我看不到）。
• 我可以通过任何方式改进代码以加快速度吗？
• 我已经阅读了“存储过程”中的某些操作，可能真的很慢，我应该在C / Java / PHP中生成文件，然后将其发送给mysql吗？

mysql -u mysqluser -p数据库名称<数字.sql

• MySQL似乎只对一个SQL查询使用一个内核，nginx或其他数据库系统是否会使用：多线程DB，Cassandra，Redis和MongoDB ..）通过存储过程实现更好的性能，并且对一个查询使用多个CPU？（因为我的单个查询只使用了大约150个线程的总CPU的20％）。

更新：

• 填写表格的有效方式，请在下面检查术语答案。
• 存储过程，现代RDBMS或内联查询的性能。

Answer 1

不要在RDBMS中特别使用这种规模的循环。

尝试使用查询快速填充1m行的表

INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, date)
SELECT 1, 'a1', 100, 1, 500000, '2013-06-14 12:40:45'
  FROM
(
select a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + f.N * 100000 + 1 N
from (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) c
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) e
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) f
) t

我的机器（MacBook Pro 16GB RAM，2.6Ghz Intel Core i7）花了大约8秒的时间才能完成

查询正常，受影响的1000000行（7.63秒）
记录：1000000重复项：0警告：0

UPDATE1 现在使用预备语句的存储过程版本

DELIMITER $$
CREATE PROCEDURE `inputRowsNoRandom`(IN NumRows INT)
BEGIN
    DECLARE i INT DEFAULT 0;

    PREPARE stmt 
       FROM 'INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, date)
             VALUES(?, ?, ?, ?, ?, ?)';
    SET @v1 = 1, @v2 = 'a1', @v3 = 100, @v4 = 1, @v5 = 500000, @v6 = '2013-06-14 12:40:45';

    WHILE i < NumRows DO
        EXECUTE stmt USING @v1, @v2, @v3, @v4, @v5, @v6;
        SET i = i + 1;
    END WHILE;

    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

在约3分钟内完成：

mysql> CALL inputRowsNoRandom（1000000）;
查询正常，受影响的0行（2分钟51.57秒）

感觉差异8秒vs 3分钟

UPDATE2 为了加快速度，我们可以显式使用事务并批量提交插入。因此，这里是SP的改进版本。

DELIMITER $$
CREATE PROCEDURE inputRowsNoRandom1(IN NumRows BIGINT, IN BatchSize INT)
BEGIN
    DECLARE i INT DEFAULT 0;

    PREPARE stmt 
       FROM 'INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, date)
             VALUES(?, ?, ?, ?, ?, ?)';
    SET @v1 = 1, @v2 = 'a1', @v3 = 100, @v4 = 1, @v5 = 500000, @v6 = '2013-06-14 12:40:45';

    START TRANSACTION;
    WHILE i < NumRows DO
        EXECUTE stmt USING @v1, @v2, @v3, @v4, @v5, @v6;
        SET i = i + 1;
        IF i % BatchSize = 0 THEN 
            COMMIT;
            START TRANSACTION;
        END IF;
    END WHILE;
    COMMIT;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

不同批次大小的结果：

mysql> CALL inputRowsNoRandom1（1000000,1000）;
查询正常，受影响的0行（27.25秒）

mysql> CALL inputRowsNoRandom1（1000000,10000）;
查询正常，受影响的0行（26.76秒）

mysql> CALL inputRowsNoRandom1（1000000,100000）;
查询正常，受影响的0行（26.43秒）

您自己会看到差异 。仍然比交叉联接差3倍以上。