一、優(yōu)化數(shù)據(jù)訪問

1.確認(rèn)應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù)。通常是訪問的行太多，但也可能是訪問的列太多
2.確認(rèn)MySQL服務(wù)器層是否在分析大量超過需要的數(shù)據(jù)行。
優(yōu)化數(shù)據(jù)的訪問（減少無效的數(shù)據(jù)檢索）

二、重構(gòu)查詢方式

1.一個(gè)復(fù)雜查詢還是多個(gè)簡單查詢
2.切分查詢
3.分解關(guān)聯(lián)查詢
重構(gòu)查詢的方式（將大而復(fù)雜的SQL進(jìn)行重構(gòu)為小而簡單的SQL）

三、查詢緩存

查詢緩存是要付出代價(jià)的，對查詢的性能有很大影響，但如果發(fā)現(xiàn)付出的代價(jià)大于帶來的好處，可以考慮關(guān)閉查詢緩存。默認(rèn)是關(guān)閉的
當(dāng)開啟查詢緩存后，所有以SEL開頭的語句都會(huì)判斷查詢緩存中是否有緩存結(jié)果。如果沒有再進(jìn)行SQL解析、執(zhí)行。 當(dāng)返回結(jié)果時(shí)會(huì)判斷是否可以進(jìn)行緩存，如果有特殊函數(shù)則不進(jìn)行緩存結(jié)果。

查詢緩存的消耗：

1.檢查緩存是否命中
2.對查詢結(jié)果進(jìn)行緩存
3.修改數(shù)據(jù)使得緩存失效
查詢緩存結(jié)果是存儲(chǔ)在內(nèi)存中的，通過幾個(gè)參數(shù)進(jìn)行分配內(nèi)存資源
Query_cache_size:查詢緩存內(nèi)存空間總大小
Query_cache_min_res_unit:內(nèi)存最小單位
內(nèi)存的分配是有MySQL服務(wù)器進(jìn)行管理的，系統(tǒng)分配內(nèi)存的操作只在初始化的時(shí)候進(jìn)行分配一次。

查詢緩存會(huì)導(dǎo)致碎片的情況：

1.多個(gè)查詢同時(shí)進(jìn)行結(jié)果緩存時(shí)，兩個(gè)查詢結(jié)果之間回收掉未使用的內(nèi)存空間。
2.某個(gè)緩存失效時(shí)
實(shí)際情況很難評估查詢緩存是否真正能夠帶來性能上的提升，而且對于性能瓶頸不在查詢上的系統(tǒng)（如：網(wǎng)絡(luò)傳輸瓶頸），查詢緩存作用很小。

查詢緩存是否能夠發(fā)揮作用：

1.理論上可以觀察打開你會(huì)關(guān)閉查詢緩存時(shí)，系統(tǒng)的效率來決定是否開啟。
大部分情況下，全局平均效率不能反映真實(shí)情況。
2.查詢緩存命中率
計(jì)算公式：Qcache_hits/(Qcache_hits+Com_select)
很難判斷命中率多高是合適的。如果查詢緩存能夠帶來效率提升，大于查詢緩存帶來的資源消耗，30%的命中率也是對系統(tǒng)有很好的性能提升。
3.命中寫入比例(Qcache_hits和Qcache_inserts的比值)
通常最少大于3:1，最好能打到10:1

查詢緩存配置：

query_cache_type：OFF/ON/DEMAND。DEMAND表示只有在查詢語句中明確寫明SQL_CACHE的語句才放入查詢緩存。
query_cache_size：總內(nèi)存空空間，必須是1024的整數(shù)倍
query_cache_min_res_unit：分配內(nèi)存塊的最小單位
query_cache_limit：能夠緩存的最大查詢結(jié)果。如果大于這個(gè)值，則不進(jìn)行緩存。如果明確知道查詢結(jié)果大于這個(gè)值，可以使用SQL_NO_CACHE，減少資源消耗
query_cache_wlock_invalidate：如果某個(gè)數(shù)據(jù)表被其他連接鎖住，是否仍然從查詢緩存中返回結(jié)果。

減少碎片：

