主页 / dba / 问题 / 43952
Accepted
Spredzy
Asked: 2013-06-07 02:14:42 +0800 CST

排序结果状态需要很长时间

  • 772

我正在使用 MySQL 5.1.69-log 数据库进行分析。

我在星型模式中设计了我的数据库。所有表都使用 InnoDB。我的innodb_buffer_pool_size=2Gb

这是我正在运行的查询：

select
     `dw_dimension_criteria`.`id` as `c0`,
     count(distinct `dw_fact_program_attendance`.`client_id`) as `m0`
from
     `dw_dimension_criteria` as `dw_dimension_criteria`,
     `dw_fact_program_attendance` as `dw_fact_program_attendance`
where
     `dw_fact_program_attendance`.`criteria_id` = `dw_dimension_criteria`.`id`
group by `dw_dimension_criteria`.`id`;

解释给出以下内容：

+----+-------------+----------------------------+-------+---------------+--------------+---------+-----------------------------------------+--------+-------------+
| id | select_type | table                      | type  | possible_keys | key          | key_len | ref                                     | rows   | Extra       |
+----+-------------+----------------------------+-------+---------------+--------------+---------+-----------------------------------------+--------+-------------+
|  1 | SIMPLE      | dw_dimension_criteria      | index | PRIMARY       | PRIMARY      | 2       | NULL                                    |      1 | Using index | 
|  1 | SIMPLE      | dw_fact_program_attendance | ref   | criteria_id   | criteria _id | 2       | clarity_nevada.dw_dimension_criteria.id | 417669 |             |
+----+-------------+----------------------------+-------+---------------+--------------+---------+-----------------------------------------+--------+-------------+

和SHOW PROFILE命令如下：

+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000117 |
| checking permissions |  0.000035 |
| checking permissions |  0.000009 |
| Opening tables       |  0.000022 |
| System lock          |  0.000004 |
| Table lock           |  0.000011 |
| init                 |  0.000032 |
| optimizing           |  0.000012 |
| statistics           |  0.000045 |
| preparing            |  0.000017 |
| executing            |  0.000043 |
| Sorting result       |  0.000005 |
| Sending data         | 12.847928 |
| end                  |  0.000103 |
| removing tmp table   |  0.000016 |
| end                  |  0.000016 |
| query end            |  0.000005 |
| freeing items        |  0.000114 |
| logging slow query   |  0.000003 |
| logging slow query   |  0.000097 |
| cleaning up          |  0.000012 |
+----------------------+-----------+

我不明白那 12 秒。我不明白它是属于Sorting Result还是Sending Data。我尝试调整sort_buffer_size但没有明显的成功。事实表大约有 700 万行。

我应该在哪里寻找将这 12 秒缩短到更合理的时间？

mysql innodb

1 个回答

  1. Best Answer

    如果其中有一个外键dw_fact_program_attendance(criteria_id)，REFERENCES dw_dimension_criteria(id)则无需连接两个表（但优化器还不够聪明，无法理解。）您只能拥有一个表并且GROUP BY criteria_id：

    SELECT
     a.criteria_id AS c0,
     COUNT(DISTINCT a.client_id) AS m0
FROM
     dw_fact_program_attendance AS a
GROUP BY
     a.criteria_id ;

    上的索引(criteria_id, client_id)将有助于提高效率。

    • 2

相关问题