mysql千万级数据导出

原创

mob649e8159b30b 2023-08-14 06:31:34 ©著作权

文章标签 MySQL 数据导出 sql 文章分类 MySQL 数据库

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

MySQL千万级数据导出

介绍

在数据库应用中，数据导出是一个常见的需求。当数据量较小的时候，我们可以直接使用MySQL的导出命令来完成数据导出。但是当数据量达到千万级以上时，直接使用MySQL的导出命令可能会导致各种问题，比如导出时间过长、内存占用过高等。

本文将介绍一种针对MySQL千万级数据导出的高效方法，并提供对应的代码示例。

准备工作

在开始之前，我们需要先准备一个MySQL数据库，并向其中插入千万级数据用于测试。在本文中，我们使用一个名为test的数据库，并创建一个名为users的表来存储用户信息。

CREATE DATABASE test;

USE test;

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

接下来，我们向users表中插入千万条记录。

DELIMITER ;;
CREATE PROCEDURE generate_users()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 10000000 DO
        INSERT INTO users (id, name, age) VALUES (i, CONCAT('User', i), FLOOR(RAND() * 100));
        SET i = i + 1;
    END WHILE;
END;;
DELIMITER ;

CALL generate_users();

数据分页导出

为了避免一次性导出全部数据，我们可以采用数据分页的方式来进行导出。这样可以降低内存占用，并且可以通过多线程并发导出来提高导出速度。

以下是一个基于Python的示例代码，用于将users表的数据分页导出为CSV文件。

import mysql.connector
import csv
import threading

# 连接MySQL数据库
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="test"
)

# 获取数据总量
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM users")
total = cursor.fetchone()[0]
cursor.close()

# 每页数据量
page_size = 10000

# 导出CSV文件
def export_csv(start, end):
    cursor = conn.cursor()
    cursor.execute(f"SELECT * FROM users LIMIT {start},{end}")
    rows = cursor.fetchall()
    cursor.close()

    filename = f"users_{start}_{end}.csv"
    with open(filename, "w", newline="") as csvfile:
        writer = csv.writer(csvfile)
        writer.writerow(["id", "name", "age"])
        for row in rows:
            writer.writerow(row)

# 分页导出数据
threads = []
for i in range(0, total, page_size):
    start = i
    end = min(i + page_size, total)
    thread = threading.Thread(target=export_csv, args=(start, end))
    thread.start()
    threads.append(thread)

# 等待所有线程完成
for thread in threads:
    thread.join()

# 关闭数据库连接
conn.close()

在上述代码中，我们首先通过SQL语句获取了数据的总量，然后使用多线程的方式，按照每页10000条数据的大小进行数据导出。最终导出的数据会存储在以页码命名的CSV文件中。