一、你是如何理解Count(*)和Count(1)的? 這兩個并沒有區(qū)別,不要覺得 count() 會查出全部字段,而 count(1) 不會。所以 count() 會更慢,你覺得 MySQL 作者會這么做嗎? 可以很明確地告訴你們 count() 和 count(1) 是一樣的,而正確有區(qū)別的是
這兩個并沒有區(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');
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拿到手軟
從這可以看出,所謂的最左前綴原則的“最左”,并不是指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字段的條件,這才是“最左”的意思。
行鎖是對具體數(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鎖鎖的是行前面的間隙,注意只是行前面的間隙,你可能會問那表的最后一行前后都有間隙啊,最后一行后面的間隙不鎖嗎?
當然會鎖了,只不,過是交給了一個叫做PAGE_NEW_SUPREMUM的記錄來說,你可以理解為PAGE_NEW_SUPREMUM記錄是InnoDB默認的,它固定作為最后一條記錄,因此只要鎖住PAGE_NEW_SUPREMUM前面的間隙,就相當于鎖住了我們所理解的最后一行后面的間隙。
臨界鎖是行鎖和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)插入新的行。
OCR識別結(jié)果出現(xiàn)了一些錯誤和混亂,導致內(nèi)容不夠清晰。雖然識別不是很理想,但我將根據(jù)我的理解和相關(guān)知識,概述該圖片可能傳達的內(nèi)容。
所謂MVCC就是多版本并發(fā)控制,MySQL為了實現(xiàn)可重復讀這個隔離級別,而且為了不采用鎖機制來實現(xiàn)可重復讀,所以采用MVCC機制來實現(xiàn)。
ReadView(讀取視圖) :
ReadView
,該視圖記錄了當前可見的所有版本,包括哪些事務(wù)是活躍的,哪些事務(wù)已經(jīng)提交。
事務(wù)ID :
ReadView
中,會記錄當前事務(wù)的ID、最小事務(wù)ID和最大事務(wù)ID。
可見性規(guī)則 :
ReadView
中的最大事務(wù)ID:這個事務(wù)的更改對當前事務(wù)不可見。
ReadView
中的活躍事務(wù):則該事務(wù)的更改不可見,因為該事務(wù)還在進行中。
ReadView
中的最小事務(wù)ID:該事務(wù)的更改是可見的,因為它已經(jīng)提交。
創(chuàng)建 ReadView :
ReadView
。它會包含當前事務(wù)的ID、活躍事務(wù)的ID以及最大和最小事務(wù)ID。
讀取數(shù)據(jù) :
ReadView
中的信息,以確定哪些版本的數(shù)據(jù)是可見的。
更新數(shù)據(jù) :
MVCC允許多個事務(wù)在不干擾彼此的情況下同時進行操作,這極大地提高了數(shù)據(jù)庫的并發(fā)性能。通過維護數(shù)據(jù)的多個版本,MVCC保證了數(shù)據(jù)的一致性和隔離性,同時減少了鎖的競爭。
最近無意間獲得一份阿里大佬寫的刷題筆記,一下子打通了我的任督二脈,進大廠原來沒那么難。這是大佬寫的, 7701頁的BAT大佬寫的刷題筆記,讓我offer拿到手軟
Online DDL 是指在不影響數(shù)據(jù)庫服務(wù)的情況下,修改數(shù)據(jù)庫表的結(jié)構(gòu)。通俗點說,就是我們可以在數(shù)據(jù)庫正常運作的同時,對表進行調(diào)整,比如新增列、修改字段類型、添加索引等,而不需要停機維護。
般的DDL操作,比如新增一個字段,會有以下幾個步驟
MySQL 首先會對 DDL 語句進行解析,確保語法正確。例如:
ALTER TABLE users ADD COLUMN age INT;
MySQL 會檢查表
users
是否存在,新增的
age
字段是否與已有的字段沖突(比如字段名重復),數(shù)據(jù)類型是否支持等。
在進行任何表結(jié)構(gòu)變更之前,MySQL 會對表加一個元數(shù)據(jù)鎖(MDL)。元數(shù)據(jù)鎖的作用是防止在變更結(jié)構(gòu)的同時,其他 DDL 操作對表進行修改,保證表結(jié)構(gòu)一致性。
類比 :元數(shù)據(jù)鎖就像在超市里裝貨架時,防止其他人也來同時更改貨架位置,避免混亂。
當我們執(zhí)行
ALTER TABLE
語句時,MySQL 會創(chuàng)建一個
臨時表
。這個臨時表是現(xiàn)有表的一個復制品,并且會按照我們的要求增加新的字段。
age
。
類比 :這就像超市貨架升級時,先在倉庫里搭建一個新的貨架模型,放置相同的商品,同時增加新的商品存放區(qū)。
當 MySQL 完成了數(shù)據(jù)復制后,它會將原表和臨時表進行替換。此時,臨時表變成了正式的表,包含了新字段。
類比 :就像倉庫里的新貨架搭建好后,把它搬進超市,同時替換掉舊貨架。顧客幾乎不會察覺到這個過程。
原始的表被新表替換后,MySQL 會自動刪除舊表的元數(shù)據(jù),釋放空間。這一步在后臺完成,不影響數(shù)據(jù)庫的正常操作。
當所有操作完成后,MySQL 會釋放元數(shù)據(jù)鎖,允許其他 DDL 或 DML 操作繼續(xù)進行。
注意 :這種方法在 不使用 Online DDL 的情況下,可能導致大量的數(shù)據(jù)復制操作,進而對性能有影響,尤其是表數(shù)據(jù)量較大時。
在 MySQL 中,索引是提高查詢效率的關(guān)鍵工具,但有時可能會遇到索引 失效 的情況,導致查詢性能大幅下降。這種情況通常與查詢語句的寫法、數(shù)據(jù)類型的選擇以及數(shù)據(jù)庫的優(yōu)化機制有關(guān)。下面是幾種常見會導致索引失效的場景:
LIKE
時通配符放在前面
如果在
LIKE
語句中,通配符
%
放在字符串的開頭,會導致索引失效。因為在這種情況下,MySQL 無法通過索引快速定位到符合條件的記錄,而需要掃描所有記錄。
SELECT * FROM users WHERE name LIKE '%abc'; -- 索引失效
這種寫法會使得 MySQL 掃描全表,而如果寫成
LIKE 'abc%'
,索引仍然有效。
類比 :這就像你在一大堆文件中查找名字以"abc"開頭的文件名,你可以直接找到相應(yīng)的部分,但如果是查找名字包含"abc"的文件,你就得看每個文件名。
當查詢條件中的字段類型與索引字段的類型不一致時,MySQL 可能不會使用索引。它會先對數(shù)據(jù)進行類型轉(zhuǎn)換,而類型轉(zhuǎn)換會導致無法高效利用索引。
假設(shè)
id
是一個整型字段:
SELECT * FROM users WHERE id = '123'; -- 索引失效
這里的
'123'
是字符串,MySQL 會進行隱式轉(zhuǎn)換,因此索引失效。
如果在查詢中對索引字段使用了函數(shù)或運算操作,MySQL 不能通過索引來查詢,導致索引失效。
SELECT * FROM users WHERE YEAR(created_at) = 2023; -- 索引失效
這里
YEAR(created_at)
是對
created_at
字段進行了函數(shù)操作,因此 MySQL 無法直接使用索引進行查找。
類比 :這就像你想按某種規(guī)律排列的列表中查找內(nèi)容,但你需要先改變它的形式才能找到,導致效率下降。
OR
關(guān)鍵字
當
OR
條件中的某一列沒有索引時,整個查詢的索引都會失效。
SELECT * FROM users WHERE id = 1 OR name = 'Alice'; -- 索引失效
假設(shè)
id
列有索引,而
name
列沒有索引,那么這個查詢就不能利用索引,MySQL 需要進行全表掃描。
優(yōu)化方式 :為
name
字段單獨建立索引,或者改寫查詢邏輯,避免OR
導致的索引失效。
!=
或
<>
)
使用不等于操作符(
!=
或
<>
)時,MySQL 不能有效使用索引,會導致全表掃描。
SELECT * FROM users WHERE age != 30; -- 索引失效
這類查詢通常會導致索引失效,因為 MySQL 無法通過索引定位所有滿足
!=
的記錄。
>
,
<
,
BETWEEN
) 后的列
在復合索引(即多列索引)中,當?shù)谝粋字段使用了范圍查詢時,后續(xù)的字段的索引可能會失效。
假設(shè)我們有一個復合索引
(age, name)
,如下查詢:
SELECT * FROM users WHERE age > 30 AND name = 'Alice'; -- `name` 索引失效
在這種情況下,由于
age
使用了范圍查詢,
name
列的索引將失效,MySQL 無法通過復合索引直接查找。
IS NULL
或
IS NOT NULL
對于索引列使用
IS NULL
或
IS NOT NULL
,有時 MySQL 可能不會利用索引,尤其是在大量數(shù)據(jù)存在
NULL
值的情況下,MySQL 會認為索引的使用不劃算。
SELECT * FROM users WHERE name IS NOT NULL; -- 可能索引失效
NOT IN
或
NOT EXISTS
使用
NOT IN
或
NOT EXISTS
也可能會導致 MySQL 不使用索引,從而引發(fā)全表掃描。
SELECT * FROM users WHERE id NOT IN (1, 2, 3); -- 索引失效
當表中的數(shù)據(jù)量很小,MySQL 可能認為全表掃描比使用索引更高效。在這種情況下,MySQL 會選擇直接掃描而不是通過索引查找。
類比 :如果你只有幾個文件需要查找,花時間先創(chuàng)建目錄索引反而不劃算,直接掃描全部文件更快。
有時,即便索引可用,MySQL 的查詢優(yōu)化器可能會根據(jù)表的統(tǒng)計信息和成本估算,認為全表掃描比使用索引更快,從而放棄索引。
索引失效通常與查詢語句的寫法、數(shù)據(jù)類型、函數(shù)使用、以及 MySQL 查詢優(yōu)化器的決策有關(guān)。為了避免索引失效,需要盡量避免上述常見的情況,如:
LIKE
查詢中避免通配符
%
開頭
通俗的理解,可以把 filesort 理解為數(shù)據(jù)庫的“備用排序方式”。當查詢中的 ORDER BY 語句無法利用索引中的排序順序時,MySQL 就會啟用 filesort 來手動排序結(jié)果。
filesort
?
MySQL 會在某些情況下使用
filesort
,比如:
沒有合適的索引
:
當查詢中的
ORDER BY
字段沒有索引,MySQL 無法利用索引順序,只能借助
filesort
來進行排序。
示例:
SELECT * FROM users ORDER BY age;
假設(shè)
users
表中沒有
age
字段的索引,這時候 MySQL 會進行
filesort
。
多列排序,但索引不匹配
:
當我們對多個列進行排序,而這些列沒有被索引覆蓋或索引順序與排序要求不符時,
filesort
也會被觸發(fā)。
示例:
SELECT * FROM users ORDER BY age, name;
假設(shè)表上只有
age
的索引,但沒有
(age, name)
的復合索引,那么 MySQL 會使用
filesort
。
組合查詢或函數(shù)操作導致索引失效
:
當查詢中對字段進行計算或函數(shù)操作時,即便這些字段有索引,也無法直接利用索引進行排序。
示例:
SELECT * FROM users ORDER BY LENGTH(name);
LENGTH(name)
是一個函數(shù)操作,MySQL 需要手動排序,因此會使用
filesort
。
filesort
的工作方式:
filesort
實際上有兩種實現(xiàn)方式,取決于 MySQL 的版本和配置:
單行數(shù)據(jù)排序
(Older Versions):MySQL 會把查詢結(jié)果的所有行都放入一個緩沖區(qū),然后根據(jù)
ORDER BY
字段逐行比較并排序。這種方式效率相對較低,因為要處理的數(shù)據(jù)量很大。
兩次掃描排序
(Optimized Versions):在較新的 MySQL 版本中,
filesort
會進行優(yōu)化,只會在第一次掃描時收集需要排序的字段和
ROW_ID
,然后通過排序后的
ROW_ID
再去讀取整行數(shù)據(jù)。這種方式減少了排序的數(shù)據(jù)量,提高了性能。
filesort
并不是說每次都會涉及磁盤操作,它有可能在內(nèi)存中完成,但當數(shù)據(jù)量較大時,內(nèi)存不足以完成排序,就可能將數(shù)據(jù)寫入磁盤進行排序,這樣會影響性能。
MySQL 有兩個重要的參數(shù)控制
filesort
行為:
sort_buffer_size
:這是 MySQL 用來在內(nèi)存中排序的緩沖區(qū)大小。如果排序的數(shù)據(jù)能放進這個緩沖區(qū),排序就會在內(nèi)存中完成;否則,MySQL 會將部分數(shù)據(jù)寫入磁盤,從而影響性能。
max_length_for_sort_data
:控制 MySQL 采用哪種
filesort
方法(單行排序或兩次掃描排序)。對于較短的數(shù)據(jù),MySQL 更可能選擇效率較高的兩次掃描排序方式。
filesort
?
使用合適的索引
:
最直接的辦法就是為查詢中的排序字段創(chuàng)建索引。尤其是在有
ORDER BY
子句時,確保創(chuàng)建了復合索引可以有效避免
filesort
。
示例:
CREATE INDEX idx_age_name ON users (age, name);
增加
sort_buffer_size
:
如果無法避免
filesort
,可以通過增加
sort_buffer_size
的大小,確保更多數(shù)據(jù)可以在內(nèi)存中排序,減少磁盤 I/O。
減少排序的數(shù)據(jù)量
:
使用
LIMIT
來限制查詢結(jié)果集的大小,可以減少需要排序的數(shù)據(jù)量,從而減小
filesort
的開銷。
示例:
SELECT * FROM users ORDER BY age LIMIT 100;
盡量避免對排序字段使用函數(shù)
:
在
ORDER BY
中,盡量不要對排序字段進行函數(shù)運算或表達式操作,這樣可以增加 MySQL 使用索引的可能性。
filesort
是 MySQL 中的一種排序機制,當查詢結(jié)果無法通過索引順序排序時,MySQL 就會啟用
filesort
進行手動排序。雖然名字中有“file”,但排序未必一定涉及磁盤操作,內(nèi)存中的排序也是常見的。
filesort
是 MySQL 的備用排序方式,盡管有時不可避免,但我們可以通過創(chuàng)建索引、調(diào)整緩沖區(qū)大小等方式來優(yōu)化它的性能。
當執(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
表的操作會被阻塞,直到變更完成。
UPDATE
或
DELETE
操作
當你執(zhí)行一個
UPDATE
或
DELETE
操作且未使用索引時,MySQL 可能會鎖住整個表進行更新或刪除,因為它必須掃描所有行。
示例:
UPDATE users SET age = 30 WHERE name LIKE '%John%'; -- 如果沒有索引,可能鎖表
由于
LIKE '%John%'
無法利用索引,MySQL 需要全表掃描,并對整個表加鎖。
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
表中的記錄,可能會導致等待。
LOCK TABLES
顯式加鎖
MySQL 支持使用
LOCK TABLES
命令顯式地對表加鎖,分為
讀鎖(READ LOCK)
和
寫鎖(WRITE LOCK)
。在寫鎖期間,其他線程不能對該表進行任何讀或?qū)懖僮;在讀鎖期間,其他線程只能讀表,而不能寫表。
示例:
LOCK TABLES users WRITE; -- 對 `users` 表加寫鎖
此時,其他線程對
users
表的任何讀寫操作都會被阻塞,直到鎖被釋放。
當你使用某些批量插入語句(如
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 需要鎖表來完成整個插入操作。
在 InnoDB 中,當插入或刪除涉及到外鍵約束的數(shù)據(jù)時,MySQL 可能會鎖住父表或子表,確保數(shù)據(jù)的完整性。雖然 InnoDB 大多情況下會使用行鎖,但在某些復雜的情況下,比如沒有合適的索引,可能會導致表鎖。
示例:
DELETE FROM orders WHERE order_id = 100; -- 觸發(fā)外鍵約束檢查,可能鎖住 `customers` 表
如果
orders
表有外鍵關(guān)聯(lián)到
customers
表,且沒有合適的索引,可能會鎖住
customers
表。
在 MyISAM 存儲引擎中,寫操作會鎖住整個表,即使只修改了一行。讀操作之間不會互相阻塞,但讀寫操作之間會發(fā)生阻塞。因此,MyISAM 表在處理高并發(fā)寫操作時可能會頻繁鎖表。
示例:
INSERT INTO myisam_table (name, age) VALUES ('John', 30); -- 寫鎖鎖住整個表
如果有大量的寫操作,表會頻繁被鎖住,影響并發(fā)性能。
鎖表會影響數(shù)據(jù)庫的并發(fā)性和性能,因此我們通常需要盡量避免。這里有一些方法可以減少鎖表的發(fā)生:
盡量使用 InnoDB 存儲引擎,它支持 行級鎖 ,可以在絕大多數(shù)情況下避免鎖表。相比之下, MyISAM 使用的是 表級鎖 ,在并發(fā)讀寫場景下性能較差。
通過為查詢條件中的列創(chuàng)建適當?shù)乃饕,避免全表掃描。例如,如果你?jīng)常根據(jù)
name
字段進行查詢和更新,應(yīng)該為
name
字段創(chuàng)建索引:
CREATE INDEX idx_name ON users (name);
索引可以有效減少鎖表的可能性。
長時間未提交的事務(wù)會持有鎖,從而阻塞其他查詢。因此,盡量縮短事務(wù)的執(zhí)行時間,確保在事務(wù)中盡快完成操作并提交。
OPTIMIZE
和
ANALYZE
慎重
這些命令會鎖住表的元數(shù)據(jù),阻止并發(fā)的讀寫操作。運行這些命令時應(yīng)避免高并發(fā)時間段。
如果需要執(zhí)行大量的
UPDATE
或
DELETE
,可以將操作分批執(zhí)行,以減少每次操作涉及的數(shù)據(jù)量,避免長時間鎖表。
示例:
DELETE FROM users WHERE created_at < '2022-01-01' LIMIT 1000; -- 分批刪除
盡量避免使用
LOCK TABLES
進行顯式加鎖操作,尤其是在高并發(fā)場景下。InnoDB 的事務(wù)機制和行級鎖已經(jīng)足夠應(yīng)對大多數(shù)并發(fā)問題。
在 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ù)。
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)性能,因此盡量避免死鎖是很有必要的。以下是一些常見的避免死鎖的方法:
確保所有事務(wù)在訪問多張表或多條記錄時,遵循相同的順序鎖定資源。這樣可以避免不同事務(wù)間出現(xiàn)交叉鎖定,減少死鎖的可能性。
示例:
所有事務(wù)在更新
users
表和
orders
表時,都先鎖住
users
表,再鎖住
orders
表,避免死鎖。
盡量減少每個事務(wù)鎖定的范圍和時間,避免長時間占用鎖。例如,盡量縮短事務(wù)的執(zhí)行時間,減少不必要的查詢。
示例:
BEGIN;
UPDATE users SET age = age + 1 WHERE id = 1;
COMMIT;
盡量避免在一個事務(wù)內(nèi)進行過多的操作或等待用戶輸入。
在查詢時盡量使用索引來減少鎖定的行數(shù),特別是在
UPDATE
和
DELETE
操作時,合適的索引可以減少鎖定的行數(shù),從而降低死鎖的風險。
示例:
為
user_id
創(chuàng)建索引:
CREATE INDEX idx_user_id ON orders (user_id);
控制數(shù)據(jù)庫的并發(fā)訪問量,如果可能的話,避免在高并發(fā)情況下進行大批量數(shù)據(jù)操作。高并發(fā)訪問會增加死鎖的概率。
使用合適的事務(wù)隔離級別可以減少鎖定沖突。InnoDB 支持多種事務(wù)隔離級別,最常見的是
REPEATABLE READ
和
READ COMMITTED
。其中,
READ COMMITTED
隔離級別可以減少鎖爭用的情況,從而降低死鎖發(fā)生的概率。
示例:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
如果需要對大量數(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ù)和鎖的等待情況。
首先,確保 慢查詢?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)化。
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é)果:
ALL
表示全表掃描,需要優(yōu)化;
range
、
ref
或
const
表示使用了索引,性能較好。
NULL
,表示沒有使用索引。
索引是 MySQL 優(yōu)化慢查詢的最常見手段之一。適當?shù)乃饕梢燥@著減少查詢的掃描行數(shù),提升查詢速度。
常見的索引優(yōu)化策略:
WHERE
子句中的字段創(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);
示例:
SELECT name FROM users WHERE age = 30; -- 如果 name 和 age 都在索引中,MySQL 可以只查索引
WHERE UPPER(name) = 'JOHN'
會導致索引失效。
LIKE '%abc'
這種通配符前綴的查詢。
改進查詢語句的寫法可以大幅提升性能。以下是幾種常見的優(yōu)化建議:
INT
存儲年齡,而不是用
VARCHAR
。
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; -- 基于索引的分頁
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
表上有索引。
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;
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
查詢的效率。
如果你的表非常大,可以考慮使用 分區(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
);
如果某個慢查詢是由于事務(wù)沖突或死鎖造成的,應(yīng)該盡量避免長事務(wù)或頻繁鎖表。通過控制事務(wù)范圍、使用合適的隔離級別、避免大批量寫操作等方式,減少鎖等待和死鎖的發(fā)生,從而加快查詢速度。
除了使用 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 分鐘
EXPLAIN
了解執(zhí)行計劃是優(yōu)化慢查詢的第一步。
JOIN
操作
,盡量減少掃描的行數(shù),并盡量使用索引覆蓋查詢。
最近無意間獲得一份阿里大佬寫的刷題筆記,一下子打通了我的任督二脈,進大廠原來沒那么難。
這是大佬寫的,
7701頁的BAT大佬寫的刷題筆記,讓我offer拿到手軟
本文,已收錄于,我的技術(shù)網(wǎng)站 cxykk.com:程序員編程資料站 ,有大廠完整面經(jīng),工作技術(shù),架構(gòu)師成長之路,等經(jīng)驗分享
點贊對我真的非常重要!在線求贊,加個關(guān)注我會非常感激!
如何使用 Pytorch 中的 DataSet 和 DataLoader
閱讀golang slice相關(guān)常見的性能優(yōu)化手段
閱讀連接Elasticsearch服務(wù)器的Python代碼示例
閱讀國產(chǎn)操作系統(tǒng)上實現(xiàn)RTMP推流攝像頭視頻和麥克風聲音到流媒體服務(wù)器
閱讀多租戶系統(tǒng)數(shù)據(jù)權(quán)限設(shè)計與RuoYi系統(tǒng)的借鑒
閱讀count(*)、count(1)哪個更快?面試必問:通宵整理的十道經(jīng)典MySQL必問面試題
閱讀從需求分析、產(chǎn)品設(shè)計到部署交付各階段說明
閱讀強化學習筆記之【ACE:Off-PolicyActor-CriticwithCausality-AwareEntropyRegularization】
閱讀使用MailKit在.NET Core中收發(fā)郵件的完整示例
閱讀本站所有軟件,都由網(wǎng)友上傳,如有侵犯你的版權(quán),請發(fā)郵件[email protected]
湘ICP備2022002427號-10 湘公網(wǎng)安備:43070202000427號© 2013~2024 haote.com 好特網(wǎng)