我在windows系统上有mysql5.5。从过去一个月以来,它运行缓慢。
以下是运行次数最多的查询:
begin
update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1
where roomlogs.netId = pnetid
and roomlogs.`status` = 0
and roomlogs.enterTime>=DATE(ptime)
and roomlogs.enterTime<DATE(ptime) + interval 1 day;
INSERT
into roomlogs (roomlogs.roomId, roomlogs.netId, roomlogs.enterTime,
roomlogs.exitTime, roomlogs.hidden, roomlogs.checkinId)
value
(pRoomId, pnetid,ptime, pexit, phidden, pcheckid);
select *
from roomlogs
where roomlogs.roomlogId = LAST_INSERT_ID();
end
以及:
begin
select count(*) as total
from roomlogs
where roomId = proomId
and roomlogs.`status` = 0
AND roomlogs.enterTime >= CURDATE()
AND roomlogs.enterTime < CURDATE() + INTERVAL 1 DAY
and roomlogs.hidden!=1;
end
这里的roomlogs索引是primary(roomlogid)、netid(netid,status,entertime)、roomid(roomid,status,entertime)
当mysql在服务器上同时运行更多的查询(大约100个)时,下面是我的innodb状态。
=====================================
2018-10-25 17:42:01 1728 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 20 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 148860 srv_active, 0 srv_shutdown, 271514 srv_idle
srv_master_thread log flush and writes: 420374
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 80056
OS WAIT ARRAY INFO: signal count 80039
Mutex spin waits 6920930, rounds 7674964, OS waits 52940
RW-shared spins 27042, rounds 803211, OS waits 26514
RW-excl spins 1173, rounds 9109, OS waits 211
Spin rounds per wait: 1.11 mutex, 29.70 RW-shared, 7.77 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 78706084
Purge done for trx's n:o < 78706079 undo n:o < 0 state: running but idle
History list length 2281
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 78705887, not started
MySQL thread id 3000, OS thread handle 0x8e0, query id 4915274 localhost 127.0.0.1 root cleaning up
**------------ SKIPPING SOME DATA HERE--------------**
---TRANSACTION 78701492, not started
MySQL thread id 15, OS thread handle 0x12c4, query id 4889345 localhost 127.0.0.1 root cleaning up
---TRANSACTION 78697207, not started
MySQL thread id 16, OS thread handle 0x408, query id 4889123 localhost 127.0.0.1 root cleaning up
---TRANSACTION 78701491, not started
MySQL thread id 14, OS thread handle 0x11c8, query id 4889320 localhost 127.0.0.1 root cleaning up
---TRANSACTION 78697202, not started
MySQL thread id 13, OS thread handle 0x10dc, query id 4889098 localhost 127.0.0.1 root cleaning up
---TRANSACTION 78706073, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 2989, OS thread handle 0x157c, query id 4916082 localhost 127.0.0.1 root updating
update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1 where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
roomlogs.enterTime>=DATE(ptime) and
roomlogs.enterTime<DATE(ptime) + interval 1 day
------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706073 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 4; hex 80000011; asc ;;
1: len 6; hex 0000028a4fc7; asc O ;;
2: len 7; hex c3000001ed0110; asc ;;
3: len 7; hex 726f6f6d313038; asc room108;;
4: len 7; hex 7973756e313032; asc ysun102;;
5: len 5; hex 999d1f25db; asc % ;;
6: len 5; hex 999d1d2a13; asc * ;;
7: len 4; hex 80000001; asc ;;
8: len 4; hex 80000000; asc ;;
9: len 4; hex 7fffffff; asc ;;
------------------
---TRANSACTION 78706056, ACTIVE 4 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 2942, OS thread handle 0x15e0, query id 4916017 localhost 127.0.0.1 root updating
update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1 where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
roomlogs.enterTime>=DATE(ptime) and
roomlogs.enterTime<DATE(ptime) + interval 1 day
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706056 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 4; hex 80000011; asc ;;
1: len 6; hex 0000028a4fc7; asc O ;;
2: len 7; hex c3000001ed0110; asc ;;
3: len 7; hex 726f6f6d313038; asc room108;;
4: len 7; hex 7973756e313032; asc ysun102;;
5: len 5; hex 999d1f25db; asc % ;;
6: len 5; hex 999d1d2a13; asc * ;;
7: len 4; hex 80000001; asc ;;
8: len 4; hex 80000000; asc ;;
9: len 4; hex 7fffffff; asc ;;
------------------
---TRANSACTION 78706045, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 2976, OS thread handle 0x125c, query id 4915976 localhost 127.0.0.1 root updating
update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1 where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
roomlogs.enterTime>=DATE(ptime) and
roomlogs.enterTime<DATE(ptime) + interval 1 day
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706045 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 4; hex 80000011; asc ;;
1: len 6; hex 0000028a4fc7; asc O ;;
2: len 7; hex c3000001ed0110; asc ;;
3: len 7; hex 726f6f6d313038; asc room108;;
4: len 7; hex 7973756e313032; asc ysun102;;
5: len 5; hex 999d1f25db; asc % ;;
6: len 5; hex 999d1d2a13; asc * ;;
7: len 4; hex 80000001; asc ;;
8: len 4; hex 80000000; asc ;;
9: len 4; hex 7fffffff; asc ;;
------------------
---TRANSACTION 78706009, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 2946, OS thread handle 0xb98, query id 4915824 localhost 127.0.0.1 root updating
update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1 where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
roomlogs.enterTime>=DATE(ptime) and
roomlogs.enterTime<DATE(ptime) + interval 1 day
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706009 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 4; hex 80000011; asc ;;
1: len 6; hex 0000028a4fc7; asc O ;;
2: len 7; hex c3000001ed0110; asc ;;
3: len 7; hex 726f6f6d313038; asc room108;;
4: len 7; hex 7973756e313032; asc ysun102;;
5: len 5; hex 999d1f25db; asc % ;;
6: len 5; hex 999d1d2a13; asc * ;;
7: len 4; hex 80000001; asc ;;
8: len 4; hex 80000000; asc ;;
9: len 4; hex 7fffffff; asc ;;
------------------
---TRANSACTION 78706004, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 2995, OS thread handle 0x1234, query id 4915806 localhost 127.0.0.1 root updating
update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1 where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
roomlogs.enterTime>=DATE(ptime) and
roomlogs.enterTime<DATE(ptime) + interval 1 day
------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78706004 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 4; hex 80000011; asc ;;
1: len 6; hex 0000028a4fc7; asc O ;;
2: len 7; hex c3000001ed0110; asc ;;
3: len 7; hex 726f6f6d313038; asc room108;;
4: len 7; hex 7973756e313032; asc ysun102;;
5: len 5; hex 999d1f25db; asc % ;;
6: len 5; hex 999d1d2a13; asc * ;;
7: len 4; hex 80000001; asc ;;
8: len 4; hex 80000000; asc ;;
9: len 4; hex 7fffffff; asc ;;
------------------
---TRANSACTION 78705997, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 2948, OS thread handle 0x1708, query id 4915783 localhost 127.0.0.1 root updating
update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1 where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
roomlogs.enterTime>=DATE(ptime) and
roomlogs.enterTime<DATE(ptime) + interval 1 day
------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78705997 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 4; hex 80000011; asc ;;
1: len 6; hex 0000028a4fc7; asc O ;;
2: len 7; hex c3000001ed0110; asc ;;
3: len 7; hex 726f6f6d313038; asc room108;;
4: len 7; hex 7973756e313032; asc ysun102;;
5: len 5; hex 999d1f25db; asc % ;;
6: len 5; hex 999d1d2a13; asc * ;;
7: len 4; hex 80000001; asc ;;
8: len 4; hex 80000000; asc ;;
9: len 4; hex 7fffffff; asc ;;
------------------
---TRANSACTION 78705985, ACTIVE 14 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 2986, OS thread handle 0x1640, query id 4915747 localhost 127.0.0.1 root updating
update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1 where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
roomlogs.enterTime>=DATE(ptime) and
roomlogs.enterTime<DATE(ptime) + interval 1 day
------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1461 page no 6 n bits 328 index `PRIMARY` of table `indoormap_v2_beta4`.`roomlogs` trx id 78705985 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 4; hex 80000011; asc ;;
1: len 6; hex 0000028a4fc7; asc O ;;
2: len 7; hex c3000001ed0110; asc ;;
3: len 7; hex 726f6f6d313038; asc room108;;
4: len 7; hex 7973756e313032; asc ysun102;;
5: len 5; hex 999d1f25db; asc % ;;
6: len 5; hex 999d1d2a13; asc * ;;
7: len 4; hex 80000001; asc ;;
8: len 4; hex 80000000; asc ;;
9: len 4; hex 7fffffff; asc ;;
------------------
---TRANSACTION 78705977, ACTIVE 16 sec fetching rows
mysql tables in use 1, locked 1
4610 lock struct(s), heap size 539064, 1001945 row lock(s)
MySQL thread id 2938, OS thread handle 0x10bc, query id 4915725 localhost 127.0.0.1 root updating
update roomlogs set roomlogs.exitTime = ptime,roomlogs.`status` = 1 where roomlogs.netId = pnetid and roomlogs.`status` = 0 and
roomlogs.enterTime>=DATE(ptime) and
roomlogs.enterTime<DATE(ptime) + interval 1 day
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
23467 OS file reads, 931766 OS file writes, 524775 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.00 writes/s, 1.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 39, seg size 41, 61 merges
merged operations:
insert 57, delete mark 5, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 4425293, node heap has 2003 buffer(s)
30.00 hash searches/s, 19.95 non-hash searches/s
---
LOG
---
Log sequence number 7396884835
Log flushed up to 7396884835
Pages flushed up to 7396717673
Last checkpoint at 7396717673
0 pending log writes, 0 pending chkp writes
164342 log i/o's done, 1.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2197815296; in additional pool allocated 0
Dictionary memory allocated 2793588
Buffer pool size 131072
Free buffers 101935
Database pages 27102
Old database pages 9967
Modified db pages 377
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 498, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 22500, created 4602, written 613885
0.00 reads/s, 0.10 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 27102, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 16384
Free buffers 12818
Database pages 3312
Old database pages 1240
Modified db pages 63
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 72, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2713, created 599, written 81358
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3312, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 16384
Free buffers 12809
Database pages 3319
Old database pages 1215
Modified db pages 27
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 15, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2770, created 549, written 78718
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3319, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 16384
Free buffers 12717
Database pages 3411
Old database pages 1256
Modified db pages 28
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 77, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2795, created 616, written 49290
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3411, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 16384
Free buffers 12733
Database pages 3395
Old database pages 1246
Modified db pages 51
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 45, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2780, created 615, written 91680
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3395, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 16384
Free buffers 12812
Database pages 3318
Old database pages 1217
Modified db pages 47
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 108, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2645, created 673, written 95064
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3318, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 16384
Free buffers 12590
Database pages 3538
Old database pages 1294
Modified db pages 51
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 35, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2905, created 633, written 89554
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3538, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 16384
Free buffers 12563
Database pages 3567
Old database pages 1307
Modified db pages 52
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 32, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3127, created 440, written 56311
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3567, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 16384
Free buffers 12893
Database pages 3242
Old database pages 1192
Modified db pages 58
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 114, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2765, created 477, written 71910
0.00 reads/s, 0.10 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3242, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 5080, state: sleeping
Number of rows inserted 97780, updated 862074, deleted 219, read 468159566176
1.55 inserts/s, 1.20 updates/s, 0.00 deletes/s, 3363648.47 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
因此,在这里您可以看到事务正在等待记录锁定,而且id为78705977的最后一个事务正在锁定1001945行,这应该是因为它没有更新那么多行。为什么要花16秒的时间来获取行呢?
以下是创建roomlogs表:
CREATE TABLE `roomlogs` (
`roomId` varchar(200) CHARACTER SET latin1 DEFAULT NULL,
`netId` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`enterTime` datetime DEFAULT NULL,
`exitTime` datetime DEFAULT NULL,
`roomlogId` int(11) NOT NULL AUTO_INCREMENT,
`status` int(11) DEFAULT '0',
`hidden` int(11) DEFAULT '0',
`checkinId` int(11) DEFAULT '-1',
PRIMARY KEY (`roomlogId`),
KEY `NETID` (`netId`,`status`,`enterTime`),
KEY `RoomLogIndex` (`roomId`,`status`,`enterTime`)
) ENGINE=InnoDB AUTO_INCREMENT=1315726 DEFAULT CHARSET=utf8
4条答案
按热度按时间oxalkeyp1#
我还不明白为什么
update
正在绊倒自己。但我看到一些事情可能会加速其他事情,从而间接地帮助:项目1,快速选择
可以(也许)用
注意:我假设这些是唯一的列,并且顺序是正确的。请提供
SHOW CREATE TABLE roomlogs
所以我们不用猜。第2项,覆盖指数
-->
这将是一个“覆盖指数”,从而加快
SELECT COUNT(*) ...
项目3,更好的优化如果
hidden
只有值0和1,则-->
优化器可以处理
=
比!=
. 在这一点上,这是一个更好的索引项目2:注意:我不建议在第2项中使用,因为
!=
.第4项,数据类型:
如果
status
不是某种形式的INT
,然后我会得到答案。再说一次,需要SHOW CREATE TABLE roomlogs
jtjikinw2#
通常情况下,争用不应该发生,因为status属性会同时更新。但是由于mvcc,不同的事务不能识别记录不再被更改。因此,它们都会看到相同的记录并锁定它们,即使它们不必这样做,因为状态已经更改。
我怀疑,对你来说,重要的是,一个改变已经完成。
此时,事务a(例如)根据事务开始时数据的状态,在所有记录看起来发生更改时锁定它们。
一旦所有记录都可以被a锁定,因为在开始时并行运行的所有事务都已提交,事务a就会锁定并获取这些记录,发现状态已经更改,并对剩余的事务进行更改。
更改之后,原来锁定的记录集将被解锁。
剩菜可能不多,但争论很激烈。
我看到的解决方案是:似乎有许多并行线程使用相同的netid更改记录。这能避免吗?
如果没有,我从未尝试过,但也许改变隔离级别会有所帮助:请参见:https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
wribegjk3#
尝试一下:
一个可能的原因是:在mysql的旧版本中,使用了不同的、不太复杂的优化器
UPDATE
以及DELETE
. 因此,升级可能是一个解决方案。检查错误。可能会出现超时或死锁。两者都需要处理。
wtzytmuj4#
(是的,我知道这是我的第三个答案。但这是另一个方向。)
考虑搬家
status
在table外面。这尤其是因为需要更改多行。另外,因为您有一个datetime,但状态与日期相关联。新table应该
你会的
JOIN
当您需要查看状态时,请转到表。当需要更改状态时,可以更新一行。你不一定会碰另一张table。
如果您需要一些行同时为status=0和status=1,这个想法可能会失败。但是,如果只有'older'行具有=0,则向表中添加时间会显示“status is 0 before the
time
". 这将使JOIN
有点复杂,但仍然可行。请告诉我们什么
status
“指”。并考虑更改列名以反映语义。