关于商品属性筛选 统计的 复杂SQL 要执行15S+ 想问这种写成存储过程 作用大吗?

[ImFantasy]

这个是最简单的版本, 只统计最简单的 执行速度非常的快

SELECT v.attribute_id, v.id AS value_id, l.value_name, l.value_url_key, v.sort_order, ( SELECT COUNT( goods_id ) FROM  `think_goods_attribute` WHERE  `attribute_id` = v.attribute_id AND  `attribute_value_id` = v.id AND  `model_id` =1 ) AS count
FROM  `think_attribute_value` AS v
LEFT JOIN  `think_attribute_value_lang` AS l ON v.id = l.attribute_value_id
WHERE v.attribute_id
IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13, 14 ) 
AND lang =1
ORDER BY attribute_id ASC , v.sort_order ASC 

下面这个增加了属性筛选条件来统计, 执行要15S 以上. 想把他控制到0.5s左右, (感觉好像不可能, 不知道神奇的存储过程,可以做到吗? )

SELECT v.attribute_id, v.id AS value_id, l.value_name, l.value_url_key, v.sort_order, 
     ( SELECT COUNT( goods_id ) FROM  `think_goods_attribute` 
		WHERE   ( `attribute_id` = v.attribute_id  AND `attribute_value_id` = v.id  AND `model_id` =1 )
		AND `goods_id` IN (SELECT goods_id FROM  `think_goods_attribute` WHERE  `attribute_id` =1 AND  `attribute_value_id` =1 AND  `model_id` =1)	 
		AND `goods_id` IN (SELECT goods_id FROM  `think_goods_attribute` WHERE  `attribute_id` =1 AND  `attribute_value_id` =2 AND  `model_id` =1)	 
		AND `goods_id` IN (SELECT goods_id FROM  `think_goods_attribute` WHERE  `attribute_id` =1 AND  `attribute_value_id` =3 AND  `model_id` =1)	 
		AND `goods_id` IN (SELECT goods_id FROM  `think_goods_attribute` WHERE  `attribute_id` =1 AND  `attribute_value_id` =4 AND  `model_id` =1)	 
	 ) AS count
FROM  `think_attribute_value` AS v
LEFT JOIN  `think_attribute_value_lang` AS l ON v.id = l.attribute_value_id
WHERE v.attribute_id
IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13, 14 ) 
AND lang =1
ORDER BY count ASC, attribute_id ASC , v.sort_order ASC 

实际使用过程中, 这一句 会替换为

WHERE v.attribute_id IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13, 14 ) 

WHERE v.attribute_id
IN ( SELECT att.id FROM  `think_attribute_sets_relation` AS rel LEFT JOIN  `think_attribute` AS att ON att.id = rel.attribute_id WHERE rel.attribute_sets_id = ( SELECT  `set_id` FROM  `think_goods_model` WHERE  `id` =1 ) ORDER BY att.sort_order ASC ) 

这个对性能影响不是很大, 主要是上面那个 15s的太卡了.

问题集中在:

( SELECT COUNT( goods_id ) FROM  `think_goods_attribute` 
		WHERE   ( `attribute_id` = v.attribute_id  AND `attribute_value_id` = v.id  AND `model_id` =1 )
		AND `goods_id` IN (SELECT goods_id FROM  `think_goods_attribute` WHERE  `attribute_id` =1 AND  `attribute_value_id` =1 AND  `model_id` =1)	 
		AND `goods_id` IN (SELECT goods_id FROM  `think_goods_attribute` WHERE  `attribute_id` =1 AND  `attribute_value_id` =2 AND  `model_id` =1)	 
		AND `goods_id` IN (SELECT goods_id FROM  `think_goods_attribute` WHERE  `attribute_id` =1 AND  `attribute_value_id` =3 AND  `model_id` =1)	 
		AND `goods_id` IN (SELECT goods_id FROM  `think_goods_attribute` WHERE  `attribute_id` =1 AND  `attribute_value_id` =4 AND  `model_id` =1)	 
	 ) AS count

[ACMAIN_CHM]

