MySQL快速生成100W条测试数据

本文介绍了一种使用MySQL内存表和存储过程快速生成大量测试数据的方法。通过创建内存表并利用函数生成随机字符串,结合存储过程批量插入记录,最后将数据迁移到普通表中。适用于需要大量测试数据的场景。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

自接看这一篇就完了(记得给我点个赞)

MySQL如何快速生成千万数据量?mysql 如何快速生成百万测试数据实现思路1、创建内存表和普通表2、创建函数及存储过程创建生成n个随机数字icon-default.png?t=O83Ahttps://mp.weixin.qq.com/s?__biz=Mzg3MzU2Njk3MA==&mid=2247492681&idx=1&sn=661a4b42913bff508336b6a97e7a416e&scene=19#wechat_redirect

1、生成思路

利用mysql内存表插入速度快的特点,先利用函数和存储过程在内存表中生成数据,然后再从内存表插入普通表中


2、创建普通表及内存表

-- 普通表
CREATE TABLE `vote_record` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`user_id` VARCHAR (20) NOT NULL,
	`vote_id` INT (11) NOT NULL,
	`group_id` INT (11) NOT NULL,
	`create_time` datetime NOT NULL,
	PRIMARY KEY (`id`),
	KEY `index_user_id` (`user_id`) USING HASH
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

 3、创建函数及存储过程

-- 创建函数
CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET latin1
BEGIN 
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; 
DECLARE return_str varchar(255) DEFAULT '' ;
DECLARE i INT DEFAULT 0; 
WHILE i < n DO 
SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1)); 
SET i = i +1; 
END WHILE; 
RETURN return_str; 
END

-- 创建存储过程
CREATE  PROCEDURE `add_vote_memory`(IN n int)
BEGIN  
  DECLARE i INT DEFAULT 1;
    WHILE (i <= n ) DO
      INSERT into vote_record  (user_id,vote_id,group_id,create_time ) VALUEs (rand_string(20),FLOOR(RAND() * 1000),FLOOR(RAND() * 100) ,now() );
			set i=i+1;
    END WHILE;
END

4、调用存储过程 (生成内存表数据)

-- 调用
CALL add_vote_memory(1000000)

 如果内存不足,wind8.1中添加一下两段代码。路径为       C:\ProgramData\MySQL\MySQL Server 5.7 (mysql有默认的设置,最好是设置一下)

  tmp_table_size=2G
  max_heap_table_size = 1G

2021年11月18日10:55:04    更新

报错 mysql 1055错误代码,可以直接略过错误或者执行以下操作

1、SELECT @@sql_mode语句

2、将查询结果中的ONLY_FULL_GROUP_BY去掉然后复制,打开MySQL的配置文件(my.ini),将sql_mode的值设置为复制的值(若没有sql_mode在[mysqld]下方添加一行即可)

