https://www.cnblogs.com/deepalley/p/12154000.html
寫SQL語句的時(shí)候我們往往關(guān)注的是SQL的執(zhí)行結(jié)果，但是是否真的關(guān)注了SQL的執(zhí)行效率，是否注意了SQL的寫法規(guī)范？
以下的干貨分享是在實(shí)際開發(fā)過程中總結(jié)的，希望對(duì)大家有所幫助！
1. limit分頁(yè)優(yōu)化
當(dāng)偏移量特別大時(shí)，limit效率會(huì)非常低。
SELECT id FROM A LIMIT 1000,10 很快
SELECT id FROM A LIMIT 90000,10 很慢
方案一：
select id from A order by id limit 90000,10;
復(fù)制代碼如果我們結(jié)合order by使用。很快，0.04秒就OK。 因?yàn)槭褂昧薸d主鍵做索引！當(dāng)然，是否能夠使用索引還需要根據(jù)業(yè)務(wù)邏輯來定，這里只是為了提醒大家，在分頁(yè)的時(shí)候還需謹(jǐn)慎使用！
方案二
select id from A order by id between 90000 and 90010;
復(fù)制代碼2.利用limit 1 、top 1 取得一行
有些業(yè)務(wù)邏輯進(jìn)行查詢操作時(shí)(特別是在根據(jù)某一字段DESC,取最大一筆).可以使用limit 1 或者 top 1 來終止[數(shù)據(jù)庫(kù)索引]繼續(xù)掃描整個(gè)表或索引。
反例
SELECT id FROM A LIKE 'abc%'
復(fù)制代碼正例
SELECT id FROM A LIKE 'abc%' limit 1
復(fù)制代碼3. 任何情況都不要用 select * from table ，用具體的字段列表替換"*"，不要返回用不到的字段,避免全盤掃描！
4. 批量插入優(yōu)化
反例
INSERT into person(name,age) values('A',24)
INSERT into person(name,age) values('B',24)
INSERT into person(name,age) values('C',24)
復(fù)制代碼正例
INSERT into person(name,age) values('A',24),('B',24),('C',24),
復(fù)制代碼sql語句的優(yōu)化主要在于對(duì)索引的正確使用，而我們?cè)陂_發(fā)中經(jīng)常犯的錯(cuò)誤便是對(duì)表進(jìn)行全盤掃描，一來影響性能，而來耗費(fèi)時(shí)間!
5.like語句的優(yōu)化
反例
SELECT id FROM A WHERE name like '%abc%'
復(fù)制代碼由于abc前面用了“%”，因此該查詢必然走全表查詢,除非必要(模糊查詢需要包含abc)，否則不要在關(guān)鍵詞前加%
正例
SELECT id FROM A WHERE name like 'abc%'
復(fù)制代碼6.where子句使用 in 或 not in 的優(yōu)化
sql語句中 in 和 not in 的使用請(qǐng)慎用！使用in 或者 not in 會(huì)丟棄索引，從而進(jìn)行全盤掃描！
方案一：between 替換 in
反例
SELECT id FROM A WHERE num in (1,2,3)
復(fù)制代碼正例
SELECT id FROM A WHERE num between 1 and 3
復(fù)制代碼方案二：exist 替換 in
注：關(guān)于exist和in的用法，片尾有彩蛋～
反例
SELECT id FROM A WHERE num in (select num from B)
復(fù)制代碼正例
SELECT num FROM A WHERE num exists (select 1 from B where B.num = A.num)
復(fù)制代碼方案三：left join 替換 in
反例
SELECT id FROM A WHERE num in (select num from B)
復(fù)制代碼正例
SELECT id FROM A LEFT JOIN B ON A.num = B.num
復(fù)制代碼7.where子句使用or的優(yōu)化
通常使用 union all 或 union 的方式替換“or”會(huì)得到更好的效果。where子句中使用了or關(guān)鍵字,索引將被放棄使用。
反例
SELECT id FROM A WHERE num = 10 or num = 20
復(fù)制代碼正例
SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20
復(fù)制代碼8.where子句中使用 IS NULL 或 IS NOT NULL 的優(yōu)化
反例
SELECT id FROM A WHERE num IS NULL
復(fù)制代碼在where子句中使用 IS NULL 或 IS NOT NULL 判斷，索引將被放棄使用，會(huì)進(jìn)行全表查詢。
正例
優(yōu)化成num上設(shè)置默認(rèn)值0，確保表中num沒有null值,?IS NULL 的用法在實(shí)際業(yè)務(wù)場(chǎng)景下SQL使用率極高，我們應(yīng)注意避免全表掃描
SELECT id FROM A WHERE num=0
復(fù)制代碼9.where子句中對(duì)字段進(jìn)行表達(dá)式操作的優(yōu)化
不要在where子句中的“=”左邊進(jìn)行函數(shù)、算數(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引。
- 1
SELECT id FROM A WHERE datediff(day,createdate,'2019-11-30')=0
復(fù)制代碼優(yōu)化為
SELECT id FROM A WHERE createdate>='2019-11-30' and createdate<'2019-12-1'
復(fù)制代碼- 2
SELECT id FROM A WHERE year(addate) <2020
復(fù)制代碼優(yōu)化為
SELECT id FROM A where addate<'2020-01-01'
復(fù)制代碼10.排序的索引問題?
mysql查詢只是用一個(gè)索引，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會(huì)使用索引。因此數(shù)據(jù)庫(kù)默認(rèn)排序可以符合要求情況下不要使用排序操作；
盡量不要包含多個(gè)列的排序，如果需要最好給這些列創(chuàng)建復(fù)合索引。
11. 盡量用 union all 替換 union
union和union all的差異主要是前者需要將兩個(gè)（或者多個(gè)）結(jié)果集合并后再進(jìn)行唯一性過濾操作，這就會(huì)涉及到排序，增加大量的cpu運(yùn)算，加大資源消耗及延遲。所以當(dāng)我們可以確認(rèn)不可能出現(xiàn)重復(fù)結(jié)果集或者不在乎重復(fù)結(jié)果集的時(shí)候，盡量使用union all而不是union
12.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;
復(fù)制代碼經(jīng)過來之多方面的證實(shí)?inner join性能比較快，因?yàn)閕nner join是等值連接，或許返回的行數(shù)比較少。但是我們要記得有些語句隱形的用到了等值連接，如：
SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;
推薦：能用inner join連接盡量使用inner join連接
- 第二：子查詢的性能又比外連接性能慢，盡量用外連接來替換子查詢。
反例
mysql是先對(duì)外表A執(zhí)行全表查詢，然后根據(jù)uuid逐次執(zhí)行子查詢，如果外層表是一個(gè)很大的表，我們可以想象查詢性能會(huì)表現(xiàn)比這個(gè)更加糟糕。
Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);
復(fù)制代碼執(zhí)行時(shí)間：2s左右
正例
Select* from A inner join B ON A.uuid=B.uuid where b.uuid>=3000; 這個(gè)語句執(zhí)行測(cè)試不到一秒；
復(fù)制代碼執(zhí)行時(shí)間：1s不到
- 第三：使用JOIN時(shí)候，應(yīng)該用小的結(jié)果驅(qū)動(dòng)大的結(jié)果
left join 左邊表結(jié)果盡量小，如果有條件應(yīng)該放到左邊先處理，right join同理反向。如：
反例
Select * from A left join B A.id=B.ref_id where A.id>10
復(fù)制代碼正例
select * from (select * from A wehre id >10) T1 left join B on T1.id=B.ref_id;
復(fù)制代碼13.exist 代替 in
反例
SELECT * from A WHERE id in ( SELECT id from B )
復(fù)制代碼正例
SELECT * from A WHERE id EXISTS ( SELECT 1 from A.id= B.id )
復(fù)制代碼分析:
in 是在內(nèi)存中遍歷比較
exist 需要查詢數(shù)據(jù)庫(kù)，所以當(dāng)B的數(shù)據(jù)量比較大時(shí)，exists效率優(yōu)于in**
in()只執(zhí)行一次，把B表中的所有id字段緩存起來，之后檢查A表的id是否與B表中的id相等，如果id相等則將A表的記錄加入到結(jié)果集中，直到遍歷完A表的所有記錄。
In 操作的流程原理如同一下代碼
List resultSet={};
Array A=(select * from A);
Array B=(select id from B);
for(int i=0;i<A.length;i++) {
for(int j=0;j<B.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
}
}
}
return resultSet;
復(fù)制代碼可以看出，當(dāng)B表數(shù)據(jù)較大時(shí)不適合使用in()，因?yàn)闀?huì)把B表數(shù)據(jù)全部遍歷一次
如：A表有10000條記錄，B表有1000000條記錄，那么最多有可能遍歷10000*1000000次，效率很差。
再如：A表有10000條記錄，B表有100條記錄，那么最多有可能遍歷10000*100次，遍歷次數(shù)大大減少，效率大大提升。
??結(jié)論：in()適合B表比A表數(shù)據(jù)小的情況
exist()會(huì)執(zhí)行A.length()次，執(zhí)行過程代碼如下
List resultSet={};
Array A=(select * from A);
for(int i=0;i<A.length;i++) {
if(exists(A[i].id) { //執(zhí)行select 1 from B where B.id=A.id是否有記錄返回
resultSet.add(A[i]);
}
}return resultSet;
復(fù)制代碼當(dāng)B表比A表數(shù)據(jù)大時(shí)適合使用exists()，因?yàn)樗鼪]有那么多遍歷操作，只需要再執(zhí)行一次查詢就行。
如：A表有10000條記錄，B表有1000000條記錄，那么exists()會(huì)執(zhí)行10000次去判斷A表中的id是否與B表中的id相等。
如：A表有10000條記錄，B表有100000000條記錄，那么exists()還是執(zhí)行10000次，因?yàn)樗粓?zhí)行A.length次，可見B表數(shù)據(jù)越多，越適合exists()發(fā)揮效果。
再如：A表有10000條記錄，B表有100條記錄，那么exists()還是執(zhí)行10000次，還不如使用in()遍歷10000*100次，因?yàn)閕n()是在內(nèi)存里遍歷比較，而exists()需要查詢數(shù)據(jù)庫(kù)，
我們都知道查詢數(shù)據(jù)庫(kù)所消耗的性能更高，而內(nèi)存比較很快。 ??文章來源：http://www.zghlxwxcb.cn/news/detail-454143.html
結(jié)論：exists()適合B表比A表數(shù)據(jù)大的情況文章來源地址http://www.zghlxwxcb.cn/news/detail-454143.html
到了這里，關(guān)于干貨！SQL性能優(yōu)化，書寫高質(zhì)量SQL語句的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
