EasyXSS更新 MySql表优化 2013.03.12-mysql教程-PHP中文网

EasyXSS更新 MySql表优化 2013.03.12

php中文网

发布： 2016-06-07 16:35:05

原创

1358人浏览过

今天很烦躁，没什么心情。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，都查不到语句在跑了，哈哈。

2088shop商城购物系统

2088shop商城购物系统是商城系统中功能最全的一个版本：非会员购物、商品无限级分类、不限商品数量、商品多级会员定价、上货库存、Word在线编辑器、订单详情销售报表、商品评论、留言簿、管理员多级别、VIP积分、会员注册积分奖励、智能新闻发布、滚动公告、投票调查、背景图片颜色更换、店标上传、版权联系方式修改、背景音乐（好歌不断）、广告图片支持Flash、弹出浮动广告、搜索引擎关健词优化、图文友情联

查看详情

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都不到。。