MySQL優化之如何寫出高質量sql語句

Posted on 2021-05-16 by WalkonNet

前言

關於數據庫優化，網上有不少資料和方法，但是不少質量參差不齊，有些總結的不夠到位，內容冗雜。這篇文章就來給大傢詳細介紹瞭26條優化建議，下面來一起看看吧

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

反例：

select * from student;

正例：

select id，name, age from student;

理由：

隻取需要的字段，可以節省資源、減少CPU和IO以及網絡開銷。
select * 進行查詢時，無法使用到覆蓋索引，就會造成回表查詢。
使用具體字段可以減少表結構變動帶來的影響。

2. 使用預編譯語句進行數據庫操作

理由：

預編譯語句可以重復使用計劃，減少SQL編譯所需要的時間
可以解決動態SQL所帶來的SQL註入的問題
隻傳參數，比傳遞SQL語句更高效
相同語句可以一次解析，多次使用，提高處理效率

3. 禁止使用不含字段列表的 insert 語句

反例：

insert into values ('a', 'b', 'c');

正例：

insert into t(a, b, c) values ('a','b','c');

理由：

不含字段名的 insert 語句，很難區分到底對應的是什麼字段，而且隻能全值插入，可讀性差。
一旦表結構發生改變，很難修改。

4. 盡量避免在 where 子句中使用 or 來連接條件

案例：新建一個user表，它有一個普通索引userId，表結構如下：

