1. 避免使用 SELECT *，使用具體欄位
反例：
SELECT * FROM employee;
正例：
SELECT id, name, age FROM employee;
使用具體欄位可以節省資源、減少網路開銷，且能避免回表查詢。
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 會導致索引失效並引發全表掃描。
3. 使用 LIMIT 避免不必要的資料返回
反例：
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 提升查詢效率，避免多餘的資料返回。
4. 使用數值型別代替字串
例子：性別欄位建議用數值（如0代表女生，1代表男生）而非字串（如"WOMEN"、"MAN"）。
原因：數值型別佔用儲存空間小、比較速度更快。
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>
原因：批次插入效能更優。
6. 使用 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 則省去這一步。
7. 儘可能使用 NOT NULL 定義欄位
原因：NOT NULL
- 可以防止出現空指標問題。
- NULL值儲存也需要額外的空間的，它也會導致比較運算更為複雜，使最佳化器難以最佳化SQL。
- NULL值有可能會導致索引失效
8. 避免在索引列上使用內建函式
反例：
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);
原因：索引列上使用函式會導致索引失效。
9. 避免在 WHERE 子句中對欄位進行表示式操作
反例：
SELECT * FROM user WHERE age - 1 = 10;
正例：
SELECT * FROM user WHERE age = 11;
- 在 GROUP BY 前進行條件過濾
反例：
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. 最佳化 LIKE 語句
反例：
SELECT userId, name FROM user WHERE userId LIKE '%123';
正例：
SELECT userId, name FROM user WHERE userId LIKE '123%';
原因：% 放在前面會導致索引失效。
12. 使用小表驅動大表
小表先執行以減少掃描量，如使用 EXISTS 或 IN 進行過濾。
假設我們有個客戶表和一個訂單表。其中訂單表有10萬記錄，客戶表只有1000行記錄。現在要查詢下單過的客戶資訊，可以這樣寫：
SELECT * FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
EXISTS 會逐行掃描 customers 表（即小表），對每一行 c.id，在 orders 表（大表）中檢查是否有 customer_id = c.id 的記錄。當然，也可以使用in實現：
SELECT * FROM customers
WHERE id IN (
SELECT customer_id FROM orders
);
in 查詢會先執行內部查詢部分 SELECT customer_id FROM orders，獲得 orders 表（大表）中的所有 customer_id，然後在 customers 表（小表）中查詢匹配的 id。因為orders表的資料量比較大，因此這裡用exists效果會相對更好一點。
13. IN 查詢的元素不宜太多
如果使用了in，即使後面的條件加了索引，還是要注意in後面的元素不要過多哈。
in元素一般建議不要超過200個，如果超過了，建議分組，每次200一組進行哈。
反例：
select user_id,name from user where user_id in (1,2,3...1000000);
如果我們對in的條件不做任何限制的話，該查詢語句一次性可能會查詢出非常多的資料，很容易導致介面超時。尤其有時候，我們是用的子查詢，in後面的子查詢，你都不知道數量有多少那種，更容易採坑.
如下這種子查詢：
select * from user where user_id in (select author_id from artilce where type = 1);
正例是，分批進行，比如每批200個：
select user_id,name from user where user_id in (1,2,3...200);
14. 最佳化 LIMIT 分頁
避免深分頁，使用“標籤記錄法”或“延遲關聯法”提升效能。
我們日常做分頁需求時，一般會用 limit 實現，但是當偏移量特別大的時候，查詢效率就變得低下，也就是出現深分頁問題。
反例：
select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;
我們可以透過減少回表次數來最佳化。一般有標籤記錄法和延遲關聯法。
標籤記錄法就是標記一下上次查詢到哪一條了，下次再來查的時候，從該條開始往下掃描。就好像看書一樣，上次看到哪裡了，你就摺疊一下或者夾個書籤，下次來看的時候，直接就翻到啦。
假設上一次記錄到100000，則SQL可以修改為：
select id,name,balance FROM account where id > 100000 limit 10;
這樣的話，後面無論翻多少頁，效能都會不錯的，因為命中了id索引。但是這種方式有侷限性：需要一種類似連續自增的欄位。延遲關聯法延遲關聯法，就是把條件轉移到主鍵索引樹，然後減少回表。
如下：
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;
最佳化思路就是，先透過idx_create_time二級索引樹查詢到滿足條件的主鍵ID，再與原表透過主鍵ID內連線，這樣後面直接走了主鍵索引了，同時也減少了回表。
15. 優先使用連線查詢而非子查詢
因為使用子查詢，可能會建立臨時表。
反例：
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);
IN 子查詢會在 orders 表中查詢所有 customer_id，並生成一個臨時結果集。
正例：
SELECT DISTINCT c.* FROM customers c JOIN orders o ON c.id = o.customer_id;
透過 JOIN 直接將 customers 和 orders 表關聯，符合條件的記錄一次性篩選完成。
MySQL 最佳化器通常可以利用索引來加速 JOIN，避免了臨時表的建立，查詢效果就更佳
16. Inner join 、left join、right join，優先使用Inner join，如果是left 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是等值連線，或許返回的行數比較少，所以效能相對會好一點。同理，使用了左連線，左邊表資料結果儘量小，條件儘量放到左邊處理，意味著返回的行數可能比較少。
17. 避免 != 或 <> 運算子
反例：
SELECT age, name FROM user WHERE age <> 18;
正例：可分為兩個查詢。
select age,name from user where age <18;
select age,name from user where age >18;
使用!=和<>很可能會讓索引失效
18. 使用聯合索引時遵循最左匹配原則
例如聯合索引 (userId, age)，查詢 userId 和 age 時優先使用 userId。
表結構：（有一個聯合索引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;
理由：當我們建立一個聯合索引的時候，如(k1,k2,k3)，相當於建立了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則。聯合索引不滿足最左原則，索引一般會失效，但是這個還跟Mysql最佳化器有關的。
19. 對 WHERE 和 ORDER BY 涉及的列建索引
反例：
SELECT * FROM user WHERE address = '深圳' ORDER BY age;
正例：
覆蓋索引能夠使得你的SQL語句不需要回表，僅僅訪問索引就能夠得到所有需要的資料，大大提高了查詢效率。
ALTER TABLE user ADD INDEX idx_address_age (address, age);
20. 使用覆蓋索引
正例：
SELECT id, name FROM user WHERE userid LIKE '123%';
21. 刪除冗餘索引
避免重複索引，節省資源。
反例：
KEY `idx_userId` (`userId`)
KEY `idx_userId_age` (`userId`,`age`)
正例:
//刪除userId索引，因為組合索引（A，B）相當於建立了（A）和（A，B）索引
KEY idx_userId_age (userId,age)
理由：重複的索引需要維護，並且最佳化器在最佳化查詢的時候也需要逐個地進行考慮，這會影響效能的。
22. 避免超過3個以上的表連線
不要有超過3個以上的表連線連表越多，編譯的時間和開銷也就越大。
把連線表拆開成較小的幾個執行，可讀性更高。
如果一定需要連線很多表才能得到資料，那麼意味著糟糕的設計了。
23. 索引數不宜超過5個
索引不宜太多，一般5個以內。
索引並不是越多越好，索引雖然提高了查詢的效率，但是也降低了插入和更新的效率。
insert或update時有可能會重建索引，所以建索引需要慎重考慮，視具體情況來定。
一個表的索引數最好不要超過5個，若太多需要考慮一些索引是否沒有存在的必要。
24. 索引不適合建立在大量重複資料的欄位上
如性別欄位，重複資料多時最佳化器可能放棄索引。
25. 字串型別欄位在 WHERE 中使用引號
反例：
SELECT * FROM user WHERE userid = 123;
正例：
SELECT * FROM user WHERE userid = '123';
26. 避免返回過多資料量
反例：
SELECT * FROM LivingInfo WHERE watchId = userId AND watchTime >= DATE_SUB(NOW(), INTERVAL 1 YEAR);
正例：
-- 分頁查詢
SELECT * FROM LivingInfo WHERE watchId = userId AND watchTime >= DATE_SUB(NOW(), INTERVAL 1 YEAR) LIMIT offset, pageSize;
理由：
查詢效率：當返回的資料量過大時，查詢所需的時間會顯著增加，導致資料庫效能下降。
透過限制返回的資料量，可以縮短查詢時間，提高資料庫響應速度。
網路傳輸：大量資料的傳輸會佔用網路頻寬，可能導致網路擁堵和延遲。
減少返回的資料量可以降低網路傳輸的負擔，提高資料傳輸效率。。
在 SQL 最佳化方面，除了已經列舉的26個技巧，這裡再補充9個技巧，使最佳化點達到35條。這些補充技巧包含一些更加細化的實踐，幫助進一步提升 SQL 查詢的效率：
27. 合理利用檢視（View）進行復雜查詢
如果一個複雜查詢需要頻繁使用，可以考慮建立檢視，以簡化查詢結構並提高查詢效率。
正例：
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;
28. 使用表分割槽（Partitioning）最佳化大表效能
對於資料量較大的表，透過分割槽可以有效提升查詢效率。表分割槽可以按日期、數值範圍等方式進行分割。
正例：
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)
);
29. 合理使用儲存過程（Stored Procedure）來減少多次 SQL 互動
將多步操作寫入儲存過程，可以減少客戶端與資料庫之間的多次互動，提高效率。
正例：
CREATE PROCEDURE update_and_select(IN user_id INT)
BEGIN
UPDATE users SET last_login = NOW() WHERE id = user_id;
SELECT * FROM users WHERE id = user_id;
END;
30. 對頻繁變更的資料使用快取
對於頻繁查詢的靜態或相對穩定的資料，可考慮將查詢結果存放到快取（如 Redis）中，以減輕資料庫的負擔。
31. 使用適當的隔離級別
在高併發環境中選擇適當的事務隔離級別（如 READ COMMITTED），可以避免不必要的鎖競爭和阻塞，提升併發效率。
32. 使用合適的資料型別
選擇合適的資料型別會節省儲存空間，提升處理速度。例如：TINYINT（1位元組）代替INT（4位元組），VARCHAR(50)代替CHAR(50)，儲存長度儘可能精確匹配業務需求。
33. 避免頻繁更新索引列
在高併發寫操作的場景中，頻繁更新索引欄位會導致索引重建，影響效能。如果欄位變動頻繁且無查詢需求，建議避免對該欄位建立索引。
34. 避免在事務中執行非必要的操作
在事務中應避免執行耗時操作，比如網路請求或複雜計算，以減少鎖的持有時間。優先確保事務操作集中在必要的資料變更上。
35. 使用批次更新或刪除
對於批次更新或刪除資料，避免一次性操作大量記錄。可以分批次執行，以減少鎖定時間，減輕系統壓力。
正例：
-- 分批刪除
DELETE FROM orders WHERE status = 'obsolete' LIMIT 1000;