查詢緩存

1.可以使用如下的語句來判斷MySQL是否開啟了查詢緩存功能：

show variables like '%query_cache%';

2.如果想查看MySQL是否是讀取的緩存，可以使用如下的語句：

show status like 'qcache_hits';

值得注意的是，如果表的數(shù)據(jù)存在更新的話，和該表相關(guān)的所有緩存都會被清空。如果當(dāng)前的查詢正好命中緩存，那么此次查詢就會省略之后的所有操作，并直接從緩存中返回數(shù)據(jù)。

3.查詢執(zhí)行計劃

MySQL服務(wù)器層對SQL語句進(jìn)行一系列優(yōu)化之后，會生成一顆查詢指令樹，然后執(zhí)行引擎會利用api調(diào)用完成查詢指令樹并返回結(jié)果。可以使用如下的步驟來查看，MySQL優(yōu)化之后的查詢：

1.explain extended select*from actor;
2.show warnings;

時間去哪了 (SQL執(zhí)行時間；SQL等待時間；然后通過分析&推理確認(rèn)為什么時間去那里了。)

是什么導(dǎo)致MySQL查詢變慢了？

對于MySQL，最簡單的衡量查詢開銷的三個指標(biāo)如下：
響應(yīng)時間
掃描的行數(shù)
返回的行數(shù)

沒有哪個指標(biāo)能夠完美地衡量查詢的開銷，但它們大致反映了MySQL在內(nèi)部執(zhí)行查詢時需要訪問多少數(shù)據(jù)，并可以大概推算出查詢運(yùn)行的時間。
查詢慢的原因基本都是：我們的不合理操作導(dǎo)致查詢的多余數(shù)據(jù)太多了。
常見原因有以下：
1.查詢不需要的記錄。
2.多表關(guān)聯(lián)時返回全部列
3.總是取出全部列

常用優(yōu)化技巧

1.用索引

最簡單且見效最快的方式就是給你的條件加索引（主鍵索引，普通索引，唯一索引等）。注：索引是要另開辟一塊空間存儲的，所以不能不要錢滴都加索引。

2.關(guān)聯(lián)子查詢

MySQL的子查詢實現(xiàn)是非常糟糕的。比如下面的

SELECT * FROM book WHERE book_id IN (SELECT book_id FROM author WHERE author_id = 1)

MySQL對IN()列表中的選項有專門的優(yōu)化策略，一般會認(rèn)為MySQL會先執(zhí)行子查詢返回所有包含author_id 為1的book_id。
或許你想MySQL的運(yùn)行時這樣子的：

SELECT GROUP_CONCAT(book_id) FROM author WHERE author_id = 1
SELECT * FROM book WHERE book_id IN (1,21,3,45,656,766,213,123)

但是，MySQL會將相關(guān)的外層表壓到子查詢中的，就是下面的樣子：

SELECT * FROM book WHERE EXISTS 
(SELECT * FROM author WHERE author_id = 1 AND book.book_id = author.book_id)

原因：因為子查詢需要book_id ，所以MySQL認(rèn)為無法先執(zhí)行這個子查詢，而是先對book 進(jìn)行全表掃描，然后再根據(jù)book_id進(jìn)行子查詢。具體可以EXPLAIN該SQL進(jìn)行分析。

建議：
1.使用左外連接（LEFT OUTER JOIN）代替子查詢。

SELECT * from book LEFT OUTER JOIN author USING(book_id) WHERE author.author_id = 1

影響因素：還有數(shù)據(jù)表放的位置等，具體應(yīng)用場景就只能你自己explain該語句對比哪種性能比較好點

2.確保ON或者USING子句的列上有索引
在創(chuàng)建索引的時候就要考慮到關(guān)聯(lián)的順序。

3.UNION使用
如果希望UNION的各個子句能根據(jù)LIMIT只取部分結(jié)果集，或者希望能夠先排好序再合并結(jié)果集的話。
第一個例子：會將author 表和user 表兩個表都存放到一個臨時表中，再從臨時表中取出前20條。

(SELECT first_name FROM author ORDER BY last_name)
UNION ALL
(SELECT first_name FROM user ORDER BY last_name)
LIMIT 20

對比上面的這樣子，就有很大的改善了。

