急求，mysql 经常导致CPU使用率100%

zcongwei

slow.log里面
提示信息为

/usr/local/mysql/libexec/mysqld, Version: 5.0.56-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 080727  0:15:17
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 68  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
use szxndiscuz;
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# Time: 080727  0:15:18
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 73  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 57  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 32  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 87  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 69  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 84  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 62  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 57  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 31  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# Time: 080727  0:15:19
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 63  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# Time: 080727  0:15:40
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 53  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 103  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 103  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 103  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 53  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 95  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 98  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# Time: 080727  0:15:49
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 32  Lock_time: 0  Rows_sent: 10  Rows_examined: 180
SELECT p.*, m.uid, m.username, m.groupid, m.adminid, m.regdate, m.lastactivity, m.posts, m.digestposts, m.oltime,^M
                m.pageviews, m.credits, m.extcredits1, m.extcredits2, m.extcredits3, m.extcredits4, m.extcredits5, m.extcredits6,^M
                m.extcredits7, m.extcredits8, m.email, m.gender, m.showemail, m.invisible, mf.nickname, mf.site,^M
                mf.icq, mf.qq, mf.yahoo, mf.msn, mf.taobao, mf.alipay, mf.location, mf.fromcity, mf.fromtown, mf.nowcity, mf.nowtown, mf.medals,^M
                mf.sightml AS signature, mf.customstatus, mf.spacename ^M
                FROM cdb_posts p^M
                LEFT JOIN cdb_members m ON m.uid=p.authorid^M
                LEFT JOIN cdb_memberfields mf ON mf.uid=m.uid^M
                WHERE p.tid='6559' AND p.invisible='0'   ORDER BY dateline LIMIT 50, 10;
# Time: 080727  0:20:12
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 156  Lock_time: 0  Rows_sent: 10  Rows_examined: 60
SELECT p.*, m.uid, m.username, m.groupid, m.adminid, m.regdate, m.lastactivity, m.posts, m.digestposts, m.oltime,^M
                m.pageviews, m.credits, m.extcredits1, m.extcredits2, m.extcredits3, m.extcredits4, m.extcredits5, m.extcredits6,^M
                m.extcredits7, m.extcredits8, m.email, m.gender, m.showemail, m.invisible, mf.nickname, mf.site,^M
                mf.icq, mf.qq, mf.yahoo, mf.msn, mf.taobao, mf.alipay, mf.location, mf.fromcity, mf.fromtown, mf.nowcity, mf.nowtown, mf.medals,^M
                mf.sightml AS signature, mf.customstatus, mf.spacename ^M
                FROM cdb_posts p^M
                LEFT JOIN cdb_members m ON m.uid=p.authorid^M
                LEFT JOIN cdb_memberfields mf ON mf.uid=m.uid^M
                WHERE p.tid='32691' AND p.invisible='0'   ORDER BY dateline LIMIT 10, 10;
# Time: 080727  0:20:13
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 266  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 ORDER BY t.lastpost DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 273  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 ORDER BY t.lastpost DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 265  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459

zcongwei

原帖由 枫影谁用了 于 2008-7-27 00:21 发表
另外看一下这里，看看关于order by的优化。

http://bbs.chinaunix.net/thread-969921-1-1.html

好的，我看看

zcongwei

原帖由 枫影谁用了 于 2008-7-27 00:20 发表
slow.log里有记录吗？

执行下面这个出来看看：
explain SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.da ...

执行这个是
mysql> explain SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
+----+-------------+-------+--------+---------------------+--------------+---------+------------------+-------+-----------------------------+
| id | select_type | table | type   | possible_keys       | key          | key_len | ref              | rows  | Extra                       |
+----+-------------+-------+--------+---------------------+--------------+---------+------------------+-------+-----------------------------+
|  1 | SIMPLE      | t     | range  | displayorder,typeid | displayorder | 2       | NULL             | 30451 | Using where; Using filesort |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY             | PRIMARY      | 2       | szxndiscuz.t.fid |     1 |                             |
+----+-------------+-------+--------+---------------------+--------------+---------+------------------+-------+-----------------------------+
2 rows in set (0.02 sec)

不过现在已经没什么人在线了，所以运行感觉比较快

zcongwei

原帖由 枫影谁用了 于 2008-7-27 00:23 发表
估计是语句的问题。

