mysql innodbautoinclockmode,MySQL自增列锁模式 innodb_autoinc_lock_mode不同参数下性能测试...

20190701：感谢@接天居士的提示，在多核心CPU的配置环境下测试，确实是有差异的，感谢纠错，本来想删除这篇文章的，留着当错教训吧，测试环境差异造成的错误教训

对于innodb_autoinc_lock_mode 各种参数的值的含义，网上也有各种详解，看完觉得意犹未尽，这里不做阐述，只动手测试，看看性能上，到底有没有理论上所说的差别。

对于自增列的锁定，据说是innodb_autoinc_lock_mode = 2模式下有较高的性能，MySQL 8.0下innodb_autoinc_lock_mode 默认值为2。

于是通过修改改参数，测试不同参数下的一些性能表现，其结果还是比较出乎意料的……

测试环境：

MySQL 8.0.12 ON CentOS 7，1核1G内存

虽然环境资源配置有限，这里目的不是做性能测试，主要是对比在innodb_autoinc_lock_mode = 0和 2模式下的性能对比(没有做参数为1的情况)

测试方法：

本地Python开启多个线程，每个线程循环一定的数量，向某一个表中插入数据，看最终的时间表现情况

测试代码如下，开启20个线程，每个线程循环插入10000条数据，同时将当前线程Id写入当前数据行中(用来多个线程的执行是否是均匀或者说是交替的)，看最终的时间。

#coding=utf-8

importthreadingimportpymysqlfrom time importctime,sleep

connstr_tencent= {'host': '***.***.***.***', 'port': 3306, 'user': 'root', 'password': 'root', 'db': 'db01', 'charset': 'utf8mb4'}defaccess_mysql(para):

conn= pymysql.connect(host=connstr_tencent['host'],

port=connstr_tencent['port'],

user=connstr_tencent['user'],

password=connstr_tencent['password'],

db=connstr_tencent['db'],

charset=connstr_tencent['charset'],

connect_timeout= 100000)

cursor=conn.cursor()for i in range(10000):

cursor.execute("insert into test_autoicrement(col2,col3,col4) values ('thread:{0}','thread:{0}','thread:{0}');".format(str(para)))

cursor.close()

conn.commit()

conn.close()defmain():#生成线程

threads =[]for i in range(20):

t= threading.Thread(target=access_mysql, args=(i,))

threads.append(t)for t inthreads:

t.setDaemon(True)

t.start()for t inthreads:

t.join()if __name__ == '__main__':print("begin at {0}".format(ctime()))

main()print("finsh at {0}".format(ctime()))

测试结果1，未开启binlog的情况

测试结果2，开启binlog的情况

测试结果仅仅是本地软硬件环境下表出现来的结果，主要目的是不同参数的性能对比

测试说明：

1，每次修改innodb_autoinc_lock_mode 之后，truncate测试表，并重启MySQL服务。

2，innodb_autoinc_lock_mode =0或者2做交叉测试，

也即测试一次innodb_autoinc_lock_mode =0的，truncate 测试表，重启MySQL服务，然后再测试innodb_autoinc_lock_mode =2的情况。

3，不管是innodb_autoinc_lock_mode 为0或者2，(当前测试条件下)均未发现跳号的情况。

4，多线程测试下，记录都记录是当前线程的id，每次检验最终测试数据的分布情况，基本上都是每个线程均匀交替执行插入的，参考下图。

结论：

1，至少在MySQL 8.0下，innodb_autoinc_lock_mode 的值设置为0或者2的情况下，在性能上，没有发现明显的差异。

2，开启binlog的情况下，可能是测试数据量或者并发量不够，未发现比没有开启binlog有明显的性能下降。

另外：

一开始本地的Python连接5.7是没有问题的，连接MySQL8.0是直接报错，然后升级pymysql即可，

pymysql的版本笔者直接从0.7升级到0.9，不知道0.8版本的是否可以连接MySQL 8.0。

20190701：感谢@接天居士的提示，在多核心CPU的配置环境下测试，确实是有差异的，感谢纠错，本来想删除这篇文章的，留着当错教训吧，测试环境差异造成的错误教训