數(shù)據(jù)庫作為一個大型的并發(fā)存儲系統(tǒng)跌宛，其內(nèi)部設計極其復雜酗宋，開發(fā)者在使用數(shù)據(jù)庫時，面臨著可用性疆拘、可靠性蜕猫、性能、安全哎迄、擴展性等多重挑戰(zhàn)回右，這就使得數(shù)據(jù)庫的使用具有較高的技術(shù)門檻。隨著DevOps理念的流行漱挚，開發(fā)者開始更多的參與和承擔數(shù)據(jù)庫的運維工作翔烁，其中就包括對數(shù)據(jù)庫的定期巡檢。
對數(shù)據(jù)庫定期進行健康檢查是數(shù)據(jù)庫日常維護的重要環(huán)節(jié)旨涝，通過檢查數(shù)據(jù)庫的各項運行指標蹬屹，評估系統(tǒng)的運行風險，提前將風險消滅在搖籃中白华，能夠有效提高數(shù)據(jù)庫服務的質(zhì)量哩治，今天我們就來聊聊開發(fā)者如何對數(shù)據(jù)庫進行健康檢查。
數(shù)據(jù)庫的健康檢查涉及索引設計衬鱼、容量規(guī)劃业筏、服務安全、參數(shù)配置鸟赫、用戶訪問蒜胖、集群復制6個方面消别。
一、索引設計
合理的索引設計能夠有效加速數(shù)據(jù)庫的訪問台谢，提高查詢的執(zhí)行效率寻狂，減少用戶查詢對服務端的資源消耗。但是不合理的朋沮、低效的蛇券、冗余的甚至無效的索引不僅無法起到加速查詢的效果，反而會影響數(shù)據(jù)庫的插入樊拓、更新性能纠亚，甚至是數(shù)據(jù)庫的高可用方案能否生效。
主鍵索引缺失
由于MySQL默認存儲引擎InnoDB（MySQL 5.6版本 以上）使用的是聚簇索引表設計筋夏，這就要求所有的表必須包含一個主鍵蒂胞，所有的數(shù)據(jù)記錄按照主鍵次序構(gòu)建B+樹。如果用戶在創(chuàng)建表時顯式指定主鍵条篷，則數(shù)據(jù)庫會使用用戶指定的主鍵構(gòu)建B+樹骗随，但是如果用戶沒有顯式指定主鍵，同時也沒有創(chuàng)建任何唯一鍵索引赴叹，InnoDB為了確保每張表至少包含一個主鍵鸿染，則默認會為用戶生成一個“隱含主鍵”，該主鍵對用戶不可見乞巧，甚至對于MySQL Server層的binlog也不可見牡昆。binlog是連接MySQL主從復制節(jié)點的紐帶，所有主節(jié)點的更新都是通過binlog傳遞給從節(jié)點的摊欠，一旦binlog中沒有更新記錄的主鍵ID丢烘，這就會導致基于Row格式的binlog在從節(jié)點執(zhí)行時，無法唯一確定一條記錄些椒，只能通過全表掃描來進行匹配播瞳，大幅降低了從機的執(zhí)行效率，造成復制延遲免糕。如果是高可用故障切換的從節(jié)點赢乓，會導致切換的時間大幅增加，甚至會導致高可用機制失效石窑。如果是實現(xiàn)讀寫分離的只讀從節(jié)點牌芋，則會導致應用讀到的數(shù)據(jù)可能是很久以前的舊數(shù)據(jù)。所以我們建議使用InnoDB存儲引擎的MySQL用戶在創(chuàng)建表時松逊，必須顯式指定主鍵躺屁。
主鍵索引與業(yè)務相關(guān)
如果用戶在創(chuàng)建表時指定的主鍵與業(yè)務相關(guān)，可能會被頻繁的更新经宏，這樣會引起MySQL數(shù)據(jù)庫的InnoDB存儲引擎進行頻繁的節(jié)點合并和分裂犀暑，造成大量額外的系統(tǒng)IO開銷驯击，影響數(shù)據(jù)庫的插入和更新性能。我們推薦開發(fā)者在創(chuàng)建表時指定與業(yè)務無關(guān)的自增字段作為主鍵耐亏，這樣不僅會提高按時間序插入的性能（順序?qū)懭胗脖P）徊都，同時也可以提高按插入時間范圍檢索的查詢效率。
冗余索引
如果一個索引涉及的字段屬性包含另外一個索引涉及的字段屬性广辰，同時兩個索引字段順序一致暇矫，且兩個索引的首字段屬性相同，則可以認為涉及字段少的索引為冗余索引择吊。在MySQL 5.7推出sys庫之前李根，我們可以通過percona的工具pt-duplicate-key-checker來完成對冗余索引的檢查，在MySQL 5.7中干发，我們可以通過sys庫schema_redundant_indexes表來完成朱巨。
低效索引
索引的作用在于通過索引史翘，查詢能夠掃描更少的記錄枉长。數(shù)據(jù)庫中的記錄在索引字段區(qū)分度越高，掃描的記錄數(shù)就越少琼讽，執(zhí)行的效率就越高必峰。如果數(shù)據(jù)庫表中的記錄在索引字段區(qū)分度不大，索引對記錄的篩選結(jié)果就不明顯钻蹬，索引就無法起到加速查詢的作用吼蚁。通過數(shù)據(jù)庫記錄在索引字段的區(qū)分度，我們可以衡量索引的執(zhí)行效率问欠。MySQL系統(tǒng)庫mysql庫下肝匆，innodb_index_stats表的stat_value字段，記錄了某張表在某個索引的不同取值的記錄個數(shù)顺献，innodb_table_stats表的n_rows字段記錄了某張表的總記錄數(shù)旗国，二者相除，即可得到數(shù)據(jù)庫記錄在某個索引的區(qū)分度注整，越接近1能曾，表示區(qū)分度越高，低于0.1肿轨，則說明區(qū)分度較差寿冕，開發(fā)者應該重新評估SQL語句涉及的字段，選擇區(qū)分度高的多個字段創(chuàng)建索引椒袍，通過運行下面的SQL語句驼唱，就可以計算每張表的索引區(qū)分度。
SELECT i.database_name AS `db`,
?????? i.table_name AS `table`,
?????? i.index_name AS `index_name`,
?????? i.stat_description AS `cols`,
?????? i.stat_value AS `defferRows`,
?????? t.n_rows AS `ROWS`,
?????? ROUND(((i.stat_value / IFNULL(IF(t.n_rows < i.stat_value,i.stat_value,t.n_rows),0.01))),2) AS sel_persent
FROM mysql.innodb_index_stats i INNER JOIN mysql.innodb_table_stats t
ON i.database_name = t.database_name AND i.table_name= t.table_name
WHERE i.index_name != 'PRIMARY' AND i.stat_name LIKE '%n_diff_pfx%';
也可以在這個WHERE條件后面跟上t.database_name=’庫名’ AND t.table_name=’表名’用于精確查找某個表的索引區(qū)分度驹暑。
無效索引
如果一個索引始終無法被查詢使用曙蒸，它的存在只能增加數(shù)據(jù)庫的維護開銷捌治，開發(fā)者應該及時刪除這些索引。通過MySQL 5.7 sys庫schema_unused_indexes視圖纽窟，可以查看當前實例哪些索引從沒有被使用肖油。
二、容量規(guī)劃
數(shù)據(jù)庫的運行依賴計算臂港、存儲森枪、網(wǎng)絡等多種資源，通過對各種資源的使用情況分析审孽，對資源進行合理的規(guī)劃配置县袱，是數(shù)據(jù)庫穩(wěn)定運行的必要條件。
CPU
通常使用CPU利用率衡量CPU的繁忙程度佑力，通過top命令式散，開發(fā)者可以查看CPU利用率實時變化。CPU 利用率持續(xù)超過80%打颤，預示計算資源已經(jīng)接近飽和暴拄，如果開發(fā)者已經(jīng)做過SQL優(yōu)化，則需要使用更高配置的CPU编饺。通過查看7天內(nèi)CPU利用率超過80%的時間占整體時間的百分比乖篷，以及單次持續(xù)時間超過一定閾值，則可視為CPU擴容的觸發(fā)條件透且。
IO
大部分數(shù)據(jù)庫應用都是的IO Bound類型撕蔼，IO 處理能力直接決定了數(shù)據(jù)庫的性能。IO 利用率統(tǒng)計了一秒內(nèi)IO請求隊列非空的時間比例秽誊，IO利用率越高就表示硬盤越繁忙鲸沮。但是IO 利用率100%并不表示系統(tǒng)已經(jīng)無法處理更多的IO請求。IOPS和每秒IO字節(jié)數(shù)可以從存儲設備的層次更準確的描述IO負載锅论。每一個存儲設備都有IOPS和每秒IO字節(jié)數(shù)的上限讼溺，任意一個達到上限，就會成為IO處理能力的瓶頸棍厌，在傳統(tǒng)機械硬盤中肾胯，隨機IO主要受到IOPS的限制，順序IO主要受帶寬限制耘纱。除此之外敬肚，我們還可以從應用的角度，使用一次IO請求的響應時間來描述IO負載束析，一次IO請求的響應時間包括其在隊列中的等待時間和實際IO處理時間之和艳馒。通過iostats開發(fā)者可以很方便的收集這些數(shù)據(jù)。如果這些指標在一段時間內(nèi)持續(xù)接近設定上限，則可以認為IO 過載弄慰，通過擴大內(nèi)存第美，讓更多的讀寫請求命中緩存可以緩解硬盤IO。另外陆爽，使用更高配置的存儲設備什往，例如固態(tài)硬盤，也可以大幅提高系統(tǒng)的IO處理能力慌闭。
存儲空間
存儲空間不足會導致嚴重的系統(tǒng)故障别威，數(shù)據(jù)庫可能會宕機，更為嚴重的是數(shù)據(jù)庫進程存活驴剔，但是無法響應服務省古，從而造成基于進程的宕機監(jiān)控失效。根據(jù)7天內(nèi)數(shù)據(jù)庫中存儲數(shù)據(jù)的變化丧失，我們可以按照一定的擬合算法豺妓，估算出未來3天內(nèi)數(shù)據(jù)的增長情況，來判斷實例是否存在存儲空間不足的風險布讹。
內(nèi)存
使用InnoDB存儲引擎的MySQL數(shù)據(jù)庫在實例啟動時琳拭，就會預分配一塊固定大小的內(nèi)存空間，所有讀寫請求都會在該空間中完成炒事，如果內(nèi)存中緩存了用戶讀寫的數(shù)據(jù)臀栈，則直接讀取內(nèi)存蔫慧，如果內(nèi)存中沒有用戶讀寫的數(shù)據(jù)挠乳，則需要將數(shù)據(jù)先從硬盤中l(wèi)oad進內(nèi)存中，由于內(nèi)存的讀寫速度遠遠快于硬盤姑躲，這就使得讀寫請求是否命中內(nèi)存決定了讀寫請求的處理速度睡扬。內(nèi)存空間越大，緩存數(shù)據(jù)越多黍析，命中的幾率也就越大卖怜。所以我們可以使用緩存命中率來衡量內(nèi)存空間大小是否滿足應用的需求。在MySQL中阐枣，show engine innodb status 命令的Buffer pool hit rate可以度量近一段時間范圍內(nèi)Buffer pool的命中情況马靠。
網(wǎng)絡
網(wǎng)絡帶寬在數(shù)據(jù)庫返回記錄較多的情況下，也可能會成為系統(tǒng)的瓶頸蔼两。一般我們使用每秒網(wǎng)絡流入和流出字節(jié)數(shù)來衡量網(wǎng)絡流量是否達到帶寬限制甩鳄。在云環(huán)境下，每臺虛擬機或者容器都是有一定的網(wǎng)絡帶寬配額额划，私有網(wǎng)絡的配額相對比較大妙啃，公網(wǎng)配額與用戶付費相關(guān)；使用iftop 可以查看當前系統(tǒng)的網(wǎng)絡流量。
三揖赴、服務安全
弱密碼
MySQL的登陸認證使用的是IP和賬戶密碼的方式馆匿，很多開發(fā)者為了方便記憶，習慣將數(shù)據(jù)庫密碼設置為弱密碼燥滑，這實際是非常危險的渐北。數(shù)據(jù)庫中的數(shù)據(jù)很多涉及敏感業(yè)務，弱密碼非常容易被破解铭拧，對數(shù)據(jù)庫中的數(shù)據(jù)是一個嚴重安全隱患腔稀。MySQL系統(tǒng)庫mysql庫下的user表的password字段保存了所有用戶的密碼，MySQL使用的是兩次sha-1的不可逆加密算法羽历，所以我們無法通過password字段獲取用戶的密碼內(nèi)容焊虏，但是我們可以通過將常見弱密碼制成彩虹表，模擬MySQL的加密算法秕磷，匹配password字段诵闭，即可發(fā)現(xiàn)數(shù)據(jù)庫中的弱密碼賬號。
網(wǎng)絡安全
在一般的業(yè)務架構(gòu)中澎嚣，數(shù)據(jù)庫都不會直接服務于終端用戶疏尿，而是服務于運行業(yè)務邏輯的應用程序。所以數(shù)據(jù)庫和業(yè)務程序之間出于安全的考慮易桃，會選擇使用私有網(wǎng)絡褥琐。即便如此，為了避免數(shù)據(jù)庫連錯晤郑，也需要在設置數(shù)據(jù)庫賬號時敌呈，增加IP來源限制。在一些特定的場景下造寝，如果數(shù)據(jù)訪問必須借助公網(wǎng)來實現(xiàn)磕洪，就會將數(shù)據(jù)庫暴漏在公網(wǎng)上。使用公網(wǎng)數(shù)據(jù)庫實例诫龙，必須要配置防火墻析显，否則存在被攻擊的隱患。通過iptables我們可以控制訪問數(shù)據(jù)庫的來源IP签赃。
權(quán)限檢查
MySQL提供了多種權(quán)限配置谷异，為了方便管理以及避免誤操作，一般會將管理權(quán)限和訪問權(quán)限配置成兩個不同的賬號锦聊，禁止使用管理權(quán)限作為業(yè)務程序訪問數(shù)據(jù)庫的賬號歹嘹。通過系統(tǒng)庫mysql庫的user表可以確認各個賬號擁有的權(quán)限，盡量避免業(yè)務賬號擁有super權(quán)限括丁。
四荞下、參數(shù)配置
內(nèi)存相關(guān)參數(shù)
MySQL數(shù)據(jù)庫的內(nèi)存使用包括兩個部分：共享內(nèi)存與連接獨占內(nèi)存。每一個用戶新建連接，數(shù)據(jù)庫都要分配一塊固定大小的內(nèi)存空間保存用戶的臨時數(shù)據(jù)尖昏，這些空間為單個連接獨占仰税。在MySQL實例啟動時，系統(tǒng)同時也會預先分配一些實例級別的共享內(nèi)存空間抽诉，例如Innodb_buffer_pool陨簇，Innodb_log_buffer_pool等，供所有連接共享迹淌。獨占內(nèi)存空間乘以最大連接數(shù)加上共享內(nèi)存空間河绽，我們可以計算出MySQL最大可使用的內(nèi)存空間，如果超過實際物理內(nèi)存大小唉窃，就存在MySQL進程被Linux操作系統(tǒng)強行oom kill風險耙饰，導致實例宕機。MySQL的這些內(nèi)存空間都可以通過配置參數(shù)指定大小纹份，如果超過實際內(nèi)存空間苟跪，應該調(diào)整相應參數(shù)配置，最常見的是調(diào)整Innodb_buffer_pool和最大連接數(shù)蔓涧。
頻繁卡頓
如果設置過大件已，會導致數(shù)據(jù)庫實例重啟或者故障恢復花費大量的時間。一般元暴，對于使用固態(tài)硬盤等高配置的存儲設備的數(shù)據(jù)庫篷扩，可以將重做日志設置大一些，對于使用機械硬盤的數(shù)據(jù)庫茉盏，應該設置小一些鉴未，一般在512M到4G之間。innodb_flush_log_at_trx_commit定義了重做日志的刷新節(jié)奏援岩，如果該參數(shù)非1歼狼，會導致數(shù)據(jù)庫宕機重啟后丟失部分更新數(shù)據(jù)掏导，對于數(shù)據(jù)可靠性要求較高的應用造成嚴重影響享怀。
二進制日志相關(guān)參數(shù)
binlog 主要用于MySQL集群復制以及故障恢復擔任協(xié)調(diào)者的作用。binlog_format定義了binlog的格式趟咆，主要包括ROW添瓷、STATEMENT、MIXED三種格式值纱，ROW格式是最安全的一種日志格式鳞贷，會保證主從數(shù)據(jù)的嚴格一致，建議開發(fā)者選用ROW格式虐唠。但是ROW格式的binlog會占用更多的存儲空間搀愧，通過expire_logs_days可以控制保存binlog的天數(shù)，如果binlog占用的存儲空間比例超過50%，則應考慮適當減少binlog的保存天數(shù)咱筛。sync_binlog 參數(shù)定義了binlog刷新硬盤的節(jié)奏搓幌，如果非1，會導致宕機重啟后最近的更新數(shù)據(jù)丟失迅箩。
連接數(shù)相關(guān)參數(shù)
MySQL有最大連接數(shù)限制max_connections溉愁，如果應用連接超過max_connetions限制，則會得到out of max connections異常饲趋，無法建立連接拐揭。show processlist可以查看當前的連接數(shù)，如果接近最大限制奕塑，則存在無法新建連接的風險堂污。通過在應用端使用連接池可以控制數(shù)據(jù)庫的連接數(shù)。
五龄砰、用戶訪問
慢連接
慢查詢數(shù)量是最直觀的反映數(shù)據(jù)庫處理能力是否滿足業(yè)務需求的指標敷鸦。通過設置slow_query_log可以開啟慢查詢?nèi)罩荆琈ySQL數(shù)據(jù)庫會將執(zhí)行時間超過long_query_time的查詢記入慢查詢?nèi)罩厩薰保绻硞€時間段內(nèi)扒披，慢查詢數(shù)量急劇增加，則開發(fā)者就必須要關(guān)注數(shù)據(jù)庫的性能問題圃泡，首先就需要進行SQL優(yōu)化碟案，其次考慮資源是否需要擴容，最后可能需要數(shù)據(jù)庫水平擴展方案颇蜡，包括創(chuàng)建只讀從節(jié)點价说。
死鎖數(shù)量
兩個事務涉及的數(shù)據(jù)庫記錄有重疊，如果SQL語句的加鎖順序不一致风秤，就會導致事務之間的死鎖鳖目。雖然MySQL數(shù)據(jù)庫會自動的檢測死鎖并強制回滾系統(tǒng)認為代價較小的事務，但是死鎖的檢測與事務回滾都有較大的代價缤弦，會嚴重拖慢數(shù)據(jù)庫的性能领迈，所以當系統(tǒng)中出現(xiàn)大量死鎖時，開發(fā)者必須引起重視碍沐，要分析發(fā)生死鎖的事務的SQL語句的加鎖規(guī)則狸捅，調(diào)整SQL語句。通過show engin innodb status可以查看死鎖的相關(guān)信息以及系統(tǒng)的處理過程累提。
六尘喝、集群復制
數(shù)據(jù)安全
復制是MySQL多個節(jié)點之間實現(xiàn)數(shù)據(jù)同步的重要機制，主要用于搭建高可用實例主從節(jié)點以及提供多個只讀從節(jié)點提高讀擴展能力斋陪。節(jié)點之間的數(shù)據(jù)是否最終一致對于高可用方案是否生效朽褪，只讀實例讀取的數(shù)據(jù)是否正確有著嚴重影響置吓。從機執(zhí)行show slave status可以獲取從機的復制狀態(tài)，Slave_IO_Running和Slave_SQL_Running分別表示IO和SQL線程是否正常運行缔赠，如果不正常交洗，則應及時處理。參數(shù)relay_log_recovery和relay_log_info_repository影響從節(jié)點宕機重啟后橡淑，與主機的復制位置是否正確构拳，如果位置錯誤，則可能導致數(shù)據(jù)錯誤梁棠。
復制性能
復制延遲經(jīng)常用來評估復制性能是否滿足業(yè)務需求置森。Show slave status的Seconds behind master字段標識了從機落后主機的延遲時間。如果延遲較長符糊，則會影響高可用實例主從切換的時間以及只讀從節(jié)點是否能夠及時讀到最新數(shù)據(jù)凫海。通過使用并行復制技術(shù)可以提高從節(jié)點的復制性能。MySQL 5.6提供了基于Database級別的并行復制男娄，通過slave_parallel_workers 設置并行線程數(shù)行贪；MySQL 5.7提供了基于LOGICAL_CLOCK的并行復制， 主機上同一個Group提交的binlog中包含事務在從機并行執(zhí)行模闲，相比database建瘫，具備更高的并發(fā)性，除了設置slave_parallel_workers尸折，還需要將slave-parallel-type設置為LOGICAL_CLOCK啰脚。slave_preserve_commit_order=1可以確保從機并行執(zhí)行的事務按序提交。同時從機的log_bin和log_slave_updates參數(shù)必須同時開啟实夹。
參考：http://www.ywnds.com/?p=7790
