1. 避免使用 SELECT *，使用具體字段

反例：

SELECT * FROM crm_customer;

正例：

SELECT id, name, age FROM customer;

使用具體字段可以節(jié)省資源、減少網(wǎng)絡(luò)開銷，且能避免回表查詢。

2. 避免在 WHERE 子句中使用 OR

反例：

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 會(huì)導(dǎo)致索引失效并引發(fā)全表掃描。

3. 使用 LIMIT 避免不必要的數(shù)據(jù)返回

反例：

SELECT id, order_date FROM order_tab WHERE user_id=666 ORDER BY create_date DESC;

正例：

SELECT id, order_date FROM order_tab WHERE user_id=666 ORDER BY create_date DESC LIMIT 1;

LIMIT 提升查詢效率，避免多余的數(shù)據(jù)返回。

4. 使用數(shù)值類型代替字符串

例子：性別字段建議用數(shù)值（如0代表女生，1代表男生）而非字符串（如"WOMEN"、"MAN"）。
原因：數(shù)值類型占用存儲(chǔ)空間小、比較速度更快。

5. 批量操作（插入、刪除、查詢）

反例：

for(User u : list) {
    INSERT INTO user(name, age) VALUES(#name#, #age#);
}

正例：

INSERT INTO user(name, age) VALUES
<foreach collection="list" item="item" index="index" separator=",">
    (#{item.name}, #{item.age})
</foreach>

原因：批量插入性能更優(yōu)。

6. 使用 UNION ALL 替換 UNION（無(wú)重復(fù)記錄時(shí)）

反例：

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 會(huì)排序和合并，UNION ALL 則省去這一步。

7. 盡可能使用 NOT NULL 定義字段

原因：NOT NULL

可以防止出現(xiàn)空指針問(wèn)題。

NULL值存儲(chǔ)也需要額外的空間的，它也會(huì)導(dǎo)致比較運(yùn)算更為復(fù)雜，使優(yōu)化器難以優(yōu)化SQL。

NULL值有可能會(huì)導(dǎo)致索引失效

8. 避免在索引列上使用內(nèi)置函數(shù)

反例：

SELECT userId, loginTime FROM loginuser WHERE DATE_ADD(loginTime, INTERVAL 7 DAY) >= NOW();

正例：

SELECT userId, loginTime FROM loginuser WHERE loginTime >= DATE_ADD(NOW(), INTERVAL -7 DAY);

原因：索引列上使用函數(shù)會(huì)導(dǎo)致索引失效。

9. 避免在 WHERE 子句中對(duì)字段進(jìn)行表達(dá)式操作

反例：

SELECT * FROM user WHERE age - 1 = 10;

正例：

SELECT * FROM user WHERE age = 11;

10. 在 GROUP BY 前進(jìn)行條件過(guò)濾

反例：

SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
HAVING city = '北京';

正例：

SELECT user_id, SUM(amount) AS total_amount
FROM orders
WHERE city = '北京'
GROUP BY user_id;

11. 優(yōu)化 LIKE 語(yǔ)句

反例：

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

正例：

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

原因：% 放在前面會(huì)導(dǎo)致索引失效。
可以加入反向索引，通過(guò)冗余一個(gè)字段來(lái)存儲(chǔ)userId倒序，來(lái)保證索引的命中

12. 使用小表驅(qū)動(dòng)大表

小表先執(zhí)行以減少掃描量，如使用 EXISTS 或 IN 進(jìn)行過(guò)濾。

假設(shè)我們有個(gè)客戶表和一個(gè)訂單表。其中訂單表有10萬(wàn)記錄，客戶表只有1000行記錄。現(xiàn)在要查詢下單過(guò)的客戶信息，可以這樣寫：

SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

EXISTS 會(huì)逐行掃描 customers 表（即小表），對(duì)每一行 c.id，在 orders 表（大表）中檢查是否有 customer_id = c.id 的記錄。當(dāng)然，也可以使用in實(shí)現(xiàn)：

SELECT * FROM customers
WHERE id IN (
    SELECT customer_id FROM orders
);

in 查詢會(huì)先執(zhí)行內(nèi)部查詢部分 SELECT customer_id FROM orders，獲得 orders 表（大表）中的所有 customer_id，然后在 customers 表（小表）中查找匹配的 id。因?yàn)閛rders表的數(shù)據(jù)量比較大，因此這里用exists效果會(huì)相對(duì)更好一點(diǎn)。

13. IN 查詢的元素不宜太多

如果使用了in，即使后面的條件加了索引，還是要注意in后面的元素不要過(guò)多哈。
in元素一般建議不要超過(guò)200個(gè)，如果超過(guò)了，建議分組，每次200一組進(jìn)行哈。
反例：

select user_id,name from user where user_id in (1,2,3...1000000); 

如果我們對(duì)in的條件不做任何限制的話，該查詢語(yǔ)句一次性可能會(huì)查詢出非常多的數(shù)據(jù)，很容易導(dǎo)致接口超時(shí)。尤其有時(shí)候，我們是用的子查詢，in后面的子查詢，你都不知道數(shù)量有多少那種，更容易采坑.
如下這種子查詢：

select * from user where user_id in (select author_id from artilce where type = 1);

正例是，分批進(jìn)行，比如每批200個(gè)：

select user_id,name from user where user_id in (1,2,3...200);

提示客戶端一次性查詢的條數(shù)，不能超過(guò)500條，或者是使用多線程，將這批值使用多線程的方式查詢出來(lái)，然后進(jìn)行數(shù)據(jù)的匯總合并

14、exists 和 in 的取舍

select * from student where school_id in (select id from school);
select * from police p where exists (select 1 from user u where u.id = p.id);

如果子查詢得出的結(jié)果集數(shù)據(jù)較少，主查詢中的表較大且又有索引時(shí)，應(yīng)該用in；反之，如果外層的主查詢數(shù)據(jù)較少，子查詢]中的表大，又有索引時(shí)使用exists。

