打造出色查詢：如何優(yōu)化SQL查詢？
本文轉(zhuǎn)載自公眾號(hào)“讀芯術(shù)”(ID：AI_Discovery)。
我們致力于打造能夠較好運(yùn)行并延續(xù)較長(zhǎng)一段時(shí)間的query(查詢)。本文將給出關(guān)于優(yōu)化SQL語(yǔ)句的幾點(diǎn)建議，希望能夠幫到你。
1. 嘗試不去用select *來(lái)查詢SQL，而是選擇專用字段。
反例：
- select * from employee;
正例：
- select id，name fromemployee;
理由：
- 通過(guò)只用必要字段進(jìn)行查詢，能夠節(jié)省資源并減少網(wǎng)絡(luò)開(kāi)銷。
- 這樣做可能不會(huì)使用覆蓋索引，會(huì)導(dǎo)致一個(gè)查詢返回到表中。
2. 如果已知只有一個(gè)查詢結(jié)果，推薦使用limit 1
假設(shè)有一張員工表格，想在其中找到一名叫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后，查找到相應(yīng)的記錄時(shí)，便不會(huì)繼續(xù)查找下去，效率會(huì)大大提高。
3. 嘗試避免在 where 子句中使用or來(lái)連接條件
創(chuàng)建一個(gè)新的用戶表格，其有一個(gè)常規(guī)索引userId，表格結(jié)構(gòu)如下：
- 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`) )
現(xiàn)在假設(shè)需要查詢userid為1或?yàn)?8歲的用戶，使用如下的SQL就會(huì)很簡(jiǎn)單。
反例：
- 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 的使用可能會(huì)使索引無(wú)效，因此需要進(jìn)行全表掃描。
在or 無(wú)索引的情況下，假設(shè)已采用userId索引，但是當(dāng)涉及到 age(年齡)查詢條件時(shí)，必須執(zhí)行全表掃描，其過(guò)程分為三步：全表掃描+索引掃描+合并。
4. 盡可能避免在where子句中使用!=或<>運(yùn)算符，否則，引擎將放棄使用索引并執(zhí)行全表掃描。
反例：
- 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;
理由：使用!=和<>可能使索引無(wú)效。
5. 優(yōu)化limit分頁(yè)
通常用limits來(lái)實(shí)現(xiàn)日常分頁(yè)，但當(dāng)偏移量特別大時(shí)，查詢效率便會(huì)降低。因?yàn)镸ysql不會(huì)跳過(guò)偏移量，而是直接獲取數(shù)據(jù)。
反例：
- 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;
理由：
- 如果使用了優(yōu)化方案1，則會(huì)返回最末的查詢記錄(偏移量)，因此可以跳過(guò)該偏移量，效率自然會(huì)大幅提高。
- 選項(xiàng)二：使用+索引排序，也可以提高查詢效率。
6. 優(yōu)化like語(yǔ)句
在日常開(kāi)發(fā)中，如果使用模糊關(guān)鍵字查詢，我們很容易想到like，但like可能會(huì)使索引無(wú)效。
反例：
- 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條件限制將要查詢的數(shù)據(jù)來(lái)避免返回額外行
假設(shè)要查詢一名用戶是否為會(huì)員，老式執(zhí)行代碼會(huì)這樣做。
反例：
- 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;
理由：能夠檢查需要的數(shù)據(jù)，避免返回非必要數(shù)據(jù)，并能節(jié)省費(fèi)用和計(jì)算機(jī)開(kāi)銷。
8. 考慮在where子句中使用默認(rèn)值而不是null
反例：
- select * from user where age is not null;
正例：
- select * from user where age>0; //Set 0 as default
理由：如果用默認(rèn)值取代null值，則通常可以建立索引，與此同時(shí)，表達(dá)式將相對(duì)清晰。
9. 如果插入數(shù)據(jù)過(guò)多，可以考慮批量插入
反例：
- 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>
理由：批量插入性能良好且省時(shí)。
打個(gè)比方，在有電梯的情況下，你需要將1萬(wàn)塊磚移送到建筑物的頂部。電梯一次可以放置適當(dāng)數(shù)量的磚塊(最多500塊)，你可以選擇一次運(yùn)送一塊磚，也可以一次運(yùn)送500塊。哪種方案更好?
10. 謹(jǐn)慎使用distinct關(guān)鍵詞
Distinct關(guān)鍵詞通常用于過(guò)濾重復(fù)記錄以返回唯一記錄。當(dāng)其被用于查詢一個(gè)或幾個(gè)字段時(shí)，Distinct關(guān)鍵詞將為查詢帶來(lái)優(yōu)化效果。然而，在字段過(guò)多的情況下，Distinct關(guān)鍵詞將大大降低查詢效率。
反例：
- SELECT DISTINCT * from user;
正例：
- select DISTINCT name from user;
理由：帶有“distinct”語(yǔ)句的CPU時(shí)間和占用時(shí)間高于沒(méi)有“ distinct”的語(yǔ)句。
如果在查詢多字段時(shí)使用distinct，數(shù)據(jù)庫(kù)引擎將比較數(shù)據(jù)，并濾除重復(fù)的數(shù)據(jù)。然而，該比較和濾除過(guò)程將消耗系統(tǒng)資源和CPU時(shí)間。
11. 刪除多余和重復(fù)的索引
反例：
- 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`)
理由：若保留重復(fù)的索引，那么優(yōu)化程序在優(yōu)化查詢時(shí)也需要對(duì)其進(jìn)行一一考量，這會(huì)影響性能。
12. 如果數(shù)據(jù)量很大，優(yōu)化 modify或delete語(yǔ)句
避免同時(shí)修改或刪除過(guò)多數(shù)據(jù)，因其將導(dǎo)致CPU利用率過(guò)高，從而影響他人對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)。
反例：
- //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；
理由：一次刪除過(guò)多數(shù)據(jù)，可能會(huì)導(dǎo)致lock wait timeout exceed error(鎖定等待超時(shí)錯(cuò)誤)，因此建議分批操作。
13. 使用explain分析SQL方案
在日常開(kāi)發(fā)中編寫(xiě)SQL時(shí)，嘗試養(yǎng)成習(xí)慣：使用explain來(lái)分析自己編寫(xiě)的SQL，尤其是索引。
- explain select * from user where userid = 10086 or age =18;
14. 嘗試用union all代替union
如果搜索結(jié)果里沒(méi)有重復(fù)的記錄，我推薦用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，則無(wú)論有沒(méi)有重復(fù)的搜索結(jié)果，它都會(huì)嘗試對(duì)其進(jìn)行合并、排序，然后輸出最終結(jié)果。
- 若已知搜索結(jié)果中沒(méi)有重復(fù)記錄，用union all代替union將提高效率。
15. 盡可能使用數(shù)字字段。如果字段僅包含數(shù)字信息，嘗試不將其設(shè)置為字符類型。
反例：
- `king_id` varchar（20） NOT NULL;
正例：
- `king_id` int(11) NOT NULL;
理由：與數(shù)字字段相比，字符類型將降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開(kāi)銷。
16. 盡可能用varchar或nvarchar代替char或nchar
反例：
- `deptName` char(100) DEFAULT NULL
正例：
- `deptName` varchar(100) DEFAULT NULL
理由：
- 首先，由于可變長(zhǎng)度字段的存儲(chǔ)空間很小，該方法可以節(jié)省存儲(chǔ)空間。
- 其次，對(duì)于查詢而言，在相對(duì)較小的字段中搜索會(huì)更有效率。
優(yōu)化和加速SQL查詢是門技術(shù)活，常常思考和嘗試，你會(huì)打開(kāi)新世界的大門。