無法完全避免碎片的產(chǎn)生，但是可以選擇合query_cache_min_res_unit，以減少碎片的大小。但是不能太小，太小則要進(jìn)行更頻繁的內(nèi)存塊申請操作。
合適大小計(jì)算公式：內(nèi)存實(shí)際消耗（query_cache_size-Qcache_free_memory）/Qcache_queries_in_cache（緩存?zhèn)€數(shù)），計(jì)算平均每個(gè)查詢緩存的大小

分析和配置查詢緩存的流程圖：

通用查詢緩存優(yōu)化：

1.用多個(gè)小表替代一個(gè)大表對查詢緩存有好處。
2.批量寫入時(shí)只需要做一次緩存失效，相比單條寫入效率更好。
3.因?yàn)榫彺婵臻g太大，在過期操作的時(shí)候可能會(huì)導(dǎo)致服務(wù)器僵死。
4.無法在數(shù)據(jù)庫或者表級別控制查詢緩存，但是可以通過SQL_CACHE和SQL_NO_CACHE來控制某個(gè)SELECT語句是否需要進(jìn)行緩存。
5.對寫密集型應(yīng)用來說，直接禁用查詢緩存性能更好。
6.如果不想所有查詢都進(jìn)入緩存，可以設(shè)置query_cache_type為DEMAND。

四、查詢優(yōu)化器提示

HIGH_PRIORITY 和 LOW_PRIORITY：

告訴MySQL，當(dāng)多個(gè)語句同時(shí)訪問某一個(gè)表的時(shí)候，哪些語句的優(yōu)先級相對高一些、哪些相對低一些。如果是HIGH_PRIORITY則會(huì)調(diào)整到所有正在等待表鎖 以便修改數(shù)據(jù)的語句之前。針對有表鎖的引擎有效

DELAYED：

立即響應(yīng)給客戶端，只對INSER和REPLACE有效。并非所有引擎都支持，而且會(huì)導(dǎo)致函數(shù)LAST_INSERT_ID()無效。

STRAIGHT_JOIN：

放在SELECT關(guān)鍵字后面，用于固定查詢中表的關(guān)聯(lián)順序按照語句的順序進(jìn)行。

SQL_SMALL_RESULT和SQL_BIG_RESULT：

對SELECT語句有效，告訴MySQL優(yōu)化去對GROUP BY和DISTINCT查詢?nèi)绾问褂门R時(shí)表排序，SQL_SMALL_RESULT表示結(jié)果集很小，在內(nèi)存的臨時(shí)表排序。反之則很大，用磁盤臨時(shí)表排序

SQL_BUFFER_RESULT：

是一種緩存策略，將查詢結(jié)果放到一個(gè)臨時(shí)表，消耗服務(wù)器內(nèi)存。會(huì)盡快的釋放表鎖

SQL_CACHE和SQL_NO_CACHE：

查詢結(jié)果是否進(jìn)行緩存

FOR UPDATE：

對查詢加鎖提示 select * from test for update;

USE INDEX\IGNORE INDEX和FORCE INDEX：

USE INDEX ：指定使用的索引
IGNORE INDEX ：忽略指定索引
FORCE INDEX ：與use index基本相同，不過會(huì)告訴優(yōu)化器，全表掃描的代價(jià)高于索引掃描。

五、優(yōu)化特定類型的查詢

1.Count()

使用近似值：執(zhí)行計(jì)劃中的值
匯總表

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

確保on或者USING子句中的列上有索引。
在創(chuàng)建索引的時(shí)候要考慮到關(guān)聯(lián)的順序。
關(guān)聯(lián)查詢也可以使用組合索引

3.Group BY

如果需要對關(guān)聯(lián)查詢做分組，那通常采用查找表的標(biāo)識(shí)列分組的效率會(huì)比其他列更高。

4.Limit

盡可能的使用索引覆蓋掃描，而不是查詢所有的列，然后根據(jù)需要做一次關(guān)聯(lián)操作再返回所需的列。
"延遲關(guān)聯(lián)"將大大提升查詢效率，使MySQL掃描盡可能少的頁，獲取需要訪問的記錄后再根據(jù)關(guān)聯(lián)列回原表查詢需要的所有列。

5.UNION

UNION與UNION ALL的差別，union all不再創(chuàng)建臨時(shí)表，這樣在聯(lián)合查詢時(shí)會(huì)減少I/O開銷