不断馈赠礼物-信息化专区

不断馈赠礼物

作者：xiaohui.com 编辑：杨晓勇 2007-06-05 00:00 来源：IT168�

我首先检查了 my.ini 的参数配置，一切正常。进入 MySQL 的命令行，调用 show processlist 语句，查找负荷最重的 SQL 语句，结合 Discuz 论坛的源码，发现有以下语句导致 CPU 上升：

mysql> show processlist;
+-----+------+----------------+---------+---------+------+------------+---------
-----------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+-----+------+----------------+---------+---------+------+------------+---------
-----------------------------------------------------------------+
| 363 | root | localhost:1393 | history | Query | 0 | statistics | SELECT C
OUNT(*) FROM cdb_pms WHERE msgfromid=?' AND folder='outbox' | 
+-----+------+----------------+---------+---------+------+------------+---------
　　检查 cdb_pms 表的结构：
mysql> show columns from cdb_pms;
+-----------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------------+------+-----+---------+----------------+
| pmid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| msgfrom | varchar(15) | NO | | | |
| msgfromid | mediumint(8) unsigned | NO | MUL | 0 | |
| msgtoid | mediumint(8) unsigned | NO | MUL | 0 | |
| folder | enum('inbox','outbox') | NO | | inbox | |
| new | tinyint(1) | NO | | 0 | |
| subject | varchar(75) | NO | | | |
| dateline | int(10) unsigned | NO | | 0 | |
| message | text | NO | | | |
| delstatus | tinyint(1) unsigned | NO | | 0 | |
+-----------+------------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

这条语句: WHERE msgfromid=?' AND folder='outbox'，我们看到，在 cdb_pms 表中，msgfromid 字段已经建立了索引，但是，folder 字段并没有。目前这个表已经有记录 7823 条。显然，这会对查询造成一定影响。于是为其建立索引：

mysql> ALTER TABLE `cdb_pms` ADD INDEX ( `folder` );
Query OK, 7823 rows affected (1.05 sec)
Records: 7823 Duplicates: 0 Warnings: 0

第1页：故障现象回放第2页：问题分析解决第3页：解决思路后记

关注我们