主页 / dba / 问题 / 82117
Accepted
automatem
Asked: 2014-11-07 18:25:00 +0800 CST

MySQL优化范围重复查询

  • 772

我需要从时间表中删除重复项。我找到了这个解决方案并根据自己的需要对其进行了调整：

DROP TABLE IF EXISTS `activity`;
CREATE TABLE IF NOT EXISTS `activity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `planned_start` datetime DEFAULT NULL,
  `planned_end` datetime DEFAULT NULL,
  `actual_start` datetime DEFAULT NULL,
  `actual_end` datetime DEFAULT NULL,
  `code_id` int(11) DEFAULT NULL,
  `setting_id` int(11) DEFAULT NULL,
  `notes` text,
  `travel_distance` decimal(8,2) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `submitted` tinyint(1) DEFAULT NULL,
  `approved` datetime DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  `peer_engagement_id` int(11) DEFAULT NULL,
  `person_id` int(11) DEFAULT NULL,
  `travel_notes` varchar(8000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `code_id_idx` (`code_id`),
  KEY `setting_id_idx` (`setting_id`),
  KEY `created_by_idx` (`created_by`),
  KEY `updated_by_idx` (`updated_by`),
  KEY `approved_by_idx` (`approved_by`),
  KEY `activity_peer_engagement_id_fk` (`peer_engagement_id`),
  KEY `activity_person_id_fk` (`person_id`),
  KEY `actual_start` (`actual_start`,`actual_end`),
  KEY `created` (`created`),
  KEY `person_id` (`person_id`,`actual_start`,`actual_end`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=165796 ;


SELECT
  COUNT(*) as occurrence
  , sub.id
  , SEC_TO_TIME(SUM(
      IF(a2start > a1start, a1end - a2start, a2end - a1start))) as duration
FROM
  (  SELECT
       a1.id
      , UNIX_TIMESTAMP(a1.actual_start) as a1start
      , UNIX_TIMESTAMP(a1.actual_end) as a1end
      , UNIX_TIMESTAMP(a2.actual_start) as a2start
      , UNIX_TIMESTAMP(a2.actual_end) as a2end
    FROM activity a1
    INNER JOIN activity a2
      ON (a1.id <> a2.id and a1.person_id=a2.person_id
      AND NOT(a1.actual_start > a2.actual_end OR a1.actual_end < a2.actual_start))
  ) sub

问题是我什至不能对我的查询运行解释，我的 mysql 服务器进入 100% CPU 使用率并且似乎在那里停留了几分钟。

我可以对内部查询运行解释：

explain SELECT
   a1.id
  , UNIX_TIMESTAMP(a1.actual_start) as a1start
  , UNIX_TIMESTAMP(a1.actual_end) as a1end
  , UNIX_TIMESTAMP(a2.actual_start) as a2start
  , UNIX_TIMESTAMP(a2.actual_end) as a2end
FROM activity a1
INNER JOIN activity a2
  ON (a1.id <> a2.id and a1.person_id=a2.person_id
  AND NOT(a1.actual_start > a2.actual_end OR a1.actual_end < a2.actual_start))

+----+-------------+-------+-------+----------------------------------------------+-----------+---------+--------------------------------------+--------+--------------------------+
| id | select_type | table | type  | possible_keys                                | key       | key_len | ref                                  | rows   | Extra                    |
+----+-------------+-------+-------+----------------------------------------------+-----------+---------+--------------------------------------+--------+--------------------------+
|  1 | SIMPLE      | a1    | index | activity_person_id_fk,actual_start,person_id | person_id | 23      | NULL                                 | 176586 | Using index              |
|  1 | SIMPLE      | a2    | ref   | activity_person_id_fk,actual_start,person_id | person_id | 5       | mabel_mindandbody_co_nz.a1.person_id |  19705 | Using where; Using index |
+----+-------------+-------+-------+----------------------------------------------+-----------+---------+--------------------------------------+--------+--------------------------+
2 rows in set (0.00 sec)

我的问题：

  • 为什么不在这里解释工作？
  • 我如何优化此查询以提供可接受的速度结果？

关于优化——除了我已经在我的表中使用的索引之外，我找不到任何其他东西。

我考虑过的另一种选择是添加一个额外的字段，将每天编码成一个数字。我确实知道时间表条目绝不会超过 24 小时，而且我确信不包括跨越午夜的时间表条目是可以接受的。因此，我希望在内部查询的这个附加列上使用较小的索引。

mysql datetime

1 个回答

  1. Best Answer

    假设没有跨越午夜的时间表条目，我添加了一列

    person_date varchar(30) not null

    这是在一夜之间计算的

    update activity set person_date = concat(person_id , '_',date(actual_start) ) where person_date='';

    我还将内部查询简化为：

    SELECT a1.id, 
a1.person_id, 
UNIX_TIMESTAMP( a1.actual_start ) AS a1start,
UNIX_TIMESTAMP( a1.actual_end ) AS a1end, 
UNIX_TIMESTAMP( a2.actual_start ) AS a2start, 
UNIX_TIMESTAMP( a2.actual_end ) AS a2end
FROM activity a1
INNER JOIN activity a2
WHERE (
a1.id < a2.id
AND a1.person_date = a2.person_date
AND a1.actual_start < a2.actual_end
AND a1.actual_end > a2.actual_start

    首先，因为我的用户经常记录他们的时间到每小时的边界，然后我们有 '0:00' 重叠，其次是 a1.id<>a2.id，当我们只需要一个时，我们发现每个条目两次。

    我得出的结论是，没有针对 a1.id < a2.id 进行优化的好方法，因此对于 person_date，我正在针对其他内容进行优化。

    • 0

相关问题