共計 4790 個字符，預計需要花費 12 分鐘才能閱讀完成。

本篇文章給大家分享的是有關如何優化 SQL 查詢，丸趣 TV 小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著丸趣 TV 小編一起來看看吧。

我們致力于打造能夠較好運行并延續較長一段時間的 query(查詢)。丸趣 TV 小編將給出關于優化 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;

正例：

//se 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;

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

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

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

反例：

select age,name from user where age 18;

正例：

//You can consider separate two sql writeselect age,name from user where age   select age,name from user where age

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

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;

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

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

反例：

select * from user where age is not null;

正例：

select * from user where age  //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 時間。

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

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 查詢，丸趣 TV 小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注丸趣 TV 行業資訊頻道。