MySQL MyISAM和Innodb表生成序列

背景

應(yīng)用端需要生成依次遞增的序列來(lái)做流水序號(hào)等，方案有

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

2、MySQL中myisam表 replace into方式；

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

分析

• redis /MySQL SEQUENCE引擎生成序列，但多個(gè)MySQL集群都有生成序列的需求，若出問(wèn)題，影響范圍大；redis /MySQL SEQUENCE中生成序列也增加了研發(fā)修改代碼的成本，新項(xiàng)目可以使用這種方式

• 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)

但存在問(wèn)題：
myisam表非事務(wù)存儲(chǔ)引擎，備份存在不一致（恢復(fù)還原數(shù)據(jù)有不一致風(fēng)險(xiǎn)）；
myisam也不是crash-safe的；
gtid模式下，同一個(gè)事務(wù)中不能操作myisam表和innodb表為什么不用innodb表replace into方式了？
該方式并發(fā)大時(shí)，存在發(fā)生死鎖的風(fēng)險(xiǎn)

• MySQL中事務(wù)性 innodb表INSERT ... ON DUPLICATE KEY，是crash-safe ，看起來(lái)myisam生成序列的存在的問(wèn)題它都沒(méi)有！實(shí)際情況了？
  使用方式：

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)

測(cè)試

普通機(jī)械磁盤機(jī)器
MySQL5.7.16
RR隔離級(jí)別
sysbench 自定義sql語(yǔ)句測(cè)試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

• 可見(jiàn)myisam隨著并發(fā)線程數(shù)的增加，replace into tps保持不變，原因是myisam是表鎖，同一時(shí)刻，該表只能寫或者只能讀

• innodb表隨著并發(fā)數(shù)的上升，insert..on duplicate tps不升反降，行鎖之前的爭(zhēng)用變大了 造成鎖等待
• 本次測(cè)試機(jī)器配置差，結(jié)果有些參考性，線上機(jī)器配置更好

注意 mysqlslap 壓測(cè)innodb表40個(gè)并發(fā)線程時(shí)可能會(huì)出現(xiàn)死鎖（RC隔離級(jí)別也是）。

/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

結(jié)論

• myisam表 replace into生成序列是穩(wěn)定的方法，不管并發(fā)線程數(shù)多少，生成序列速度是穩(wěn)定的，但myisam表存在缺陷問(wèn)題
• innodb表 inert on duplicate 生成序列適合并發(fā)線程數(shù)少情況，并發(fā)線程數(shù)多會(huì)出現(xiàn)死鎖 生成序列速度下降情況
• 若要求生成序列的速度快，可用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?語(yǔ)句
#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

作者：jiaxin

出處：http://www.cnblogs.com/YangJiaXin/