一亿数据——数据库造数实践

十叶知秋

1164人浏览 · 2025-03-06 22:30:00

十叶知秋 · 2025-03-06 22:30:00 发布

背景

由于需要某个页面有大量数据的时候，访问的情况性能如何，因此需要造一亿的数据。

现在ai辅助已经很强大了，一些脚本的文件其实可以直接让其生成，再微改。重要的是，想AI提问的方式，要符合自己的要求，包括一些细节方面的事情。

过程

确认表与表结构、字段类型

找研发确认需要造数的业务表，以及确认表之间的关联关系，以及对应的字段类型。

如下，拿到了三个表，

表message_cot

-- datasense_copilot_new.message_cot definition

CREATE TABLE `message_cot` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `tenant_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '租户ID',
  `creator` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人',
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  `editor` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '修改人',
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '修改时间',
  `remarks` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `state` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'normal' COMMENT '状态：normal；deleted',
  `msg_id` bigint NOT NULL COMMENT '消息ID',
  `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '内容',
  `user_cot_content` text COLLATE utf8mb4_unicode_ci COMMENT '用户思索链',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `msg_id_index` (`msg_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=50626 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='思索链表';

表message

-- datasense_copilot_new.message definition

CREATE TABLE `message` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `tenant_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '租户ID',
  `creator` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '创建人',
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  `editor` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '修改人',
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '修改时间',
  `remarks` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `state` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'normal' COMMENT '状态：normal；deleted',
  `conversation_id` bigint NOT NULL COMMENT '会话ID',
  `parent_id` bigint DEFAULT NULL COMMENT '父ID',
  `author` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `creator_nickname` varchar(119) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `question` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '问题',
  `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '回复内容',
  `author_is_user` tinyint(1) DEFAULT '0',
  `feedback` int DEFAULT '0' COMMENT '反馈：0-无；-1-点踩；1-点赞；',
  `feedback_user` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '反馈人员',
  `action_type` int DEFAULT '0' COMMENT '无动作，正常消息：0-无；1：动作-召回指标；2-动作推荐问题',
  `mql` json DEFAULT NULL COMMENT '查询MQL内容',
  `action_content` json DEFAULT NULL COMMENT '动作内容',
  `final_question` text COLLATE utf8mb4_unicode_ci COMMENT '最终问题',
  `question_type` int DEFAULT '0' COMMENT '0:正常用户提问；1：召回指标；2：推荐问题',
  `message_status` tinyint NOT NULL DEFAULT '0' COMMENT '消息状态: 0-正常；1-异常；2-拒答；3-召回；',
  `feedback_type` json DEFAULT NULL COMMENT '反馈类型',
  `feedback_comment` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '反馈内容',
  `indicator` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '指标名称',
  `intention_status` int DEFAULT '0' COMMENT '0:意图正常，1：意图异常',
  `resultData` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `recommend_question` varchar(1024) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户选择推荐问题',
  `querySql` longtext COLLATE utf8mb4_unicode_ci COMMENT '查询sql',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=115017 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='消息表';

表

-- datasense_copilot_new.conversation definition

CREATE TABLE `conversation` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `tenant_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '租户ID',
  `creator` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人',
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  `editor` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '修改人',
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '修改时间',
  `remarks` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `state` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'normal' COMMENT '状态：normal；deleted',
  `app_user_id` bigint NOT NULL,
  `title` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '会话标题',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1050 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='会话表';

表关联关系：

message.id = message_cot.msg_id

conversation.id = message.conversation_id

表关联关系：

message.id = message_cot.msg_id

conversation.id = message.conversation_id。

其中，message：message_cot是2:1的关系，conversation：message是1：多的关系

选择生成数方式

通过python脚本直接连接数据库，批量插入生成，适用于数据量在100-200w以内，非宽表生成
通过python生成对应的csv文件，然后直接导入mysql，适用于大数据量生成

这里采用的是方式2。

生成csv文件代码

import csv
import random
from datetime import datetime, timedelta
import time
import sys

# 配置参数
conversation_count = 500000
message_count = 10_000_000
cot_count = 5_000_000
tenants = ['fastdata', 'tenant2', 'tenant3']
users = [f'user{i}' for i in range(1, 1001)]

# 中文问题生成组件
time_frames = ["今日", "本周", "本月", "本季度", "近三年", "去年", "前年", "最近三个月"]
metrics = ["销售额", "销售量", "产量", "利润", "客流量", "库存量", "退货量", "用户数"]
entities = ["张三", "李四", "王五", "销售部", "生产部", "华东区", "旗舰店", "线上渠道"]
verbs = ["的", "有多少", "是多少", "的统计", "的汇总", "的趋势", "的情况"]


def generate_chinese_question():
    """生成随机中文业务问题"""
    time = random.choice(time_frames)
    metric = random.choice(metrics)
    entity = random.choice(entities)
    verb = random.choice(verbs)

    pattern = random.choice([
        f"{time}{entity}的{metric}{verb}？",
        f"{time}{metric}{verb}？",
        f"{entity}{time}{metric}{verb}？",
        f"{metric}{verb}（{time}）？",
        f"{time}各{entity}的{metric}对比？"
    ])
    return pattern


# 进度跟踪工具类
class ProgressTracker:
    def __init__(self, total, name="Progress"):
        self.total = total
        self.name = name
        self.start_time = time.time()
        self.last_report = 0
        self.counter = 0

    def update(self, current):
        self.counter = current
        now = time.time()
        # 控制刷新频率：每1%或至少5秒更新一次
        if current == self.total or (now - self.last_report) > 5 or (
                current / self.total - self.last_report / self.total) >= 0.01:
            elapsed = now - self.start_time
            percent = current / self.total * 100
            speed = current / elapsed if elapsed > 0 else 0
            eta = (self.total - current) / speed if speed > 0 else 0

            sys.stdout.write(
                f"\r{self.name}: {percent:.1f}% | "
                f"已处理: {current}/{self.total} | "
                f"速度: {speed:.1f} rows/s | "
                f"已用: {timedelta(seconds=int(elapsed))} | "
                f"剩余: {timedelta(seconds=int(eta))}      "
            )
            sys.stdout.flush()
            self.last_report = now


# 生成 conversation.csv
print("\n生成会话数据...")
with open('conversation.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(
        ['id', 'tenant_id', 'creator', 'created_at', 'editor', 'updated_at', 'remarks', 'state', 'app_user_id',
         'title'])

    tracker = ProgressTracker(conversation_count, "Conversation")
    for c_id in range(1, conversation_count + 1):
        tenant = tenants[c_id % len(tenants)]
        creator = users[c_id % len(users)]
        created_at = (datetime(2023, 1, 1) + timedelta(
            days=random.randint(0, 365),
            seconds=random.randint(0, 86400))
                      ).strftime('%Y-%m-%d %H:%M:%S.%f')[:-3]

        writer.writerow([
            c_id,
            tenant,
            creator,
            created_at,
            creator,
            created_at,
            f'Remark {c_id}',
            'normal',
            random.randint(1000, 9999),
            f'Conversation {c_id}'
        ])
        tracker.update(c_id)
    print("\n会话数据生成完成")

# 生成 message.csv
print("\n生成消息数据...")
with open('message.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(
        ['id', 'tenant_id', 'creator', 'created_at', 'editor', 'updated_at', 'remarks', 'state', 'conversation_id',
         'parent_id', 'author', 'creator_nickname', 'question', 'content', 'author_is_user', 'feedback',
         'feedback_user', 'action_type', 'mql', 'action_content', 'final_question', 'question_type', 'message_status',
         'feedback_type', 'feedback_comment', 'indicator', 'intention_status', 'resultData', 'recommend_question',
         'querySql'])

    tracker = ProgressTracker(message_count, "Messages")
    for msg_id in range(1, message_count + 1):
        is_parent = msg_id % 2 == 1
        c_id = (msg_id - 1) // 20 + 1

        question = generate_chinese_question() if is_parent else None

        tenant = tenants[c_id % len(tenants)]
        creator = users[c_id % len(users)]
        created_at = (datetime(2023, 1, 1) + timedelta(
            days=random.randint(0, 365),
            seconds=random.randint(0, 86400))
                      ).strftime('%Y-%m-%d %H:%M:%S.%f')[:-3]

        parent_id = None if is_parent else msg_id - 1

        writer.writerow([
            msg_id,
            tenant,
            creator,
            created_at,
            creator,
            created_at,
            f'Msg Remark {msg_id}',
            'normal',
            c_id,
            parent_id,
            'Copilot' if is_parent else 'User',
            f'{creator}_nick',
            question,
            f'Content {msg_id}' if not is_parent else None,
            0 if is_parent else 1,
            0,
            None,
            0,
            None,
            None,
            None,
            0,
            0,
            None,
            None,
            '交易金额' if is_parent else None,
            0,
            None,
            None,
            None
        ])
        tracker.update(msg_id)
    print("\n消息数据生成完成")

# 生成 message_cot.csv
print("\n生成COT数据...")
with open('message_cot.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(
        ['id', 'tenant_id', 'creator', 'created_at', 'editor', 'updated_at', 'remarks', 'state', 'msg_id', 'content',
         'user_cot_content'])

    tracker = ProgressTracker(cot_count, "COTs")
    for cot_id in range(1, cot_count + 1):
        msg_id = 2 * cot_id - 1
        c_id = (msg_id - 1) // 20 + 1
        tenant = tenants[c_id % len(tenants)]
        creator = users[c_id % len(users)]
        created_at = (datetime(2023, 1, 1) + timedelta(
            days=random.randint(0, 365),
            seconds=random.randint(0, 86400))
                      ).strftime('%Y-%m-%d %H:%M:%S.%f')[:-3]

        writer.writerow([
            cot_id,
            tenant,
            creator,
            created_at,
            creator,
            created_at,
            f'COT Remark {cot_id}',
            'normal',
            msg_id,
            f'COT Content {cot_id}',
            f'User COT {cot_id}'
        ])
        tracker.update(cot_id)
    print("\nCOT数据生成完成")

print("\n所有数据生成完成！")

导入数据库

这里踩坑的一点：容器部署的MySQL与非容器部署的不一样。若是在容器内部署的，需要进入容器里面执行，并且进入容器的时候，要加上–local-infile，否则会报错拒绝。

加上之后就可以正常导入了：

导入前准备：

-- 1. 登录 MySQL（需有 FILE 权限和足够权限）
mysql -u root -p

-- 2. 创建数据库（若不存在）
CREATE DATABASE IF NOT EXISTS datasense_copilot_new 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 3. 确认表结构已存在（需与CSV字段顺序完全一致）
-- 此处需执行你提供的三个 CREATE TABLE 语句

导入过程：

-- 禁用约束和索引（提速关键）
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
SET sql_log_bin = 0;  -- 如果不需要binlog

-- 导入 conversation
ALTER TABLE conversation DISABLE KEYS;
LOAD DATA LOCAL INFILE '/绝对路径/conversation.csv' 
INTO TABLE conversation
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
ALTER TABLE conversation ENABLE KEYS;

-- 导入 message（耗时最长）
ALTER TABLE message DISABLE KEYS;
LOAD DATA LOCAL INFILE '/绝对路径/message.csv' 
INTO TABLE message
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
ALTER TABLE message ENABLE KEYS;

-- 导入 message_cot
ALTER TABLE message_cot DISABLE KEYS;
LOAD DATA LOCAL INFILE '/绝对路径/message_cot.csv' 
INTO TABLE message_cot
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
ALTER TABLE message_cot ENABLE KEYS;

-- 恢复设置
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
SET sql_log_bin = 1;

-- 优化表结构（可选）
ANALYZE TABLE conversation, message, message_cot;

参数	说明	推荐值
`<font style="color:rgb(64, 64, 64);">LOCAL</font>`	从客户端机器读取文件（否则需服务端路径权限）	必须与文件位置匹配
`<font style="color:rgb(64, 64, 64);">CHARACTER SET</font>`	字符集需与CSV文件一致	utf8mb4
`<font style="color:rgb(64, 64, 64);">FIELDS TERMINATED</font>`	字段分隔符	,
`<font style="color:rgb(64, 64, 64);">LINES TERMINATED</font>`	行终止符	\n（Windows需\r\n）
`<font style="color:rgb(64, 64, 64);">IGNORE 1 ROWS</font>`	跳过CSV标题行	根据实际文件调整

执行之后，等待执行完就好了。

预计总导入时间在：

普通机械硬盘：约2-3小时
SSD硬盘：约30-60分钟
内存盘：约10-20分钟

查看是否导入成功

直接select count（1） * table_name; 查看数据条数是否能对上

DAMO开发者矩阵

DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。

更多推荐

VLAff：用于统一可操作Affordance的视觉-语言-Affordance模型

DAMO开发者矩阵

【NeurIPS 2025】SAFE：面向视觉-语言-动作模型的多任务失败检测｜从机器人安全部署视角

SAFE（NeurIPS 2025）提出多任务失败检测：只读 VLA 末层内部特征回归失败分数，用功能共形预测把误报率控制在 α 以内。在 OpenVLA、π0、π0-FAST 的仿真与真机上，未见任务 ROC-AUC 平均领先最佳基线 4–5%，附加推理开销仅 0.73 ms（<1%）。

DAMO开发者矩阵

学生党写论文别乱堆AI工具：开题、写稿、查重、定稿分别用什么，一篇说清

论文工具别乱堆：从开题到定稿，真正适合学生的 AI 辅助组合很多同学一写论文就问：“ChatGPT、Claude、Kimi、DeepSeek、NotebookLM、毕业之家，到底哪个最强？” 这个问题本身就问错了。大模型负责启发和表达，科研智能体负责文献和流程，查重系统负责风险检测，文献管理工具负责引用规范。它们不是替代关系，而是分工关系。真正高效的用法，是在不同阶段选不同工具，而不是把降重、找