怎么加快查詢速度，優(yōu)化查詢效率，主要原則就是應(yīng)盡量避免全表掃描，應(yīng)該考慮在where及order by 涉及的列上建立索引。
建立索引不是建的越多越好，原則是：
第一：一個(gè)表的索引不是越多越好，也沒(méi)有一個(gè)具體的數(shù)字，根據(jù)以往的經(jīng)驗(yàn)，一個(gè)表的索引最多不能超過(guò)6個(gè)，因?yàn)樗饕蕉?，?duì)update和insert操作也會(huì)有性能的影響，涉及到索引的新建和重建操作。
第二：建立索引的方法論為：多數(shù)查詢經(jīng)常使用的列；很少進(jìn)行修改操作的列；索引需要建立在數(shù)據(jù)差異化大的列上
利用以上的基礎(chǔ)我們討論一下如何優(yōu)化sql.
1、sql語(yǔ)句模型結(jié)構(gòu)優(yōu)化指導(dǎo)
a. ORDER BY + LIMIT組合的索引優(yōu)化
如果一個(gè)SQL語(yǔ)句形如：SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];
這個(gè)SQL語(yǔ)句優(yōu)化比較簡(jiǎn)單，在[sort]這個(gè)欄位上建立索引即可。
b. WHERE + ORDER BY + LIMIT組合的索引優(yōu)化
如果一個(gè)SQL語(yǔ)句形如：SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [VALUE] ORDER BY [sort] LIMIT [offset],[LIMIT];
這個(gè)語(yǔ)句，如果你仍然采用第一個(gè)例子中建立索引的方法，雖然可以用到索引，但是效率不高。更高效的方法是建立一個(gè)聯(lián)合索引(columnX,sort)
c. WHERE+ORDER BY多個(gè)欄位+LIMIT
如果一個(gè)SQL語(yǔ)句形如：SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
對(duì)于這個(gè)語(yǔ)句，大家可能是加一個(gè)這樣的索引:(x,y,uid)。但實(shí)際上更好的效果是(uid,x,y)。這是由MySQL處理排序的機(jī)制造成的。
2、復(fù)合索引(形如(x,y,uid)索引的索引)
先看這樣一條語(yǔ)句這樣的：select* from users where area =’beijing’ and age=22;
? ? ? ? 如果我們是在area和age上分別創(chuàng)建索引的話，由于mysql查詢每次只能使用一個(gè)索引，所以雖然這樣已經(jīng)相對(duì)不做索引時(shí)全表掃描提高了很多效率，但是如果area，age兩列上創(chuàng)建復(fù)合索引的話將帶來(lái)更高的效率。 在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致。例如我們建立了一個(gè)這樣的索引（area,age,salary），那么其實(shí)相當(dāng)于創(chuàng)建了（area,age,salary）,(area,age),(area)三個(gè)索引，這樣稱為最佳左前綴特性。
3、like語(yǔ)句優(yōu)化
SELECTidFROMAWHEREnamelike'%abc%'
由于abc前面用了“%”，因此該查詢必然走全表查詢，除非必要，否則不要在關(guān)鍵詞前加%，優(yōu)化成如下? ? ? ? ? ? ?SELECTidFROMAWHEREnamelike'abc%'
4、where子句使用 ！= 或 <> 操作符優(yōu)化
在where子句中使用 ！= 或 <>操作符，索引將被放棄使用，會(huì)進(jìn)行全表查詢。
如SQL:SELECT id FROM A WHERE ID != 5?優(yōu)化成：SELECT id FROM A WHERE ID>5 OR ID<5
5、where子句中使用 IS NULL 或 IS NOT NULL 的優(yōu)化
在where子句中使用 IS NULL 或 IS NOT NULL 判斷，索引將被放棄使用，會(huì)進(jìn)行全表查詢。
如SQL:SELECT id FROM A WHERE num IS NULL?優(yōu)化成num上設(shè)置默認(rèn)值0，確保表中num沒(méi)有null值，然后SQL為：SELECT id FROM A WHERE num=0
6、where子句使用or的優(yōu)化
很多時(shí)候使用union all 或 nuin(必要的時(shí)候)的方式替換“or”會(huì)得到更好的效果。where子句中使用了or,索引將被放棄使用。
如SQL:SELECT id FROM A WHERE num =10 or num = 20?優(yōu)化成：SELECT id FROM A WHERE num = 10 union allSELECT id FROM A WHERE num=20
7、where子句使用IN 或 NOT IN的優(yōu)化
in和not in 也要慎用，否則也會(huì)導(dǎo)致全表掃描。
方案一：between替換in
?如SQL:SELECT id FROM A WHERE num in(1,2,3)?優(yōu)化成：SELECT id FROM A WHERE num?between 1 and 3
方案二：exist替換in
?如SQL:SELECT id FROM A WHERE num in(select num from b )?優(yōu)化成：SELECT num FROM A WHERE num?exists(select 1 from B where B.num = A.num)
方案三：left join替換in
?如SQL:SELECT id FROM A WHERE num in(select num from B)?優(yōu)化成：SELECT id FROM A?LEFT JOIN B ON A.num = B.num
8、where子句中對(duì)字段進(jìn)行表達(dá)式操作的優(yōu)化
不要在where子句中的“=”左邊進(jìn)行函數(shù)、算數(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無(wú)法正確使用索引。
?如SQL:SELECT id FROM A WHERE num/2 = 100?優(yōu)化成：SELECT id FROM A WHERE num = 100*2
?如SQL:SELECT id FROM A WHERE substring(name,1,3) = 'abc'?優(yōu)化成：SELECT id FROM A WHERE LIKE 'abc%'
如SQL:SELECT id FROM A WHERE datediff(day,createdate,'2016-11-30')=0?優(yōu)化成：SELECT id FROM A WHERE createdate>='2016-11-30' and?createdate<'2016-12-1'
?如SQL:SELECT id FROM A WHERE year(addate) <2016?優(yōu)化成：SELECT id FROM A where addate<'2016-01-01'
9、任何地方都不要用 select * from table ，用具體的字段列表替換"*"，不要返回用不到的字段
10、使用“臨時(shí)表”暫存中間結(jié)果
采用臨時(shí)表暫存中間結(jié)果好處：
?。?）避免程序中多次掃描主表，減少程序執(zhí)行“共享鎖”阻塞“更新鎖”，減少了阻塞，提高了并發(fā)性能。
?。?）盡量使用表變量來(lái)代替臨時(shí)表。如果表變量包含大量數(shù)據(jù)，請(qǐng)注意索引非常有限（只有主鍵索引）。
?。?）避免頻繁創(chuàng)建和刪除臨時(shí)表，以減少系統(tǒng)資源的浪費(fèi)。
?。?）盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過(guò)大，應(yīng)考慮相應(yīng)需求是否合理。
11、limit分頁(yè)優(yōu)化
當(dāng)偏移量特別時(shí)，limit效率會(huì)非常低
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。 因?yàn)橛昧薸d主鍵做索引當(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;
12、批量插入優(yōu)化
INSERTintoperson(name,age)values('A',14)INSERTintoperson(name,age)values('B',14)INSERTintoperson(name,age)values('C',14)
可優(yōu)化為：
INSERTintoperson(name,age)values('A',14),('B',14),('C',14),
13、利用limit 1 、top 1 取得一行
有時(shí)要查詢一張表時(shí)，你知道只需要看一條記錄，你可能去查詢一條特殊的記錄?？梢允褂胠imit 1 或者 top 1 來(lái)終止數(shù)據(jù)庫(kù)索引繼續(xù)掃描整個(gè)表或索引。
如SQL：SELECT id FROM A LIKE 'abc%'?優(yōu)化為：SELECT id FROM A LIKE 'abc%'?limit 1
14、盡量不要使用 BY RAND()命令
BY RAND()是隨機(jī)顯示結(jié)果，這個(gè)函數(shù)可能會(huì)為表中每一個(gè)獨(dú)立的行執(zhí)行BY RAND()命令，這個(gè)會(huì)消耗處理器的處理能力。
如SQL：SELECT * FROM A order by rand() limit 10?優(yōu)化為：SELECT * FROM A WHERE id >= ((SELECT MAX(id) FROM A)-(SELECT MIN(id) FROM A)) * RAND() + (SELECT MIN(id) FROM A) LIMIT 10
15、排序的索引問(wèn)題
Mysql查詢只是用一個(gè)索引，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會(huì)使用索引的。因此數(shù)據(jù)庫(kù)默認(rèn)排序可以符合要求情況下不要使用排序操作；
盡量不要包含多個(gè)列的排序，如果需要最好給這些列創(chuàng)建復(fù)合索引。
16、盡量用 union add 替換 union
union和union all的差異主要是前者需要將兩個(gè)（或者多個(gè)）結(jié)果集合并后再進(jìn)行唯一性過(guò)濾操作，這就會(huì)涉及到排序，增加大量的cpu運(yùn)算，加大資源消耗及延遲。所以當(dāng)我們可以確認(rèn)不可能出現(xiàn)重復(fù)結(jié)果集或者不在乎重復(fù)結(jié)果集的時(shí)候，盡量使用union all而不是union
17、避免類型轉(zhuǎn)換
這里所說(shuō)的“類型轉(zhuǎn)換”是指where子句中出現(xiàn)column字段的類型和傳入的參數(shù)類型不一致的時(shí)候發(fā)生的類型轉(zhuǎn)換。人為的上通過(guò)轉(zhuǎn)換函數(shù)進(jìn)行轉(zhuǎn)換，直接導(dǎo)致mysql無(wú)法使用索引。如果非要轉(zhuǎn)型，應(yīng)該在傳入?yún)?shù)上進(jìn)行轉(zhuǎn)換。
? 例如utime 是datetime類型，傳入的參數(shù)是“2016-07-23”，在比較大小時(shí)通常是 date（utime）>"2016-07-23",可以優(yōu)化為utime>"2016-07-23 00：00：00"
18、盡可能使用更小的字段
MySQL從磁盤讀取數(shù)據(jù)后是存儲(chǔ)到內(nèi)存中的，然后使用cpu周期和磁盤I/O讀取它，這意味著越小的數(shù)據(jù)類型占用的空間越小，從磁盤讀或打包到內(nèi)存的效率都更好，但也不要太過(guò)執(zhí)著減小數(shù)據(jù)類型，要是以后應(yīng)用程序發(fā)生什么變化就沒(méi)有空間了。
修改表將需要重構(gòu)，間接地可能引起代碼的改變，這是很頭疼的問(wèn)題，因此需要找到一個(gè)平衡點(diǎn)。
19、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)過(guò)來(lái)之多方面的證實(shí)inner join性能比較快，因?yàn)閕nner join是等值連接，或許返回的行數(shù)比較少。但是我們要記得有些語(yǔ)句隱形的用到了等值連接，如：
SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;
推薦：能用inner join連接盡量使用inner join連接
第二：子查詢的性能又比外連接性能慢，盡量用外連接來(lái)替換子查詢。
Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);
A表的數(shù)據(jù)為十萬(wàn)級(jí)表，B表為百萬(wàn)級(jí)表，在本機(jī)執(zhí)行差不多用2秒左右，我們可以通過(guò)explain可以查看到子查詢是一個(gè)相關(guān)子查詢(DEPENDENCE SUBQUERY);Mysql是先對(duì)外表A執(zhí)行全表查詢，然后根據(jù)uuid逐次執(zhí)行子查詢，如果外層表是一個(gè)很大的表，我們可以想象查詢性能會(huì)表現(xiàn)比這個(gè)更加糟糕。
?一種簡(jiǎn)單的優(yōu)化就是用innerjoin的方法來(lái)代替子查詢，查詢語(yǔ)句改為：
Select* from A inner join B ON A.uuid=B.uuid using(uuid) where b.uuid>=3000;?這個(gè)語(yǔ)句執(zhí)行測(cè)試不到一秒；
第三：使用JOIN時(shí)候，應(yīng)該用小的結(jié)果驅(qū)動(dòng)打的結(jié)果（left join 左邊表結(jié)果盡量小，如果有條件應(yīng)該放到左邊先處理，right join同理反向），同時(shí)盡量把牽涉到多表聯(lián)合的查詢拆分多個(gè)query(多個(gè)表查詢效率低，容易鎖表和阻塞)。如：
Select * from A left join B A.id=B.ref_id where ?A.id>10;可以優(yōu)化為：select * from (select * from A wehre id >10) T1 left join B on T1.id=B.ref_id;
20、exist 代替 in
SELECT*fromAWHEREidin (SELECTidfromB)
SELECT*fromAWHEREidEXISTS(SELECT1fromA.id=B.id)
in 是在內(nèi)存中遍歷比較
exist 需要查詢數(shù)據(jù)庫(kù)，所以當(dāng)B的數(shù)據(jù)量比較大時(shí)，exists效率優(yōu)于in.
in()只執(zhí)行一次，把B表中的所有id字段緩存起來(lái)，之后檢查A表的id是否與B表中的id相等，如果id相等則將A表的記錄加入到結(jié)果集中，直到遍歷完A表的所有記錄。
In 操作的流程原理如同一下代碼
List resultSet={};
? ? Array A=(select * from A);
? ? Array B=(select id from B);
? ? for(inti=0;i
? ? ? ? ? ? for(intj=0;j
? ? ? ? ? if(A[i].id==B[j].id) {
? ? ? ? ? ? resultSet.add(A[i]);
? ? ? ? ? ? break;
? ? ? ? ? }
? ? ? }
? ? }
? ? returnresultSet;
可以看出，當(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í)行過(guò)程代碼如下
List resultSet={};
? ? Array A=(select * from A);
? ? for(inti=0;i
? ? ? if(exists(A[i].id) {//執(zhí)行select 1 from B where B.id=A.id是否有記錄返回? ? ? ? ? resultSet.add(A[i]);
? ? ? }
? ? }
? ? returnresultSet;
當(dāng)B表比A表數(shù)據(jù)大時(shí)適合使用exists()，因?yàn)樗鼪](méi)有那么多遍歷操作，只需要再執(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次，可見(jiàn)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)存比較很快。
結(jié)論：exists()適合B表比A表數(shù)據(jù)大的情況
當(dāng)A表數(shù)據(jù)與B表數(shù)據(jù)一樣大時(shí)，in與exists效率差不多，可任選一個(gè)使用
