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";