转自 http://snshuangxin.sinaapp.com/blog/selectview/?viewtype=aindex&objid=486

解决mysql CPU 占用持续 100%+的实践 

问题描述:游戏日志记录表，当数据量超过500W+时，查询数据库时间过长，CPU占用一直100%+，这个太恐怖了，必须优化

表结构：

mysql> desc gamelog;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| player     | int(11)     | NO   | MUL | NULL    |                |
| role       | int(11)     | NO   |     | NULL    |                |
| platform   | smallint(6) | NO   |     | NULL    |                |
| channel_id | int(11)     | NO   | MUL | NULL    |                |
| logtype_id | int(11)     | NO   | MUL | NULL    |                |
| level      | smallint(6) | NO   |     | NULL    |                |
| logtime    | datetime    | NO   |     | NULL    |                |
| gft        | varchar(60) | NO   |     | NULL    |                |
| gt         | varchar(60) | NO   |     | NULL    |                |
| params     | longtext    | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

慢查询日志定位原因：

# Time: 141231 16:08:50
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 29.559243  Lock_time: 0.000101 Rows_sent: 100  Rows_examined: 1903475
use gamelog;
SET timestamp=1420013330;
SELECT `gamelog`.`id`, `gamelog`.`player`, `gamelog`.`role`, `gamelog`.`platform`, `gamelog`.`channel_id`, `gamelog`.`logtype_id`, `gamelog`.`level`, `gamelog`.`logtime`, `gamelog`.`gft`, `gamelog`.`gt`, `gamelog`.`params`, `channel`.`id`, `channel`.`mark`, `channel`.`name`, `channel`.`content`, `logtype`.`id`, `logtype`.`mark`, `logtype`.`name`, `logtype`.`content` FROM `gamelog` INNER JOIN `channel` ON ( `gamelog`.`channel_id` = `channel`.`id` ) INNER JOIN `logtype` ON ( `gamelog`.`logtype_id` = `logtype`.`id` ) ORDER BY `gamelog`.`id` DESC LIMIT 100;

查询为多表关联查询（3张表），通过mysql的慢查询日志可以定位到问题所在（查询字段 player channel_id logtype_id没有建立索引, 需要建立）临时表大小 tmp_table_size 太小导致ORDER GROUP BY 需要操作磁盘导致数据库查询时间过长CPU占用居高不下此外还可以增大查询缓存大小query_cache_limit=10M query_cache_size=200M

开启mysql慢查询：

long_query_time = 5

log-slow-queries = /var/log/mysql/slow_query_log.log

添加db_index字段：

ALTER TABLE `gamelog` ADD INDEX ( `player` );

ALTER TABLE `gamelog` ADD INDEX ( `channel_id` ); 

ALTER TABLE `gamelog` ADD INDEX ( `logtype_id` );

效果十分明显阿

2014-12-31 15:30:56

2014-12-31 15:40:54

2014-12-31 15:50:57

2014-12-31 16:00:56

2014-12-31 16:10:57

2014-12-31 16:20:55

2014-12-31 16:30:54

2014-12-31 16:40:54

2014-12-31 16:50:57 

2014-12-31 17:00:54 

2014-12-31 17:10:55

2014-12-31 17:20:53

2014-12-31 17:30:52 

2014-12-31 17:40:56

2014-12-31 17:50:52

2014-12-31 18:00:04

2014-12-31 18:10:04 

2014-12-31 18:20:03

每隔10分钟准时执行一次的脚本（需要查询gamelog表的渠道）， 秒钟为脚本执行时间， 啊，庆祝一下！！！！元旦快乐