mysql cpu达到100%的罪魁祸首
一、使用top命令看到的情况如下：
可以看到服务器负载很高，，mysql CPU使用已达到接近400%（因为是四核，所以会有超过100%的情况）。
二、在服务器上执行mysql -u root -p之后，输入show full processlist; 可以看到正在执行的语句。
可以看到是下面的SQL语句执行耗费了较长时间。
SELECT id,title,most_top,view_count,posttime FROM article
where status=3 AND catalog_id in (select catalog_id from catalog where catalog_id=17 or parent_id=17)
order by most_top desc,posttime desc limit 0,8
但是从数据库设计方面来说，该做的索引都已经做了，SQL语句似乎没有优化的空间。
直接执行此条SQL，发现速度很慢，需要1-6秒的时间（跟mysql正在并发执行的查询有关，如果没有并发的，需要1秒多）。如果把排序依据改为一个，则查询时间可以缩短至0.01秒（most_top）或者0.001秒（posttime）。
第一步肯定是线看看是不是mysql使用的线程十分多，如果线程十分多可能出现两种情况 nginx 访问量比较大，要不就是程序架构出现问题，导致cpu占用十分高
show full processlist;
+--------+------+-----------------+--------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------------+--------+---------+------+-------+-----------------------+
| 386527 | root | 127.0.0.1:49172 | screen | Sleep | 1 | | NULL |
| 386528 | root | 127.0.0.1:49173 | screen | Sleep | 2 | | NULL |
| 386529 | root | 127.0.0.1:49174 | screen | Sleep | 1 | | NULL |
| 386530 | root | 127.0.0.1:49175 | screen | Sleep | 0 | | NULL |
| 524172 | root | localhost | NULL | Query | 0 | init | show full processlist |
+--------+------+-----------------+--------+---------+------+-------+-----------------------+
5 rows in set (0.00 sec)
可以看出只有5个线程，所以这不是mysql cpu占用100%的使用原因
show variables like '%slowquerylog%';
查找慢日志查询
+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /data/mysql/long_logs/long.log |
+---------------------+--------------------------------+
日志位置位于/data/mysql/long_logs/long.log
vim /data/mysql/long_logs/long.log
查看慢日志
# Time: 170104 10:41:04 # User@Host: root[root] @ [127.0.0.1] Id: 785264 # Query_time: 2.082531 Lock_time: 0.000132 Rows_sent: 0 Rows_examined: 437963 SET timestamp=1483497664; SELECT id,update_time,update_end_time,ctime FROM `sg_point_log` WHERE pid = 105 AND date(ctime) = curdate() AND (update_time != 0 OR update_end_time != 0) ORDER BY id DESC limit 1; # Time: 170104 10:42:06 # User@Host: root[root] @ [127.0.0.1] Id: 785461 # Query_time: 2.037191 Lock_time: 0.000162 Rows_sent: 0 Rows_examined: 438013 SET timestamp=1483497726; SELECT id,update_time,update_end_time,ctime FROM `sg_point_log` WHERE pid = 203 AND date(ctime) = curdate() AND (update_time != 0 OR update_end_time != 0) ORDER BY id DESC limit 1; # Time: 170104 10:42:10 # User@Host: root[root] @ [127.0.0.1] Id: 785549 # Query_time: 2.003415 Lock_time: 0.000071 Rows_sent: 0 Rows_examined: 438016 SET timestamp=1483497730; SELECT id,update_time,update_end_time,ctime FROM `sg_point_log` WHERE pid = 281 AND date(ctime) = curdate() AND (update_time != 0 OR update_end_time != 0) ORDER BY id DESC limit 1; # Time: 170104 10:42:14 # User@Host: root[root] @ [127.0.0.1] Id: 785264 # Query_time: 2.152803 Lock_time: 0.000127 Rows_sent: 0 Rows_examined: 438018 SET timestamp=1483497734; SELECT id,update_time,update_end_time,ctime FROM `sg_point_log` WHERE pid = 8 AND date(ctime) = curdate() AND (update_time != 0 OR update_end_time != 0) ORDER BY id DESC limit 1; # Time: 170104 10:42:16 # User@Host: root[root] @ [127.0.0.1] Id: 785264 # Query_time: 2.040670 Lock_time: 0.000077 Rows_sent: 0 Rows_examined: 438021 SET timestamp=1483497736; SELECT id,upload_img_time,ctime FROM `sg_point_log` WHERE pid = 7 AND date(ctime) = curdate() AND upload_img_time != 0 ORDER BY id DESC limit 1; # Time: 170104 10:42:18 # User@Host: root[root] @ [127.0.0.1] Id: 785264 # Query_time: 2.139948 Lock_time: 0.000124 Rows_sent: 0 Rows_examined: 438025 SET timestamp=1483497738; SELECT id,update_time,update_end_time,ctime FROM `sg_point_log` WHERE pid = 7 AND date(ctime) = curdate() AND (update_time != 0 OR update_end_time != 0) ORDER BY id DESC limit 1;
可以看出罪魁祸首就是
SELECT id,upload_img_time,ctime FROM `sg_point_log` WHERE pid = 1105 AND date(ctime) = curdate() AND upload_img_time != 0 ORDER BY id DESC limit 1;
然后用mysql 执行这个sql语句速度居然慢到
mysql> SELECT id,upload_img_time,ctime FROM `sg_point_log` WHERE pid = 1105 AND date(ctime) = curdate() AND upload_img_time != 0 ORDER BY id DESC limit 1;
