☰

MySQL 高 CPU 负载缓慢查询被记录需要帮助来理解

tag-icon • tag-icon mysql sql
MySQL 高 CPU 负载缓慢查询被记录需要帮助来理解

我正在尝试解决服务器上的高负载问题，今天由于某种原因，MySQL 占用了过多的 CPU 时间。我启用了慢查询，发现此查询和其他查询类似。

该表大约有 700k 行。

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)

我已运行此查询几次，结果介于 0 到 30 秒之间。

这个查询是否存在根本性错误？或者由于 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         |      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)

编辑添加索引

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      |         |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

答案1

查询本质上没有任何错误 - 但您忘了告诉我们索引是如何配置的。

最佳情况下，此查询将在 post_files_likes.date 上使用 BTREE 索引，但有些情况下 DBMS 不会使用该索引/不会提高性能（例如，如果日期列的基数较低，则 DBMS 不会使用它，基于哈希的索引对于查找数据范围来说效率非常低）。

将 image_id 和 LIKES 添加到索引（日期之后）意味着索引已覆盖，无需参考表数据即可满足查询。但帖子可以在同一时间被点赞多次吗？

如果您频繁运行此 wquery，则非规范化和/或缓存结果将会有所帮助，因为（再次从上下文推断）数据似乎不需要实时。

答案2

该表有索引吗？

SHOW INDEX FROM post_files_likes;

此查询返回了多少行（不含 SUM）？

你的 my.cnf 是否针对此进行了优化？

尝试调整参数以获得最佳配置，至少使用这个：https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

答案3

从您的查询计划中：

Using where; Using temporary; Using filesort

这太糟糕了。它将使用临时的磁盘表。

造成这种情况的可能原因是 mysql 正在使用 key image_id_2，它确实不是包括日期，这是因为你使用DATE_SUB(...)而不是简单的比较。尝试在代码中预先计算截止日期并使用date >= that-date-here

相关内容