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

532次阅读 | 发布于2年以前

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>
</select>

2.2 必须清晰看到索引情况

为了避免我们的SQL执行全表扫描（对于InnoDB来说就是直接扫描全部的聚簇索引记录），我们需要让我们的SQL走到我们设定的索引上，减少执行查询时遍历的数据行数，提升查询性能。SQL map里面要能一眼看出这条SQL是否走了索引，具体走了哪些索引。比如下面的SQL，就不能明确看出走了哪个索引。


<select id="selectOrderList"
            parameterType="OrderEntity"
            resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List_2_Report"/>
        from table_order ro
        <where>
            <if test="orderNo != null">
                and ro.order_no = #{orderNo}
            </if>
            <if test="subOrderNo != null">
                and ro.sub_order_no = #{subOrderNo}
            </if>
            <if test="refundOrderNo != null">
                and ro.refund_order_no = #{refund_order_no}
            </if>
            <if test="startTime != null and endTime != null">
                and ro.create_time between #{startTime} and #{endTime}
            </if>
        </where>
        order by ro.create_time asc
        LIMIT #{offSet},#{limit}
    </select>

2.3 非必需不要使用动态SQL

因为Mybatis提供的动态SQL功能，可以很便捷的拼装SQL，但也有弊端：

（1）同时也会将查询数据库的参数交易后置到Mapper中，如果出现遗漏，或者校验不正确，使用动态SQL极有可能存在内存溢出隐患，导致OOM

（2）代码可读性差。

要求：在新增的代码不要出现动态SQL，索引字段不要动态，需要明确，例如下面一种场景就是索引不明确，refund_no、sub_order_no、order_no三个字段都可能走索引：


<select id="getDetail"  parameterType="OrderEntity" resultMap="BaseResultMap">
  select <include refid="Base_Column_List" />
  from table_order
  <where>
    <if test="refund_No != null and refundNo != ''">
      and refund_No =#{refundNo}
    </if>
    <if test="subOrderNo != null and subOrderNo != ''">
      and sub_order_no =#{subOrderNo}
    </if>
    <if test="orderNo != null and orderNo != ''">
      and order_no =#{orderNo}
    </if>
  </where>
</select>

（3）因为订单都是比较重数据的需要提供一些查询类的接口给到外部，比如客服、商家后台等，这类偏向于B端的查询，很多时候查询条件是很多的，也是可选的，为了支持这些查询接口，我们需要明确哪些是必传字段，必传字段在dao层就需要强制校验，mapper层不再做校验。例如我们针对商家接口查询，一般要求卖家id、时间范围都是必须传值的。

2.4 update操作，需要明确更新字段，

不要传po对象作为参数，

在SQL mapper中进行if判断

更新字段要明确，不要在mapper中做if判断，正例：


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

反例：

<update id="update"
        parameterType="OrderEntity">
    update table_order
    <set>
        <if test="orderNo != null">
            order_no = #{orderNo,jdbcType=VARCHAR},
        </if>
        <if test="subOrderNo != null">
            sub_order_no = #{subOrderNo,jdbcType=VARCHAR},
        </if>
        <if test="bizType != null">
            biz_type = #{bizType,jdbcType=VARCHAR},
        </if>
        <if test="refundType != null">
            refund_type = #{refundType,jdbcType=TINYINT},
        </if>
        <if test="orderStatus != null">
            order_status = #{orderStatus,jdbcType=INTEGER},
        </if>
    </set>
    where order_no = #{orderNo,jdbcType=VARCHAR}
</update>

2.5 order by排序问题

需要注意两点：

① 如果业务对记录结果是否排序无要求，尽量不要用order by排序，排序比较耗时。

② 因为innodb的索引结构是B+树，索引中的数据是按照一定顺序进行排列的，在排序查询过程中，需要利用索引，避免额外的排序，即order by字段需要走到索引。

2.5.1 order by join查询

在join查询中，oder by排序，查看执行计划，避免Extra 字段有“Using filesort”出现，“Using filesort”表示的就是需要文件排序，如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。要尽量避免这种Using Sort出现。

2.5.2 order by 多个条件排序查询

当odrer by多个字段的时候，容易导致使用文件排序，不要轻易的写order by 多个字段这样的SQL，去掉不需要的order by字段，只保留一个必须的。

2.6 limit的使用

当我们的查询SQL，没有走到唯一索引或者主键索引，或者传入空参数，那么返回极有可能是多条数据，甚至几十万，几百万条数据，这时候，我们需要加limit限制，一定要有兜底，避免发生大批量的数据查询，全表扫描情况出现，造成查询超时，或者查询返回大量数据，使应用内存急剧上升，导致oom。如果条件中带有主键查询的，直接使用limit 1 。

**2.7 不允许select *全部字段**

