mysql插入百万数据

基础概念

MySQL是一种关系型数据库管理系统，广泛应用于各种规模的应用程序中。插入百万条数据到MySQL数据库是一个常见的需求，但也可能遇到性能瓶颈。

类型

插入百万数据的方法可以分为以下几种：

单条插入：逐条插入数据。
批量插入：一次性插入多条数据。
事务插入：将插入操作放在一个事务中，以提高效率。

应用场景

插入百万数据的场景包括但不限于：

数据迁移
数据备份
数据初始化
大数据分析前的数据准备

遇到的问题及原因

性能问题：单条插入数据会导致大量的数据库连接和事务开销，严重影响性能。
内存不足：大量数据的插入可能会导致内存不足。
锁表：在高并发环境下，插入操作可能会导致表被锁，影响其他操作。

解决方法

批量插入

批量插入可以显著提高插入效率。以下是一个示例代码：

START TRANSACTION;

INSERT INTO table_name (column1, column2, column3)
VALUES
    (value1, value2, value3),
    (value4, value5, value6),
    ...
    (valueN-2, valueN-1, valueN);

COMMIT;

使用LOAD DATA INFILE

对于大量数据的插入，可以使用LOAD DATA INFILE语句，这通常比批量插入更快。

LOAD DATA INFILE 'data.txt'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

优化数据库配置

调整缓冲区大小：增加innodb_buffer_pool_size和key_buffer_size。
调整日志文件大小：增加innodb_log_file_size。
禁用二进制日志：如果不需要二进制日志，可以临时禁用以提高插入速度。

SET GLOBAL innodb_fast_shutdown = 0;
SET GLOBAL innodb_buffer_pool_size = 2G;
SET GLOBAL key_buffer_size = 512M;
SET GLOBAL innodb_log_file_size = 256M;
SET GLOBAL sql_log_bin = OFF;

使用存储过程

通过存储过程可以自动化插入过程，减少手动操作的复杂性。

DELIMITER //

CREATE PROCEDURE InsertData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 1000000 DO
        INSERT INTO table_name (column1, column2, column3) VALUES (i, i+1, i+2);
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

CALL InsertData();