mysql - 如何提高跨三个连接表的 DISTINCT 选择的性能？

Question

我有以下表格有问题:

• 角色
• ImpressionsPersonas [连接表 - Personas ManyToMany Impressions]
• 印象

我的查询看起来像这样，EXPLAIN 结果附在下面:

   SELECT

   DISTINCT (Personas.id),
    Personas.parent_id, 
   Personas.persona, 
   Personas.subpersonas_count, 
   Personas.is_subpersona, 
   Personas.impressions_count, 
   Personas.created, 
   Personas.modified


   FROM personas as Personas 

   INNER JOIN   
        impressions_personas ImpressionsPersonas ON (
        Personas.id = ImpressionsPersonas.persona_id
    )
   inner JOIN impressions Impressions ON (Impressions.id = ImpressionsPersonas.impression_id AND Impressions.timestamp >= "2016-06-01 00:00:00" AND Impressions.timestamp <= "2016-07-31 00:00:00") 

解释

    +----+-------------+---------------------+--------+-----------------------------------------------------------------------+-------------+---------+---------------------------------------------+------+----------+-----------------------+
    | id | select_type | table               | type   | possible_keys                                                         | key         | key_len | ref                                         | rows | filtered | Extra                 |
    +----+-------------+---------------------+--------+-----------------------------------------------------------------------+-------------+---------+---------------------------------------------+------+----------+-----------------------+
    |  1 | SIMPLE      | Personas            | ALL    | PRIMARY                                                               | NULL        | NULL    | NULL                                        |  159 |   100.00 | Using temporary       |
    |  1 | SIMPLE      | ImpressionsPersonas | ref    | impression_idx,persona_idx,comp_imp_persona,comp_imp_pri,comp_per_pri | persona_idx | 8       | gen1_d2go.Personas.id                       |  396 |   100.00 | Distinct              |
    |  1 | SIMPLE      | Impressions         | eq_ref | PRIMARY,timestamp,timestamp_id                                        | PRIMARY     | 8       | gen1_d2go.ImpressionsPersonas.impression_id |    1 |   100.00 | Using where; Distinct |
    +----+-------------+---------------------+--------+-----------------------------------------------------------------------+-------------+---------+---------------------------------------------+------+----------+-----------------------+
    3 rows in set, 1 warning (0.00 sec)

为角色创建声明

CREATE TABLE `personas` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(20) unsigned DEFAULT NULL,
  `persona` varchar(150) NOT NULL,
  `subpersonas_count` int(10) unsigned DEFAULT '0',
  `is_subpersona` tinyint(1) unsigned DEFAULT '0',
  `impressions_count` bigint(20) unsigned DEFAULT '0',
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `lookup` (`parent_id`,`persona`),
  KEY `parent_index` (`parent_id`),
  KEY `persona` (`persona`),
  KEY `persona_a_id` (`id`,`persona`),
  CONSTRAINT `self_referential_join_to_self` FOREIGN KEY (`parent_id`) REFERENCES `personas` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1049 DEFAULT CHARSET=utf8;

为 IMPRESSIONS_PERSONAS 创建声明

CREATE TABLE `impressions_personas` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `impression_id` bigint(20) unsigned NOT NULL,
  `persona_id` bigint(20) unsigned NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `impression_idx` (`impression_id`),
  KEY `persona_idx` (`persona_id`),
  KEY `comp_imp_persona` (`impression_id`,`persona_id`),
  KEY `comp_imp_pri` (`impression_id`,`id`),
  KEY `comp_per_pri` (`persona_id`,`id`),
  CONSTRAINT `impression` FOREIGN KEY (`impression_id`) REFERENCES `impressions` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `persona` FOREIGN KEY (`persona_id`) REFERENCES `personas` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=19387839 DEFAULT CHARSET=utf8;

为印象创建声明

CREATE TABLE `impressions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `device_id` bigint(20) unsigned NOT NULL,
  `beacon_id` bigint(20) unsigned NOT NULL,
  `zone_id` bigint(20) unsigned NOT NULL,
  `application_id` bigint(20) unsigned DEFAULT NULL,
  `timestamp` datetime NOT NULL,
  `google_place_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(60) DEFAULT NULL,
  `lat` decimal(15,10) DEFAULT NULL,
  `lng` decimal(15,10) DEFAULT NULL,
  `personas_count` int(10) unsigned DEFAULT '0',
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `device_idx` (`device_id`),
  KEY `zone_idx` (`zone_id`),
  KEY `beacon_id_idx2` (`beacon_id`),
  KEY `timestamp` (`timestamp`),
  KEY `appid_fk_idx_idx` (`application_id`),
  KEY `comp_lookup` (`device_id`,`beacon_id`,`timestamp`),
  KEY `timestamp_id` (`timestamp`,`id`),
  CONSTRAINT `appid_fk_idx` FOREIGN KEY (`application_id`) REFERENCES `applications` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `beacon_id` FOREIGN KEY (`beacon_id`) REFERENCES `beacons` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `device2` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `zone_FK` FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1582724 DEFAULT CHARSET=utf8;

---

现在 - 当我在没有 DISTINCT 的情况下使用 COUNT(*) 运行查询时，它提取了大约 17,000,000 条记录。使用 DISTINCT 运行它会产生 112 条记录。我不确定为什么解释只显示 159 和 396 时会显示这么多记录。

关于表格的一些信息:

Personas 表包含 159 条记录。 ImpressionsPersonas 表包含大约 1260 万条记录，Impressions 表包含大约 920,000 条记录。

我们正在做的是选择 Personas 表并通过连接表 ImpressionsPersonas 连接到 Impressions。 Impressions 表(在本例中为日期)应用了过滤器。

注意:删除日期过滤器对执行时间的影响可以忽略不计 - 徘徊在 120 秒左右。有没有办法过滤这些记录以减少此查询的执行时间？

Answer 1

我假设您想获得在指定时间段内至少有 1 次印象的人员列表。为此，您可以使用这样的相关子查询:

SELECT
   Personas.id,
   Personas.parent_id, 
   Personas.persona, 
   Personas.subpersonas_count, 
   Personas.is_subpersona, 
   Personas.impressions_count, 
   Personas.created, 
   Personas.modified

   FROM personas as Personas 
   WHERE EXISTS(SELECT 1 FROM impressions_personas 
       LEFT JOIN impressions Impressions ON 
           Impressions.id = ImpressionsPersonas.impression_id 
       WHERE Personas.id = ImpressionsPersonas.persona_id
           AND Impressions.timestamp >= "2016-06-01 00:00:00" 
           AND Impressions.timestamp <= "2016-07-31 00:00:00"
   )