count(*)、count(1)哪個更快？面試必問：通宵整理的十道經(jīng)典MySQL必問面試題

一、你是如何理解Count(*)和Count(1)的？

這兩個并沒有區(qū)別，不要覺得 count( ) 會查出全部字段，而 count(1) 不會。所以 count( ) 會更慢，你覺得 MySQL 作者會這么做嗎？

可以很明確地告訴你們 count( ) 和 count(1) 是一樣的，而正確有區(qū)別的是 count(字段)。如果你 count() 的是具體的字段，那么 MySQL 會判斷某行記錄中對應(yīng)字段是否為 null，如果為 null 就不會進行統(tǒng)計了。因此 count(字段) 的結(jié)果可能會小于 count( ) 和 count(1)。

另外，直接執(zhí)行 select (*) from t1; 時，也可以利用到索引的，并不一定是全表掃描，也可以掃描某個索引 B+ 樹的葉子節(jié)點，從而得到總條數(shù)，因為不管是什么索引，主鍵索引還是輔助索引，實際上它們在葉子節(jié)點的數(shù)量是一樣的，只不過字段數(shù)不一樣，主鍵索引存了全部字段，而輔助索引只存了定義的索引字段 + 主鍵字段，所以通常輔助索引是更占用空間的，因此遍歷起來也會更快，但是記錄條數(shù)是一樣的。

二、你是如何理解最左前綴原則的？

這個原則表明，只有在復合索引的左側(cè)部分的列上，條件才能被優(yōu)化。換句話說，當使用復合索引時，查詢的條件應(yīng)該從索引的最左側(cè)列開始，才能最大化利用索引

我們創(chuàng)建一個簡單的示例表，命名為 employees ，并在其上創(chuàng)建一個復合索引。表結(jié)構(gòu)如下：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department VARCHAR(50),
    INDEX idx_name_age (last_name, first_name, age)
);

接下來，我們插入一些實例數(shù)據(jù)

INSERT INTO employees (first_name, last_name, age, department) VALUES
('John', 'Doe', 30, 'HR'),
('Jane', 'Doe', 25, 'IT'),
('Mary', 'Smith', 35, 'Finance'),
('Michael', 'Johnson', 40, 'IT'),
('Emily', 'Davis', 29, 'HR');

符合最左前綴原則的查詢SQL

SELECT * FROM employees WHERE last_name = 'Doe' AND first_name = 'Jane' AND age=25;
SELECT * FROM employees WHERE  first_name = 'Jane' AND last_name = 'Doe' AND age=30;
SELECT * FROM employees WHERE  age=30 AND last_name = 'Doe' AND first_name = 'Jane';

對于上面這些查詢，MySQL會使用idx_name_age索引，從這能夠看出，以上SQL都能走索引，和Where條件順序沒有關(guān)系

+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | employees | range | idx_name_age  | idx_name_age | 100     | NULL |     2 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+

在這個執(zhí)行計劃中，我們看到type是range，說明MySQL正在使用idx_name_age索引，并且只檢查了大約2行數(shù)據(jù)。

那如果把last_name去掉呢？

不符合最左前綴原則的查詢

SELECT * FROM employees WHERE first_name = 'Jane' AND age = 25;

對于這個查詢，MySQL不會使用復合索引idx_name_age，因為它沒有從最左側(cè)的列l(wèi)ast_name開始。

通過Explain執(zhí)行計劃，查看索引執(zhí)行情況

+----+-------------+-----------+-------+---------------+------+---------+------+-------+-------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+-------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | employees | ALL   | NULL          | NULL | NULL    | NULL |     5 | Using where |
+----+-------------+-----------+-------+---------------+------+---------+------+-------+-------------+

在這個執(zhí)行計劃中，我們看到type是ALL，這意味著MySQL沒有使用任何索引，而是進行了全表掃描，這樣效率較低。

最近無意間獲得一份阿里大佬寫的刷題筆記，一下子打通了我的任督二脈，進大廠原來沒那么難。這是大佬寫的， 7701頁的BAT大佬寫的刷題筆記，讓我offer拿到手軟

總結(jié)

從這可以看出，所謂的最左前綴原則的“最左”，并不是指where條件中的last_name一定要在最左邊，而是指where條件中一定要給出定義聯(lián)合索引的最左邊字段，比如我們定義“l(fā)ast_name, first_name, age”聯(lián)合索引的SQL為：

INDEX idx_name_age (last_name, first_name, age)

其中l(wèi)ast_name字段是最左邊的字段，因此如果想要走idx_name_age索引，那么SQL一定要給出last_name字段的條件，這才是“最左”的意思。

三、你是如何理解行鎖、GAP鎖、臨健鎖的？

1、行數(shù)

行鎖是對具體數(shù)據(jù)行的鎖定，允許多個事務(wù)并發(fā)操作不同行，只有在同一行上進行寫入時才會阻塞其他事務(wù)

假設(shè)我們有如下表結(jié)構(gòu)和數(shù)據(jù)：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);

如果事務(wù)A更新了某個特定員工的信息：

-- 事務(wù)A
START TRANSACTION;
UPDATE employees SET age = 31 WHERE last_name = 'Doe';

在這個過程中，行鎖會被加在last_name = 'Doe'所對應(yīng)的行上（即John和Jane）。如果此時事務(wù)B嘗試更新同一行：

-- 事務(wù)B
START TRANSACTION;
UPDATE employees SET age = 29 WHERE last_name = 'Doe';

