• 151983

    文章

  • 1250

    评论

  • 10

    友链

  • 最近新加了换肤功能,大家多来逛逛吧~~~~
  • 喜欢这个网站的朋友可以加一下QQ群,我们一起交流技术。

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

 

*文/胡志敏

关注得物技术,每周一三五晚 18:30 更新技术干货
要是觉得文章对你有帮助的话,欢迎评论转发点赞~


695856371Web网页设计师②群 | 喜欢本站的朋友可以收藏本站,或者加入我们大家一起来交流技术!

自定义皮肤 主体内容背景
打开支付宝扫码付款购买视频教程
遇到问题联系客服QQ:419400980
注册梁钟霖个人博客