Início / dba / Perguntas / 224822
Accepted
Chloe
Asked: 2018-12-13 11:40:19 +0800 CST

Como posso acelerar essa consulta de 2m5s que possui índices?

  • 772

Como posso acelerar essa consulta de 2m5s que possui índices?

select urls.id as urlId, 
    count(case when s1.hit_type = 0 then 1 end) as aCount, 
    count(case when s1.hit_type = 1 then 1 end) as bCount, 
    count(case when s1.hit_type = 2 then 1 end) as cCount, 
    count(distinct s1.source_id) as sourcesCount 
from urls join stats s1 on urls.id = s1.url_id 
where s1.hit_date >= '2017-12-12' 
group by urls.id 
order by aCount desc 
limit 0,100;

mysql> show create table stats;

| stats | CREATE TABLE `stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `url_id` varchar(100) DEFAULT NULL,
  `hit_date` datetime DEFAULT NULL,
  `hit_type` tinyint(4) DEFAULT NULL,
  `source_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `url_id_idx` (`url_id`),
  KEY `source_id` (`source_id`),
  KEY `stats_hit_date_idx` (`hit_date`),
  CONSTRAINT `stats_ibfk_1` FOREIGN KEY (`url_id`) REFERENCES `urls` (`ID`),
  CONSTRAINT `stats_ibfk_2` FOREIGN KEY (`source_id`) REFERENCES `sources` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6027557 DEFAULT CHARSET=latin1 |

mysql> describe select...
| id | select_type | table   | type   | possible_keys                                                                                   | key     | key_len | ref                      | rows    | Extra                                        |
+----+-------------+---------+--------+-------------------------------------------------------------------------------------------------+---------+---------+--------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | s1      | ALL    | url_id_idx,stats_hit_date_idx                                                                   | NULL    | NULL    | NULL                     | 5869695 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | urls    | eq_ref | PRIMARY,urls_email_idx,urls_status_idx,deptId_idx,deptId_status_email_idx                       | PRIMARY | 102     | db.s1.url_id             |     1   | Using index                                  |

Não parece estar usando o índice hit_date ou o índice url_id.

Eu tentei usar uma sub-seleção (select count(*) from stats where url_id = ... and hit_date >= ... and hit_type = 0) as aCounte foi mais rápido e levou 24s. Existe uma maneira de torná-lo menos de 5s? O limite para toda a solicitação é de 30 segundos.

Versão do MySQL Server: 5.6.35-log MySQL Community Server (GPL)

mysql mysql-5.6

2 respostas

  1. Best Answer

    Sua consulta é igual a

    select /* urls.id */ s1.url_id as urlId, 
    count(case when s1.hit_type = 0 then 1 end) as aCount, 
    count(case when s1.hit_type = 1 then 1 end) as bCount, 
    count(case when s1.hit_type = 2 then 1 end) as cCount, 
    count(distinct s1.source_id) as sourcesCount 
from /* urls join */ stats s1 /* on urls.id = s1.url_id */
where s1.hit_date >= '2017-12-12' 
group by /* urls.id */ s1.url_id
order by aCount desc 
limit 0,100;

    exceto na saída da sua consulta, existem apenas registros cujos "pares" existem na urlstabela.

    Mas a restrição

    CONSTRAINT `stats_ibfk_1` FOREIGN KEY (`url_id`) REFERENCES `urls` (`ID`)

    não permita esses registros.

    Portanto, minha consulta é absolutamente igual à sua, e você pode usá-la.

    Para aumentar essa velocidade de consulta, você pode criar um índice de cobertura

    ALTER TABLE stats ADD INDEX idx (url_id, hit_date, hit_type, source_id)

    E a melhor maneira é passar url_idpara uma tabela separada e substituí-la por uma referência do tipo numérico (agrupar por campo VARCHAR é caro).

    Além disso - count(case when s1.hit_type = N then 1 end)pode ser substituído por curto SUM(s1.hit_type = N).

    Para acelerar toda a consulta, recomendo tentar dividi-la em 4 consultas separadas:

    SELECT urlId, 
       MAX(aCount) aCount, 
       MAX(bCount) bCount, 
       MAX(cCount) cCount, 
       MAX(sourcesCount) sourcesCount 
FROM (  select  s1.url_id as urlId, 
                COUNT(*) as aCount, 
                0 as bCount, 
                0 as cCount, 
                0 as sourcesCount 
        from stats s1 
        where s1.hit_date >= '2017-12-12' AND s1.hit_type = 0
        group by s1.url_id
      UNION ALL
        select  s1.url_id, 0, COUNT(*), 0, 0
        from stats s1 
        where s1.hit_date >= '2017-12-12'  AND s1.hit_type = 1
        group by s1.url_id
      UNION ALL
        select  s1.url_id as urlId, 0, 0, COUNT(*), 0
        from stats s1 
        where s1.hit_date >= '2017-12-12'  AND s1.hit_type = 2
        group by s1.url_id
      UNION ALL
        select  s1.url_id as urlId, 0, 0, 0, count(distinct s1.source_id)
        from stats s1 
        where s1.hit_date >= '2017-12-12' 
        group by s1.url_id
    ) x
GROUP BY urlId
order by aCount desc 
limit 0,100;

    O índice by (url_id, hit_type, hit_date)acelerará as primeiras 3 subconsultas e by (url_id, hit_date, source_id)acelerará a última subconsulta.

    • 3

  2. Sua consulta depende de querer obter um resumo depois de ler mais de 5869695 resultados e combiná-los em outra tabela.

    Conseguir isso em < 5 segundos é uma grande pergunta.

    Como parece que seus dados são bastante constantes após a entrada, sugiro criar tabelas de resumo com base na data e ter um {a,b,c}Count.

    • 1

relate perguntas