image.png
定位問(wèn)題
- 日志里出現(xiàn)了幾千次這樣的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
這肯定是循環(huán)執(zhí)行了sql了。所以，需要找循環(huán)執(zhí)行的地方齿椅。
- 通過(guò)全局搜索
findByClassId、findChild兩個(gè)函數(shù)启泣，且是循環(huán)執(zhí)行的地方涣脚，發(fā)現(xiàn)可能是 Mapper 里出的問(wèn)題：
<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>
這個(gè)文件里，這兩個(gè)sql是同時(shí)出現(xiàn)的寥茫，正好和日志里出現(xiàn)的模式一樣遣蚀。這樣的文件有兩個(gè)
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>
注釋里居然還寫了
遞歸查詢所有分類
優(yōu)化目標(biāo)：去掉 mapper 里的循環(huán)查詢
使用了 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個(gè)地方。首頁(yè)纱耻，或者類似首頁(yè)用的有：
- 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 + "轉(zhuǎn)化成功");
}
}else{
gclzs = goodsClassCustomDao.findAllbyisshow(goodsClassCustom);
}
return gclzs;
}
調(diào)用了這個(gè)方法的地方有：
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>
這段代碼芭梯，分別使用了結(jié)果集的 classCustomList、但是并沒有用到brandList弄喘。假如僅僅只去掉 brandList 的嵌套玖喘，會(huì)加快一倍的加載時(shí)間。
同時(shí)注意蘑志，classCustomList是嵌套兩層用的累奈。也就是說(shuō)，如果不嵌套執(zhí)行sql的話急但，而是使用Java來(lái)處理結(jié)果集澎媒，也只嵌套兩層就可以了。
為了不影響其他使用 findAllbyisshow 的地方羊始，新建一個(gè)方法： findAllbyisshowWithoutRecursion 處理查詢結(jié)果集
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'這個(gè)條件
用Java處理返回結(jié)果集：
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 + "轉(zhuǎn)化成功");
}
}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);
}
}
}
// 模板生成菜單的時(shí)候只有兩層旱幼，所以，處理兩層就可以了突委。
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;
}
