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表

基于arduino、百度云、采用django、redis鱼缸在线监控

为什么不用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隔离级别也是),死锁详细见最后
为什么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

原创文章,作者:admin,如若转载,请注明出处:https://www.2lxm.com/archives/3557.html