mysqld有时候占用CPU搞，请高手指导

wqivblpk

mysqld有时候占用CPU搞，请高手指导这是一个DISCUZ论坛
这是记录的超过5秒的慢查询语句，请高手指点

将哪个表的哪个字段设置索引，如何操作


MYSQL经常锁死，表现为网页打不开或者缓慢


MySQLa, Version: 5.5.40 (MySQL Community Server (GPL)). started with:
TCP Port: 6306, Named Pipe: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 151105 15:14:35
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.312602  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 185087
use ultrax;
SET timestamp=1446707675;
SELECT count(*) FROM pre_forum_thread where authorid='3' and displayorder!=-2 and displayorder!=-1;
# Time: 151105 15:14:37
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.750111  Lock_time: 0.000000 Rows_sent: 12  Rows_examined: 185111
SET timestamp=1446707677;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3  and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 15:14:40
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.687609  Lock_time: 0.000000 Rows_sent: 12  Rows_examined: 185111
SET timestamp=1446707680;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3  and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.296976  Lock_time: 0.000000 Rows_sent: 12  Rows_examined: 185111
SET timestamp=1446707680;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3  and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 15:14:41
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.890738  Lock_time: 0.000000 Rows_sent: 12  Rows_examined: 185111
SET timestamp=1446707681;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3  and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 15:14:42
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 6.609502  Lock_time: 0.000000 Rows_sent: 12  Rows_examined: 185111
SET timestamp=1446707682;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3  and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 16:03:29
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 55.031954  Lock_time: 0.000000 Rows_sent: 10  Rows_examined: 743696
SET timestamp=1446710609;
select a.tid,a.subject,a.dateline,a.views,a.replies,a.author,a.authorid,b.message from pre_forum_thread a join pre_forum_post b on a.tid=b.tid where a.attachment!=0 and a.displayorder>=0 and b.first=1 order by tid desc limit 0,10;
# Time: 151105 16:04:18
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 46.578721  Lock_time: 0.000000 Rows_sent: 10  Rows_examined: 743698
SET timestamp=1446710658;
select a.tid,a.subject,a.dateline,a.views,a.replies,a.author,a.authorid,b.message from pre_forum_thread a join pre_forum_post b on a.tid=b.tid where a.attachment!=0 and a.displayorder>=0 and b.first=1 order by tid desc limit 0,10;
# Time: 151105 16:04:30
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 6.765712  Lock_time: 1.531270 Rows_sent: 12  Rows_examined: 185111
SET timestamp=1446710670;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3  and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 16:04:46
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 7.078215  Lock_time: 0.000000 Rows_sent: 12  Rows_examined: 185111
SET timestamp=1446710686;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3  and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 16:13:25
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.718823  Lock_time: 0.406255 Rows_sent: 12  Rows_examined: 185111
SET timestamp=1446711205;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3  and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 16:13:52
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 6.546959  Lock_time: 1.296892 Rows_sent: 12  Rows_examined: 185111
SET timestamp=1446711232;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3  and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 16:13:53
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 6.609459  Lock_time: 0.625008 Rows_sent: 12  Rows_examined: 185111
SET timestamp=1446711233;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3  and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;
# Time: 151105 16:14:30
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 5.687573  Lock_time: 0.656259 Rows_sent: 12  Rows_examined: 185111
SET timestamp=1446711270;
SELECT a.tid,a.subject,a.fid,b.name FROM pre_forum_thread a inner join pre_forum_forum b on a.fid=b.fid where a.authorid=3  and a.displayorder!=-2 and a.displayorder!=-1 order by a.dateline DESC LIMIT 0,12;

nycle

几个原则：
1.索引通常建在过滤字段或关联字段。
2.尽量少count，数据量大时，考虑变通实现。
3.索引字段上的不等于的条件，采用变通的实现方式。

kider

另外你需重点关注下，这样的语句为啥出奇的慢：
select a.tid,a.subject,a.dateline,a.views,a.replies,a.author,a.authorid,b.message from pre_forum_thread a join pre_forum_post b on a.tid=b.tid where a.attachment!=0 and a.displayorder>=0 and b.first=1 order by tid desc limit 0,10;
检查表字段a.tid=b.tid，a.displayorder，b.first=1是否有索引。
order by 后的tid字段前最好加上表名，因为两个表中都有这个字段。

kider

至少这句话，在我这里的discuz数据库中运行很快。

在cpu高的时候，用show processlist,找找MySQL里的运行慢的SQL，有可能也是其他慢SQL引起的...