my.cnf里加大这些值看看：
sort_buffer_size = 1M

好，原来是sort_buffer_size = 1M

我现在加大到sort_buffer_size = 4M

zcongwei

经常show processlist 都是出现下面几句
我觉得应该主要问题就出在这几句中
不过不知道是什么问题
mysql> show full processlist;
+------+-----------+-----------+------------+---------+------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id   | User      | Host      | db         | Command | Time | State          | Info                                                                                                                                                                                                                                        |
+------+-----------+-----------+------------+---------+------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  395 | szxnmysql | localhost | szxndiscuz | Query   |    0 | NULL           | show full processlist                                                                                                                                                                                                                       |
| 1254 | szxnmysql | localhost | szxndiscuz | Query   |    1 | Sending data   | SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 AND t.dateline>1216486557 ORDER BY t.views DESC LIMIT 0, 10 |
| 1257 | szxnmysql | localhost | szxndiscuz | Query   |    4 | Sorting result | SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 ORDER BY t.lastpost DESC LIMIT 0, 10                        |
| 1259 | szxnmysql | localhost | szxndiscuz | Query   |    4 | Sorting result | SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 ORDER BY t.lastpost DESC LIMIT 0, 10                        |
| 1264 | szxnmysql | localhost | szxndiscuz | Query   |    5 | Sorting result | SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 ORDER BY t.lastpost DESC LIMIT 0, 10                        |
| 1265 | szxnmysql | localhost | szxndiscuz | Query   |    4 | Sorting result | SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 ORDER BY t.lastpost DESC LIMIT 0, 10                        |
| 1266 | szxnmysql | localhost | szxndiscuz | Query   |    0 | Opening tables | SELECT t.tid, t.closed, f.*, ff.*  , f.fid AS fid
                        FROM cdb_threads t
                        INNER JOIN cdb_forums f ON f.fid=t.fid
                        LEFT JOIN cdb_forumfields ff ON ff.fid=f.fid
                        WHERE t.tid='32692' AND t.displayorder>='0' LIMIT 1              |

zcongwei

在日志中也是
# Time: 080727  0:45:25
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 27  Lock_time: 1  Rows_sent: 50  Rows_examined: 61871
SELECT attach.aid, attach.attachment, t.tid, t.fid, t.subject FROM cdb_attachments attach LEFT JOIN cdb_threads t ON t.tid=attach.tid WHERE attach.readperm='0' AND displayorder>='0' AND filetype like 'image/%g%'  GROUP BY attach.tid ORDER BY attach.dateline DESC LIMIT 0, 50;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 30  Lock_time: 0  Rows_sent: 50  Rows_examined: 61871
SELECT attach.aid, attach.attachment, t.tid, t.fid, t.subject FROM cdb_attachments attach LEFT JOIN cdb_threads t ON t.tid=attach.tid WHERE attach.readperm='0' AND displayorder>='0' AND filetype like 'image/%g%'  GROUP BY attach.tid ORDER BY attach.dateline DESC LIMIT 0, 50;
# Time: 080727  0:56:10
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 12  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10;
# Time: 080727  0:56:13
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 11  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 ORDER BY t.lastpost DESC LIMIT 0, 10;
# Time: 080727  0:56:30
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 20  Lock_time: 3  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 ORDER BY t.lastpost DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 20  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 ORDER BY t.lastpost DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 21  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 ORDER BY t.lastpost DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 20  Lock_time: 0  Rows_sent: 10  Rows_examined: 30459
SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 ORDER BY t.lastpost DESC LIMIT 0, 10;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 15  Lock_time: 3  Rows_sent: 1  Rows_examined: 1
SELECT t.tid, t.closed, t.dateline, t.special, t.lastpost AS lastthreadpost,  f.*, ff.*  , f.fid AS fid^M
                        FROM cdb_threads t^M
                        INNER JOIN cdb_forums f ON f.fid=t.fid^M
                        LEFT JOIN cdb_forumfields ff ON ff.fid=f.fid  ^M
                        WHERE t.tid='11898' AND t.displayorder>='0' LIMIT 1;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 15  Lock_time: 3  Rows_sent: 1  Rows_examined: 1
