打造出色查詢:如何優化SQL查詢?

全文共2630字，預計學習時長14分鐘

圖源：unsplash

我們致力於打造能夠較好運行並延續較長一段時間的query（查詢）。本文將給出關於優化SQL語句的幾點建議，希望能夠幫到你。

1.嘗試不去用select *來查詢SQL，而是選擇專用欄位。

反例：

select * from employee;

正例:

select id，name fromemployee;

理由：

· 通過只用必要欄位進行查詢，能夠節省資源並減少網絡開銷。

· 這樣做可能不會使用覆蓋索引，會導致一個查詢返回到表中。

2.如果已知只有一個查詢結果，推薦使用limit 1

假設有一張員工表格，想在其中找到一名叫jay的員工。

CREATE TABLE employee (

id int(11) NOT NULL,

name varchar(255) DEFAULT NULL,

age int(11) DEFAULT NULL,

date datetime DEFAULT NULL,

sex int(1) DEFAULT NULL,

PRIMARY KEY (`id`) );

反例:

select id，name from employeewhere name='jay';

正例:

select id，name from employeewhere name='jay' limit 1;

理由：添加limit 1後，查找到相應的記錄時，便不會繼續查找下去，效率會大大提高。

3.嘗試避免在 where 子句中使用or來連接條件

創建一個新的用戶表格，其有一個常規索引userId，表格結構如下：

CREATE TABLE `user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`userId` int(11) NOT NULL,

`age` int(11) NOT NULL,

`name` varchar(255) NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_userId` (`userId`) )

現在假設需要查詢userid為1或為18歲的用戶，使用如下的SQL就會很簡單。

反例:

select * from user where userid = 1 or age = 18;

正例:

//Use union all

select * from user where userid=1

union all

select * from user where age = 18;//Or write two separate SQL

select * from user where userid=1;

select * from user where age = 18;

理由：or 的使用可能會使索引無效，因此需要進行全表掃描。

在or 無索引的情況下，假設已採用userId索引，但是當涉及到 age（年齡）查詢條件時，必須執行全表掃描，其過程分為三步：全表掃描+索引掃描+合併。

圖源：unsplash

4.儘可能避免在where子句中使用!=或<>運算符，否則，引擎將放棄使用索引並執行全表掃描。

反例:

select age,name from user where age<>18;

正例:

//You can consider separate two sql writeselect age,name from user where age <18;

select age,name from user where age>18;

理由：使用!=和<>可能使索引無效。

5.優化limit分頁

通常用limits來實現日常分頁，但當偏移量特別大時，查詢效率便會降低。因為Mysql不會跳過偏移量，而是直接獲取數據。

反例:

select id，name，age from employeelimit 10000，10;

正例:

//Solution 1: Return the largest record (offset) of the last query

select id，name from employeewhere id>10000 limit 10;//Solution 2: order by + index

select id，name from employeeorder by id limit 10000，10;

理由：

· 如果使用了優化方案1，則會返回最末的查詢記錄（偏移量），因此可以跳過該偏移量，效率自然會大幅提高。

· 選項二：使用+索引排序，也可以提高查詢效率。

6.優化like語句

在日常開發中，如果使用模糊關鍵字查詢，我們很容易想到like，但like可能會使索引無效。

反例:

select userId，name from user where userId like '%123';

正例:

select userId，name from user where userId like '123%';

理由：https://medium.com/@pawanjain.432/hey-thanks-dovid-for-pointing-out-a-typo-in-13-1000a4103fe6

7.使用where條件限制將要查詢的數據來避免返回額外行

假設要查詢一名用戶是否為會員，老式執行代碼會這樣做。

反例：

List<Long> userIds = sqlMap.queryList("select userId from userwhere isVip=1");boolean isVip = userIds.contains(userId);

正例：

Long userId = sqlMap.queryObject("select userId from user whereuserId='userId' and isVip='1' ")boolean isVip = userId！=null;

理由：能夠檢查需要的數據，避免返回非必要數據，並能節省費用和計算機開銷。

圖源：unsplash

8.考慮在where子句中使用默認值而不是null

反例：

select * from user where age is not null;

