image.png
定位问题
- 日志里出现了几千次这样的sql
mapper.BrandMapper.findByClassId - ========> Preparing: select brand_id, brand_name, brand_class, brand_pic, brand_sort, brand_recommend, store_id,brand_apply, class_id,is_del,create_time,update_time from shop_brand where class_id = ? and is_del = 0 and brand_apply = 1 order by brand_sort desc
GoodsClassCustomMapper.findChild - ========> Preparing: SELECT gc_id,gc_parent_id,gc_rel_gcid,gc_name,gc_pic,gc_sort,gc_show,gc_title,gc_keywords, gc_description,gc_idpath,gc_url,gc_adduser,gc_upuser,gc_advid,create_time, update_time , (select count(*) from shop_goods_class_custom s1 where s1.gc_parent_id=s.gc_id ) has_child FROM shop_goods_class_custom s where gc_parent_id = ? and gc_show=1 order by gc_sort DESC
这肯定是循环执行了sql了。所以，需要找循环执行的地方。
- 通过全局搜索
findByClassId、findChild两个函数，且是循环执行的地方，发现可能是 Mapper 里出的问题：
<resultMap id="BaseResultMap" type="com.leimingtech.core.entity.GoodsClass">
<id column="gc_id" property="gcId" jdbcType="VARCHAR"/>
<result column="gc_name" property="gcName" jdbcType="VARCHAR"/>
<result column="gc_pic" property="gcpic" jdbcType="VARCHAR"/>
<result column="type_id" property="typeId" jdbcType="VARCHAR"/>
<result column="type_name" property="typename" jdbcType="VARCHAR"/>
<result column="gc_parent_id" property="gcParentId" jdbcType="VARCHAR"/>
<result column="gc_sort" property="gcSort" jdbcType="INTEGER"/>
<result column="gc_show" property="gcshow" jdbcType="INTEGER"/>
<result column="gc_title" property="gcTitle" jdbcType="VARCHAR"/>
<result column="gc_keywords" property="gcKeywords" jdbcType="VARCHAR"/>
<result column="gc_description" property="gcDescription" jdbcType="VARCHAR"/>
<result column="gc_idpath" property="gcIdpath" jdbcType="VARCHAR"/>
<result column="has_child" property="hasChild" jdbcType="INTEGER"/>
<result column="expen_scale" property="expenScale" jdbcType="FLOAT"/>
<result column="is_relate" property="isRelate" jdbcType="INTEGER"/>
<result column="virtual_goods" property="virtualGoods" jdbcType="INTEGER"/>
<result column="relation" property="relation" jdbcType="INTEGER"/>
<collection property="classList" ofType="com.leimingtech.core.entity.GoodsClass"
column="gc_id" javaType="arraylist"
select="com.leimingtech.service.module.goods.dao.mapper.GoodsClassMapper.findChild">
</collection>
<collection property="brandList" ofType="com.leimingtech.core.entity.base.Brand" column="gc_id"
javaType="arrayList"
select="com.leimingtech.service.module.goods.dao.mapper.BrandMapper.findByClassId">
</collection>
</resultMap>
这个文件里，这两个sql是同时出现的，正好和日志里出现的模式一样。这样的文件有两个
C:\opt\zzwelfare\welfare-service\src\main\resources\com\leimingtech\service\module\goods\dao\mapper\GoodsClassCustomMapper.xml
C:\opt\zzwelfare\welfare-service\src\main\resources\com\leimingtech\service\module\goods\dao\mapper\GoodsClassMapper.xml
- 在
GoodsClassCustomMapper使用了BaseResultMap的地方有：
<!--递归查询所有分类-->
<select id="findAllbyisshow" resultMap="BaseResultMap" parameterType="com.leimingtech.core.entity.GoodsClassCustom">
SELECT
<include refid="Base_Column_List" />,
(select count(*) from shop_goods_class_custom s1 where s1.gc_parent_id=s.gc_id and s1.gc_show=#{gcshow}) has_child
FROM shop_goods_class_custom s where gc_parent_id ='0'
<if test="gcshow != null and gcshow != ''">
and gc_show=#{gcshow}
</if>
order by gc_sort DESC
</select>
注释里居然还写了
递归查询所有分类
优化目标：去掉 mapper 里的循环查询
使用了 findAllbyisshow 的地方有：
welfare-api/src/main/java/com/leimingtech/extend/module/api/GoodsApi.java
welfare-front/src/main/java/com/leimingtech/front/module/tag/EgoodsClassCustomProductTag.java
welfare-front/src/main/java/com/leimingtech/front/module/tag/EgoodsClassProductTag.java
welfare-front/src/main/java/com/leimingtech/front/module/tag/GoodsClassCustomTag.java
welfare-front/src/main/java/com/leimingtech/front/module/tag/GoodsClassTag.java
welfare-h5/src/main/java/com/leimingtech/extend/module/h5/category/CategoryHtml5Action.java
welfare-service/src/main/java/com/leimingtech/service/module/goods/dao/GoodsClassCustomDao.java
welfare-service/src/main/java/com/leimingtech/service/module/goods/dao/GoodsClassDao.java
welfare-service/src/main/java/com/leimingtech/service/module/goods/dao/impl/GoodsClassCustomDaoImpl.java
welfare-service/src/main/java/com/leimingtech/service/module/goods/dao/impl/GoodsClassDaoImpl.java
welfare-service/src/main/java/com/leimingtech/service/module/goods/dao/mapper/GoodsClassCustomMapper.java
welfare-service/src/main/java/com/leimingtech/service/module/goods/dao/mapper/GoodsClassMapper.java
welfare-service/src/main/java/com/leimingtech/service/module/goods/service/GoodsClassCustomService.java
welfare-service/src/main/java/com/leimingtech/service/module/goods/service/GoodsClassService.java
welfare-service/src/main/java/com/leimingtech/service/module/goods/service/impl/GoodsClassCustomServiceImpl.java
welfare-service/src/main/java/com/leimingtech/service/module/goods/service/impl/GoodsClassServiceImpl.java
总共这16个地方。首页，或者类似首页用的有：
- welfare-service/src/main/java/com/leimingtech/service/module/goods/service/GoodsClassCustomService.java
/**
* 查询所有的分类
* @return
*/
@Override
public List<GoodsClassCustom> findAllbyisshow(GoodsClassCustom goodsClassCustom) {
List<GoodsClassCustom> gclzs = null;
if(JedisConfig.JEDIS_STATUS){
////需要安装redis
Object obj = JedisUtils.getObject(JedisConfig.GCLZ_PREFIX + "goodsClassCustom");
if(obj == null){
gclzs = goodsClassCustomDao.findAllbyisshow(goodsClassCustom);
//10分钟
JedisUtils.setObject(JedisConfig.GCLZ_PREFIX + "goodsClassCustom", gclzs, JedisConfig.JEDIS_EXPIRE);
log.debug(JedisConfig.GCLZ_PREFIX + "存入redis");
}else{
gclzs = (List<GoodsClassCustom>)obj;
log.debug(JedisConfig.GCLZ_PREFIX + "转化成功");
}
}else{
gclzs = goodsClassCustomDao.findAllbyisshow(goodsClassCustom);
}
return gclzs;
}
调用了这个方法的地方有：
C:\opt\zzwelfare\welfare-front\src\main\java\com\leimingtech\front\module\tag\GoodsClassCustomTag.java
C:\opt\zzwelfare\welfare-front\src\main\java\com\leimingtech\front\module\tag\EgoodsClassCustomProductTag.java
C:\opt\zzwelfare\welfare-h5\src\main\java\com\leimingtech\extend\module\h5\category\CategoryHtml5Action.java
使用了 GoodsClassCustomTag 的地方有：
C:\opt\zzwelfare\welfare-front\src\main\webapp\WEB-INF\views_v4.0\commons\header.ftl
C:\opt\zzwelfare\welfare-front\src\main\webapp\WEB-INF\views_v4.0\search\goods-search.ftl
C:\opt\zzwelfare\welfare-front\src\main\webapp\WEB-INF\views_v4.0\category\all_class.ftl
header.ftl 中使用 GoodsClassCustomTag 的地方
<#--<#assign goodsClassTag = newTag("goodsClassTag") />-->
<#assign goodsClassTag = newTag("goodsClassCustomTag") />
<#assign goodsList = goodsClassTag("") />
<#if goodsList?exists && goodsList?size gt 0>
<#list goodsList as class>
<li class="classes masonry-brick">
<div class="title">
<#if class.gcUrl ??&& class.gcUrl!=null>
<a href="${class.gcUrl}">${class.gcName}</a>
<#else>
<i></i> <a href="${base}/search/goodsSearch?searchType=gcIdSearch&keyword=${class.gcId}" title="${class.gcName}">${class.gcName}</a>
</#if>
</div>
<#if class.hasChild gt 0>
<#assign firstchild = class.classCustomList>
<#list firstchild as firstclass>
<dl>
<dt>
<#if firstclass.gcUrl ??&& firstclass.gcUrl!=null>
<a href="${firstclass.gcUrl}">${firstclass.gcName}</a>
<#else>
<a href="${base}/search/goodsSearch?searchType=gcIdSearch&keyword=${firstclass.gcRelId}" title="${firstclass.gcName}">${firstclass.gcName}</a>
</#if>
</dt>
<dd>
<#if firstclass.hasChild gt 0>
<#assign secondchild = firstclass.classCustomList>
<#list secondchild as secondclass>
<#if secondclass.gcUrl ??&& secondclass.gcUrl!=null>
<a href="${secondclass.gcUrl}">${secondclass.gcName}</a>
<#else>
<a href="${base}/search/goodsSearch?searchType=gcIdSearch&keyword=${secondclass.gcRelId}" title="${secondclass.gcName}">${secondclass.gcName}</a>
</#if>
</#list>
</#if>
</dd>
</dl>
</#list>
</#if>
</li>
</#list>
</#if>
这段代码，分别使用了结果集的 classCustomList、但是并没有用到brandList。假如仅仅只去掉 brandList 的嵌套，会加快一倍的加载时间。
同时注意，classCustomList是嵌套两层用的。也就是说，如果不嵌套执行sql的话，而是使用Java来处理结果集，也只嵌套两层就可以了。
为了不影响其他使用 findAllbyisshow 的地方，新建一个方法： findAllbyisshowWithoutRecursion 处理查询结果集
C:\opt\zzwelfare\welfare-service\src\main\resources\com\leimingtech\service\module\goods\dao\mapper\GoodsClassCustomMapper.xml
<!--非递归查询所有分类-->
<select id="findAllbyisshowWithoutRecursion" resultMap="BaseResultMap8" parameterType="com.leimingtech.core.entity.GoodsClassCustom">
SELECT
s.gc_id,s.gc_parent_id,s.gc_rel_gcid,s.gc_name,s.gc_pic,s.gc_sort,s.gc_show,s.gc_title,s.gc_keywords,
s.gc_description,s.gc_idpath,s.gc_url,s.gc_adduser,s.gc_upuser,s.gc_advid,s.create_time,s.update_time
FROM shop_goods_class_custom s
where
<if test="gcshow != null and gcshow != ''">
s.gc_show=#{gcshow}
</if>
order by s.gc_sort DESC
</select>
注意这里去掉了
gc_parent_id ='0'这个条件
用Java处理返回结果集：
C:\opt\zzwelfare\welfare-service\src\main\java\com\leimingtech\service\module\goods\service\impl\GoodsClassCustomServiceImpl.java
/**
* 查询所有的分类
* @return
*/
@Override
public List<GoodsClassCustom> findAllbyisshowWithoutRecursion(GoodsClassCustom goodsClassCustom) {
List<GoodsClassCustom> gclzs = new ArrayList<>();
List<GoodsClassCustom> allCategoryList = null;
if(JedisConfig.JEDIS_STATUS){
////需要安装redis
Object obj = JedisUtils.getObject(JedisConfig.GCLZ_PREFIX + "goodsClassCustom");
if(obj == null){
allCategoryList = goodsClassCustomDao.findAllbyisshowWithoutRecursion(goodsClassCustom);
//10分钟
JedisUtils.setObject(JedisConfig.GCLZ_PREFIX + "goodsClassCustom", allCategoryList, JedisConfig.JEDIS_EXPIRE);
log.debug(JedisConfig.GCLZ_PREFIX + "存入redis");
}else{
allCategoryList = (List<GoodsClassCustom>)obj;
log.debug(JedisConfig.GCLZ_PREFIX + "转化成功");
}
}else{
allCategoryList = goodsClassCustomDao.findAllbyisshowWithoutRecursion(goodsClassCustom);
}
if(null != allCategoryList && allCategoryList.size() > 0) {
for(GoodsClassCustom children: allCategoryList) {
// 顶层：ParentId == 0
if(children.getGcParentId().equals("0")) {
GoodsClassCustom custom = new GoodsClassCustom();
custom.setGcId(children.getGcId());
custom.setGcParentId(children.getGcParentId());
custom.setGcRelId(children.getGcRelId());
custom.setGcName(children.getGcName());
custom.setGcPic(children.getGcPic());
custom.setGcSort(children.getGcSort());
custom.setGcshow(children.getGcshow());
custom.setGcTitle(children.getGcTitle());
custom.setGcDescription(children.getGcDescription());
custom.setGcIdpath(children.getGcIdpath());
custom.setGcUrl(children.getGcUrl());
custom.setGcAddUser(children.getGcAddUser());
custom.setGcUpUser(children.getGcUpUser());
custom.setGcAdvid(children.getGcAdvid());
custom.setCreateTime(children.getCreateTime());
custom.setUpdateTime(children.getUpdateTime());
if (!gclzs.contains(custom)) {
gclzs.add(custom);
}
}
}
// 模板生成菜单的时候只有两层，所以，处理两层就可以了。
for(GoodsClassCustom custom: gclzs) {
for(GoodsClassCustom children: allCategoryList) {
if(custom.getGcId().equals(children.getGcParentId())) {
custom.setHasChild(1);
List<GoodsClassCustom> sons;
if(null == custom.getClassCustomList()) {
sons = new ArrayList<>();
custom.setClassCustomList(sons);
} else {
sons = custom.getClassCustomList();
}
sons.add(children);
}
}
}
for(GoodsClassCustom custom: gclzs) {
List<GoodsClassCustom> sons = custom.getClassCustomList();
if(null != sons && sons.size() > 0 ) {
for(GoodsClassCustom son: sons) {
for(GoodsClassCustom children: allCategoryList) {
if(son.getGcId().equals(children.getGcParentId())) {
son.setHasChild(1);
List<GoodsClassCustom> grandsons;
if(null == son.getClassCustomList()) {
grandsons = new ArrayList<>();
son.setClassCustomList(grandsons);
} else {
grandsons = son.getClassCustomList();
}
grandsons.add(children);
}
}
}
}
}
}
return gclzs;
}
网友评论