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

反例子：

select * from employee;

正例子：

select id，name, age from employee;

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

2、應(yīng)盡量避免在where子句中使用or來(lái)連接條件

反例:

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

//或者分開(kāi)兩條sql寫(xiě)：
select * from user where userid=1
select * from user where age = 18

• 使用or可能會(huì)使索引失效，從而全表掃描。一位朋友踩過(guò)這個(gè)坑，差點(diǎn)把數(shù)據(jù)庫(kù)CPU打滿(mǎn)了。

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

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

假設(shè)一個(gè)用戶(hù)有多個(gè)訂單，要查詢(xún)最新訂單的下單時(shí)間的話(huà)，你是這樣查詢(xún)：

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

獲取到訂單列表后，取第一個(gè)的下單時(shí)間：

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

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

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

顯然，使用limit的更好~ 因?yàn)檎w性能更好。

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

比如我們定義性別字段的時(shí)候，更推薦0代表女生，1表示男生，而不是定義為WOMEN 或者M(jìn)AN的字符串。

因?yàn)椋?/p>

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

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

反例：

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

正例：

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

理由：

• 批量插入性能好，更加省時(shí)間

打個(gè)比喻: 假如你需要搬一萬(wàn)塊磚到樓頂,你有一個(gè)電梯,電梯一次可以放適量的磚（最多放500）,你可以選擇一次運(yùn)送一塊磚,也可以一次運(yùn)送500,你覺(jué)得哪個(gè)時(shí)間消耗大?

6、盡量用 union all 替換 union

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

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

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

city VARCHAR(50) NOT NULL

為什么呢？

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

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

業(yè)務(wù)需求：查詢(xún)最近七天內(nèi)登陸過(guò)的用戶(hù)(假設(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 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致系統(tǒng)放棄使用索引而進(jìn)行全表掃

反例：

select * from user where age-1 =10；

正例：

select * from user where age =11；

理由：

• 雖然age加了索引，但是因?yàn)閷?duì)它進(jìn)行運(yùn)算，索引直接迷路了。。。

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

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

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

計(jì)算北京用戶(hù)的消費(fèi)總額，按用戶(hù)分組，反例SQL（不使用 WHERE 條件過(guò)濾）：

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

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

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

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

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

反例：

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

正例：

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

理由：

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

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

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

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

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

現(xiàn)在要查詢(xún)下單過(guò)的客戶(hù)信息，可以這樣寫(xiě)：

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 查詢(xún)會(huì)先執(zhí)行內(nèi)部查詢(xún)部分 SELECT customer_id FROM orders，獲得 orders 表（大表）中的所有 customer_id，然后在 customers 表（小表）中查找匹配的 id。

因?yàn)閛rders表的數(shù)據(jù)量比較大，因此這里用exists效果會(huì)相對(duì)更好一點(diǎn)。

13. in查詢(xún)的元素不宜太多

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

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);

14. 優(yōu)化limit分頁(yè)

我們?nèi)粘Ｗ龇猪?yè)需求時(shí)，一般會(huì)用 limit 實(shí)現(xiàn)，但是當(dāng)偏移量特別大的時(shí)候，查詢(xún)效率就變得低下，也就是出現(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)記一下上次查詢(xún)到哪一條了，下次再來(lái)查的時(shí)候，從該條開(kāi)始往下掃描。就好像看書(shū)一樣，上次看到哪里了，你就折疊一下或者夾個(gè)書(shū)簽，下次來(lái)看的時(shí)候，直接就翻到啦。

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

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

這樣的話(huà)，后面無(wú)論翻多少頁(yè)，性能都會(huì)不錯(cuò)的，因?yàn)槊辛薸d索引。但是這種方式有局限性：需要一種類(lèi)似連續(xù)自增的字段。

延遲關(guān)聯(lián)法

延遲關(guān)聯(lián)法，就是把條件轉(zhuǎn)移到主鍵索引樹(shù)，然后減少回表。如下：

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í)索引樹(shù)查詢(xún)到滿(mǎn)足條件的主鍵ID，再與原表通過(guò)主鍵ID內(nèi)連接，這樣后面直接走了主鍵索引了，同時(shí)也減少了回表。

15. 盡量使用連接查詢(xún)而不是子查詢(xún)

因?yàn)槭褂米硬樵?xún)，可能會(huì)創(chuàng)建臨時(shí)表。

反例如下：

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

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

我們可以用連接查詢(xún)避免臨時(shí)表：

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)建，查詢(xún)效果就更佳

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

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

都滿(mǎn)足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ù)可能比較少。

17、應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。

反例：

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

正例：

//可以考慮分開(kāi)兩條sql寫(xiě)
select age,name  from user where age <18;
select age,name  from user where age >18;

理由：

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

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

表結(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)合索引不滿(mǎn)足最左原則，索引一般會(huì)失效，但是這個(gè)還跟Mysql優(yōu)化器有關(guān)的。

19、對(duì)查詢(xún)進(jìn)行優(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(shí)候，使用覆蓋索引。

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

反例：

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

正例：

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

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

反例：

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)化查詢(xún)的時(shí)候也需要逐個(gè)地進(jìn)行考慮，這會(huì)影響性能的。

22、不要有超過(guò)3個(gè)以上的表連接

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

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

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

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

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

25、如何字段類(lèi)型是字符串，where時(shí)一定用引號(hào)括起來(lái)，否則索引失效

反例：

select * from user where userid =123;

正例：

select * from user where userid ='123';

理由：

• 為什么第一條語(yǔ)句未加單引號(hào)就不走索引了呢？這是因?yàn)椴患訂我?hào)時(shí)，是字符串跟數(shù)字的比較，它們類(lèi)型不匹配，MySQL會(huì)做隱式的類(lèi)型轉(zhuǎn)換，把它們轉(zhuǎn)換為浮點(diǎn)數(shù)再做比較。

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

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

反例：

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

正例：

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

//如果是前端分頁(yè)，可以先查詢(xún)前兩百條記錄，因?yàn)橐话阌脩?hù)應(yīng)該也不會(huì)往下翻太多頁(yè)，
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit 200 ;

理由：

• 查詢(xún)效率：當(dāng)返回的數(shù)據(jù)量過(guò)大時(shí)，查詢(xún)所需的時(shí)間會(huì)顯著增加，導(dǎo)致數(shù)據(jù)庫(kù)性能下降。通過(guò)限制返回的數(shù)據(jù)量，可以縮短查詢(xún)時(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ù)傳輸效率。