mysql - 两个 MySql 表有正确的索引，但在小表上 JOIN 需要 9 秒

Question

mysql Ver 14.14 Distrib 5.1.73，适用于使用 readline 5.1 的 redhat-linux-gnu (x86_64)

我正在接手一个项目。它已经很老了，原来的程序员早已不在了。没有人知道为什么做出某些决定。

以下查询(在我的 Mac 上)运行时间为 9.5 秒，但如果我删除最后一个 JOIN，则时间会降至 2.5 秒。最后的 JOIN 有什么问题吗？

  select `ttl`.`id` AS `id`,
  `ttl`.`name` AS `name`,
  `ttl`.`updated_at` AS `last_update_on`,
  `ttl`.`user_id` AS `list_creator`,
  `ttl`.`retailer_nomination_list` AS `nomination_list`,
  `ttl`.`created_at` AS `created_on`,
  count(distinct `tlb`.`title_id`) AS `title_count`
  from `haha_title_lists` `ttl` 
    left join `haha_title_list_to_users` `tltu` on((`ttl`.`id` = `tltu`.`title_list_id`))
    left join `users` `u` on((`tltu`.`user_id` = `u`.`id`)) 
    left join `users` `u2` on((`tltu`.`user_id` = `u2`.`id`)) 
    left join `haha_title_list_to_venues` `tlv` on((`ttl`.`id` = `tlv`.`title_list`)) 
    left join `haha_venue_properties` `tvp` on((`tlv`.`venue_id` = `tvp`.`id`)) 
    join `haha_title_list_to_books` `tlb` on((`ttl`.`id` = `tlb`.`title_list_id`))
    join `wawa_title` `ot` on((`tlb`.`title_id` = `ot`.`title_id`))
    group by `ttl`.`id`;

表格:

  CREATE TABLE `haha_title_list_to_books` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `title_id` int(11) NOT NULL,
    `title_list_id` int(11) NOT NULL,
    `sdk` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
    `created_at` datetime NOT NULL,
    `promo_start_date` date DEFAULT NULL,
    `promo_end_date` date DEFAULT NULL,
    `promo_price` float DEFAULT NULL,
    `confirmations` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `nominations` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `title_note` text COLLATE utf8_unicode_ci,
    `executed` int(11) DEFAULT NULL,
    `event_created` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_promo_start_date` (`promo_start_date`),
    KEY `idx_promo_end_date` (`promo_end_date`),
    KEY `idx_title_list_to_books_title_id` (`title_id`),
    KEY `idx_title_list_to_books_title_list_id` (`title_list_id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=21847 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

