mysql - 如何使用两个内部联接提高查询性能？

Question

我有 3 个表:

CREATE TABLE `t_event` (
  `id` int(10) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL,
  `kind` int(10) NOT NULL,
  `type` int(10) NOT NULL,
  `short_desc` varchar(500) default NULL,
  `long_desc` varchar(1500) default NULL,
  `location` int(10) NOT NULL,
  `price` decimal(11,0) NOT NULL,
  `currency` int(11) NOT NULL default '1',
  `remark_price` varchar(250) default NULL,
  `remark_prerequisite` varchar(250) default NULL,
  `date_start` date NOT NULL,
  `date_end` date default NULL,
  `date_remark` varchar(300) default NULL,
  `time_start` time default NULL,
  `time_end` time default NULL,
  `remark_time` varchar(50) default NULL,
  `leader` int(50) NOT NULL,
  `leader2` int(100) NOT NULL,
  `eve_contact_name` varchar(50) default NULL,
  `eve_contact_phone` varchar(50) default NULL,
  `eve_contact_email` varchar(50) default NULL,
  `eve_contact_url` varchar(150) default NULL,
  `eve_image_path` varchar(250) default NULL,
  `provider` int(10) default NULL,
  `timestamp` datetime NOT NULL,
  `last_change` datetime NOT NULL default '0000-00-00 00:00:00',
  `quality` int(10) default NULL,
  `min_number` int(10) NOT NULL,
  `max_number` int(10) NOT NULL,
  `active_for_reservation` tinyint(1) NOT NULL,
  `cancellation_day1` int(10) NOT NULL,
  `cancellation_day2` int(10) NOT NULL,
  `cancellation_fee1` varchar(255) NOT NULL,
  `cancellation_fee2` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_t_event_t_event_kind` (`kind`),
  KEY `FK_t_event_t_event_type` (`type`),
  KEY `FK_t_event_t_location` (`location`),
  KEY `FK_t_event_t_currency` (`currency`),
  KEY `FK_t_event_t_leader` (`leader`),
  KEY `FK_t_event_t_provider` (`provider`),
  KEY `FK_t_event_t_quality` (`quality`),
  CONSTRAINT `FK_t_event_t_currency` FOREIGN KEY (`currency`) REFERENCES `t_currency` (`id`),
  CONSTRAINT `FK_t_event_t_event_kind` FOREIGN KEY (`kind`) REFERENCES `t_event_kind` (`id`),
  CONSTRAINT `FK_t_event_t_event_type` FOREIGN KEY (`type`) REFERENCES `t_event_type` (`id`),
  CONSTRAINT `FK_t_event_t_leader` FOREIGN KEY (`leader`) REFERENCES `t_leader` (`id`),
  CONSTRAINT `FK_t_event_t_location` FOREIGN KEY (`location`) REFERENCES `t_location` (`id`),
  CONSTRAINT `FK_t_event_t_provider` FOREIGN KEY (`provider`) REFERENCES `t_provider` (`id`),
  CONSTRAINT `FK_t_event_t_quality` FOREIGN KEY (`quality`) REFERENCES `t_quality` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8432 DEFAULT CHARSET=latin1

CREATE TABLE `t_location` (
  `id` int(10) NOT NULL auto_increment,
  `loc_name` varchar(50) NOT NULL,
  `loc_detail` varchar(50) default NULL,
  `loc_adress1` varchar(50) NOT NULL,
  `loc_adress2` varchar(50) default NULL,
  `loc_country` int(50) NOT NULL default '1',
  `loc_zip` varchar(50) NOT NULL,
  `loc_loc` varchar(50) NOT NULL,
  `loc_shortdesc` varchar(250) default NULL,
  `loc_contact_name` varchar(250) default NULL,
  `loc_contact_gender` int(10) default NULL,
  `loc_contact_phone` varchar(250) default NULL,
  `loc_contact_email` varchar(250) default NULL,
  `loc_contact_url` varchar(250) default NULL,
  `loc_image_path` varchar(250) default NULL,
  `latitude` varchar(100) default NULL,
  `longitude` varchar(100) default NULL,
  `created` datetime NOT NULL,
  `last_change` datetime NOT NULL default '0000-00-00 00:00:00',
  `provider` int(10) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` USING BTREE (`id`),
  KEY `FK_t_location_t_country` (`loc_country`),
  KEY `FK_t_location_t_gender` (`loc_contact_gender`),
  KEY `FK_t_location_t_provider` (`provider`),
  CONSTRAINT `FK_t_location_t_country` FOREIGN KEY (`loc_country`) REFERENCES `t_country`(`id`),
  CONSTRAINT `FK_t_location_t_provider` FOREIGN KEY (`provider`) REFERENCES `t_provider` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1287 DEFAULT CHARSET=latin1

CREATE TABLE `t_dates` (
  `id` int(10) NOT NULL auto_increment,
  `events_id` int(10) NOT NULL,
  `events_start_date` date NOT NULL,
  `events_end_date` date NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `IND_id` (`id`),
  KEY `IND_events_id` (`events_id`),
  CONSTRAINT `t_dates_ibfk_1` FOREIGN KEY (`events_id`) REFERENCES `t_event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32048 DEFAULT CHARSET=latin1

---

我的查询是:

SELECT e.*,I.* ,d.*
FROM t_event AS e
INNER JOIN t_location AS I ON   I.id = e.location
INNER JOIN t_dates  AS d ON  d.events_id  = e.id
;

此查询需要 90 秒才能执行并返回 = 27727

PROFILE 命令显示“发送数据”部分几乎花费了执行时间。EXPLAIN 命令如下:

+----+------------+------+------+----------------------------+--------------------+---------+-----------+-------+-------+
| id | select_type | table | type | possible_keys               | key                | key_len | ref       | rows | Extra |
+----+------------+------+------+----------------------------+--------------------+---------+-----------+-------+-------+
|  1 | SIMPLE     | I    | ALL | PRIMARY,id                 | NULL               | NULL    | NULL      |  1143 |       |
|  1 | SIMPLE     | e    | ref  | PRIMARY,FK_t_event_t_location | FK_t_event_t_location | 4       | wu_db.I.id |     4 |       |
|  1 | SIMPLE     | d    | ref  | IND_events_id               | IND_events_id       | 4       | wu_db.e.id |     3 |       |
+----+------------+------+------+----------------------------+--------------------+---------+-----------+-------+-------+

我的观点是，大量的列是造成这种减速的原因，但即使我写“SELECT e.id, I.events_id, d.id”，它仍然需要 16 秒。

我认为我必须用 LIMIT 和 OFFSET 子句重写查询，你怎么看？

每个表的记录数:

• t_event = 7991
• t_location = 1086
• t_dates = 27727

Answer 1

从广义上讲，MySQL 只能使用查询中每个表的一个 索引来过滤记录。

也就是说，虽然您的 t_event 表在 id 和 location 上都定义了索引，但这些索引中只有一个可用于满足您的查询。您可以在 EXPLAIN 输出中看到这一点，这表明 PRIMARY 和 FK_t_event_t_location 键都被确定为可能有用(后者实际被选中供使用)。

因此，您与 t_dates 的连接(涉及对 id 列的测试)是通过表扫描而不是索引查找来实现的。同样，您可以从 EXPLAIN 输出的第一行看到这一点，其中显示 type = ALL(表扫描)和 key = NULL(不正在使用索引)。

您应该在 (id, location) 上为您的 t_event 表创建一个复合索引:

ALTER TABLE t_event ADD INDEX (id, location);