來源：oracleace

在MySQL的資料庫中，我們有時會發現MySQL資料庫明明沒有負載，CPU、硬碟、記憶體和網路等資源都很空閒，但很多SQL都pending在哪兒，MySQL資料庫無法處理交易。這是怎麼回事呢？

關於號主，姚遠：

Oracle ACE（Oracle和MySQL資料庫方向）
華為雲最有價值專家
《MySQL 8.0運維與最佳化》的作者
擁有 Oracle 10g、12c和19c OCM等數十項資料庫認證
曾任IBM公司資料庫部門經理
20+年DBA經驗，服務2萬+客戶
精通C和Java，發明兩項計算機專利

在資料庫系統中出現這種情況通常是鎖競爭造成的，MySQL資料庫更加容易出現這種情況，因為它的儲存層和服務層是分開的，我們來看鎖競爭在MySQL 5.7和8.0裡的表現和解決辦法。

5.7版本中查詢鎖競爭

在MySQL 8.0 之前，必須SET GLOBAL innodb_status_output_locks=ON後才能在SHOW ENGINE INNODB STATUS中查到資料鎖，例如下面這個事務：

mysql> begin;Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where intcol1=0 for update;...900 rows in set (0.00 sec)

在SHOW ENGINE INNODB STATUS中查到

---TRANSACTION 7827, ACTIVE 11 sec222 lock struct(s), heap size 24784, 5720 row lock(s)MySQL thread id 9912, OS thread handle 139967683151616, query id 11123 localhost rootTABLE LOCK table `mysqlslap`.`t1` trx id 7827 lock mode IXRECORD LOCKS space id 25 page no 37 n bits 1056 index in_1 of table `mysqlslap`.`t1` trx id 7827 lock_mode X
...

修改了900行，卻鎖住了5720行。查詢space id為25對應的物件：

mysql> select * from information_schema.INNODB_SYS_DATAFILES where space=25;+-------+--------------------+| SPACE | PATH               |+-------+--------------------+|    25 | ./mysqlslap/t1.ibd |+-------+--------------------+1 row in set (0.00 sec)

在另外一個session裡執行

mysql> update t1 set intcol1=1 where intcol1=0;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

查詢鎖的情況


mysql> select * from information_schema.innodb_lock_waits;+-------------------+-------------------+-----------------+------------------+| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |+-------------------+-------------------+-----------------+------------------+| 7829              | 7829:25:4:2       | 7827            | 7827:25:4:2      |+-------------------+-------------------+-----------------+------------------+1 row in set, 1 warning (0.00 sec)
mysql> select * from information_schema.innodb_locks;+-------------+-------------+-----------+-----------+------------------+-----------------+------------+-----------+----------+----------------+| lock_id     | lock_trx_id | lock_mode | lock_type | lock_table       | lock_index      | lock_space | lock_page | lock_rec | lock_data      |+-------------+-------------+-----------+-----------+------------------+-----------------+------------+-----------+----------+----------------+| 7829:25:4:2 | 7829        | X         | RECORD    | `mysqlslap`.`t1` | GEN_CLUST_INDEX |         25 |         4 |        2 | 0x000000000200 || 7827:25:4:2 | 7827        | X         | RECORD    | `mysqlslap`.`t1` | GEN_CLUST_INDEX |         25 |         4 |        2 | 0x000000000200 |+-------------+-------------+-----------+-----------+------------------+-----------------+------------+-----------+----------+----------------+2 rows in set, 1 warning (0.00 sec)

查詢阻塞的執行緒：

SELECT b.trx_mysql_thread_id             AS 'blocked_thread_id'       ,b.trx_query                      AS 'blocked_sql_text'       ,c.trx_mysql_thread_id             AS 'blocker_thread_id'      ,c.trx_query                       AS 'blocker_sql_text'      ,( Unix_timestamp() - Unix_timestamp(c.trx_started) )                               AS 'blocked_time' FROM   information_schema.innodb_lock_waits a     INNER JOIN information_schema.innodb_trx b          ON a.requesting_trx_id = b.trx_id     INNER JOIN information_schema.innodb_trx c          ON a.blocking_trx_id = c.trx_id WHERE  ( Unix_timestamp() - Unix_timestamp(c.trx_started) ) > 4; +-------------------+-----------------------------------------+-------------------+------------------+--------------+| blocked_thread_id | blocked_sql_text                        | blocker_thread_id | blocker_sql_text | blocked_time |+-------------------+-----------------------------------------+-------------------+------------------+--------------+|              9921 | update t1 set intcol1=1 where intcol1=0 |              9917 | NULL             |          782 |+-------------------+-----------------------------------------+-------------------+------------------+--------------+1 row in set, 1 warning (0.00 sec)

根據執行緒號查詢執行的SQL

SELECT a.sql_text,        c.id,        d.trx_started FROM   performance_schema.events_statements_current a        join performance_schema.threads b          ON a.thread_id = b.thread_id        join information_schema.processlist c          ON b.processlist_id = c.id        join information_schema.innodb_trx d          ON c.id = d.trx_mysql_thread_id where c.id=9917ORDER  BY d.trx_started\G*************************** 1. row ***************************   sql_text: select * from t1 where intcol1=0 for update         id: 9917trx_started: 2023-05-26 13:24:591 row in set (0.00 sec)

注意這裡查詢出的SQL是阻塞事務的最後一條SQL，並不一定是阻塞的源頭。

解決鎖競爭

解決方法是針對where中的條件增加索引，使MySQL服務層的過濾能在儲存層完成，例如

mysql> create index in_1 on t1(intcol1);Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table t1\G*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `intcol1` int(32) DEFAULT NULL,  `intcol2` int(32) DEFAULT NULL,  `charcol1` varchar(128) DEFAULT NULL,  `charcol2` varchar(128) DEFAULT NULL,  `charcol3` varchar(128) DEFAULT NULL,  KEY `in_1` (`intcol1`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

再鎖住同樣的行

mysql> begin;Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where intcol1=0 for update;...900 rows in set (0.00 sec)

在SHOW ENGINE INNODB STATUS中查到

---TRANSACTION 7841, ACTIVE 15 sec155 lock struct(s), heap size 24784, 1801 row lock(s)MySQL thread id 9917, OS thread handle 139966608627456, query id 11227 localhost rootTABLE LOCK table `mysqlslap`.`t1` trx id 7841 lock mode IXRECORD LOCKS space id 25 page no 37 n bits 1056 index in_1 of table `mysqlslap`.`t1` trx id 7841 lock_mode X

...

被鎖住的記錄從之前的5720條減少到1801條。

有索引後執行計劃也不同，加索引之前的執行計劃

mysql> explain select * from t1 where intcol1=0 for update;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 6143 |    10.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

加了索引後的執行計劃是：

mysql> explain select * from t1 where intcol1=0 for update;+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+|  1 | SIMPLE      | t1    | NULL       | ref  | in_1          | in_1 | 5       | const |  900 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

對比一下可以發現後者的Extra欄位中沒有“Using where”，因為過濾再儲存層已經完成了。

生產中可以先 select 出主鍵id，再用主鍵id 去 update

8.0版本

從 MySQL 8.0 開始，performance_schema.data_locks顯示 InnoDB 資料鎖。具體參見拙作《MySQL 8.0運維於最佳化》第18章第3節“最佳化索引”。

為什麼MySQL沒有負載，但交易卻跑不動？

5.7版本中查詢鎖競爭

解決鎖競爭

8.0版本

相關文章