SQL語句最佳化策略介紹

王林轉載: 2020-01-28 22:05:332279瀏覽

SQL語句最佳化策略介紹

1、避免全表掃描

對查詢進行最佳化，應盡量避免全表掃描，首先應考慮在where 及order by涉及的列上建立索引。

2、避免判斷null 值

應盡量避免在where 子句中對欄位進行null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null

可以在num 上設定預設值0，確保表中num 列沒有null 值，然後這樣查詢：

select id from t where num=0

（免費學習影片教學建議： mysql影片教學）

3、避免不等值判斷

#應盡量避免在where 子句中使用!=或a8093152e673feb7aba1828c43532094操作符，否則引擎將放棄使用索引而進行全表掃描。

4、避免使用or 邏輯

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

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 也要慎用，否則會導致全表掃描，如：

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

此時外層查詢會全表掃描，不使用索引。可以修改為：

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

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

6、注意模糊查詢

下面的查詢也會導致全表掃描：

select id from t where name like &#39;%abc%&#39;

模糊查詢如果是必要條件時，可以使用select id from t where name like 'abc%'來實作模糊查詢，此時索引將會被使用。如果頭匹配是必要邏輯，建議使用全文搜尋引擎（Elastic search、Lucene、Solr 等）。

7、避免查詢條件中欄位計算

應盡量避免在where 子句中對欄位進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where num/2=100

應改為:

select id from t where num=100*2

8、避免查詢條件中對欄位進行函數運算

應盡量避免在where 子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where substring(name,1,3)=&#39;abc&#39;--name 以abc 开头的id

要改為:

select id from t where name like &#39;abc%&#39;

9、WHERE 子句「=」左邊注意點

不要在where 子句中的「=」左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。

10、組合索引使用

在使用索引字段作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應盡可能的讓欄位順序與索引順序相符。

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

不要寫一些沒有意義的查詢，如需要產生一個空表結構：

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

這類程式碼不會回傳任何結果集，但是會消耗系統資源的，應改成這樣：

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 是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL 查询可能不会去利用索引，如一表中有字段sex，male、female 几乎各一半，那么即使在sex 上建了索引也对查询效率起不了作用。

14、表格字段类型选择

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。尽可能的使用varchar 代替char ，因为首先可变长度字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

15、查询语法中的字段

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

16、索引无关优化

不使用*、尽量不使用union，union all 等关键字、尽量不使用or 关键字、尽量使用等值判断。表连接建议不超过5 个。如果超过5 个，则考虑表格的设计。（互联网应用中）表连接方式使用外联优于内联。外连接有基础数据存在。

如：A left join B,基础数据是A。A inner join B，没有基础数据的，先使用笛卡尔积完成全连接，在根据连接条件得到内连接结果集。

大数据量级的表格做分页查询时，如果页码数量过大，则使用子查询配合完成分页逻辑。

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

SQL語句最佳化策略介紹

相關文章