mysql数据库模拟是什么_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)