- 论坛徽章:
- 0
|
我先把问题描述一下,mysql不是很熟,请教各位高手了:)
mysql 里有一表:(目前60个字段,有需要会动态增加,主键 reporttime,hostip )
mysql> desc QGG_Info;
+---------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| hostip | varchar(15) | | PRI | | |
| reporttime | varchar(12) | | PRI | | |
| data_40020100 | int(11) unsigned | YES | | NULL | |
| data_40020200 | int(11) unsigned | YES | | NULL | |
| data_40020300 | int(11) unsigned | YES | | NULL | |
+---------------+------------------+------+-----+---------+-------+
60 rows in set (0.02 sec)
该表大概有200w条数据
当我在控制台执行该条sql语句的时候 发现cpu使用率几乎100%
mysql> select sum(data_40023500),reporttime from QGG_Info where data_40023500 is not null group by reporttime order by reporttime desc limit 3,1;
+--------------------+--------------+
| sum(data_40023500) | reporttime |
+--------------------+--------------+
| 87537 | 200603151057 |
+--------------------+--------------+
1 row in set (10.81 sec)
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8685 root 14 0 455m 262m 247m R 99.9 13.0 39:47.52 mysqld
12528 root 16 0 1008 1008 772 R 9.1 0.0 0:02.69 top
25819 root 9 0 1948 1712 1096 S 0.6 0.1 0:39.71 manager
25824 root 9 0 1948 1712 1096 S 0.6 0.1 0:38.93 manager
25822 root 9 0 1948 1712 1096 S 0.3 0.1 0:39.55 manager
///////////////////////////////////////////////////////////////////////////////////////////////////////////////
show processlist;
+-------+------+--------------------+-------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+--------------------+-------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 22218 | root | IedOssBak1:54815 | stat | Sleep | 1 | | NULL |
| 22219 | root | IedOssBak1:54816 | stat | Sleep | 7 | | NULL |
| 22220 | root | IedOssBak1:54817 | stat | Sleep | 1 | | NULL |
| 22221 | root | IedOssBak1:54818 | stat | Sleep | 41 | | NULL |
| 22222 | root | IedOssBak1:54819 | stat | Sleep | 31 | | NULL |
| 22223 | root | IedOssBak1:54820 | stat | Sleep | 21 | | NULL |
| 37132 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 37444 | root | localhost| qqpet | Query | 7 | Copying to tmp table | select sum(data_40023500),reporttime from QGG_Info where data_40023500 is not null group by reportt |
+-------+------+--------------------+-------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
可以确认就是这条sql 语句引起的,请问高手们是什么原因呢?怎样解决??多谢了 |
|