时间戳

首页 > 解决方案 > 从两个大表的连接中选择不同的值

mysql - 从两个大表的连接中选择不同的值

问题描述

我有animals一张包含大约 300 万条记录的表。除了其他几列之外，该表还有id、name和owner_id列。我有animal_breeds一张包含大约 250 万条记录的表。该表只有一个animal_id和breed列。

我试图找到breed与特定关联的不同值owner_id，但查询需要 20 秒左右。这是查询：

SELECT DISTINCT `breed`
FROM `animal_breeds` 
INNER JOIN `animals` ON `animals`.`id` = `animal_breeds`.`animal_id` 
WHERE `animals`.`owner_id` = ? ;

这些表具有所有适当的索引。我无法通过向表中添加一breed列来对animals表进行非规范化，因为可以为动物分配多个品种。我对其他一些具有一对多关系的大表也有这个问题。

有没有更高效的方法来实现我正在寻找的东西？这似乎是一个非常简单的问题，但除了预先计算和缓存结果之外，我似乎无法找出实现这一目标的最佳方法。

这是我的查询的解释输出。注意Using temporary

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   "SIMPLE"    "a" NULL    "ref"   "PRIMARY,animals_animal_id_index"   "animals_animal_id_index"   "153"   "const" 1126303 100.00  "Using index; Using temporary"
1   "SIMPLE"    "ab"    NULL    "ref"   "animal_breeds_animal_id_breed_unique,animal_breeds_animal_id_index,animal_breeds_breed_index"  "animal_breeds_animal_id_breed_unique"  "5" "pedigreeonline.a.id"   1   100.00  "Using index"

根据要求，这里是创建表语句（我从表中省略了一些不相关的列和索引animals）。我相信由于表上的唯一键，表上的animal_breeds_animal_id_index索引animal_breeds是多余的，但我们现在可以忽略它，只要它不会导致问题:)

CREATE TABLE `animals` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `owner_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `animals_animal_id_index` (`owner_id`,`id`),
  KEY `animals_name_index` (`name`),
) ENGINE=InnoDB AUTO_INCREMENT=2470843 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


CREATE TABLE `animal_breeds` (
  `animal_id` int(10) unsigned DEFAULT NULL,
  `breed` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  UNIQUE KEY `animal_breeds_animal_id_breed_unique` (`animal_id`,`breed`),
  KEY `animal_breeds_animal_id_index` (`animal_id`),
  KEY `animal_breeds_breed_index` (`breed`),
  CONSTRAINT `animal_breeds_animal_id_foreign` FOREIGN KEY (`animal_id`) REFERENCES `animals` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

任何帮助，将不胜感激。谢谢！

标签： mysqlsqlperformancejoindistinct

解决方案

对于此查询：

SELECT DISTINCT ab.`breed`
FROM `animal_breeds` ab INNER JOIN
     `animals` a
      ON a.`id` = ab.`animal_id` 
WHERE a.`owner_id` = ? ;

你想要索引animals(owner_id, id)和animal_breeds(animal_id, breed)。复合索引中列的顺序很重要。

有了正确的索引，我想这会非常快。

编辑：

根据解释，您使用的值有 1,126,303 个匹配项。时间是由于删除重复。考虑到表格的大小，令人惊讶的是会有这么多匹配一个值。

推荐阅读