首页 / MYSQL / Mysql快速导出导入数据的实验
Mysql快速导出导入数据的实验
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql快速导出导入数据的实验,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5280字,纯文字阅读大概需要8分钟。
内容图文
![Mysql快速导出导入数据的实验](/upload/InfoBanner/zyjiaocheng/489/9c8bc66d50a04f93b41a354e16cdae8e.jpg)
二、使用Python3.6产生测试数据
1、ChangePipSource.py 作用:加快PIP的安装速度,原理:使用豆瓣的镜像
import os ini = """[global] index-url = https://pypi.doubanio.com/simple/ [install] trusted-host=pypi.doubanio.com disable-pip-version-check = true timeout = 600 """ pippath = os.environ["USERPROFILE"] + "\\pip\\" if not os.path.exists(pippath): os.mkdir(pippath) with open(pippath + "pip.ini", "w+") as f: f.write(ini)
2、生成测试数据的脚本
(1)Util/Config.py
class InitConfig: DataBaseHost = ‘127.0.0.1‘ DataBasePort = 22066 DataBaseUser = ‘root‘ DataBasePassword = ‘dsideal‘ DataBaseName = "example"
(2)Util/MySQLHelper.py
# --encoding:utf-8-- # pip install pymysql import pymysql.cursors from Util.Config import * class MySQLHelper: myVersion = 0.1 def __init__(self, host=InitConfig.DataBaseHost, port=InitConfig.DataBasePort, user=InitConfig.DataBaseUser, password=InitConfig.DataBasePassword, db=InitConfig.DataBaseName, charset="utf8"): self.host = host self.user = user self.port = port self.password = password self.charset = charset self.db = db try: self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.password, db=self.db, charset=self.charset, cursorclass=pymysql.cursors.DictCursor) self.cursor = self.conn.cursor() except Exception as e: print(‘MySql Error : %d %s‘ % (e.args[0], e.args[1])) def query(self, sql): try: self.cursor.execute(sql) result = self.cursor.fetchall() return result except Exception as e: print(‘MySql Error: %s SQL: %s‘ % (e, sql)) def execute(self, sql): try: self.cursor.execute(sql) self.conn.commit() except Exception as e: print(‘MySql Error: %s SQL: %s‘ % (e, sql)) def executemany(self, sql, data): try: self.cursor.executemany(sql, data) self.conn.commit() except Exception as e: print(‘MySql Error: %s SQL: %s‘ % (e, sql)) def close(self): self.cursor.close() self.conn.close()
(3)generate_user_data.py
#!/usr/bin/python # -*- coding: UTF-8 -*- import random import string import time from Util.MySQLHelper import * #批量插的次数 loop_count = 1000000 #每次批量查的数据量 batch_size = 100 success_count = 0 fails_count = 0 #数据库的连接 chars = ‘AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz‘ digits = ‘0123456789‘ def random_generate_string(length): return ‘‘.join(random.sample(chars, length)) def random_generate_number(length): if length > len(digits): digit_list = random.sample(digits, len(digits)) digit_list.append(random.choice(digits)) return ‘‘.join(digit_list) return ‘‘.join(random.sample(digits, length)) def random_generate_data(num): c = [num] phone_num_seed = 13100000000 def _random_generate_data(): c[0] += 1 return ( c[0], "last_name_" + str(random.randrange(100000)), "first_name_" + str(random.randrange(100000)), random.choice(‘MF‘), random.randint(1, 120), phone_num_seed + c[0], random_generate_string(20), random_generate_string(10), time.strftime("%Y-%m-%d %H:%M:%S") ) return _random_generate_data def execute_many(insert_sql, batch_data): db = MySQLHelper() db.executemany(insert_sql, batch_data) db.close() try: #user表列的数量 column_count = 9 #插入的SQL insert_sql = "replace into user(id, last_name, first_name, sex, age, phone, address, password, create_time) values (" + ",".join([ "%s" for x in range(column_count)]) + ")" batch_count = 0 begin_time = time.time() for x in range(loop_count): batch_count = x * batch_size gen_fun = random_generate_data(batch_count) batch_data = [gen_fun() for x in range(batch_size)] execute_many(insert_sql, batch_data) success_count=success_count+batch_size print("Running..."+str(success_count)) end_time = time.time() total_sec = end_time - begin_time qps = success_count / total_sec print("总共生成数据: " + str(success_count)) print("总共耗时(s): " + str(total_sec)) print("QPS: " + str(qps)) except Exception as e: print(e) raise else: pass finally: pass
3、将生成的100W条测试数据导出生成CSV
select id,last_name,first_name,sex,age,phone,address,password,create_time from user into outfile ‘d://user.csv‘ fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘ lines terminated by ‘\r\n‘;
4、测试导入
truncate table user; load data infile ‘d://user.csv‘ into table `user` fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘ lines terminated by ‘\r\n‘;
5、测试一下系统中的大表
load data infile ‘/usr/local/t_resource_info.csv‘ into table `t_resource_info` fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘ lines terminated by ‘\r\n‘; /* 1、导出 受影响的行: 822445 时间: 26.410s 985.91MB 2、导入 受影响的行: 822445 时间: 257.772s */
Mysql快速导出导入数据的实验
标签:qps 实验 tco count esc running 导出 gen efault
本文系统来源:http://www.cnblogs.com/littlehb/p/7343840.html
内容总结
以上是互联网集市为您收集整理的Mysql快速导出导入数据的实验全部内容,希望文章能够帮你解决Mysql快速导出导入数据的实验所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。