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

反例子：

select * from employee;

正例子：

select id，name, age from employee;

• select具體字段，節(jié)省資源、減少網(wǎng)絡(luò)開銷。
• select * 進行查詢時，很可能就不會使用到覆蓋索引了，就會造成回表查詢。

2、應(yīng)盡量避免在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

//或者分開兩條sql寫：
select * from user where userid=1
select * from user where age = 18

• 使用or可能會使索引失效，從而全表掃描。一位朋友踩過這個坑，差點把數(shù)據(jù)庫CPU打滿了。

如果userId加了索引，age沒加索引，以上or的查詢SQL，假設(shè)它走了userId的索引，但是走到age查詢條件時，它還得全表掃描，也就是需要三步過程：全表掃描+索引掃描+合并。如果它一開始就走全表掃描，直接一遍掃描就完事。mysql是有優(yōu)化器的，處于效率與成本考慮，遇到or條件，索引可能失效，看起來也合情合理。

3. 盡量使用limit，避免不必要的返回

假設(shè)一個用戶有多個訂單，要查詢最新訂單的下單時間的話，你是這樣查詢：

select id, order_date from order_tab 
where user_id=666 
order by create_date desc;

獲取到訂單列表后，取第一個的下單時間：

List<Order> orderList = orderService.queryOrderListByUserId('666');
Date orderDate = orderList.get(0).getOrderDate();

還是用limit ，只獲取最新那個訂單返回：

select id, order_date from order_tab 
where user_id=666 
order by create_date desc limit 1;

顯然，使用limit的更好~ 因為整體性能更好。

4. 盡量使用數(shù)值類型而不是字符串

比如我們定義性別字段的時候，更推薦0代表女生，1表示男生，而不是定義為WOMEN 或者MAN的字符串。

因為：

• 數(shù)值類型（如 INT, FLOAT, DECIMAL 等）通常占用的存儲空間比字符串類型（如 VARCHAR, CHAR）小
• 數(shù)值類型的比較和計算速度通常比字符串快

5. 批量操作(更新、刪除、查詢)

反例：

for(User u :list){
 INSERT into user(name,age) values(#name#,#age#)   
}

正例：

//一次500批量插入，分批進行
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
    (#{item.name},#{item.age})
</foreach>

理由：

• 批量插入性能好，更加省時間

打個比喻: 假如你需要搬一萬塊磚到樓頂,你有一個電梯,電梯一次可以放適量的磚（最多放500）,你可以選擇一次運送一塊磚,也可以一次運送500,你覺得哪個時間消耗大?

6、盡量用 union all 替換 union

如果檢索結(jié)果中不會有重復(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，不管檢索結(jié)果有沒有重復(fù)，都會嘗試進行合并，然后在輸出最終結(jié)果前進行排序。如果已知檢索結(jié)果沒有重復(fù)記錄，使用union all 代替union，這樣會提高效率。

7. 盡可能使用not null定義字段

如果沒有特殊的理由， 一般都建議將字段定義為NOT NULL。

city VARCHAR(50) NOT NULL

為什么呢？

• NOT NULL 可以防止出現(xiàn)空指針問題。
• 其次，NULL值存儲也需要額外的空間的，它也會導(dǎo)致比較運算更為復(fù)雜，使優(yōu)化器難以優(yōu)化SQL。
• NULL值有可能會導(dǎo)致索引失效

8、盡量避免在索引列上使用mysql的內(nèi)置函數(shù)

業(yè)務(wù)需求：查詢最近七天內(nèi)登陸過的用戶(假設(shè)loginTime加了索引)

反例：

select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();

正例：

explain  select userId,loginTime from loginuser where  loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);

理由：

• 索引列上使用mysql的內(nèi)置函數(shù)，索引失效

9、應(yīng)盡量避免在 where 子句中對字段進行表達式操作，這將導(dǎo)致系統(tǒng)放棄使用索引而進行全表掃

反例：

select * from user where age-1 =10；

正例：

select * from user where age =11；

理由：

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

10、為了提高group by 語句的效率，可以在執(zhí)行到該語句前，把不需要的記錄過濾掉。

假設(shè)有一個 orders 表，存儲了所有用戶的訂單信息，并包含 city 字段表示用戶所在城市。我們想要計算來自北京的每個用戶的總消費金額。

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    city VARCHAR(50) NOT NULL,
    amount DECIMAL(10, 2)
);

計算北京用戶的消費總額，按用戶分組，反例SQL（不使用 WHERE 條件過濾）：

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

應(yīng)該先用 WHERE 條件過濾，正例如下:

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

11、優(yōu)化你的like語句

日常開發(fā)中，如果用到模糊關(guān)鍵字查詢，很容易想到like，但是like很可能讓你的索引失效。

反例：

select userId，name from user where userId like '%123';

正例：

select userId，name from user where userId like '123%';

理由：

• 把%放前面，并不走索引.

有些時候你就是需要包含關(guān)鍵詞，可以結(jié)合其他查詢條件（加索引的其他條件）結(jié)合起來。或者可以借助 Elasticsearch 來進行模糊查詢，這里知道like在前會導(dǎo)致索引失效這個點就好啦。

12.使用小表驅(qū)動大表的思想

小表驅(qū)動大表，這主要是為了優(yōu)化性能，讓查詢執(zhí)行得更高效。背后的核心原因是減少數(shù)據(jù)掃描量，盡量讓數(shù)據(jù)庫在處理時能先過濾掉大量無關(guān)數(shù)據(jù)，從而縮短查詢時間。

假設(shè)我們有個客戶表和一個訂單表。其中訂單表有10萬記錄，客戶表只有1000行記錄。

現(xiàn)在要查詢下單過的客戶信息，可以這樣寫：

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 的記錄。

當(dāng)然，也可以使用in實現(xiàn)：

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

in 查詢會先執(zhí)行內(nèi)部查詢部分 SELECT customer_id FROM orders，獲得 orders 表（大表）中的所有 customer_id，然后在 customers 表（小表）中查找匹配的 id。

因為orders表的數(shù)據(jù)量比較大，因此這里用exists效果會相對更好一點。

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

如果使用了in，即使后面的條件加了索引，還是要注意in后面的元素不要過多哈。in元素一般建議不要超過200個，如果超過了，建議分組，每次200一組進行哈。

反例：

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

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

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. 優(yōu)化limit分頁

我們?nèi)粘Ｗ龇猪撔枨髸r，一般會用 limit 實現(xiàn)，但是當(dāng)偏移量特別大的時候，查詢效率就變得低下，也就是出現(xiàn)深分頁問題。