换IN为EXIST试一下。
建议贴出你的表结构并说明一下想查询的功能是什么。以便别人分析。

[ImFantasy]

不好意思, 忘记贴数据结构了.

CREATE TABLE IF NOT EXISTS `think_attribute_lang` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `attribute_id` int(11) unsigned NOT NULL  ,
  `lang` tinyint(2) unsigned NOT NULL DEFAULT '0'  ,
  `name` varchar(50) NOT NULL ,
  `url_key` char(50) NOT NULL ,
  PRIMARY KEY (`id`),
  KEY `attribute_id` (`attribute_id`),
  KEY `lang_url_key` (`lang`,`url_key`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='属性多语言名称表' AUTO_INCREMENT=76 ;





CREATE TABLE IF NOT EXISTS `think_attribute_value` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT  ,
  `attribute_id` int(11) unsigned NOT NULL  ,
  `is_default` tinyint(1) unsigned NOT NULL DEFAULT '0'  ,
  `admin_name` varchar(50) NOT NULL  ,
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`id`),
  KEY `attribute_id` (`attribute_id`),
  KEY `is_default` (`is_default`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='属性值数据表' AUTO_INCREMENT=126 ;




CREATE TABLE IF NOT EXISTS `think_attribute_value_lang` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT  ,
  `attribute_value_id` int(11) unsigned NOT NULL  ,
  `lang` tinyint(2) unsigned NOT NULL DEFAULT '0'  ,
  `value_name` varchar(20) NOT NULL  ,
  `value_url_key` char(20) NOT NULL  ,
  PRIMARY KEY (`id`),
  KEY `attribute_value_id` (`attribute_value_id`),
  KEY `lang_url_key` (`lang`,`value_url_key`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='属性值多语言数据表' AUTO_INCREMENT=671 ;


CREATE TABLE IF NOT EXISTS `think_goods_attribute` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `goods_id` mediumint(8) unsigned NOT NULL  ,
  `attribute_id` int(11) unsigned NOT NULL  ,
  `attribute_value_id` int(11) unsigned NOT NULL  ,
  `model_id` smallint(5) unsigned NOT NULL ,
  PRIMARY KEY (`id`),
  KEY `attribute_id` (`attribute_id`,`attribute_value_id`,`model_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='商品属性值表' AUTO_INCREMENT=21404 ;

[rucypli]

SELECT COUNT( goods_id ) FROM `think_goods_attribute`
WHERE ( `attribute_id` = v.attribute_id AND `attribute_value_id` = v.id AND `model_id` =1 )
AND `goods_id` IN (SELECT goods_id FROM `think_goods_attribute` WHERE `attribute_id` =1 AND `attribute_value_id` =1 AND `model_id` =1)
AND `goods_id` IN (SELECT goods_id FROM `think_goods_attribute` WHERE `attribute_id` =1 AND `attribute_value_id` =2 AND `model_id` =1)
AND `goods_id` IN (SELECT goods_id FROM `think_goods_attribute` WHERE `attribute_id` =1 AND `attribute_value_id` =3 AND `model_id` =1)
AND `goods_id` IN (SELECT goods_id FROM `think_goods_attribute` WHERE `attribute_id` =1 AND `attribute_value_id` =4 AND `model_id` =1)


改成这个
select count(*) from `think_goods_attribute` A
where ( `attribute_id` = v.attribute_id AND `attribute_value_id` = v.id AND `model_id` =1 )
and exists (select 1 from `think_goods_attribute` B where A.`goods_id`=B.`goods_id` and B.`attribute_id` =1 AND B.`attribute_value_id` in (1,2,3,4) AND B.`model_id` =1)

[wwwwb]

sELECT COUNT( goods_id ) force index (`attribute_id`) FROM `think_goods_attribute`
WHERE
AND `goods_id` IN (SELECT goods_id FROM `think_goods_attribute` WHERE `attribute_id` =1 AND `attribute_value_id` in(1,2,3,4) AND `model_id` =1)


速度如何

[--小F--]

感觉用了IN都不怎么快的。