Sql語句優(yōu)化和索引

1.Innerjoin和左連接，右連接，子查詢

A.     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)過來之多方面的證實(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連接

B．子查詢的性能又比外連接性能慢，盡量用外連接來替換子查詢。

  Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);

A表的數(shù)據(jù)為十萬級(jí)表，B表為百萬級(jí)表，在本機(jī)執(zhí)行差不多用2秒左右，我們可以通過explain可以查看到子查詢是一個(gè)相關(guān)子查詢(DEPENDENCE SUBQUERY);Mysql是先對(duì)外表A執(zhí)行全表查詢，然后根據(jù)uuid逐次執(zhí)行子查詢，如果外層表是一個(gè)很大的表，我們可以想象查詢性能會(huì)表現(xiàn)比這個(gè)更加糟糕。

  一種簡(jiǎn)單的優(yōu)化就是用innerjoin的方法來代替子查詢，查詢語句改為：

   Select* from A inner join B using(uuid) where b.uuid>=3000;

  這個(gè)語句執(zhí)行測(cè)試不到一秒；

C．在使用ON 和 WHERE 的時(shí)候，記得它們的順序，如：

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id WHERE B.NAME=’XXX’

執(zhí)行過程會(huì)先執(zhí)行ON 后面先過濾掉B表的一些行數(shù)。然而WHERE是后再過濾他們兩個(gè)連接產(chǎn)生的記錄。

不過在這里提醒一下大家：ON后面的條件只能過濾出B表的條數(shù)，但是連接返回的記錄的行數(shù)還是A表的行數(shù)是一樣。如：

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;

返回的記錄數(shù)是A表的條數(shù)，ON后面的條件只起到過濾B表的記錄數(shù)，而

SELECT A.id,A.name,B.id,B.name FROM A ,B WHERE A.id = B.id

返回的條數(shù)，是笛卡爾積后，符合A.id = B.id這個(gè)條件的記錄

D．使用JOIN時(shí)候，應(yīng)該用小的結(jié)果驅(qū)動(dòng)打的結(jié)果（left join 左邊表結(jié)果盡量小，如果有條件應(yīng)該放到左邊先處理，right join同理反向），同事盡量把牽涉到多表聯(lián)合的查詢拆分多個(gè)query(多個(gè)表查詢效率低，容易鎖表和阻塞)。如：

Select * from A left join B ona.id=B.ref_id where B.ref_id>10;

可以優(yōu)化為：select * from (select * from A wehre id >10) T1 left join B onT1.id=B.ref_id;

2.建立索引,加快查詢性能.

A．在建立復(fù)合索引的時(shí)候，在where條件中用到的字段在復(fù)合索引中，則最好把這個(gè)字段放在復(fù)合索引的最左端，這樣才能使用索引，才能提高查詢。

B．保證連接的索引是相同的類型，意思就是A表和B表相關(guān)聯(lián)的字段，必須是同類型的。這些類型都建立了索引，這樣才能兩個(gè)表都能使用索引，如果類型不一樣，至少有一個(gè)表使用不了索引。

C．索引，不僅僅是主鍵和唯一鍵，也可以是其他的任何列。在使用like其中一個(gè)有索引的字段列的時(shí)候。

如： select *from A name like ‘xxx%’;

這個(gè)sql會(huì)使用name的索引（前提name建立了索引）；而下面的語句就使用不了索引

Select * from A name like ‘%xxx’;

因?yàn)椤?’代表任何字符，%xxx不知道怎么去索引的，所以使用不了索引。

D.復(fù)合索引

比如有一條語句這樣的：select* from users where area =’beijing’ and age=22;

如果我們是在area和age上分別創(chuàng)建索引的話，由于mysql查詢每次只能使用一個(gè)索引，所以雖然這樣已經(jīng)相對(duì)不做索引時(shí)全表掃描提高了很多效率，但是如果area，age兩列上創(chuàng)建復(fù)合索引的話將帶來更高的效率。如果我們創(chuàng)建了（area,age,salary）的復(fù)合索引，那么其實(shí)相當(dāng)于創(chuàng)建了（area,age,salary）,(area,age),(area)三個(gè)索引，這樣稱為最佳左前綴特性。因此我們?cè)趧?chuàng)建復(fù)合索引的應(yīng)該將最常用作限制條件的列放在最左邊，依次遞減。

