mysql 单表扫描大数据测试

环境
Intel(R) Core(TM) i3-3210 CPU @ 3.20GHz
DDR3 mem 4g
ST500DM002-1BD142 500g
xp sp3
mysqld 5.6.13

建表

CREATE TABLE `test100w` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`subject` varchar(200) CHARACTER SET utf8 NOT NULL,
`content` text CHARACTER SET utf8 NOT NULL,
`ctime` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

插入随机数据
import MySQLdb
from time import *
from time import clock
from random import *
start=clock()

now = time()

try:
cOnn=MySQLdb.connect(host='localhost',user='root',passwd='display',db='bench',port=3306,charset='utf8')
cur=conn.cursor()
i=1
while i< 100*10000:
rand=randint(10000, 99999)
sql = "insert into test100w(subject, content,ctime) values ('%s', '%s',%d)" % ("testdata_"+str(rand),"somedata_"+str(rand),now )
#query="select * from test200w where subject like '%%%s' " % str(rand)
print sql
try :
count=cur.execute(sql)
conn.commit()
except Exception,e:
print e
conn.rollback()

i=i+1

cur.close()

conn.close()
except MySQLdb.Error,e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
finish=clock()
print (finish-start)/1000000


查询
import MySQLdb
from time import clock
from random import *
start=clock()


try:
cOnn=MySQLdb.connect(host='localhost',user='root',passwd='display',db='bench',port=3306,charset='utf8')
cur=conn.cursor()
rand=randint(10000, 99999)
query="select * from test100w where subject like '%%%s' " % str(rand)
print query
count=cur.execute(query)
print 'there has %s rows record' % count
result=cur.fetchone()
print result
#print 'ID: %s info %s' % result
#results=cur.fetchmany(5)
#for r in results:
# print r
cur.close()
conn.close()
except MySQLdb.Error,e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
finish=clock()
print (finish-start)/1000000

结果
100w 1.45214545424e-06
200w 2.183407109e-06
400w 4.3417384053e-06