MySQL經典案例分析

玉樹臨楓發表於2021-10-17

原文網址 : https://www.cnblogs.com/yuanfy008/p/15415900.html

MySql

一、前言

前面說了一些概念，比如事務、MVCC、鎖等，對Innodb有了個大概瞭解。這次通過一個經典案例來將這些串起來回顧下。

二、經典案例

面試官：select * from t1 where id = 10；這個SQL語句加了哪些鎖，你能說說嗎？

正在面試的某某，不自信的說：這個應該不加鎖吧。

面試官：delete from t1 where id = 10; 那這個SQL呢？

正在面試的某某，忽然停頓了....

上面的問題相信大家或多或少都有遇到過，這裡我也拿來說說。

其實這兩個都是開放題，背景需要自己去定，不同背景對應加鎖情況也許就不一樣。

這裡第一個問題比第二個問題簡單，所以先回答下第一個問題，因為它差不多全與事務隔離級別有關。

在讀未提交（READ UNCOMMITTED）這種事務隔離級別下，是以非鎖定讀取，所以是不會加鎖的。
而在讀已提交（READ COMMITTED）和可重複讀（REPEATABLE READ）這兩種事務隔離級別下，情況是一樣的，使用的是快照讀，都是不會加鎖的。

如果是當前讀，則會涉及到加鎖的情況。
如果在序列讀（SERIALIZABLE）這種事務隔離級別下，不支援快照讀，會給select隱藏的加上讀共享鎖。

所以看一個這麼簡單的問題，分情況對應的結果就可能有所不同。

下面看看第二個問題，第二個問題涉及的場景多些，需要從事務隔離級別以及id是什麼索引對應的組合說明下。

注意： InnoDB存在四種隔離級別，第一種事務（讀未提交：READ UNCOMMITTED）由於存在太多問題所以就不加入舉例中說明。
第四種事務（序列讀：SERIALIZABLE）, 在刪除的情況和RR隔離級別是一樣的。

下面是想的幾種情況：

1、RC + id是主鍵
2、RC + id是唯一鍵
3、RC + id是普通索引
4、RC + id無索引
5、RR + id是主鍵
6、RR + id是唯一鍵
7、RR + id是普通索引
8、RR + id無索引

RC = 讀已提交（READ COMMITTED）， RR = （REPEATABLE READ）

在說明上述場景的前提下我們以這個表為基礎進行說明

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1、RC + id是主鍵

select @@transaction_isolation;

set @@session.transaction_isolation = 'READ-COMMITTED';
alter table t1 add primary key (id);
insert into t1 values (1, '1', 1), (2, '2', 2), (10, '10', 10), (20, '20', 20);

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where id = 10;
Query OK, 1 row affected (0.00 sec)


mysql> select t1.ENGINE_TRANSACTION_ID, t1.OBJECT_NAME, t1.INDEX_NAME, t1.LOCK_TYPE, t1.LOCK_MODE, t1.LOCK_STATUS, t1.LOCK_DATA  from performance_schema.data_locks t1, information_schema.INNODB_TRX t2 where t1.ENGINE_TRANSACTION_ID = t2.trx_id and t2.trx_mysql_thread_id = CONNECTION_ID();
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                 38967 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                 38967 | t1          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 10        |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+

總結：加了表意向鎖以及 id=10記錄的行（X）鎖

2、RC + id是唯一鍵

alter table t1 drop primary key, add primary key (name), add unique key (id);

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where id = 10;
Query OK, 1 row affected (0.00 sec)


mysql> select t1.ENGINE_TRANSACTION_ID, t1.OBJECT_NAME, t1.INDEX_NAME, t1.LOCK_TYPE, t1.LOCK_MODE, t1.LOCK_STATUS, t1.LOCK_DATA  from performance_schema.data_locks t1, information_schema.INNODB_TRX t2 where t1.ENGINE_TRANSACTION_ID = t2.trx_id and t2.trx_mysql_thread_id = CONNECTION_ID();
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                 39048 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                 39048 | t1          | id         | RECORD    | X,REC_NOT_GAP | GRANTED     | 10, '10'  |
|                 39048 | t1          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | '10'      |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
3 rows in set (0.01 sec)

