原创 得物技术 – 胡志敏
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 更新技术干货
要是觉得文章对你有帮助的话,欢迎评论转发点赞~