提升SQL效能
===
[TOC]
避免使用Select *
---
由於每次都以一個頁面為IO對象，故在MySQL中無論是否設定select的列，實際上拉出的資料量都是一樣多的，但假如select的對象有覆蓋到index，則可以透過index查找對應內容並吐回，進而減少IO操作
避免隨機Select資料，例如使用Rand()
---
避免使用雙重Select、子查詢
---
這是一個雙重查詢的例子：
```SQL=
SELECT * FROM table1 WHERE id (SELECT id FROM table2 WHERE address = 'A');
```
一般執行時會先查table1全部，才查table2去篩選所以效能會很差
除了SELECT，在UPDATE、DELETE等也會碰到
特別是Mysql5.5前尤其容易撞到，故請極力避免這種寫法
在MySQL5.6以後或MariaDB10，有設計改善此寫法並自動轉換為JOIN：
```SQL=
SELECT t1.* FROM table1 JOIN table2 on table1.id = table2.id
```
但自動轉換的貼心設計僅限於SELECT，所以能不要這樣寫就不要這樣寫
聯集查詢時，若已確定兩Select資料不重複，則用Union All取代Union
---
union的用途是將兩個SELECT結果一起秀出並刪掉多出的重複項目：
```SQL=
select test_pk, test_val from test1
union
select test_pk, test_val from test2
```
而union all則是忠實呈現兩個SELECT結果，即使重複也如實show出：
```SQL=
select test_pk, test_val from test1
union all
select test_pk, test_val from test2
```
由於找出並刪除重複項目牽涉到排序，故會耗損CPU資源產生延遲
所以在show出不重複聯集的情境時，若已100%確定不會有重複結果，那可以棄用union而改用不對資料多做整理的union all來節省資源
JOIN > between > IN > OR
---
高效率查詢(IN)：
```SQL=
SELECT * FROM t WHERE id IN (10,20,30);
```
低效率查詢(OR)：
```SQL=
SELECT * FROM t WHERE id = 10 OR id = 20 OR id = 30;
```
**IN的使用注意**：
IN查出錯誤結果時可能整組噴錯誤，盡量在確定、明確，且有限的集合內才用IN，例如IN(10, 20, 30)
外表小用exist，內表小用IN
---
外/前表小用exist：
```SQL=
select * from 表A where exists(select * from 表B where 表B.id=表A.id)
```
內/後表小用IN：
```SQL=
select * from 表A where id in (select id from 表B)
```
使用LIMIT時已">"跳過前面的offset不讀，而非僅用LIMIT m,n
---
高效率查詢(跳過前200行，只返回201開始的20行)：
```SQL=
select id,name from table
where id> 200 limit 20
```
低效率查詢(讀了前200行)：
```SQL=
select id,name from table
limit 200, 20
```
利用">"能做出與原表的JOIN關聯效果，來跳過那些不想讀的行數，進而只讀取offset後面所需的20行。這個問題特別容易在MySQL撞到，須盡量避免
禁用Order！Group時停掉預設的Order By
---
沒事不要用Order By
在使用Group時order by NULL來停掉預設的排序功能：
```SQL=
SELECT id,count(*) FROM t GROUP BY id ORDER BY NULL
```
特別在MySQL中會預設幫Group排序，故請用Group by NULL停掉
假如真的需要排：
1. 盡量靠index來排
確認方法：explain結果內沒有"Using Temporary"或"Using Filesort"
2. 盡量用ram臨時表格而非HDD臨時表格
透過些微調大tmp_table_size參數來達成
3. 對於極大資料量，用SQL_BIG_RESULT來使用HDD臨時表格排序
4. 優先使用where，避免使用having
高效排序：
```SQL=
SELECT JOB, AVG(SAL) FROM EMP
WHERE JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’
GROUP by JOB
```
低效排序：
```SQL=
SELECT JOB, AVG(SAL) FROM EMP
GROUP by JOB
HAVING JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’
```
將多次INSERT改成一個INSERT中多組插入
---
高效率插入：
```SQL=
INSERT INTO t(id, name)
VALUES(1, 'aaa'),(2, 'bbb'),(3, 'ccc');
```
低效率插入：
```SQL=
INSERT INTO t(id, name) VALUES(1, 'aaa');
INSERT INTO t(id, name) VALUES(2, 'bbb');
INSERT INTO t(id, name) VALUES(3, 'ccc');
```
盡量使用數字列名取代字串列名(假如情況允許)
---
由於查詢及JOIN時會逐字比較字串中每個字元，而數字列名只比較一次會相對省效能
---
**參考資料:** [[CSDN]实践中如何优化MySQL（精）](https://blog.csdn.net/qq_35642036/article/details/82820129)
###### tags: `SQL`
Sign in with Wallet
Connect another wallet