事務(wù)B會被阻塞，直到事務(wù)A提交或回滾,因為事務(wù)A、事務(wù)B加的都是排它鎖，也叫悲觀鎖。這樣行鎖確保了數(shù)據(jù)的一致性。

GAP鎖

行鎖鎖的是某一行，而GAP鎖鎖的是行前面的間隙，注意只是行前面的間隙，你可能會問那表的最后一行前后都有間隙啊，最后一行后面的間隙不鎖嗎？

當然會鎖了，只不，過是交給了一個叫做PAGE_NEW_SUPREMUM的記錄來說，你可以理解為PAGE_NEW_SUPREMUM記錄是InnoDB默認的，它固定作為最后一條記錄，因此只要鎖住PAGE_NEW_SUPREMUM前面的間隙，就相當于鎖住了我們所理解的最后一行后面的間隙。

臨健鎖（Next-Key Lock）

臨界鎖是行鎖和GAP鎖的結(jié)合，鎖定具體的數(shù)據(jù)行以及行之間的空隙。它用于確保在一個范圍內(nèi)的查詢中，不僅防止了幻讀，還能保護行數(shù)據(jù)

繼續(xù)使用之前的例子，假設(shè)我們執(zhí)行了如下操作:

-- 事務(wù)D
START TRANSACTION;
SELECT * FROM employees WHERE last_name >= 'D' FOR UPDATE;

在此查詢中，MySQL會對所有l(wèi)ast_name為'D'及其后的行加上行鎖，同時對'D'之前的空隙加上GAP鎖，這樣可以防止在該范圍內(nèi)插入新的行。

四、你是如何理解MVCC的？

OCR識別結(jié)果出現(xiàn)了一些錯誤和混亂，導致內(nèi)容不夠清晰。雖然識別不是很理想，但我將根據(jù)我的理解和相關(guān)知識，概述該圖片可能傳達的內(nèi)容。

如何理解MVCC？

所謂MVCC就是多版本并發(fā)控制，MySQL為了實現(xiàn)可重復讀這個隔離級別，而且為了不采用鎖機制來實現(xiàn)可重復讀，所以采用MVCC機制來實現(xiàn)。

主要概念

1. ReadView（讀取視圖） ：

   • 當一個事務(wù)開始時，MVCC會創(chuàng)建一個 ReadView ，該視圖記錄了當前可見的所有版本，包括哪些事務(wù)是活躍的，哪些事務(wù)已經(jīng)提交。

2. 事務(wù)ID ：

   • 每個事務(wù)都有一個唯一的事務(wù)ID。在 ReadView 中，會記錄當前事務(wù)的ID、最小事務(wù)ID和最大事務(wù)ID。

3. 可見性規(guī)則 ：

   • 如果一個事務(wù)的ID ：
     1. 大于 ReadView 中的最大事務(wù)ID：這個事務(wù)的更改對當前事務(wù)不可見。
     2. 屬于 ReadView 中的活躍事務(wù)：則該事務(wù)的更改不可見，因為該事務(wù)還在進行中。
     3. 小于 ReadView 中的最小事務(wù)ID：該事務(wù)的更改是可見的，因為它已經(jīng)提交。

MVCC的工作流程

• 創(chuàng)建 ReadView ：

  • 當事務(wù)開始時，MVCC會生成一個 ReadView 。它會包含當前事務(wù)的ID、活躍事務(wù)的ID以及最大和最小事務(wù)ID。

• 讀取數(shù)據(jù) ：

  • 當事務(wù)讀取數(shù)據(jù)時，它將參考 ReadView 中的信息，以確定哪些版本的數(shù)據(jù)是可見的。

• 更新數(shù)據(jù) ：

  • 當事務(wù)更新數(shù)據(jù)時，MVCC不會直接覆蓋原有的數(shù)據(jù)，而是創(chuàng)建一個新的版本。只有在所有引用該數(shù)據(jù)的事務(wù)完成后，才會清理舊版本。

總結(jié)

MVCC允許多個事務(wù)在不干擾彼此的情況下同時進行操作，這極大地提高了數(shù)據(jù)庫的并發(fā)性能。通過維護數(shù)據(jù)的多個版本，MVCC保證了數(shù)據(jù)的一致性和隔離性，同時減少了鎖的競爭。

最近無意間獲得一份阿里大佬寫的刷題筆記，一下子打通了我的任督二脈，進大廠原來沒那么難。這是大佬寫的， 7701頁的BAT大佬寫的刷題筆記，讓我offer拿到手軟

五、你是如何理解Online DDL的？

Online DDL 是指在不影響數(shù)據(jù)庫服務(wù)的情況下，修改數(shù)據(jù)庫表的結(jié)構(gòu)。通俗點說，就是我們可以在數(shù)據(jù)庫正常運作的同時，對表進行調(diào)整，比如新增列、修改字段類型、添加索引等，而不需要停機維護。

般的DDL操作，比如新增一個字段，會有以下幾個步驟

1. 解析與檢查

MySQL 首先會對 DDL 語句進行解析，確保語法正確。例如：

ALTER TABLE users ADD COLUMN age INT;

MySQL 會檢查表 users 是否存在，新增的 age 字段是否與已有的字段沖突（比如字段名重復），數(shù)據(jù)類型是否支持等。

2. 表的元數(shù)據(jù)鎖（metadata lock）

在進行任何表結(jié)構(gòu)變更之前，MySQL 會對表加一個元數(shù)據(jù)鎖（MDL）。元數(shù)據(jù)鎖的作用是防止在變更結(jié)構(gòu)的同時，其他 DDL 操作對表進行修改，保證表結(jié)構(gòu)一致性。

