打造出色查詢：如何優化SQL查詢？
本文轉載自公眾號“讀芯術”(ID：AI_Discovery)。
我們致力于打造能夠較好運行并延續較長一段時間的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;
正例：
- //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 <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;
理由：能夠檢查需要的數據，避免返回非必要數據，并能節省費用和計算機開銷。
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時間。
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;
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查詢是門技術活，常常思考和嘗試，你會打開新世界的大門。
