Mysql快速导出导入数据的实验

一、创建测试数据库

CREATE database example;

use example;
create TABLE `user` (
  `id` int(11) NOT NULL,
  `last_name` varchar(45) DEFAULT NULL,
  `first_name` varchar(45) DEFAULT NULL,
  `sex` set(‘M‘,‘F‘)  DEFAULT NULL,
  `age` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `address` varchar(45) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_last_first_name_age` (`last_name`,`first_name`,`age`) USING BTREE,
  KEY `idx_phone` (`phone`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、使用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
*/
时间: 08-10

Mysql快速导出导入数据的实验的相关文章

MySQL 之 导出导入数据

导出数据库(sql脚本)  mysqldump -u 用户名 -p 数据库名 > 导出的文件名mysqldump -u root -p --databases db_name > test_db.sql                 ##得到整个数据库,最全面::不要--databases也行,就是不够信息全面 mysql导出数据库一个表 mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名mysqldump -u wcnc -p test_db users>

【原创】MySQL和PostgreSQL 导入数据对比

在虚拟机上测评了下MySQL 和 PostgreSQL 的各种LOAD FILE方式以及时间. 因为是虚拟机上的测评,所以时间只做参考,不要太较真, 看看就好了. MySQL 工具: 1. 自带mysqlimport工具. 2. 命令行 load data infile ... 3. 利用mysql-connector-python Driver来写的脚本. PostgreSQL 工具: 1. pgloader 第三方工具. 2. 命令行 copy ... from ... 3. 利用psyco

MySQL和PostgreSQL 导入数据对照

在虚拟机上測评了下MySQL 和 PostgreSQL 的各种LOAD FILE方式以及时间. 由于是虚拟机上的測评,所以时间仅仅做參考,不要太较真, 看看就好了.MySQL 工具:    1. 自带mysqlimport工具.    2. 命令行 load data infile ...    3. 利用mysql-connector-python Driver来写的脚本. PostgreSQL 工具:    1. pgloader 第三方工具.    2. 命令行 copy ... from

mysql 导出导入数据 -csv

MySql数据库导出csv文件命令: mysql> select first_name,last_name,email from account into outfile 'e://output1.csv' fields terminated by ','optionally enclosed by ''lines terminated by '/n'; csv文件效果: sunny Grigoryan [email protected]           Jon Siegal [email 

mysql 5.5 导入数据 和 [【乱码解决

今日导入数据到阿里云上的mysql 5.5,遇到乱码问题,参考了网上不少相关帖子,顺便记录下来,供参考: # mysql --version mysql  Ver 14.14 Distrib 5.5.40, for linux2.6 (x86_64) using readline 5.1 1)先查看编码信息: mysql> show variables like 'character%' -> ; +--------------------------+--------------------

solr6.6教程-从mysql数据库中导入数据(三)

整理多半天的solr6.6,终于算是把solr6.6中配置数据库弄出来了,网上的文章千篇一律,各说个的,没有一篇统一覆盖solr6.6版本配置的 帖子,本章节我会把配置的一些注意事项一一列举,由于时间原因,本片只介绍如何一次性导入数据,不附带增量导入,想看增量的同学可以在后面的文章中. 1,配置准备 本文的前提是你已经配置好了solr,并新创建了一个core,我们下面都会按照前一篇文章中的core_demo为基础开始 2,修改soreconfig.xml 在soreconfig.xml的<req

mysql导出导入数据

1.导出整个数据库mysqldump -u 用户名 -p 数据库名 > 导出的文件名mysqldump -u dbuser -p dbname > dbname.sql2.导出一个表mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名mysqldump -u dbuser -p dbname users> dbname_users.sql3.导出一个数据库结构mysqldump -u dbuser -p -d --add-drop-table dbname >

navicat 导出导入数据

可以借助这个工具来进行数据的导入导出操作. 导出数据,选择导出的格式Excel 2007 file,next会列出表的列表 选择需要导出的表,在相应的Export to位置选择导出的excel的存放位置 继续next,这里可以选择导出的字段 默认是选中全部的Fields.next 这里需要注意的是那个包含表头,一定要选中,因为如果你下步想用这个来重新导入进来的话,这个就必须选中,否则这个excel会提示打不开.next start即可,导出成功会有提示 看到这个Finished-Successf

Mysql导入导出大量数据的方法、备份恢复办法

经常使用PHP+Mysql的朋友一般都是通过phpmyadmin来管理数据库的.日常的一些调试开发工作,使用phpmyadmin确实很方便.但是当我们需要导出几百兆甚至几个G的数据库时,phpmyadmin就会很卡.甚至如果你是想导入大量数据,则phpmyadmin完全无法完成.因为phpmyadmin是基于PHP网页的,无法上传太大的sql文件.其实mysql官方给了我们很好的解决方案:mysqldump. 下面介绍,如何在Windows环境下,使用mysqldump导出数据库: 找到你mys