CREATE TABLE `user` (  
`id` int(11) NOT NULL AUTO_INCREMENT,  
`user_id` int(11) NOT NULL,  
`age` int(11) NOT NULL,  
`name` varchar(30) NOT NULL,  
PRIMARY KEY (`id`),  
KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查詢userid為1 或者年齡為 18 歲的用戶

反例:

select id, user_id, age, name from user where userid=1 or age =18

正例：

# 使用union all 
select id, user_id, age, name from user where userid=1 union all select * from user where age = 18
# 或者分開兩條sql寫
select id, user_id, age, name from user where userid=1; select * from user where age = 18

理由：

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

5. 使用 where 條件查詢，要限定要查詢的數據，避免返回多餘的行，同時避免數據類型的隱式轉換

假設 id 為 int 類型，查詢 id = 1 的數據

反例：

select id, name from student where id = '1';

正例：

select id, name from student where id = 1;

理由：

需要什麼數據，就去查什麼數據，避免返回不必要的數據，節省開銷。
隱式轉換會導致索引失效

6. 靜止在 where 子句中對字段進行表達式操作或函數轉換，這將導致系統放棄使用索引而進行全表掃描

假設 user 表的 age 字段，加瞭索引，對其進行數據查詢

反例：

select name, age from user where age - 1 = 20;

正例：

select name, age from user where age = 21;

理由：

age 加瞭索引，但是因為對它進行運算查詢，導致索引不生效，大大的降低效率。

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

（Mysql中適用）

反例：

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;

理由：

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

8. 對查詢優化，應考慮在where及order by涉及的列上建立索引，盡量避免全表掃描。

反例：

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

正例：添加索引再查詢

alter table user add index idx_address_age (address,age)

9. where子句中考慮使用默認值代替 null

反例：(這種會全查所有數據)

select user_id, name, age from user where age is not null;

正例：

# 表字段age設置0為默認值代替null
select user_id, name, age from user where age > 0;
1
2

理由：

並不是說使用瞭 is null 或者 is not null 就會不走索引瞭，這個跟mysql版本以及查詢成本都有關。
如果mysql優化器發現，走索引比不走索引成本還要高，肯定會放棄索引，這些條件 !=，> isnull，is not null 經常讓索引失效，其實是因為一般情況下，查詢的成本高，優化器自動放棄索引的。
如果把 null 值，換成默認值，很多時候讓走索引成為可能，同時，表達意思會相對清晰一點。

10. 如果查詢結果隻有一條或者隻需要一條記錄（可能最大/小值），建議使用 limit 1

假設現在有student學生表，要找出一個名字叫 Tom 的人.

CREATE TABLE `student` (  
`id` int(11) NOT NULL,  
`name` varchar(50) DEFAULT NULL,  
`age` int(11) DEFAULT NULL,  
`date` datetime DEFAULT NULL,  
`sex` int(1) DEFAULT NULL,  
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

反例：

select id，name from student where name='Tom '

正例

select id，name from employee where name='Tom ' limit 1;

理由：

加上 limit 1 分頁後，隻要找到瞭對應的一條記錄，就不會繼續向下掃描瞭，效率將會大大提高。
如果name是唯一索引的話，是不必要加上 limit 1 瞭，因為limit的存在主要就是為瞭防止全表掃描，從而提高性能，如果一個語句本身可以預知不用全表掃描，有沒有limit ，性能的差別並不大。

11. 優化 limit 分頁語句

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

反例：

select id，name，age from student limit 10000,10

正例：

# 方案一 ：返回上次查詢的最大記錄(偏移量)
select id，name from student where id > 10000 limit 10;

# 方案二：order by + 索引
select id，name from student order by id  limit 10000,10;

# 方案三：在業務允許的情況下限制頁數：

理由：

當偏移量大的時候，查詢效率就會越低，因為Mysql並非是跳過偏移量直接去取後面的數據，而是先把偏移量 + 要取的條數，然後再把前面偏移量這一段的數據拋棄掉再返回的。
如果使用優化方案一，返回上次最大查詢記錄（偏移量），這樣可以跳過偏移量，效率提升不少。
方案二使用 order by+索引，也是可以提高查詢效率的。
方案三的話，建議跟業務討論，有沒有必要查這麼後的分頁。因為絕大多數用戶都不會往後翻太多頁。

12. 盡量避免向客戶端返回過多數據量，使用limit分頁

假設業務需求是，用戶請求查看自己最近一年觀看過的電影數據。

反例：

# 一次性查詢所有數據回來
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

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

13. 優化 like 語句

當用到模糊關鍵字查詢使用 like 時，like很可能讓索引失效。

反例：

SELECT * FROM student
WHERE name LIKE '%strive_day';
-- 或者使用 % 包裹
SELECT * FROM student
WHERE name LIKE '%strive_day%';

正例：

SELECT * FROM student
WHERE name LIKE 'strive_day%';

理由：

把 % 放前面，不會走索引查詢。
把 % 放關鍵字後面，會走索引進行查詢。
將 % 包裹關鍵字，也不會走索引查詢。
無前置 %，隻有後置 % 才會走索引查詢

14. 盡量避免在索引列上使用mysql的內置函數

案例：查詢最近七天內登陸過的用戶(假設 loginTime 字段加瞭索引)

反例：

SELECT * FROM system_user user
WHERE Date_ADD(user.loginTime,Interval 7 DAY) >= now();

正例：

SELECT * FROM system_user user
WHERE user.loginTime >=Date_ADD(NOW(),INTERVAL - 7 DAY);

理由：

索引列上使用mysql的內置函數，索引會失效
如果索引列不加內置函數，會走索引查詢

15. 使用聯合索引時，註意索引列的順序，一般遵循最左匹配原則

假設有一個聯合索引 (user_id, age)，user_id 在前，age 在後。

反例：

select user_id, name, age from user where age = 10;

正例：

# 符合最左匹配原則
select user_id, name, age from user where userid = 1 and age = 21;
# 符合最左匹配原則
select user_id, name, age from user where userid = 1;

理由：

當我們創建一個聯合索引的時候，如(k1,k2,k3)，相當於創建瞭（k1）、（k1,k2）和（k1,k2,k3）三個索引，這就是最左匹配原則。
聯合索引不滿足最左原則，索引一般會失效，但是這個還跟Mysql優化器有關的。

16. 在適當時候，使用覆蓋索引。

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

反例：

# like模糊查詢，不走索引
select user_id, name, age from user where user_id like '%123%'

# id為主鍵，那麼為普通索引，即覆蓋索引。
select user_id, name, age from user where userid like '%123%';

17. 刪除冗餘和重復索引

反例：

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

正例:

  KEY `idx_userId_age` (`userId`,`age`)
#  刪除 userId 的索引（KEY `idx_userId_age` (`userId`,`age`)）
#  因為組合索引（A，B）相當於創建瞭（A）和（A，B）索引。

理由：

重復的索引需要維護，並且優化器在優化查詢的時候也需要逐個地進行考慮，這會影響性能

18. Inner join 、left join、right join，優先使用Inner join，如果是left join，左邊表結果盡量小

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

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

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

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

反例:

select name, age from tab1 t1 left join tab2 t2  on t1.age = t2.age where t1.id = 2;

正例：

select name, age from (select * from tab1 where id = 2) t1 left join tab2 t2 on t1.age = t2.age;

理由：

如果 inner join 是等值連接，或許返回的行數比較少，所以性能相對會好一點
使用瞭左連接，左邊表數據結果盡量小，條件盡量放到左邊處理，意味著返回的行數可能比較少

19. 如果插入數據過多，考慮批量插入

反例：

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>

理由：

批量插入性能好，減少時間損耗。

20. 盡量少用 distinct 關鍵字

distinct 關鍵字一般用來過濾重復記錄，以返回不重復的記錄。在查詢一個字段或者很少字段的情況下使用時，給查詢帶來優化效果。但是在字段很多的時候使用，卻會大大降低查詢效率。

反例：

# 去重多個字段
SELECT DISTINCT * from  user;

正例：

select DISTINCT name from user;

理由：

帶 distinct 的語句 cpu 時間和占用時間都高於不帶distinct的語句。
因為當查詢很多字段時，如果使用distinct，數據庫引擎就會對數據進行比較，過濾掉重復數據，然而這個比較、過濾的過程會占用系統資源，cpu時間。

21. 不要有超過5個以上的表連接

理由：

連表越多，編譯的時間和開銷也就越大
連表可讀性差，把連接表拆開成較小的幾個執行，可讀性更高

22. 數據量大的時候，如何優化更新語句。

數據量大的時候，需要避免同時修改或刪除過多數據，同時會造成cpu利用率過高，從而影響別人對數據庫的訪問。

反例：

# 一次刪除10萬或者100萬+條數據
delete from user where id < 1000000;
# 或者采用單一循環操作，效率低，時間漫長
for（User user：list）{delete from user;}

正例：

# 分批進行刪除,如每次500   
delete user where id < 500
delete user where id >= 500 and id < 1000；
...
delete user where id >= 999500 and id < 1000000;

理由：

一次性刪除/更新太多數據，可能會有 lock wait timeout exceed 的錯誤，所以建議分批操作。

23. 合理使用 exist 和 in

假設表A表示某企業的員工表，表B表示部門表，查詢所有部門的所有員工SQL

反例：:

select * from A where deptId in (select deptId from B);

這樣寫等價於：

先查詢部門表B
select deptId from B
再由部門deptId，查詢A的員工
select * from A where A.deptId = B.deptId

可以抽象成這樣的一個循環語句：

List<> resultSet ;    
for(int i = 0; i < B.length; i ++) {
 for(int j = 0; j < A.length; j ++) {
     if(A[i].id == B[j].id) {
         resultSet.add(A[i]);
            break;          
        }       
     }    
 }

我們也可以用exists實現一樣的查詢功能

select * from A where exists (select 1 from B where A.deptId = B.deptId);

上述代碼等價於：

select * from A，先從A表做循環
select * from B where A.deptId = B.deptId，再從B表做循環.

因為exists查詢的理解就是，先執行主查詢，獲得數據後，再放到子查詢中做條件驗證，根據驗證結果（true或者false），來決定主查詢的數據結果是否得以保留。

同理，可以抽象成這樣一個循環：

List<> resultSet;    
for(int i = 0; i < A.length; i ++) {
 for(int j = 0; j < B.length; j ++) {
     if(A[i].deptId == B[j].deptId) {
         resultSet.add(A[i]);
            break;          
            }       
        }    
    }

理由：

數據庫最費勁的就是跟程序鏈接釋放。假設鏈接瞭兩次，每次做上百萬次的數據集查詢，查完就走，這樣就隻做瞭兩次；相反如果每次單獨查詢，建立瞭上百萬次鏈接，申請鏈接釋放反復重復
mysql優化原則，就是小表驅動大表，小的數據集驅動大的數據集，從而讓性能更優
我們要選擇最外層循環小的，也就是，如果B的數據量小於A，適合使用 in，如果B的數據量大於A，即適合選擇exist

24. 盡量使用數字型字段，若隻含數值信息的字段盡量不要設計為字符型

反例：

`king_id` varchar（20） NOT NULL COMMENT '123'

正例：

 `king_id` int(11) NOT NULL COMMENT '123'

理由：

相對於數字型字段，字符型會降低查詢和連接的性能，並會增加存儲開銷。

25. 盡量用 union all 替換 union

如果檢索結果中不會有重復的記錄，推薦 union all 替換 union

反例：

select * from user where userid = 1
union
select * from user where age = 20

正例：

select * from user where userid = 1
union all
select * from user where age = 20

理由：

如果使用union，不管檢索結果有沒有重復，都會嘗試進行合並，然後在輸出最終結果前進行排序。
如果已知檢索結果沒有重復記錄，使用 union all 代替 union，這樣會提高效率。

26. 如果字段類型是字符串，where時一定用引號括起來，否則將導致索引失效

反例：

select * from user where userid = 1;

正例：

select * from user where userid ='1';

理由：

第一條語句未加單引號就不走索引，這是因為不加單引號時，是字符串跟數字的比較，它們類型不匹配，MySQL會做隱式的類型轉換，把它們轉換為浮點數再做比較。

總結

到此這篇關於MySQL優化之如何寫出高質量sql語句的文章就介紹到這瞭,更多相關MySQL優化sql語句內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

MySQL優化之如何寫出高質量sql語句

前言

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

2. 使用預編譯語句進行數據庫操作

4. 盡量避免在 where 子句中使用 or 來連接條件

5. 使用 where 條件查詢，要限定要查詢的數據，避免返回多餘的行，同時避免數據類型的隱式轉換

6. 靜止在 where 子句中對字段進行表達式操作或函數轉換，這將導致系統放棄使用索引而進行全表掃描

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

8. 對查詢優化，應考慮在where及order by涉及的列上建立索引，盡量避免全表掃描。

9. where子句中考慮使用默認值代替 null

10. 如果查詢結果隻有一條或者隻需要一條記錄（可能最大/小值），建議使用 limit 1

11. 優化 limit 分頁語句

12. 盡量避免向客戶端返回過多數據量，使用limit分頁

13. 優化 like 語句

14. 盡量避免在索引列上使用mysql的內置函數

15. 使用聯合索引時，註意索引列的順序，一般遵循最左匹配原則

16. 在適當時候，使用覆蓋索引。

17. 刪除冗餘和重復索引

18. Inner join 、left join、right join，優先使用Inner join，如果是left join，左邊表結果盡量小

19. 如果插入數據過多，考慮批量插入

20. 盡量少用 distinct 關鍵字

21. 不要有超過5個以上的表連接

22. 數據量大的時候，如何優化更新語句。

23. 合理使用 exist 和 in

24. 盡量使用數字型字段，若隻含數值信息的字段盡量不要設計為字符型

25. 盡量用 union all 替換 union

26. 如果字段類型是字符串，where時一定用引號括起來，否則將導致索引失效

總結

推薦閱讀：

發佈留言取消回覆

近期文章

前言

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

2. 使用預編譯語句進行數據庫操作

4. 盡量避免在 where 子句中使用 or 來連接條件

5. 使用 where 條件查詢，要限定要查詢的數據，避免返回多餘的行，同時避免數據類型的隱式轉換

6. 靜止在 where 子句中對字段進行表達式操作或函數轉換，這將導致系統放棄使用索引而進行全表掃描

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

8. 對查詢優化，應考慮在where及order by涉及的列上建立索引，盡量避免全表掃描。

9. where子句中考慮使用默認值代替 null

10. 如果查詢結果隻有一條或者隻需要一條記錄（可能最大/小值），建議使用 limit 1

11. 優化 limit 分頁語句

12. 盡量避免向客戶端返回過多數據量，使用limit分頁

13. 優化 like 語句

14. 盡量避免在索引列上使用mysql的內置函數

15. 使用聯合索引時，註意索引列的順序，一般遵循 最左匹配原則

16. 在適當時候，使用覆蓋索引。

17. 刪除冗餘和重復索引

18. Inner join 、left join、right join，優先使用Inner join，如果是left join，左邊表結果盡量小

19. 如果插入數據過多，考慮 批量插入

20. 盡量少用 distinct 關鍵字

21. 不要有超過5個以上的表連接

22. 數據量大的時候，如何優化更新語句。

23. 合理使用 exist 和 in

24. 盡量使用數字型字段，若隻含數值信息的字段盡量不要設計為字符型

25. 盡量用 union all 替換 union

26. 如果字段類型是字符串，where時一定用引號括起來，否則將導致索引失效

總結

推薦閱讀：

發佈留言 取消回覆

近期文章

標籤

15. 使用聯合索引時，註意索引列的順序，一般遵循最左匹配原則

19. 如果插入數據過多，考慮批量插入

發佈留言取消回覆