MySQL MyISAM和Innodb表生成序列

vlambda
2020-07-09

MySQL MyISAM和Innodb表生成序列

背景

应用端需要生成依次递增的序列来做流水序号等，方案有

1、redis /MySQL SEQUENCE引擎生成序列；

2、MySQL中myisam表 replace into方式；

3、MySQL中innodb表INSERT ... ON DUPLICATE KEY方式

分析

redis /MySQL SEQUENCE引擎生成序列，但多个MySQL集群都有生成序列的需求，若出问题，影响范围大；redis /MySQL SEQUENCE中生成序列也增加了研发修改代码的成本，新项目可以使用这种方式

MySQL中myisam表 replace into 是我们目前使用生成序列的方式（虽然是表锁，每秒生成的序列也满足得了需求），使用方式为

CREATE TABLE `test_sequence` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `val` (`val`)
) ENGINE=MyISAM;

>replace into test_sequence(val) values(99);
Query OK, 1 row affected (0.00 sec)

>select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

>replace into test_sequence(val) values(99);
Query OK, 2 rows affected (0.00 sec)

>select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)

但存在问题：
myisam表非事务存储引擎，备份存在不一致（恢复还原数据有不一致风险）；
myisam也不是crash-safe的；
gtid模式下，同一个事务中不能操作myisam表和innodb表为什么不用innodb表replace into方式了？
该方式并发大时，存在发生死锁的风险

MySQL中事务性 innodb表INSERT ... ON DUPLICATE KEY，是crash-safe ，看起来myisam生成序列的存在的问题它都没有！实际情况了？
使用方式：

CREATE TABLE `test_sequence2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `val` (`val`)
) ENGINE=InnoDB;

00>insert into test_sequence2(val) values(99) on duplicate key update id=id+1;
Query OK, 1 row affected (0.00 sec)

39>select id from test_sequence2;
+---------+
| id |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)

22>insert into test_sequence2(val) values(99) on duplicate key update id=id+1;
Query OK, 2 rows affected (0.00 sec)

25>select id from test_sequence2;
+---------+
| id |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)

测试

普通机械磁盘机器
MySQL5.7.16
RR隔离级别
sysbench 自定义sql语句测试tps（每秒生成多少序列）

myisam replace into 方式

cd /usr/share/sysbench/tests
sysbench ./test_myisam.lua  --mysql-host=127.0.0.1  --mysql-port=3701 --mysql-db=test --mysql-user=sysbench --mysql-password=sysbench --tables=1 --threads=10 --time=30 --report-interval=5  run

innodb INSERT ... ON DUPLICATE KEY UPDATE方式

cd /usr/share/sysbench/tests
sysbench ./test_innodb.lua  --mysql-host=127.0.0.1  --mysql-port=3701 --mysql-db=test --mysql-user=sysbench --mysql-password=sysbench --tables=1 --threads=10 --time=30 --report-interval=5  run

	myisam replace into	innodb insert..on duplicate
1并发线程	124 tps	122 tps
10并发线程	123 tps	121 tps
20并发线程	125 tps	104 tps
30并发线程	127 tps	67 tps
40并发线程	127 tps	33 tps

可见myisam随着并发线程数的增加，replace into tps保持不变，原因是myisam是表锁，同一时刻，该表只能写或者只能读

innodb表随着并发数的上升，insert..on duplicate tps不升反降，行锁之前的争用变大了造成锁等待
本次测试机器配置差，结果有些参考性，线上机器配置更好

注意 mysqlslap 压测innodb表40个并发线程时可能会出现死锁（RC隔离级别也是）。

/usr/local/mysql/bin/mysqlslap -usysbench -h127.0.0.1 -P3701 -p --concurrency=40 --iterations=1 --create-schema=test --query='insert into test_sequence2(val) values(99) on duplicate key update id=id+1;select id from test_sequence2;'
/usr/local/mysql/bin/mysqlslap: Cannot run query insert into test_sequence2(val) values(99) on duplicate key update id=id+1;select id from test_sequence2; ERROR : Deadlock found when trying to get lock; try restarting transaction

结论

myisam表 replace into生成序列是稳定的方法，不管并发线程数多少，生成序列速度是稳定的，但myisam表存在缺陷问题
innodb表 inert on duplicate 生成序列适合并发线程数少情况，并发线程数多会出现死锁生成序列速度下降情况
若要求生成序列的速度快，可用redis /MySQL SEQUENCE方式

死锁日志

LATEST DETECTED DEADLOCK
------------------------
2020-02-11 11:03:11 0x7f6a0c643700
*** (1) TRANSACTION:
TRANSACTION 39260727, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 28 lock struct(s), heap size 3520, 26 row lock(s), undo log entries 1
MySQL thread id 460828, OS thread handle 140093451958016, query id 21296424 127.0.0.1 root update
insert into test_sequence2(val) values(99) on duplicate key update id=id+1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 48 page no 4 n bits 72 index val of table `test`.`test_sequence2` trx id 39260727 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex e3; asc ;;
 1: len 8; hex 000000000000001a; asc ;;

*** (2) TRANSACTION:
TRANSACTION 39260729, ACTIVE 1 sec updating or deleting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
29 lock struct(s), heap size 3520, 27 row lock(s), undo log entries 1
MySQL thread id 460835, OS thread handle 140093451155200, query id 21296425 127.0.0.1 root update
insert into test_sequence2(val) values(99) on duplicate key update id=id+1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 48 page no 4 n bits 72 index val of table `test`.`test_sequence2` trx id 39260729 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex e3; asc ;;
 1: len 8; hex 000000000000001a; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 48 page no 3 n bits 168 index PRIMARY of table `test`.`test_sequence2` trx id 39260729 lock_mode X waiting
Record lock, heap no 37 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 000000000000001b; asc ;;
 1: len 6; hex 000002571237; asc W 7;;
 2: len 7; hex b6000001680110; asc h ;;
 3: len 1; hex e3; asc ;;

*** WE ROLL BACK TRANSACTION (1)

自定义sysbench脚本

less test_myisam/innodb.lua

require("oltp_common")


function thread_init(thread_id)
 drv=sysbench.sql.driver()
 con=drv:connect()
end

function event(thread_id)
local vid1
local dbprefix

con:query('replace into test_sequence(val) values(99)')
con:query('select last_insert_id()')

##innodb insert..on duplicate 语句
#con:query('insert into test_sequence2(val) values(99) on duplicate key update id=id+1')
#con:query('select id from test_sequence2;')

end

function thread_done()
 con:disconnect()
end