关于医院数据库锁表优化浅谈

南京市口腔医院 江苏 南京 210018

1 研究背景

1.1 业务背景

鉴于我院的数据库在2020年4月12日及4月13日两天接连锁表，且锁表时间段均为非业务高峰期。我们对数据库环境进行了为期一个月的监控以及相关优化。

1.2 技术背景

众所周知，数据库健康状态最为明显的体现就在于I/O读写的速度快慢，索引所使用的磁盘空间越大则sql语句运行的就更为缓慢，当运行缓慢的sql产生了阻塞及互相等待的时候就会造成数据库的锁表现象，锁表主要分为以下类型：

（1）共享锁。S锁，也叫读锁，用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源[1]。

（2）排他锁。X锁，也叫写锁，表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。

（3）更新锁。U锁，在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。

2 问题分析

锁表当日对数据库情况进行分析，优先排除了HIS数据库服务器压力过大原因导致的锁表。同时分析，HIS数据库在相当长的一段时间内并未发生锁表问题，怀疑是由于业务科室偶发性的跨大时间段的数据查询，引起数据库超负荷，导致数据库锁表。问题发生时的紧急处理方式是杀掉引起锁表进程。除此之外并没有更好的方式。为了预防和解决此类现象我们对该数据库进行了分析和处理[2]。

3 查找原因

3.1 原因分析以及针对性处理

通过实时数据查找，发现mz_visit_tale，mz_detail_charge，gh_detail_charge等业务主表的数据量过大，最大数据量达到3000w。已经对相关临床科室的使用造成影响，我们进行了即时处理，并通过后台进行数据迁移，将一周前的数据迁移至相应的B表中保存。

3.2 大跨度数据查询导致的共享锁

在进行完第一阶段的处理过后，对数据库I/O读写情况进行监控，分析4月24日高峰期数据结构，当日的I/O读写在3-4w，5-6w，10-20w，100w+的数据量过高，优先处理可能会导致锁表的I/O较高的语句，优化方式如下：建立C表，将B表的数据定期转移至C表中保存，保证数据不丢失。但是此操作有可能导致各个职能科室的数据调取出现问题。针对此问题我们建立了备份库进行跨月跨年的大数据查询。防止在业务高峰期进行的查询与医院日常业务撞车导致的锁表问题。对整个数据库的业务主表进行索引重构，降低扫描开销，增加扫描

3.3 各种接口导致的排它锁处理

在第一阶段处理完成之后我们在4月29日对数据库进行同期I/O检测,分析可以看出5-6w，10-20w的语句数量依旧较大，分析有关语句并对其进行优化，优化策略如下：对自助机，院长报表查询中的语句使用大数据量视图修改为小数据量的业务表，减少每次抓取的数据范围。对自助机，各种APP的挂号，取号的相关存储过程进行修改，充分利用现有表结构中的索引以及主键，减少表扫描和键值查询所用的I/O开销[3]。

3.4 结构问题优化

在第二次优化处理之后，又对4月30日的数据库情况进行跟踪并分析，可以得出5-6w的数据量依旧没有优化，又对这个区间的sql语句进行相关的分析，做出如下策略：检查相关的表结构做优化，对相关表建立如下非聚集索引以减少表扫描的相关I/O开销。

3.5 更新锁的分析处理

在上次优化之后，又对数据库健康状态进行持续的跟踪，并在5月18日发现了相关的数据库异常状态得出，3-4w，10-20w的数据量异常较多，对相关语句进行分析后发现问题主要出现在微信挂号，以及起航相关数据抓取的语句上，做出相关修改。首先对表jd_yyhh建立相关非聚集索引，以减少APP挂号取号的相关表扫描开销。并为修改相关接口增加update时的运行效率，其次优化APP取号的存储过程，将表gh_receipt中的索引条件利用起来。经过一系列的优化操作之后，南京市口腔医院的数据库健康状态已经达标。此后一直又对数据库健康状态进行持续的跟踪，至今关于我院的数据优化工作已经取得了实质性的进展[4]。

4 结束语

此次数据库锁表事件主要原因在于忽略了对数据库健康情况的检测，此次事件无疑为我们敲响了警钟。此后在日常维护的过程中一定要做到经常查看，出现问题及时响应。在进行sql语句编写的时候一定要进行语句分析，开销计划分析，对错烂语句要及时发现及时处理，减少对于数据库造成的影响。对数据库数据的数量级要进行及时监测，防止业务表的数据过大导致的表锁或者运行缓慢。