MySQL alto cpu load lento quries sendo registrado precisa de ajuda para entender

Question

Estou tentando solucionar um problema de alta carga em um servidor. Hoje, por alguma razão, o MySQL está ocupando um tempo excessivo de CPU. Eu ativei consultas lentas e encontrei essa consulta e outras semelhantes.

As tabelas têm cerca de 700 mil linhas.

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)

Eu executei essa consulta algumas vezes e fiquei em qualquer lugar entre 0 e 30 segundos.

Existe algo fundamentalmente errado com esta consulta? ou esta consulta demora muito devido a outros problemas com o mysql?

EDITAR

 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 Índices adicionados

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

    

Answer 1

Não há nada intrinsecamente errado com a consulta, mas você esqueceu de nos informar como os índices estão configurados.

Idealmente, essa consulta usaria um índice BTREE em post_files_likes.date, mas há casos em que isso não será usado pelo DBMS / não melhorará o desempenho (por exemplo, se a cardinalidade da coluna de data for baixa, o DBMS não vai usá-lo, um índice baseado em hash é muito ineficiente para procurar intervalos de dados).

Adicionar image_id e depois LIKES ao índice (AFTER date) significa que o índice está cobrindo e a consulta pode ser satisfeita sem referência aos dados da tabela. Mas uma postagem pode ser apreciada mais de uma vez exatamente ao mesmo tempo?

Se você estiver executando esse wquery com freqüência, desnormalizando e / ou armazenando em cache os resultados ajudará, pois (novamente inferindo do contexto) os dados não parecem ser necessários em tempo real.

    

Answer 2

Esta tabela está indexada?

SHOW INDEX FROM post_files_likes;

Quantas linhas foram retornadas por esta consulta (sem SUM)?

O seu my.cnf é otimizado para isso?

Tente ajustar os parâmetros para uma configuração ideal, pelo menos com isto: link

    

Answer 3

Do seu plano de consulta:

Using where; Using temporary; Using filesort

Isso vai ser uma droga. Vai usar uma tabela temporária em disco.

A causa provável disso é que o mysql está usando a chave image_id_2 , que não inclui a data, porque você usa DATE_SUB(...) ao invés de uma simples comparação. Tente precalcular a data de corte em seu código e use date >= that-date-here