相关帖子功能，聚合标签查找相关帖子功能，这货是个技术活！没干好，就数据库负载9000

SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('2736','4046','42478','3415') AND idtype='tid' LIMIT 20;
# Time: 120809 15:57:47
# User@Host: [root] @  [192.168.100.20]
# Query_time: 2.458414  Lock_time: 0.000031 Rows_sent: 0  Rows_examined: 42113
SET timestamp=1344499067;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('52856') AND idtype='tid' LIMIT 20;
# Time: 120809 15:57:48
# User@Host: [root] @  [192.168.100.20]
# Query_time: 6.835308  Lock_time: 0.000038 Rows_sent: 20  Rows_examined: 132315
SET timestamp=1344499068;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('2332','24294','34857') AND idtype='tid' LIMIT 20;
# Time: 120809 15:57:49
# User@Host: [root] @  [192.168.100.20]
# Query_time: 2.584774  Lock_time: 0.000032 Rows_sent: 0  Rows_examined: 42113
SET timestamp=1344499069;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('51549') AND idtype='tid' LIMIT 20;
# Time: 120809 15:57:51
# User@Host: [root] @  [192.168.100.20]
# Query_time: 36.540090  Lock_time: 0.000050 Rows_sent: 0  Rows_examined: 622808
SET timestamp=1344499071;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('45128','45031') AND idtype='tid' LIMIT 20;
# Time: 120809 15:57:52
# User@Host: [root] @  [192.168.100.20]
# Query_time: 4.407871  Lock_time: 0.000042 Rows_sent: 20  Rows_examined: 79405
SET timestamp=1344499072;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('8146','6331','44815') AND idtype='tid' LIMIT 20;
# Time: 120809 15:57:54
# User@Host: [root] @  [192.168.100.20]
# Query_time: 3.044398  Lock_time: 0.000035 Rows_sent: 0  Rows_examined: 42113
SET timestamp=1344499074;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('53668') AND idtype='tid' LIMIT 20;
# Time: 120809 15:57:55
# User@Host: [root] @  [192.168.100.20]
# Query_time: 2.065762  Lock_time: 0.000046 Rows_sent: 25  Rows_examined: 71582
SET timestamp=1344499075;
SELECT t.* FROM pre_forum_thread t
                WHERE  t.fid='243'  AND (t.displayorder IN (0, 1, 2, 3, 4))
                ORDER BY t.displayorder DESC, t.dateline DESC
                LIMIT 0, 25;