類比 ：元數(shù)據(jù)鎖就像在超市里裝貨架時，防止其他人也來同時更改貨架位置，避免混亂。

3. 創(chuàng)建臨時表

當我們執(zhí)行 ALTER TABLE 語句時，MySQL 會創(chuàng)建一個 臨時表 。這個臨時表是現(xiàn)有表的一個復制品，并且會按照我們的要求增加新的字段。

臨時表的步驟：

1. 復制原表結(jié)構(gòu) ：MySQL 會復制原表的結(jié)構(gòu)到一個臨時表中，并加上我們新增的字段，比如 age 。
2. 復制數(shù)據(jù) ：MySQL 將原表中的所有數(shù)據(jù)行逐行復制到臨時表中，同時為每一行填充新增加字段的默認值（如果有）。

類比 ：這就像超市貨架升級時，先在倉庫里搭建一個新的貨架模型，放置相同的商品，同時增加新的商品存放區(qū)。

4. 切換表

當 MySQL 完成了數(shù)據(jù)復制后，它會將原表和臨時表進行替換。此時，臨時表變成了正式的表，包含了新字段。

• 在這個過程中，所有的 DML（增刪改查）操作都會暫時被掛起，直到替換完成。這段時間很短暫，對服務(wù)的影響非常小。

類比 ：就像倉庫里的新貨架搭建好后，把它搬進超市，同時替換掉舊貨架。顧客幾乎不會察覺到這個過程。

5. 刪除舊表

原始的表被新表替換后，MySQL 會自動刪除舊表的元數(shù)據(jù)，釋放空間。這一步在后臺完成，不影響數(shù)據(jù)庫的正常操作。

6. 釋放鎖

當所有操作完成后，MySQL 會釋放元數(shù)據(jù)鎖，允許其他 DDL 或 DML 操作繼續(xù)進行。

總結(jié)：

1. 解析語句并檢查合法性。
2. 對表加元數(shù)據(jù)鎖（防止沖突的結(jié)構(gòu)變更）。
3. 創(chuàng)建臨時表，并將數(shù)據(jù)從舊表復制到臨時表。
4. 替換舊表，刪除舊表的元數(shù)據(jù)。
5. 釋放鎖。

注意 ：這種方法在 不使用 Online DDL 的情況下，可能導致大量的數(shù)據(jù)復制操作，進而對性能有影響，尤其是表數(shù)據(jù)量較大時。

六、你知道哪些情況下會導致索引失效

在 MySQL 中，索引是提高查詢效率的關(guān)鍵工具，但有時可能會遇到索引 失效 的情況，導致查詢性能大幅下降。這種情況通常與查詢語句的寫法、數(shù)據(jù)類型的選擇以及數(shù)據(jù)庫的優(yōu)化機制有關(guān)。下面是幾種常見會導致索引失效的場景：

1. 使用 LIKE 時通配符放在前面

如果在 LIKE 語句中，通配符 % 放在字符串的開頭，會導致索引失效。因為在這種情況下，MySQL 無法通過索引快速定位到符合條件的記錄，而需要掃描所有記錄。

示例：

SELECT * FROM users WHERE name LIKE '%abc';  -- 索引失效

這種寫法會使得 MySQL 掃描全表，而如果寫成 LIKE 'abc%' ，索引仍然有效。

類比 ：這就像你在一大堆文件中查找名字以"abc"開頭的文件名，你可以直接找到相應(yīng)的部分，但如果是查找名字包含"abc"的文件，你就得看每個文件名。

2. 數(shù)據(jù)類型不一致

當查詢條件中的字段類型與索引字段的類型不一致時，MySQL 可能不會使用索引。它會先對數(shù)據(jù)進行類型轉(zhuǎn)換，而類型轉(zhuǎn)換會導致無法高效利用索引。

示例：

假設(shè) id 是一個整型字段：

SELECT * FROM users WHERE id = '123';  -- 索引失效

這里的 '123' 是字符串，MySQL 會進行隱式轉(zhuǎn)換，因此索引失效。

3. 對索引字段使用函數(shù)

如果在查詢中對索引字段使用了函數(shù)或運算操作，MySQL 不能通過索引來查詢，導致索引失效。

示例：

SELECT * FROM users WHERE YEAR(created_at) = 2023;  -- 索引失效

這里 YEAR(created_at) 是對 created_at 字段進行了函數(shù)操作，因此 MySQL 無法直接使用索引進行查找。

類比 ：這就像你想按某種規(guī)律排列的列表中查找內(nèi)容，但你需要先改變它的形式才能找到，導致效率下降。

4. 使用 OR 關(guān)鍵字

當 OR 條件中的某一列沒有索引時，整個查詢的索引都會失效。

示例：

SELECT * FROM users WHERE id = 1 OR name = 'Alice';  -- 索引失效

假設(shè) id 列有索引，而 name 列沒有索引，那么這個查詢就不能利用索引，MySQL 需要進行全表掃描。

優(yōu)化方式 ：為 name 字段單獨建立索引，或者改寫查詢邏輯，避免 OR 導致的索引失效。

5. 不等條件 ( != 或 <> )

使用不等于操作符（ != 或 <> ）時，MySQL 不能有效使用索引，會導致全表掃描。

示例：

SELECT * FROM users WHERE age != 30;  -- 索引失效

這類查詢通常會導致索引失效，因為 MySQL 無法通過索引定位所有滿足 != 的記錄。

6. 范圍查詢 ( > , < , BETWEEN ) 后的列

