1、避免全表掃描

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

2、避免判斷null 值

應(yīng)盡量避免在where 子句中對字段進行null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null

可以在num 上設(shè)置默認(rèn)值0，確保表中num 列沒有null 值，然后這樣查詢：

select id from t where num=0

（免費學(xué)習(xí)視頻教程推薦：mysql視頻教程）

3、避免不等值判斷

應(yīng)盡量避免在where 子句中使用!=或<>操作符，否則引擎將放棄使用索引而進行全表掃描。

4、避免使用or 邏輯

應(yīng)盡量避免在where 子句中使用or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描，如：

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10 union all select id from t where num=20

5、慎用in 和not in 邏輯

in 和not in 也要慎用，否則會導(dǎo)致全表掃描，如：

select id from t1 where num in(select id from t2 where id > 10)

此時外層查詢會全表掃描，不使用索引?？梢孕薷臑椋?/p>

select id from t1,(select id from t1 where id > 10)t2 where t1.id = t2.id

此時索引被使用，可以明顯提升查詢效率。

6、注意模糊查詢

下面的查詢也將導(dǎo)致全表掃描：

select id from t where name like '%abc%'

模糊查詢?nèi)绻潜匾獥l件時，可以使用select id from t where name like 'abc%'來實現(xiàn)模糊查詢，此時索引將被使用。如果頭匹配是必要邏輯，建議使用全文搜索引擎（Elastic search、Lucene、Solr 等）。

7、避免查詢條件中字段計算

應(yīng)盡量避免在where 子句中對字段進行表達式操作，這將導(dǎo)致引擎放棄使用索引而進行全表掃描。如：

select id from t where num/2=100

應(yīng)改為:

select id from t where num=100*2

8、避免查詢條件中對字段進行函數(shù)操作

應(yīng)盡量避免在where 子句中對字段進行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進行全表掃描。如：

select id from t where substring(name,1,3)='abc'--name 以abc 開頭的id

應(yīng)改為:

select id from t where name like 'abc%'

9、WHERE 子句“=”左邊注意點

不要在where 子句中的“=”左邊進行函數(shù)、算術(shù)運算或其他表達式運算，否則系統(tǒng)將可能無法正確使用索引。

10、組合索引使用

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

11、不要定義無異議的查詢

不要寫一些沒有意義的查詢，如需要生成一個空表結(jié)構(gòu)：

select col1,col2 into #t from t where 1=0

這類代碼不會返回任何結(jié)果集，但是會消耗系統(tǒng)資源的，應(yīng)改成這樣：

create table #t(...)

12、exists

很多時候用exists 代替in 是一個好的選擇：

select num from a where num in(select num from b)

用下面的語句替換：

select num from a where exists(select 1 from b where num=a.num)

13、索引也可能失效

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

14、表格字段類型選擇

盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。

這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了。盡可能的使用varchar 代替char ，因為首先可變長度字段存儲空間小，可以節(jié)省存儲空間，其次對于查詢來說，在一個相對較小的字段內(nèi)搜索效率顯然要高些。

15、查詢語法中的字段

任何地方都不要使用select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。

16、索引無關(guān)優(yōu)化

不使用*、盡量不使用union，union all 等關(guān)鍵字、盡量不使用or 關(guān)鍵字、盡量使用等值判斷。表連接建議不超過5 個。如果超過5 個，則考慮表格的設(shè)計。（互聯(lián)網(wǎng)應(yīng)用中）表連接方式使用外聯(lián)優(yōu)于內(nèi)聯(lián)。外連接有基礎(chǔ)數(shù)據(jù)存在。

如：A left join B,基礎(chǔ)數(shù)據(jù)是A。A inner join B，沒有基礎(chǔ)數(shù)據(jù)的，先使用笛卡爾積完成全連接，在根據(jù)連接條件得到內(nèi)連接結(jié)果集。

大數(shù)據(jù)量級的表格做分頁查詢時，如果頁碼數(shù)量過大，則使用子查詢配合完成分頁邏輯。

Select * from table limit 1000000, 10 Select * from table where id in (select pk from table limit100000, 10)

相關(guān)文章教程推薦：mysql教程