• 如果是exists，那么以外層表為驅(qū)動(dòng)表，先被訪問(wèn)。
• 如果是in，那么先執(zhí)行子查詢。

in 是把外表和內(nèi)表作 hash 連接，而 exists 是對(duì)外表作 loop 循環(huán)，每次 loop 循環(huán)再對(duì)內(nèi)表進(jìn)行查詢。所以，我們會(huì)以驅(qū)動(dòng)表的快速返回為目標(biāo)，目標(biāo)是以小表驅(qū)動(dòng)大表，這是性能優(yōu)化的本質(zhì)。

15. 優(yōu)化 LIMIT 分頁(yè)

避免深分頁(yè)，使用“標(biāo)簽記錄法”或“延遲關(guān)聯(lián)法”提升性能。
我們?nèi)粘Ｗ龇猪?yè)需求時(shí)，一般會(huì)用 limit 實(shí)現(xiàn)，但是當(dāng)偏移量特別大的時(shí)候，查詢效率就變得低下，也就是出現(xiàn)深分頁(yè)問(wèn)題。
反例：

select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;

我們可以通過(guò)減少回表次數(shù)來(lái)優(yōu)化。一般有標(biāo)簽記錄法和延遲關(guān)聯(lián)法。
標(biāo)簽記錄法就是標(biāo)記一下上次查詢到哪一條了，下次再來(lái)查的時(shí)候，從該條開始往下掃描。就好像看書一樣，上次看到哪里了，你就折疊一下或者夾個(gè)書簽，下次來(lái)看的時(shí)候，直接就翻到啦。
假設(shè)上一次記錄到100000，則SQL可以修改為：

select  id,name,balance FROM account where id > 100000 limit 10;

這樣的話，后面無(wú)論翻多少頁(yè)，性能都會(huì)不錯(cuò)的，因?yàn)槊辛薸d索引。但是這種方式有局限性：需要一種類似連續(xù)自增的字段。延遲關(guān)聯(lián)法延遲關(guān)聯(lián)法，就是把條件轉(zhuǎn)移到主鍵索引樹，然后減少回表。
如下：

select  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) AS acct2 on acct1.id= acct2.id;

優(yōu)化思路就是，先通過(guò)idx_create_time二級(jí)索引樹查詢到滿足條件的主鍵ID，再與原表通過(guò)主鍵ID內(nèi)連接，這樣后面直接走了主鍵索引了，同時(shí)也減少了回表。

16. 優(yōu)先使用連接查詢而非子查詢

因?yàn)槭褂米硬樵儯赡軙?huì)創(chuàng)建臨時(shí)表。
反例：

SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);

IN 子查詢會(huì)在 orders 表中查詢所有 customer_id，并生成一個(gè)臨時(shí)結(jié)果集。

正例：

SELECT DISTINCT c.* FROM customers c JOIN orders o ON c.id = o.customer_id;

