關(guān)于醫(yī)院數(shù)據(jù)庫鎖表優(yōu)化淺談

南京市口腔醫(yī)院 江蘇 南京 210018

1 研究背景

1.1 業(yè)務(wù)背景

鑒于我院的數(shù)據(jù)庫在2020年4月12日及4月13日兩天接連鎖表，且鎖表時(shí)間段均為非業(yè)務(wù)高峰期。我們對(duì)數(shù)據(jù)庫環(huán)境進(jìn)行了為期一個(gè)月的監(jiān)控以及相關(guān)優(yōu)化。

1.2 技術(shù)背景

眾所周知，數(shù)據(jù)庫健康狀態(tài)最為明顯的體現(xiàn)就在于I/O讀寫的速度快慢，索引所使用的磁盤空間越大則sql語句運(yùn)行的就更為緩慢，當(dāng)運(yùn)行緩慢的sql產(chǎn)生了阻塞及互相等待的時(shí)候就會(huì)造成數(shù)據(jù)庫的鎖表現(xiàn)象，鎖表主要分為以下類型：

（1）共享鎖。S鎖，也叫讀鎖，用于所有的只讀數(shù)據(jù)操作。共享鎖是非獨(dú)占的，允許多個(gè)并發(fā)事務(wù)讀取其鎖定的資源[1]。

（2）排他鎖。X鎖，也叫寫鎖，表示對(duì)數(shù)據(jù)進(jìn)行寫操作。如果一個(gè)事務(wù)對(duì)對(duì)象加了排他鎖，其他事務(wù)就不能再給它加任何鎖了。

（3）更新鎖。U鎖，在修改操作的初始化階段用來鎖定可能要被修改的資源，這樣可以避免使用共享鎖造成的死鎖現(xiàn)象。

2 問題分析

鎖表當(dāng)日對(duì)數(shù)據(jù)庫情況進(jìn)行分析，優(yōu)先排除了HIS數(shù)據(jù)庫服務(wù)器壓力過大原因?qū)е碌逆i表。同時(shí)分析，HIS數(shù)據(jù)庫在相當(dāng)長的一段時(shí)間內(nèi)并未發(fā)生鎖表問題，懷疑是由于業(yè)務(wù)科室偶發(fā)性的跨大時(shí)間段的數(shù)據(jù)查詢，引起數(shù)據(jù)庫超負(fù)荷，導(dǎo)致數(shù)據(jù)庫鎖表。問題發(fā)生時(shí)的緊急處理方式是殺掉引起鎖表進(jìn)程。除此之外并沒有更好的方式。為了預(yù)防和解決此類現(xiàn)象我們對(duì)該數(shù)據(jù)庫進(jìn)行了分析和處理[2]。

3 查找原因

3.1 原因分析以及針對(duì)性處理

通過實(shí)時(shí)數(shù)據(jù)查找，發(fā)現(xiàn)mz_visit_tale，mz_detail_charge，gh_detail_charge等業(yè)務(wù)主表的數(shù)據(jù)量過大，最大數(shù)據(jù)量達(dá)到3000w。已經(jīng)對(duì)相關(guān)臨床科室的使用造成影響，我們進(jìn)行了即時(shí)處理，并通過后臺(tái)進(jìn)行數(shù)據(jù)遷移，將一周前的數(shù)據(jù)遷移至相應(yīng)的B表中保存。

3.2 大跨度數(shù)據(jù)查詢導(dǎo)致的共享鎖

在進(jìn)行完第一階段的處理過后，對(duì)數(shù)據(jù)庫I/O讀寫情況進(jìn)行監(jiān)控，分析4月24日高峰期數(shù)據(jù)結(jié)構(gòu)，當(dāng)日的I/O讀寫在3-4w，5-6w，10-20w，100w+的數(shù)據(jù)量過高，優(yōu)先處理可能會(huì)導(dǎo)致鎖表的I/O較高的語句，優(yōu)化方式如下：建立C表，將B表的數(shù)據(jù)定期轉(zhuǎn)移至C表中保存，保證數(shù)據(jù)不丟失。但是此操作有可能導(dǎo)致各個(gè)職能科室的數(shù)據(jù)調(diào)取出現(xiàn)問題。針對(duì)此問題我們建立了備份庫進(jìn)行跨月跨年的大數(shù)據(jù)查詢。防止在業(yè)務(wù)高峰期進(jìn)行的查詢與醫(yī)院日常業(yè)務(wù)撞車導(dǎo)致的鎖表問題。對(duì)整個(gè)數(shù)據(jù)庫的業(yè)務(wù)主表進(jìn)行索引重構(gòu)，降低掃描開銷，增加掃描

