一.SQL優(yōu)化
1.Sql優(yōu)化就是指語句在執(zhí)行的時候效率不是那么樂觀所以提高Sql優(yōu)化就是指在數(shù)據(jù)庫的執(zhí)行速度的。
可通過配置搜索引擎优训，
加索引朵你，
分庫分表，
對Sql的各列進(jìn)行精確取其范圍值揣非，
盡量避免全局掃描
2.Sql優(yōu)化的規(guī)則：
不要有超過5個以上的表連接（JOIN）
考慮使用臨時表或表變量存放中間結(jié)果抡医。
少用子查詢
視圖嵌套不要過深,一般視圖嵌套不要超過2個為宜。
能用inner join連接盡量使用inner join等值連接早敬，LEFT JOIN RIGHT JOIN是 取的某個表的滿足條件的全量記錄
單一表的索引數(shù)嚴(yán)禁超過6個
3.Sql查詢緩慢的原因:
數(shù)據(jù)量過大
表設(shè)計不合理
sql語句寫得不好
沒有合理使用索引
4.針對SQL語句的優(yōu)化
4.1 查詢語句中不要使用 *
4.2 盡量減少子查詢忌傻，使用關(guān)聯(lián)查詢（left join,right join,inner join）替代
4.3 減少使用IN或者NOT IN ,使用exists，not exists或者關(guān)聯(lián)查詢語句替代
4.4 or的查詢盡量用 union或者union all 代替(在確認(rèn)沒有重復(fù)數(shù)據(jù)或者不用剔除重復(fù)數(shù)據(jù)時搞监，union all會更好)
4.5 合理的增加冗余的字段（減少表的聯(lián)接查詢）
4.6增加中間表進(jìn)行優(yōu)化（這個主要是在統(tǒng)計報表的場景水孩，后臺開定時任務(wù)將數(shù)據(jù)先統(tǒng)計好，盡量不要在查詢的時候去統(tǒng)計）
4.7建表的時候能使用數(shù)字類型的字段就使用數(shù)字類型（type,status...）琐驴，數(shù)字類型的字段作為條件查詢比字符串的快
4.8 那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的最末尾
4.9 合理使用like模糊查詢
eg：關(guān)鍵詞 %姜%俘种，由于姜前面用到了“%”，因此該查詢必然走全表掃描绝淡，除非必要宙刘，否則不要在關(guān)鍵詞前加%
select * from student where name like '%姜%' --會造成全表掃描
select * from student where name like '姜%' --不會造成全表掃描
4.10where子句使用 ！= 或 <> 操作符優(yōu)化
在where子句中使用 牢酵！= 或 <>操作符悬包，索引將被放棄使用，會進(jìn)行全表查詢馍乙。
eg：SELECT id FROM A WHERE ID != 5 --會造成全表掃描
SELECT id FROM A WHERE ID>5 OR ID<5 --不會造成全局表描
4.11 where子句中使用 IS NULL 或 IS NOT NULL 的優(yōu)化
在where子句中使用 IS NULL 或 IS NOT NULL 判斷布近，索引將被放棄使用，會進(jìn)行全表查詢丝格。
eg：SELECT id FROM A WHERE num IS NULL --會造全表掃描
SELECT id FROM A WHERE num=0 --優(yōu)化成num上設(shè)置默認(rèn)值0撑瞧，確保表中num沒有null值
4.12 where子句使用or的優(yōu)化
很多時候使用union all 或 nuin(必要的時候)的方式替換“or”會得到更好的效果。where子句中使用了or,索引將被放棄使用显蝌。
eg：SELECT id FROM A WHERE num =10 or num = 20--索引失效
SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20 --優(yōu)化后
4.13where子句使用IN 或 NOT IN的優(yōu)化 in和not in 也要慎用季蚂，否則也會導(dǎo)致全表掃描。
方案一：between替換in
SELECT id FROM A WHERE num in(1,2,3) --會造成全表掃描
SELECT id FROM A WHERE num between 1 and 3 --不會造成全表掃描
方案二：exist替換in
SELECT id FROM A WHERE num in(select num from b ) --會造成全表掃描
SELECT num FROM A WHERE num exists(select 1 from B where B.num = A.num) --不會造成全表掃描
方案三：left join替換in
SELECT id FROM A WHERE num in(select num from B) --會造成全表掃描
SELECT id FROM A LEFT JOIN B ON A.num = B.num --不會造成全表掃描
4.14 where子句中對字段進(jìn)行表達(dá)式操作的優(yōu)化 不要在where子句中的“=”左邊進(jìn)行函數(shù)、算數(shù)運算或其他表達(dá)式運算扭屁，否則
系統(tǒng)將可能無法正確使用索引算谈。
eg:
SELECT id FROM A WHERE num/2 = 100 --會造成索引失效
SELECT id FROM A WHERE num = 100*2 --優(yōu)化后
SELECT id FROM A WHERE substring(name,1,3) = 'abc' --會造成索引失效
SELECT id FROM A WHERE LIKE 'abc%' --優(yōu)化后
SELECT id FROM A WHERE datediff(day,createdate,'2016-11-30')=0--會造成索引失效
SELECT id FROM A WHERE createdate>='2016-11-30' and createdate<'2016-12-1'--不會造成索引失效
SELECT id FROM A WHERE year(addate) <2016--會造成索引失效
SELECT id FROM A where addate<'2016-01-01'--不會造成索引失效
4.15 任何地方都不要用 select * from table ，用具體的字段列表替換"*"料滥，不要返回用不到的字段
4.16 使用“臨時表”暫存中間結(jié)果
采用臨時表暫存中間結(jié)果好處：
（1）避免程序中多次掃描主表然眼，減少程序執(zhí)行“共享鎖”阻塞“更新鎖”，減少了阻塞葵腹，提高了并發(fā)性能高每。
（2）盡量使用表變量來代替臨時表。如果表變量包含大量數(shù)據(jù)践宴，請注意索引非常有限（只有主鍵索引）鲸匿。
（3）避免頻繁創(chuàng)建和刪除臨時表，以減少系統(tǒng)資源的浪費阻肩。
（4）盡量避免向客戶端返回大數(shù)據(jù)量带欢，若數(shù)據(jù)量過大，應(yīng)考慮相應(yīng)需求是否合理烤惊。
4.17 limit分頁優(yōu)化 當(dāng)偏移量特別大時乔煞，limit效率會非常低
SELECT id FROM A LIMIT 1000,10 --超級快
SELECT id FROM A LIMIT 90000,10 --特別慢
優(yōu)化方法:
方法一：select id from A order by id limit 90000,10; 很快，0.04秒就OK柒室。因為用了id主鍵做索引當(dāng)然快
方法二：select id,title from A where id>=(select id from collect order by id limit 90000,1) limit 10
方法三：select id from A order by id between 10000000 and 10000010;
4.18 批量插入優(yōu)化
INSERT into person(name,age) values('A',14)
INSERT into person(name,age) values('B',14)
INSERT into person(name,age) values('C',14)
可優(yōu)化為：
INSERT into person(name,age) values('A',14),('B',14),('C',14)
4.19 利用limit 1 渡贾、top 1 取得一行
有時要查詢一張表時，你知道只需要看一條記錄雄右，你可能去查詢一條特殊的記錄空骚。可以使用limit 1 或者 top 1 來終止數(shù)據(jù)庫
索引繼續(xù)掃描整個表或索引擂仍。
SELECT id FROM A LIKE 'abc%' --優(yōu)化之前
SELECT id FROM A LIKE 'abc%' limit 1 --優(yōu)化之后
4.20 盡量不要使用 BY RAND()命令
BY RAND()是隨機(jī)顯示結(jié)果囤屹，這個函數(shù)可能會為表中每一個獨立的行執(zhí)行BY RAND()命令，這個會消耗處理器的處理能力防楷。
SELECT * FROM A order by rand() limit 10
SELECT * FROM A WHERE id >= ((SELECT MAX(id) FROM A-(SELECT MIN(id) FROM A)) * RAND() + (SELECT MIN(id) FROM A) LIMIT 10--優(yōu)化之后
4.21 排序的索引問題
Mysql查詢只是用一個索引，因此如果where子句中已經(jīng)使用了索引的話则涯，那么order by中的列是不會使用索引的复局。因此數(shù)據(jù)
庫默認(rèn)排序可以符合要求情況下不要使用排序操作；盡量不要包含多個列的排序粟判，如果需要最好給這些列創(chuàng)建復(fù)合索引亿昏。
4.22 盡量用 union all 替換 union
union和union all的差異主要是前者需要將兩個（或者多個）結(jié)果集合并后再進(jìn)行唯一性過濾操作，這就會涉及到排序档礁，增加大量
的cpu運算角钩，加大資源消耗及延遲。所以當(dāng)我們可以確認(rèn)不可能出現(xiàn)重復(fù)結(jié)果集或者不在乎重復(fù)結(jié)果集的時候，盡量使用union all
而不是union
4.23 避免類型轉(zhuǎn)換
這里所說的“類型轉(zhuǎn)換”是指where子句中出現(xiàn)column字段的類型和傳入的參數(shù)類型不一致的時候發(fā)生的類型轉(zhuǎn)換递礼。人為的上
通過轉(zhuǎn)換函數(shù)進(jìn)行轉(zhuǎn)換惨险，直接導(dǎo)致mysql無法使用索引。如果非要轉(zhuǎn)型脊髓，應(yīng)該在傳入?yún)?shù)上進(jìn)行轉(zhuǎn)換辫愉。
例如utime 是datetime類型，傳入的參數(shù)是“2016-07-23”将硝，在比較大小時通常是 date（utime）>"2016-07-23",可以優(yōu)化為
utime>"2016-07-23 00：00：00"
4.24 盡可能使用更小的字段
MySQL從磁盤讀取數(shù)據(jù)后是存儲到內(nèi)存中的恭朗，然后使用cpu周期和磁盤I/O讀取它，這意味著越小的數(shù)據(jù)類型占用的空間越
小依疼，從磁盤讀或打包到內(nèi)存的效率都更好痰腮，但也不要太過執(zhí)著減小數(shù)據(jù)類型，要是以后應(yīng)用程序發(fā)生什么變化就沒有空間了律罢。
修改表將需要重構(gòu)膀值，間接地可能引起代碼的改變，這是很頭疼的問題弟翘，因此需要找到一個平衡點虫腋。
4.25 Inner join 和 left join、right join稀余、子查詢
第一：inner join內(nèi)連接也叫等值連接是悦冀，left/rightjoin是外連接。
SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;
SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;
SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;
經(jīng)過多方面的證實inner join性能比較快睛琳，因為inner join是等值連接盒蟆，或許返回的行數(shù)比較少。但是我們要記得有些語句隱形的用
到了等值連接师骗，如：SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;
推薦：能用inner join連接盡量使用inner join連接
第二：子查詢的性能又比外連接性能慢历等，盡量用外連接來替換子查詢。
Select * from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);
A表的數(shù)據(jù)為十萬級表辟癌，B表為百萬級表寒屯，在本機(jī)執(zhí)行差不多用2秒左右，我們可以通過explain可以查看到子查詢是一個相關(guān)子查
詢(DEPENDENCE SUBQUERY);Mysql是先對外表A執(zhí)行全表查詢黍少，然后根據(jù)uuid逐次執(zhí)行子查詢寡夹，如果外層表是一個很大的
表，我們可以想象查詢性能會表現(xiàn)比這個更加糟糕厂置。一種簡單的優(yōu)化就是用innerjoin的方法來代替子查詢菩掏，查詢語句改為：
Select * from A inner join B ON A.uuid=B.uuid using(uuid) where b.uuid>=3000; 這個語句執(zhí)行測試不到一秒；
第三：使用JOIN時候昵济，應(yīng)該用小的結(jié)果驅(qū)動打的結(jié)果（left join 左邊表結(jié)果盡量小智绸，如果有條件應(yīng)該放到左邊先處理野揪，right join
同理反向），同時盡量把牽涉到多表聯(lián)合的查詢拆分多個query (多個表查詢效率低瞧栗，容易鎖表和阻塞)斯稳。如：
Select * from A left join B A.id=B.ref_id where A.id>10;
可以優(yōu)化為：
select * from (select * from A where id >10) T1 left join B on T1.id=B.ref_id;
4.26 exist 代替 in
SELECT * from A WHERE idin (SELECT id from B)
SELECT * from A WHERE id EXISTS (SELECT 1 from A.id= B.id)
in 是在內(nèi)存中遍歷比較exist 需要查詢數(shù)據(jù)庫，所以當(dāng)B的數(shù)據(jù)量比較大時沼溜，exists效率優(yōu)于in. in()只執(zhí)行一次平挑，把B表中的所有
id字段緩存起來，之后檢查A表的id是否與B表中的id相等系草，如果id相等則將A表的記錄加入到結(jié)果集中通熄，直到遍歷完A表的所有記
錄。in()適合B表比A表數(shù)據(jù)小的情況找都，exists()適合B表比A表數(shù)據(jù)大的情況唇辨。
針對mysql，其條件執(zhí)行順序是 從左往右能耻，自上而下赏枚；
針對orcale，其條件執(zhí)行順序是從右往左晓猛，自下而上饿幅。