我们尽量查询我们需要输出的字段或者需要用到字段，不要查询没必要输出的字段，需要查询的字段可以单独的列出来，一律不允许用select *查询。

HTML:

<SQL id="Base_Column_List">
  order_no, sub_order_no, user_id, biz_type, pay_tool, pay_status, out_pay_no
</SQL>

有三个方面原因：

① 一个是查询只需要的字段如果是索引，那么可以直接使用index里面的数据就可以返回，不用做回表查询

② 数据量大的场景会影响网络io传输性能

③ 可能会造成大对象

2.8 join查询

① 能不能join查询，尽量不要要join查询

② 必须用join查询的，在做join查询的时候，要注意驱动表的选择，一般我们选用小表作为驱动表，被驱动表可以走索引。

2.9 范围查询

对于需要提供一段时间范围查询的接口，我们需要对这个时间范围进行限制，不能过大，如果超出范围，要提前抛异常，一定要进行强制校验，比如针对时间范围的数据查询，一般根据数据量，确定一个查询时间范围，范围太大，会造成慢SQL。

2.10 联合索引问题

对于使用到联合索引查询SQL的时候，我们需要注意联合索引的顺序，一般情况下，SQL执行的时候，会根据在前面顺序在前面的先进行过滤，我们的期望是顺序越在前面的索引可以过滤掉越多的数据，这样层层推进，各列应按按量排序。

我们希望排在前面的字段可以过滤掉绝大部分数据。联合索引选择性高的索引排在前面，减少扫描行数。

2.11 in查询

in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。一定要加强制校验。如下面的查询条件中的subOrderNoList需要在外层做大小校验。特别是在分库分表场景下，为了提升查询效率，很有必要对in查询条件数量做限制，因为分库分表场景下，数据是散落在不同，需要去对应数据库中读取数据再做聚合返回，效率比较低。

HTML:

<select id="queryDetail" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from table_order
    where order_no in
    <foreach collection="orderNoList" open="(" close=")" separator="," item="orderNo" index="i">
        #{orderNo}
    </foreach>
    order by create_time desc
    limit 20
</select>

2.12 update操作之前做select查询

在做update操作之前尽量先查询以下，避免不必要的数据库交互操作。

2.13 分页查询大小限制

分页查询pagesize大小要加强制校验，超过限定值，直接异常返回。

2.14 超大分页场景

MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行控制，offset和pageSize需要做限制

select * from table_order LIMIT #{offset}, #{pageSize};

2.15 批量插入

对于批量插入数据操作，我们希望可以一次执行，避免多次重复数据库交互操作。

反例：

Java：



public void batchSave(List<TableOrder> orderEntityList) {
    if (CollectionUtil.isEmpty(orderEntityList)) {
        return;
    }
    for (OrderEntity orderEntity : orderEntityList) {
        save(orderEntity);
    }
}

正例：

SQL:

INSERT into table_order(cloum1,cloum2,....cloumx) values('?','?',....'?'),('?','?',....'?'),('?','?',....'?');

2.16 like 语句的优化

%%这种匹配方式完全无法使用索引，从而进行全表扫描导致效率极低，需要通过走到索引查找数据

2.17 不要在程序中拼接查询条件

Java：


public Criteria andBizTypeIn(List<String> values) {
    addCriterion("biz_type in", values, "bizType");
    return (Criteria) this;
}

2.18 select count问题

统计查询一律使用select count(*)，按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)

2.19 删除mapper中不需要的操作

例如一些delete物理删除这些操作方法可以清理掉。这些方法都是通过自动生成工具生成的，因为正常情况下，我们一般不会对数据库数据做物理删除。

2.20 影响行数结果判断

对于任何update更新操作，都需要判断返回值，即影响行数。在进行update操作的时候，正常情况下，数据库返回影响行数，如果数据库中没有需要进行update的数据，也是不报错的，这就需要我们在应用层自己进行判断，如果更新行数是0，或者多于1条，我们的业务处理逻辑该怎么走。

Java：



int effect = repository.update(param1,param2)
if (effect < 1){
    throw new Exception("update操作执行失败");
}

