今天很烦躁，没什么心情。vps客服说耗CPU很严重，索性跟了下xss.tw的性能问题。 top -c 使用top命令，发现，mysqld的进程占CPU居高不下，跟进mysql的语句： mysql show processlist;+-----+------+-----------+------+---------+------+--------------+-----
今天很烦躁，没什么心情。vps客服说耗CPU很严重，索性跟了下xss.tw的性能问题。
使用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, 感谢原作者分享。