總結：加了表意向鎖、唯一索引id=10記錄的行（X）鎖和主鍵name=‘10’的記錄鎖

3、RC + id是普通索引

alter table t1 drop index id, add index id_idx(id);

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`name`),
  KEY `id_idx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where id = 10;
Query OK, 1 row affected (0.00 sec)


mysql> select t1.ENGINE_TRANSACTION_ID, t1.OBJECT_NAME, t1.INDEX_NAME, t1.LOCK_TYPE, t1.LOCK_MODE, t1.LOCK_STATUS, t1.LOCK_DATA  from performance_schema.data_locks t1, information_schema.INNODB_TRX t2 where t1.ENGINE_TRANSACTION_ID = t2.trx_id and t2.trx_mysql_thread_id = CONNECTION_ID();
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                 39062 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                 39062 | t1          | id_idx     | RECORD    | X,REC_NOT_GAP | GRANTED     | 10, '10'  |
|                 39062 | t1          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | '10'      |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
3 rows in set (0.01 sec)

總結：加了表意向鎖、普通索引id=10記錄的行（X）鎖和主鍵name=‘10’的記錄鎖

這裡要注意同一個值有多條記錄的時候

4、RC + id無索引

alter table t1 drop index id_idx;

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where id = 10;
Query OK, 1 row affected (0.00 sec)


mysql> select t1.ENGINE_TRANSACTION_ID, t1.OBJECT_NAME, t1.INDEX_NAME, t1.LOCK_TYPE, t1.LOCK_MODE, t1.LOCK_STATUS, t1.LOCK_DATA  from performance_schema.data_locks t1, information_schema.INNODB_TRX t2 where t1.ENGINE_TRANSACTION_ID = t2.trx_id and t2.trx_mysql_thread_id = CONNECTION_ID();
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                 39084 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                 39084 | t1          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | '10'      |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.01 sec)

總結：從結果中只看出了加了表意向鎖和主鍵name=‘10’的記錄鎖，實際上是mysql server 層面進行了優化。其實id列上沒有索引，SQL會走聚簇索引的全掃描進行過濾，因此每條記錄，無論是否滿足條件，都會被加上X鎖。但是，為了效率考量，MySQL做了優化，對於不滿足條件的記錄，會在判斷後放鎖，最終持有的，是滿足條件的記錄上的鎖，但是不滿足條件的記錄上的加鎖/放鎖動作不會省略。

可以開啟事務2進行驗證下：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where id = 20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

5、RR + id是主鍵

id列是主鍵列，Repeatable Read隔離級別，針對delete from t1 where id = 10; 這條SQL，加鎖與組合一："id主鍵 + RC"一致。

6、RR + id是唯一鍵

id唯一索引 + RR的加鎖與id唯一索引，RC一致。兩個X鎖，id唯一索引滿足條件的記錄上一個，對應的聚簇索引上的記錄一個。

alter table t1 drop primary key, add primary key (name), add unique key (id);

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where id = 10;
Query OK, 1 row affected (0.00 sec)

mysql> select t1.ENGINE_TRANSACTION_ID, t1.OBJECT_NAME, t1.INDEX_NAME, t1.LOCK_TYPE, t1.LOCK_MODE, t1.LOCK_STATUS, t1.LOCK_DATA  from performance_schema.data_locks t1, information_schema.INNODB_TRX t2 where t1.ENGINE_TRANSACTION_ID = t2.trx_id and t2.trx_mysql_thread_id = CONNECTION_ID();
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                 39110 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                 39110 | t1          | id         | RECORD    | X,REC_NOT_GAP | GRANTED     | 10, '10'  |
|                 39110 | t1          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | '10'      |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
3 rows in set (0.01 sec)

這裡要留意如果刪除id一個不存在值，則會存在間隙鎖哦

7、RR + id是普通索引

mysql> alter table t1 drop index id, add index id_idx(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`name`),
  KEY `id_idx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where id = 10;
