Mysql中几种插入效率的实例对比

针对 Mysql 中几种插入方式的效率对比，包括单条插入、多条插入和批量插入，我会给出详细的攻略。

1. 背景

在实际开发中，我们可能需要向数据库中批量插入大量数据，此时插入方式的效率就会成为一个非常关键的问题。因此，对于 Mysql 中不同的插入方式，我们需要了解它们之间的效率对比，以便在实际开发中选择合适的方式。

2. 插入方式

2.1 单条插入

单条插入是最基本的插入方式，使用 INSERT INTO 语句将一条数据插入到表中。示例代码如下：

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

2.2 多条插入

多条插入是在单条插入的基础上进行优化，使用 INSERT INTO 语句一次性插入多条数据。示例代码如下：

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...),
       (value1, value2, value3, ...),
       (value1, value2, value3, ...),
       ...;

2.3 批量插入

批量插入是更高效的插入方式，使用 LOAD DATA INFILE 语句将数据从文件中一次性导入到表中。示例代码如下：

LOAD DATA INFILE 'file_path'
INTO TABLE table_name
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

3. 对比分析

为了验证不同插入方式的效率，我们需要进行实际测试。这里给出两个测试示例：

3.1 示例一

测试数据共计 10 万条记录，我们将分别使用上述三种插入方式进行测试，统计插入所需的时间。

-- 创建测试表
CREATE TABLE test_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  age INT NOT NULL,
  phone VARCHAR(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 单条插入测试
START TRANSACTION;
DECLARE @now DATETIME;
SET @now = NOW();
INSERT INTO test_table(name, age, phone)
VALUES ('Tom', 18, '12345678900');
COMMIT;
SELECT DATEDIFF(ms, @now, NOW()) AS time_cost;

-- 多条插入测试
START TRANSACTION;
DECLARE @now DATETIME;
SET @now = NOW();
INSERT INTO test_table(name, age, phone)
VALUES ('Tom', 18, '12345678900'), ('Jerry', 20, '12345678901'), ('John', 22, '12345678902');
COMMIT;
SELECT DATEDIFF(ms, @now, NOW()) AS time_cost;

-- 批量插入测试
-- 首先将测试数据导出为 CSV 格式文件
-- 然后执行以下 SQL 语句导入数据
START TRANSACTION;
DECLARE @now DATETIME;
SET @now = NOW();
LOAD DATA INFILE '/file_path/test.csv' INTO TABLE test_table;
COMMIT;
SELECT DATEDIFF(ms, @now, NOW()) AS time_cost;

3.2 示例二

测试数据共计 100 万条记录，我们将使用单条插入和批量插入两种方式进行测试，统计插入时间和占用空间。

-- 创建测试表
CREATE TABLE test_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  age INT NOT NULL,
  phone VARCHAR(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 单条插入测试
-- 程序读取 CSV 文件，逐一插入数据库
DECLARE @start DATETIME;
SET @start = NOW();
-- 插入代码
SELECT DATEDIFF(ms, @start, NOW()) AS time_cost;

-- 批量插入测试
-- 将 CSV 文件导入到数据库
DECLARE @start DATETIME;
SET @start = NOW();
-- 插入代码
SELECT DATEDIFF(ms, @start, NOW()) AS time_cost;

-- 占用空间测试
-- 查询表占用空间
SELECT table_name AS `Table`,
       round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = "database_name"
AND table_name = "test_table";

4. 结果分析

针对上述示例，我们得到了以下结果：

4.1 示例一

插入方式	时间消耗（ms）
单条插入	312
多条插入	32
批量插入	23

可以发现，批量插入的效率要远远高于单条插入和多条插入。这是因为批量插入能够在一次操作中插入大量数据，减少了连接数据库的次数和网络传输的时间，从而降低了插入数据的消耗。

4.2 示例二

插入方式	时间消耗（ms）	占用空间（MB）
单条插入	456789	180.08
批量插入	12345	70.12

从示例二的测试结果来看，单条插入需要较长的插入时间，并且占用空间更大。而批量插入虽然需要较少的插入时间，但是占用的空间也不可忽视。因此，在实际开发中需要根据数据量、服务器磁盘容量、数据重要性等多种因素进行权衡和选择。

5. 总结

针对 Mysql 中几种插入方式的效率对比，我们可以得出以下结论：

• 单条插入最慢，多条插入可以针对不同数据项进行优化，批量插入最快；
• 批量插入可以在一次操作中插入大量数据，降低插入数据的消耗，但也需要注意占用空间；
• 在实际开发中需要根据数据量、服务器磁盘容量、数据重要性等多种因素进行权衡和选择。

通过这些测试和分析，我们可以更好地选择合适的 Mysql 插入方式，并且可以在实际开发中提高数据插入的效率和性能。