mysql優(yōu)化

數(shù)據(jù)庫層面：

成都創(chuàng)新互聯(lián)公司長期為上千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團隊從業(yè)經(jīng)驗10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為于都企業(yè)提供專業(yè)的成都網(wǎng)站制作、成都網(wǎng)站建設(shè)、外貿(mào)營銷網(wǎng)站建設(shè)，于都網(wǎng)站改版等技術(shù)服務(wù)。擁有10年豐富建站經(jīng)驗和眾多成功案例,為您定制開發(fā)。

• 應(yīng)用系統(tǒng)層面優(yōu)化

1. SQL優(yōu)化

   SQL優(yōu)化一般通過分析慢查詢?nèi)罩緛碜ト￠L事務(wù)高消耗的sql，通過結(jié)合具體業(yè)務(wù)，對sql邏輯進行分析and精簡，or重寫sql。通過配置slow_query_log=1和log_queries_not_using_indexes=1啟動慢查詢?nèi)罩居涗浐陀涗浵聸]有使用索引的查詢,后者會讓慢查詢?nèi)罩疚募芸炫蛎?，需要定時對文件進行切割。分析慢日志的工具一般使用pt工具包的pt-query-digest或者MySQL自帶的mysqldumpslow，個人比較傾向用pt,分析出來的內(nèi)容比較詳細(xì)。需要注意pt-query-digest其實是一個perl腳本，如果慢日志文件較大（幾G多），需要大量消耗CPU資源。建議在業(yè)務(wù)低峰時候調(diào)度該工具。

2. 索引優(yōu)化

   索引是數(shù)據(jù)庫最為常見的對象?；旧?0%的sql性能問題都是沒有建索引or低效索引導(dǎo)致的。所以根據(jù)實際業(yè)務(wù)場景建合適的索引，能夠使得sql優(yōu)化事半功倍。

   索引的設(shè)計規(guī)則：選擇唯一性索引or主鍵；為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引，盡量建立復(fù)合索引而非單列索引；為常作為查詢條件的字段建立索引；限制索引的數(shù)目；盡量使用數(shù)據(jù)量少的索引；盡量使用前綴來索引；刪除不再使用或者很少使用的索引。

   創(chuàng)建索引的一些注意事項：

   （1）避免在where子句中使用!=或者<>操作符，否則引擎會放棄索引而進行全表掃描

   （2）避免在where子句中使用or來連接條件，考慮用union代替。

   （3）避免在where子句中對字段進行表達(dá)式操作或者函數(shù)操作

   （4）先應(yīng)考慮在 where 及 order by 涉及的列上建立索引

   （5）在使用索引字段作為條件時，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個字段作為條件 時才能保證系統(tǒng)使用該索引， 否則該索引將不被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致。即最左原則。

   （6）索引固然可以提高相應(yīng)的 select 的效率，但是也需要成本去維護索引，因此表的索引個數(shù)并非越多越好，一般不要超過7個。

   （7）如果使用到了臨時表，在最后將所有的臨時表顯式刪除時，先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長時間鎖定。

   （8）避免頻繁創(chuàng)建和刪除臨時表，以減少系統(tǒng)表資源的消耗。對于一次性事件， 最好使用導(dǎo)出表。

   （9）最好不要給數(shù)據(jù)庫留NULL，盡可能的使用 NOT NULL填充數(shù)據(jù)庫.備注、描述、評論之類的可以設(shè)置為 NULL，其他的，最好不要使用NULL。

   （10）對于varchar的字段創(chuàng)建索引，指定索引長度，避免創(chuàng)建全字段索引，可以通過count(distinct left(列名，索引長度))/count(*)計算區(qū)分度來確定索引長度。

   （11）多表關(guān)聯(lián)查詢的時候，必須保證關(guān)聯(lián)的字段有索引。而且字段的類型必須一致，避免由于隱式轉(zhuǎn)換導(dǎo)致索引失效。

