Db层治理：SQL精细化及并发更新数据丢失问题解决最佳实践 ｜ 得物技术

1.背景

1.1 概述

1.1.1 SQL精细化提出背景

目前使用比较广泛的数据库持久层框架有两个，JPA和Mybatis，先来简单比较一下这两种框架。

从选择上来看，和业务场景是有关系的，迭代比较频繁、复杂性较高，配合MYSQL数据库，我们的数据库持久层选用的是Mybatis,Mybatis的一个很重要的特性就是开发可以自己写SQL，或者借助自动生成工具生成SQL，在实际的开发工作中，没有统一的规范，我们习惯于SQL Mapper文件中的通用的或者万能的查询、更新、插入等方法上叠加自己的增删改查操作，随着业务的场景增多，SQL的复杂性也会随着上升，那么在开发完成开发之后，一个可以review的SQL是我们迫切需要的，大家在CR代码的时候，可以很快的发现SQL中可能存在的一些问题，比如慢SQL、是否走到索引、走了哪个索引等。SQL质量的好坏将会影响整体应用的性能以及整体链路的压力，所以对于SQL的精细化管理是很有必要的一项工作。

1.1.2 并发更新导致数据丢失问题

我们的数据库表结构设计的时候，往往会预留一个feature字段，很多的业务增值信息都会存放到这个字段，以一个大json存储，如果我们在各种场景对同一条记录的这个字段更新，如果没有做到并发控制，那么则会导致数据被覆盖，形成脏数据。

针对1.1.1和1.1.2两个问题，结合我们的业务，针对db层的治理，尝试给出一些建议。

1.2 SQL精细化管理的收益

SQL精细化管理作为解决慢SQL的一个重要组成部分，可以带来如下收益：

（1）如果Mybatis的SQL Mapper无法有效的进行review，那么其中存在的风险，我们第一时间是无法直观感知的，我们需要可review的SQL，将风险提前暴露

（2）解决慢SQL问题，提高查询效率，提升用户体验及提高系统吞吐

（3）避免潜在OOM风险，导致应用崩溃

（4）避免慢SQL问题（慢SQL消耗的资源往往比正常SQL要高几倍、几十倍、几百倍）导致MYSQL崩溃，拖垮整个数据库，降低CPU使用率

（5）解决代码及SQL缺陷问题，可以提升我们订单服务的稳定性

1.3  并发更新导致数据丢失问题现象产生的原因

例如有如下SQL更新feature字段：

<update id="update"
        parameterType="OrderEntity">
    update table_order
    set feature = #{feature,jdbcType=VARCHAR}
    where order_no = #{orderNo,jdbcType=VARCHAR}
</update>

当存在两个线程同时更新，则会出现以下情况：

我们最终的期望结果是：

JSON:

a=1;b=1;c=1;

那么随着Thread-1和Thread-2的先后顺序不同，我们得到的结果有可能会是:

a=1;b=1;

或：

a=1;c=1;

所以就出现了脏数据，并不是我们最终想要的。

2.SQL精细化最佳实践

2.1 不要使用工具自动生成的通用SQL

这种SQL是自动生成的，SQL条件也是拼接的，需要在程序中去拼接查询条件或者更新值，我们也看不出执行的SQL到底是什么样的，不能直观看到具体的查询SQL是什么，需要在代码中一层层往上找，找到赋值的地方，如：

<select id="selectByExample" parameterType="OrderExample" resultMap="BaseResultMap">
  select
  <if test="distinct">
    distinct
  </if>
  <include refid="Base_Column_List" />
  from table_order
  <if test="_parameter != null">
    <include refid="Example_Where_Clause" />
  </if>
  <if test="orderByClause != null">
    order by ${orderByClause}
  </if>
</s