MySQL MyISAM和Innodb表生成序列

程序员考拉

共 5699字,需浏览 12分钟

 ·

2020-07-10 03:49

124627daf3f0d754237aab70be23ce9e.webp


背景


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


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 intoinnodb insert..on duplicate
1并发线程124 tps122 tps
10并发线程123 tps121 tps
20并发线程125 tps104 tps
30并发线程127 tps67 tps
40并发线程127 tps33 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


作者:jiaxin

出处:http://www.cnblogs.com/YangJiaXin/



浏览 23
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报