對于需要排序的字段使用索引

當(dāng)查詢結(jié)果需要order by的時候，可以在order by 的字段加上索引，因為索引已經(jīng)排列好順序了，

所以可以更快的完成排序，而不需要每次對查詢結(jié)果進(jìn)行排序，耗費大量內(nèi)存和時間。

盡量使用union all 而不是union

除非確實需要服務(wù)器消除重復(fù)的行，否則一定要使用union all，因此沒有all關(guān)鍵字，

mysql會在查詢的時候給臨時表加上distinct的關(guān)鍵字，這個操作的代價很高。

exists 和 join如何選擇

join

需要多張表進(jìn)行連接，并且需要查詢的字段不是來自一張表的，比如a.name,b.dept 這種情況需要使用join

exists

要查詢的字段都在a表，但是有一個很復(fù)雜的條件，可以使用exists子句來描述后面復(fù)雜的查詢條件

現(xiàn)實情況中，exists用的比較少，大家都想不起來用哈哈哈哈

and優(yōu)先級高于or

比如一個查詢，需要篩選名字為AAA，并且年齡為20或者21的數(shù)據(jù)，

sql語句如下：

select * from tab_a where name = 'AAA' and age = 20 or age = 21;

查詢結(jié)果會變?yōu)?/p>

name = ‘AAA’ and age = 20的數(shù)據(jù)和

age=21的數(shù)據(jù)，查詢結(jié)果錯誤。

需要改造sql語句如下：

select * from tab_a where name = 'AAA' and (age=20 or age=21)

盡量不要join超過三張表

單表索引控制在5個以內(nèi)

索引越多，b+樹越大，會影響插入、刪除效率

組合索引的字段不能超過5個

key(a,b,c,d,e)不宜過多，由于最左匹配原則，如果查詢的列在右邊，那個查詢的時候需要補充前幾列的條件

并且，索引的長度過長會使b+樹偏大，消耗存儲空間

limit優(yōu)化

limit語法如下：

從第一行開始返回，返回前n條數(shù)據(jù)

select * from tab_a order by a limit n

從第m行數(shù)據(jù)開始返回，返回后面的n條數(shù)據(jù)

select * from tab_a order by a limit m,n;

例如，如果要查詢tab_a表的第3頁，每頁10條數(shù)據(jù)，可以如下進(jìn)行：

select * from tab_a order by a limit 20,10;

limit相當(dāng)于是一個指針，在遍歷完前面的數(shù)據(jù)之后，找到需要的數(shù)據(jù)，再返回給用戶，如果數(shù)據(jù)量非常大時，例如limit(25000,20)，會掃描全表，limit效率會變得非常低，

這種情況的調(diào)優(yōu)方式如下：

使用索引列子查詢進(jìn)行調(diào)優(yōu)

調(diào)優(yōu)前：

select film_id,description from film order by title limit 50,5

調(diào)優(yōu)后：

explain select film.film_id,film.description from film inner join (select film_id from film order by title limit 1500,5) as lim using(film_id);

調(diào)優(yōu)后，避免了limit指針掃描全表獲取數(shù)據(jù)，而是先使用主鍵id進(jìn)行查詢，id在查詢時，

只會使用b+樹去訪問到數(shù)據(jù)，不需要對整張表進(jìn)行io，

速度更快，并且不需要回表，再將查詢結(jié)果和主表做join，返回數(shù)據(jù)。

避免向數(shù)據(jù)庫查詢不必要的數(shù)據(jù)

數(shù)據(jù)庫服務(wù)層會查詢所有的結(jié)果，形成結(jié)果集，獲取前面n條數(shù)據(jù)后關(guān)閉結(jié)果集，為了避免形成不必要的結(jié)果集，可以使用limit提升速率例如：

select * from tab_a where id = 'sdasafdf676d8' limit 1;

避免使用select *

如果需要不斷的重復(fù)查詢，使用redis進(jìn)行緩存

盡量使用關(guān)聯(lián)代替子查詢

因為子查詢在執(zhí)行的過程中會將子查詢結(jié)果放到臨時表，增加了io，對內(nèi)存的開銷較大，而join可以利用join buffer進(jìn)行快速匹配，運行速度較快。

group by, distinct, order by 時推薦使用索引列

使用自定義變量

什么是自定義變量？

set @one： = 1；
set @current_actor: = select actor_id from actor order by last_update desc limit 1 ;
set @last_week :=current_date-interval 1 week;
-- 使用如下
select count(1) from actor where last_update < @last_week;
select * from message where actor_id = @current_actor;

自定義變量使用場景：

優(yōu)化排序

set @rownum：= 0；
select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論