在復合索引（即多列索引）中，當?shù)谝粋�字段使用了范圍查詢時，后續(xù)的字段的索引可能會失效。

示例：

假設(shè)我們有一個復合索引 (age, name) ，如下查詢：

SELECT * FROM users WHERE age > 30 AND name = 'Alice';  -- `name` 索引失效

在這種情況下，由于 age 使用了范圍查詢， name 列的索引將失效，MySQL 無法通過復合索引直接查找。

7. 索引列前加上 IS NULL 或 IS NOT NULL

對于索引列使用 IS NULL 或 IS NOT NULL ，有時 MySQL 可能不會利用索引，尤其是在大量數(shù)據(jù)存在 NULL 值的情況下，MySQL 會認為索引的使用不劃算。

示例：

SELECT * FROM users WHERE name IS NOT NULL;  -- 可能索引失效

8. 查詢條件中使用 NOT IN 或 NOT EXISTS

使用 NOT IN 或 NOT EXISTS 也可能會導致 MySQL 不使用索引，從而引發(fā)全表掃描。

示例：

SELECT * FROM users WHERE id NOT IN (1, 2, 3);  -- 索引失效

9. 表中的數(shù)據(jù)量很小

當表中的數(shù)據(jù)量很小，MySQL 可能認為全表掃描比使用索引更高效。在這種情況下，MySQL 會選擇直接掃描而不是通過索引查找。

類比 ：如果你只有幾個文件需要查找，花時間先創(chuàng)建目錄索引反而不劃算，直接掃描全部文件更快。

10. MySQL 優(yōu)化器選擇不使用索引

有時，即便索引可用，MySQL 的查詢優(yōu)化器可能會根據(jù)表的統(tǒng)計信息和成本估算，認為全表掃描比使用索引更快，從而放棄索引。

總結(jié)：

索引失效通常與查詢語句的寫法、數(shù)據(jù)類型、函數(shù)使用、以及 MySQL 查詢優(yōu)化器的決策有關(guān)。為了避免索引失效，需要盡量避免上述常見的情況，如：

• 在 LIKE 查詢中避免通配符 % 開頭
• 保持數(shù)據(jù)類型一致
• 盡量不對索引列使用函數(shù)或運算操作
• 合理規(guī)劃復合索引中的查詢順序

七、你是如何理解MySQL的filesort的？

通俗的理解，可以把 filesort 理解為數(shù)據(jù)庫的“備用排序方式”。當查詢中的 ORDER BY 語句無法利用索引中的排序順序時，MySQL 就會啟用 filesort 來手動排序結(jié)果。

什么時候會觸發(fā) filesort ？

MySQL 會在某些情況下使用 filesort ，比如：

1. 沒有合適的索引 ：
   當查詢中的 ORDER BY 字段沒有索引，MySQL 無法利用索引順序，只能借助 filesort 來進行排序。

   示例：

   SELECT * FROM users ORDER BY age;
   

   假設(shè) users 表中沒有 age 字段的索引，這時候 MySQL 會進行 filesort 。

2. 多列排序，但索引不匹配 ：
   當我們對多個列進行排序，而這些列沒有被索引覆蓋或索引順序與排序要求不符時， filesort 也會被觸發(fā)。

   示例：

   SELECT * FROM users ORDER BY age, name;
   

   假設(shè)表上只有 age 的索引，但沒有 (age, name) 的復合索引，那么 MySQL 會使用 filesort 。

3. 組合查詢或函數(shù)操作導致索引失效 ：
   當查詢中對字段進行計算或函數(shù)操作時，即便這些字段有索引，也無法直接利用索引進行排序。

   示例：

   SELECT * FROM users ORDER BY LENGTH(name);
   

   LENGTH(name) 是一個函數(shù)操作，MySQL 需要手動排序，因此會使用 filesort 。

filesort 的工作方式：

filesort 實際上有兩種實現(xiàn)方式，取決于 MySQL 的版本和配置：

1. 單行數(shù)據(jù)排序 （Older Versions）：MySQL 會把查詢結(jié)果的所有行都放入一個緩沖區(qū)，然后根據(jù) ORDER BY 字段逐行比較并排序。這種方式效率相對較低，因為要處理的數(shù)據(jù)量很大。

2. 兩次掃描排序 （Optimized Versions）：在較新的 MySQL 版本中， filesort 會進行優(yōu)化，只會在第一次掃描時收集需要排序的字段和 ROW_ID ，然后通過排序后的 ROW_ID 再去讀取整行數(shù)據(jù)。這種方式減少了排序的數(shù)據(jù)量，提高了性能。

filesort 性能的影響：

filesort 并不是說每次都會涉及磁盤操作，它有可能在內(nèi)存中完成，但當數(shù)據(jù)量較大時，內(nèi)存不足以完成排序，就可能將數(shù)據(jù)寫入磁盤進行排序，這樣會影響性能。

MySQL 有兩個重要的參數(shù)控制 filesort 行為：

1. sort_buffer_size ：這是 MySQL 用來在內(nèi)存中排序的緩沖區(qū)大小。如果排序的數(shù)據(jù)能放進這個緩沖區(qū)，排序就會在內(nèi)存中完成；否則，MySQL 會將部分數(shù)據(jù)寫入磁盤，從而影響性能。

2. max_length_for_sort_data ：控制 MySQL 采用哪種 filesort 方法（單行排序或兩次掃描排序）。對于較短的數(shù)據(jù)，MySQL 更可能選擇效率較高的兩次掃描排序方式。

如何避免或優(yōu)化 filesort ？

