真的是不知道怎么优化这个sql了

需求:搜索商品,首先根据关键字(品名,材质,规格,钢厂,仓库,公司名)进行模糊查询,需要返回筛选条件,返回的筛选条件为品类,品名(多选),材质(多选),钢厂(多选)。然后根据品类,品名,材质,钢厂,地区，在一次进行精确搜索。

表设计

CREATE TABLE `steels_shopping` (
  `id` int(50) NOT NULL AUTO_INCREMENT COMMENT '主键 ',
  `shopping_id` varchar(50) DEFAULT NULL COMMENT '商品id',
  `shopping_name` varchar(50) DEFAULT NULL COMMENT '商品名称',
  `product_name` varchar(50) DEFAULT NULL COMMENT '品名',
  `material` varchar(50) DEFAULT NULL COMMENT '材质',
  `manufactor` varchar(50) DEFAULT NULL COMMENT '钢厂/厂家',
  `warehouse` varchar(50) DEFAULT NULL COMMENT '仓库',
  `shop_type` varchar(50) DEFAULT NULL COMMENT '店铺分类',
  `unit` varchar(20) DEFAULT NULL COMMENT '单位(详见字典表WEIGHT_UNIT)',
  `stock` varchar(20) DEFAULT NULL COMMENT '库存',
  `starting_quantity` varchar(50) DEFAULT NULL COMMENT '起订量',
  `weighting_method` varchar(20) DEFAULT NULL COMMENT '计重方式(详见字典表WEIGHTING_METHOD)',
  `quality_grade` varchar(20) DEFAULT NULL COMMENT '质量等级(详见字典表QUALITY_GRADE)',
  `warranty` varchar(20) DEFAULT NULL COMMENT '质保书(详见字典表WARRANTY)',
  `shopping_detail` varchar(9000) DEFAULT NULL COMMENT '商品详情',
  `shopping_abbr` varchar(300) DEFAULT NULL COMMENT '商品缩略图',
  `is_shelf` varchar(1) DEFAULT 'Y' COMMENT '是否上架(Y是 N否)',
  `is_hot` varchar(1) DEFAULT 'Y' COMMENT '是否热销(Y是 N否)',
  `is_new` varchar(1) DEFAULT 'Y' COMMENT '是否新品(Y是 N否)',
  `is_main` varchar(1) DEFAULT NULL COMMENT '是否主打产品(Y是 N否)',
  `order` int(10) DEFAULT '100' COMMENT '排序',
  `key_word` varchar(200) DEFAULT NULL COMMENT '关键字',
  `shopping_status` varchar(10) DEFAULT NULL COMMENT '审核状态(详见字典表SHOPPING_STATUS)',
  `volume` int(10) DEFAULT '0' COMMENT '销量',
  `created_by` varchar(50) DEFAULT NULL COMMENT '创建人',
  `updated_by` varchar(50) DEFAULT NULL COMMENT '修改人',
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `STEELS_SHOPPING_SHOPPING_ID` (`shopping_id`) USING BTREE,
  KEY `STEELS_SHOPPING_CREATED_BY` (`created_by`) USING BTREE,
  KEY `STEELS_SHOPPING_IS_SHELF` (`is_shelf`)
) ENGINE=InnoDB AUTO_INCREMENT=3942 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='商品表';

CREATE TABLE `steels_shopping_attribute` (
  `id` int(50) NOT NULL AUTO_INCREMENT COMMENT '主键 ',
  `shopping_attribute_id` varchar(50) DEFAULT NULL COMMENT '商品id',
  `shopping_id` varchar(50) DEFAULT NULL COMMENT '商品id',
  `shopping_attribute_type` varchar(50) DEFAULT NULL COMMENT '商品属性类型(详见字典表SHOPPING_ATTRIBUTE_TYPE)',
  `shopping_attribute_main_type` varchar(50) DEFAULT NULL COMMENT '商品属性主类型(详见字典表SHOPPING_ATTRIBUTE_MAIN_TYPE)',
  `shopping_attribute_name` varchar(50) DEFAULT NULL COMMENT '商品属性名称',
  `inclusive_price` double(20,2) DEFAULT NULL COMMENT '含税价格(只有当商品属性主类型是规格的时候才有数据)',
  `stock` int(20) DEFAULT NULL COMMENT '库存(只有当商品属性主类型是规格的时候才有数据)',
  `is_custom_input` varchar(1) DEFAULT NULL COMMENT '是否可自定义输入(Y是  N否)',
  `created_by` varchar(50) DEFAULT NULL COMMENT '创建人',
  `updated_by` varchar(50) DEFAULT NULL COMMENT '修改人',
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `STEELS_SHOPPING_ATTRIBUTE_SHOPPING_ATTRIBUTE_ID` (`shopping_attribute_id`),
  KEY `STEELS_SHOPPING_ATTRIBUTE_SHOPPING_ID` (`shopping_id`) USING BTREE,
  KEY `STEELS_SHOPPING_ATTRIBUTE_CREATED_BY` (`created_by`) USING BTREE,
  KEY `STEELS_SHOPPING_ATTRIBUTE_MAIN_TYPE` (`shopping_attribute_main_type`)
) ENGINE=InnoDB AUTO_INCREMENT=86243 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='商品属性表';

