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数据库中创建表格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%左右。