Mysql 性能優(yōu)化——必勝之道

mysql的性能優(yōu)化是運維和DBA們常常面對的問題，也是各大公司招聘人才時看中的要點之一。性能優(yōu)化聽上去很難，似乎只有大神才能做，然而，mysql的性能優(yōu)化絕不是運維獨自一個能完成的，DBA、開發(fā)、架構也要參與其中，齊心協(xié)力才能打贏性能優(yōu)化之戰(zhàn)。

    本文將全面解說數(shù)據(jù)庫優(yōu)化的各個方面，主要也就是硬件、網(wǎng)絡、系統(tǒng)、架構、軟件這幾個方面，其中軟件主要包括軟件版本、表設計、引擎、SQl語句、配置文件my.cnf等幾個方面

一 硬件和網(wǎng)絡

    硬件和網(wǎng)絡應該怎么優(yōu)化呢？兩個字“砸錢”！購買優(yōu)質(zhì)服務器，高性能多核cpu，高內(nèi)存，高性能磁盤或者ssd盤等。硬件高配置是數(shù)據(jù)庫高性能的基礎，一般公司來說一臺數(shù)據(jù)庫機器配上72G內(nèi)存已經(jīng)足夠了，具體配置也可根據(jù)公司的業(yè)務需求來添加。網(wǎng)絡的基礎配件性能越高越好，N兆光纖、N兆交換機、N兆網(wǎng)卡。當然，如果公司差錢，那就選擇合適當前業(yè)務的基礎配置即可。總之一句話：硬件與網(wǎng)絡要用錢來優(yōu)化！

二 系統(tǒng)

    操作系統(tǒng)選擇，首選當前流行穩(wěn)定的版本，比如centos6.5 centos6.8，盡量少用5版本，如果公司處于整體環(huán)境升級階段，可以直接換成最新的7版本，其性能優(yōu)于5、6版本；如果不差錢也可以用redhat的相應版本，本人建議還是用centos吧，不花錢且社區(qū)活躍，方便后期管理升級。AIX系統(tǒng)不建議使用，至于什么原因，我只能送給AIX系統(tǒng)兩個字“呵呵”。文件系統(tǒng)上盡量用ext4！

三 架構體系

    mysql數(shù)據(jù)庫的架構體系比較單純，從單機到一主一從，再到一主多從，再到多主多從，再到讀寫分離，分級存儲，分庫分表，主要的形式也就這么多，具體怎么選擇是根據(jù)業(yè)務需求而定，業(yè)務量較小的公司可以考慮一主多從，一般公司做到主從+讀寫分離就可以了，這樣既有了性能也有了備份，數(shù)據(jù)量特別巨大的才用分庫分表。

四 數(shù)據(jù)庫版本控制

    數(shù)據(jù)庫版本與數(shù)據(jù)庫的性能有著密切的關系，版本越高功能越豐富性能越強悍，但是高版本存在著可能未發(fā)現(xiàn)的隱患。目前大部分系統(tǒng)自帶的mysql5.1，此版本穩(wěn)定而悠久，在功能和性能要求不高的數(shù)據(jù)庫環(huán)境中可以使用；不過企業(yè)環(huán)境中還是建議升級到mysql5.6以上版本，如果單機性能要求特高可以使用mysql5.7或者mariadb10.1或者percona5.7；mysql5.6在企業(yè)環(huán)境的應用較為普遍，性能也穩(wěn)定，推薦使用這個版本；如果想使用最新版本的高性能數(shù)據(jù)庫（據(jù)測5.7版本讀寫性能是5.6版本的2倍），推薦使用mariadb10.1，因為mysql企業(yè)版收費了，也有閉源的趨勢?？偨Y(jié)下：推薦使用mysql5.6或者mariadb10.1的相應版本。

    高版本mysql5.7企業(yè)版，較之前版本功能性能的確有了較大提升：

    1 在讀模式下，3倍性能提升；在讀寫模式下，2倍性能提升。

    2 默認開啟ssl安全模式

        >bin/mysql_ssl_rsa_setup 開啟

    3 Buffer_pool可以動態(tài)設置大小，無需重啟

        set global innodb_buffer_pool_size=256*1024*1024   ##設置256M

    4 緩沖池預熱 

        innodb_buffer_pool_dump_at_shutdown=1 innodb_buffer_pool_load_at_startup=1

    5 支持全中文索引

    6 支持死鎖打印到錯誤日志  innodb_print_all_deadlocks=1

    7 支持json格式數(shù)據(jù)存儲

    8 支持kill慢sql        set global max_statement_time=1

    9 支持日志審計 audit plugin

    10 支持錯誤日志打印到系統(tǒng)日志文件中