CREATE TABLE `steels_company_attribute` (
  `id` int(50) NOT NULL AUTO_INCREMENT COMMENT '主键 ',
  `company_attribute_id` varchar(50) DEFAULT NULL COMMENT '公司属性id',
  `company_id` varchar(50) DEFAULT NULL COMMENT '公司id',
  `company_attribute_name` varchar(50) DEFAULT NULL COMMENT '公司属性名称',
  `company_attribute_type` varchar(50) DEFAULT NULL COMMENT '公司属性类型',
  `created_by` varchar(50) DEFAULT NULL COMMENT '创建人',
  `updated_by` varchar(50) DEFAULT NULL COMMENT '修改人',
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `STEELS_COMPANY_ATTRIBUTE_CREATED_BY` (`created_by`) USING BTREE,
  KEY `STEELS_COMPANY_ATTRIBUTE_COMPANY_ID` (`company_id`) USING BTREE,
  KEY `STEELS_COMPANY_ATTRIBUTE_COMPANY_ATTRIBUTE_TYPE` (`company_attribute_type`)
) ENGINE=InnoDB AUTO_INCREMENT=6102 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='公司属性表';

CREATE TABLE `steels_dictionary` (
  `id` int(50) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `tree_id` varchar(100) DEFAULT NULL COMMENT '树id',
  `parent_id` varchar(50) DEFAULT NULL COMMENT '父级id',
  `group` varchar(50) DEFAULT NULL COMMENT '分组',
  `value` varchar(50) DEFAULT NULL COMMENT '值',
  `name` varchar(50) DEFAULT NULL COMMENT '名称',
  `comment` varchar(50) DEFAULT NULL COMMENT '描述',
  `order` int(50) DEFAULT NULL COMMENT '排序',
  `is_default` varchar(1) DEFAULT NULL COMMENT '是否默认',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `STEELS_DICTIONARY_GROUP` (`group`) USING BTREE,
  KEY `STEELS_DICTIONARY_VALUE` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=384 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='字典表';

sql,筛选条件

select
            group_concat(distinct  s.shop_type) typeName,
            group_concat(distinct  s.product_name) productName,
            group_concat(distinct  s.material) material,
            group_concat(distinct  s.manufactor) manufactor
        from
            steels_shopping s,
            steels_shopping_attribute sa,
            steels_company c,
            steels_company_attribute ca,
            steels_dictionary d
        where
            s.shopping_id = sa.shopping_id and
            sa.shopping_attribute_main_type = 'SPEC' and
            s.created_by = c.created_by and
            c.company_id = ca.company_id and
            ca.company_attribute_type = 'SALE_REGION' and
            d.`value` = ca.company_attribute_name and
            d.`group` = 'PURCHASE_REGION'
            <if test="pcSearchShoppingParamDTO.type != null and pcSearchShoppingParamDTO.type != ''">
                and s.shop_type = #{pcSearchShoppingParamDTO.type}
            </if>
            <if test="pcSearchShoppingParamDTO.productName != null and pcSearchShoppingParamDTO.productName.size > 0">
                and s.product_name in
                <foreach item="productName" collection="pcSearchShoppingParamDTO.productName" separator="," open="(" close=")" index="">
                     #{productName}
                </foreach>
            </if>
            <if test="pcSearchShoppingParamDTO.material != null and pcSearchShoppingParamDTO.material.size > 0">
                and s.material in
                <foreach item="material" collection="pcSearchShoppingParamDTO.material" separator="," open="(" close=")" index="">
                     #{material}
                </foreach>
            </if>
            <if test="pcSearchShoppingParamDTO.manufactor != null and pcSearchShoppingParamDTO.manufactor.size > 0">
                and s.manufactor in
                <foreach item="manufactor" collection="pcSearchShoppingParamDTO.manufactor" separator="," open="(" close=")" index="">
                     #{manufactor}
                </foreach>
            </if>
            <if test="pcSearchShoppingParamDTO.spec != null and pcSearchShoppingParamDTO.spec.size > 0">
                and sa.shopping_attribute_name in
                <foreach item="spec" collection="pcSearchShoppingParamDTO.spec" separator="," open="(" close=")" index="">
                     #{spec}
                </foreach>
            </if>
            <if test="pcSearchShoppingParamDTO.warehouse != null and pcSearchShoppingParamDTO.warehouse != ''">
                and s.warehouse = #{pcSearchShoppingParamDTO.warehouse}
            </if>
            <if test="pcSearchShoppingParamDTO.companyName != null and pcSearchShoppingParamDTO.companyName != ''">
                and c.company_name = #{pcSearchShoppingParamDTO.companyName}
            </if>
            <if test="pcSearchShoppingParamDTO.keyWord != null and pcSearchShoppingParamDTO.keyWord != ''">
                and s.key_word like concat("%",#{pcSearchShoppingParamDTO.keyWord},"%")
            </if>

sql:返回数据

select
        s.shopping_id shoppingId,
        s.shopping_name shoppingName,
        s.shopping_abbr shoppingAbbr,
        sa.inclusive_price price,
        d1.`comment` unit,
        s.warehouse,
        c.company_id companyId,
        c.company_name companyName
    from
        steels_shopping s,
        steels_shopping_attribute sa,
        steels_company c,
        steels_company_attribute ca,
        steels_dictionary d,
        steels_dictionary d1
    where
        s.shopping_id = sa.shopping_id and 
        s.created_by  = c.created_by and
        sa.shopping_attribute_main_type = 'SPEC' and
        c.company_id = ca.company_id and
        ca.company_attribute_type = 'SALE_REGION' and
        d.`value` = ca.company_attribute_name and
        d.`group` = 'PURCHASE_REGION' and
        d1.`value` = s.unit and
        d1.`group` = 'WEIGHT_UNIT' and
        s.is_shelf = 'Y'
        <if test="pcSearchShoppingParamDTO.type != null and pcSearchShoppingParamDTO.type != ''">
            and s.shop_type = #{pcSearchShoppingParamDTO.type}
        </if>
        <if test="pcSearchShoppingParamDTO.productName != null and pcSearchShoppingParamDTO.productName.size > 0">
            and s.product_name in
            <foreach item="productName" collection="pcSearchShoppingParamDTO.productName" separator="," open="(" close=")" index="">
                 #{productName}
            </foreach>
        </if>
        <if test="pcSearchShoppingParamDTO.material != null and pcSearchShoppingParamDTO.material.size > 0">
            and s.material in
            <foreach item="material" collection="pcSearchShoppingParamDTO.material" separator="," open="(" close=")" index="">
                 #{material}
            </foreach>
        </if>
        <if test="pcSearchShoppingParamDTO.manufactor != null and pcSearchShoppingParamDTO.manufactor.size > 0">
            and s.manufactor in
            <foreach item="manufactor" collection="pcSearchShoppingParamDTO.manufactor" separator="," open="(" close=")" index="">
                 #{manufactor}
            </foreach>
        </if>
        <if test="pcSearchShoppingParamDTO.spec != null and pcSearchShoppingParamDTO.spec.size > 0">
            and sa.shopping_attribute_name in
            <foreach item="spec" collection="pcSearchShoppingParamDTO.spec" separator="," open="(" close=")" index="">
                 #{spec}
            </foreach>
        </if>
        <if test="pcSearchShoppingParamDTO.warehouse != null and pcSearchShoppingParamDTO.warehouse != ''">
            and s.warehouse = #{pcSearchShoppingParamDTO.warehouse}
        </if>
        <if test="pcSearchShoppingParamDTO.companyName != null and pcSearchShoppingParamDTO.companyName != ''">
            and c.company_name = #{pcSearchShoppingParamDTO.companyName}
        </if>
        <if test="pcSearchShoppingParamDTO.keyWord != null and pcSearchShoppingParamDTO.keyWord != ''">
            and s.key_word like concat("%",#{pcSearchShoppingParamDTO.keyWord},"%")
        </if>
        <if test="pcSearchShoppingParamDTO.area != null and pcSearchShoppingParamDTO.area != ''">
            and d.`comment` = #{pcSearchShoppingParamDTO.area}
        </if>
        GROUP BY s.shopping_id
        order by sa.inclusive_price
        <choose>
             <when test="pcSearchShoppingParamDTO.order != null and pcSearchShoppingParamDTO.order != ''">
                 <choose>
                    <when test='pcSearchShoppingParamDTO.order == "DESC" or pcSearchShoppingParamDTO.order == "ASC"'>
                        ${pcIndexSupplyProductsParamDTO.order}
                    </when>
                    <otherwise>
                     desc
                    </otherwise>
                </choose>
            </when>
            <otherwise>
                 desc
            </otherwise>
        </choose>

筛选条件sql用时0.7s左右,返回数据sql用时1.4s左右,接口调用用时2s左右,商品表3807条数据,商品属性表83741条数据,公司表108条数据,公司属性表里面3029条数据,字典表350条数据,不分组查询数据量为94820条,通过分组查询为3793条,索引该创建的都创建了,该使用的也使用了

只是一个小白,在大公司搜索都接触不到,现在在小公司啥都要自己做,才发现原来优化一条sql这么难,这问题弄了三天了,还是没有找到解决的方法

看了你的sql，都会产生笛卡尔积，你可以使用left join或者right join 试试

建议尝试使用elasticSearch