SQL優化
Sql執行順序
基礎Sql優化
查詢SQL儘量不要使用select *，而是具體欄位
避免在where子句中使用or來連接條件
使用varchar代替char
儘量使用數值替代字符串類型
查詢儘量避免返回大量數據
使用Explain分析你SQL執行計劃
是否使用了索引及其掃描類型
創建name欄位的索引
優化like語句：
字符串怪現象
索引不宜太多，一般5個以內
索引不適合建在有大量重複數據的欄位上
where限定查詢的數據
避免在索引列上使用內置函數
避免在where中對欄位進行表達式操作
避免在where子句中使用!=或<>操作符
去重distinct過濾欄位要少
where中使用默認值代替null
高級SQL優化
批量插入性能提升
批量刪除優化
偽刪除設計
提高group by語句的效率
複合索引最左特性
排序欄位創建索引
刪除冗餘和重複的索引
不要有超過5個以上的表連接
inner join 、left join、right join，優先使用inner join
in子查詢的優化
儘量使用union all替代union
Sql執行順序
(8) SELECT(9) DISTINCT column,…
選擇欄位 、去重
(6) AGG_FUNC(column or expression),…
聚合函數
(1) FROM [left_table]
選擇表
(3) <join_type> JOIN <right_table>
連結
(2) ON <join_condition>
連結條件
(4) WHERE <where_condition>
條件過濾
(5) GROUP BY <group_by_list>
分組
(7) HAVING <having_condition>
分組過濾
(10) ORDER BY <order_by_list>
排序
(11) LIMIT count OFFSET count;
分頁
基礎Sql優化
查詢SQL儘量不要使用select *，而是具體欄位
反例：
SELECT * FROM student
1
正例：
SELECT id,NAME FROM student
1
理由：
欄位多時，大表能達到100多個欄位甚至達200多個欄位
只取需要的欄位，節省資源、減少網絡開銷
select * 進行查詢時，很可能不會用到索引，就會造成全表掃描
避免在where子句中使用or來連接條件
查詢id為1或者薪水為3000的用戶：
反例：
SELECT * FROM student WHERE id=1 OR salary=30000
1
正例：
使用union all
SELECT * FROM student WHERE id=1
UNION ALL
SELECT * FROM student WHERE salary=30000
1
2
3
分開兩條sql寫
SELECT * FROM student WHERE id=1
SELECT * FROM student WHERE salary=30000
1
2
理由：
使用or可能會使索引失效，從而全表掃描
對於or沒有索引的salary這種情況，假設它走了id的索引，但是走到salary查詢條件時，它還得全表掃描。也就是說整個過程需要三步：全表掃描+索引掃描+合併。如果它一開始就走全表掃描，直接一遍掃描就搞定。雖然mysql是有優化器的，處於效率與成本考慮，遇到or條件，索引還是可能失效的
使用varchar代替char
反例：
`deptname` char(100) DEFAULT NULL COMMENT '部門名稱'
1
正例：
`deptname` varchar(100) DEFAULT NULL COMMENT '部門名稱'
1
理由：
varchar變長欄位按數據內容實際長度存儲，存儲空間小，可以節省存儲空間
char按聲明大小存儲，不足補空格
其次對於查詢來說，在一個相對較小的欄位內搜索，效率更高
儘量使用數值替代字符串類型
主鍵（id）：primary key優先使用數值類型int，tinyint
性別（sex）：0-代表女，1-代表男；資料庫沒有布爾類型，mysql推薦使用tinyint
支付方式（payment）：1-現金、2-微信、3-支付寶、4-信用卡、5-銀行卡
服務狀態（state）：1-開啟、2-暫停、3-停止
商品狀態（state）：1-上架、2-下架、3-刪除
查詢儘量避免返回大量數據
如果查詢返回數據量很大，就會造成查詢時間過長，網絡傳輸時間過長。同時，大量數據返回也可能沒有實際意義。如返回上千條甚至更多，用戶也看不過來。
通常採用分頁，一頁習慣10/20/50/100條。
使用explain分析你SQL執行計劃
SQL很靈活，一個需求可以很多實現，那哪個最優呢？SQL提供了explain關鍵字，它可以分析你的SQL執行計劃，看它是否最佳。Explain主要看SQL是否使用了索引。
EXPLAIN
SELECT * FROM student WHERE id=1
1
2
返回結果：
是否使用了索引及其掃描類型
SQL索引概念（詳解B+樹）
type：
ALL 全表掃描，沒有優化，最慢的方式
index 索引全掃描
range 索引範圍掃描，常用語<，<=，>=，between等操作
ref 使用非唯一索引掃描或唯一索引前綴掃描，返回單條記錄，常出現在關聯查詢中
eq_ref 類似ref，區別在於使用的是唯一索引，使用主鍵的關聯查詢
const 當查詢是對主鍵或者唯一鍵進行精確查詢，系統會把匹配行中的其他列作為常數處理
null MySQL不訪問任何表或索引，直接返回結果
System 表只有一條記錄(實際中基本不存在這個情況)
性能排行：
System > const > eq_ref > ref > range > index > ALL
possible_keys：
顯示可能應用在這張表中的索引
key：
真正使用的索引方式
創建name欄位的索引
提高查詢速度的最簡單最佳的方式
ALTER TABLE student ADD INDEX index_name (NAME)
1
優化like語句：
模糊查詢，程式設計師最喜歡的就是使用like，但是like很可能讓你的索引失效
反例：
EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE '%1'
EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE '%1%'
1
2
3
4
正例：
EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE '1%'
1
2
理由：
未使用索引：故意使用sex非索引欄位
EXPLAIN
SELECT id,NAME FROM student WHERE NAME=1 OR sex=1
1
2
主鍵索引生效
EXPLAIN
SELECT id,NAME FROM student WHERE id=1
1
2
索引失效，type=ALL，全表掃描
EXPLAIN
SELECT id,NAME FROM student WHERE id LIKE '%1'
1
2
字符串怪現象
反例：
#未使用索引
EXPLAIN
SELECT * FROM student WHERE NAME=123
1
2
3
正例：
#使用索引
EXPLAIN
SELECT * FROM student WHERE NAME='123'
1
2
3
理由：
為什麼第一條語句未加單引號就不走索引了呢？這是因為不加單引號時，是字符串跟數字的比較，它們類型不匹配，MySQL會做隱式的類型轉換，把它們轉換為數值類型再做比較
索引不宜太多，一般5個以內
索引並不是越多越好，雖其提高了查詢的效率，但卻會降低插入和更新的效率
索引可以理解為一個就是一張表，其可以存儲數據，其數據就要占空間
再者，索引表的一個特點，其數據是排序的，那排序要不要花時間呢？肯定要
insert或update時有可能會重建索引，如果數據量巨大，重建將進行記錄的重新排序，所以建索引需要慎重考慮，視具體情況來定
一個表的索引數最好不要超過5個，若太多需要考慮一些索引是否有存在的必要
索引不適合建在有大量重複數據的欄位上
如性別欄位。因為SQL優化器是根據表中數據量來進行查詢優化的，如果索引
列有大量重複數據，Mysql查詢優化器推算發現不走索引的成本更低，很可能就放棄索引了。
where限定查詢的數據
數據中假定就一個男的記錄
反例：
SELECT id,NAME FROM student WHERE sex='男'
1
正例：
SELECT id,NAME FROM student WHERE id=1 AND sex='男'
1
理由：
需要什麼數據，就去查什麼數據，避免返回不必要的數據，節省開銷
避免在索引列上使用內置函數
業務需求：查詢最近七天內新生兒（用學生表替代下）
給birthday欄位創建索引：
ALTER TABLE student ADD INDEX idx_birthday (birthday)
1
當前時間加7天：
SELECT NOW()
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY)
1
2
反例：
EXPLAIN
SELECT * FROM student
WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();
1
2
3
正例：
EXPLAIN
SELECT * FROM student
WHERE birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);
1
2
3
理由：
使用索引列上內置函數
索引失效：
索引有效：
避免在where中對欄位進行表達式操作
反例：
EXPLAIN
SELECT * FROM student WHERE id+1-1=+1
1
2
正例：
EXPLAIN
SELECT * FROM student WHERE id=+1-1+1
1
2
EXPLAIN
SELECT * FROM student WHERE id=1
1
2
理由：
SQL解析時，如果欄位相關的是表達式就進行全表掃描
欄位乾淨無表達式，索引生效
避免在where子句中使用!=或<>操作符
應儘量避免在where子句中使用!=或<>操作符，否則引擎將放棄使用索引而進行全表掃描。記住實現業務優先，實在沒辦法，就只能使用，並不是不能使用。如果不能使用，SQL也就無需支持了。
反例：
EXPLAIN
SELECT * FROM student WHERE salary!=3000
1
2
EXPLAIN
SELECT * FROM student WHERE salary<>3000
1
2
理由：
使用!=和<>很可能會讓索引失效
去重distinct過濾欄位要少
#索引失效
EXPLAIN
SELECT DISTINCT * FROM student
1
2
3
#索引生效
EXPLAIN
SELECT DISTINCT id,NAME FROM student
1
2
3
EXPLAIN
SELECT DISTINCT NAME FROM student
1
2
理由：
帶distinct的語句占用cpu時間高於不帶distinct的語句。因為當查詢很多欄位時，如果使用distinct，資料庫引擎就會對數據進行比較，過濾掉重複數據，然而這個比較、過濾的過程會占用系統資源，如cpu時間
where中使用默認值代替null
環境準備：
#修改表，增加age欄位，類型int，非空，默認值0
ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;
1
2
#修改表，增加age欄位的索引，名稱為idx_age
ALTER TABLE student ADD INDEX idx_age (age);
1
2
反例：
EXPLAIN
SELECT * FROM student WHERE age IS NOT NULL
1
2
正例：
EXPLAIN
SELECT * FROM student WHERE age>0
1
2
理由：
並不是說使用了is null 或者 is not null 就會不走索引了，這個跟mysql版本以及查詢成本都有關
如果mysql優化器發現，走索引比不走索引成本還要高，就會放棄索引，這些條件 !=，<>，is null，is not null經常被認為讓索引失效，其實是因為一般情況下，查詢的成本高，優化器自動放棄索引的
如果把null值，換成默認值，很多時候讓走索引成為可能，同時，表達意思也相對清晰一點
高級SQL優化
批量插入性能提升
大量數據提交，上千，上萬，批量性能非常快，mysql獨有
多條提交：
INSERT INTO student (id,NAME) VALUES(4,'name1');
INSERT INTO student (id,NAME) VALUES(5,'name2');
1
2
批量提交：
INSERT INTO student (id,NAME) VALUES(4,'name1'),(5,'name2');
1
理由：
默認新增SQL有事務控制，導致每條都需要事務開啟和事務提交；而批量處理是一次事務開啟和提交。自然速度飛升
數據量小體現不出來
批量刪除優化
避免同時修改或刪除過多數據，因為會造成cpu利用率過高，會造成鎖表操作，從而影響別人對資料庫的訪問。
反例：
#一次刪除10萬或者100萬+？
delete from student where id <100000;
1
2
#採用單一循環操作，效率低，時間漫長
for（User user:list）{
delete from student;
}
1
2
3
4
正例：
#分批進行刪除，如每次500
for(){
delete student where id<500;
}
1
2
3
4
delete student where id>=500 and id<1000;
1
理由：
一次性刪除太多數據，可能造成鎖表，會有lock wait timeout exceed的錯誤，所以建議分批操作
偽刪除設計
商品狀態（state）：1-上架、2-下架、3-刪除
理由：
這裡的刪除只是一個標識，並沒有從資料庫表中真正刪除，可以作為歷史記錄備查
同時，一個大型系統中，表關係是非常複雜的，如電商系統中，商品作廢了，但如果直接刪除商品，其它商品詳情，物流信息中可能都有其引用。
通過where state=1或者where state=2過濾掉數據，這樣偽刪除的數據用戶就看不到了，從而不影響用戶的使用
操作速度快，特別數據量很大情況下
提高group by語句的效率
可以在執行到該語句前，把不需要的記錄過濾掉
反例：先分組，再過濾
select job，avg（salary） from employee
group by job
having job ='president' or job = 'managent';
1
2
3
正例：先過濾，後分組
select job，avg（salary） from employee
where job ='president' or job = 'managent'
group by job;
1
2
3
複合索引最左特性
創建複合索引，也就是多個欄位
ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)
1
滿足複合索引的左側順序，哪怕只是部分，複合索引生效
EXPLAIN
SELECT * FROM student WHERE NAME='name1'
1
2
沒有出現左邊的欄位，則不滿足最左特性，索引失效
EXPLAIN
SELECT * FROM student WHERE salary=3000
1
2
複合索引全使用，按左側順序出現 name,salary，索引生效
EXPLAIN
SELECT * FROM student WHERE NAME='陳子樞' AND salary=3000
1
2
雖然違背了最左特性，但MYSQL執行SQL時會進行優化，底層進行顛倒優化
EXPLAIN
SELECT * FROM student WHERE salary=3000 AND NAME='name1'
1
2
理由：
複合索引也稱為聯合索引
當我們創建一個聯合索引的時候，如(k1,k2,k3)，相當於創建了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則
聯合索引不滿足最左原則，索引一般會失效，但是這個還跟Mysql優化器有關的
排序欄位創建索引
什麼樣的欄位才需要創建索引呢？原則就是where和order by中常出現的欄位就創建索引。
#使用*，包含了未索引的欄位，導致索引失效
EXPLAIN
SELECT * FROM student ORDER BY NAME;
EXPLAIN
SELECT * FROM student ORDER BY NAME,salary
#name欄位有索引
EXPLAIN
SELECT id,NAME FROM student ORDER BY NAME
#name和salary複合索引
EXPLAIN
SELECT id,NAME FROM student ORDER BY NAME,salary
EXPLAIN
SELECT id,NAME FROM student ORDER BY salary,NAME
#排序欄位未創建索引，性能就慢
EXPLAIN
SELECT id,NAME FROM student ORDER BY sex
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
刪除冗餘和重複的索引
SHOW INDEX FROM student
#創建索引index_name
ALTER TABLE student ADD INDEX index_name (NAME)
#刪除student表的index_name索引
DROP INDEX index_name ON student ;
#修改表結果，刪除student表的index_name索引
ALTER TABLE student DROP INDEX index_name ;
#主鍵會自動創建索引，刪除主鍵索引
ALTER TABLE student DROP PRIMARY KEY ;
1
2
3
4
5
6
7
8
9
10
11
12
13
不要有超過5個以上的表連接
關聯的表個數越多，編譯的時間和開銷也就越大
每次關聯內存中都生成一個臨時表
應該把連接表拆開成較小的幾個執行，可讀性更高
如果一定需要連接很多表才能得到數據，那麼意味著這是個糟糕的設計了
阿里規範中，建議多表聯查三張表以下
inner join 、left join、right join，優先使用inner join
三種連接如果結果相同，優先使用inner join，如果使用left join左邊表儘量小
inner join 內連接，只保留兩張表中完全匹配的結果集
left join會返回左表所有的行，即使在右表中沒有匹配的記錄
right join會返回右表所有的行，即使在左表中沒有匹配的記錄
理由：
如果inner join是等值連接，返回的行數比較少，所以性能相對會好一點
同理，使用了左連接，左邊表數據結果儘量小，條件儘量放到左邊處理，意味著返回的行數可能比較少。這是mysql優化原則，就是小表驅動大表，小的數據集驅動大的數據集，從而讓性能更優
in子查詢的優化
日常開發實現業務需求可以有兩種方式實現：
一種使用資料庫SQL腳本實現
一種使用程序實現
如需求：查詢所有部門的所有員工：
#in子查詢
SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);
#這樣寫等價於：
#先查詢部門表
SELECT id FROM tb_dept
#再由部門dept_id，查詢tb_user的員工
SELECT * FROM tb_user u,tb_dept d WHERE u.dept_id = d.id
1
2
3
4
5
6
7
8
9
假設表A表示某企業的員工表，表B表示部門表，查詢所有部門的所有員工，很容易有以下程序實現，可以抽象成這樣的一個嵌套循環：
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;
}
}
}
1
2
3
4
5
6
7
8
9
上面的需求使用SQL就遠不如程序實現，特別當數據量巨大時。
理由：
資料庫最費勁的就是程序連結的釋放。假設連結了兩次，每次做上百萬次的數據集查詢，查完就結束，這樣就只做了兩次；相反建立了上百萬次連結，申請連結釋放反覆重複，就會額外花費很多實際，這樣系統就受不了了，慢，卡頓
儘量使用union all替代union
反例：
SELECT * FROM student
UNION
SELECT * FROM student
1
2
3
正例：
SELECT * FROM student
UNION ALL
SELECT * FROM student
1
2
3
理由：
union和union all的區別是，union會自動去掉多個結果集合中的重複結果，而union all則將所有的結果全部顯示出來，不管是不是重複
union：對兩個結果集進行並集操作，不包括重複行，同時進行默認規則的排序
union在進行表連結後會篩選掉重複的記錄，所以在表連結後會對所產生的結果集進行排序運算，刪除重複的記錄再返回結果。實際大部分應用中是不會產生重複的記錄，最常見的是過程表與歷史表UNION