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 tps保持不變，原因是myisam是表鎖，同一時刻，該表只能寫或者只能讀
• innodb表隨著併發數的上升，insert..on duplicate tps不升反降，行鎖之前的爭用變大了 造成鎖等待
• 本次測試機器配置差，結果有些參考性，線上機器配置更好

註意 mysqlslap 壓測innodb表40個併發線程時可能會出現死鎖（RC隔離級別也是），死鎖詳細見最後
為什麼sysbench40 併發線程測試沒有出現過死鎖？難道sysbench併發線程不是同一時刻發出的？_{_}

/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