本文主要內(nèi)容：

1：查詢語(yǔ)句where 子句使用時(shí)候優(yōu)化或者需要注意的

2：like語(yǔ)句使用時(shí)候需要注意

3：in語(yǔ)句代替語(yǔ)句

4：索引使用或是創(chuàng)建需要注意

假設(shè)用戶表有一百萬(wàn)用戶量。也就是1000000.num是主鍵

1：對(duì)查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在where及order by 涉及的列上創(chuàng)建索引。

因?yàn)椋核饕龑?duì)查詢的速度有著至關(guān)重要的影響。

2：盡量避免在where字句中對(duì)字段進(jìn)行null值的判斷。否則將會(huì)導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。

例如：select id from user where num is null ?？梢詫um是這個(gè)字段設(shè)置默認(rèn)值0.確保表中沒(méi)有null值，然后在進(jìn)行查詢。

sql如下：select id from user where num=0;

（考慮如下情況，假設(shè)數(shù)據(jù)庫(kù)中一個(gè)表有10^{6條記錄，DBMS的頁(yè)面大小為4K，并存儲(chǔ)100條記錄。如果沒(méi)有索引，查詢將對(duì)整個(gè)表進(jìn)行掃描，最壞的情況下，如果所有數(shù)據(jù)頁(yè)都不在內(nèi)存，需要讀取10}4個(gè)頁(yè)面，如果這10^{4個(gè)頁(yè)面在磁盤上隨機(jī)分布，需要進(jìn)行10}4次I/O，假設(shè)磁盤每次I/O時(shí)間為10ms(忽略數(shù)據(jù)傳輸時(shí)間)，則總共需要100s(但實(shí)際上要好很多很多)。如果對(duì)之建立B-Tree索引，則只需要進(jìn)行l(wèi)og100(10^6)=3次頁(yè)面讀取，最壞情況下耗時(shí)30ms。這就是索引帶來(lái)的效果，很多時(shí)候，當(dāng)你的應(yīng)用程序進(jìn)行SQL查詢速度很慢時(shí)，應(yīng)該想想是否可以建索引）

3：應(yīng)盡量避免在where子句中使用!=或者是<>操作符號(hào)。否則引擎將放棄使用索引，進(jìn)而進(jìn)行全表掃描。

4：應(yīng)盡量避免在where子句中使用or來(lái)連接條件，否則導(dǎo)致放棄使用索引而進(jìn)行全表掃描。可以使用 union 或者是 union all代替。

例如： select id from user where num =10 or num =20 這個(gè)語(yǔ)句景導(dǎo)致引擎放棄num索引，而要全表掃描來(lái)進(jìn)行處理的。

可以使用union 或者是 union all來(lái)代替。如下：

select id from user where num = 10;

union all

select id from user where num =20;

(union 和 nuion all 的區(qū)別這里就不贅述了)

5：in 和 not in 也要慎用，否則將會(huì)導(dǎo)致全表掃描。

in 對(duì)于連續(xù)的數(shù)組，可以使用between ...and.來(lái)代替。

例如：

select id from user where num in (1,2,3);

像這樣連續(xù)的就可以使用between ...and...來(lái)代替了。如下：

select id from user where num between 1 and 3;

6：like使用需注意

下面這個(gè)查詢也將導(dǎo)致全表查詢：

select id from user where name like '%三'；

如果想提高效率，可以考慮到全文檢索。比如solr或是luncene

而下面這個(gè)查詢卻使用到了索引：

select id from user where name like '張%'；

7：where子句參數(shù)使用時(shí)候需注意

如果在where子句中使用參數(shù)，也會(huì)導(dǎo)致全表掃描。因?yàn)閟ql只會(huì)在運(yùn)行時(shí)才會(huì)解析局部變量。但優(yōu)化程序不能將訪問(wèn)計(jì)劃的選擇推遲到運(yùn)行時(shí)；必須在編譯時(shí)候進(jìn)行選擇。然而，如果在編譯時(shí)建立訪問(wèn)計(jì)劃，變量的值還是未知大，因而無(wú)法作為索引選擇輸入項(xiàng)。

如下面的語(yǔ)句將會(huì)進(jìn)行全表掃描：

select id from user where num = @num

進(jìn)行優(yōu)化，我們知道num就是主鍵。是索引。

所以可以改為強(qiáng)制查詢使用索引：

select id from user where (index(索引名稱)) where num = @num;

8：盡量避免在where子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。

例如：select id from user where num/2=100

應(yīng)修改為：

select id from user where num = 100*2;

9：盡量避免愛where子句中對(duì)字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄索引，而進(jìn)行全表掃描。

例如：

select id from user substring(name,1,3) = 'abc' ，這句sql的含義其實(shí)就是，查詢name以abc開頭的用戶id

(注：substring(字段，start,end)這個(gè)是mysql的截取函數(shù))

應(yīng)修改為：

select id from user where name like 'abc%';

10：不要在where子句中的"="左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或是使用其他表達(dá)式運(yùn)算，否則系統(tǒng)可能無(wú)法正確使用索引

11：復(fù)合索引查詢注意

在使用索引字段作為條件時(shí)候，如果該索引是復(fù)合索引，那么必須使用該索引中的第一個(gè)字段作為條件時(shí)候才能保證系統(tǒng)使用該所以，否則該索引將不會(huì)被使用，并且應(yīng)盡可能的讓字段順序和索引順序一致。

12：不要寫一些沒(méi)意義的查詢。

例如：需要生成一個(gè)空表結(jié)構(gòu)和user表結(jié)構(gòu)一樣(注：生成的新 new table的表結(jié)構(gòu)和 老表 old table 結(jié)構(gòu)一致)

select col1,col2,col3.....into newTable from user where 1=0

上面這行sql執(zhí)行后不會(huì)返回任何的結(jié)果集，但是會(huì)消耗系統(tǒng)資源的。

應(yīng)修改為：

create table newTable (....)這種語(yǔ)句。

13：很多時(shí)候用exists 代替 in是一個(gè)很好的選擇。

比如：

select num from user where num in(select num from newTable);

可以使用下面語(yǔ)句代替：

select num from user a where exists(select num from newTable b where b.num = a.num );

14：并不是所有索引對(duì)查詢都有效，sql是根據(jù)表中數(shù)據(jù)進(jìn)行查詢優(yōu)化的，當(dāng)索引lie(索引字段)有大量重復(fù)數(shù)據(jù)的時(shí)候，sql查詢可能不會(huì)去利用索引。如一表中字段 sex、male、female 幾乎各一半。那么即使在sex上創(chuàng)建了索引對(duì)查詢效率也起不了多大作用。

15：索引創(chuàng)建需注意

并非索引創(chuàng)建越多越好。索引固然可以提高相應(yīng)的查詢效率，但是同樣會(huì)降低insert以及update的效率。因?yàn)樵趇nsert或是update的時(shí)候有可能會(huì)重建索引或是修改索引。所以索引怎樣創(chuàng)建需要慎重考慮，視情況而定。一個(gè)表中所以數(shù)量最好不要超過(guò)6個(gè)。若太多，則需要考慮一些不常用的列上創(chuàng)建索引是否有必要.