infobright与mysql_对比mysql一般引擎与infobright

#! /usr/bin/env python

#coding=utf-8

##################################

import MySQLdb,time,datetime

#建立和数据库系统的连接

#conn_src = MySQLdb.connect(host='10.28.174.237', user='root',passwd='123456',db='moblepush',port=3307)

conn_src = MySQLdb.connect(host='10.28.174.74', user='test',passwd='123456',db='mobilepush',port=3306)

#获取操作游标

cursor_src = conn_src.cursor()

cursor_src.execute("set autocommit = 1")

count_num_sql='''select count(1) t from fact_app_market_pin_plat '''

con_sql='''where ( browse_top5=654 OR browse_top1=830 OR browse_top2=830 OR

browse_top3=830 OR browse_top4=830 OR browse_top5=830 OR

browse_top1=654 OR browse_top2=654 OR browse_top3=654 OR

browse_top4=654 ) '''

fetch_data_sql='''select s_o_memberid from fact_app_market_pin_plat '''

print "----start time:"+datetime.datetime.now().strftime("%a, %d %b %Y %H:%M:%S +0000")

cursor_src.execute(count_num_sql+con_sql)

result = cursor_src.fetchone()

print "----end time:"+datetime.datetime.now().strftime("%a, %d %b %Y %H:%M:%S +0000")

total=result[0]

print "----start time:"+datetime.datetime.now().strftime("%a, %d %b %Y %H:%M:%S +0000")

pageSize=50000

for start in range(0,total,pageSize):

print "exec start"+str(start)+datetime.datetime.now().strftime("%a, %d %b %Y %H:%M:%S +0000")

cursor_src.execute(fetch_data_sql+con_sql+" limit "+str(start)+","+str(pageSize))

cursor_src.fetchone()

print "----end time:"+datetime.datetime.now().strftime("%a, %d %b %Y %H:%M:%S +0000")

conn_src.close();

3.测试数据700w左右结果1：

infobright：

----start time:Tue, 23 Jul 2013 13:51:02 +0000

----end time:Tue, 23 Jul 2013 13:51:04 +0000

----start time:Tue, 23 Jul 2013 13:51:04 +0000

exec start0Tue, 23 Jul 2013 13:51:04 +0000

exec start50000Tue, 23 Jul 2013 13:51:05 +0000

exec start100000Tue, 23 Jul 2013 13:51:06 +0000

exec start150000Tue, 23 Jul 2013 13:51:07 +0000

exec start200000Tue, 23 Jul 2013 13:51:09 +0000

----end time:Tue, 23 Jul 2013 13:51:11 +0000

mysql:

----start time:Tue, 23 Jul 2013 13:51:32 +0000

----end time:Tue, 23 Jul 2013 13:51:42 +0000

----start time:Tue, 23 Jul 2013 13:51:42 +0000

exec start0Tue, 23 Jul 2013 13:51:42 +0000

exec start50000Tue, 23 Jul 2013 13:51:44 +0000

exec start100000Tue, 23 Jul 2013 13:51:49 +0000

exec start150000Tue, 23 Jul 2013 13:51:55 +0000

exec start200000Tue, 23 Jul 2013 13:52:04 +0000

----end time:Tue, 23 Jul 2013 13:52:12 +0000

从结果得出：

mysqlinfobright

count：10s2s

fetch_data(20w左右分页5w):30s7s

分享到：

2013-07-23 14:27

浏览 563

评论