3. 庫表優(yōu)化

   舉個例子：業(yè)務(wù)前期不注重表設(shè)計，導(dǎo)致日志、報文、圖片這類數(shù)據(jù)都通過表的方式存儲在數(shù)據(jù)庫。而這類數(shù)據(jù)一般是通過text/blob等類型的字段存儲，極易使得表容量暴增，而且很難去優(yōu)化這類表的查詢sql。需要做好庫表設(shè)計，對圖片、報文這類數(shù)據(jù)，改為通過MongoDB等NoSql數(shù)據(jù)庫進行存儲?；蛘邔⒈淼牟糠执笞侄芜M行拆分，單獨出來一個表，通過冗余部分字段，實現(xiàn)表與表之間的數(shù)據(jù)關(guān)聯(lián)（不建議通過mysql的外鍵約束實現(xiàn)關(guān)聯(lián)，因為在高并發(fā)的情況下，會出現(xiàn)大量行鎖影響數(shù)據(jù)庫性能，強烈建議通過應(yīng)用程序?qū)崿F(xiàn)數(shù)據(jù)關(guān)聯(lián)。）

   采用統(tǒng)一的字符集和校驗集，使用innodb引擎，表設(shè)計中采用與業(yè)務(wù)無關(guān)的自增ID列作為主鍵，減少存儲過程and自定義函數(shù)，盡量不用text/blob這類字段類型。

4. 表設(shè)計規(guī)范

   請參考附件 阿里巴巴Java開發(fā)手冊 和 58到家數(shù)據(jù)庫30條軍規(guī)解讀 （https://www.oschina.net/question/54100_2231325）

5. 數(shù)據(jù)庫對象優(yōu)化

• 內(nèi)存配置優(yōu)化

  innodb緩沖池設(shè)置：innodb_buffer_pool_size，一般為整機內(nèi)存的70%~80%

  緩沖池臟頁占比：innodb_max_dirty_pages_pct，默認(rèn)為75%，建議按照業(yè)務(wù)場景進行設(shè)置。

  強烈建議關(guān)閉query cache。通過配置文件設(shè)置query_cache_size = 0、query_cache_type = 0即可。

  redo log緩沖區(qū)設(shè)置：innodb_log_buffer_size，如果沒大事務(wù)，控制在8M-16M即可，生產(chǎn)環(huán)境目前配置到64M。

• IO配置優(yōu)化

  sync_binlog:

  sync_binlog=0，當(dāng)事務(wù)提交之后，MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤，而讓Filesystem自行決定什么時候來做同步，或者cache滿了之后才同步到磁盤。

  sync_binlog=n，當(dāng)每進行n次事務(wù)提交之后，MySQL將進行一次fsync之類的磁盤同步指令來將binlog_cache中的數(shù)據(jù)強制寫入磁盤。

  sync_relay_log:

  同sync_binlog參數(shù)功能一樣，只不過對象是relay log而不是binlog。

  innodb_flush_log_at_trx_commit:

  如果innodb_flush_log_at_trx_commit設(shè)置為0，log buffer將每秒一次地寫入log file中，并且log file的flush(刷到磁盤)操作同時進行.該模式下，在事務(wù)提交的時候，不會主動觸發(fā)寫入磁盤的操作。
  如果innodb_flush_log_at_trx_commit設(shè)置為1，每次事務(wù)提交時MySQL都會把log buffer的數(shù)據(jù)寫入log file，并且flush(刷到磁盤)中去.
  如果innodb_flush_log_at_trx_commit設(shè)置為2，每次事務(wù)提交時MySQL都會把log buffer的數(shù)據(jù)寫入log file.但是flush(刷到磁盤)操作并不會同時進行。該模式下,MySQL會每秒執(zhí)行一次 flush(刷到磁盤)操作。

  sync_master_info:

  每間隔多少事務(wù)刷新master.info，如果是table（innodb）設(shè)置無效，每個事務(wù)都會更新

  sync_relay_log_info:

  每間隔多少事務(wù)刷新relay-log.info，如果是table（innodb）設(shè)置無效，每個事務(wù)都會更新

  master_info_repository:

  記錄主庫binlog的信息，可以設(shè)置FILE（master.info）或者TABLE（mysql.slave_master_info）

  relay_log_info_repository:

  記錄備庫relaylog的信息，可以設(shè)置FILE（relay-log.info）或者TABLE（mysql.slave_relay_log_info）

  innodb_io_capacity:默認(rèn)為200，如果是SSD盤，建議調(diào)整到5000

