數(shù)據(jù)庫的健康檢查涉及索引設(shè)計、容量規(guī)劃、服務(wù)安全、參數(shù)配置、用戶訪問、集群復(fù)制6個方面。

數(shù)據(jù)庫作為一個大型的并發(fā)存儲系統(tǒng)，其內(nèi)部設(shè)計極其復(fù)雜。開發(fā)者在使用數(shù)據(jù)庫時，面臨著可用性、可靠性、性能、安全、擴(kuò)展性等多重挑戰(zhàn)，這就使得數(shù)據(jù)庫的使用具有較高的技術(shù)門檻。

一般大型團(tuán)隊都會雇傭?qū)Ｂ毜?DBA來維護(hù)數(shù)據(jù)庫的日常運(yùn)行，指導(dǎo)開發(fā)者建立正確的使用姿勢。但是對于一般的中小型團(tuán)隊，受限于人力成本，這種模式很難實現(xiàn)。

而隨著 DevOps理念的流行，開發(fā)者開始更多地參與和承擔(dān)數(shù)據(jù)庫的運(yùn)維工作，其中就包括對數(shù)據(jù)庫的定期巡檢。

對數(shù)據(jù)庫定期進(jìn)行健康檢查是數(shù)據(jù)庫日常維護(hù)的重要環(huán)節(jié)，通過檢查數(shù)據(jù)庫的各項運(yùn)行指標(biāo)，評估系統(tǒng)的運(yùn)行風(fēng)險，提前將風(fēng)險消滅在搖籃中，能夠有效提高數(shù)據(jù)庫的服務(wù)質(zhì)量。

今天我們就來聊聊開發(fā)者如何對數(shù)據(jù)庫進(jìn)行健康檢查。

數(shù)據(jù)庫的健康檢查涉及索引設(shè)計、容量規(guī)劃、服務(wù)安全、參數(shù)配置、用戶訪問、集群復(fù)制6個方面。

索引設(shè)計

合理的索引設(shè)計能夠有效加速數(shù)據(jù)庫的訪問，提高查詢的執(zhí)行效率，減少用戶查詢對服務(wù)端的資源消耗。

而不合理的、低效的、冗余的甚至無效的索引不僅無法起到加速查詢訪問的效果，反而會影響數(shù)據(jù)庫的插入、更新性能，甚至是數(shù)據(jù)庫的高可用方案能否生效。 所以針對索引的健康檢查主要是發(fā)現(xiàn)系統(tǒng)表結(jié)構(gòu)設(shè)計中的不合理、低效、冗余、無效的索引。

主鍵索引缺失

由于 MySQL默認(rèn)存儲引擎 InnoDB(MySQL 5.6 之后)使用的是聚簇索引表設(shè)計，這就要求所有的表必須包含一個主鍵，所有的數(shù)據(jù)記錄需要按照主鍵順序構(gòu)建 B+樹，所有數(shù)據(jù)記錄位于葉子節(jié)點。

如果用戶在創(chuàng)建表時顯式指定主鍵，則數(shù)據(jù)庫會使用用戶指定的主鍵構(gòu)建B+樹，但是如果用戶沒有顯式指定主鍵，同時也沒有指定任何唯一鍵索引，InnoDB為了確保每張表至少包含一個主鍵，則默認(rèn)會為用戶生成一個“隱含主鍵”，該主鍵對用戶不可見，甚至對于 MySQL Server層的 binlog也不可見。

binlog 是連接 MySQL主從復(fù)制節(jié)點的紐帶，所有主節(jié)點的更新都是通過 binlog傳遞給從節(jié)點的，一旦 binlog中沒有更新記錄的主鍵 ID，就會導(dǎo)致基于 Row格式的 binlog在從節(jié)點執(zhí)行時，無法唯一標(biāo)識某一條記錄，只能通過全表掃描來進(jìn)行全表匹配，大大降低了從機(jī)更新的執(zhí)行效率，從而造成復(fù)制延遲。如果是用于高可用故障切換的從節(jié)點，會導(dǎo)致切換的時間大幅增加，甚至?xí)?dǎo)致高可用機(jī)制失效。如果是用于提高讀能力搭建的只讀從節(jié)點，則會導(dǎo)致應(yīng)用讀到的數(shù)據(jù)可能是很久以前的舊數(shù)據(jù)。所以我們建議使用 InnoDB存儲引擎的 MySQL用戶在創(chuàng)建表時，必須顯示指定主鍵。

