向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除|赢在IT，Playin' with IT,Focus on Killer Application,Marketing Meets Technology.

我用的bo-blog，刚才，发现博客的服务器的负载很高，就一博客，不致与：
[root@jackxiang ~]# uptime 19:44:57 up 21 days,  6:11,  2 users,  load average: 28.47, 25.09, 21.21
Top一看：
PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                 2372 mysql     15   0  201m  13m 3356 R 36.7  4.7   1557:17 mysqld    
Mysql:
show processlist
如下：
| 1181636 | jackxiang_mysql | localhost | jackxiang_mysql | Query   |    0 | Waiting for table level lock | SELECT `blogid`,`pubtime`,`edittime`,`blogalias` FROM `boblog_blogs` WHERE `property`<2  ORDER BY `p | | 1184593 | jackxiang_mysql | localhost | jackxiang_mysql | Query   |    0 | Writing to net | SELECT * FROM `boblog_blogs` WHERE `property`<2 AND `frontpage`=0 ORDER BY `sticky`DESC, `pubtime` | 1184668 | jackxiang_mysql | localhost | jackxiang_mysql | Query   |    0 | Writing to net | SELECT * FROM `boblog_blogs` WHERE `property`<2 AND `frontpage`=0 ORDER BY `sticky`DESC, `pubtime` D |

一看索引没有，赶紧加上索引：


SELECT `blogid`,`pubtime`,`edittime`,`blogalias` FROM `boblog_blogs` WHERE `property`<2  ORDER BY
alter table `boblog_blogs` add index property(property);

SELECT `blogid`,`title`,`blogalias` FROM `boblog_blogs` WHERE `property`<'2' AND `pubtime`<'13040783';
alter table `boblog_blogs` add index property_pubtime(property,pubtime);

SELECT * FROM `boblog_blogs` WHERE `property`<2 AND `frontpage`=0 ORDER BY `sticky`DESC, `pubtime`;
alter table `boblog_blogs` add index property_frontpage(property,frontpage);

博客次数和上面是一个表，索引加多了，update效率会不会下降？如下：
UPDATE LOW_PRIORITY `boblog_blogs` SET `views`=`views`+1 WHERE `blogid`='4257'
看来一个博客的访问设计仍旧需要考究和研究，我的博文也就3000多，DB性能就显现出来了。
后记：
查看后依旧有这样那样的小问题，但sleep和锁表大为缓解。
mysql> explain SELECT * FROM `boblog_blogs` WHERE `property`<2 AND `frontpage`=0 ORDER BY `sticky`DESC, `pubtime`; +----+-------------+--------------+-------+----------------------------------------------+----------+---------+------+------+-----------------------------+ | id | select_type | table        | type  | possible_keys                                | key      | key_len | ref  | rows | Extra                       | +----+-------------+--------------+-------+----------------------------------------------+----------+---------+------+------+-----------------------------+ |  1 | SIMPLE      | boblog_blogs | range | property,property_pubtime,property_frontpage | property | 4       | NULL | 3485 | Using where; Using filesort | +----+-------------+--------------+-------+----------------------------------------------+----------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec)

后来，负载降低，由25降低到5，说明还是有点效果滴嘛：
top - 20:18:21 up 21 days,  6:45,  2 users,  load average: 4.03, 4.28, 8.81

mysql> explain SELECT * FROM `boblog_blogs` WHERE `property`<2 AND `frontpage`=0 ORDER BY `sticky`DESC, `pubtime`;

+----+-------------+--------------+-------+----------------------------------------------+----------+---------+------+------+-----------------------------+
| id | select_type | table        | type  | possible_keys                                | key      | key_len | ref  | rows | Extra                       |
+----+-------------+--------------+-------+----------------------------------------------+----------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | boblog_blogs | range | property,property_pubtime,property_frontpage | property | 4       | NULL | 3526 | Using where; Using filesort |
+----+-------------+--------------+-------+----------------------------------------------+----------+---------+------+------+-----------------------------+

作者：jackxiang@向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除
地址：https://jackxiang.com/post/4280/
版权所有。转载时必须以链接形式注明作者和原始出处及本声明！