1. 使用合適的索引 ：
   最直接的辦法就是為查詢中的排序字段創(chuàng)建索引。尤其是在有 ORDER BY 子句時，確保創(chuàng)建了復合索引可以有效避免 filesort 。

   示例：

   CREATE INDEX idx_age_name ON users (age, name);
   

2. 增加 sort_buffer_size ：
   如果無法避免 filesort ，可以通過增加 sort_buffer_size 的大小，確保更多數(shù)據(jù)可以在內(nèi)存中排序，減少磁盤 I/O。

3. 減少排序的數(shù)據(jù)量 ：
   使用 LIMIT 來限制查詢結(jié)果集的大小，可以減少需要排序的數(shù)據(jù)量，從而減小 filesort 的開銷。

   示例：

   SELECT * FROM users ORDER BY age LIMIT 100;
   

4. 盡量避免對排序字段使用函數(shù) ：
   在 ORDER BY 中，盡量不要對排序字段進行函數(shù)運算或表達式操作，這樣可以增加 MySQL 使用索引的可能性。

總結(jié)：

filesort 是 MySQL 中的一種排序機制，當查詢結(jié)果無法通過索引順序排序時，MySQL 就會啟用 filesort 進行手動排序。雖然名字中有“file”，但排序未必一定涉及磁盤操作，內(nèi)存中的排序也是常見的。 filesort 是 MySQL 的備用排序方式，盡管有時不可避免，但我們可以通過創(chuàng)建索引、調(diào)整緩沖區(qū)大小等方式來優(yōu)化它的性能。

八、你知道哪些情況下會鎖表嗎？

一、常見的鎖表情況

1. DDL 操作（數(shù)據(jù)定義語言）

當執(zhí)行一些結(jié)構(gòu)性變更的操作（例如 ALTER TABLE 、 CREATE TABLE 、 DROP TABLE ）時，MySQL 會鎖住整個表，防止其他線程對該表進行操作。這種鎖是 元數(shù)據(jù)鎖 （Metadata Lock），用來保證表結(jié)構(gòu)的變更不會與其他并發(fā)操作發(fā)生沖突。

示例：

ALTER TABLE users ADD COLUMN age INT;

此操作會鎖表，其他對 users 表的操作會被阻塞，直到變更完成。

2. 全表掃描的 UPDATE 或 DELETE 操作

當你執(zhí)行一個 UPDATE 或 DELETE 操作且未使用索引時，MySQL 可能會鎖住整個表進行更新或刪除，因為它必須掃描所有行。

示例：

UPDATE users SET age = 30 WHERE name LIKE '%John%';  -- 如果沒有索引，可能鎖表

由于 LIKE '%John%' 無法利用索引，MySQL 需要全表掃描，并對整個表加鎖。

3. 事務(wù)中的寫操作（ INSERT 、 UPDATE 、 DELETE ）

在 InnoDB 存儲引擎中，寫操作會對數(shù)據(jù)行加上 行鎖 （Row Lock）。但是在某些情況下（如沒有索引的情況下），MySQL 可能會退化為 表鎖 （Table Lock）。即使是行鎖，在長事務(wù)未提交或回滾的情況下，也可能阻塞其他事務(wù)，從而產(chǎn)生間接的鎖表現(xiàn)象。

示例：

BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1234;  -- 行鎖，但可能鎖表
COMMIT;

如果該事務(wù)運行了很長時間，并且其他操作也需要訪問 orders 表中的記錄，可能會導致等待。

4. LOCK TABLES 顯式加鎖

MySQL 支持使用 LOCK TABLES 命令顯式地對表加鎖，分為 讀鎖（READ LOCK） 和 寫鎖（WRITE LOCK） 。在寫鎖期間，其他線程不能對該表進行任何讀或?qū)懖僮�；在讀鎖期間，其他線程只能讀表，而不能寫表。

示例：

LOCK TABLES users WRITE;  -- 對 `users` 表加寫鎖

此時，其他線程對 users 表的任何讀寫操作都會被阻塞，直到鎖被釋放。

5. 大批量插入數(shù)據(jù)

當你使用某些批量插入語句（如 INSERT INTO ... SELECT ... 或 INSERT IGNORE ）插入大量數(shù)據(jù)時，如果沒有恰當?shù)乃饕�，MySQL 可能會鎖表，尤其是在 MyISAM 存儲引擎中。

示例：

INSERT INTO new_users SELECT * FROM users WHERE created_at > '2023-01-01';  -- 可能鎖表

如果 created_at 沒有索引，MySQL 需要鎖表來完成整個插入操作。

6. 外鍵約束檢查

在 InnoDB 中，當插入或刪除涉及到外鍵約束的數(shù)據(jù)時，MySQL 可能會鎖住父表或子表，確保數(shù)據(jù)的完整性。雖然 InnoDB 大多情況下會使用行鎖，但在某些復雜的情況下，比如沒有合適的索引，可能會導致表鎖。

示例：

DELETE FROM orders WHERE order_id = 100;  -- 觸發(fā)外鍵約束檢查，可能鎖住 `customers` 表

如果 orders 表有外鍵關(guān)聯(lián)到 customers 表，且沒有合適的索引，可能會鎖住 customers 表。

7. MyISAM 存儲引擎的讀寫操作

在 MyISAM 存儲引擎中，寫操作會鎖住整個表，即使只修改了一行。讀操作之間不會互相阻塞，但讀寫操作之間會發(fā)生阻塞。因此，MyISAM 表在處理高并發(fā)寫操作時可能會頻繁鎖表。