E.索引不會(huì)包含有NULL值的列

只要列中包含有NULL值都將不會(huì)被包含在索引中（除非是唯一值的域，可以存在一個(gè)NULL），復(fù)合索引中只要有一列含有NULL值，那么這一列對(duì)于此復(fù)合索引是無效的。所以我們?cè)跀?shù)據(jù)庫(kù)設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為NULL.

F.使用短索引

對(duì)串列進(jìn)行索引，如果可能應(yīng)該指定一個(gè)前綴長(zhǎng)度。例如，如果有一個(gè)CHAR(255)的列，如果在錢10個(gè)或者20字符內(nèi)，多數(shù)值是唯一的，那么就不要對(duì)整個(gè)列進(jìn)行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。

G．排序的索引問題

Mysql查詢只是用一個(gè)索引，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會(huì)使用索引的。因此數(shù)據(jù)庫(kù)默認(rèn)排序可以符合要求情況下不要使用排序操作；盡量不要包含多個(gè)列的排序，如果需要最好給這些列創(chuàng)建復(fù)合索引。

3.limit千萬級(jí)分頁的時(shí)候優(yōu)化。

A．在我們平時(shí)用limit,如：

Select * from A order by id limit 1,10;

這樣在表數(shù)據(jù)很少的時(shí)候，看不出什么性能問題，倘若到達(dá)千萬級(jí)，如：

Select * from A order by id limit10000000,10;

雖然都是只查詢10記錄，但是這個(gè)就性能就讓人受不了了。所以為什么當(dāng)表數(shù)據(jù)很大的時(shí)候，我們還繼續(xù)用持久層框架如hibernate,ibatis就會(huì)有一些性能問題，除非持久層框架對(duì)這些大數(shù)據(jù)表做過優(yōu)化。

B．在遇見上面的情況，我們可以用另外一種語句優(yōu)化，如：

Select * from A where id>=(Select idfrom a limit 10000000,1) limit 10;

確實(shí)這樣快了很多，不過前提是，id字段建立了索引。也許這個(gè)還不是最優(yōu)的，其實(shí)還可以這樣寫：

Select * from A where id between 10000000and 10000010;

這樣的效率更加高。

4.盡量避免Select * 命令

A.從表中讀取越多的數(shù)據(jù)，查詢會(huì)變得更慢。它會(huì)增加磁盤的操作時(shí)間，還是在數(shù)據(jù)庫(kù)服務(wù)器與web服務(wù)器是獨(dú)立分開的情況下，你將會(huì)經(jīng)歷非常漫長(zhǎng)的網(wǎng)絡(luò)延遲。僅僅是因?yàn)閿?shù)據(jù)不必要的在服務(wù)器之間傳輸。

5.盡量不要使用BY RAND()命令

 A．如果您真需要隨機(jī)顯示你的結(jié)果，有很多更好的途徑實(shí)現(xiàn)。而這個(gè)函數(shù)可能會(huì)為表中每一個(gè)獨(dú)立的行執(zhí)行BY RAND()命令—這個(gè)會(huì)消耗處理器的處理能力，然后給你僅僅返回一行。

 

6.利用limit 1取得唯一行

 A．有時(shí)要查詢一張表時(shí)，你要知道需要看一行，你可能去查詢一條獨(dú)特的記錄。你可以使用limit 1.來終止數(shù)據(jù)庫(kù)引擎繼續(xù)掃描整個(gè)表或者索引,如：

Select * from A  where namelike ‘%xxx’ limit 1;

這樣只要查詢符合like ‘%xxx’的記錄，那么引擎就不會(huì)繼續(xù)掃描表或者索引了。

 

7.盡量少排序

A.排序操作會(huì)消耗較多的CPU資源，所以減少排序可以在緩存命中率高等

 