和:

  CREATE TABLE `wawa_title` (
    `title_id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(200) DEFAULT NULL,
    `title_alpha` varchar(25) NOT NULL,
    `display_title` varchar(200) NOT NULL,
    `subtitle` text NOT NULL,
    `sdk10` varchar(13) DEFAULT '',
    `sdk13` varchar(15) DEFAULT NULL,
    `primary_sdk13` varchar(15) DEFAULT NULL,
    `asin` varchar(10) DEFAULT NULL,
    `pub_season` varchar(15) NOT NULL,
    `pub_year` varchar(15) NOT NULL,
    `bisac1` varchar(15) NOT NULL,
    `bisac2` varchar(15) NOT NULL,
    `bisac3` varchar(15) NOT NULL,
    `barcode` varchar(30) DEFAULT NULL,
    `dewey_decimal` varchar(15) NOT NULL,
    `lib_of_congress` varchar(15) NOT NULL,
    `spanish_language` tinyint(4) NOT NULL,
    `target_audience` tinyint(3) unsigned DEFAULT NULL,
    `language` varchar(20) DEFAULT NULL,
    `edition` varchar(45) DEFAULT NULL,
    `pages` int(11) DEFAULT NULL,
    `number_in_series` int(11) DEFAULT NULL,
    `trimsize` varchar(10) DEFAULT NULL,
    `filesize` varchar(10) DEFAULT NULL,
    `duration_hours` int(11) DEFAULT NULL,
    `duration_minutes` int(11) DEFAULT NULL,
    `discs` int(11) DEFAULT NULL,
    `download` date DEFAULT NULL,
    `size_unit` varchar(15) NOT NULL DEFAULT '',
    `digitization_date` date NOT NULL,
    `us_on_sale_date` date NOT NULL,
    `aus_on_sale_date` date NOT NULL,
    `can_on_sale_date` date NOT NULL,
    `uk_on_sale_date` date NOT NULL,
    `us_list_price` varchar(10) NOT NULL,
    `aus_list_price` varchar(10) NOT NULL,
    `can_list_price` varchar(10) NOT NULL,
    `uk_list_price` varchar(10) NOT NULL,
    `isPrimary` varchar(1) DEFAULT NULL,
    `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `modifier` int(11) NOT NULL,
    `activated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `active` varchar(3) NOT NULL DEFAULT 'N',
    `flagged_string` text,
    `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `assets_id` varchar(20) DEFAULT NULL,
    `book_details` text,
    `book_keynote` text,
    `exclude_goodreads` char(1) NOT NULL DEFAULT 'N',
    `series_description` text,
    `review_quote1` text,
    `territory_id` int(11) DEFAULT '27',
    `featured_newsletter_id` tinyint(3) unsigned DEFAULT '0',
    `retailer_discovery_check` datetime DEFAULT NULL,
    `suppress_retailer_approval` tinyint(1) DEFAULT '0',
    `suppress_retailer_approval_reason` varchar(255) DEFAULT NULL,
    `ebb_description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
    `slug` varchar(150) DEFAULT NULL,
    `legacy_slug` varchar(150) DEFAULT NULL,
    `us_agency_price` varchar(10) DEFAULT NULL,
    `firebrand_title_id` int(11) DEFAULT NULL,
    `ebb_label` varchar(200) DEFAULT NULL,
    `ebb_end_sale_date` date DEFAULT NULL,
    `ebb_downprice` decimal(10,2) DEFAULT NULL,
    `book_club` varchar(1) DEFAULT NULL,
    `best_seller` varchar(1) DEFAULT NULL,
    `award_winner` varchar(1) DEFAULT NULL,
    `discovery` char(1) NOT NULL DEFAULT 'Y',
    `narrator_id` int(11) DEFAULT NULL,
    `suppress_series_data` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`title_id`),
    KEY `active_index` (`active`),
    KEY `fk_title_series_id_idx` (`series_id`),
    KEY `series_id` (`series_id`),
    KEY `idx_title_sdk13` (`sdk13`),
    KEY `idx_title_active_isprimary` (`active`,`isPrimary`),
    KEY `bisac1` (`bisac1`),
    KEY `bisac2` (`bisac2`),
    KEY `bisac3` (`bisac3`),
    KEY `idx_primary_sdk13` (`primary_sdk13`),
    KEY `idx_territory_id` (`territory_id`),
    CONSTRAINT `fk_title_series_id` FOREIGN KEY (`series_id`) REFERENCES `wawa_series` (`series_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
  ) ENGINE=InnoDB AUTO_INCREMENT=19700 DEFAULT CHARSET=utf8 |

如果我删除这一行:

    join `wawa_title` `ot` on((`tlb`.`title_id` = `ot`.`title_id`))

查询速度从9.5秒下降到2.5秒。虽然不是很好，但是是一个巨大的进步。

然而，两个表都在 table_id 上有索引，那么为什么该行会成为问题呢？

我注意到一个表是InnoDB，另一个表是MyISAM。这样会有影响吗？

Answer 1

不要JOIN到您不使用的表。

JOIN 通常会“爆炸”行数，然后像您这样的 GROUP BY 会增加行数。要看到这一点，请将所有 JOIN 保留在那里，但删除 GROUP BY。查看您获得了多少行。

为了避免部分爆炸，请进行更改

count(distinct `tlb`.`title_id`) AS `title_count`

至

( SELECT count(distinct `title_id`)
    FROM `haha_title_list_to_books`
    WHERE `ttl`.`id` = `title_list_id`
) AS `title_count`

并删除当前到 tlb 的 JOIN。

混合 MyISAM 和 InnoDB 不应对此 SELECT 产生任何直接影响。但是，您应该考虑将所有表移至 InnoDB。