後端程序員必備：書寫高質量SQL的 N 條建議 [SQL 效率/速度 提升/加快]

資料來源: https://mp.weixin.qq.com/s?__biz=MzIxMjE5MTE1Nw==&mid=2653202914&idx=2&sn=5641cbcf31e7820015a89e2f1db00c12&chksm=8c99db38bbee522e4db1e5e47afe9b0183a3d0b19719254fc7914676989f65988639aa72e769&scene=126&sessiONid=1588814305&key=1c0393bf1db6185899b131d6445488036fb5f840345a172c1866feae856c87156027db9c791520e4da78c0739e3a28639dec73b48114889e2b71690d45dffa20c50585716fa6979c50c0b72a7d0ed1e5&ascene=1&uin=MjIwODk2NDgxNw%3D%3D&devicetype=Windows+10+x64&versiON=62090070&lang=zh_TW&exportkey=AvrTTvZrNFtcCLvvBjoo%2BrQ%3D&pass_ticket=6EMnHONfG4zna6UxSx%2F8izfPblHff0SCTosru7qpWzBdBURupZil5ANHNAPJvoUo

01、查詢SQL盡量不要使用SELECT *，而是SELECT具體字段。

反例子：

SELECT * FROM employee;

正例子：

SELECT id,name FROM employee;

理由：

只取需要的字段，節省資源、減少網絡開銷。

SELECT * 進行查詢時，很可能就不會使用到覆蓋索引了，就會造成回表查詢。

02、如果知道查詢結果只有一條或者只要最大/最小一條記錄，建議用limit 1

假設現在有employee員工表，要找出一個名字叫jay的人.

反例：

SELECT id,name FROM employee WHERE name='jay'

正例

SELECT id,name FROM employee WHERE name='jay' limit 1;

理由：

加上limit 1後,只要找到了對應的一條記錄,就不會繼續向下掃描了,效率將會大大提高。

當然，如果name是唯一索引的話，是不必要加上limit 1了，因為limit的存在主要就是為了防止全表掃描，從而提高性能,如果一個語句本身可以預知不用全表掃描，有沒有limit ，性能的差別並不大。

03、應盡量避免在WHERE子句中使用or來連接條件

假設現在需要查詢userid為1或者年齡為18歲的用戶，很容易有以下SQL

反例:

SELECT * FROM user WHERE userid=1 or age=18

正例：

//使用UNION ALL
SELECT * FROM user WHERE userid=1 UNION  ALL SELECT * FROM user WHERE age=18

理由：

使用or可能會使索引失效，從而全表掃描。

對於or+沒有索引的age這種情況，假設它走了userId的索引，但是走到age查詢條件時，它還得全表掃描，也就是需要三步過程：全表掃描+索引掃描+合併
如果它一開始就走全表掃描，直接一遍掃描就完事。mysql是有優化器的，處於效率與成本考慮，遇到or條件，索引可能失效，看起來也合情合理。

04、優化limit分頁
我們日常做分頁需求時，一般會用limit 實現，但是當偏移量特別大的時候，查詢效率就變得低下。

反例：

SELECT id,name,age FROM employee limit 10000,10

正例：

//方案一 ：返回上次查询的最大记录(偏移量)
SELECT id,name FROM employee WHERE id>10000 limit 10

//方案二：order by + 索引
SELECT id,name FROM employee order by id limit 10000,10

理由：

當偏移量最大的時候，查詢效率就會越低，因為Mysql並非是跳過偏移量直接去取後面的數據，而是先把偏移量+要取的條數，然後再把前面偏移量這一段的數據拋棄掉再返回的。

如果使用優化方案一，返回上次最大查詢記錄（偏移量），這樣可以跳過偏移量，效率提升不少。

方案二使用order by+索引，也是可以提高查詢效率的。

05、優化你的like語句
日常開發中，如果用到模糊關鍵字查詢，很容易想到like，但是like很可能讓你的索引失效。

反例：

SELECT userId,name FROM user WHERE userId like '%123';

正例：

SELECT userId,name FROM user WHERE userId like '123%';

理由：

把%放前面，並不走索引

06、應盡量避免在WHERE子句中對字段進行表達式操作，這將導致系統放棄使用索引而進行全表掃

反例：

SELECT * FROM user WHERE age-1 =10；

正例：

SELECT * FROM user WHERE age =11；

理由：

雖然age加了索引，但是因為對它進行運算，索引直接迷路了。

07、INNER JOIN 、LEFT JOIN、RIGHT JOIN，優先使用INNER JOIN，如果是LEFT JOIN，左邊表結果盡量小

INNER JOIN 內連接，在兩張表進行連接查詢時，只保留兩張表中完全匹配的結果集

LEFT JOIN 在兩張表進行連接查詢時，會返回左表所有的行，即使在右表中沒有匹配的記錄。

RIGHT JOIN 在兩張表進行連接查詢時，會返回右表所有的行，即使在左表中沒有匹配的記錄。

都滿足SQL需求的前提下，推薦優先使用INNER JOIN（內連接），如果要使用LEFT JOIN，左邊表數據結果盡量小，如果有條件的盡量放到左邊處理。

反例:

SELECT * FROM tab1 t1 LEFT JOIN tab2 t2 ON t1.size = t2.size WHERE t1.id>2;

正例：

SELECT * FROM (SELECT * FROM tab1 WHERE id >2) t1 LEFT JOIN tab2 t2 ON t1.size = t2.size;

理由：

如果INNER JOIN是等值連接，或許返回的行數比較少，所以性能相對會好一點。

同理，使用了左連接，左邊表數據結果盡量小，條件盡量放到左邊處理，意味著返回的行數可能比較少。

08、應盡量避免在WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。

反例：

SELECT age,name FROM user WHERE age <>18;

正例：

//可以考虑分开两条sql写
SELECT age,name FROM user WHERE age <18;
SELECT age,name FROM user WHERE age >18;

理由：

使用!=和<>很可能會讓索引失效

09、對查詢進行優化，應考慮在WHERE及order by涉及的列上建立索引，盡量避免全表掃描。

10、不要有超過5個以上的表連接

11、盡量用uniON all替換uniON

12、索引不宜太多，一般5個以內。
索引並不是越多越好，索引雖然提高了查詢的效率，但是也降低了插入和更新的效率。

insert或update時有可能會重建索引，所以建索引需要慎重考慮，視具體情況來定。

一個表的索引數最好不要超過5個，若太多需要考慮一些索引是否沒有存在的必要。

13、盡量使用數字型字段，若只含數值信息的字段盡量不要設計為字符型