EasyXSS更新 MySql表优化 2013.03.12

今天很烦躁，没什么心情。vps客服说耗CPU很严重，索性跟了下xss.tw的性能问题。 top -c 使用top命令，发现，mysqld的进程占CPU居高不下，跟进mysql的语句： mysql show processlist;+-----+------+-----------+------+---------+------+--------------+-----

今天很烦躁，没什么心情。vps客服说耗CPU很严重，索性跟了下xss.tw的性能问题。

top -c

使用top命令，发现，mysqld的进程占CPU居高不下，跟进mysql的语句：

mysql> show processlist;
+-----+------+-----------+------+---------+------+--------------+----------------------------------------------------------+
| Id  | User | Host      | db   | Command | Time | State        | Info                                                     |
+-----+------+-----------+------+---------+------+--------------+----------------------------------------------------------+
| 213 | root | localhost | NULL | Query   |    0 | NULL         | show processlist                                         |
| 313 | xss  | localhost | xss  | Query   |    0 | Sending data | SELECT * FROM `xss_result_data` WHERE ( `rid` = 254565 ) |
| 316 | xss  | localhost | xss  | Query   |    1 | Sending data | SELECT * FROM `xss_result_data` WHERE ( `rid` = 702159 ) |
| 317 | xss  | localhost | xss  | Query   |    1 | Sending data | SELECT * FROM `xss_result_data` WHERE ( `rid` = 468659 ) |
| 319 | xss  | localhost | xss  | Query   |    1 | Sending data | SELECT * FROM `xss_result_data` WHERE ( `rid` = 797221 ) |
| 321 | xss  | localhost | xss  | Query   |    1 | Sending data | SELECT * FROM `xss_result_data` WHERE ( `rid` = 581303 ) |
+-----+------+-----------+------+---------+------+--------------+----------------------------------------------------------+
6 rows in set (0.00 sec)

发现耗时基本上都在1秒以上。 rid是外键，那么给它个索引。

ALTER TABLE `xss_result_data` ADD INDEX(`rid`)

运行这条mysql语句时，我才恍然大悟，原来设计EasyXSS的数据库时，其实没有任何一个表做过索引，怪不得性能那么差劲。随即继续跟进processlist，把耗时1秒以上的语句，对表字段适当的做了索引，速度就快起来了。查询processlist，都查不到语句在跑了，哈哈。

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 54 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 54 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 54 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 54 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> 
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 54 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

现在cpu的负载明显降低很多很多很多很多很多。。。 原来客服说，长期大于5，才把server给关闭的。现在，0.1都不到。。

load average: 0.00, 0.01, 0.09

原文地址：EasyXSS更新 MySql表优化 2013.03.12, 感谢原作者分享。