Query OK, 1 row affected (0.00 sec)

mysql> select t1.ENGINE_TRANSACTION_ID, t1.OBJECT_NAME, t1.INDEX_NAME, t1.LOCK_TYPE, t1.LOCK_MODE, t1.LOCK_STATUS, t1.LOCK_DATA  from performance_schema.data_locks t1, information_schema.INNODB_TRX t2 where t1.ENGINE_TRANSACTION_ID = t2.trx_id and t2.trx_mysql_thread_id = CONNECTION_ID();
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                 39154 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                 39154 | t1          | id_idx     | RECORD    | X             | GRANTED     | 10, '10'  |
|                 39154 | t1          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | '10'      |
|                 39154 | t1          | id_idx     | RECORD    | X,GAP         | GRANTED     | 20, '20'  |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)

總結：相比RC+id是普通索引多了間隙鎖

8、RR + id無索引

由於間隙鎖的存在，相當於全表鎖了，不能插入、更新、刪除

JavaScript經典案例（二）
2018-08-27
JavaScript
【MySQL經典案例分析】關於資料行溢位由淺至深的探討
2018-12-27
MySql
Java基礎經典案例
2021-01-04
Java
Python入門經典案例一
2018-09-24
Python
C++ 經典案例1例
2023-11-02
C++
Linux 【Shell指令碼經典案例】
2020-09-15
Linux指令碼
OpenCV之C++經典案例
2022-11-24
OpenCVC++
MySQL 資料庫“十宗罪”（十大經典錯誤案例）
2018-12-21
MySql資料庫
資料恢復經典案例分析-raid兩塊硬碟離線恢復
2018-12-03
資料恢復AI硬碟
深入理解負載均衡經典案例
2019-03-01
負載
關於AWK的10個經典案例
2019-08-21
Mysql之案例分析（一）
2021-04-07
MySql
故障分析 | MySQL死鎖案例分析
2023-01-05
MySql
JavaScript經典案例：鍵盤控制元素移動
2020-11-25
JavaScript
R語言經典統計分析
2024-10-16
R語言
【經典案例】Python詳解設計模式：策略模式
2019-04-22
Python設計模式
MySQL批量更新死鎖案例分析
2018-09-21
MySql
經典資料分析應用介紹
2023-12-11
全球十大農業大資料經典案例
2018-11-16
大資料
30個關於Shell指令碼的經典案例（中）
2019-09-10
指令碼
30個關於Shell指令碼的經典案例（上）
2019-09-06
指令碼
30個關於Shell指令碼的經典案例（下）
2019-09-14
指令碼
【C++】 55_經典問題分析四
2019-05-14
C++
《暗黑2》經典數值公式分析總結
2024-07-31
公式
《戰國 BASARA》，一個盛極必衰的經典案例
2019-07-30
智慧垃圾分類回收系統解決方案（經典案例）
2020-06-11
畫江湖之SQL優化 -10大經典案例場景
2021-09-16
SQL優化
openGauss核心分析（七）：SQL by pass & 經典執行器
2023-02-15
SQL
視訊教程-Python機器學習經典案例實戰-Python
2020-05-28
Python機器學習
淺談「復仇」在遊戲中的作用與經典案例
2020-07-14
遊戲
H5經典案例：2015,你靠什麼過的更好？
2020-04-04
H5
SICP 經典
2024-03-25
vuex-stepbystep 經典案例，詳細註釋一步到位
2018-12-22
Vue
JS 第三方工具封裝經典案例（canvas時鐘）
2020-01-21
JS封裝Canvas
經典氣泡排序的分析、優化及測試
2020-10-03
排序優化
MySQL EXPLAIN結果集分析 - 附帶大量案例
2019-09-05
MySqlAI
人工智慧研究：經典推理和非經典推理
2019-01-08
人工智慧
Spring 經典教程
2018-08-22
Spring