Início / dba / Perguntas / 48063
Accepted
Jacobm001
Asked: 2013-08-14 09:15:42 +0800 CST

Otimização de consultas para agregar valor

  • 772

Eu tenho uma consulta que está se comportando de maneira estranha. No meu banco de dados tenho uma tabela chamada "registros". Ele me dá um monte de informações sobre quais aplicativos um usuário executou nas máquinas da minha empresa. Estou tentando agregar algumas estatísticas, mas estou tendo alguns problemas estranhos com uma consulta.

Esta consulta é executada em cerca de 6,5 minutos (~30 milhões de entradas em "registros"). Eu esperaria que demorasse mais quando divisionName não fosse especificado, mas parece estar demorando um tempo irracional para terminar (da noite para o dia e ainda demorando).

select divisionName, programName, count(usageID) 
    from records R 
    right join Programs P 
        on P.programID=R.usageProgramID 
    right join locate L 
        on L.computerID=R.usageComputerID 
    where divisionName="umbrella"
    group by programName
    order by programName asc
    INTO OUTFILE '/tmp/lab_prog_umbrella.csv'
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n';

Existe uma estrutura alternativa para agilizar a consulta? Eu tenho um índice em (computerID,divisionName) em localizar e (programID,programName) em Programas, bem como uma infinidade de índices em registros.

Nota: Programas contém 4 campos e localização contém 2. Não acho que as junções sejam excepcionalmente grandes.

Editar:

Explique:

+----+-------------+-------+------+-----------------+-----------+---------+----------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys   | key       | key_len | ref                  | rows | Extra                                        |
+----+-------------+-------+------+-----------------+-----------+---------+----------------------+------+----------------------------------------------+
|  1 | SIMPLE      | L     | ref  | loc             | loc       | 27      | const                | 1195 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | R     | ref  | uprog,computers | computers | 34      | scf.L.computerID     | 1627 |                                              |
|  1 | SIMPLE      | P     | ref  | pid_name        | pid_name  | 43      | scf.R.usageProgramID |    1 | Using index                                  |
+----+-------------+-------+------+-----------------+-----------+---------+----------------------+------+----------------------------------------------+

Descrição dos registros:

+-----------------+-------------+------+-----+---------------------+-------+
| Field           | Type        | Null | Key | Default             | Extra |
+-----------------+-------------+------+-----+---------------------+-------+
| usageID         | varchar(24) | NO   | PRI | NULL                |       |
| usageWhen       | datetime    | NO   | PRI | 0000-00-00 00:00:00 |       |
| usageEnum       | int(11)     | YES  |     | NULL                |       |
| usageServerID   | int(11)     | YES  |     | NULL                |       |
| usageServerType | int(11)     | YES  |     | NULL                |       |
| usageProgramID  | varchar(40) | NO   | PRI |                     |       |
| usageLicenseID  | varchar(18) | YES  |     | NULL                |       |
| usageComputerID | varchar(31) | YES  | MUL | NULL                |       |
| usageExpansion  | varchar(0)  | YES  |     | NULL                |       |
| usageUser       | varchar(31) | YES  | MUL | NULL                |       |
| usageAddress    | varchar(28) | YES  |     | NULL                |       |
| usageGroup      | varchar(16) | YES  |     | NULL                |       |
| usageEvent      | int(11)     | YES  |     | NULL                |       |
| usageReason     | int(11)     | YES  |     | NULL                |       |
| usageTime       | int(11)     | YES  |     | NULL                |       |
| usageOtherTime  | varchar(25) | YES  |     | NULL                |       |
| usageGMTOffset  | int(11)     | YES  |     | NULL                |       |
| usageCount      | int(11)     | YES  |     | NULL                |       |
+-----------------+-------------+------+-----+---------------------+-------+

Descrição do local:

+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| computerID   | varchar(31) | YES  | MUL | NULL    |       |
| divisionName | varchar(24) | YES  | MUL | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

Descrição dos Programas:

+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| programID      | varchar(40) | YES  | MUL | NULL    |       |
| programName    | varchar(63) | YES  | MUL | NULL    |       |
| programVersion | varchar(31) | YES  |     | NULL    |       |
| category       | varchar(30) | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
mysql performance

1 respostas

  1. Best Answer
    • Crie chaves estrangeiras de RECORDSpara PROGRAMSans LOCATE(você não menciona se elas existem).
    • Use LEFT JOINem vez de RIGHT JOIN. Afinal RECORDSé a tabela "forte" nesta consulta.
    • Agrupar por R.usageProgramIDem vez de by ProgramName.
    select divisionName, programName, count(usageID) 
    from records R 
    left join Programs P 
        on P.programID=R.usageProgramID 
    left join locate L 
        on L.computerID=R.usageComputerID 
    where divisionName="umbrella"
    group by R.usageProgramID 
    order by programName asc

    Outra alternativa é tentar isso:

    select
    t.divisionName, P.programName, count(*) as total
from (
        select L.divisionName, R.usageComputerID
        from records R 
        left join locate L 
        on L.computerID=R.usageComputerID 
        where L.divisionName="umbrella"
      ) t 
    left join Programs P 
        on P.programID=t.usageProgramID 
group by
    group by P.programName
    order by P.programName asc

    Já que a ausência de FK talvez não ajude.

    • 1

relate perguntas