由于锁定，MYSQL性能下降 [英] MYSQL slow performance because of Locking
本文介绍了由于锁定，MYSQL性能下降的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在Windows系统上有Mysql5.5.自过去1个月以来，它运行缓慢.
I have Mysql5.5 on windows system. Since past 1 month it is running slow.
以下是运行最多的查询:
Here is the query which are running the most:
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
AND:
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
这里的房间日志的索引是主(roomlogId)，NETID(netid，状态，enterTime)，ROOMID(房间ID，状态，enterTime)
Here index of roomlogs are primary(roomlogId), NETID(netid,status,enterTime), ROOMID(roomID,status,enterTime)
当MySQL在服务器中同时运行更多查询(大约100个)时，这是我的InnoDB状态.
Here is my InnoDB status when MySQL has more number of queries (around 100) running simultaneously in the server.
=====================================
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秒之类的行?
So, Here you can see that transaction was waiting for a record lock, Also the last transaction with ID 78705977 is locking 1001945 rows which it should be as it is not updating that much rows. Also Why it is taking time to fetch the rows like 16 seconds?
这里是创建房间日志表:
Here is create Table of 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
推荐答案
(是的，我知道这是我的第三个答案.但这是一个不同的方向.)
(Yeah, I know this is my 3rd answer. But it is in a different direction.)
请考虑将status从表中移出.尤其是因为需要更改多行.另外，由于您有日期时间，但状态与日期相关.
Consider moving status out of the table. This is especially since multiple rows need to be changed. Also since you have a datetime, but the status is associated with a date.
新表将具有
roomid ...
status TINYINT UNSIGNED NOT NULL (default 0?)
date DATE NOT NULL -- comes from DATE(enterTime)
PRIMARY KEY(roomid)
需要查看状态时，您将JOIN移到表格中.
You would JOIN to the table when you need to see the status.
当您需要更改状态时，您将更新 one 行.而且您不一定要碰另一张桌子.
You would update the one row when you need to change the status. And you would not necessarily touch the other table.
如果您需要同时将某些行的状态设置为status = 0，同时将某些状态设置为= 1，则此想法可能会失败.但是，如果只有较旧的"行具有= 0，则在表中添加时间将显示"time之前的状态为0".这会使JOIN稍微复杂一些，但仍然可行.
This idea will probably fail if you need some rows to be status=0 and some status=1 simultaneously. However, if only 'older' rows have =0, then adding a time to the table would say "status is 0 before the time". This would make the JOIN a bit more complex, but still doable.
请告诉我们status是什么意思.并考虑更改列名以反映语义.
Please tell us what status "means". And consider changing the column name to reflect the semantics.
这篇关于由于锁定，MYSQL性能下降的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
