询问一条sql语句的优化

Question

大家好，我是一位新手，最近遇到一条sql语句，执行效率很慢，希望大家能帮忙优化下。
table `picture` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `picture_desc` text NOT NULL,
  `picture_rating` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`id`),
)
table `pictag` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `tag_des` text NOT NULL,
  PRIMARY KEY  (`id`),
)
table `picture_pictag` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `picture_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `picture_id` (`picture_id`,`tag_id`),
  KEY `pid` (`picture_id`),
  KEY `tid` (`tag_id`)
)
picture是图片表、pictag是标签表、picture_pictag是关联表。(many_to_many关联)现在picture表的数据约为20W条，pictag表的数据约为10W条，关联表picture_pictag的数据约为120W条。现在我需要通过标签去查询图片，查询属于一部分标签，且不属于另一部分标签的图片。另外图片有一个picture_rating字段，要求picture_rating=1。
我写的查询语句如下：
select a.picture_id as picture_id from
 ( select c.`picture_id` from `picture_pictag` as c,
    `picture_options` as d
          where c.tag_id in (361,368,409,367,415,410)
          and c.picture_id=d.id
          and d.picture_rating=1
          group by c.`picture_id` having count(c.`picture_id`)=6 ) a
  left join `picture_pictag` b
     on a.picture_id=b.picture_id
     and b.tag_id in (772,7,80)
   where b.picture_id is null执行上面的查询，执行时间为1.672s

Answer 1

picture_options是什么?你都建了哪些index?

Answer 2

我的问题第一个是要理解你的问题.第二个index更是直接关系到查询的效率,都需要考虑.问题描述不清楚:
假设图像的标签集为P,要找的标签集是A,不要的标签集是B.
符合要求的图像要满足
1. P和B没有交集.
2. P和A的关系:是P=A,P包含A还是P和A有交集?

Answer 3

1、将picture_picta表的UNIQUE KEY变成(`tag_id`,`picture_id`)，或者group by c.`picture_id` 后面加order by null试试。前提是改变索引次序不影响其他查询的效率。
2、拆分语句，用临时变量或者临时表

Answer 4

首先抱歉，因为提问得比较急，没有描述清楚。
我查看一些文档，不考虑picture_rating这个字段的条件，现在写出来的sql如下。select a.picture_id as picture_id
from ( select picture_id
from picture_pictag
where tag_id in (361,368,409,367,415,410)
group by picture_id
having count(picture_id)=6
) a left join picture_pictag b
on a.picture_id=b.picture_id
and b.tag_id in (772,7,80)
where b.picture_id is null这是一个网站页面的应用，因为前面对标签有一些处理，所以可以直接得到tag_id。picture_pictag是picture和pictag的many to many 关联表，上面那条sql的查询条件即为属于标签id为"361,368,409,367,415,410"的图片id，并且图片不在标签id"772,7,80"下。
现在这条sql的效率，应该是取决于临时表a的结果集，我分析这张临时表是没有索引可用的，不知道这样认为是否正确。
因为我发现上面这条sql语句的执行速度很快，0.032秒。但是如果属于标签下的id数量少，速度就不尽人意了。比如同样的sql语句，tag_id的范围改变，这样：select a.picture_id as picture_id
from ( select `picture_id`
from `picture_pictag`
where `tag_id` in (7,43)
group by `picture_id` having count(`picture_id`)=2
) a left join `acg_picture_pictag` b
on a.picture_id=b.picture_id
and b.tag_id in (1584,12531,247)
where b.picture_id is null执行时间为1.588秒。我发现select `picture_id`
from `picture_pictag`
where `tag_id` in (7,43)
group by `picture_id` having count(`picture_id`)=2这条sql生成的数据有2W多条。随后，我按照4楼的"rushm"兄的建议，修改了picture_pictag的UNIQUE KEY为(`tag_id`,`picture_id`)。
貌似奇迹就发生了...再执行上面那条sql语句，耗时为0.703秒，速度快了一倍。
现在问题来了。
1、请问UNIQUE KEY的顺序有什么影响？
2、因为修改UNIQUE KEY时我用了Navicat for Mysql这个工具，我发现表描述中，UNIQUE KEY这行变成了这样"  UNIQUE KEY `picture_id` USING BTREE (`tag_id`,`picture_id`)"，请问USING BTREE是什么意思？
3、还有更优化的sql语句吗？比如对临时表a建立一个索引如何？因为我是新手，查看到一些资料说对临时表建立索引，但是没看明白如何处理。此外，我这是一个网站上的应用，图片和标签更新很快，标签的查询条件，又是各式各样的，所以不适合做缓存，比如同样一个条件生成的sql语句，可能10分钟后，很多图片被更新，很多新的标签又建立出来了，结果也就有了很大的改变。在这种情况下，对临时表a建立索引，是否适合，在mysql中，需要花费多少时间对临时表建立一个索引呢？耗费机器的cpu开销和内存开销如何？

Answer 5

上面的"acg_picture_pictag"即为"picture_pictag"，我为了描述方便，把表前缀去掉了。

Answer 6

 select c.`picture_id` from `picture_pictag` as c,
  `picture_options` as d
  where c.tag_id in (361,368,409,367,415,410)
  and c.picture_id=d.id
  and d.picture_rating=1
  group by c.`picture_id` having count(c.`picture_id`)=6这个子查询速度如何

Answer 7

只查询tag_id in 某个集合的速度挺快的。我尝试了很多种情况，速度都不错。

Answer 8

这个顺序涉及到索引结构，索引也是讲究顺序的。
USING BTREE是说他试用BTREE索引结构来seek。
如果数据更新频率快，查询效率还行的话，执行次数不多的，我觉得没有必要再建个临时表来维护，0.几秒算可以介绍吧，呵呵