切換語言為:簡體

SQL 調優最佳實踐

  • 爱糖宝
  • 2024-07-12
  • 2096
  • 0
  • 0

寫作背景

網上一直在說 SQL 調優,到底什麼是 SQL 調優?是不是覺得自己知道但又很模糊說不清楚,面試被問沒有真實的案例,並不具備說服力。本文以為詳細案例給大家解讀。

開始之前,先回答什麼是 SQL 調優,SQL 調優是爲了讓 SQL 獲得更好的效能、查詢更快、資源消耗少。簡單概述就是別人寫的 SQL 查詢耗時 10s,掃描資料 100w 行;你寫的 SQL 耗時 100ms,掃描 1w 行,相比較你的 SQL 更優。

SQL 最佳化基本原則

爲了讓 SQL 獲得更好的效能,應該遵循下面幾個原則

  1. 掃描的行數越少越好,最好只掃描需要的資料,避免掃描多餘的資料;

  2. 使用合適的索引,SQL 中的 WHERE 條件,需要保證命中最優的索引,索引選擇錯或者全表掃描,效能可能會很差。

  3. 使用合適的 Join 型別,根據查詢中各個表的大小和關聯性,選擇合適的 Join 型別(本文不講)。

  4. 使用合適的資料庫。首先你要明確你的業務是資料分析型還是業務型的,根據不同業務場景選擇 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 開發實戰';


SQL 調優最佳實踐

從執行計劃中的 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 開發實戰';


SQL 調優最佳實踐

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 開發實戰';


SQL 調優最佳實踐

執行計劃可以看出從索引中查詢了最終結果,並沒有回表了。

覆蓋索引其實並非最快的,還有一種更快的方式,透過主鍵點查。

主鍵點查

先給 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;

SQL 調優最佳實踐

從執行計劃看出,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);
刪除資料

刪除資料應該遵循下面規則

  1. 刪除語句中指定 WHERE 條件,考慮安全層面和效能層面;

  2. 如果刪除大量行(數萬或更多)的時候,使用批次刪除,對於分散式資料庫是有事務限制的;

  3. 如果刪除表內的所有資料,不要使用 DELETE 語句,應該使用 TRUNCATE 語句;

  4. 刪除資料也是一次檢索資料的過程,檢索符合條件的資料刪除,一定要確保 WHERE 條件正確命中索引;

  5. 如果需要刪除表內的所有資料,不要使用 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 行資料(這個數字並非準確的,跟表的列有關係,可以自己測試一個最佳數字)。刪除資料規則如下

  1. 待刪除資料行數<=10000,用上面刪除 SQL 沒問題;

  2. 待刪除資料行數>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 語句後執行計劃如下

SQL 調優最佳實踐

從執行計劃看出全表掃描了,需要給 create_time 列增加索引。

更新資料

update 用於修改指定表資料,和 delete、select 相似,更新表資料也需要遵循一些規則

  1. 在更新語句中指定 WHERE 條件,必要時指定 limit 條數。

  2. 需要更新大量行(數萬或更多)的時候,使用批次更新,對於分散式資料庫是有事務限制的;另外,一次性過多的資料更新,將導致持有鎖時間過長(悲觀事務),或產生大量衝突(樂觀事務)。

update 語法也並不支援 explain analyze,可以把 SQL 語句轉換為 select 分析執行計劃,參考 delete 。

索引最佳實踐

索引能提高 SQL 執行效率,索引的建立和使用也要遵循一些規則

建立索引規則
  1. 不需要的索引及時刪除,避免建立不需要的索引,新增一個索引是有代價的。每增加一個索引,在插入一條資料的時,就要儲存索引資料。索引越多,寫入越慢、並且空間佔用越大。另外過多索引會影響 SQL 最佳化器執行時間,索引過多可能會誤導最佳化器選擇錯誤索引,尤其是大體量表非常明顯;

  2. 建立索引考慮查詢能用上覆蓋索引大幅度提升效能。這裏敲黑板,日常開發中是很難把控的,比如業務需要 10 個列,但建索引不可能把 10 個列都加上,所以並不是所有場景都適合,所以不要盲目的使用覆蓋索引;

  3. 原則上對查詢中需要用到的列建立索引,目的是提高效能。但有些情況並不適合

    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);


  1. 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);
索引使用規則

索引建立好了並不代表索引有意義,索引的目的是爲了加速查詢,索引使用也要遵循一些規則

  1. 確保索引在一些查詢中被用上,如果一個索引沒有被用上,那這個索引是沒有意義的,即使刪除;

  2. 使用索引時需要滿足左字首規則。

以 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 開發實戰');

分析執行計劃會發現,雖然用上了索引但基本都是全表掃

SQL 調優最佳實踐

使用 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 調優可以從上文角度全方位給提問者剖析,你應該會得到他的認可。

0則評論

您的電子郵件等資訊不會被公開,以下所有項目均必填

OK! You can skip this field.