MySQL实验准备(二)--Python模拟数据(MySQL数据库)

Python模拟数据(MySQL数据库)

数据模拟

目的:模拟多个表的插入和查询数据的模拟，再通过基准测试脚本测试服务器性能和收集数据，仿真模拟。

备注:
如果需要基础的python环境，可以查看《MySQL实验准备(一)--环境准备》文档

实验脚本

通过对一个简单表的脚本插入和查询模拟，后面能 举一反三，完成多张表的多表插入，多线程的多表插入，多线程查询，和多线程的join查询。

• 数据库的表结构

mysql> show create table zdemo.student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table   | Create Table                                                                                                                                                                                                                                          |
  +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | student | CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `stu_id` mediumint(8) unsigned NOT NULL COMMENT '学号id',
  `stu_name` varchar(30) NOT NULL COMMENT '姓名',
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8     |
  +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)

• batch_insert_stu.py
  批量导入百万的数据。

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
import random
import string
conn = MySQLdb.connect("10.1.11.170", "zsd", "zsd@7101", "zdemo", charset='utf8' )
sql ='insert into student (stu_id,stu_name) values (%s,%s)'
for time in range(1,110):
        data = [];
        for r in range(1,9001):
                data.append((
                        time * 10000 + r ,
                        ['zhang', 'liu', 'hu','lu','han'][random.randint(0, 4)] + str(r)
                ))
        conn.cursor().executemany(sql, data)
        conn.commit()
        time.sleep(5)
        print("9000 inserted.")
conn.close()

• select_stu.py
  简单的查询语句，用于简单测试MySQL服务的性能。使用了timeit函数，计算时间。

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
import random
import string
import timeit
conn = MySQLdb.connect("10.1.11.170", "zsd", "zsd@7101", "zdemo", charset='utf8' )
def selectOne(conn):
        sql = 'select count(*) from student'
        conn.cursor().execute(sql)
#看selectOne函数，执行10次的执行时间：
print(timeit.timeit('selectOne(conn)', 'from __main__ import selectOne,conn', number=10))

• **threads_select_stu.py **
  多线程的查询脚本，仿真模拟数据的查询，测试MySQL数据库的性能。

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
import random
import string
import timeit
import time
import threading
import thread
def selectOne(threadName,n,delay):
      conn = MySQLdb.connect("10.1.11.170", "zsd", "zsd@7101", "zdemo", charset='utf8' )
      cursor = conn.cursor()
      i = 1
      while i < n:
        i += 1
        time.sleep(delay)
        print '%s: %s is running...' % ( threadName, time.ctime(time.time()) )
        sql = 'select count(*) from student'
        cursor.execute(sql)
        results = cursor.fetchall()
        for row in results:
            count = row[0]
            print "count=%s" % count
        print '%s: %s ended.' % ( threadName, time.ctime(time.time()) )
        print(threading.current_thread().name, threading.current_thread().ident)
      conn.close()
t1 = threading.Thread(target=selectOne,args=("Thread-1",4,2, ),name='worker-01')
t2 = threading.Thread(target=selectOne,args=("Thread-2",4,4, ),name='worker-02')
t1.start()
t2.start()
t1.join()
t2.join()

• **查询的结果 **
  如下日志能看到，线程交替执行，模拟多线程查询

[root@db01 data]# python threads_select_stu.py
Thread-1: Sat Jun 16 20:57:00 2018 is running...
count=1119000
Thread-1: Sat Jun 16 20:57:00 2018 ended.
('worker-01', 139755490019072)
Thread-2: Sat Jun 16 20:57:02 2018 is running...
Thread-1: Sat Jun 16 20:57:02 2018 is running...
count=1119000
Thread-1: Sat Jun 16 20:57:03 2018 ended.
('worker-01', 139755490019072)
count=1119000
Thread-2: Sat Jun 16 20:57:03 2018 ended.
('worker-02', 139755479529216)
Thread-1: Sat Jun 16 20:57:05 2018 is running...
count=1119000
Thread-1: Sat Jun 16 20:57:05 2018 ended.
('worker-01', 139755490019072)
Thread-2: Sat Jun 16 20:57:07 2018 is running...
count=1119000
Thread-2: Sat Jun 16 20:57:12 2018 ended.
('worker-02', 139755479529216)
Thread-2: Sat Jun 16 20:57:16 2018 is running...
count=1119000
Thread-2: Sat Jun 16 20:57:17 2018 ended.
('worker-02', 139755479529216)