為什么要優(yōu)化慢sql ？

慢sql會(huì)長(zhǎng)時(shí)間占用 數(shù)據(jù)庫(kù)連接數(shù)，如果項(xiàng)目中有大量的慢sql，那么可用的數(shù)據(jù)庫(kù)連接數(shù)就會(huì)變少，進(jìn)而會(huì)影響業(yè)務(wù)。

慢sql優(yōu)化

• 優(yōu)化慢sql，最常見的就是添加索引。
• 查詢語(yǔ)句中不要使用select *
• 盡量減少子查詢，使用關(guān)聯(lián)查詢（left join,right join,inner join）替代
• 減少使用IN或者NOT IN ,使用exists，not exists或者關(guān)聯(lián)查詢語(yǔ)句替代
• or 的查詢盡量用 union或者union all 代替(在確認(rèn)沒(méi)有重復(fù)數(shù)據(jù)或者不用剔除重復(fù)數(shù)據(jù)時(shí)，union all會(huì)更好)
• 應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。
• 應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：
  select id from t where num is null
  可以在num上設(shè)置默認(rèn)值0，確保表中num列沒(méi)有null值，然后這樣查詢：
  select id from t where num=0
• 對(duì)于like查詢，”%”不要放在前面。

    SELECT * FROM  t_order  WHERE uname LIKE '編程%' -- 走索引 SELECT * FROM  t_order  WHERE uname LIKE  '%編程%' -- 不走索引

可以用instr代替左模糊。

instr(title,'name')>0  相當(dāng)于  title like '%name%' 
instr(title,'name')=1  相當(dāng)于  title like 'name%' 
instr(title,'name')=0  相當(dāng)于  title not like '%name%' 

EXPLAIN查看執(zhí)行計(jì)劃

EXPLAIN可以查看執(zhí)行計(jì)劃，對(duì) SELECT 語(yǔ)句進(jìn)行分析，并輸出 SELECT 執(zhí)行的詳細(xì)信息，方便針對(duì)性地優(yōu)化。
查詢結(jié)果的字段如下：

    select_type: SELECT 查詢的類型。包括SIMPLE、PRIMARY、UNION、UNION RESULT等table: 查詢的是哪個(gè)表partitions: 匹配的分區(qū)type(重要): 類型。type值為all，表示全表掃描。type值為const，說(shuō)明使用了主鍵索引。

system: 表中只有一條數(shù)據(jù). 這個(gè)類型是特殊的 const 類型.

const: 針對(duì)主鍵或唯一索引的等值查詢掃描, 最多只返回一行數(shù)據(jù). const 查詢速度非常快, 因?yàn)樗鼉H僅讀取一次即可.

eq_ref: 此類型通常出現(xiàn)在多表的 join 查詢, 表示對(duì)于前表的每一個(gè)結(jié)果, 都只能匹配到后表的一行結(jié)果. 并且查詢的比較操作通常是 =, 查詢效率較高.

ref: 此類型通常出現(xiàn)在多表的 join 查詢, 針對(duì)于非唯一或非主鍵索引, 或者是使用了 最左前綴 規(guī)則索引的查詢.

range: 表示使用索引范圍查詢, 通過(guò)索引字段范圍獲取表中部分?jǐn)?shù)據(jù)記錄. 這個(gè)類型通常出現(xiàn)在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
當(dāng) type 是 range 時(shí), 那么 EXPLAIN 輸出的 ref 字段為 NULL, 并且 key_len 字段是此次查詢中使用到的索引的最長(zhǎng)的那個(gè).

index: 表示全索引掃描(full index scan), 和 ALL 類型類似, 只不過(guò) ALL 類型是全表掃描, 而 index 類型則僅僅掃描所有的索引, 而不掃描數(shù)據(jù).
index 類型通常出現(xiàn)在: 所要查詢的數(shù)據(jù)直接在索引樹中就可以獲取到, 而不需要掃描數(shù)據(jù). 當(dāng)是這種情況時(shí), extra 字段 會(huì)顯示 Using index.

all: 表示全表掃描, 這個(gè)類型的查詢是性能最差的查詢之一. 通常來(lái)說(shuō), 我們的查詢不應(yīng)該出現(xiàn) ALL 類型的查詢, 因?yàn)檫@樣的查詢?cè)跀?shù)據(jù)量大的情況下, 對(duì)數(shù)據(jù)庫(kù)的性能是巨大的災(zāi)難. 如一個(gè)查詢是 ALL 類型查詢, 那么一般來(lái)說(shuō)可以對(duì)相應(yīng)的字段添加索引來(lái)避免.

不同的 type 類型的性能關(guān)系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system。

    possible_keys: 此次查詢中可能選用的索引key(重要): 此次查詢中確切使用到的索引.ref: 哪個(gè)字段或常數(shù)與 key 一起被使用rows(重要): 顯示此查詢一共掃描了多少行. 這個(gè)是一個(gè)估計(jì)值.filtered: 表示此查詢條件所過(guò)濾的數(shù)據(jù)的百分比extra: 額外信息,比如using index表示使用覆蓋索引,using where表示在存儲(chǔ)引擎之后進(jìn)行過(guò)濾,using temporary表示使用臨時(shí)表,using filesort表示對(duì)結(jié)果進(jìn)行外部排序。

技術(shù)角度

• 用EXPLAIN 查看執(zhí)行計(jì)劃。
• 拆解sql，復(fù)雜的sql拆成多條sql，再用 java代碼拼接。
• 復(fù)雜的sql，在上線之前，先去生產(chǎn)環(huán)境 EXPLAIN 一下執(zhí)行計(jì)劃。

業(yè)務(wù)角度

• 與產(chǎn)品/業(yè)務(wù)討論，查詢時(shí)，能否限制時(shí)間范圍，比如只查七天、只查一個(gè)月。
• 與產(chǎn)品/業(yè)務(wù)討論，能否清理無(wú)用的舊數(shù)據(jù)，只保留最近三個(gè)月、最近一年的數(shù)據(jù)。