通過(guò) JOIN 直接將 customers 和 orders 表關(guān)聯(lián)，符合條件的記錄一次性篩選完成。
MySQL 優(yōu)化器通?？梢岳盟饕齺?lái)加速 JOIN，避免了臨時(shí)表的創(chuàng)建，查詢效果就更佳

17. Inner join 、left join、right join，優(yōu)先使用Inner join，如果是left join，左邊表結(jié)果盡量小

如需 LEFT JOIN，左表數(shù)據(jù)結(jié)果盡量小。

Inner join 內(nèi)連接，在兩張表進(jìn)行連接查詢時(shí)，只保留兩張表中完全匹配的結(jié)果集

left join 在兩張表進(jìn)行連接查詢時(shí)，會(huì)返回左表所有的行，即使在右表中沒(méi)有匹配的記錄。

right join 在兩張表進(jìn)行連接查詢時(shí)，會(huì)返回右表所有的行，即使在左表中沒(méi)有匹配的記錄。

都滿足SQL需求的前提下，推薦優(yōu)先使用Inner join（內(nèi)連接），如果要使用left join，左邊表數(shù)據(jù)結(jié)果盡量小，如果有條件的盡量放到左邊處理。
反例:

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是等值連接，或許返回的行數(shù)比較少，所以性能相對(duì)會(huì)好一點(diǎn)。同理，使用了左連接，左邊表數(shù)據(jù)結(jié)果盡量小，條件盡量放到左邊處理，意味著返回的行數(shù)可能比較少。

18. 避免 != 或 <> 操作符

反例：

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

正例：可分為兩個(gè)查詢。

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

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

19. 使用聯(lián)合索引時(shí)遵循最左匹配原則

例如聯(lián)合索引 (userId, age)，查詢 userId 和 age 時(shí)優(yōu)先使用 userId。

表結(jié)構(gòu)：（有一個(gè)聯(lián)合索引idx_userid_age，userId在前，age在后）

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
   

反例：

select * from user where age = 10;

正例：//符合最左匹配原則

select * from user where userid=10 and age =10;

//符合最左匹配原則

select * from user where userid =10;

理由：當(dāng)我們創(chuàng)建一個(gè)聯(lián)合索引的時(shí)候，如(k1,k2,k3)，相當(dāng)于創(chuàng)建了（k1）、(k1,k2)和(k1,k2,k3)三個(gè)索引，這就是最左匹配原則。聯(lián)合索引不滿足最左原則，索引一般會(huì)失效，但是這個(gè)還跟Mysql優(yōu)化器有關(guān)的。

20. 對(duì) WHERE 和 ORDER BY 涉及的列建索引

反例：

SELECT * FROM user WHERE address = '深圳' ORDER BY age;

正例：
覆蓋索引能夠使得你的SQL語(yǔ)句不需要回表，僅僅訪問(wèn)索引就能夠得到所有需要的數(shù)據(jù)，大大提高了查詢效率。

ALTER TABLE user ADD INDEX idx_address_age (address, age);

21. 使用覆蓋索引

正例：

SELECT id, name FROM user WHERE userid LIKE '123%';

22. 刪除冗余索引

避免重復(fù)索引，節(jié)省資源。
反例：

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

正例:

//刪除userId索引，因?yàn)榻M合索引（A，B）相當(dāng)于創(chuàng)建了（A）和（A，B）索引
  KEY `idx_userId_age` (`userId`,`age`)

理由：重復(fù)的索引需要維護(hù)，并且優(yōu)化器在優(yōu)化查詢的時(shí)候也需要逐個(gè)地進(jìn)行考慮，這會(huì)影響性能的。

23. 避免超過(guò)3個(gè)以上的表連接

不要有超過(guò)3個(gè)以上的表連接連表越多，編譯的時(shí)間和開銷也就越大。

把連接表拆開成較小的幾個(gè)執(zhí)行，可讀性更高。

如果一定需要連接很多表才能得到數(shù)據(jù)，那么意味著糟糕的設(shè)計(jì)了。

24. 索引數(shù)不宜超過(guò)5個(gè)

索引不宜太多，一般5個(gè)以內(nèi)。

索引并不是越多越好，索引雖然提高了查詢的效率，但是也降低了插入和更新的效率。

insert或update時(shí)有可能會(huì)重建索引，所以建索引需要慎重考慮，視具體情況來(lái)定。

