主页 / server / 问题 / 531975
Accepted
squareborg
Asked: 2013-08-20 03:26:48 +0800 CST

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

  • 772

我正在尝试解决服务器上的高负载问题，今天由于某种原因 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      |         |
+------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
mysql

3 个回答

  1. Best Answer

    查询本质上没有任何问题 - 但您忘记告诉我们如何配置索引。

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

    添加 image_id 然后 LIKES 到索引（AFTER date）意味着索引覆盖并且可以在不参考表数据的情况下满足查询。但是一个帖子可以在同一时间被多次点赞吗？

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

    • 2

  2. 该表是否已编入索引？

    SHOW INDEX FROM post_files_likes;

    此查询返回了多少行（没有 SUM）？

    您的 my.cnf 是否为此进行了优化？

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

    • 0

  3. 从您的查询计划中：

    Using where; Using temporary; Using filesort

    这会很糟糕。它将使用一个临时的磁盘表。

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

    • 0

相关问题