Mybatis查询超时

问题现象

测试同学反馈，商品查询功能，输入字母或汉字正常，输入数字搜索时报错。

问题排查

初次排查

查看了一下耗时点，在mybatis执行到DruidDataSource.getConnect方法之间耗时很久(两次，每次都差不多100s左右)。

猜测：连接池问题，全部被占用，在等待连接？
查看了下getConnection代码， 里面才开始获取连接，说明这个中间过程还没有进行连接获取

public DruidPooledConnection getConnection() throws SQLException {
    return getConnection(maxWait);
}

这个功能，在其他环境验证的时候正常，上线很久了，也没有暴露过问题。查看了这个接口对应的请求，在那个点附近的几十、百多秒，之前、之后的都在1s内。

当时查看部署服务器，那个时间段CPU耗时较高。环境之前出现过不稳定的情况，当时以为环境问题，适当加大了接口超时时间。

问题再现

第二天，测试反馈又重现了。一次可以是凑巧，两次可能性很低。
看了下请求数据，超时请求，输入的搜索关键字是"2"。关键字会去模糊匹配商品名称和条码。匹配到很多商品？
查看了下数据库里面的商品，搜索条件下模糊匹配到几W条。pinpoint里面参数也印证了。

对比分析了下之前输入中文或字母搜索的，匹配的商品数量很少。
由于是测试环境，之前批量造数据的时候，商品名称不规范，数字"2"匹配到了很多。

至此找到了根本原因，如下：
我们的商品条码和商品名称是存放在两张表的。查看代码实现，是先去条码表模糊匹配，找到一批商品ukid，然后在作为入参，去商品表里面做分页查询，计算总量等。
商品数据量很大，mybatis做动态sql拼接时耗时很久。 从mybatis开始执行，到getConnection，中间是完成SQL组装。

商品表查询，xml配置

<select id="selectRsDefineds" parameterType="java.lang.Long" resultMap="BaseResultMap">
  select
  <include refid="Base_Column_List" />
  from rs_defined r
  where
  defined_ukid IS NOT NULL
  <if test="buIds != null">
    and definer_id in
    <foreach close=")" collection="buIds" item="ownerUkid" open="(" separator=",">
      #{ownerUkid}
    </foreach>
  </if>
  <if test="definedName != null">
    and
    (
    defined_name LIKE #{definedName}
    <if test="inDefinedUkidList != null">
      or defined_ukid in
      <foreach close=")" collection="inDefinedUkidList" item="definedUkid" open="(" separator=",">
        #{definedUkid}
      </foreach>
    </if>
    )
  </if>
  <if test="statusList != null">
    and status in
    <foreach close=")" collection="statusList" item="status" open="(" separator=",">
      #{status}
    </foreach>
  </if>
  <if test="statusList == null">
    and status = 1
  </if>
  <if test="typeList != null">
    and defined_type in
    <foreach close=")" collection="typeList" item="type" open="(" separator=",">
      #{type}
    </foreach>
  </if>
  <if test="debarUkids != null">
    and defined_ukid NOT IN
    <foreach close=")" collection="debarUkids" item="debarDefinedUkid" open="(" separator=",">
      #{debarDefinedUkid}
    </foreach>
  </if>
  <if test="sku != null">
    and sku = #{sku,jdbcType=BIGINT}
  </if>
  <if test="sort != null">
    ORDER BY ${sort}
  </if>
  <if test="limit != null">
    <if test="offset != null">
      limit ${offset}, ${limit}
    </if>
    <if test="offset == null">
      limit ${limit}
    </if>
  </if>
</select>

解决方案

通过elasticSearch查询

直接把所有商品数据同步到es，通过es做搜索。
自测耗时500ms左右。 (总量：28w+，匹配到的数据量：15w+，虚拟机)

公司已有es环境，es本身用于搜索，对大数据量筛选过滤、匹配度等支持更好。而且调整成本不高。

其他方案

• 自身在程序中做SQL拼接，不要用mybatis的。 针对这种特定场景，没必要所有的均如此
• 表结构设计调整，条码、名称 聚合到一张表中进行搜索匹配

补充说明

mybatis做动态sql拼接，很久以前碰到过一次栈溢出的。当时查询到SQL拼接使用的visitor模式，构造了比较复杂的语法树。
本次这块没有去往下分析。有大神欢迎赐教，或者有资料分享下，谢

之前栈溢出的记录
https://blog.csdn.net/LG772EF/article/details/58636910