SELECT t.tid, t.closed, t.dateline, t.special, t.lastpost AS lastthreadpost,  f.*, ff.*  , f.fid AS fid^M
                        FROM cdb_threads t^M
                        INNER JOIN cdb_forums f ON f.fid=t.fid^M
                        LEFT JOIN cdb_forumfields ff ON ff.fid=f.fid  ^M
                        WHERE t.tid='9640' AND t.displayorder>='0' LIMIT 1;
# User@Host: szxnmysql[szxnmysql] @ localhost []
# Query_time: 16  Lock_time: 4  Rows_sent: 1  Rows_examined: 1
SELECT t.tid, t.closed, f.*, ff.*  , f.fid AS fid^M
                        FROM cdb_threads t^M
                        INNER JOIN cdb_forums f ON f.fid=t.fid^M
                        LEFT JOIN cdb_forumfields ff ON ff.fid=f.fid  ^M
                        WHERE t.tid='32692' AND t.displayorder>='0' LIMIT 1;

zcongwei

原帖由 yueliangdao0608 于 2008-7-27 09:54 发表
贴mysqldumpslow -t 10 -a slow.log

显示如下
[root@localhost log]# mysqldumpslow -t 10 -a slow.log

Reading mysql slow query log from slow.log
Count: 123  Time=187.42s (23053s)  Lock=4.90s (603s)  Rows=10.0 (1230), szxnmysql[szxnmysql]@localhost
  SELECT t.*, f.name FROM cdb_threads t, cdb_forums f where f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) ORDER BY t.dateline DESC LIMIT 0, 10

Count: 1  Time=174.00s (174s)  Lock=0.00s (0s)  Rows=20.0 (20), szxnmysql[szxnmysql]@localhost
  SELECT t.tid, t.readperm, t.price, t.author, t.dateline, t.subject, p.message, p.status
  FROM cdb_threads t
  LEFT JOIN cdb_posts p ON p.tid=t.tid AND p.first='1'
  WHERE t.fid='45' AND t.displayorder>='0'
  ORDER BY t.dateline DESC LIMIT 20

Count: 1  Time=165.00s (165s)  Lock=0.00s (0s)  Rows=20.0 (20), szxnmysql[szxnmysql]@localhost
  SELECT t.tid, t.readperm, t.price, t.author, t.dateline, t.subject, p.message, p.status
  FROM cdb_threads t
  LEFT JOIN cdb_posts p ON p.tid=t.tid AND p.first='1'
  WHERE t.fid='35' AND t.displayorder>='0'
  ORDER BY t.dateline DESC LIMIT 20

Count: 75  Time=160.44s (12033s)  Lock=0.64s (48s)  Rows=9.4 (703), szxnmysql[szxnmysql]@localhost
  SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 ORDER BY t.lastpost DESC LIMIT 0, 10

Count: 2  Time=136.50s (273s)  Lock=0.00s (0s)  Rows=15.0 (30), szxnmysql[szxnmysql]@localhost
  SELECT t.tid,t.fid,t.readperm,t.author,t.authorid,t.subject,t.dateline,t.lastpost,t.lastposter,t.views,t.replies,t.highlight,t.digest,t.typeid
  FROM `cdb_threads` t WHERE t.readperm='0'
  AND t.fid = 29
  AND t.displayorder>='0'
  AND t.fid>'0'

  ORDER BY t.views DESC
  LIMIT 0,15

Count: 1  Time=120.00s (120s)  Lock=0.00s (0s)  Rows=5.0 (5), szxnmysql[szxnmysql]@localhost
  SELECT t.* FROM cdb_threads t
  WHERE t.fid='29'  AND t.displayorder IN (0, 1)
  ORDER BY t.displayorder DESC, t.lastpost DESC
  LIMIT 657, 30

Count: 1  Time=119.00s (119s)  Lock=0.00s (0s)  Rows=20.0 (20), szxnmysql[szxnmysql]@localhost
  SELECT t.tid, t.readperm, t.price, t.author, t.dateline, t.subject, p.message, p.status
  FROM cdb_threads t
  LEFT JOIN cdb_posts p ON p.tid=t.tid AND p.first='1'
  WHERE t.fid='13' AND t.displayorder>='0'
  ORDER BY t.dateline DESC LIMIT 20

Count: 1  Time=115.00s (115s)  Lock=0.00s (0s)  Rows=1.0 (1), szxnmysql[szxnmysql]@localhost
  SELECT COUNT(*) FROM cdb_threads WHERE fid='25' AND special='4' AND displayorder>='0'