8.盡量少OR

 A.當(dāng)where子句中存在多個(gè)條件以“或”并存的時(shí)候，Mysql的優(yōu)化器并沒有很好的解決其執(zhí)行計(jì)劃優(yōu)化問題，再加上mysql特有的sql與Storage分層架構(gòu)方式，造成了其性能比較地下，很多時(shí)候使用union all或者union(必要的時(shí)候)的方式代替“or”會(huì)得到更好的效果。

 

9.盡量用union all 代替union

 A.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.

10.避免類型轉(zhuǎn)換

A.這里所說的“類型轉(zhuǎn)換”是指where子句中出現(xiàn)column字段的類型和傳入的參數(shù)類型不一致的時(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)換。

 

11.不要在列上進(jìn)行運(yùn)算

A. 如下面:select * fromusers where YEAR(adddate)<2007;將在每個(gè)行進(jìn)行運(yùn)算，這些導(dǎo)致索引失效進(jìn)行全表掃描，因此我們可以改成：

Select * from users where adddate<’2007-01-01’;

 

12.盡量不要使用NOT IN和<>操作

A. NOT IN和<>操作都不會(huì)使用索引，而是將會(huì)進(jìn)行全表掃描。NOT IN可以NOT EXISTS代替，id<>3則可以使用id>3 or id <3;如果NOT EXISTS是子查詢，還可以盡量轉(zhuǎn)化為外連接或者等值連接，要看具體sql的業(yè)務(wù)邏輯。

B．把NOT IN轉(zhuǎn)化為L(zhǎng)EFT JOIN如：

SELECT * FROM customerinfo WHERE CustomerIDNOT in (SELECT CustomerID FROM salesinfo );

優(yōu)化：

SELECT * FROM customerinfo LEFT JOINsalesinfoON customerinfo.CustomerID=salesinfo. CustomerID WHEREsalesinfo.CustomerID IS NULL;

 

13.使用批量插入節(jié)省交互（最好是使用存儲(chǔ)過程）

A. 盡量使用insert intousers(username,password) values(‘test1’,’pass1’), (‘test2’,’pass2’), (‘test3’,’pass3’);

 

14. 鎖定表

A. 盡管事務(wù)是維護(hù)數(shù)據(jù)庫(kù)完整性的一個(gè)非常好的方法,但卻因?yàn)樗莫?dú)占性,有時(shí)會(huì)影響數(shù)據(jù)庫(kù)的性能,尤其是在很多的應(yīng)用系統(tǒng)中.由于事務(wù)執(zhí)行的過程中,數(shù)據(jù)庫(kù)將會(huì)被鎖定,因此其他的用戶請(qǐng)求只能暫時(shí)等待直到該事務(wù)結(jié)算.如果一個(gè)數(shù)據(jù)庫(kù)系統(tǒng)只有少數(shù)幾個(gè)用戶來使用,事務(wù)造成的影響不會(huì)成為一個(gè)太大問題;但假設(shè)有成千上萬的用戶同時(shí)訪問一個(gè)數(shù)據(jù)庫(kù)系統(tǒng),例如訪問一個(gè)電子商務(wù)網(wǎng)站,就會(huì)產(chǎn)生比較嚴(yán)重的響應(yīng)延遲.其實(shí)有些情況下我們可以通過鎖定表的方法來獲得更好的性能.如:

LOCK TABLE inventory write

Select quanity from inventory whereitem=’book’;

…

Update inventory set quantity=11 whereitem=’book’;

UNLOCK TABLES;

這里，我們用一個(gè)select語句取出初始數(shù)據(jù)，通過一些計(jì)算，用update語句將新值更新到列表中。包含有write關(guān)鍵字的LOCK TABLE語句可以保證在UNLOCK TABLES命令被執(zhí)行之前，不會(huì)有其他的訪問來對(duì)inventory進(jìn)行插入，更新或者刪除的操作。

 

15.對(duì)多表關(guān)聯(lián)的查詢，建立視圖

A．對(duì)多表的關(guān)聯(lián)可能會(huì)有性能上的問題，我們可以對(duì)多表建立視圖，這樣操作簡(jiǎn)單話，增加數(shù)據(jù)安全性，通過視圖，用戶只能查詢和修改指定的數(shù)據(jù)。且提高表的邏輯獨(dú)立性，視圖可以屏蔽原有表結(jié)構(gòu)變化帶來的影響。