主鍵索引與業(yè)務(wù)相關(guān)

如果用戶在創(chuàng)建表時指定的主鍵與業(yè)務(wù)相關(guān)，可能會被頻繁地更新，這樣會引起MySQL數(shù)據(jù)庫的 InnoDB存儲引擎進(jìn)行頻繁的節(jié)點合并和分裂，造成大量額外的系統(tǒng) IO開銷，影響數(shù)據(jù)庫的插入和更新性能。

我們推薦開發(fā)者在創(chuàng)建表時指定與業(yè)務(wù)無關(guān)的自增字段作為主鍵，這樣不僅會提高按時間序插入的性能(順序?qū)懭胗脖P)，同時也可以提高按插入時間范圍檢索的查詢效率。

冗余索引

如果一個索引涉及的字段屬性包含另外一個索引涉及的字段屬性，同時兩個索引字段順序一致，且兩個索引的首字段屬性相同，則可以認(rèn)為涉及字段少的索引為冗余索引。

在 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ā)者應(yīng)該重新評估 SQL語句涉及的字段，選擇區(qū)分度高的多個字段創(chuàng)建索引，通過運(yùn)行下面的 SQL語句，可以計算每張表的索引區(qū)分度。

select t.database_name AS `db`,

t.TABLE_NAME AS `table`,

i.INDEX_NAME AS `index name`,

i.stat_description as cols ,

