主页 / dba / 问题 / 48063
Accepted
Jacobm001
Asked: 2013-08-14 09:15:42 +0800 CST

附加值的查询优化

  • 772

我有一个表现有点奇怪的查询。在我的数据库中，我有一个名为“记录”的表。它告诉我有关用户在我公司的机器上运行了哪些应用程序的大量信息。我正在尝试汇总一些统计信息，但在查询时遇到了一些奇怪的问题。

此查询运行大约 6.5 分钟（“记录”中约有 3000 万个条目）。如果未指定 divisionName，我预计它会花费更长的时间，但它似乎花费了不合理的时间来完成（一夜之间并且仍在发出声音）。

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';

是否有替代结构来加速查询？我在 locate 中有一个关于 (computerID,divisionName) 的索引，在 Programs 中有一个关于 (programID,programName) 的索引，以及记录中的大量索引。

注意：Programs 包含 4 个字段，locate 包含 2 个。我不认为连接特别大。

编辑：

解释：

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

记录说明：

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

定位说明：

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

程序说明：

+----------------+-------------+------+-----+---------+-------+
| 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 个回答

  1. Best Answer
    • 创建从RECORDS到PROGRAMSans的外键LOCATE（你不提它们是否存在）。
    • 使用LEFT JOIN而不是RIGHT JOIN。毕竟RECORDS是这个查询中的“强”表。
    • 分组依据R.usageProgramID而不是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

    另一种选择是试试这个：

    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

    由于 FK 的缺席可能无济于事。

    • 1

相关问题