<think>我们面对的需求是生成1亿行测试数据用于MySQL性能测试。由于直接生成如此庞大的数据量对资源和时间都是挑战,我们需要采用高效的方法。考虑关键点:1.**数据真实性**:测试数据应尽可能模拟真实数据分布(如姓名、地址的随机性,数值字段的分布等)2.**生成效率**:避免单INSERT语句,应采用批量生成和加载3.**资源消耗**:在客户端生成数据会消耗大量内存,建议在数据库服务器端生成4.**扩展性**:能灵活控制数据量、字段类型和分布以下是几种高效方法:---###⚡方法一:使用内存表+存储过程(适合中等数据量,1亿行需分批次)```sql--创建内存临时表作为数据生成模板CREATETEMPORARYTABLEtemplate(idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(50),emailVARCHAR(100),ageTINYINT,created_atTIMESTAMP)ENGINE=MEMORY;--插入基础数据模板(可重复利用)INSERTINTOtemplate(name,email,age,created_at)VALUES(CONCAT('User',FLOOR(RAND()*100000)),CONCAT(FLOOR(RAND()*100000),'@test.com'),FLOOR(10+RAND()*50),NOW());--创建目标测试表(实际存储)CREATETABLEtest_data(idBIGINTPRIMARYKEY,nameVARCHAR(50),emailVARCHAR(100),ageTINYINT,created_atTIMESTAMP)ENGINE=InnoDB;--使用存储过程分批插入(每次插入100万行,执行100次)DELIMITER$$CREATEPROCEDUREgenerate_data()BEGINDECLAREiINTDEFAULT0;WHILEi<100DOINSERTINTOtest_dataSELECTNULL,CONCAT('User',FLOOR(RAND()*10000000)),CONCAT(FLOOR(RAND()*10000000),'@loadtest.com'),FLOOR(10+RAND()*50),NOW()-INTERVALFLOOR(RAND()*365)DAYFROMtemplatet1,templatet2,templatet3;--笛卡尔积扩展(调整连接表数量控制每批行数)SETi=i+1;ENDWHILE;END$$DELIMITER;--执行(注意:可能需要调整max_allowed_packet)CALLgenerate_data();```>**原理**:通过内存表的笛卡尔积快速生成组合数据,每批可达百万行。但需注意:>-需根据模板行数计算笛卡尔积量级(如模板5行:$t1\timest2\timest3=5^3=125$行/批)>-1亿行分100批,每批需生成100万行,则需$t1\timest2\times\cdots\timest_n\geq10^6$>-解决方案:串联更多表或使用循环嵌套---###🚀方法二:使用INSERT...SELECT与随机函数(推荐,无需存储过程)```sql--一步生成法(每批插入50万行,循环200次)INSERTINTOtest_data(name,email,age,created_at)SELECTCONCAT('User',FLOOR(RAND()*10000000))ASname,CONCAT(FLOOR(RAND()*10000000),'@test.com')ASemail,FLOOR(10+RAND()*50)ASage,NOW()-INTERVALFLOOR(RAND()*3650)DAYAScreated_atFROM(SELECT0UNIONSELECT1UNIONSELECT2UNIONSELECT3)t1,--4行(SELECT0UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5)t2,--6行(SELECT0UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7)t3,--8行(SELECT0UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9)t4--10行LIMIT500000;--4*6*8*10=1920行→需更多表...--为达到批量,需创建更多衍生表:--示例:t1到t8表(每个表10行)可产生$10^8=1亿$行--但内存消耗巨大,应分批执行:--使用循环控制(例如每次LIMIT1000000,执行100次)SET@batch_size=1000000;SET@batches=100;SET@i=0;WHILE@i< @batchesDOINSERTINTOtest_data(...)SELECT...FROM(多个衍生表)LIMIT@batch_size;SET@i=@i+1;ENDWHILE;```>**优化技巧**:>1.使用`SELECT...LIMITn`强制停止笛卡尔积,避免全量生成>2.调整`innodb_flush_log_at_trx_commit=0`和`sync_binlog=0`提升写入速度(测试环境!)>3.禁用唯一性检查:`SETUNIQUE_CHECKS=0;`---###⚙️方法三:使用外部工具生成数据文件再导入####步骤:1.用Python/Java等生成CSV文件```python#Python示例(分文件生成)importcsvfromfakerimportFakerfake=Faker()BATCH_SIZE=1000000FILE_COUNT=100forfile_numinrange(FILE_COUNT):withopen(f'data_{file_num}.csv','w',newline='')ascsvfile:writer=csv.writer(csvfile)foriinrange(BATCH_SIZE):writer.writerow([fake.name(),fake.email(),fake.random_int(min=18,max=65),fake.date_time_this_decade()])```2.使用`LOADDATAINFILE`高速导入```sqlLOADDATAINFILE'/path/to/data_0.csv'INTOTABLEtest_dataFIELDSTERMINATEDBY','(name,email,age,created_at);```>**速度优势**:比SQL插入快10倍以上[^1]>**注意**:需确保MySQL有文件读取权限---###📊性能对比表|方法|速度|复杂度|适用场景||------|------|--------|----------||存储过程|中等|高|需要动态数据生成,避免外部依赖||INSERT...SELECT|快|中|纯SQL环境,可接受笛卡尔积||外部导入|极快|低|允许使用外部工具,追求效率|---###⚠️重要注意事项1.**索引影响**:生成数据时建议先**禁用索引**,完成后再重建2.**日志控制**:临时关闭二进制日志(`SETsql_log_bin=0;`)可提速3.**事务提交**:批量提交(如每10万行提交一次)避免超大事务4.**硬件要求**:生成1亿行数据约需10-20GB磁盘空间(根据字段)---###❓相关问题1.如何生成符合特定分布的测试数据(如邮编、电话号码)?2.MySQL有哪些内置函数可用于生成随机数据?3.生成大量数据时如何避免主键冲突?4.除了数据量,还有哪些因素会影响数据库压力测试结果?</think> 下面是为MySQL生成1亿行测试数据的专业方案,结合引用信息对MySQL 8.0特性的优化建议: ### 📊 高效生成1亿行测试数据的方法 #### ⚙️ 方法一:使用存储过程(适合可控数据分布) ```sql DELIMITER $$ CREATE PROCEDURE GenerateTestData() BEGIN DECLARE i INT DEFAULT 0; WHILE i < 100000000 DO -- 1亿行 INSERT INTO test_table VALUES ( NULL, -- 自增ID CONCAT('user_', FLOOR(RAND() * 10000000)), -- 用户名 FLOOR(RAND() * 100), -- 年龄 NOW() - INTERVAL FLOOR(RAND() * 3650) DAY, -- 时间戳 SUBSTRING(MD5(RAND()) FROM 1 FOR 10) -- 随机字符串 ); SET i = i + 1; -- 每10万行提交一次 IF i % 100000 = 0 THEN COMMIT; START TRANSACTION; END IF; END WHILE; END$$ DELIMITER ; ``` > **优化建议**: > 1. 执行前关闭自动提交:`SET autocommit=0;` > 2. 临时禁用索引:`ALTER TABLE test_table DISABLE KEYS;` > 3. 调整批量提交阈值(如每50万行)可提升30%速度[^1] #### 🚀 方法二:使用INSERT ... SELECT笛卡尔积(极速生成) ```sql INSERT INTO test_table (name, age, created_at, random_str) SELECT CONCAT('user_', RAND() * 10000000), RAND() * 100, NOW() - INTERVAL RAND() * 3650 DAY, SUBSTRING(MD5(RAND()), 1, 10) FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) a, (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) b, (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) c, ... -- 添加更多表达到所需行数 -- 每个表贡献4行,10个表可生成4^10 ≈ 1,048,576行 -- 重复执行或增加表数量达到1亿 ``` #### 📁 方法三:外部工具+LOAD DATA(最快速度) 1. 用Python生成CSV文件: ```python import csv from faker import Faker fake = Faker() with open('test_data.csv', 'w') as f: writer = csv.writer(f) for i in range(100_000_000): # 1亿行 writer.writerow([ fake.user_name(), fake.random_int(1,100), fake.date_time_this_decade(), fake.password(length=10) ]) if i % 1_000_000 == 0: print(f"Generated {i:,} rows") ``` 2. MySQL加载数据: ```sql LOAD DATA INFILE '/path/test_data.csv' INTO TABLE test_table FIELDS TERMINATED BY ',' (name, age, created_at, random_str); ``` ### ⚡ MySQL 8.0优化重点 1. **并行复制改进**:使用`binlog_transaction_dependency_tracking=WRITESET`提升复制性能,特别对低并发负载有显著改善[^1] 2. **避免ALTER TABLE问题**:确认DDL操作不包含列前缀的主键(MySQL 8.0.20+已修复Bug #16544336)[^2] 3. **兼容性检查**:确保主机二进制日志格式 ≥ MySQL 5.0(8.0不兼容旧格式)[^3] ### 📊 性能压测建议方案 | 阶段 | 操作 | 目标 | |------|------|------| | 准备期 | `SET GLOBAL sync_binlog=0, innodb_flush_log_at_trx_commit=0` | 临时关闭持久化 | | 数据加载 | 用LOAD DATA或INSERT...SELECT | 最大化I/O吞吐 | | 压测执行 | 开启`binlog_transaction_dependency_tracking=WRITESET` | 优化复制并行性 | | 监控 | 检查`SHOW ENGINE INNODB STATUS`, `SHOW SLAVE STATUS` | 识别瓶颈点 | ### ⚠️ 关键注意事项 1. **磁盘空间**:1亿行约需15-30GB(取决于字段) 2. **事务隔离**:使用`START TRANSACTION`+分批`COMMIT`避免超大事务 3. **索引策略**:数据加载后重建索引 4. **字符集**:生成包含多语言字符的数据测试UTF8MB4 5. **MySQL配置**: ```ini [mysqld] bulk_insert_buffer_size = 256M max_allowed_packet = 1G ``` ### ❓ 相关问题 1. MySQL 8.0的Writeset复制机制如何提升复制性能? 2. 生成测试数据时如何模拟真实业务的数据分布? 3. MySQL压力测试中应监控哪些关键指标? 4. 如何解决LOAD DATA导入过程中的外键约束问题? 5. 压测期间如何模拟高并发读写混合的场景?
评论 8
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值