五 表設計優(yōu)化

    關系數(shù)據(jù)庫面臨著范式的選擇，最低滿足第一范式：表中的字段都是單一屬性，不可再分的；第二范式要求實體的屬性完全依賴于主關鍵字；第三范式是不存在非關鍵字對任一候選主鍵的函數(shù)依賴；數(shù)據(jù)庫的設計應當最大程度的滿足三范式，當然三范式也是存在著問題的，它通常要很多join表，導致查詢效率低下；有時候可以適當?shù)淖鋈哂?，減少join，但要用審慎的態(tài)度對待！

    表字段類型選擇也是優(yōu)化的重點。選取原則盡量是選小不選大，能用字節(jié)少的字段就不用大字段。更小的字段類型占用更少的內(nèi)存，占用的磁盤和內(nèi)存也會很小，占用的寬帶也會少，所以做字段選擇時候必須堅持“用小不用大”。比如主鍵，強烈推薦使用int整型。

    數(shù)值類型一般常用的int（4個字節(jié)）和bigint（8個字節(jié)），比如手機號可用bigint，年齡用tinyint；字符類型最常用的有char（256）和varchar（65535），他們跟字符編碼也有關系，latin1占用一個字節(jié)，gbk占用2個字節(jié)，utf8占用3個字節(jié)；時間類型常用date（3個字節(jié)）和datetime（8個字節(jié)）、timestamp（4個字節(jié)）。選擇各種字段類型時候選擇占用最少字節(jié)最適合的。

    在線修改表結(jié)構，可以使用percona的工具pt-online-schema-change,不影響業(yè)務。

六 數(shù)據(jù)庫引擎的選擇

    MyISAM和InnoDB是mysql數(shù)據(jù)庫最常用的兩種存儲引擎，在5.5版本之前，默認是MyISAM，之后版本默認是InnoDB。兩者的主要區(qū)別如下：

    1  MyISAM是非事務安全型，InnoDB是事務安全型

    2  MyISAM應用表級鎖，開銷小，InnoDB是行級鎖，開銷大，支持更好的并發(fā)寫操作

    3 MyISAM支持全文索引，Innodb在5.6版本以后才支持

    4 MyISAM相對簡單，管理方便，效率高，小型應用可以考慮使用MyISAM引擎

    5 MyISAM表保存成文件形式，易于跨平臺遷移

    6 InnoDB比MyISAM更安全，可以隨時從非事務切換到事務

    據(jù)測，在同等配置下的壓力測試中，InnoDB性能是MyISAM的10倍左右，所以在選著具有大量讀寫操作的應用中，推薦InnoDB優(yōu)先使用引擎！

七 SQL語句優(yōu)化

    SQL語句決定著數(shù)據(jù)庫70%的性能狀況，大多數(shù)性能不好都是sql語句引起的。

    普通硬件+普通配置+完美sql語句=一般性能

    完美硬件+完美配置+垃圾sql語句=較差性能

    完美硬件+完美配置+一般sql語句=一般性能

    完美硬件+完美配置+完美sql語句=超優(yōu)性能

    1 定位慢SQL語句(記錄)

    開啟慢日志功能，在my.cnf中添加配置：

        slow_query_log=1

        slow_query_log_file=mysql.slow

        long_query_time=2        #超過兩秒的記錄下來

        當數(shù)據(jù)庫連接數(shù)較高時，就可以截取某段時間的滿日志

        sed -n '#time 2017-03-08 14:30:00/,/end/p' mysql.slow > slow.log

    然后用mysqldumpslow 命令取出耗時間最長的10條慢sql分析

        mysqldumpslow -s t -t 10 slow.log

    2 優(yōu)化not in子查詢

    用left join 代替not in 子查詢

    原語句：> select sql_cache count(*) from t1 where id not in (select id from t2);

    優(yōu)化： > select sql_cache count(*) from t1 left join t2 on t1.id=t2.id where t2.id is null; 

    3 優(yōu)化like語句

    在mysql中，like 'xxx%'可以用到索引，但是like '%xxx%'不能用到索引。

    使用索引可以減少IO，提高性能。

    原語句：> select * from t1 where name like '%game%';

    優(yōu)化： > select id from t1 where name like '%game%';

    4 limit 分頁優(yōu)化

    原語句： > select game * from t1 order by id limit 99,10;

    優(yōu)化： > select game * from t1 where id>=100 order by id limit 10;

    5 優(yōu)化count統(tǒng)計

    利用輔助索引和distinct優(yōu)化

    原語句： > select count（*） from my_user;

    優(yōu)化： > select count(*) from my_user where id >=0;

               > select count(*) from (select distinct k from my_user)tmp;

    6 優(yōu)化or語句

    使用union all 代替or

    原語句： > select * from user where name='a' or age=18;

    優(yōu)化： > select * from user where name='a' union all select * from user where age =18;

    7 合理使用索引

八 my.cnf配置文件優(yōu)化

    配置文件內(nèi)的可以設置的項很多，下面列出常常需要注意重要項

    1 max_connections 最大連接數(shù)，默認100 ，一般設置500-1000即可。

    2 innodb_buffer_pool_size，默認128M，可以設置為物理內(nèi)存的60%-70%。

    3 query_cache_size=64M query_cache_type=1 query_cache_limit=1M

    4 wait_timeout=100    等待時長

    5 connect_timeout=20    interactive_timeout=100

    6 slow_query_log=1    開啟慢日志

    7 thread_cache_size=64

    8 relay_log_recovery=1    中繼日志恢復

    9 open_files_limit=28512

    ..........

具體配置參數(shù)需要根據(jù)硬件環(huán)境和業(yè)務需求去定。

結(jié)語：mysql性能優(yōu)化是一項系統(tǒng)龐大的工程，需要運維、架構、開發(fā)等多方參與共同改進，切勿盲目做調(diào)優(yōu)處理，認真分析性能瓶頸所在，針對瓶頸處調(diào)優(yōu)方能事半功倍！