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

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

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

2. where中使用默認值代替null

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

為啥建議where中使用默認值代替null，四個原因：

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

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

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

4. 慎用 OR 來連接條件

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

5. 慎用 IN 和 NOT IN

IN 和 NOT IN 也要慎用，否則會導(dǎo)致全表掃描。對于連續(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 ‘…%’，是會使用索引的；
2. 左模糊like ‘%…’無法直接使用索引，但可以利用reverse + function index的形式，變化成 like ‘…%’；
3. 全模糊查詢是無法優(yōu)化的，一定要使用的話建議使用搜索引擎，比如 ElasticSearch。

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

如下面語句將進行全表掃描：

select id from t where num=@num

因為SQL只有在運行時才會解析局部變量，但優(yōu)化程序不能將訪問計劃的選擇推 遲到 運行時；

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

所以， 可以改為強制查詢使用索引：

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

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

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

如：

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

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

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 是一個好的選擇

很多時候用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)

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

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

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

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

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

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

（5）查詢效率低；

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

最新評論