寫作背景
網上一直在說 SQL 調優,到底什麼是 SQL 調優?是不是覺得自己知道但又很模糊說不清楚,面試被問沒有真實的案例,並不具備說服力。本文以為詳細案例給大家解讀。
開始之前,先回答什麼是 SQL 調優,SQL 調優是爲了讓 SQL 獲得更好的效能、查詢更快、資源消耗少。簡單概述就是別人寫的 SQL 查詢耗時 10s,掃描資料 100w 行;你寫的 SQL 耗時 100ms,掃描 1w 行,相比較你的 SQL 更優。
SQL 最佳化基本原則
爲了讓 SQL 獲得更好的效能,應該遵循下面幾個原則
掃描的行數越少越好,最好只掃描需要的資料,避免掃描多餘的資料;
使用合適的索引,SQL 中的 WHERE 條件,需要保證命中最優的索引,索引選擇錯或者全表掃描,效能可能會很差。
使用合適的 Join 型別,根據查詢中各個表的大小和關聯性,選擇合適的 Join 型別(本文不講)。
使用合適的資料庫。首先你要明確你的業務是資料分析型還是業務型的,根據不同業務場景選擇 OLTP、OLAP 資料庫(本文不講)。
詳細案例
案例分析開始前,準備一些資料
資料庫:TIDB,TIDB 相容 MYSQL 協議的,沒用過 TIDB 不影響本文閱讀;
資料表:books,圖書系統核心表;
資料量:37w。
drop table if exists books; create table books ( id bigint null, name varchar(255) null, title varchar(255) null, create_time bigint null, update_time bigint null, price DECIMAL(15, 2) NOT NULL DEFAULT 0.00 );
日常開發中,不管上層業務如何編排、業務複雜 or 簡單,對業務表的操作無非就是 CURD 只是 SQL 複雜程度不同而已,每種操作 SQL 都可能存在效能問題導致資料庫穩定性。
DQL 最佳實踐(SELECT)
對於查詢類 SQL ,能單表出結果就不要 JOIN,尤其是 JOIN 多表,資料體量大、索引多的情況下資料庫索引選錯的機率非常大。另外,統計業務儘量不要走關係型資料庫。
查詢類 SQL 一般遇到下面這些效能問題
全表掃描
SELECT 語句執行是全表掃描(一般是沒走索引)或者是用了不合適的索引。
select * from books where title='java 開發實戰';
+-------------------+----------+---------+-------------+-------------+------+ |id |name |title |create_time |update_time |price | +-------------------+----------+---------+-------------+-------------+------+ |1772467546674778112|未開啟憑證完成任務1|java 開發實戰|1711424168639|1716288691077|102.50| +-------------------+----------+---------+-------------+-------------+------+ 1 rows retrieved in 536 ms (execution: 513 ms, fetching: 23 ms)
上面 SQL 查詢結果 1 條資料,耗時 536 ms。為什麼會這麼慢?看看執行計劃
explain analyze select * from books where title='java 開發實戰';
從執行計劃中的 TableFullScan_5 可以看出 TiDB 對錶 books 進行全表掃描,然後對每一行都判斷 title 是否滿足條件。TableFullScan_5 的 estRows 值為 376369.00,說明掃了 376369.00 行資料,最終找到 1 行結果,耗時 536 ms,有人會說這個耗時能接受,但資料體量增加 10 倍、100 倍,SQL 效能就會非常慢了,注意:有同學可能重複多次執行 SQL 發現耗時明顯降低了,這種情況是資料庫快取。
從執行計劃看出 SQL 沒有命中任何索引,先給 books title 列增加索引
CREATE INDEX idx_title ON books (title);
再執行 SQL 看效果
explain analyze select * from books where title='java 開發實戰';
1 row retrieved starting from 1 in 270 ms (execution: 249 ms, fetching: 21 ms)
SQL 命中了 索引 idx_title ,其 estRows 值為 90.18,說明只會掃描 90.18 行資料(資料很神奇有小數點),遠遠小於之前全表掃的 376369.00 行資料,所以效能明顯提升了。
IndexRangeScan_8 idx_index 索引獲取符合條件的索引資料,然後 TableRowIDScan_9 根據索引資料 Row ID 回表查詢相應的行資料,這裏回表查了資料。
如何解決回表的問題?一般使用覆蓋索引。
覆蓋索引
上面用索引最佳化了全表掃描,先透過索引 idx_title 查詢到符合索引資料,再透過索引的 Row ID 回表查詢響應的資料,如果我們能透過索引查到最終結果減少回表資料,也能大大提高 SQL 效率。
假設業務上透過 title 查詢 price 和 id,SQL 如下
select id,title,price from books where title='java 開發實戰';
+-------------------+---------+------+ |id |title |price | +-------------------+---------+------+ |1772467546674778112|java 開發實戰|102.50| +-------------------+---------+------+ 1 row retrieved starting from 1 in 270 ms (execution: 249 ms, fetching: 21 ms)
索引只包含了 title 資訊,所以需要回表查詢 id 和 price 資料。下面我們刪除 idx_title 索引,新增 id、title、price 列組合索引
drop index idx_title on books; CREATE INDEX idx_title_id_price ON books (title,id,price);
執行 SQL
1 row retrieved starting from 1 in 204 ms (execution: 178 ms, fetching: 26 ms)
增加了 idx_title_id_price 索引後,SQL 執行效率更高,速度更快,檢視執行計劃
explain analyze select id,title,price from books where title='java 開發實戰';
執行計劃可以看出從索引中查詢了最終結果,並沒有回表了。
覆蓋索引其實並非最快的,還有一種更快的方式,透過主鍵點查。
主鍵點查
先給 id 列增加主鍵索引。
ALTER TABLE books ADD PRIMARY KEY(id);
執行 SQL
select * from books where id=1772467546674778112;
執行結果
+-------------------+----------+---------+-------------+-------------+------+ |id |name |title |create_time |update_time |price | +-------------------+----------+---------+-------------+-------------+------+ |1772467546674778112|未開啟憑證完成任務1|java 開發實戰|1711424168639|1716288691077|102.50| +-------------------+----------+---------+-------------+-------------+------+ 1 row retrieved starting from 1 in 199 ms (execution: 178 ms, fetching: 21 ms)
從結果來看,執行效率比覆蓋索引更高、更快,檢視執行計劃
explain analyze select * from books where id=1772467546674778112;
從執行計劃看出,Point_Get(又名 “點查”),執行速度也非常快。
本文暫時不講 join 類執行計劃,join 類 SQL 更復雜,放到下一節專門來講。在日常開發中,儘量少用 join 查詢尤其是大表場景,用不好很容易慢查詢,可以從單表查詢結果後,再 byids 點查詢附表資料即可,這塊我踩了非常多坑的。
避免不必要的資訊
如果非必要,不要總用 select * 返回所有列資料,日常開發中發現一些同學爲了簡便會把一些無用的資料帶出來,然後用程式碼過濾,這些完全沒必要。
select * from books where title='java 開發實戰';
改為
select id,title,price from books where title='java 開發實戰';
業務上可以做一些規則,根據外部傳入的列資訊查詢時返回對應的列即可(預設返回一些基礎列),業務方呼叫會複雜一些,針對列多的場景收益是非常大的。
比如:預設只返回列是 id,其它列業務方傳入則返回。
DML 最佳實踐(INERT、UPDATE、DELETE)
多行資料操作切勿單條 SQL 操作(攢批概念)
當需要修改多行資料時,推薦使用單個 SQL 操作多行資料的語句,避免單條 SQL 處理。所以,業務層一定要做好贊批操作。
-- 不推薦做法 INSERT INTO books VALUES (1, 'GO 開發實戰'); INSERT INTO books VALUES (2, 'C++ 開發實戰'); DELETE FROM books WHERE id = 1; DELETE FROM books WHERE id = 2; -- 推薦做法 INSERT INTO books VALUES (1, 'GO 開發實戰'), (2, 'C++ 開發實戰'); DELETE FROM books WHERE id IN (1, 2);
刪除資料
刪除資料應該遵循下面規則
刪除語句中指定 WHERE 條件,考慮安全層面和效能層面;
如果刪除大量行(數萬或更多)的時候,使用批次刪除,對於分散式資料庫是有事務限制的;
如果刪除表內的所有資料,不要使用 DELETE 語句,應該使用 TRUNCATE 語句;
刪除資料也是一次檢索資料的過程,檢索符合條件的資料刪除,一定要確保 WHERE 條件正確命中索引;
如果需要刪除表內的所有資料,不要使用 DELETE 語句,應該使用 TRUNCATE 語句。
TRUNCATE 代替 DELETE
當一個表資料不需要時需要刪除全表資料,應該使用 truncate 或者 drop(如果確定表不要了可以用drop)而非使用 delete 。
truncate books; drop table books;
不推薦使用
delete books;
批次刪除資料
假設某一客戶不需要某段時間資料,需要刪除某段時間資料,刪除 SQL 如下
DELETE FROM books WHERE create_time >= '1709890312467' AND create_time <= '1720701398113';
SQL 看上去沒什麼問題,但在資料體量大場景,比如 10w 甚至上 100w 場景上面 SQL 就不適合了。分散式資料庫,一般都有事務限制,超出事務限制資料庫會向業務層拋異常,TIDB 允許單次批次刪除 10000 行資料(這個數字並非準確的,跟表的列有關係,可以自己測試一個最佳數字)。刪除資料規則如下
待刪除資料行數<=10000,用上面刪除 SQL 沒問題;
待刪除資料行數>10000,採用迴圈刪除,直到刪除資料<10000 時退出。
affectedRows := int64(-1) for affectedRows<10000{ affectedRows = DELETE FROM books WHERE create_time >= '1709890312467' AND create_time <= '1720701398113' limit 10000; }
上面的程式碼是我偷懶寫的看懂就行,在業務邏輯層 for 迴圈刪除即可。這裏需要注意,如果資料體量大的場景,建議大家做非同步刪除(比如每次刪除投遞佇列,下次消費再刪除),避免同步刪除大量資料導致業務方邏輯被阻塞(生產環境踩了不少坑)。
各位是否發現刪除資料我並沒有解釋執行計劃,因為 delete 語法並不支援 explain analyze。但可以把 delete 語句轉換成 select 分析執行計劃也是一樣。
explain analyze select title ,price FROM books WHERE create_time >= '1709890312467' AND create_time <= '1720701398113' limit 1000;
delete 語句轉換成 select 語句後執行計劃如下
從執行計劃看出全表掃描了,需要給 create_time 列增加索引。
更新資料
update 用於修改指定表資料,和 delete、select 相似,更新表資料也需要遵循一些規則
在更新語句中指定 WHERE 條件,必要時指定 limit 條數。
需要更新大量行(數萬或更多)的時候,使用批次更新,對於分散式資料庫是有事務限制的;另外,一次性過多的資料更新,將導致持有鎖時間過長(悲觀事務),或產生大量衝突(樂觀事務)。
update 語法也並不支援 explain analyze,可以把 SQL 語句轉換為 select 分析執行計劃,參考 delete 。
索引最佳實踐
索引能提高 SQL 執行效率,索引的建立和使用也要遵循一些規則
建立索引規則
不需要的索引及時刪除,避免建立不需要的索引,新增一個索引是有代價的。每增加一個索引,在插入一條資料的時,就要儲存索引資料。索引越多,寫入越慢、並且空間佔用越大。另外過多索引會影響 SQL 最佳化器執行時間,索引過多可能會誤導最佳化器選擇錯誤索引,尤其是大體量表非常明顯;
建立索引考慮查詢能用上覆蓋索引大幅度提升效能。這裏敲黑板,日常開發中是很難把控的,比如業務需要 10 個列,但建索引不可能把 10 個列都加上,所以並不是所有場景都適合,所以不要盲目的使用覆蓋索引;
原則上對查詢中需要用到的列建立索引,目的是提高效能。但有些情況並不適合
a. 建立索引選擇過濾性好的列,透過增加索引可以顯著提高過濾後的行數,比如身份證號碼、能標識唯一性的等,但有一些列不合適,比如狀態、性別這類過濾性太弱了,比如在 books 表新增一個布林型別欄位 is_del 代表改行是否刪除,查詢語句如下:
select title ,price FROM books WHERE title ='java 開發實戰' and is_del = false; -- 建立索引時並不需要加 is_del 過濾性太弱加上並沒有意義 CREATE INDEX idx_title_create_time ON books (title);
b.where 跟多個查詢條件時,可以選擇組合索引,把等值條件的列放在組合索引的前面,比如:
select title ,price FROM books WHERE title ='java 開發實戰' and create_time >= '1709890312467' AND create_time <= '1720701398113' limit 1000; CREATE INDEX idx_title_create_time ON books (title,create_time);
索引使用規則
索引建立好了並不代表索引有意義,索引的目的是爲了加速查詢,索引使用也要遵循一些規則
確保索引在一些查詢中被用上,如果一個索引沒有被用上,那這個索引是沒有意義的,即使刪除;
使用索引時需要滿足左字首規則。
以 books 表為案例,假設某個業務場景需要對 id、title、price 列建索引;
CREATE INDEX idx_title_id_price ON books (title,id,price);
下面 SQL 能用上索引
select id,title,price from books where title='java 開發實戰';
下面 SQL 不能用上索引
select id,title,price from books where price=102.50;
儘量使用覆蓋索引(自己根據業務判斷),避免使用 select * 需要業務控制;
select id,title,price from books where title='java 開發實戰';
查詢條件使用 !=,NOT IN 時雖然能用上索引,可能效果並不明顯;
select id,title,price from books where title != 'java 開發實戰'; select id,title,price from books where title not in ('java 開發實戰');
分析執行計劃會發現,雖然用上了索引但基本都是全表掃
使用 LIKE 時並且條件是以萬用字元 % 開頭,也無法使用索引;
-- 下面 sql 全索引掃描,不推薦 select id,title,price from books where title like '%java 開發實戰%'; -- 下面 sql 正確命中索引 select id,title,price from books where title like 'java 開發實戰%';
查詢條件是 in 時,建議不要超過 500 個;
當有多個索引提供使用,SQL 最佳化器索引選擇錯誤,你知道最優索引時建議使用強制索引。
總結
SQL 調優其實非常寬泛,並不只包含查詢類 SQL 、索引最佳化等。當被問如何做 SQL 調優可以從上文角度全方位給提問者剖析,你應該會得到他的認可。