一、环境准备

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数据库中创建表格t CREATE 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 Manager import time # This will initialise both reader and writer connections to the default hosts time1 = 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 > 0 def 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 - time1 print 'delete time is:\t\t', time3 - time2 print 'insert time is:\t\t', time4 - time3 print 'getdata time is:\t', time5 - time4 print 'whole exec time is:\t', time5 - time1

3、MySQLdb

MySQLdb_test.py

import MySQLdb import time my_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 data def 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.py connect time is: 4.50611114502e-05 delete time is: 0.00116300582886 insert time is: 364.271649122 getdata time is: 2.5710170269 whole exec time is: 366.843874216 python MySQLdb_test.py connect time is: 0.0189650058746 s delete time is: 0.127696037292 s insert time is: 2.96214795113 s getdata time is: 2.973539114 s close time is: 6.103515625e-05 s whole exec time is: 6.08240914345 s

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