反例：

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

我們可以通過減少回表次數(shù)來優(yōu)化。一般有標(biāo)簽記錄法和延遲關(guān)聯(lián)法。

標(biāo)簽記錄法

就是標(biāo)記一下上次查詢到哪一條了，下次再來查的時候，從該條開始往下掃描。就好像看書一樣，上次看到哪里了，你就折疊一下或者夾個書簽，下次來看的時候，直接就翻到啦。

假設(shè)上一次記錄到100000，則SQL可以修改為：

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

這樣的話，后面無論翻多少頁，性能都會不錯的，因為命中了id索引。但是這種方式有局限性：需要一種類似連續(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)化思路就是，先通過idx_create_time二級索引樹查詢到滿足條件的主鍵ID，再與原表通過主鍵ID內(nèi)連接，這樣后面直接走了主鍵索引了，同時也減少了回表。

15. 盡量使用連接查詢而不是子查詢

因為使用子查詢，可能會創(chuàng)建臨時表。

反例如下：

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

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

我們可以用連接查詢避免臨時表：

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

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

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

• Inner join 內(nèi)連接，在兩張表進行連接查詢時，只保留兩張表中完全匹配的結(jié)果集
• left join 在兩張表進行連接查詢時，會返回左表所有的行，即使在右表中沒有匹配的記錄。
• right join 在兩張表進行連接查詢時，會返回右表所有的行，即使在左表中沒有匹配的記錄。

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

17、應(yīng)盡量避免在 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;

理由：

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

18、使用聯(lián)合索引時，注意索引列的順序，一般遵循最左匹配原則。

表結(jié)構(gòu)：（有一個聯(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)建一個聯(lián)合索引的時候，如(k1,k2,k3)，相當(dāng)于創(chuàng)建了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則。
• 聯(lián)合索引不滿足最左原則，索引一般會失效，但是這個還跟Mysql優(yōu)化器有關(guān)的。

19、對查詢進行優(yōu)化，應(yīng)考慮在 where 及 order by 涉及的列上建立索引，盡量避免全表掃描。

反例：

select * from user where address ='深圳' order by age ;

正例：

添加索引
alter table user add index idx_address_age (address,age)

20、在適當(dāng)?shù)臅r候，使用覆蓋索引。

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

反例：

// like模糊查詢，不走索引了
select * from user where userid like '%123%'

正例：

//id為主鍵，那么為普通索引，即覆蓋索引登場了。
select id,name from user where userid like '%123%';

21、刪除冗余和重復(fù)索引

反例：

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

正例:

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

理由：

• 重復(fù)的索引需要維護，并且優(yōu)化器在優(yōu)化查詢的時候也需要逐個地進行考慮，這會影響性能的。

22、不要有超過3個以上的表連接

• 連表越多，編譯的時間和開銷也就越大。
• 把連接表拆開成較小的幾個執(zhí)行，可讀性更高。
• 如果一定需要連接很多表才能得到數(shù)據(jù)，那么意味著糟糕的設(shè)計了。

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

• 索引并不是越多越好，索引雖然提高了查詢的效率，但是也降低了插入和更新的效率。
• insert或update時有可能會重建索引，所以建索引需要慎重考慮，視具體情況來定。
• 一個表的索引數(shù)最好不要超過5個，若太多需要考慮一些索引是否沒有存在的必要。

24、索引不適合建在有大量重復(fù)數(shù)據(jù)的字段上，如性別這類型數(shù)據(jù)庫字段。

因為SQL優(yōu)化器是根據(jù)表中數(shù)據(jù)量來進行查詢優(yōu)化的，如果索引列有大量重復(fù)數(shù)據(jù)，Mysql查詢優(yōu)化器推算發(fā)現(xiàn)不走索引的成本更低，很可能就放棄索引了。

25、如何字段類型是字符串，where時一定用引號括起來，否則索引失效

反例：

select * from user where userid =123;

正例：

select * from user where userid ='123';

理由：

• 為什么第一條語句未加單引號就不走索引了呢？這是因為不加單引號時，是字符串跟數(shù)字的比較，它們類型不匹配，MySQL會做隱式的類型轉(zhuǎn)換，把它們轉(zhuǎn)換為浮點數(shù)再做比較。

26、盡量避免向客戶端返回過多數(shù)據(jù)量。

假設(shè)業(yè)務(wù)需求是，用戶請求查看自己最近一年觀看過的直播數(shù)據(jù)。

反例：

//一次性查詢所有數(shù)據(jù)回來
select * from LivingInfo where watchId =useId and watchTime >= Date_sub(now(),Interval 1 Y)

正例：

//分頁查詢
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit offset，pageSize

//如果是前端分頁，可以先查詢前兩百條記錄，因為一般用戶應(yīng)該也不會往下翻太多頁，
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit 200 ;

理由：

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