最佳實(shí)踐

使用InnoDB存儲引擎

InnoDB引擎已經(jīng)在多方面超越了MyIsam引擎，沒有特殊需求的情況下建議選擇InnoDB引擎邑闺。

讓InnoDB使用全部內(nèi)存

innodb_buffer_pool_size參數(shù)指定了 InnoDB 可以使用的內(nèi)存總量。
建議設(shè)置為物理內(nèi)存的 80%，因?yàn)橐o操作系統(tǒng)留有空間。
如果你的內(nèi)存是 32GB法褥，可以設(shè)置為大約 25GB

innodb_buffer_pool_size = 25600M

注意：
（1）如果值小于1GB，說明真的應(yīng)該升級服務(wù)器了
（2）如果內(nèi)存特別大酬屉，例如200gb半等，就不必給操作系統(tǒng)留 20% 了，因?yàn)镺S用不了 40gb呐萨。

讓InnoDB多實(shí)例

innodb_buffer_pool_size的值大于 1G時(shí)杀饵，innodb_buffer_pool_instances會把 InnoDB 的緩存池劃分成多個(gè)實(shí)例。
多個(gè)緩沖池的好處：
多個(gè)線程同時(shí)訪問緩沖池時(shí)可能會遇到瓶頸谬擦，而多個(gè)緩沖池則可以最小化這個(gè)沖突
官方建議的 buffer 數(shù)量：每個(gè) buffer pool 實(shí)例至少要 1G
例如內(nèi)存為 32GB切距，innodb_buffer_pool_size為 25GB，那么合適的方案就是 25600M / 24 = 1.06GB
innodb_buffer_pool_instances = 24

加大max_length_for_sort_data參數(shù)的設(shè)置

在MySQL中惨远，排序算法分為兩種谜悟，一是只加載排序字段到內(nèi)存，排序完成后再到表中取其他字段北秽，二是加載所有需要的字段到內(nèi)存葡幸，顯然第二種節(jié)省了IO操作，所以更快贺氓。決定使用哪種算法是通過參數(shù)max_length_for_sort_data來決定的蔚叨，當(dāng)所有返回字段的最大長度小于這個(gè)參數(shù)值時(shí)，MySQL就會選擇第二種算法辙培，反之使用第一種蔑水。所以，如果有充足的內(nèi)存讓MySQL存放須要返回的非排序字段扬蕊，就可以加大這個(gè)參數(shù)的值來讓MySQL選擇第二種排序算法搀别。
當(dāng)內(nèi)存不是很充裕時(shí)，不能簡單地通過強(qiáng)行加大上面的參數(shù)來強(qiáng)迫MySQL去使用高效算法尾抑，否則可能會造成MySQL不得不將數(shù)據(jù)分成很多段歇父，然后進(jìn)行排序，這樣可能會得不償失蛮穿，此時(shí)就須要去掉不必要的返回字段庶骄，讓返回結(jié)果長度適應(yīng)max_length_for_sort_data參數(shù)的限制。

增大sort_buffer_size參數(shù)設(shè)置

增大sort_buffer_size并不是為了讓 MySQL選擇第二種排序算法践磅，而是為了讓MySQL盡量減少在排序過程中對須要排序的數(shù)據(jù)進(jìn)行分段，因?yàn)榉侄螘斐蒑ySQL不得不使用臨時(shí)表來進(jìn)行交換排序灸异。

打開查詢緩存

充分利用Mysql的查詢緩存機(jī)制府适，業(yè)務(wù)中很多SQL都會重復(fù)執(zhí)行的羔飞，當(dāng)然現(xiàn)在很多數(shù)據(jù)層框架中也有緩存功能，但數(shù)據(jù)層框架中的緩存屬于應(yīng)用級別的檐春，在數(shù)據(jù)被外部更新時(shí)會導(dǎo)致緩存數(shù)據(jù)過期問題逻淌。

案例

發(fā)現(xiàn)網(wǎng)站頁面打開非常慢，對處理過程簡單記錄了一下疟暖。

找問題

首先登錄服務(wù)器使用 top 查看當(dāng)前進(jìn)程信息卡儒，發(fā)現(xiàn)排名第一的是 mysql，占用 cpu 達(dá)到了 100% 以上俐巴，這就明確了是 mysql 的問題骨望。
登錄 mysql，使用 show processlist 查看下當(dāng)前執(zhí)行狀態(tài)欣舵，發(fā)現(xiàn)了大量 LOCK 操作擎鸠，也有多個(gè) Copying to tmp table 的操作，說明有 sql 出現(xiàn)了問題缘圈，操作過于復(fù)雜劣光，對臨時(shí)表使用頻繁，把其他操作阻塞了糟把。

解決思路

找到了問題后绢涡，把處理方向確定為檢查和修改配置、sql優(yōu)化遣疯。

修改mysql配置

臨時(shí)表
既然涉及了到了臨時(shí)表垂寥，就先查看下目前臨時(shí)表的信息
查看臨時(shí)表的使用狀態(tài)

show global status like 'created_tmp%';

發(fā)現(xiàn) created_tmp_disk_tables 值過高，需要增加此值另锋。
再看一下現(xiàn)在臨時(shí)表的大小

show variables like '%tmp_table_size%';

在現(xiàn)在值的基礎(chǔ)上增加一些滞项，重新設(shè)置臨時(shí)表大小
線程緩存數(shù)
看當(dāng)前線程情況

show global status like 'Thread%';

發(fā)現(xiàn) threads_created 的值過大，表明MySQL服務(wù)器一直在創(chuàng)建線程
查看當(dāng)前值

show variables like 'thread_cache_size';

此參數(shù)需要調(diào)高
打開表數(shù)量
查看打開表的情況

show global status like 'open%tables%';

發(fā)現(xiàn) opened_tables 數(shù)量過大夭坪，說明 table_cache 的值可能太小文判。
查看當(dāng)前值

show variables like 'table_cache';

此參數(shù)需要調(diào)高
最大連接數(shù)
查看當(dāng)前允許的最大連接數(shù)

show variables like 'max_connections';

查看服務(wù)器連接數(shù)的峰值

show global status like 'Max_used_connections';

峰值還沒到最大限制，不需要修改
join buffer 和 sort buffer
查看現(xiàn)有值

SELECT @@sort_buffer_size;
SELECT @@join_buffer_size;

是默認(rèn)值室梅，需要修改
修改配置
確定了要修改的參數(shù)后戏仓，修改 my.cnf ，例如

table_cache = 64
sort_buffer_size = 8M
join_buffer_size = 4M
thread_cache_size = 300
thread_concurrency = 8
tmp_table_size = 246M

sql優(yōu)化

從 show processlist 結(jié)果集中找出主要的復(fù)雜語句亡鼠，對其進(jìn)行 explain 和 profile 分析赏殃，進(jìn)行索引優(yōu)化，把復(fù)雜的sql 根據(jù)業(yè)務(wù)拆分為多個(gè)小的sql间涵。