Count: 1  Time=103.00s (103s)  Lock=0.00s (0s)  Rows=10.0 (10), szxnmysql[szxnmysql]@localhost
  SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 AND t.dateline>1216499374 ORDER BY t.views DESC LIMIT 0, 10

Count: 1  Time=95.00s (95s)  Lock=0.00s (0s)  Rows=10.0 (10), szxnmysql[szxnmysql]@localhost
  SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 AND t.dateline>1216498955 ORDER BY t.views DESC LIMIT 0, 10

[ 本帖最后由 zcongwei 于 2008-7-27 12:03 编辑 ]

zcongwei

原帖由 枫影谁用了 于 2008-7-27 08:33 发表


嗯 ，优化这些SQL语句吧。

哎，就不知道怎么去优化这些

所以要请教一下各位大虾呢

谢谢

zcongwei

原帖由 枫影谁用了 于 2008-7-27 12:44 发表

用explain看下。

贴下结果。

好，我运行了那几句，结果如下

mysql> explain SELECT attach.aid, attach.attachment, t.tid, t.fid, t.subject FROM cdb_attachments attach LEFT JOIN cdb_threads t ON t.tid=attach.tid WHERE attach.readperm='0' AND displayorder>='0' AND filetype like 'image/%g%'  GROUP BY attach.tid ORDER BY attach.dateline DESC LIMIT 0, 50;
+----+-------------+--------+--------+---------------+---------+---------+-----------------------+-------+----------------------------------------------+
| id | select_type | table  | type   | possible_keys | key     | key_len | ref                   | rows  | Extra                                        |
+----+-------------+--------+--------+---------------+---------+---------+-----------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | attach | index  | tid           | tid     | 3       | NULL                  | 28650 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | t      | eq_ref | PRIMARY       | PRIMARY | 3       | szxndiscuz.attach.tid |     1 | Using where                                  |
+----+-------------+--------+--------+---------------+---------+---------+-----------------------+-------+----------------------------------------------+
2 rows in set (0.05 sec)

mysql> explain SELECT t.*, f.name FROM cdb_threads t, cdb_forums f WHERE f.fid=t.fid AND f.fid not in (41) AND t.displayorder not in (-1,-2) AND t.closed NOT LIKE 'moved|%' AND t.replies !=0 ORDER BY t.lastpost DESC LIMIT 0, 10;
+----+-------------+-------+--------+---------------------+--------------+---------+------------------+-------+-----------------------------+
| id | select_type | table | type   | possible_keys       | key          | key_len | ref              | rows  | Extra                       |
+----+-------------+-------+--------+---------------------+--------------+---------+------------------+-------+-----------------------------+
|  1 | SIMPLE      | t     | range  | displayorder,typeid | displayorder | 2       | NULL             | 30457 | Using where; Using filesort |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY             | PRIMARY      | 2       | szxndiscuz.t.fid |     1 |                             |
+----+-------------+-------+--------+---------------------+--------------+---------+------------------+-------+-----------------------------+
2 rows in set (0.00 sec)


mysql> explain SELECT t.tid, t.readperm, t.price, t.author, t.dateline, t.subject, p.message, p.status
    ->   FROM cdb_threads t
    ->   LEFT JOIN cdb_posts p ON p.tid=t.tid AND p.first='1'
    ->   WHERE t.fid='45' AND t.displayorder>='0'
    ->   ORDER BY t.dateline DESC LIMIT 20;
+----+-------------+-------+-------+---------------------+--------------+---------+------------------------+------+-----------------------------+
| id | select_type | table | type  | possible_keys       | key          | key_len | ref                    | rows | Extra                       |
+----+-------------+-------+-------+---------------------+--------------+---------+------------------------+------+-----------------------------+
|  1 | SIMPLE      | t     | range | displayorder,typeid | displayorder | 3       | NULL                   |  680 | Using where; Using filesort |
|  1 | SIMPLE      | p     | ref   | displayorder,first  | first        | 4       | szxndiscuz.t.tid,const |   46 |                             |
+----+-------------+-------+-------+---------------------+--------------+---------+------------------------+------+-----------------------------+
2 rows in set (0.03 sec)

zcongwei

不能对得很齐