mysql 快速插入百万表数据到另外的表中

原创

mob64ca12e9cad4 2024-01-19 05:36:50 ©著作权

©著作权归作者所有：来自51CTO博客作者mob64ca12e9cad4的原创作品，请联系作者获取转载授权，否则将追究法律责任

标题：MySQL快速插入百万表数据到另外的表中

引言

在实际应用中，我们经常面临将一个表中的数据快速插入到另外一个表中的需求。尤其是在处理大量数据时，传统的逐行插入方式效率较低，会导致整个过程非常耗时。本文将介绍如何使用MySQL的高效插入技术，以及一些优化手段，帮助开发者提高插入大量数据的效率。

问题背景

假设我们有一个名为table_a的表，包含了100万条数据。我们需要将这100万条数据快速插入到另外一个名为table_b的表中。传统的方法是使用INSERT语句逐行插入数据，但这种方式效率较低，耗时较长。为了解决这个问题，我们将介绍两种高效的插入方式。

使用INSERT INTO SELECT语句

第一种高效插入方式是使用INSERT INTO SELECT语句。该语句可以一次性将一个表中的数据插入到另一个表中，而无需逐行插入。下面是一个示例代码：

INSERT INTO table_b (column1, column2, column3)
SELECT column1, column2, column3
FROM table_a;

在上述代码中，我们将table_a表中的column1、column2和column3列的数据插入到table_b表中。通过一次性插入，我们可以显著提高插入数据的效率。

使用LOAD DATA INFILE语句

第二种高效插入方式是使用LOAD DATA INFILE语句。该语句可以将文本文件中的数据直接导入到表中，而无需逐行插入。下面是一个示例代码：

LOAD DATA INFILE '/path/to/file.csv' INTO TABLE table_b
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

在上述代码中，我们将位于/path/to/file.csv位置的CSV文件的数据导入到table_b表中。通过直接导入文件，我们可以极大地提高插入数据的效率。

优化插入性能的技巧

除了使用高效的插入方式外，我们还可以采用一些优化技巧来提高插入性能。

1. 禁用索引

在插入大量数据时，为了提高性能，可以考虑禁用表中的索引。禁用索引可以减少插入数据时的索引维护开销。插入完成后，再重新启用索引。

ALTER TABLE table_b DISABLE KEYS;
-- 执行插入语句
ALTER TABLE table_b ENABLE KEYS;

2. 批量提交数据

在进行大量数据插入时，可以将数据分批次提交，而不是一次性提交所有数据。这样可以减少数据库的负担，提高整体插入性能。

import mysql.connector

# 建立数据库连接
conn = mysql.connector.connect(user='username', password='password', host='localhost', database='dbname')
cursor = conn.cursor()

# 分批次插入数据
batch_size = 1000
data = []
for i in range(1000000):
    # 构造数据
    data.append((i, 'data' + str(i)))
    
    # 达到批量插入阈值时，执行插入操作
    if len(data) == batch_size:
        cursor.executemany('INSERT INTO table_b (id, data) VALUES (%s, %s)', data)
        conn.commit()
        data = []
    
# 最后一批数据插入
if data:
    cursor.executemany('INSERT INTO table_b (id, data) VALUES (%s, %s)', data)
    conn.commit()

# 关闭连接
cursor.close()
conn.close()