• 高并發(fā)設(shè)置

    擴大文件描述符：

    1、動態(tài)修改,重啟失效,只能使用root,并且當(dāng)前session有效：ulimit -n 65535

    2、修改配置文件,永久生效，在/etc/security/limits.conf配置文件中增加：         

        * soft nofile 65535
        * soft nproc 65535
        * hard nofile 65535
        * hard nproc 65535

主機層面：

1、CPU

將其調(diào)整為性能模式，即：performance，可以參考博客：http://blog.csdn.net/myarrow/article/details/7917181/

2、內(nèi)存

（1）關(guān)閉NUMA特性。NUMA陷阱現(xiàn)象是當(dāng)你的服務(wù)器還有內(nèi)存的時候，發(fā)現(xiàn)它已經(jīng)在開始使用swap了，甚至已經(jīng)導(dǎo)致機器出現(xiàn)停滯的現(xiàn)象。這個就有可能是由于numa的限制，如果一個進程限制它只能使用自己的numa節(jié)點的內(nèi)存，那么當(dāng)自身numa node內(nèi)存使用光之后，就不會去使用其他numa node的內(nèi)存了，會開始使用swap，甚至更糟的情況，機器沒有設(shè)置swap的時候，可能會直接宕機。所以，強烈建議在操作系統(tǒng)層面關(guān)閉NUMA特性。直接在/etc/grub.conf的kernel行最后添加numa=off即可。

（2）盡量配置高內(nèi)存。這種mysql可以充分利用內(nèi)存資源緩存熱塊數(shù)據(jù)，避免由于內(nèi)存不足導(dǎo)致臟數(shù)據(jù)不斷地刷盤從而產(chǎn)生IO瓶頸，也可以避免熱塊數(shù)據(jù)被擠出緩存區(qū)的情況發(fā)生。

（3）修改swappiness設(shè)置。swappiness是linux的一個內(nèi)核參數(shù),用來控制物理內(nèi)存交換出去的策略.它允許一個百分比的值,最小的為0,最大的為100,改值默認(rèn)是60.m.swappiness設(shè)置為0表示盡量少使用swap,100表示盡量將inactive的內(nèi)存頁交換到swap里或者釋放cache。inactive內(nèi)存的意思是程序映射著，但是”長時間”不用的內(nèi)存。這個值推薦設(shè)置為1,設(shè)置方法如下,在/etc/sysctl.conf文件中增加一行：vm.swappiness = 1

3、磁盤IO

（1）盡量將數(shù)據(jù)文件和日志文件分開，各自承載相應(yīng)的磁盤。避免日志刷盤和數(shù)據(jù)刷盤之間爭用IO。

（2）盡量使用高IO的磁盤，或者使用raid10這類磁盤陣列，或者直接采用SSD盤

4、網(wǎng)絡(luò)優(yōu)化

集群內(nèi)機器最好部署在同一內(nèi)網(wǎng)or專線直連的網(wǎng)絡(luò)環(huán)境，以保證低延遲，高吞吐的網(wǎng)絡(luò)環(huán)境。避免由于網(wǎng)絡(luò)問題導(dǎo)致的集群內(nèi)腦裂or主從復(fù)制異常的情況。

操作系統(tǒng)層面：