3.3 各種接口導(dǎo)致的排它鎖處理

在第一階段處理完成之后我們?cè)?月29日對(duì)數(shù)據(jù)庫進(jìn)行同期I/O檢測,分析可以看出5-6w，10-20w的語句數(shù)量依舊較大，分析有關(guān)語句并對(duì)其進(jìn)行優(yōu)化，優(yōu)化策略如下：對(duì)自助機(jī)，院長報(bào)表查詢中的語句使用大數(shù)據(jù)量視圖修改為小數(shù)據(jù)量的業(yè)務(wù)表，減少每次抓取的數(shù)據(jù)范圍。對(duì)自助機(jī)，各種APP的掛號(hào)，取號(hào)的相關(guān)存儲(chǔ)過程進(jìn)行修改，充分利用現(xiàn)有表結(jié)構(gòu)中的索引以及主鍵，減少表掃描和鍵值查詢所用的I/O開銷[3]。

3.4 結(jié)構(gòu)問題優(yōu)化

在第二次優(yōu)化處理之后，又對(duì)4月30日的數(shù)據(jù)庫情況進(jìn)行跟蹤并分析，可以得出5-6w的數(shù)據(jù)量依舊沒有優(yōu)化，又對(duì)這個(gè)區(qū)間的sql語句進(jìn)行相關(guān)的分析，做出如下策略：檢查相關(guān)的表結(jié)構(gòu)做優(yōu)化，對(duì)相關(guān)表建立如下非聚集索引以減少表掃描的相關(guān)I/O開銷。

3.5 更新鎖的分析處理

在上次優(yōu)化之后，又對(duì)數(shù)據(jù)庫健康狀態(tài)進(jìn)行持續(xù)的跟蹤，并在5月18日發(fā)現(xiàn)了相關(guān)的數(shù)據(jù)庫異常狀態(tài)得出，3-4w，10-20w的數(shù)據(jù)量異常較多，對(duì)相關(guān)語句進(jìn)行分析后發(fā)現(xiàn)問題主要出現(xiàn)在微信掛號(hào)，以及起航相關(guān)數(shù)據(jù)抓取的語句上，做出相關(guān)修改。首先對(duì)表jd_yyhh建立相關(guān)非聚集索引，以減少APP掛號(hào)取號(hào)的相關(guān)表掃描開銷。并為修改相關(guān)接口增加update時(shí)的運(yùn)行效率，其次優(yōu)化APP取號(hào)的存儲(chǔ)過程，將表gh_receipt中的索引條件利用起來。經(jīng)過一系列的優(yōu)化操作之后，南京市口腔醫(yī)院的數(shù)據(jù)庫健康狀態(tài)已經(jīng)達(dá)標(biāo)。此后一直又對(duì)數(shù)據(jù)庫健康狀態(tài)進(jìn)行持續(xù)的跟蹤，至今關(guān)于我院的數(shù)據(jù)優(yōu)化工作已經(jīng)取得了實(shí)質(zhì)性的進(jìn)展[4]。

4 結(jié)束語

此次數(shù)據(jù)庫鎖表事件主要原因在于忽略了對(duì)數(shù)據(jù)庫健康情況的檢測，此次事件無疑為我們敲響了警鐘。此后在日常維護(hù)的過程中一定要做到經(jīng)常查看，出現(xiàn)問題及時(shí)響應(yīng)。在進(jìn)行sql語句編寫的時(shí)候一定要進(jìn)行語句分析，開銷計(jì)劃分析，對(duì)錯(cuò)爛語句要及時(shí)發(fā)現(xiàn)及時(shí)處理，減少對(duì)于數(shù)據(jù)庫造成的影響。對(duì)數(shù)據(jù)庫數(shù)據(jù)的數(shù)量級(jí)要進(jìn)行及時(shí)監(jiān)測，防止業(yè)務(wù)表的數(shù)據(jù)過大導(dǎo)致的表鎖或者運(yùn)行緩慢。