一個(gè)表的索引數(shù)最好不要超過(guò)5個(gè)，若太多需要考慮一些索引是否沒(méi)有存在的必要。

25. 索引不適合建立在大量重復(fù)數(shù)據(jù)的字段上

如性別字段，重復(fù)數(shù)據(jù)多時(shí)優(yōu)化器可能放棄索引。

26. 字符串類型字段在 WHERE 中使用引號(hào)

反例：

SELECT * FROM user WHERE userid = 123;

正例：

SELECT * FROM user WHERE userid = '123';

27. 避免返回過(guò)多數(shù)據(jù)量

反例：

SELECT * FROM LivingInfo WHERE watchId = userId AND watchTime >= DATE_SUB(NOW(), INTERVAL 1 YEAR);

正例：

-- 分頁(yè)查詢
SELECT * FROM LivingInfo WHERE watchId = userId AND watchTime >= DATE_SUB(NOW(), INTERVAL 1 YEAR) LIMIT offset, pageSize;

理由：
查詢效率：當(dāng)返回的數(shù)據(jù)量過(guò)大時(shí)，查詢所需的時(shí)間會(huì)顯著增加，導(dǎo)致數(shù)據(jù)庫(kù)性能下降。
通過(guò)限制返回的數(shù)據(jù)量，可以縮短查詢時(shí)間，提高數(shù)據(jù)庫(kù)響應(yīng)速度。
網(wǎng)絡(luò)傳輸：大量數(shù)據(jù)的傳輸會(huì)占用網(wǎng)絡(luò)帶寬，可能導(dǎo)致網(wǎng)絡(luò)擁堵和延遲。
減少返回的數(shù)據(jù)量可以降低網(wǎng)絡(luò)傳輸?shù)呢?fù)擔(dān)，提高數(shù)據(jù)傳輸效率。。

在 SQL 優(yōu)化方面，除了已經(jīng)列舉的26個(gè)技巧，這里再補(bǔ)充9個(gè)技巧，使優(yōu)化點(diǎn)達(dá)到35條。這些補(bǔ)充技巧包含一些更加細(xì)化的實(shí)踐，幫助進(jìn)一步提升 SQL 查詢的效率：

28. 合理利用視圖（View）進(jìn)行復(fù)雜查詢

如果一個(gè)復(fù)雜查詢需要頻繁使用，可以考慮創(chuàng)建視圖，以簡(jiǎn)化查詢結(jié)構(gòu)并提高查詢效率。
正例：

CREATE VIEW view_user_orders AS
SELECT u.id, u.name, o.order_id, o.amount
FROM user u JOIN orders o ON u.id = o.user_id;
-- 使用視圖查詢
SELECT * FROM view_user_orders WHERE amount > 100;

29. 使用表分區(qū)（Partitioning）優(yōu)化大表性能

對(duì)于數(shù)據(jù)量較大的表，通過(guò)分區(qū)可以有效提升查詢效率。表分區(qū)可以按日期、數(shù)值范圍等方式進(jìn)行分割。
正例：

CREATE TABLE sales (
 sale_id INT,
 sale_date DATE,
 amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
 PARTITION p2019 VALUES LESS THAN (2020),
 PARTITION p2020 VALUES LESS THAN (2021),
 PARTITION p2021 VALUES LESS THAN (2022)
);

30. 對(duì)頻繁變更的數(shù)據(jù)使用緩存

對(duì)于頻繁查詢的靜態(tài)或相對(duì)穩(wěn)定的數(shù)據(jù)，可考慮將查詢結(jié)果存放到緩存（如 Redis）中，以減輕數(shù)據(jù)庫(kù)的負(fù)擔(dān)。
如菜單、按鈕、路由等數(shù)據(jù)

31. 使用適當(dāng)?shù)母綦x級(jí)別

在高并發(fā)環(huán)境中選擇適當(dāng)?shù)氖聞?wù)隔離級(jí)別（如 READ COMMITTED），可以避免不必要的鎖競(jìng)爭(zhēng)和阻塞，提升并發(fā)效率。

32. 使用合適的數(shù)據(jù)類型

選擇合適的數(shù)據(jù)類型會(huì)節(jié)省存儲(chǔ)空間，提升處理速度。例如：TINYINT（1字節(jié)）代替INT（4字節(jié)），VARCHAR(50)代替CHAR(50)，存儲(chǔ)長(zhǎng)度盡可能精確匹配業(yè)務(wù)需求。

