python连接mysql，并在中间用memcached保存sql结果
我在python和mysql中间加了一层memcached中间层，缓存sql查询的结果，以期望获得更好的系统性能。参考：
http://www.cnblogs.com/rollenholt/archive/2012/05/29/2524327.html
http://www.the5fire.com/python-opt-mysql.html
python连接mysql需要先安装一些lib，我是ubuntu，比较easy，直接apt-get
sudo apt-get install libmysqld-dev
sudo apt-get install libmysqlclient-dev
sudo apt-get install python-mysqldb
然后就可以写python去connect mysql啦，当然，一开始，mysql的数据库里面是神马也没有的，要自己先去create一个数据库，然后再create table，insert data。下面是建表和插入数据的python代码
1 #!/usr/bin/env python
2
3 # 20140105,create_table.py
4
5 import MySQLdb
6
7 try:
8 conn=MySQLdb.connect(host='192.168.1.6',user='dba',passwd='111111',port=3306)
9 cur=conn.cursor()
10
11 #cur.execute('create database if not exists testdb')
12 conn.select_db('testdb')
13 cur.execute('create table id_info(id int,info varchar(20))')
14
15 # value=
16 # cur.execute('insert into id_info values(%s,%s)',value)
17
18 rg = 100000
19 values=[]
20 for i in range(rg):
21 values.append((i,'aaa'+str(i)))
22
23 cur.executemany('insert into id_info values(%s,%s)',values)
24
25 values=[]
26 for i in range(rg):
27 values.append((i+rg,'bbb'+str(i)))
28
29 cur.executemany('insert into id_info values(%s,%s)',values)
30
31 values=[]
32 for i in range(rg):
33 values.append((i+2*rg,'ccc'+str(i)))
34
35 cur.executemany('insert into id_info values(%s,%s)',values)
36
37 # cur.execute('update id_info set info="I am rollen" where id=3')
38
39 conn.commit()
40 cur.close()
41 conn.close()
42
43 except MySQLdb.Error,e:
44 print "Mysql Error %d: %s" % (e.args, e.args)
经过以上代码的运行，testdb这个数据库里面就有了一个id_info表，并且这个表里面还有了300000行数据。然后是连接mysql并执行select，我比较喜欢用面向对象的方式来写代码，所以就把连接mysql的程序做了一点封装
1 #!/usr/bin/env python
2
3 # 20140105,conn_mysql.py
4
5 import MySQLdb
6
7 class conn_mysql(object):
8 def __init__(self):
9 print "init mysql"
10
11 def __del__(self):
12 print "quit mysql"
13
14 def connect_db(self):
15 self.conn=MySQLdb.connect(host='192.168.1.6',user='dba',passwd='111111',db='testdb',port=3306)
16 self.cur=self.conn.cursor()
17 self.conn.select_db('testdb')
18
19 def test_select(self):
20 count=self.cur.execute('select * from id_info')
21 print 'there has %s rows record' % count
22
23 result=self.cur.fetchone()
24 print result
25 print 'ID: %s info %s' % result
26
27 results=self.cur.fetchmany(5)
28 for r in results:
29 print r
30
31 print '=='*10
32 self.cur.scroll(0,mode='absolute')
33
34 results=self.cur.fetchall()
35 for r in results:
36 print r
37
38 self.conn.commit()
39
40 def test_count(self, str_sql):
41 count=self.cur.execute(str_sql)
42 # print 'there has %s rows record' % count
43
44 result=self.cur.fetchone()
45 # print 'id_info has %s rows' % result
46 str_rows = '%s' % result
47 return str_rows
48
49 def disconnect_db(self):
50 self.cur.close()
51 self.conn.close()
用test_select方法，来测试是否连接上，talbe里面数据很多，我是在只有30条数据的时候运行这个测试，之后实验中就一直是用test_count。test_count这个函数的意思是，对输入的sql，其类似格式是“select count(*) from ......”这样的时候，就把结果以字符串形式返回。下面是测试程序
1 #!/usr/bin/env python
2
3 # 20140105,conn_mysql_raw.py
4
5 import conn_mysql
6
7 str_sql = 'select count(*) from id_info'
8
9 db_connect = conn_mysql.conn_mysql()
10 db_connect.connect_db()
11
12 for i in range(10000):
13 str_rows = db_connect.test_count('select count(*) from id_info where info like \'bbb%\'')
14
15 print(str_rows + ' rows selected.')
16
17 db_connect.disconnect_db()
针对之前的数据，运行10000次sql，select count(*) from id_info where info like 'bbb%'，当然，每次的返回结果都是100000，主要是测试这个程序的运行时间。在我的机器上，时间是1.9s。
然后是在mysql前面加入一层memcached，需要先下载python-memcached-latest.tar.gz，这个自行google吧，apt-get源里面似乎是没有。我拿到的版本是python-memcached-1.53。安装python-memcached之前要先安装python-setuptools，不然会报错“ImportError: No module named 'setuptools'”
tar zxvf python-memcached-latest.tar.gz
cd python-memcached-1.53/
sudo apt-get install python-setuptools
sudo python setup.py install
然后，把memcached启动，run一段python代码测试一下
1 #!/usr/bin/env python
2 # 20140105, test_memcached.py
3
4 import memcache
5
6 mc = memcache.Client(['localhost:11211'],debug=0)
7 mc.set("foo","bar")
8 value = mc.get("foo")
9 print value
看到输出是“bar”就说明已经连上memcached了。下面就要用memcached做mysql的缓存，看性能能提升到什么程度。先改写mysql连接的封装类
1 #!/usr/bin/env python
2
3 # 20140105,conn_mysql.py
4
5 import MySQLdb
6 import memcache
7 import hashlib
8
9 class conn_mysql(object):
10 def __init__(self):
11 print "init mysql"
12
13 def __del__(self):
14 print "quit mysql"
15
16 def connect_db(self):
17 self.conn=MySQLdb.connect(host='192.168.1.6',user='dba',passwd='111111',db='testdb',port=3306)
18 self.cur=self.conn.cursor()
19 self.conn.select_db('testdb')
20
21 def test_select(self):
22 count=self.cur.execute('select * from id_info')
23 print 'there has %s rows record' % count
24
25 result=self.cur.fetchone()
26 print result
27 print 'ID: %s info %s' % result
28
29 results=self.cur.fetchmany(5)
30 for r in results:
31 print r
32
33 print '=='*10
34 self.cur.scroll(0,mode='absolute')
35
36 results=self.cur.fetchall()
37 for r in results:
38 print r
39
40 self.conn.commit()
41
42 def test_count(self, str_sql):
43 count=self.cur.execute(str_sql)
44 # print 'there has %s rows record' % count
45
46 result=self.cur.fetchone()
47 # print 'id_info has %s rows' % result
48 str_rows = '%s' % result
49 return str_rows
50
51 def connect_cache(self):
52 self.mc = memcache.Client(['localhost:11211'],debug=0)
53
54 def test_count_cached(self, str_sql):
55 str_hash = hashlib.md5(str_sql).hexdigest()
56 #str_hash = myhash(str_sql)
57
58 result = self.mc.get(str_hash)
59 if result != None:
60 # str_org_sql = self.mc.get('SQL'+str_hash)
61 # if str_org_sql == str_sql:
62 str_rows = '%s' % result
63 return str_rows
64
65 count = self.cur.execute(str_sql)
66 # print 'there has %s rows record' % count
67
68 result = self.cur.fetchone()
69 self.mc.set(str_hash, result)
70 self.mc.set('SQL'+str_hash, str_sql)
71 # print(str_hash)
72 # print 'id_info has %s rows' % result
73 str_rows = '%s' % result
74 return str_rows
75
76 def disconnect_db(self):
77 self.cur.close()
78 self.conn.close()
增加memcached相关的配置信息，增加测试函数test_count_cached，先对输入的sql做字符串hash（我用的md5），以这个hash值为key去memcached中查找有没有结果，如果有就直接返回；否则再去mysql中查询，并把查询的结果做value，sql的hash值做key，存在memcached中。run这个测试函数
1 #!/usr/bin/env python
2
3 # 20140105,conn_mysql_memcached.py
4
5 import conn_mysql
6
7 str_sql = 'select count(*) from id_info where info like \'bbb%\''
8
9
10 db_connect = conn_mysql.conn_mysql()
11 db_connect.connect_db()
12 db_connect.connect_cache()
13
14 for i in range(10000):
15 str_rows = db_connect.test_count_cached(str_sql)
16
17 print(str_rows + ' rows selected.')
18
19 db_connect.disconnect_db()
经过改进之后的test_count_cached的运行时间是1.0s，改进并不如我期望的大。可能的原因，我的mysql是装在本机上的，没有网络通讯的开销，一般情况下，mysql是在单独的数据库服务器上，而memcached是在业务服务器上，做一次sql查询是有网络开销的，所以在这种场景下，效果应该会更明显。
实验中还有一个小问题，我在对比sql文本的时候只对比了hash值，并没有对比sql文本本身，如果进行这样的对比，势必会造成性能下降。事实也是如此，我加入这段对比之后，test_count_cached的运行时间变为1.8s，不考虑误差的话，运行时间基本上是刚才的case的2倍。这也很显然，因为主要的开销都是在获取memcached中的结果，加入sql文本对比的同时也多了1次获取memcached结果的消耗。
页:
[1]