mysql由于锁定而导致性能降低

7uhlpewt  于 2021-06-18  发布在  Mysql
关注(0)|答案(4)|浏览(335)

我在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
mysqlinnodblockingtransactions

4条答案

按热度按时间
rnmwe5a2

rnmwe5a21#

通常情况下，争用不应该发生，因为status属性会同时更新。但是由于mvcc，不同的事务不能识别记录不再被更改。因此，它们都会看到相同的记录并锁定它们，即使它们不必这样做，因为状态已经更改。
我怀疑，对你来说，重要的是，一个改变已经完成。
此时，事务a（例如）根据事务开始时数据的状态，在所有记录看起来发生更改时锁定它们。
一旦所有记录都可以被a锁定，因为在开始时并行运行的所有事务都已提交，事务a就会锁定并获取这些记录，发现状态已经更改，并对剩余的事务进行更改。
更改之后，原来锁定的记录集将被解锁。
剩菜可能不多，但争论很激烈。
我看到的解决方案是：似乎有许多并行线程使用相同的netid更改记录。这能避免吗？
如果没有，我从未尝试过，但也许改变隔离级别会有所帮助：请参见：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

赞(0）分享  回复(0）举报  2021-06-18
dgenwo3n

dgenwo3n2#

我还不明白为什么 update 正在绊倒自己。但我看到一些事情可能会加速其他事情，从而间接地帮助：
项目1，快速选择

select  *
   from  roomlogs
   where  roomlogs.roomlogId = LAST_INSERT_ID();

可以（也许）用

SELECT LAST_INSERT_ID(),
        pRoomId, pnetid, ptime, pexit, phidden, pcheckid;

注意：我假设这些是唯一的列，并且顺序是正确的。请提供 SHOW CREATE TABLE roomlogs 所以我们不用猜。
第2项，覆盖指数

INDEX (roomID, status, enterTime)

-->

INDEX (roomID, status, enterTime, hidden)

这将是一个“覆盖指数”，从而加快 SELECT COUNT(*) ... 项目3，更好的优化
如果 hidden 只有值0和1，则

hidden != 1

-->

hidden = 0

优化器可以处理 = 比 != . 在这一点上，这是一个更好的索引项目2：

INDEX (roomID, status, hidden, enterTime)

注意：我不建议在第2项中使用，因为 != .
第4项，数据类型：
如果 status 不是某种形式的 INT ，然后我会得到答案。再说一次，需要 SHOW CREATE TABLE roomlogs

赞(0）分享  回复(0）举报  2021-06-18
kwvwclae

kwvwclae3#

尝试一下：

begin
    START TRANSACTION;
        SELECT *
            FROM roomlogs
            WHERE  netId = pnetid
              AND  `status` = 0
              AND  enterTime >= DATE(ptime)
              AND  enterTime  < DATE(ptime) + interval 1 day;
            FOR UPDATE;    -- This might cause different locking action
            -- WHERE clause is equivalent to UPDATE's
        -- The following are as before (or as further optimized):
        UPDATE ...
        INSERT ...
        SELECT ...
    COMMIT;
end;

一个可能的原因是：在mysql的旧版本中，使用了不同的、不太复杂的优化器 UPDATE 以及 DELETE . 因此，升级可能是一个解决方案。
检查错误。可能会出现超时或死锁。两者都需要处理。

赞(0）分享  回复(0）举报  2021-06-18
iyfamqjs

iyfamqjs4#

（是的，我知道这是我的第三个答案。但这是另一个方向。）
考虑搬家 status 在table外面。这尤其是因为需要更改多行。另外，因为您有一个datetime，但状态与日期相关联。
新table应该

roomid ...
status TINYINT UNSIGNED NOT NULL (default 0?)
date  DATE NOT NULL   -- comes from  DATE(enterTime)
PRIMARY KEY(roomid)

你会的 JOIN 当您需要查看状态时，请转到表。
当需要更改状态时，可以更新一行。你不一定会碰另一张table。
如果您需要一些行同时为status=0和status=1，这个想法可能会失败。但是，如果只有'older'行具有=0，则向表中添加时间会显示“status is 0 before the time ". 这将使 JOIN 有点复杂，但仍然可行。
请告诉我们什么 status “指”。并考虑更改列名以反映语义。

赞(0）分享  回复(0）举报  2021-06-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com