作业要求

构建一个关系模式和课本中的关系 movies(title,year,length,movietype,studioname,producerC) 一样的关系,名称自定,在这个关系中插入1000万条记录。

注:关系 movies 的主键为 (title,year)

要求如下:

  • 在尽可能短的时间内完成;
  • 只允许使用原生的SQL,不允许将SQL作为嵌入语言,也不允许使用其他语言如C#、Python等来完成;
  • 提交你的详细解决方案和结果。
  • 分析

    查资料得知

  • 可以将多条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

    登录并创建数据库:

    1
    2
    3
    mysql -u root -p

    CREATE DATABASE `newmoviedb` DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

    复制moviedb至newmoviedb

    复制数据库

    1
    mysqldump moviedb -u root -pchouxianyu --add-drop-table | mysql newmoviedb -u root -pchouxianyu

    上面 chouxianyu 是我的mysql密码

    进入newmoviedb

    1
    use newmoviedb;

    设置max_allowed_packet

    设置max_allowed_packet为100M

    1
    set global max_allowed_packet = 100*1024*1024;

    删除movies中所有元素(调试用)

    1
    delete from movies;

    生成一条insert语句

    下边是 insert.py

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    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://www.cnblogs.com/freefei/p/7679991.html

    https://blog.csdn.net/qq_22855325/article/details/76087138

    https://blog.csdn.net/weixin_44595372/article/details/88723191

    https://zhidao.baidu.com/question/185665472.html

    https://www.cnblogs.com/zhangjpn/p/6231662.html

    https://www.cnblogs.com/wangcp-2014/p/8038683.html

    https://blog.csdn.net/gb4215287/article/details/82669785

    作者: @臭咸鱼

    转载请注明出处: https://www.cnblogs.com/chouxianyu/

    欢迎讨论和交流!