示例：

INSERT INTO myisam_table (name, age) VALUES ('John', 30);  -- 寫鎖鎖住整個表

如果有大量的寫操作，表會頻繁被鎖住，影響并發(fā)性能。

二、如何避免鎖表？

鎖表會影響數(shù)據(jù)庫的并發(fā)性和性能，因此我們通常需要盡量避免。這里有一些方法可以減少鎖表的發(fā)生：

1. 使用合適的存儲引擎

盡量使用 InnoDB 存儲引擎，它支持 行級鎖 ，可以在絕大多數(shù)情況下避免鎖表。相比之下， MyISAM 使用的是 表級鎖 ，在并發(fā)讀寫場景下性能較差。

2. 創(chuàng)建合適的索引

通過為查詢條件中的列創(chuàng)建適當?shù)乃饕�，避免全表掃描。例如，如果你�?jīng)常根據(jù) name 字段進行查詢和更新，應(yīng)該為 name 字段創(chuàng)建索引：

CREATE INDEX idx_name ON users (name);

索引可以有效減少鎖表的可能性。

3. 減少長事務(wù)

長時間未提交的事務(wù)會持有鎖，從而阻塞其他查詢。因此，盡量縮短事務(wù)的執(zhí)行時間，確保在事務(wù)中盡快完成操作并提交。

4. 使用 OPTIMIZE 和 ANALYZE 慎重

這些命令會鎖住表的元數(shù)據(jù)，阻止并發(fā)的讀寫操作。運行這些命令時應(yīng)避免高并發(fā)時間段。

5. 分批次操作

如果需要執(zhí)行大量的 UPDATE 或 DELETE ，可以將操作分批執(zhí)行，以減少每次操作涉及的數(shù)據(jù)量，避免長時間鎖表。

示例：

DELETE FROM users WHERE created_at < '2022-01-01' LIMIT 1000;  -- 分批刪除

6. 避免顯式表鎖

盡量避免使用 LOCK TABLES 進行顯式加鎖操作，尤其是在高并發(fā)場景下。InnoDB 的事務(wù)機制和行級鎖已經(jīng)足夠應(yīng)對大多數(shù)并發(fā)問題。

九、你是如何理解MySQL中的死鎖機制的？

在 MySQL 中， 死鎖 （Deadlock）是指兩個或多個事務(wù)互相等待對方持有的鎖，導致它們都無法繼續(xù)執(zhí)行。這是一種常見的并發(fā)問題，尤其是在高并發(fā)情況下，事務(wù)在訪問相同的數(shù)據(jù)資源時容易產(chǎn)生死鎖。

通俗理解：

可以把死鎖類比為兩個人走在一條窄路上，他們都需要對方讓路才能繼續(xù)前進。A 擋住了 B 的路，B 又擋住了 A 的路，誰也不肯退讓，結(jié)果兩個人都卡住了。這在數(shù)據(jù)庫中表現(xiàn)為事務(wù) A 等待事務(wù) B 釋放資源，而事務(wù) B 同時也在等待事務(wù) A 釋放資源，最終兩個事務(wù)都無法繼續(xù)。

死鎖的產(chǎn)生過程：

1. 事務(wù) A 獲取資源 X 的鎖 。
2. 事務(wù) B 獲取資源 Y 的鎖 。
3. 事務(wù) A 嘗試獲取資源 Y 的鎖，但資源 Y 被事務(wù) B 鎖住，于是事務(wù) A 進入等待狀態(tài)。
4. 事務(wù) B 嘗試獲取資源 X 的鎖，但資源 X 已經(jīng)被事務(wù) A 鎖住，事務(wù) B 也進入等待狀態(tài)。
5. 結(jié)果是：事務(wù) A 等待 B，事務(wù) B 等待 A，形成一個循環(huán)等待，產(chǎn)生死鎖。

MySQL 中的死鎖機制：

MySQL 使用的存儲引擎 InnoDB 提供了行級鎖，這雖然減少了鎖沖突的概率，但也更容易導致死鎖。InnoDB 遇到死鎖時，會主動檢測并解決這個問題，通過回滾其中一個事務(wù)來打破僵局。

死鎖的處理方式：

在 MySQL 中，當 InnoDB 檢測到死鎖時，它會選擇回滾 代價最小的事務(wù) ，通常是回滾鎖定較少資源的事務(wù)。然后，它會向客戶端返回一個錯誤消息，類似于：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

當一個事務(wù)被回滾后，另一個事務(wù)可以繼續(xù)執(zhí)行，解決了死鎖問題。

如何避免死鎖？

盡管 MySQL 能自動檢測并處理死鎖，但頻繁出現(xiàn)死鎖會影響系統(tǒng)性能，因此盡量避免死鎖是很有必要的。以下是一些常見的避免死鎖的方法：

1. 固定鎖的順序

確保所有事務(wù)在訪問多張表或多條記錄時，遵循相同的順序鎖定資源。這樣可以避免不同事務(wù)間出現(xiàn)交叉鎖定，減少死鎖的可能性。

示例：
所有事務(wù)在更新 users 表和 orders 表時，都先鎖住 users 表，再鎖住 orders 表，避免死鎖。

2. 減少鎖定范圍

盡量減少每個事務(wù)鎖定的范圍和時間，避免長時間占用鎖。例如，盡量縮短事務(wù)的執(zhí)行時間，減少不必要的查詢。

示例：

BEGIN;
UPDATE users SET age = age + 1 WHERE id = 1;
COMMIT;

