确认
当前程序版本
3.5.7
问题描述
环境：
MP版本 3.5.7，临时升级到3.5.9也一样 JDK17 DM数据库 驱动DmJdbcDriver18 springboot 3.1.4
场景一
数据库2000w，单字段in查询，数据库有idcard索引 异常： 耗时：1分钟30秒
LambdaQueryWrapper<CenterDiseaseDetail> wrapper = new LambdaQueryWrapper<>();
wrapper.in(CenterDiseaseDetail::getIdcard, idcardList);
wrapper.select(CenterDiseaseDetail::getIdcard, CenterDiseaseDetail::getCode);
long l = System.currentTimeMillis();
List<CenterDiseaseDetail> diseaseDetailList = serviceMap.getService(CenterDiseaseDetailService.class).findList(wrapper);
LogPrintUtils.log("aa", l);
sql打印：
SELECT idcard,code FROM center_disease_detail WHERE (idcard IN ('DEMO00195403160202','DEMO00197301071002','DEMO00194805140201','DEMO00195001110803','DEMO00195008210801','DEMO00196809171001','DEMO00196409270401','DEMO00194107220501'))
正常： 使用xml方式正常，耗时：70 ms
List<CenterDiseaseDetail> diseaseDetailList = serviceMap.getService(CenterDiseaseDetailService.class).find(idcardList);
<select id="find" resultType="com.kairui.aimb.model.center.disease.CenterDiseaseDetail">
select idcard,code
from center_disease_detail
where idcard IN
<foreach collection="idcardList" item="idcard" separator="," open="(" close=")">
#{idcard}
</foreach>
</select>
sql打印：
select idcard,code from center_disease_detail where idcard IN ( 'DEMO00195403160202' , 'DEMO00197301071002' , 'DEMO00194805140201' , 'DEMO00195001110803' , 'DEMO00195008210801' , 'DEMO00196809171001' , 'DEMO00196409270401' , 'DEMO00194107220501' )
场景二
数据库2000w，数据库有pid索引，查询条件拼接顺序影响索引生效 异常： 耗时：15秒
long l = System.currentTimeMillis();
QueryWrapper<CenterFollowHbpDrug> wrapper = new QueryWrapper<>();
wrapper.lambda()
.eq(CenterFollowHbpDrug::getStatus, 0)
.eq(CenterFollowHbpDrug::getPid, "2016070100899052_542101");
List<CenterFollowHbpDrug> tempList = serviceMap.getService(CenterFollowHbpDrugService.class).findList(wrapper);
LogPrintUtils.log("" + tempList.size(), l);
sql打印：
SELECT id,hr_id,name,spec,num,unit,frequency,way,dosage_per_use,dosage_unit_per_use,remarks,pid,status,utime,utimestamp,day FROM center_follow_hbp_drug WHERE (status = 0 AND pid = '2016070100899052_542101')
正常：耗时：99 ms
long l = System.currentTimeMillis();
QueryWrapper<CenterFollowHbpDrug> wrapper = new QueryWrapper<>();
wrapper.lambda()
.eq(CenterFollowHbpDrug::getPid, "2016070100899052_542101")
.eq(CenterFollowHbpDrug::getStatus, 0);
List<CenterFollowHbpDrug> tempList = serviceMap.getService(CenterFollowHbpDrugService.class).findList(wrapper);
LogPrintUtils.log("" + tempList.size(), l);
sql打印：
SELECT id,hr_id,name,spec,num,unit,frequency,way,dosage_per_use,dosage_unit_per_use,remarks,pid,status,utime,utimestamp,day FROM center_follow_hbp_drug WHERE (pid = '2016070100899052_542101' AND status = 0)
诉求：
MySql数据库测试过，没有出现这两个异常现象，以上任意SQL直接DM数据库查询都是正常速度， 使用MP框架代码执行会变得异常缓慢，想问的是需要做哪些全局配置才能修复不走索引的问题？
详细堆栈日志
场景一：
异常日志
2024-11-28T15:39:58.201+08:00 DEBUG 1111 --- [nio-8180-exec-1] c.k.a.m.c.d.C.selectList : ==> Preparing: SELECT idcard,code FROM center_disease_detail WHERE (idcard IN (?,?,?,?,?,?,?,?))
2024-11-28T15:39:58.220+08:00 DEBUG 1111 --- [nio-8180-exec-1] c.k.a.m.c.d.C.selectList : ==> Parameters: DEMO00195403160202(String), DEMO00197301071002(String), DEMO00194805140201(String), DEMO00195001110803(String), DEMO00195008210801(String), DEMO00196809171001(String), DEMO00196409270401(String), DEMO00194107220501(String)
2024-11-28T15:41:28.194+08:00 DEBUG 1111 --- [nio-8180-exec-1] c.k.a.m.c.d.C.selectList : <== Total: 72
2024-11-28 15:41:28.211：aa 耗时：1分钟30秒
正常日志
2024-11-28T15:50:01.415+08:00 DEBUG 1868 --- [nio-8180-exec-1] c.k.a.m.c.d.C.find : ==> Preparing: select idcard,code from center_disease_detail where idcard IN ( ? , ? , ? , ? , ? , ? , ? , ? )
2024-11-28T15:50:01.439+08:00 DEBUG 1868 --- [nio-8180-exec-1] c.k.a.m.c.d.C.find : ==> Parameters: DEMO00195403160202(String), DEMO00197301071002(String), DEMO00194805140201(String), DEMO00195001110803(String), DEMO00195008210801(String), DEMO00196809171001(String), DEMO00196409270401(String), DEMO00194107220501(String)
2024-11-28T15:50:01.467+08:00 DEBUG 1868 --- [nio-8180-exec-1] c.k.a.m.c.d.C.find : <== Total: 72
2024-11-28 15:50:01.484：aa 耗时：70 ms
场景二：
异常日志
==> Preparing: SELECT id,hr_id,name,spec,num,unit,frequency,way,dosage_per_use,dosage_unit_per_use,remarks,pid,status,utime,utimestamp,day FROM center_follow_hbp_drug WHERE (status = ? AND pid = ?)
==> Parameters: 0(Integer), 2016070100899052_542101(String)
<== Columns: ID, HR_ID, NAME, SPEC, NUM, UNIT, FREQUENCY, WAY, DOSAGE_PER_USE, DOSAGE_UNIT_PER_USE, REMARKS, PID, STATUS, UTIME, UTIMESTAMP, DAY
<== Row: a90e5c5e-399c-48f9-8252-8fd8d68d07f3, null, 门冬氨酸氨氯地平片, null, null, 8, null, po, qd, null, null, 2016070100899052_542101, 0, 2024-11-27 11:09:26.865000, 1732676966865, null
<== Row: af76bf3b-b50a-4934-a97e-7e4f3325be1b, null, 奥美沙坦酯片, null, null, 8, null, po, qd, null, null, 2016070100899052_542101, 0, 2024-11-27 11:12:34.894000, 1732677154894, null
<== Row: 701fba81-2d0e-49b4-890f-d24ee0cf77fa, null, 门冬氨酸氨氯地平片, null, null, 8, qd, po, 5, 8, null, 2016070100899052_542101, 0, 2024-11-27 18:25:45.687000, 1732703145687, null
<== Row: a72fcf87-e96d-4d94-8d06-6faade1bd8ca, null, 奥美沙坦酯片, null, null, 8, qd, po, 20, 8, null, 2016070100899052_542101, 0, 2024-11-27 18:30:34.070000, 1732703434070, null
<== Total: 4
2024-11-28 15:58:41.173：4 耗时：15秒
正常日志
==> Preparing: SELECT id,hr_id,name,spec,num,unit,frequency,way,dosage_per_use,dosage_unit_per_use,remarks,pid,status,utime,utimestamp,day FROM center_follow_hbp_drug WHERE (pid = ? AND status = ?)
==> Parameters: 2016070100899052_542101(String), 0(Integer)
<== Columns: ID, HR_ID, NAME, SPEC, NUM, UNIT, FREQUENCY, WAY, DOSAGE_PER_USE, DOSAGE_UNIT_PER_USE, REMARKS, PID, STATUS, UTIME, UTIMESTAMP, DAY
<== Row: a90e5c5e-399c-48f9-8252-8fd8d68d07f3, null, 门冬氨酸氨氯地平片, null, null, 8, null, po, qd, null, null, 2016070100899052_542101, 0, 2024-11-27 11:09:26.865000, 1732676966865, null
<== Row: af76bf3b-b50a-4934-a97e-7e4f3325be1b, null, 奥美沙坦酯片, null, null, 8, null, po, qd, null, null, 2016070100899052_542101, 0, 2024-11-27 11:12:34.894000, 1732677154894, null
<== Row: 701fba81-2d0e-49b4-890f-d24ee0cf77fa, null, 门冬氨酸氨氯地平片, null, null, 8, qd, po, 5, 8, null, 2016070100899052_542101, 0, 2024-11-27 18:25:45.687000, 1732703145687, null
<== Row: a72fcf87-e96d-4d94-8d06-6faade1bd8ca, null, 奥美沙坦酯片, null, null, 8, qd, po, 20, 8, null, 2016070100899052_542101, 0, 2024-11-27 18:30:34.070000, 1732703434070, null
<== Total: 4
2024-11-28 16:10:17.712：4 耗时：99 ms
Comment From: miemieYaho
那你不应该去问数据库吗?
Comment From: sgps000
问题是使用xml方式和数据库直接sql执行都没问题，使用Wrapper才出现的现象
Comment From: miemieYaho
你说xml没问题,那你xml出来的sql和wrapper出来的sql都长得不一样有什么可比性
Comment From: sgps000
一、所有的sql打印都是MP log插件里拷贝的，堆栈日志里的sql也贴出来了； 二、场景一中xml方式与Wrapper对比，唯一的区别就是Wrapper把where后面的加了括号； 三、场景二中sql唯一的区别就是 查询条件位置有前后区分； 四、又出现特殊情况，在场景一和场景二的异常情况代码中，加上wrapper.last("--")后，全都正常走索引了。
LambdaQueryWrapper<CenterDiseaseDetail> wrapper = new LambdaQueryWrapper<>();
wrapper.in(CenterDiseaseDetail::getIdcard, idcardList);
wrapper.select(CenterDiseaseDetail::getIdcard, CenterDiseaseDetail::getCode);
wrapper.last("--");
long l = System.currentTimeMillis();
List<CenterDiseaseDetail> diseaseDetailList = serviceMap.getService(CenterDiseaseDetailService.class).findList(wrapper);
LogPrintUtils.log("aa1-" + diseaseDetailList.size(), l);
LambdaQueryWrapper<CenterDiseaseDetail> wrapper2 = new LambdaQueryWrapper<>();
wrapper2.in(CenterDiseaseDetail::getIdcard, idcardList);
wrapper2.select(CenterDiseaseDetail::getIdcard, CenterDiseaseDetail::getCode);
l = System.currentTimeMillis();
diseaseDetailList = serviceMap.getService(CenterDiseaseDetailService.class).findList(wrapper2);
LogPrintUtils.log("aa2-" + diseaseDetailList.size(), l);
堆栈日志：
2024-11-28T19:11:28.490+08:00 DEBUG 19267 --- [nio-8180-exec-1] c.k.a.m.c.d.C.selectList : ==> Preparing: SELECT idcard,code FROM center_disease_detail WHERE (idcard IN (?,?,?,?,?,?,?,?)) --
2024-11-28T19:11:28.506+08:00 DEBUG 19267 --- [nio-8180-exec-1] c.k.a.m.c.d.C.selectList : ==> Parameters: DEMO00195403160202(String), DEMO00195008210801(String), DEMO00199100001501(String), DEMO00195001110803(String), DEMO00197301071002(String), DEMO00196809171001(String), DEMO00194305180301(String), DEMO00196409270401(String)
2024-11-28T19:11:28.524+08:00 DEBUG 19267 --- [nio-8180-exec-1] c.k.a.m.c.d.C.selectList : <== Total: 78
2024-11-28 19:11:28.538：aa1-78 耗时：50 ms
2024-11-28T19:11:28.539+08:00 DEBUG 19267 --- [nio-8180-exec-1] c.k.a.m.c.d.C.selectList : ==> Preparing: SELECT idcard,code FROM center_disease_detail WHERE (idcard IN (?,?,?,?,?,?,?,?))
2024-11-28T19:11:28.554+08:00 DEBUG 19267 --- [nio-8180-exec-1] c.k.a.m.c.d.C.selectList : ==> Parameters: DEMO00195403160202(String), DEMO00195008210801(String), DEMO00199100001501(String), DEMO00195001110803(String), DEMO00197301071002(String), DEMO00196809171001(String), DEMO00194305180301(String), DEMO00196409270401(String)
2024-11-28T19:12:43.914+08:00 DEBUG 19267 --- [nio-8180-exec-1] c.k.a.m.c.d.C.selectList : <== Total: 78
2024-11-28 19:12:43.930：aa2-78 耗时：1分钟15秒
Comment From: sgps000
你说xml没问题,那你xml出来的sql和wrapper出来的sql都长得不一样有什么可比性
就是因为MP的Wrapper输出的sql和直接写xml的不一致导致查询速度不一致才来反馈问题的，不然我就认为是数据库的问题了。我想知道的是MP是否有什么特殊机制会造成这种现象，有没有全局配置可以修复？
Comment From: miemieYaho
你只用mybatis且在xml写SQL的格式和wrapper生成出来的一样的情况下速度有区别你才能说是mp有问题懂吗？
Comment From: sgps000
你只用mybatis且在xml写SQL的格式和wrapper生成出来的一样的情况下速度有区别你才能说是mp有问题懂吗？
wrapper的SQL where后面自动加括号是框架自带的吗？还是可配置的？
Comment From: miemieYaho
没配置就这样
Comment From: sgps000
代码：
String pid = "2016070100899052_542101";
long l = System.currentTimeMillis();
List<CenterFollowHbpDrug> tempList = serviceMap.getService(CenterFollowHbpDrugService.class).findByXml(pid);
LogPrintUtils.log("" + tempList.size(), l);
l = System.currentTimeMillis();
tempList = serviceMap.getService(CenterFollowHbpDrugService.class).findByWrapper(pid);
LogPrintUtils.log("" + tempList.size(), l);
service:
@Resource
private CenterFollowHbpDrugMapper mapper;
public List<CenterFollowHbpDrug> findByXml(String pid) {
return mapper.find(0, pid);
}
public List<CenterFollowHbpDrug> findByWrapper(String pid) {
LambdaQueryWrapper<CenterFollowHbpDrug> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(CenterFollowHbpDrug::getStatus, 0)
.eq(CenterFollowHbpDrug::getPid, pid);
return mapper.selectList(wrapper);
}
mapper:
@Mapper
public interface CenterFollowHbpDrugMapper extends BaseMapper<CenterFollowHbpDrug> {
List<CenterFollowHbpDrug> find(@Param("status") Integer status,@Param("pid") String pid);
}
XML:
<select id="find" resultType="c.k.a.m.c.f.CenterFollowHbpDrug">
SELECT id,
hr_id,
name,
spec,
num,
unit,
frequency,
way,
dosage_per_use,
dosage_unit_per_use,
remarks,
pid,
status,
utime,
utimestamp, day
FROM center_follow_hbp_drug
WHERE (status = #{status} AND pid = #{pid})
</select>
堆栈日志：
2024-11-29T10:02:15.734+08:00 DEBUG 31544 --- [ Test worker] c.k.a.m.c.f.C.find : ==> Preparing: SELECT id, hr_id, name, spec, num, unit, frequency, way, dosage_per_use, dosage_unit_per_use, remarks, pid, status, utime, utimestamp, day FROM center_follow_hbp_drug WHERE (status = ? AND pid = ?)
2024-11-29T10:02:15.752+08:00 DEBUG 31544 --- [ Test worker] c.k.a.m.c.f.C.find : ==> Parameters: 0(Integer), 2016070100899052_542101(String)
2024-11-29T10:02:15.774+08:00 DEBUG 31544 --- [ Test worker] c.k.a.m.c.f.C.find : <== Total: 4
2024-11-29 10:02:15.790：4 耗时：74 ms
2024-11-29T10:02:15.794+08:00 DEBUG 31544 --- [ Test worker] c.k.a.m.c.f.C.selectList : ==> Preparing: SELECT id,hr_id,name,spec,num,unit,frequency,way,dosage_per_use,dosage_unit_per_use,remarks,pid,status,utime,utimestamp,day FROM center_follow_hbp_drug WHERE (status = ? AND pid = ?)
2024-11-29T10:02:15.811+08:00 DEBUG 31544 --- [ Test worker] c.k.a.m.c.f.C.selectList : ==> Parameters: 0(Integer), 2016070100899052_542101(String)
2024-11-29T10:02:31.456+08:00 DEBUG 31544 --- [ Test worker] c.k.a.m.c.f.C.selectList : <== Total: 4
2024-11-29 10:02:31.474：4 耗时：15秒
SQL log插件打印：
-- 311 -- 2024-11-29T10:02:15.734+08:00 DEBUG 31544 --- [ Test worker] c.k.a.m.c.f.C.find : ==>
SELECT id, hr_id, name, spec, num, unit, frequency, way, dosage_per_use, dosage_unit_per_use, remarks, pid, status, utime, utimestamp, day FROM center_follow_hbp_drug WHERE (status = 0 AND pid = '2016070100899052_542101')
-- 312 -- 2024-11-29T10:02:15.794+08:00 DEBUG 31544 --- [ Test worker] c.k.a.m.c.f.C.selectList : ==>
SELECT id,hr_id,name,spec,num,unit,frequency,way,dosage_per_use,dosage_unit_per_use,remarks,pid,status,utime,utimestamp,day FROM center_follow_hbp_drug WHERE (status = 0 AND pid = '2016070100899052_542101')
我把xml也加上了括号，此时sql应该是一样了吧，速度有差别，请问还有哪些变量没有做到一致？
Comment From: miemieYaho
提供一下你的db环境以及git项目吧,能稳定复现的
Comment From: miemieYaho
而且你试过
public List<CenterFollowHbpDrug> findByWrapper(String pid) {
QueryWrapper<CenterFollowHbpDrug> wrapper = new QueryWrapper<>();
wrapper.eq("status", 0).eq("pid", pid);
return mapper.selectList(wrapper);
}
了吗?
Comment From: sgps000
提供一下你的db环境以及git项目吧,能稳定复现的
我是拿生产环境的DB调的，生产环境的DM数据库是信创工程师部署的，我的测试服的DM数据库是对方提供的试用版，我自己测试服没出现这个问题，就生产环境出现了。DM数据库是有语法兼容模式选择的，但对方工程师未提供，全靠自己摸索。所以我想是来问问MP底层是否有特殊的机制会迫使数据库不走索引。
而且你试过
java public List<CenterFollowHbpDrug> findByWrapper(String pid) { QueryWrapper<CenterFollowHbpDrug> wrapper = new QueryWrapper<>(); wrapper.eq("status", 0).eq("pid", pid); return mapper.selectList(wrapper); }了吗?
试过了，跟Lambda的效果一致。
之后又测试了以下两种情况 test1:
public List<CenterFollowHbpDrug> findByWrapper(String pid) {
QueryWrapper<CenterFollowHbpDrug> wrapper = new QueryWrapper<>();
wrapper.eq("status", 0)
.eq("pid", pid)
.select("id","hr_id");
return mapper.selectList(wrapper);
}
test2:
QueryWrapper<CenterFollowHbpDrug> wrapper = new QueryWrapper<>();
wrapper.eq("status", 0)
.eq("pid", pid)
.select("id",
"hr_id",
"name",
"spec",
"num",
"unit",
"frequency",
"way",
"dosage_per_use",
"dosage_unit_per_use",
"remarks",
"pid",
"status",
"utime",
"utimestamp",
"day"
);
return mapper.selectList(wrapper);
发现速度又正常了，test2打印的sql日志与你发的案例一模一样的，区别在于手动select了所有字段。 但是这是适用场景二，场景一尝试了一下这种test2的方案还是没解决。
然后又按照你的思路，严格保证sql一致，因为之前的sql里还是有个别有空格，再加上有sql末尾任意拼接不影响的sql的字符串（场景一种的last(”--“)）都能变快的案例，就在场景一把字段加上个空格试了一下，结果是变正常了
QueryWrapper<CenterDiseaseDetail> wrapper = new QueryWrapper<>();
wrapper.in("idcard", idcardList);
wrapper.select("idcard ", "code ");
List<CenterDiseaseDetail> diseaseDetailList = serviceMap.getService(CenterDiseaseDetailService.class).findList(wrapper);
所以目前的解决方案是 直接加了个全局sql拦截器，把所有的逗号改成逗号加空格。
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}),
})
public class DmInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object target = invocation.getTarget();
if (target instanceof StatementHandler statementHandler) {
// 获取sql语句
String originalSql = statementHandler.getBoundSql().getSql();
// System.out.println("SQL: " + originalSql);
// sql替换
String newSql = originalSql.replaceAll(",", ", ");
if (!Objects.equals(originalSql, newSql)) {
// 更新 SQL 语句
Field field = statementHandler.getBoundSql().getClass().getDeclaredField("sql");
field.setAccessible(true);
field.set(statementHandler.getBoundSql(), newSql);
// System.out.println("转换后" + statementHandler.getBoundSql().getSql());
}
}
// 继续调用拦截链
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Interceptor.super.plugin(target);
}
@Override
public void setProperties(Properties properties) {
}
}
应该不会和MP的其他逻辑冲突吧？ 感觉直接改sql不是长久之计，但目前工作繁多，只能先应付使用了。如果此方案不会对MP框架造成影响，可以直接关闭这个issue
Comment From: miemieYaho
所以那还是dm有问题啊
Comment From: sgps000
所以那还是dm有问题啊
我已经尽量排除数据库的影响了，我想指出的是，MP在DM特殊的一个版本下，可能存在兼容问题。主要还是目前比较小众，但信创改版项目越来越多，我不确定会不会成为主流数据库，如果MP框架没有强制DM改变索引策略的机制话，可以不管了。当给后来者一个临时解决方案吧。