# Time: 120809 15:57:56
# User@Host: [root] @  [192.168.100.20]
# Query_time: 22.351596  Lock_time: 0.000040 Rows_sent: 20  Rows_examined: 354056
SET timestamp=1344499076;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('4429','1896','51486') AND idtype='tid' LIMIT 20;
# Time: 120809 15:57:57
# User@Host: [root] @  [192.168.100.20]
# Query_time: 21.748788  Lock_time: 0.000059 Rows_sent: 20  Rows_examined: 354056
SET timestamp=1344499077;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('4429','1896','51486') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:01
# User@Host: [root] @  [192.168.100.20]
# Query_time: 23.450715  Lock_time: 0.000036 Rows_sent: 20  Rows_examined: 391565
SET timestamp=1344499081;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('53643','21824') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:06
# User@Host: [root] @  [192.168.100.20]
# Query_time: 23.220620  Lock_time: 0.000047 Rows_sent: 20  Rows_examined: 448623
SET timestamp=1344499086;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('48986','24225') AND idtype='tid' LIMIT 20;
# User@Host: [root] @  [192.168.100.20]
# Query_time: 35.107119  Lock_time: 0.000033 Rows_sent: 4  Rows_examined: 622810
SET timestamp=1344499086;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('53393','26505') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:08
# User@Host: [root] @  [192.168.100.20]
# Query_time: 32.128709 Lock_time: 0.000040 Rows_sent: 11  Rows_examined: 622810
SET timestamp=1344499088;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('53683','12537') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:10
# User@Host: [root] @  [192.168.100.20]
# Query_time: 2.115500  Lock_time: 0.000033 Rows_sent: 0  Rows_examined: 42113
SET timestamp=1344499090;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('44442') AND idtype='tid' LIMIT 20;
# User@Host: [root] @  [192.168.100.20]
# Query_time: 32.045817  Lock_time: 0.000056 Rows_sent: 6  Rows_examined: 622810
SET timestamp=1344499090;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('44900','8243','23577') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:12
# User@Host: [root] @  [192.168.100.20]
# Query_time: 2.375137  Lock_time: 0.000040 Rows_sent: 20  Rows_examined: 57649
SET timestamp=1344499092;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('2736','4046','42478','3415') AND idtype='tid' LIMIT 20;
# User@Host: [root] @  [192.168.100.20]
# Query_time: 2.098018  Lock_time: 0.000037 Rows_sent: 20  Rows_examined: 57649
SET timestamp=1344499092;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('2736','4046','42478','3415') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:14
# User@Host: [root] @  [192.168.100.20]
# Query_time: 11.641370  Lock_time: 0.000037 Rows_sent: 20  Rows_examined: 311937
SET timestamp=1344499094;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('2668','36100','4390','26898') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:16
# User@Host: [root] @  [192.168.100.20]
# Query_time: 28.873743  Lock_time: 0.000042 Rows_sent: 20  Rows_examined: 620153
SET timestamp=1344499096;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('48838','5017') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:18
# User@Host: [root] @  [192.168.100.20]
# Query_time: 29.299338  Lock_time: 0.000040 Rows_sent: 11  Rows_examined: 622810
SET timestamp=1344499098;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('53683','12537') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:26
# User@Host: [root] @  [192.168.100.20]
# Query_time: 27.491128 Lock_time: 0.000040 Rows_sent: 11  Rows_examined: 622814
SET timestamp=1344499106;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('53683','12537') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:28
# User@Host: [root] @  [192.168.100.20]
# Query_time: 19.806618  Lock_time: 0.000037 Rows_sent: 20  Rows_examined: 448623
SET timestamp=1344499108;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('48986','24225') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:29
# User@Host: [root] @  [192.168.100.20]
# Query_time: 19.408712  Lock_time: 0.000035 Rows_sent: 20  Rows_examined: 448623
SET timestamp=1344499109;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('48986','24225') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:36
# User@Host: [root] @  [192.168.100.20]
# Query_time: 21.823272  Lock_time: 0.000060 Rows_sent: 7  Rows_examined: 622819
SET timestamp=1344499116;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('44883','38218','53363','12539') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:37
# User@Host: [root] @  [192.168.100.20]
# Query_time: 24.360117  Lock_time: 0.000036 Rows_sent: 14  Rows_examined: 622819
SET timestamp=1344499117;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('53597','15853') AND idtype='tid' LIMIT 20;
# User@Host: [root] @  [192.168.100.20]
# Query_time: 24.772675  Lock_time: 0.000037 Rows_sent: 20  Rows_examined: 614664
SET timestamp=1344499117;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('661','49399') AND idtype='tid' LIMIT 20;
# User@Host: [root] @  [192.168.100.20]
# Query_time: 4.323386  Lock_time: 0.000060 Rows_sent: 20  Rows_examined: 100364
SET timestamp=1344499117;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('1012','53115','3533','4836') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:40
# User@Host: [root] @  [192.168.100.20]
# Query_time: 20.987766  Lock_time: 0.000039 Rows_sent: 20  Rows_examined: 612703
SET timestamp=1344499120;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('53168','23845') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:41
# User@Host: [root] @  [192.168.100.20]
# Query_time: 8.982803  Lock_time: 0.000040 Rows_sent: 20  Rows_examined: 250567
SET timestamp=1344499121;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('5966','34609','12073','34610') AND idtype='tid' LIMIT 20;
# Time: 120809 15:58:43
# User@Host: [root] @  [192.168.100.20]
# Query_time: 7.848113  Lock_time: 0.000041 Rows_sent: 20  Rows_examined: 230907
SET timestamp=1344499123;
SELECT itemid FROM pre_common_tagitem WHERE tagid IN ('1641','51014') AND idtype='tid' LIMIT 20;
后面的就不贴了，太多了，几乎可以用“此出省略一万字”来表示。

数据库负载一度达到9000，CPU占用率到达300%，top一看，一个mysqld进程给独占了。
帖子数大概30000000，tag数640000.

该问题我们再核查下，感谢反馈！

第一、应该添加索引，这是必须的，随着帖子数增长，tag也会增长，select in方式本来就效率低。
第二、应该缓存查询至少一个小时以上。

zhengwu119 发表于 2012-8-10 09:55
第一、应该添加索引，这是必须的，随着帖子数增长，tag也会增长，select in方式本来就效率低。
第二、应该 ...

检查一下是否使用上了索引，也可以尝试重建一下索引
这TAG好像也太多了点儿，我们也会再讨论一下

这应该不算多的吧，我见过更多的。现在只能暂时关闭相关帖子功能。