大网数据广告活动
服务器运维问答

MySQL high cpu load slow quries being logged need help to understand

· 52CE 我爱测

I'm trying to troubleshoot a high load problem on a server, Today for some reason MySQL is taking up excessive CPU time. I have enabled slow-queries and found this query and others similar. The tables has around 700k rows. SELECT…

问题原文

原帖链接 · squareborg · CC BY-SA 3.0

I'm trying to troubleshoot a high load problem on a server, Today for some reason MySQL is taking up excessive CPU time. I have enabled slow-queries and found this query and others similar.

The tables has around 700k rows.

SELECT SUM( likes ) AS likes, image_id FROM post_files_likes WHERE image_id NOT IN(563593,591800,578109,581180,515832,646310,670601,626185,689090,80019,399472,468198) AND date > DATE_SUB( '2013-08-19' , INTERVAL 1 MONTH ) GROUP BY image_id ORDER BY likes DESC LIMIT 12;

`

mysql> describe post_files_likes
    -> ;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(10) | NO   | PRI | NULL    | auto_increment |
| image_id | int(10) | NO   | MUL | NULL    |                |
| likes    | int(11) | NO   |     | NULL    |                |
| date     | date    | NO   |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> EXPLAIN SELECT SUM( likes ) AS likes, image_id FROM post_files_likes WHERE image_id NOT IN(563593,591800,578109,581180,515832,646310,670601,626185,689090,80019,399472,468198) AND date > DATE_SUB( '2013-08-19' , INTERVAL 1 MONTH ) GROUP BY image_id ORDER BY likes DESC LIMIT 12;
+----+-------------+------------------+-------+---------------------+------------+---------+------+--------+----------------------------------------------+
| id | select_type | table            | type  | possible_keys       | key        | key_len | ref  | rows   | Extra                                        |
+----+-------------+------------------+-------+---------------------+------------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | post_files_likes | range | image_id,image_id_2 | image_id_2 | 4       | NULL | 709885 | Using where; Using temporary; Using filesort |
+----+-------------+------------------+-------+---------------------+------------+---------+------+--------+----------------------------------------------+
1 row in set (2.92 sec)

I have run this query a few times and get anywhere between 0 to 30 seconds.

Is there something fundamentally wrong with this query? or is this query taking a long time due to other issues with mysql?

EDIT

 SHOW INDEX FROM post_files_likes;
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| post_files_likes |          0 | PRIMARY    |            1 | id          | A         |      710969 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          0 | image_id   |            1 | image_id    | A         |      355484 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          0 | image_id   |            2 | date        | A         |      710969 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          1 | image_id_2 |            1 | image_id    | A         |      355484 |     NULL | NULL   |      | BTREE      |         |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.05 sec)

EDIT Added indexes

mysql> SHOW INDEX FROM post_files_likes;
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| post_files_likes |          0 | PRIMARY    |            1 | id          | A         |      711181 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          0 | image_id   |            1 | image_id    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          0 | image_id   |            2 | date        | A         |      711181 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          1 | image_id_2 |            1 | image_id    | A         |      237060 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          1 | likes      |            1 | likes       | A         |         445 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          1 | likes      |            2 | date        | A         |        4709 |     NULL | NULL   |      | BTREE      |         |
| post_files_likes |          1 | likes      |            3 | image_id    | A         |      711181 |     NULL | NULL   |      | BTREE      |         |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

已采纳解答

原帖链接 · symcbean · CC BY-SA 3.0

There's nothing instrinsically wrong with the query - but you forgot to tell us how the indexes are configured.

Optimally this query would use a BTREE index on post_files_likes.date, but there are cases where this won't be used by the DBMS / won't improve the performance (e.g. if the cardinality of the date column is low the DBMS won't use it, a hash based index is very innefficient for looking up ranges of data).

Adding image_id then LIKES to the index (AFTER date) means that the index is covering and the query can be satisfied without reference to the table data. But can a post be liked more than once at exactly the same time?

If you're running this wquery frequently then denormalizing and / or caching the results will help, as (again inferring from context) the data does not appear to be needed in real-time.

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2013-08-19)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。