盡量避免在一個事務(wù)內(nèi)進行過多的操作或等待用戶輸入。

3. 使用合適的索引

在查詢時盡量使用索引來減少鎖定的行數(shù)，特別是在 UPDATE 和 DELETE 操作時，合適的索引可以減少鎖定的行數(shù)，從而降低死鎖的風險。

示例：
為 user_id 創(chuàng)建索引：

CREATE INDEX idx_user_id ON orders (user_id);

4. 減少并發(fā)量

控制數(shù)據(jù)庫的并發(fā)訪問量，如果可能的話，避免在高并發(fā)情況下進行大批量數(shù)據(jù)操作。高并發(fā)訪問會增加死鎖的概率。

5. 合理設(shè)置事務(wù)隔離級別

使用合適的事務(wù)隔離級別可以減少鎖定沖突。InnoDB 支持多種事務(wù)隔離級別，最常見的是 REPEATABLE READ 和 READ COMMITTED 。其中， READ COMMITTED 隔離級別可以減少鎖爭用的情況，從而降低死鎖發(fā)生的概率。

示例：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

6. 通過批量操作減少鎖定的時間

如果需要對大量數(shù)據(jù)進行更新或刪除操作，可以考慮分批處理，減少每次事務(wù)鎖定的行數(shù)，從而降低死鎖風險。

示例：

DELETE FROM orders WHERE order_date < '2023-01-01' LIMIT 1000;

使用 LIMIT 分批刪除舊數(shù)據(jù)。

如何檢測死鎖？

當死鎖發(fā)生時，InnoDB 會在錯誤日志中記錄下死鎖信息，包含了死鎖的相關(guān)信息以及導致死鎖的事務(wù)和查詢�？梢酝ㄟ^以下 SQL 語句獲取死鎖信息：

SHOW ENGINE INNODB STATUS;

這條命令會顯示 InnoDB 的狀態(tài)信息，其中包含最近一次死鎖的詳細信息，包括參與死鎖的事務(wù)和鎖的等待情況。

十、你是如何優(yōu)化慢查詢的？

慢查詢的優(yōu)化步驟和方法：

1. 分析慢查詢?nèi)罩?

首先，確保 慢查詢?nèi)罩? （Slow Query Log）已開啟，這是 MySQL 用來記錄執(zhí)行時間超過指定閾值的查詢。你可以通過分析這些日志，找出系統(tǒng)中耗時最長的查詢。

啟用慢查詢?nèi)罩荆?

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 設(shè)置慢查詢閾值為 1 秒

之后，你可以查看慢查詢?nèi)罩緛砹私饽男┎樵儓?zhí)行時間過長，并從這些查詢中入手進行優(yōu)化。

2. 使用 EXPLAIN 分析查詢執(zhí)行計劃

使用 EXPLAIN 命令可以幫助你了解 MySQL 如何執(zhí)行查詢，它會提供信息如：查詢是否使用了索引、掃描了多少行、排序方式等。你可以通過查看執(zhí)行計劃，發(fā)現(xiàn)查詢中的性能瓶頸。

示例：

EXPLAIN SELECT * FROM users WHERE name = 'John';

執(zhí)行結(jié)果會顯示查詢的類型（如 ALL 、 INDEX 、 RANGE 等），表明 MySQL 是否使用了全表掃描（ ALL ）或者索引（ INDEX ）。如果看到 ALL 表示全表掃描，這通常是需要優(yōu)化的信號。

如何解讀一些常見的結(jié)果：

• type : ALL 表示全表掃描，需要優(yōu)化； range 、 ref 或 const 表示使用了索引，性能較好。
• rows : 表示 MySQL 預計掃描的行數(shù)，越少越好。掃描的行數(shù)越多，查詢的開銷越大。
• key : 顯示 MySQL 使用了哪個索引，如果顯示為 NULL ，表示沒有使用索引。

3. 創(chuàng)建和優(yōu)化索引

索引是 MySQL 優(yōu)化慢查詢的最常見手段之一。適當?shù)乃饕�可以顯著減少查詢的掃描行數(shù)，提升查詢速度。

常見的索引優(yōu)化策略：

• 單列索引 ：對查詢中常用的過濾條件或 WHERE 子句中的字段創(chuàng)建索引。
• 復合索引 ：對涉及多個條件的查詢，創(chuàng)建復合索引。例如， SELECT * FROM users WHERE age = 30 AND status = 'active'; ，可以為 (age, status) 創(chuàng)建一個復合索引。

示例：

CREATE INDEX idx_name ON users (name);
CREATE INDEX idx_age_status ON users (age, status);

• 覆蓋索引 ：如果索引包含查詢所需的所有字段，MySQL 可以直接從索引中讀取數(shù)據(jù)，而無需訪問表本身。這樣能減少 I/O 操作，大幅提升查詢效率。

示例：

SELECT name FROM users WHERE age = 30;  -- 如果 name 和 age 都在索引中，MySQL 可以只查索引

• 避免索引失效 ：確保查詢條件能夠正確利用索引。例如：
  • 避免對索引字段使用函數(shù)或表達式，如 WHERE UPPER(name) = 'JOHN' 會導致索引失效。
  • 使用精確匹配，盡量避免 LIKE '%abc' 這種通配符前綴的查詢。

4. 優(yōu)化查詢語句

改進查詢語句的寫法可以大幅提升性能。以下是幾種常見的優(yōu)化建議：

