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?')



  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值