我的服务器配置是2核 2g的 Linux系统，安装一个淘宝客系统，这个可以采集淘宝商品，一开始采集还可以，但是当数据表里面数量达到17万以上的时候，就显得特别慢，因为他在采集的时候 要查询这个东西是不是已经被采集过，应该去每采集一次就会扫表一次，，mysql进程占用 cpu 98%以上。我不相信是服务器配置不够所照成的，因为2核2g这个配置并不是很低，而且还是Linux系统，如果17w数据都带不动，那么Linux比Windows强不了多少，还不如Windows啊。不知道是不是我哪里配置 设置不对，开始以为表没有索引，但是看了，索引也有，我通过mysql查询记录发现，采集任务在采集的时候，如果这个商品一经被采集过，他不是不采集，而是执行更新，所以他在采集的时候，后台执行大量update更新操作，导致cpu 占用100%，请大侠指导，有没有办法在不改变采集程序情况下把 执行更新的语句cpu占用率降下来，mysql 怎么优化？
下面是程序在采集的时候，mysql 记录，源代码粘贴不了，只有截图了。
下面是程序在采集的时候，mysql 记录，源代码粘贴不了，只有截图了。
你用workbench监控一下，看看你的mysql的基本信息，包括每秒执行事务数量，每秒执行查询数量，有了这些基本的数据，才能判断，到底是什么原因导致cpu那么高的。
监控一下哪些sql消耗的资源最多，看看能否优化
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.254568 Lock_time: 0.000088 Rows_sent: 1 Rows_examined: 191779
SET timestamp=1452683291;
# Time: 160113 19:08:49
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.980830 Lock_time: 0.027621 Rows_sent: 120 Rows_examined: 201861
# Time: 160113 19:14:27
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.082317 Lock_time: 0.006028 Rows_sent: 1 Rows_examined: 191779
# Time: 160113 19:14:30
# User@Host: tbk2016[tbk2016] @ localhost []
# Time: 160113 19:14:37
# User@Host: tbk2016[tbk2016] @ localhost []
SELECT * FROM `ftxia_items` WHERE ( `status` = 'underway' ) AND ( `cate_id` IN ('10','11','23') ) AND ( `coupon_start_time` <= 1452683674 ) AND ( `coupon_end_time` >= 1452683674 ) AND ( `coupon_rate` < 10000 ) AND ( `pass` = 1 ) AND ( `isshow` = 1 ) ORDER BY ordid asc, rand() LIMIT 240,120;
# Time: 160113 19:18:26
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.294501 Lock_time: 0.029631 Rows_sent: 120 Rows_examined: 201501
SET timestamp=1452683906;
SELECT * FROM `ftxia_items` WHERE ( `status` = 'underway' ) AND ( `cate_id` IN ('10','11','23') ) AND ( `coupon_start_time` <= 1452683905 ) AND ( `coupon_end_time` >= 1452683905 ) AND ( `coupon_rate` < 10000 ) AND ( `pass` = 1 ) AND ( `isshow` = 1 ) ORDER BY ordid asc, rand() LIMIT 0,120;
# Time: 160113 19:19:43
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 5.222446 Lock_time: 0.019167 Rows_sent: 12 Rows_examined: 1708
SET timestamp=1452683983;
SELECT * FROM `ftxia_brand_items` WHERE ( `cate_id` = 4 ) AND ( `pass` = 1 ) ORDER BY id desc LIMIT 0,12;
# Time: 160113 20:30:01
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.102624 Lock_time: 0.000168 Rows_sent: 120 Rows_examined: 201621
SET timestamp=1452688201;
SELECT * FROM `ftxia_items` WHERE ( `status` = 'underway' ) AND ( `cate_id` IN ('10','11','23') ) AND ( `coupon_start_time` <= 1452688200 ) AND ( `coupon_end_time` >= 1452688200 ) AND ( `coupon_rate` < 10000 ) AND ( `pass` = 1 ) AND ( `isshow` = 1 ) ORDER BY ordid asc, rand() LIMIT 120,120;
# Time: 160114 3:23:10
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.101263 Lock_time: 0.000201 Rows_sent: 1 Rows_examined: 191779
SET timestamp=1452712990;
SELECT COUNT(*) AS tp_count FROM `ftxia_items` WHERE ( `status` = 'underway' ) AND ( (`title` LIKE '%展现自我%') OR (`tags` LIKE '%展现自我%') OR (`num_iid` LIKE '%展现自我
%') ) AND ( `pass` = 1 ) LIMIT 1;
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.176508 Lock_time: 0.000107 Rows_sent: 120 Rows_examined: 191899
SET timestamp=1452712990;
SELECT * FROM `ftxia_items` WHERE ( `status` = 'underway' ) AND ( (`title` LIKE '%度假%') OR (`tags` LIKE '%度假%') OR (`num_iid` LIKE '%度假%') ) AND ( `pass` = 1 ) ORDER BY ordid asc ,id desc, coupon_start_time DESC LIMIT 0,120;
# Time: 160114 4:43:04
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.055258 Lock_time: 0.000100 Rows_sent: 120 Rows_examined: 201501
SET timestamp=1452717784;
SELECT * FROM `ftxia_items` WHERE ( `status` = 'underway' ) AND ( `cate_id` IN ('10','11','23') ) AND ( `coupon_start_time` <= 1452717783 ) AND ( `coupon_end_time` >= 1452717783 ) AND ( `coupon_rate` < 10000 ) AND ( `pass` = 1 ) AND ( `isshow` = 1 ) ORDER BY ordid asc, rand() LIMIT 0,120;
83668 ) AND ( `coupon_rate` < 10000 ) AND ( `pass` = 1 ) AND ( `isshow` = 1 ) ORDER BY ordid asc, rand() LIMIT 120,120;
# Time: 160113 19:14:37
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 2.874080 Lock_time: 0.022908 Rows_sent: 120 Rows_examined: 201741
SET timestamp=1452683677;
SELECT * FROM `ftxia_items` WHERE ( `status` = 'underway' ) AND ( `cate_id` IN ('10','11','23') ) AND ( `coupon_start_time` <= 1452683674 ) AND ( `coupon_end_time` >= 14526
83674 ) AND ( `coupon_rate` < 10000 ) AND ( `pass` = 1 ) AND ( `isshow` = 1 ) ORDER BY ordid asc, rand() LIMIT 240,120;
# Time: 160113 19:18:26
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.294501 Lock_time: 0.029631 Rows_sent: 120 Rows_examined: 201501
SET timestamp=1452683906;
SELECT * FROM `ftxia_items` WHERE ( `status` = 'underway' ) AND ( `cate_id` IN ('10','11','23') ) AND ( `coupon_start_time` <= 1452683905 ) AND ( `coupon_end_time` >= 14526
83905 ) AND ( `coupon_rate` < 10000 ) AND ( `pass` = 1 ) AND ( `isshow` = 1 ) ORDER BY ordid asc, rand() LIMIT 0,120;
# Time: 160113 19:19:43
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 5.222446 Lock_time: 0.019167 Rows_sent: 12 Rows_examined: 1708
SET timestamp=1452683983;
SELECT * FROM `ftxia_brand_items` WHERE ( `cate_id` = 4 ) AND ( `pass` = 1 ) ORDER BY id desc LIMIT 0,12;
# Time: 160113 20:30:01
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.102624 Lock_time: 0.000168 Rows_sent: 120 Rows_examined: 201621
SET timestamp=1452688201;
SELECT * FROM `ftxia_items` WHERE ( `status` = 'underway' ) AND ( `cate_id` IN ('10','11','23') ) AND ( `coupon_start_time` <= 1452688200 ) AND ( `coupon_end_time` >= 14526
88200 ) AND ( `coupon_rate` < 10000 ) AND ( `pass` = 1 ) AND ( `isshow` = 1 ) ORDER BY ordid asc, rand() LIMIT 120,120;
# Time: 160114 3:23:10
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.101263 Lock_time: 0.000201 Rows_sent: 1 Rows_examined: 191779
SET timestamp=1452712990;
SELECT COUNT(*) AS tp_count FROM `ftxia_items` WHERE ( `status` = 'underway' ) AND ( (`title` LIKE '%展现自我%') OR (`tags` LIKE '%展现自我%') OR (`num_iid` LIKE '%展现自我
%') ) AND ( `pass` = 1 ) LIMIT 1;
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.176508 Lock_time: 0.000107 Rows_sent: 120 Rows_examined: 191899
SET timestamp=1452712990;
SELECT * FROM `ftxia_items` WHERE ( `status` = 'underway' ) AND ( (`title` LIKE '%度假%') OR (`tags` LIKE '%度假%') OR (`num_iid` LIKE '%度假%') ) AND ( `pass` = 1 ) ORDER
BY ordid asc ,id desc, coupon_start_time DESC LIMIT 0,120;
# Time: 160114 4:43:04
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.055258 Lock_time: 0.000100 Rows_sent: 120 Rows_examined: 201501
SET timestamp=1452717784;
SELECT * FROM `ftxia_items` WHERE ( `status` = 'underway' ) AND ( `cate_id` IN ('10','11','23') ) AND ( `coupon_start_time` <= 1452717783 ) AND ( `coupon_end_time` >= 14527
17783 ) AND ( `coupon_rate` < 10000 ) AND ( `pass` = 1 ) AND ( `isshow` = 1 ) ORDER BY ordid asc, rand() LIMIT 0,120;
从你的满日志里，提炼出来几个慢语句：# Time: 160113 19:18:26
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.294501 Lock_time: 0.029631 Rows_sent: 120 Rows_examined: 201501
SET timestamp=1452683906;
SELECT * FROM `ftxia_items` WHERE ( `status` = 'underway' ) AND ( `cate_id` IN ('10','11','23') ) AND ( `coupon_start_time` <= 1452683905 ) AND ( `coupon_end_time` >= 1452683905 ) AND ( `coupon_rate` < 10000 ) AND ( `pass` = 1 ) AND ( `isshow` = 1 ) ORDER BY ordid asc, rand() LIMIT 0,120;
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 1.176508 Lock_time: 0.000107 Rows_sent: 120 Rows_examined: 191899
SET timestamp=1452712990;
SELECT * FROM `ftxia_items` WHERE ( `status` = 'underway' ) AND ( (`title` LIKE '%度假%') OR (`tags` LIKE '%度假%') OR (`num_iid` LIKE '%度假%') ) AND ( `pass` = 1 ) ORDER
BY ordid asc ,id desc, coupon_start_time DESC LIMIT 0,120;
# Time: 160113 19:19:43
# User@Host: tbk2016[tbk2016] @ localhost []
# Query_time: 5.222446 Lock_time: 0.019167 Rows_sent: 12 Rows_examined: 1708
SET timestamp=1452683983;
SELECT * FROM `ftxia_brand_items` WHERE ( `cate_id` = 4 ) AND ( `pass` = 1 ) ORDER BY id desc LIMIT 0,12;这些查询最慢的是5秒左右，此外，这些查询都是 limit 求前几行的，但是应该用的都是全表扫描，检查的行20w，但是只返回120条，所以，应该可以通过索引，来加快速度。
explain select ...
show index from ..
以供分析。
都已经存在记录了，为什么还要更新哪，不懂!
都count（*） 了，还加limit 1干嘛
大量的order by 和like，看看是否有必要