业务代码访问数据库的昂贵查询及注意点

一、背景

业务开发时会遇到以下场景：

• 提供rpc查询接口，根据上游给的参数查询数据库
• 提供http查询接口，根据前端给的参数查询数据库
• 核心功能需要根据相关参数查询数据库用于逻辑判断等

数据库是脆弱的，将不知从何处来的参数一股脑塞到数据库进行查询，会造成数据库慢查询，慢查询过多会直接导致数据库cpu升高，连接池占满，直接带崩链接该数据库的所有服务。因此，我们需要对即将扔给数据库查询的参数进行校验，包含以下点

二、常见昂贵查询及解决方案

2.1 分页防止pageNumber和pageSize过大

        提供前端的列表接口一般需要分页，页数pageNumber越深，一页的记录数pageSize越大，查询越昂贵。因此要结合业务需要及特点，对分页参数进行范围校验

2.2 方式范围查询in的参数过多

        范围查询in的参数过多，查询越昂贵。如果上游业务使用10000个id来查询信息，如果我们直接使用10000个id来查询数据。一会导致查询时间长，二会导致一次性返回多条记录，占带宽。有两种解决办法一是限制上游的参数size大小，太多的参数拒绝访问，二是代码内部进行参数分割，比如1000个id，分成十次，每次100个id去查询。

三、常见注意点

3.1 mybatis查询list为空时报错

        比较常见，in范围查询时，传入的参数为empty，相应DML如下时，mysql服务端会报出异常，在查询时先行判断，如果in查询的参数为empty时直接返回，不要扔到数据库查询

select * from student where student_id in ();

3.2 update的时候new一个新对象（Mybatis）

        通过mybatis更新数据库，通常会使用如下的接口，对应的DML为：

mapper.updateByPrimaryKeySelective(record);

update table set xxx where id = x;

        该接口根据record中的id信息作为筛选信息，将record中非空的属性设置到id=record.id的记录中，例如以下的record及实际上执行的DML操作是：

record（id=5，sex=1，age=15）
update table set sex=1 and age=15 where id = 5;

业务中对记录进行更新时，我们会先查询出原记录：

record(id =5, sex=0, age=14, hobbies='dance')

如果不new 新对象，直接使用record进行upate操作，java代码如下，实际上进行的DML操作是：

Table table = record;

record.setSex(1);

record.setAge(15);

mapper.updateByPrimaryKeySelective(record);

update table set sex=1 and age=15 and hobbies ='dance' where id = 5;

        注意这里的问题是，hobbies不是我们要改的属性，但是因为复用select出的对象进行更新，导致 hobbies ='dance'赋值操作，这里会引入并发问题，如果线程A更改age=15，线程B更改hobbies ='sing'，线程A及线程B同时读取出id=5的record，B先将hobbies ='sing'操作完成，那么A再进行上述代码操作时会覆盖掉B的更改。

因此update时应该new 新对象进行id及想要更改的属性赋值，java代码如下，实际上进行的DML操作是：

Table table = new Table();

table.setId(record.getId())

record.setSex(1);

record.setAge(15);

mapper.updateByPrimaryKeySelective(record);

update table set sex=1 and age=15 where id = 5;

3.3.不要赋与非业务字段业务意义

updated_time 更新字段、id 主键id、created_time 记录创建时间都是没有业务含义的字段，不要赋予非业务字段业务含义，比如：

审批表中的更新时间，不能代表最近的审批时间，只能代表这条记录的更新时间，如果依赖这个字段为业务含义「最近的审批时间」，当这张表刷数据更新所有更新时间时，该字段的业务属性会失真。需要的话另外创建最近的审批时间字段，用于业务功能实现。

学生表的id字段，不能用来当作学号，数据表进行迁移时，同记录的id可能会更改，导致学生信息与学号的映射关系会丢失。