pyhs(0.2.4) + MySQLdb性能比较

一、环境准备

• 1.MySQL和Handlersocket：percona mysql5.5 + Handlersocket安装与实践
• 2.Python和MySQLdb：linux下MySQLdb模块简便安装
• 3.Python和Handlersocket：pyhs(0.2.4):python2.7+handlersocket

---

二、性能比较

性能将从数据的删除、插入、查询三个方面进行比较，测试的数据量为10000条。

1、测试表格准备

# 登录MySQL中，在test数据库中创建表格tCREATE TABLE IF NOT EXISTS `test`.`t` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `a` varchar(10) NOT NULL,  `b` varchar(10) NOT NULL,  PRIMARY KEY (`id`),  KEY `a_b` (`a`,`b`)) ENGINE=InnoDB;

2、pyhs(0.2.4)

pyhs_test.py

# -*- coding: utf-8 -*- from pyhs import Managerimport time# This will initialise both reader and writer connections to the default hoststime1 = time.time()hs = Manager()# 插入一条数据到数据库'test',表格't'# insert into test.t (id,a,b) values (1,'a1','b1')# hs.delete('test', 't', [('id', '4')])def insert_db():    for i in xrange(1,10000):           hs.insert('test', 't', [('id', str(i)), ('a', 'a'+str(i)), ('b', 'b'+str(i))])# 条件查找，得到一条数据# select * from test.t where id = i;def get_db():    for i in xrange(1,10000):        data = hs.get('test', 't', ['id', 'a', 'b'], '%d' % i)        # print data# 删除数据# delete from test.t where id > 0def delete_db():    hs.delete('test', 't', '>', ['id', 'a', 'b'], ['0'], limit=10000)time2 = time.time()delete_db()time3 = time.time()insert_db()time4 = time.time()get_db()time5 = time.time()print 'connect time is:\t', time2 - time1print 'delete time is:\t\t', time3 - time2print 'insert time is:\t\t', time4 - time3print 'getdata time is:\t', time5 - time4print 'whole exec time is:\t', time5 - time1

3、MySQLdb

MySQLdb_test.py

import MySQLdbimport timemy_host = '127.0.0.1'my_user = 'root'my_pass = '1234'my_db = 'test'time1 = time.time()db = MySQLdb.connect(host=my_host, user=my_user, passwd=my_pass, db=my_db)cursor = db.cursor(MySQLdb.cursors.DictCursor)def insert_db():    for i in xrange(1,10000):        #sql = 'insert into t values(%d, %s, %s)'% (int(i),str(i),str(i))        sql = 'insert into t values(%d'%i        sql = sql + ',%s, %s)'        #print sql        #cursor.execute(sql)        cursor.execute(sql,['a'+str(i), 'b'+str(i)])    db.commit()def delete_db():    sql = 'delete from t where id >0'    cursor.execute(sql)    db.commit()def get_db():    for i in xrange(1,10000):        sql = 'select * from t where id = %d'%i        cursor.execute(sql)        data = cursor.fetchall()        # print datadef close():    cursor.close()    db.close()time2 = time.time()delete_db()time3 = time.time()insert_db()time4 = time.time()get_db()time_get = time.time()close()time5 = time.time()print 'connect time is:\t',   time2 - time1, ' s'print 'delete time is:\t\t',  time3 - time2, ' s'print 'insert time is:\t\t',  time4 - time3, ' s'print 'getdata time is:\t',   time_get - time4, 's'print 'close time is:\t\t',   time5 - time_get, ' s'print 'whole exec time is:\t', time5 - time1, ' s'

---

4、结果与分析

python pyhs_test.pyconnect time is:    4.50611114502e-05delete time is:     0.00116300582886insert time is:     364.271649122getdata time is:    2.5710170269whole exec time is: 366.843874216python MySQLdb_test.pyconnect time is:    0.0189650058746  sdelete time is:     0.127696037292  sinsert time is:     2.96214795113  sgetdata time is:    2.973539114 sclose time is:      6.103515625e-05  swhole exec time is: 6.08240914345  s

分析：可知，pyhs在删除和查找数据较MySQLdb要快，但是插入数据则要慢很多。最重要的查找性能要好13.5%左右。