正例：

select * from user where age>0; //Set 0 as default

理由：如果用默認值取代null值，則通常可以建立索引，與此同時，表達式將相對清晰。

9.如果插入數據過多，可以考慮批量插入

反例：

for(User u :list){

INSERT into user(name,age)values(#name#,#age#)

}

正例：

//One batch of 500 inserts, carried out in batchesinsert intouser(name,age) values

<foreach collection="list" item="item"index="index" separator=",">

(#{item.name},#{item.age})

</foreach>

理由：批量插入性能良好且省時。

打個比方，在有電梯的情況下，你需要將1萬塊磚移送到建築物的頂部。電梯一次可以放置適當數量的磚塊（最多500塊），你可以選擇一次運送一塊磚，也可以一次運送500塊。哪種方案更好？

10.謹慎使用distinct關鍵詞

Distinct關鍵詞通常用於過濾重複記錄以返回唯一記錄。當其被用於查詢一個或幾個欄位時，Distinct關鍵詞將為查詢帶來優化效果。然而，在欄位過多的情況下，Distinct關鍵詞將大大降低查詢效率。

反例:

SELECT DISTINCT * from user;

正例:

select DISTINCT name from user;

理由：帶有「distinct」語句的CPU時間和佔用時間高於沒有「 distinct」的語句。

如果在查詢多欄位時使用distinct，資料庫引擎將比較數據，並濾除重複的數據。然而，該比較和濾除過程將消耗系統資源和CPU時間。

圖源：unsplash

11.刪除多餘和重複的索引

反例：

KEY `idx_userId` (`userId`)

KEY `idx_userId_age` (`userId`,`age`)

正例：

//Delete the userId index, because the combined index (A, B) is equivalentto creating the (A) and (A, B) indexesKEY `idx_userId_age` (`userId`,`age`)

理由：若保留重複的索引，那麼優化程序在優化查詢時也需要對其進行一一考量，這會影響性能。

12.如果數據量很大，優化 modify或delete語句

避免同時修改或刪除過多數據，因其將導致CPU利用率過高，從而影響他人對資料庫的訪問。

反例：

//Delete 100,000 or 1 million+ at a time?

delete from user where id <100000;//Or use single cycle operation, lowefficiency and long time

for（User user：list）{

delete from user；}

正例：

//Delete in batches, such as 500 each timedelete user where id<500;

delete product where id>=500 and id<1000；

理由：一次刪除過多數據，可能會導致lock wait timeout exceed error（鎖定等待超時錯誤），因此建議分批操作。

13.使用explain分析SQL方案

在日常開發中編寫SQL時，嘗試養成習慣：使用explain來分析自己編寫的SQL，尤其是索引。

explain select * from user where userid = 10086 or age =18;

圖源：unsplash

14.嘗試用union all代替union

如果搜索結果裡沒有重複的記錄，我推薦用union all代替union。

反例:

select * from user where userid=1

union

select * from user where age = 10

正例:

select * from user where userid=1

union all

select * from user where age = 10

理由：

· 如果使用union，則無論有沒有重複的搜索結果，它都會嘗試對其進行合併、排序，然後輸出最終結果。

· 若已知搜索結果中沒有重複記錄，用union all代替union將提高效率。

15.儘可能使用數字欄位。如果欄位僅包含數字信息，嘗試不將其設置為字符類型。

反例：

`king_id` varchar（20） NOT NULL;

正例：

`king_id` int(11) NOT NULL;

理由：與數字欄位相比，字符類型將降低查詢和連接的性能，並會增加存儲開銷。

16.儘可能用varchar或nvarchar代替char或nchar

反例：

`deptName` char(100) DEFAULT NULL

正例：

`deptName` varchar(100) DEFAULT NULL

理由：

· 首先，由於可變長度欄位的存儲空間很小，該方法可以節省存儲空間。

· 其次，對於查詢而言，在相對較小的欄位中搜索會更有效率。

優化和加速SQL查詢是門技術活，常常思考和嘗試，你會打開新世界的大門。

留言點讚關注

我們一起分享AI學習與發展的乾貨

如轉載，請後臺留言，遵守轉載規範