如何调优表中包含MySQL行的7表-联接30,000+计数查询？
如何调优表中包含MySQL行的7表-联接30,000+计数查询？
提问于 2010-06-30 16:38:51
我有一个sql查询，它计算复杂查询的结果数。当限制到20个结果时，实际的select查询非常快，但是经过大量优化后，计数版本在我当前的表上大约需要4.5秒。
如果删除站点标记和图片库标签上的两个联接和where子句，则查询执行时间为1.5秒。如果我创建3个单独的查询--一个用于选择支付站点，一个用于选择名称，另一个用于将所有内容组合在一起--我可以将查询降到.6秒，这仍然不够好。这也会迫使我使用存储过程，因为我必须在Hibernate中总共进行4次查询。
对于“原样”的查询，以下是一些信息：
Handler_read_key是1746669
Handler_read_next是1546324
图库表有40,000行
站点表有900行。
name表有800行。
标签表有3560行。
我对MySQL和调优非常陌生，我在以下方面有索引：
- 标签表中的“术语”列
- 图库表中的“已发布”列
- 名称表的“值”
我希望这个查询达到0.1毫秒。
SELECT count(distinct gallery.id)
from gallery gallery
inner join
site site
on gallery.site_id = site.id
inner join
site_to_tag p2t
on site.id = p2t.site_id
inner join
tag site_tag
on p2t.tag_id = site_tag.id
inner join
gallery_to_name g2mn
on gallery.id = g2mn.gallery_id
inner join
name name
on g2mn.name_id = name.id
inner join
gallery_to_tag g2t
on gallery.id = g2t.gallery_id
inner join
tag tag
on g2t.tag_id = tag.id
where
gallery.published = true and (
name.value LIKE 'sometext%' or
tag.term = 'sometext' or
site.`name` like 'sometext%' or
site_tag.term = 'sometext'
)解释数据：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+-------------------------------------------------------------------+--------------------+---------+-------------------------------------------+------+------------------------------------+
| 1 | SIMPLE | site | index | PRIMARY,nameIndex | nameIndex | 258 | NULL | 950 | Using index; Using temporary |
| 1 | SIMPLE | gallery | ref | PRIMARY,publishedIndex,FKF44C775296EECE37,publishedSiteIdIndex | FKF44C775296EECE37 | 9 | production.site.id | 20 | Using where |
| 1 | SIMPLE | g2mn | ref | PRIMARY,FK3EFFD7F8AFAD7A5E,FK3EFFD7F832C04188 | FK3EFFD7F8AFAD7A5E | 8 | production.gallery.id | 1 | Using index; Distinct |
| 1 | SIMPLE | name | eq_ref | PRIMARY,valueIndex | PRIMARY | 8 | production.g2mn.name_id | 1 | Distinct |
| 1 | SIMPLE | g2t | ref | PRIMARY,FK3DDB4D63AFAD7A5E,FK3DDB4D63E210FBA6 | FK3DDB4D63AFAD7A5E | 8 | production.g2mn.gallery_id | 2 | Using where; Using index; Distinct |
| 1 | SIMPLE | tag | eq_ref | PRIMARY,termIndex | PRIMARY | 8 | production.g2t.tag_id | 1 | Distinct |
| 1 | SIMPLE | p2t | ref | PRIMARY,FK29424AB796EECE37,FK29424AB7E210FBA6 | PRIMARY | 8 | production.gallery.site_id | 3 | Using where; Using index; Distinct |
| 1 | SIMPLE | site_tag | eq_ref | PRIMARY,termIndex | PRIMARY | 8 | production.p2t.tag_id | 1 | Using where; Distinct |
+----+-------------+--------------+--------+-------------------------------------------------------------------+--------------------+---------+-------------------------------------------+------+------------------------------------+个人计数速度：
[SQL] select count(*) from gallery;
Affected rows: 0
Time: 0.014ms
Results: 40385
[SQL]
select count(*) from gallery_to_name;
Affected rows: 0
Time: 0.012ms
Results: 35615
[SQL]
select count(*) from gallery_to_tag;
Affected rows: 0
Time: 0.055ms
Results: 165104
[SQL]
select count(*) from tag;
Affected rows: 0
Time: 0.002ms
Results: 3560
[SQL]
select count(*) from site;
Affected rows: 0
Time: 0.001ms
Results: 901
[SQL]
select count(*) from site_to_tag;
Affected rows: 0
Time: 0.003ms
Results: 7026页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/3151410
复制相关文章
相似问题
腾讯云开发者
