我试图解决服务器上的高负载问题，今天出于某种原因，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 | | +------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
查询没有什么内在的错误 – 但是你忘了告诉我们索引是如何configuration的。
这个查询最好在post_files_likes.date上使用BTREE索引，但是在某些情况下，DBMS不会使用它/不会提高性能（例如，如果date列的基数很低，DBMS将不会使用它，基于散列的索引对于查找数据范围是非常不利的）。
将image_id然后LIKES添加到索引（AFTERdate）意味着索引覆盖并且可以在不参考表数据的情况下满足查询。 但是可以在同一时间不止一次地发帖吗？
如果您经常运行这个wquery，那么反规范化和/或caching结果将会有所帮助，因为（从上下文中再次推断）数据似乎并不是实时需要的。
这个表是索引？
SHOW INDEX FROM post_files_likes;
这个查询返回多less行（没有SUM）？
你的my.cnf是为此优化的吗？
尝试调整参数以获得最佳configuration，至less可以这样做： https ： //raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
从您的查询计划：
Using where; Using temporary; Using filesort
这将是吸吮。 它将使用临时的磁盘表。
这可能的原因是，mysql正在使用关键字image_id_2 ，它不包含date，这是因为您使用DATE_SUB(...)而不是简单的比较。 尝试预先计算代码中的截止date，并使用date >= that-date-here