33. 避免頻繁更新索引列

在高并發(fā)寫操作的場(chǎng)景中，頻繁更新索引字段會(huì)導(dǎo)致索引重建，影響性能。如果字段變動(dòng)頻繁且無(wú)查詢需求，建議避免對(duì)該字段建立索引。

34. 避免在事務(wù)中執(zhí)行非必要的操作

在事務(wù)中應(yīng)避免執(zhí)行耗時(shí)操作，比如網(wǎng)絡(luò)請(qǐng)求或復(fù)雜計(jì)算，以減少鎖的持有時(shí)間。優(yōu)先確保事務(wù)操作集中在必要的數(shù)據(jù)變更上。

35. 使用批量更新或刪除

對(duì)于批量更新或刪除數(shù)據(jù)，避免一次性操作大量記錄?？梢苑峙螆?zhí)行，以減少鎖定時(shí)間，減輕系統(tǒng)壓力。
正例：

-- 分批刪除
DELETE FROM orders WHERE status = 'obsolete' LIMIT 1000;

36、區(qū)分度不大的數(shù)據(jù)，不要添加索引
比如性別字段，只有有兩種值，查詢時(shí)，這種索引很難縮小查找范圍，可能還不如直接全表掃描快。而且在插入或更新數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)要頻繁維護(hù)這個(gè)沒(méi)什么區(qū)分度的索引，會(huì)有額外的性能損耗

37、盡可能保持邏輯刪除
物理刪除對(duì)索引的影響
當(dāng)進(jìn)行物理刪除操作時(shí)，數(shù)據(jù)庫(kù)需要對(duì)索引進(jìn)行大量的維護(hù)工作。例如，在一個(gè) B - Tree 索引結(jié)構(gòu)中，如果一條記錄被物理刪除，索引中的相應(yīng)節(jié)點(diǎn)可能需要重新調(diào)整。對(duì)于頻繁的物理刪除操作，索引會(huì)頻繁地進(jìn)行節(jié)點(diǎn)分裂和合并操作，這會(huì)消耗大量的系統(tǒng)資源，包括 CPU 時(shí)間和磁盤 I/O。
假設(shè)一個(gè)包含大量訂單記錄的表，每次物理刪除一個(gè)訂單，索引中與該訂單相關(guān)的鍵值對(duì)應(yīng)的節(jié)點(diǎn)都要進(jìn)行調(diào)整。如果刪除操作很頻繁，這種索引的頻繁調(diào)整會(huì)導(dǎo)致查詢性能下降，因?yàn)樵谡{(diào)整索引節(jié)點(diǎn)的過(guò)程中，其他查詢操作可能需要等待索引維護(hù)完成才能繼續(xù)進(jìn)行。

38、逆范式設(shè)計(jì)表
減少表的連接: 在范式化的數(shù)據(jù)庫(kù)設(shè)計(jì)中，數(shù)據(jù)通常被分散到多個(gè)表中，這樣在查詢時(shí)需要進(jìn)行多次表連接（JOIN）。當(dāng)數(shù)據(jù)量較大或者連接操作頻繁時(shí)，JOIN操作會(huì)變得非常耗時(shí)。反范式化通過(guò)將相關(guān)的數(shù)據(jù)存儲(chǔ)在同一張表中，減少了連接操作，從而提升了查詢性能。

優(yōu)化讀取操作: 在許多應(yīng)用場(chǎng)景中，讀取操作遠(yuǎn)多于寫入操作。為了優(yōu)化讀取性能，可以在表中引入冗余字段，預(yù)先計(jì)算并存儲(chǔ)一些常用的查詢結(jié)果，這樣可以在查詢時(shí)直接獲取結(jié)果，而不需要進(jìn)行復(fù)雜的計(jì)算。

39、count(*) count(字段) count(1)
count(1) 和 count(*): 統(tǒng)計(jì)所有行的數(shù)量，無(wú)論行內(nèi)數(shù)據(jù)是否為 NULL。
count(字段): 只統(tǒng)計(jì)特定字段中非 NULL 的行數(shù)量
SQL92推薦count(*)方式，因?yàn)樗籗QL優(yōu)化器優(yōu)化得最好，適用于計(jì)算總行

40、對(duì)于update和delete 添加limit
在update和delele中使用limit一是保證錯(cuò)誤的代碼導(dǎo)致誤刪數(shù)據(jù)的影響，二是提高執(zhí)行效率