作业要求
这次作业的要求是向课本中的movies(title,year,length,movietype,studioname,producerC)
中添加1千万条记录。
movies
的主键为(title,year)
。
分析
查资料得知
- 可以将多条insert语句合并为一句,即一条insert语句插入多个元组
- 可以通过事务,减少每条insert语句都建立新事务带来的时空消耗
- 可以通过
load data infile
将文件中的数据导入mysql,似乎很快的样子
虽然第三种似乎很快,但在此我采用了前两种方法,通过合并+事务实现。
我通过python模拟生成(只改变主键中的year,以生成不同元组)1千万条记录,将其组织为\(10\times100\times10000\)条记录添加进movies
,分成10个事务,每个事务里有100条insert语句,每条insert语句插入10000个元组。
实现
实现思路如下:
- 复制原数据库
moviedb
至newmoviedb
- 设置max_allowed_packet,以保证一条insert语句可以插入足够多的元组
- 用python生成一条一次插入10000个元组的insert语句
- 用python生成一个包含100条insert语句的事务,保存至sql文件
- 用navicat运行该sql文件
至此就可以实现一百万条记录的插入了(我的电脑耗时327s?好像很慢!?)
之后再套一层循环就可以继续完成1千万条记录的插入了。
下面给出可能用到的步骤(如未说明,代码默认为控制行或者mysql环境下的命令):
复制数据库
创建新数据库newmoviedb
登录并创建数据库:
mysql -u root -p
CREATE DATABASE `newmoviedb` DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;
复制moviedb至newmoviedb
复制数据库
mysqldump moviedb -u root -pchouxianyu --add-drop-table | mysql newmoviedb -u root -pchouxianyu
上面chouxianyu
是我的mysql密码
进入newmoviedb
use newmoviedb;
设置max_allowed_packet
设置max_allowed_packet为100M
set global max_allowed_packet = 100*1024*1024;
删除movies中所有元素(调试用)
delete from movies;
生成一条insert语句
下边是insert.py
insertStr = "INSERT INTO movies(title,year,length,movietype,studioname,producerC) VALUES"
value1_str = "('mymovietitle',"
# j
value2_str = ",120,'sciFic','MGM',100)"
# ,;
num_value = 10000
f = open(r'C:\Users\Cxy\Documents\Navicat\MySQL\Servers\MySQL\newmoviedb\insertRow.sql', 'w') # 清空文件内容再写
f.write(insertStr)
for j in range(1, num_value):
f.write(value1_str)
f.write(str(j))
f.write(value2_str)
f.write(',')
f.write(value1_str)
f.write(str(num_value))
f.write(value2_str)
f.write(';')
f.close()
生成一个事务
以下是transaction.py
transaction_begin_str = "START TRANSACTION;\n"
transaction_end_str = "COMMIT;\n"
insertStr = "INSERT INTO movies(title,year,length,movietype,studioname,producerC) VALUES"
value1_str = "('mymovietitle',"
# j
value2_str = ",120,'sciFic','MGM',100)"
# ,;
num_value = 10000
num_sql = 100
# 打开文件
f = open(r'C:\Users\Cxy\Documents\Navicat\MySQL\Servers\MySQL\newmoviedb\transaction.sql', 'w') # 清空文件内容再写
# 将SQL语句写入文件
f.write(transaction_begin_str)
for i in range(1, num_sql+1):
f.write(insertStr)
for j in range(1, num_value):
f.write(value1_str)
f.write(str(i*num_value*10+j))
f.write(value2_str)
f.write(',')
f.write(value1_str)
f.write(str(i*num_value*10+num_value))
f.write(value2_str)
f.write(';\n')
f.write(transaction_end_str)
# 关闭文件
f.close()
参考链接
https://blog.csdn.net/qq_22855325/article/details/76087138
https://segmentfault.com/a/1190000016867644
https://mp.weixin.qq.com/s?__biz=MzUxMTgyNTQ2MA==&mid=2247483757&idx=1&sn=e8f9728ef6cd12c3c87195fcd4c7eb01&chksm=f96c8236ce1b0b20753e278ade6e58351c3c3bd8d1160d4ab1a0a3a9597bf9ab75bbf1cf2a9e&token=1803387227&lang=zh_CN#rd
https://zhuanlan.zhihu.com/p/55538088
https://zhuanlan.zhihu.com/p/39850961
作者:@臭咸鱼
转载请注明出处:https://www.cnblogs.com/chouxianyu/
欢迎讨论和交流!