2.21 list.get(0）

对于任何list.get(0)，都需要是排序之后的结果。例如我们在dao层查询的时候，是用list查询，返回列表，也没有进行排序，在controller层直接get(0),很容易取错数据。

2.22 list返回

对于任何查询，只要是where条件中没有主键或唯一健，都需要用list返回，不能简单的根据场景来认为数据库中只可能有一条记录，容易引起Too Many results error。存在一些场景，比如，创建退货，然后取消退货，再创建退货，再取消退货，根据订单号来查询可以查询多条记录，但我们只需要最新的一条，没有做limit限制，就会异常。

3.并发更新覆盖数据解决最佳实践

一般解决这种并发导致数据的方法有悲观锁和乐观锁两种机制。

3.1 悲观锁

使用悲观锁的原理就是，当我们在查询出feature信息之后，就会把当前行数据进行锁定，然后去修改feature字段，直到我们修改数据提交完毕之后，其他事物才可以进行修改，前提是我们需要将执行的SQL放在同一个事物中，否则达不到锁定行数据的目的。


start transaction
select feature from table_order where order_no = '100' for update;
update table_order set feature = new_feature where order_no = '100';
commit

select...for update是MySQL提供的实现悲观锁的方式。此时在refund_order表中，refund_no为100的那条数据就被我们锁定了，其它的要执行的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

select feature from table_order where order_no = '100' for update;

悲观锁虽然可以强保证数据不会被覆盖，但也存在一些问题，比如：

（1）事物粒度控制不好的情况下，容易产生死锁

（2）InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。如果SQL未走到索引，将会带来灾难性事件。

（3）悲观锁依赖数据库，效率较低，但更新失败的概率也较低，一般在金融、支付行业中使用相对较多。在目前行业内的高并发架构中，倾向于fail-fast模式，悲观锁相对使用较少。

3.2 乐观锁

乐观锁相对于悲观锁而言，认为数据在一般情况下不会造成冲突，在数据进行提交更新的时候，才会正式对数据的冲突进行检测，如果冲突，则失败返回。

3.2.1 全局version乐观锁

乐观锁一般常用的实现机制是利用版本号（version）来实现。一般通过为数据库表增加一个数字类型的 “version” 字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值+1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败。

执行过程如下：

SQL:

select (feature,version) from table_order where refund_no='100';
update table_order set feature=new_feature,version=version+1 where table_order ='100' and version= version;

我们可以看到若采用版本号version作为乐观锁，则每次只有一个事务能更新成功，业务感知上就是大量操作失败。

3.2.2 feature_version版本字段

因为version字段是全局的，利用全局字段version进行处理，则会发现refund_order表的其他字段变更也会失败。那么如何解决这个问题？业内的做法是从锁的粒度上进行优化，比如针对feature字段专门新增一个feature_version字段，表示feature的版本号，很好的避免了version对所有字段变更的冲突。执行SQL如下：


update    
    table_order
set    
    features=#features#,    
    features_version= features_version +1,
    version = version +1
where    
    order_no=#order_no#    
    and features_version =#ori_features_version#

通过3.2.1和3.2.2对比，我们可以清晰的发现，3.2.2中方案是较优的，在我们的实际业务中也是采取这一方式进行了改造。

4.总结与展望

前面已经谈到，对于订单系统的核心来说就是数据，那么对于我们应用来说，就是对这些数据进行增删改查操作，对Db层进行梳理和改造，对我们业务数据的正确性以及应用性能提升是有很大帮助的，然而，Db层的治理工作，对于一个应用来说，有牵一发而动全身的影响，针对这方面的改造需要关注以下几个点：

（1）测试需要充分，测试的场景需要梳理全面，可以从Mapper层反推涉及的场景接口，因为涉及Mapper层的改动，是没办法进行对比的。

（2）在没有全流量对比工具的前提下，需要做好灰度切流工作，在灰度粒度上，可以选择按接口维度，或者按机器维度，也可以开始按接口维度，等所有场景覆盖全之后，再按照机器灰度。如果订单在夜间、白天处理逻辑不一样，我们灰度需要考虑到隔夜观察，也可以利用一些辅助工具例如对账等。

（3）Db层治理工作一直在路上，完成全面梳理改造只是迈出的第一步，一个好的规范如何进行推进下去，让大家保持统一的规范，随着时间的推移，这种规范能够继续保持不走样，是我们需要思考的问题。

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

1.背景

1.1 概述

1.1.1 SQL精细化提出背景

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

1.2 SQL精细化管理的收益

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

2.SQL精细化最佳实践

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

2.2 必须清晰看到索引情况

2.3 非必需不要使用动态SQL

2.4 update操作，需要明确更新字段，

不要传po对象作为参数，

在SQL mapper中进行if判断

2.5 order by排序问题

2.5.1 order by join查询

2.5.2 order by 多个条件排序查询

2.6 limit的使用

2.7 不允许select *全部字段

2.8 join查询

2.9 范围查询

2.10 联合索引问题

2.11 in查询

2.12 update操作之前做select查询

2.13 分页查询大小限制

2.14 超大分页场景

2.15 批量插入

2.16 like 语句的优化

2.17 不要在程序中拼接查询条件

2.18 select count问题

2.19 删除mapper中不需要的操作

2.20 影响行数结果判断

2.21 list.get(0）

2.22 list返回

3.并发更新覆盖数据解决最佳实践

3.1 悲观锁

3.2 乐观锁

3.2.1 全局version乐观锁

3.2.2 feature_version版本字段

4.总结与展望

**2.7 不允许select *全部字段**