测试插入 1000000 条记录,表结构分别如下:
create table if not exists t_click(
id int(11) unsigned auto_increment primary key,
curl varchar(256) default '' comment '',
rurl varchar(256) default '' comment '',
headers varchar(1024) default '' comment '',
ctime datetime not null
)ENGINE=Innodb AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 comment '点击记录,用于记录日志';
create table if not exists t_click(
id int(11) unsigned auto_increment primary key,
curl varchar(256) default '' comment '',
rurl varchar(256) default '' comment '',
headers varchar(1024) default '' comment '',
ctime datetime not null
)ENGINE=Myisam AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 comment '点击记录,用于记录日志';
单线程 myisam 插入 1000000 条记录,花费时间 981.554 秒,大约 16.36 秒;第二次花费时间 977.062 秒,大约 16.28 秒;
30个线程 myisam 并发插入 1000000 条记录,花费 172.557 秒;
单线程 innodb 插入 1000000 条记录,花费时间 6785 秒,已经不是一个数量级了;
30个线程没有测试。
在 mysql 5.7.16 上测试:
myisam 单线程 123.74 秒插入 1000000 条记录;30 个线程同时插入, 95.84 秒;
innodb 单线程 123.66 秒插入 1000000 条记录;30 个线程同时插入, 99.06 秒;
已经没什么区别了
考虑到 myisam 引擎已经不再更新(https://www.oschina.net/news/77072/mysql-8-0-dmr),建议统一使用 innodb
#!python
#-*- coding:utf-8 -*-
'''
测试 insert 1000000 条记录时,innodb 和 myisam 的性能,分别测试单线程和30个线程的,测试的表如下:
CREATE TABLE `t_click` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`curl` varchar(256) DEFAULT '',
`rurl` varchar(256) DEFAULT '',
`headers` varchar(1024) DEFAULT '',
`ctime` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7437 DEFAULT CHARSET=utf8 COMMENT='点击记录,用于记录日志';
CREATE TABLE `t_click1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`curl` varchar(256) DEFAULT '',
`rurl` varchar(256) DEFAULT '',
`headers` varchar(1024) DEFAULT '',
`ctime` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=Innodb AUTO_INCREMENT=7437 DEFAULT CHARSET=utf8 COMMENT='点击记录,用于记录日志';
'''
import os
import sys
import time
import threading
import mysql.connector
from mysql.connector.pooling import MySQLConnectionPool
mysqlConfig = {'host': '1.2.3.4', 'user': 'a', 'password': 'ff',
'port': 3306, 'database': 'test', 'charset': 'utf8', 'autocommit': 1
}
userAgent = '{"host":["www.baidu.com"],"user-agent":["Mozilla\/5.0 (Linux; Android 6.0.1; SAMSUNG SM-G935F Build\/MMB29K) AppleWebKit\/537.36 (KHTML, like Gecko) SamsungBrowser\/4.0 Chrome\/44.0.2403.133 Mobile Safari\/537.36"],"accept":["text\/html,application\/xhtml+xml,application\/xml;q=0.9,*\/*;q=0.8"],"accept-language":["pt-br"],"accept-encoding":["gzip, deflate"],"upgrade-insecure-requests":["1"],"cache-control":["max-age=0"],"connection":["keep-alive"]}'
userAgent = userAgent.replace('"', '\\"')
sql = 'insert into t_click value (NULL, "http://www.baidu.com", ' \
'"http://www.baidu.com", "%s", "2016-12-29 17:52:25")' % (userAgent)
sql1 = 'insert into t_click1 value (NULL, "http://www.baidu.com", ' \
'"http://www.baidu.com", "%s", "2016-12-29 17:52:25")' % (userAgent)
def testMyisam(cur):
cur.execute(sql)
def testInnodb(cur):
cur.execute(sql1)
def testOneThread():
'''
单线程测试
:return:
'''
con = mysql.connector.Connect(**mysqlConfig)
cur = con.cursor()
start = time.time()
for i in range(0, 1000000, 1):
testMyisam(cur)
end = time.time()
print('myisam: start %s, end %s, spend %s' % (str(start), str(end), str(end - start)))
start = time.time()
for i in range(0, 1000000, 1):
testInnodb(cur)
end = time.time()
print('innodb: start %s, end %s, spend %s' % (str(start), str(end), str(end - start)))
cur.close()
con.close()
## 用来计数;总计 1000000 万
semaphore = threading.Semaphore(1000000)
def testWithSem(cur, sql):
while semaphore.acquire(False):
cur.execute(sql)
def test30ThreadMyisam(localMysqlPool):
'''
100个线程并发测试
:return:
'''
global semaphore
ths = []
cons = []
start = time.time()
for i in range(0, 30, 1):
con = localMysqlPool.get_connection()
cur = con.cursor()
cons.append(con)
th = threading.Thread(target=testWithSem, args=(cur,sql))
ths.append(th)
th.start()
for i in ths:
i.join()
## 所有线程结束表示执行完毕
end = time.time()
print('myisam 30 thread: start %s, end %s, spend %s' % (str(start), str(end), str(end - start)))
for i in cons:
i.close()
def test30ThreadInnodb(localMysqlPool):
## 修改全局变量
ths = []
cons = []
start = time.time()
for i in range(0, 30, 1):
con = localMysqlPool.get_connection()
cur = con.cursor()
cons.append(con)
th = threading.Thread(target=testWithSem, args=(cur, sql1))
ths.append(th)
th.start()
for i in ths:
i.join()
## 所有线程结束表示执行完毕
end = time.time()
print('innodb 30 thread: start %s, end %s, spend %s' % (str(start), str(end), str(end - start)))
for i in cons:
i.close()
if __name__ == '__main__':
if len(sys.argv) > 1:
if sys.argv[1] == '1':
testOneThread()
elif sys.argv[1] == '30m':
localMysqlPool = MySQLConnectionPool(pool_size=30, pool_name='p%s' % str(os.getpid()), **mysqlConfig)
test30ThreadMyisam(localMysqlPool)
elif sys.argv[1] == '30i':
localMysqlPool = MySQLConnectionPool(pool_size=30, pool_name='p%s' % str(os.getpid()), **mysqlConfig)
test30ThreadInnodb(localMysqlPool)
else:
print('what do you prepare to do?')