• 選擇合適的數(shù)據(jù)類型 ：盡量使用合適的數(shù)據(jù)類型，避免使用過大的字段長度。比如，用 INT 存儲年齡，而不是用 VARCHAR 。
• 減少查詢的返回結(jié)果 ：避免 SELECT * ，只查詢需要的字段。返回的數(shù)據(jù)越少，查詢速度越快。

示例：

SELECT id, name FROM users WHERE age = 30;  -- 避免 SELECT *，只取所需的字段

• 分解復雜查詢 ：將復雜的查詢拆分為多個小查詢，有時能提升性能，尤其是在涉及多個關(guān)聯(lián)表時。例如，把一個包含多個 JOIN 的復雜查詢，拆分成多次查詢緩存中間結(jié)果。

• 使用 LIMIT 優(yōu)化分頁 ：在大表的分頁查詢中，避免掃描大量數(shù)據(jù)。可以通過主鍵或者索引結(jié)合 LIMIT 優(yōu)化。

示例：

SELECT * FROM users WHERE id > 1000 LIMIT 10;  -- 基于索引的分頁

5. 優(yōu)化 JOIN 操作

JOIN 操作在多表查詢中常見，但它們?nèi)菀讓е滦阅軉栴}，特別是當表很大時。優(yōu)化 JOIN 時的注意事項：

• 確保連接條件字段有索引 ：對于 JOIN 中使用的字段，確保它們有合適的索引。

示例：

SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status = 'active';

在這種情況下， user_id 和 id 應(yīng)該分別在 orders 和 users 表上有索引。

• 盡量減少關(guān)聯(lián)表的數(shù)據(jù)量 ：通過先篩選出需要的數(shù)據(jù)，再進行 JOIN 操作。例如，將篩選條件放在子查詢中，減少需要關(guān)聯(lián)的行數(shù)。

示例：

SELECT * FROM (SELECT id FROM users WHERE status = 'active') u JOIN orders o ON o.user_id = u.id;

6. 調(diào)整 MySQL 配置參數(shù)

MySQL 的一些配置參數(shù)直接影響查詢性能，特別是在高并發(fā)、大數(shù)據(jù)量場景下。以下是一些常見的優(yōu)化參數(shù)：

• innodb_buffer_pool_size ：這是 InnoDB 的緩沖池大小，決定了 MySQL 可以用多少內(nèi)存來緩存數(shù)據(jù)頁。這個值通常設(shè)置為系統(tǒng)內(nèi)存的 70%-80%，以便盡可能減少磁盤 I/O。

• query_cache_size ：如果系統(tǒng)中大量的查詢結(jié)果是相同的，可以啟用查詢緩存，以減少重復查詢的開銷。需要注意的是，MySQL 8.0 中查詢緩存被棄用，因為它對高并發(fā)場景可能帶來性能瓶頸。

• tmp_table_size 和 max_heap_table_size ：這些參數(shù)決定了臨時表可以在內(nèi)存中使用的最大大小，增大這些參數(shù)的值，可以避免頻繁將臨時表寫入磁盤，從而提高排序和 GROUP BY 查詢的效率。

7. 使用分區(qū)表

如果你的表非常大，可以考慮使用 分區(qū)表 來優(yōu)化查詢性能。分區(qū)表將數(shù)據(jù)分成多個更小的物理子表，MySQL 可以根據(jù)查詢條件直接定位到某個分區(qū)，從而減少掃描的數(shù)據(jù)量。

示例：

CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    user_id INT,
    ...
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2015),
    PARTITION p2 VALUES LESS THAN (2020),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

8. 避免死鎖和長事務(wù)

如果某個慢查詢是由于事務(wù)沖突或死鎖造成的，應(yīng)該盡量避免長事務(wù)或頻繁鎖表。通過控制事務(wù)范圍、使用合適的隔離級別、避免大批量寫操作等方式，減少鎖等待和死鎖的發(fā)生，從而加快查詢速度。

9. 緩存

除了使用 MySQL 自身的查詢緩存，你還可以使用應(yīng)用層的緩存機制（如 Redis、Memcached），將頻繁訪問的數(shù)據(jù)緩存到內(nèi)存中，減少數(shù)據(jù)庫的訪問頻率。

示例：

# 在應(yīng)用層緩存 MySQL 查詢結(jié)果
cache.set('users:active', active_users, timeout=60*5)  # 緩存 5 分鐘

總結(jié)：

• 分析慢查詢?nèi)罩? 和使用 EXPLAIN 了解執(zhí)行計劃是優(yōu)化慢查詢的第一步。
• 創(chuàng)建合適的索引 可以顯著提高查詢速度，尤其是在大表中。
• 優(yōu)化查詢語句和 JOIN 操作 ，盡量減少掃描的行數(shù)，并盡量使用索引覆蓋查詢。
• 通過調(diào)整 MySQL 配置參數(shù) ，提升系統(tǒng)對內(nèi)存和資源的利用率。
• 在大表上使用分區(qū)表，并通過緩存減少對數(shù)據(jù)庫的壓力

最后說一句(求關(guān)注，求贊，別白嫖我)

最近無意間獲得一份阿里大佬寫的刷題筆記，一下子打通了我的任督二脈，進大廠原來沒那么難。
這是大佬寫的， 7701頁的BAT大佬寫的刷題筆記，讓我offer拿到手軟

本文，已收錄于，我的技術(shù)網(wǎng)站 cxykk.com：程序員編程資料站 ，有大廠完整面經(jīng)，工作技術(shù)，架構(gòu)師成長之路，等經(jīng)驗分享

求一鍵三連：點贊、分享、收藏

點贊對我真的非常重要！在線求贊，加個關(guān)注我會非常感激！