mysql insert 测试

测试插入 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 '点击记录，用于记录日志';

在 mysql 5.1.73-log 上测试记录如下：

单线程 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?')