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

发表于 2年以前 | 总阅读数：509 次

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中方案是较优的，在我们的实际业务中也是采取这一方式进行了改造。