i.stat_value AS differRows,

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_percent`

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 t.table_name= %

AND t.database_name= %

AND i.INDEX_NAME != 'PRIMARY'

AND i.stat_name like '%n_diff_pfx%';

無效索引

如果一個索引始終無法被查詢使用，它的存在只能增加數(shù)據(jù)庫的維護(hù)開銷，開發(fā)者應(yīng)該及時刪除這些索引。通過 MySQL 5.7 sys庫 schema_unused_indexes視圖，可以查看當(dāng)前實例哪些索引從沒有被使用。

容量規(guī)劃

數(shù)據(jù)庫的運(yùn)行需要依賴計算、存儲、網(wǎng)絡(luò)等多種資源，對各種資源歷史使用情況的分析，對資源配置進(jìn)行合理的規(guī)劃，是確保數(shù)據(jù)庫穩(wěn)定運(yùn)行的必要條件。

CPU

CPU利用率是衡量 CPU繁忙程度最直觀的指標(biāo)，通過 top命令，開發(fā)者可以查看 CPU的使用情況。

CPU 利用率持續(xù)超過80%，說明計算資源已經(jīng)接近飽和，如果開發(fā)者已經(jīng)做過 SQL優(yōu)化，則需要使用更高配置的 CPU。通過查看7天內(nèi) CPU利用率超過80%的時間占整體時間的百分比以及單次持續(xù)時間超過的一定的閾值，可清楚 CPU擴(kuò)容的觸發(fā)條件。

IO

大部分?jǐn)?shù)據(jù)庫應(yīng)用的都是 IO Bound類型，IO處理能力最常用的衡量指標(biāo)是 IO 利用率。IO 利用率統(tǒng)計的是一秒內(nèi) IO請求隊列非空的時間比例，IO利用率越高就表示硬盤越繁忙。但是 IO利用率100%并不表示系統(tǒng)已經(jīng)無法處理更多的 IO請求。

IOPS和每秒 IO字節(jié)數(shù)可以從存儲設(shè)備的角度更準(zhǔn)確地描述 IO狀態(tài)。每一個存儲設(shè)備都有 IOPS和每秒 IO字節(jié)數(shù)的上限，任意一個達(dá)到上限，就會成為 IO處理能力的瓶頸。在傳統(tǒng)機(jī)械硬盤中，隨機(jī) IO主要受到 IOPS的限制，順序 IO主要受帶寬限制。除此之外，我們還可以從應(yīng)用的角度、使用一次 IO請求的響應(yīng)時間來描述 IO負(fù)載。一次 IO請求的響應(yīng)時間包括其在隊列中的等待時間和實際 IO處理時間之和。

通過 iostats，開發(fā)者可以采集到上述指標(biāo)，如果這些指標(biāo)在一段時間內(nèi)持續(xù)接近設(shè)定上限，則可以認(rèn)為 IO過載。通過擴(kuò)大內(nèi)存，讓更多的讀寫請求命中緩存可以緩解硬盤 IO，因為數(shù)據(jù)庫的應(yīng)用大部分都屬于熱點應(yīng)用。另外，使用更高配置的存儲設(shè)備，例如固態(tài)硬盤，可以大幅提高系統(tǒng)的 IO處理能力。

存儲空間

存儲空間不足會導(dǎo)致嚴(yán)重的系統(tǒng)故障，數(shù)據(jù)庫可能宕機(jī)，更為嚴(yán)重的是數(shù)據(jù)庫進(jìn)程存活但是無法響應(yīng)服務(wù)，從而造成基于進(jìn)程的監(jiān)控失效。根據(jù)7天內(nèi)數(shù)據(jù)庫中存儲數(shù)據(jù)的變化，我們可以估算出未來3天內(nèi)數(shù)據(jù)的增長情況，從而判斷實例是否存在存儲空間不足的風(fēng)險。

內(nèi)存

使用 InnoDB存儲引擎的 MySQL數(shù)據(jù)庫在實例啟動時，就會預(yù)分配一塊固定大小的內(nèi)存空間，所有讀寫請求都會在該空間中完成。如果內(nèi)存中緩存了用戶讀寫的數(shù)據(jù)，則直接讀取內(nèi)存，如果內(nèi)存中沒有用戶讀寫的數(shù)據(jù)，則需要將數(shù)據(jù)先從硬盤中 load進(jìn)內(nèi)存中。由于內(nèi)存的讀寫速度遠(yuǎn)遠(yuǎn)快于硬盤，這就使得讀寫請求是否命中內(nèi)存決定了讀寫請求的處理速度。內(nèi)存空間越大，緩存數(shù)據(jù)越多，命中的幾率也就越大。所以我們可以使用緩存命中率來衡量內(nèi)存空間大小是否滿足應(yīng)用的需求。

在MySQL中，show engine innodb status 命令的 Buffer pool hit rate可以度量從上一次執(zhí)行 show engine innodb status到本次時間范圍內(nèi) Buffer pool的命中情況。

網(wǎng)絡(luò)

網(wǎng)絡(luò)帶寬在數(shù)據(jù)庫返回記錄較多的情況下，也可能會成為系統(tǒng)的瓶頸。一般我們使用每秒網(wǎng)絡(luò)流入和流出字節(jié)數(shù)來衡量網(wǎng)絡(luò)流量是否達(dá)到帶寬限制。在云環(huán)境下，每臺虛擬機(jī)或者容器都是有一定的網(wǎng)絡(luò)帶寬配額，私有網(wǎng)絡(luò)的配額會比較大，公網(wǎng)配額與用戶付費(fèi)相關(guān);使用 iftop 可以查看當(dāng)前系統(tǒng)的網(wǎng)絡(luò)流量。

服務(wù)安全

弱密碼

MySQL的登陸認(rèn)證使用的是 IP+賬戶密碼的方式，很多開發(fā)者為了方便記憶，習(xí)慣將數(shù)據(jù)庫密碼設(shè)置為弱密碼，這實際是非常危險的。數(shù)據(jù)庫中的數(shù)據(jù)很多涉及敏感業(yè)務(wù)，弱密碼非常容易被破解，對數(shù)據(jù)庫中的數(shù)據(jù)是一個嚴(yán)重的安全隱患。

MySQL系統(tǒng)庫 mysql庫下的 user表的 password字段保存了所有用戶的密碼，MySQL使用的是兩次 sha-1的不可逆加密算法，所以我們無法通過 password字段獲取用戶的密碼內(nèi)容，但是我們可以通過將常見弱密碼制成彩虹表，模擬 MySQL的加密算法，匹配 password字段，即可發(fā)現(xiàn)數(shù)據(jù)庫中的弱密碼賬號。

網(wǎng)絡(luò)安全

在一般的業(yè)務(wù)架構(gòu)中，數(shù)據(jù)庫都不會直接服務(wù)于終端用戶，而是服務(wù)于運(yùn)行業(yè)務(wù)邏輯的應(yīng)用程序。所以數(shù)據(jù)庫和業(yè)務(wù)程序之間出于安全考慮，會選擇使用私有網(wǎng)絡(luò)。即使使用私有網(wǎng)絡(luò)，為了避免數(shù)據(jù)庫連錯，也需要在設(shè)置數(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è)務(wù)程序訪問數(shù)據(jù)庫的賬號。通過系統(tǒng)庫 mysql庫的 user表可以確認(rèn)各個賬號擁有的權(quán)限，盡量避免業(yè)務(wù)賬號擁有 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)會預(yù)先分配一些實例級別的連接共享內(nèi)存空間，例如 Innodb_buffer_pool，Innodb_log_buffer_pool等。

獨占內(nèi)存空間乘以最大連接數(shù)加上共享內(nèi)存空間，我們可以計算出 MySQL最大可使用的內(nèi)存空間，如果超過實際物理內(nèi)存大小，就存在 MySQL進(jìn)程被操作系統(tǒng)強(qiáng)行 oom kill風(fēng)險，導(dǎo)致實例宕機(jī)。

MySQL的這些內(nèi)存空間都可以通過配置參數(shù)指定大小，如果超過實際內(nèi)存空間，應(yīng)該調(diào)整相應(yīng)參數(shù)配置，最常見的是調(diào)整 Innodb_buffer_pool和最大連接數(shù)。

memory = key_buffer_size + query_cache_size + tmp_table_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size +

innodb_log_buffer_size + max_connections * (sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size +

thread_stack + binlog_cache_size)。

Innodb 日志相關(guān)參數(shù)

innodb_log_file_size 參數(shù)定義了 Innodb 重做日志的大小。如果參數(shù)設(shè)置過小，會導(dǎo)致數(shù)據(jù)庫寫操作頻繁卡頓，如果設(shè)置過大，會導(dǎo)致數(shù)據(jù)庫實例 recovery花費(fèi)大量的時間。

一般情況下，對于使用固態(tài)硬盤等高配置存儲設(shè)備的數(shù)據(jù)庫，我們可以將重做日志設(shè)置大一些;對于使用機(jī)械硬盤的數(shù)據(jù)庫，可以設(shè)置小一些，一般在512M到4G之間。innodb_flush_log_at_trx_commit定義了重做日志的刷新節(jié)奏，如果該參數(shù)非1，會導(dǎo)致數(shù)據(jù)庫宕機(jī)重啟后丟失部分更新數(shù)據(jù)，對于數(shù)據(jù)可靠性要求較高的應(yīng)用造成嚴(yán)重影響。

二進(jìn)制日志相關(guān)參數(shù)

binlog 主要在 MySQL集群復(fù)制以及故障恢復(fù)中擔(dān)任協(xié)調(diào)者的作用。binlog_format 定義了 binlog的格式，主要包括ROW、STATEMENT、MIXED三種格式。ROW格式是最安全的一種日志格式，會保證主從數(shù)據(jù)的嚴(yán)格一致，建議使用 ROW格式。

但是 ROW格式的 binlog會占用更多的存儲空間，通過 expire_logs_days可以控制保存 binlog的天數(shù)，如果 binlog占用的存儲空間比例超過50%，則應(yīng)考慮適當(dāng)減少 binlog的保存天數(shù)。 sync_binlog 參數(shù)定義了 binlog刷新硬盤的節(jié)奏，如果非1，會導(dǎo)致宕機(jī)重啟后部分?jǐn)?shù)據(jù)丟失。

連接數(shù)相關(guān)參數(shù)

MySQL有最大連接數(shù)限制 max_connections，如果應(yīng)用連接超過 max_connetions限制，則會得到 out of max connections異常，無法建立連接。

show processlist 可以查看當(dāng)前的連接數(shù)，如果接近最大限制，則存在無法新建連接的風(fēng)險。通過使用連接池可以控制數(shù)據(jù)庫的連接數(shù)。

用戶訪問

慢連接

慢查詢數(shù)量是最直觀的反映數(shù)據(jù)庫處理能力是否滿足業(yè)務(wù)需求的指標(biāo)。通過設(shè)置 slow_query_log可以開啟慢查詢?nèi)罩荆琈ySQL數(shù)據(jù)庫會將執(zhí)行時間超過 long_query_time的查詢記入慢查詢?nèi)罩?。如果某個時間段內(nèi)，慢查詢數(shù)量急劇增加，則開發(fā)者就必須要關(guān)注數(shù)據(jù)庫的性能問題，首先就需要進(jìn)行 SQL優(yōu)化，其次考慮資源是否需要擴(kuò)容，最后可能需要數(shù)據(jù)庫水平擴(kuò)展方案，包括創(chuàng)建只讀從節(jié)點。

死鎖數(shù)量

兩個事務(wù)涉及的數(shù)據(jù)庫記錄有重疊，如果 SQL語句的加鎖順序不一致，就會導(dǎo)致事務(wù)之間的死鎖。雖然 MySQL數(shù)據(jù)庫會自動檢測死鎖并強(qiáng)制回滾系統(tǒng)認(rèn)為代價較小的事務(wù)，但是死鎖的檢測與事務(wù)回滾都有較大的代價，會嚴(yán)重拖慢數(shù)據(jù)庫的性能，所以當(dāng)系統(tǒng)中出現(xiàn)大量死鎖時，開發(fā)者必須引起重視，要分析發(fā)生死鎖的事務(wù)的 SQL語句的加鎖規(guī)則，調(diào)整 SQL語句。通過 show engin innodb status可以查看死鎖的相關(guān)信息以及系統(tǒng)的處理過程。

集群復(fù)制

數(shù)據(jù)安全

復(fù)制是 MySQL多個節(jié)點之間實現(xiàn)數(shù)據(jù)同步的重要機(jī)制，主要用于搭建高可用實例主從節(jié)點以及提供多個只讀從節(jié)點提高讀擴(kuò)展能力。節(jié)點之間的數(shù)據(jù)是否最終一致對于高可用方案是否生效、只讀實例讀取的數(shù)據(jù)是否正確有著嚴(yán)重影響。

從機(jī)執(zhí)行 show slave status可以獲取從機(jī)的復(fù)制狀態(tài)，Slave_IO_Running和 Slave_SQL_Running分別表示 IO和 SQL線程是否運(yùn)行正常，如果不正常，則應(yīng)及時處理。參數(shù) relay_log_recovery和 relay_log_info_repository影響從節(jié)點宕機(jī)重啟后，與主機(jī)的復(fù)制位置是否正確，如果位置錯誤，則可能導(dǎo)致數(shù)據(jù)錯誤。

復(fù)制性能

復(fù)制延遲經(jīng)常用來評估復(fù)制性能是否滿足業(yè)務(wù)需求。Show slave status的 Seconds behind master字段標(biāo)識了從機(jī)落后主機(jī)的延遲時間。如果延遲較長，則會影響高可用實例主從切換的時間以及只讀從節(jié)點是否能夠及時讀到最新數(shù)據(jù)。

通過使用并行復(fù)制技術(shù)可以提高從節(jié)點的復(fù)制性能。MySQL 5.6 提供了基于 Database級別的并行復(fù)制，通過 slave_parallel_workers 設(shè)置并行線程數(shù);MySQL 5.7提供了基于 LOGICAL_CLOCK的并行復(fù)制， 主機(jī)上同一個 Group提交的 binlog中包含事務(wù)在從機(jī)并行執(zhí)行，相比 Database，具備更高的并發(fā)性，除了設(shè)置 slave_parallel_workers，還需要將 slave-parallel-type設(shè)置為 LOGICAL_CLOCK。slave_preserve_commit_order=1可以確保從機(jī)并行執(zhí)行的事務(wù)按序提交。同時從機(jī)的 log_bin和 log_slave_updates參數(shù)必須同時開啟。

這樣繁瑣又細(xì)致的過程極其考驗我們的耐心， 如果你是網(wǎng)易蜂巢的開發(fā)者，這些工作都可以一鍵解決。

網(wǎng)易蜂巢智能數(shù)據(jù)庫健康診斷系統(tǒng)

使用網(wǎng)易蜂巢的開發(fā)者，可以使用平臺提供的智能健康診斷系統(tǒng)對數(shù)據(jù)庫服務(wù)中的關(guān)系數(shù)據(jù)庫實例進(jìn)行自動健康檢查。檢查內(nèi)容覆蓋6個大類，22個子項，檢查結(jié)束后根據(jù)檢查結(jié)果，會自動生成健康指數(shù)，開發(fā)者根據(jù)健康指數(shù)，可以快速判斷系統(tǒng)存在風(fēng)險的嚴(yán)重程度，同時平臺提供了該分?jǐn)?shù)在所有實例的健康檢查中的排名。

有風(fēng)險的項目，平臺會使用橙色標(biāo)識，開發(fā)者點擊風(fēng)險項目，會看到系統(tǒng)對該風(fēng)險的詳細(xì)描述以及相應(yīng)的修復(fù)建議。針對部分檢查內(nèi)容，系統(tǒng)提供了一鍵自動修復(fù)功能。

來源：網(wǎng)易云博客