MySQL Quick Export Import data experiment

First, create a test database

CREATE Database example;use example;create TABLE ' user ' (' ID 'int( One) not NULL, ' last_name ' varchar ( $) DEFAULT NULL, ' first_name ' varchar ( $) DEFAULT NULL, ' sex 'Set('M','F') DEFAULT NULL, ' age ' tinyint (1) DEFAULT NULL, ' phone ' varchar ( One) DEFAULT NULL, ' address ' varchar ( $) DEFAULT NULL, ' password ' varchar ( $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 BTR EE) ENGINE=innodb DEFAULT Charset=utf8;

Ii. use of Python3.6 to generate test data

1, changepipsource.py effect: Speed up the installation speed of PIP, principle: Using the image of the watercress

Import Osini="""[Global]Index-url = https://pypi.doubanio.com/simple/[install]trusted-host=pypi.doubanio.comdisable-pip-version-check =trueTimeout= -"""Pippath = os.environ["UserProfile"] +"\\pip\\"ifNot os.path.exists (pippath): Os.mkdir (Pippath) with open (Pippath+"Pip.ini","w+") asf:f.write (INI)

2. Script to generate test data

(1) util/config.py

class initconfig:     ' 127.0.0.1 '  22066' root'                dsideal' "Example"    

(2) util/mysqlhelper.py

#--encoding:utf-8--# pip Install pymysqlimport pymysql.cursors fromUtil.config Import *classmysqlhelper:myversion=0.1def __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=DBTry: 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 asE:print ('MySql Error:%d%s'% (e.args[0], e.args[1]) def query (self, SQL):Try: Self.cursor.execute (SQL) result=Self.cursor.fetchall ()returnresult except Exception asE:print ('MySql Error:%s SQL:%s'%(E, SQL)) def execute (self, SQL):Try: Self.cursor.execute (SQL) Self.conn.commit () except Exception asE:print ('MySql Error:%s SQL:%s'%(E, SQL)) def executemany (self, SQL, data):Try: Self.cursor.executemany (SQL, data) self.conn.commit () except Exception asE: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 RandomimportstringImport Time fromUtil.mysqlhelper Import *#批量插的次数loop_count=1000000#每次批量查的数据量batch_size= -Success_count=0Fails_count=0#数据库的连接chars='Aabbccddeeffgghhiijjkkllmmnnooppqqrrssttuuvvwwxxyyzz'digits='0123456789'def random_generate_string (length):return "'. Join (Random.sample (chars, length)) def random_generate_number (length):ifLength >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=13100000000def _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, -), Phone_num_seed+ c[0], random_generate_string ( -), random_generate_string (Ten), Time.strftime ("%y-%m-%d%h:%m:%s")        )    return_random_generate_datadef 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 (IDs, last_name, first_name, sex, age, phone, address, password, create_time) VALUES ("+",". Join (["%s"  forXinchRange (Column_count)]) +")"Batch_count=0Begin_time=time.time () forXinchRange (loop_count): Batch_count= x *batch_size Gen_fun=Random_generate_data (batch_count) Batch_data= [Gen_fun () forXinchrange (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 ("generate Data in total:"+str (success_count)) print ("Total time Elapsed (s):"+str (TOTAL_SEC)) print ("QPS:"+str (QPS)) except Exception asE:print (e) RaiseElse: Passfinally: Pass

3. Export generated 100W test data to generate 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. Test Import

' D://user.csv ' into table ' user '   ,  ' ' ' '  "'  \ r \ n';

5. Test the large table in the system

' /usr/local/t_resource_info.csv ' into table ' T_resource_info '   ,'  " ' ' " '  ' \ r \ n ' ; /* 1. Export the affected rows: 822445 time: 26.410S985.91MB2, import the affected rows: 822445 time: 257.772s */

MySQL quickly export the experiment of importing data