(SELECT first_name FROM author ORDER BY last_name LIMIT 20)
UNION ALL
(SELECT first_name FROM user ORDER BY last_name LIMIT 20)
LIMIT 20

4.COUNT()查詢
比如如果想統(tǒng)計文章id大于25的數(shù)量，可以如下：

EXPLAIN SELECT COUNT(*) FROM article WHERE id >25

另外一種思路：可以先查詢文章總數(shù)，減去小于等于25的數(shù)量。僅僅提供思路，具體效果還是你具體情況，自己比較，擇優(yōu)選擇

EXPLAIN SELECT (SELECT COUNT(*) FROM article) - COUNT(*) FROM article WHERE id <=25

題外話：
如果需要區(qū)分不同顏色的商品數(shù)量時，可以如下做法：

seelct count(color = 'blue' OR NULL) as blue,COUNT(color = 'red' OR NULL) AS RED FROM items

5.GROUP BY和DISTINCT
它們的優(yōu)化最有效的方法就是用索引來。
但是GROUP BY有時候用得不對，索引是會失效的。
比如：把兩個單獨的索引合并成一個組合索引，即把where條件字段的索引和group by的分組字段索引組合成一個。

6.limit分頁
下面這條查詢，非常常見。

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

但是如果這個表很大的時候，那么這個50變成100654這樣子的話，這里MySQL就要掃描100654+5條數(shù)據(jù)，然后丟棄100654條，僅僅去最后5條。
一種思路：

select film_id,description from film inner join (select film_id from film order by title limit 50,5) as lim USING(film_id);

該思路是通過延遲關(guān)聯(lián)將大大提升查詢效率，它讓MySQL掃描盡可能少的頁面。獲取需要訪問的記錄后，再更加關(guān)聯(lián)列會原表查詢所需要的所有列。以上并不一定符合你，具體還需explain對比擇優(yōu)使用。

小結(jié)：
總體來說都是圍繞著盡量少全表掃描，盡量使用索引進(jìn)行優(yōu)化。
最后往往是要自己在實際場景多用explain分析是否有更好的sql解決方案。

索引會失效的場景

1.隱式轉(zhuǎn)換導(dǎo)致索引失效.

這一點應(yīng)當(dāng)引起重視.也是開發(fā)中經(jīng)常會犯的錯誤. 由于表的字段tu_mdn定義為varchar2(20),但在查詢時把該字段作為number類型以where條件傳給Oracle,這樣會導(dǎo)致索引失效.

錯誤的例子：select * from test where tu_mdn=13333333333;
正確的例子：select * from test where tu_mdn='13333333333';

2.對索引列進(jìn)行運(yùn)算導(dǎo)致索引失效

所指的對索引列進(jìn)行運(yùn)算*包括(+，-，，/，! 等)

錯誤的例子：select * from test where id-1=9;
正確的例子：select * from test where id=10;

3. 使用內(nèi)部函數(shù)導(dǎo)致索引失效.

對于這樣情況應(yīng)當(dāng)創(chuàng)建基于函數(shù)的索引.

// 錯誤的例子：
select * from test where round(id)=10; //說明，此時id的索引已經(jīng)不起作用了
 //正確的例子：首先建立函數(shù)索引
create index test_id_fbi_idx on test(round(id));
//然后 
select * from test where round(id)=10; 

4. 不要將空的變量值直接與比較運(yùn)算符（符號）比較。

如果變量可能為空，應(yīng)使用 IS NULL 或 IS NOT NULL 進(jìn)行比較，或者使用 ISNULL 函數(shù)。

5.不要在 SQL 代碼中使用雙引號。

因為字符常量使用單引號。如果沒有必要限定對象名稱，可以使用（非 ANSI SQL 標(biāo)準(zhǔn)）括號將名稱括起來。

6. 以下使用會使索引失效，應(yīng)避免使用

a. 使用 <> 、not in 、not exist、!=
b. like "%_" 百分號在前（可采用在建立索引時用reverse(columnName)這種方法處理）
c. 單獨引用復(fù)合索引里非第一位置的索引列.應(yīng)總是使用索引的第一個列，如果索引是建立在多個列上, 只有在它的第一個列被where子句引用時，優(yōu)化器才會選擇使用該索引。
d. 字符型字段為數(shù)字時在where條件里不添加引號.
e. 當(dāng)變量采用的是times變量，而表的字段采用的是date變量時.或相反情況。