SQL語(yǔ)句性能優(yōu)化策略

1. 為 WHERE 及 ORDER BY 涉及的列上建立索引

對(duì)查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 WHERE 及 ORDER BY 涉及的列上建立索引

2. where中使用默認(rèn)值代替null

應(yīng)盡量避免在 WHERE 子句中對(duì)字段進(jìn)行 NULL 值判斷，創(chuàng)建表時(shí) NULL 是默認(rèn)值，但大多數(shù)時(shí)候應(yīng)該使用 NOT NULL，或者使用一個(gè)特殊的值，如 0，-1 作為默認(rèn)值。

為啥建議where中使用默認(rèn)值代替null，四個(gè)原因：

1. 并不是說使用了is null或者 is not null就會(huì)不走索引了，這個(gè)跟mysql版本以及查詢成本都有關(guān)；
2. 如果mysql優(yōu)化器發(fā)現(xiàn)，走索引比不走索引成本還要高，就會(huì)放棄索引，這些條件 !=，<>，is null，is not null經(jīng)常被認(rèn)為讓索引失效；
3. 其實(shí)是因?yàn)橐话闱闆r下，查詢的成本高，優(yōu)化器自動(dòng)放棄索引的；
4. 如果把null值，換成默認(rèn)值，很多時(shí)候讓走索引成為可能，同時(shí)，表達(dá)意思也相對(duì)清晰一點(diǎn)；

3. 慎用 != 或 <> 操作符

MySQL 只有對(duì)以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些時(shí)候的 LIKE。所以：應(yīng)盡量避免在 WHERE 子句中使用 != 或 <> 操作符， 會(huì)導(dǎo)致全表掃描。

4. 慎用 OR 來連接條件

使用or可能會(huì)使索引失效，從而全表掃描； 應(yīng)盡量避免在 WHERE 子句中使用 OR 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。

5. 慎用 IN 和 NOT IN

IN 和 NOT IN 也要慎用，否則會(huì)導(dǎo)致全表掃描。對(duì)于連續(xù)的數(shù)值，能用 BETWEEN 就不要用 IN：select id from t where num between 1 and 3。

6. 慎用 左模糊like ‘%…’

模糊查詢，程序員最喜歡的就是使用like，like很可能讓索引失效。比如：

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

而select id from t where name like‘abc%’才用到索引。 所以：

1. 首先盡量避免模糊查詢，如果必須使用，不采用全模糊查詢，也應(yīng)盡量采用右模糊查詢， 即like ‘…%’，是會(huì)使用索引的；
2. 左模糊like ‘%…’無法直接使用索引，但可以利用reverse + function index的形式，變化成 like ‘…%’；
3. 全模糊查詢是無法優(yōu)化的，一定要使用的話建議使用搜索引擎，比如 ElasticSearch。

7. WHERE條件使用參數(shù)會(huì)導(dǎo)致全表掃描

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

select id from t where num=@num

因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量，但優(yōu)化程序不能將訪問計(jì)劃的選擇推 遲到 運(yùn)行時(shí)；

它必須在編譯時(shí)進(jìn)行選擇。然而，如果在編譯時(shí)建立訪問計(jì)劃，變量的值還是未知的，因而無法作為索引選擇的輸入項(xiàng)。

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

select id from t with(index(索引名)) where num=@num

8. 應(yīng)避免WHERE 表達(dá)式操作/對(duì)字段進(jìn)行函數(shù)操作

任何對(duì)列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫(kù)函數(shù)、計(jì)算表達(dá)式等等， 應(yīng)盡量避免在 WHERE 子句中對(duì)字段進(jìn)行表達(dá)式操作，應(yīng)盡量避免在 WHERE 子句中對(duì)字段進(jìn)行函數(shù)操作。

如：

select id from t where num/5=100
應(yīng)改為:
select id from t where num=100*5

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

select id from t where substring(name,1,3)=‘a(chǎn)bc'

select id from t where datediff(day,createdate,‘2022-11-30')=0

應(yīng)改為:

select id from t where name like ‘a(chǎn)bc%'

select id from t where createdate>=‘2022-11-30' and createdate<‘2022-12-1'

9. 用 EXISTS 代替 IN 是一個(gè)好的選擇

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

select num from a where num in(select num from b)
用下面的語(yǔ)句替換：
select num from a where exists(select 1 from b where num=a.num)

10. 查詢SQL盡量不要使用select *，而是具體字段

最好不要使用返回所有：select * from t ，用具體的字段列表代替 “*”，不要返回用不到的任何字段。select *的弊端：

（1）增加很多不必要的消耗，比如CPU、IO、內(nèi)存、網(wǎng)絡(luò)帶寬；

（2）增加了使用覆蓋索引的可能性；

（3）增加了回表的可能性；

（4）當(dāng)表結(jié)構(gòu)發(fā)生變化時(shí)，前端也需要更改；

（5）查詢效率低；

到此這篇關(guān)于常見的十種SQL語(yǔ)句性能優(yōu)化策略詳解的文章就介紹到這了,更多相關(guān)SQL語(yǔ)句性能優(yōu)化策略內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論