1、文件系統(tǒng)

    強烈建議采用xfs文件系統(tǒng)，ext4文件系統(tǒng)存在bug，觸發(fā)會占用自身大部分IO，造成IO瓶頸。詳見http://1057212.blog.51cto.com/1047212/1891734

    優(yōu)化文件系統(tǒng)掛載參數(shù)：文件系統(tǒng)掛載參數(shù)是在/etc/fstab文件中修改,重啟時候生效。noatime表示不記錄訪問時間,nodiratime不記錄目錄的訪問時間。barrier=0,表示關(guān)閉barrier功能。其中nobarrier是xfs文件系統(tǒng)特有，ext4文件系統(tǒng)并無此參數(shù)。

2、IO調(diào)度算法

    NOOP:NOOP算法的全寫為No Operation。該算法實現(xiàn)了最最簡單的FIFO隊列，所有IO請求大致按照先來后到的順序進行操作。

    CFQ:CFQ算法的全寫為Completely Fair Queuing。該算法的特點是按照IO請求的地址進行排序，而不是按照先來后到的順序來進行響應(yīng)。

    DEADLINE:DEADLINE在CFQ的基礎(chǔ)上，解決了IO請求餓死的極端情況。除了CFQ本身具有的IO排序隊列之外，DEADLINE額外分別為讀IO和寫IO提供了FIFO隊列。讀FIFO隊列的最大等待時間為500ms，寫FIFO隊列的最大等待時間為5s。FIFO隊列內(nèi)的IO請求優(yōu)先級要比CFQ隊列中的高，，而讀FIFO隊列的優(yōu)先級又比寫FIFO隊列的優(yōu)先級高。優(yōu)先級可以表示為：FIFO(Read) > FIFO(Write) > CFQ

    一般mysql服務(wù)器的磁盤IO調(diào)度算法采用deadline，既可以保證IO請求不被餓死，又可以使得讀IO的處理優(yōu)先級大于寫IO。

系統(tǒng)架構(gòu)層面優(yōu)化

1. 負(fù)載均衡

   這里可以分為兩類：

   1、PXC or mysql cluster or mysql group replication這類數(shù)據(jù)庫集群，由于支持多點寫入的方式，這里的負(fù)載均衡可以實現(xiàn)讀和寫都均衡負(fù)載的情況，通過在數(shù)據(jù)庫前端部署haproxy或者LVS的方式實現(xiàn)負(fù)載均衡。但是需要強調(diào)的是，目前這類型集群在多點寫入的情況很容易產(chǎn)生鎖沖突和更新丟失的情況，一般官方建議開啟單點寫入。也就是說，一般也只能實現(xiàn)單節(jié)點承載寫操作，剩余節(jié)點均衡負(fù)載讀操作。

   2、mysql一主多從架構(gòu)：由于主庫必須單獨承載寫操作，故均衡負(fù)載只是針對于讀操作。也是通過在數(shù)據(jù)庫前端部署haproxy或者LVS的方式實現(xiàn)讀操作負(fù)載均衡。

2. 緩存

   一般采用內(nèi)存數(shù)據(jù)庫如redis、memcached同mysql結(jié)合，將熱點數(shù)據(jù)放在內(nèi)存數(shù)據(jù)庫上實現(xiàn)高并發(fā)?？梢詤⒖疾┛停篽ttp://blog.csdn.net/stubborn_cow/article/details/50586990

3. 分布式優(yōu)化

   分庫分表：

   這里也可以分為2類：

   （1）通過前端應(yīng)用代碼邏輯實現(xiàn)的方式，實現(xiàn)表分拆的方式。這樣做對應(yīng)用程序的侵入性比較大，但是數(shù)據(jù)處理邏輯的過程把控在自己手上，有異常可以自主定位。

   （2）通過中間件的方式實現(xiàn)，目前常用的mycat、cobar實現(xiàn)數(shù)據(jù)分片。

   讀寫分離：

   一般通過數(shù)據(jù)庫中間件的方式實現(xiàn)，常用的中間件例如：maxscale、mycat、cobar、altas等

網(wǎng)頁題目：mysql優(yōu)化
文章來源：http://muchs.cn/article10/jejgdo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供虛擬主機、微信小程序、云服務(wù)器、建站公司、域名注冊、Google

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)