下面是4张表的表结构:

日历:

CREATE TABLE `calender` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `HospitalID` int(11) NOT NULL,
  `ColorCode` int(11) DEFAULT NULL,
  `RecurrID` int(11) NOT NULL,
  `IsActive` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`),
  KEY `idxHospital` (`ID`,`StaffID`,`HospitalID`,`ColorCode`,`RecurrID`,`IsActive`)
) ENGINE=InnoDB AUTO_INCREMENT=4638 DEFAULT CHARSET=latin1;

日历参加者:

CREATE TABLE `calenderattendee` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `CalenderID` int(11) NOT NULL,
  `StaffID` int(11) NOT NULL,
  `IsActive` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `idxCalStaffID` (`StaffID`,`CalenderID`)
) ENGINE=InnoDB AUTO_INCREMENT=20436 DEFAULT CHARSET=latin1;

调用计划人员:

CREATE TABLE `callplanstaff` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Staffname` varchar(45) NOT NULL,
  `IsActive` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`),
  KEY `idx_IsActive` (`Staffname`,`IsActive`),
  KEY `idx_staffName` (`Staffname`,`ID`) USING BTREE KEY_BLOCK_SIZE=100
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

用户:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL DEFAULT '',
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_users_on_email` (`email`),
  UNIQUE KEY `index_users_on_name` (`name`),
  KEY `idx_email` (`email`) USING BTREE KEY_BLOCK_SIZE=100
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

我要做的是使用以下查询获取 calender.ID 和 Users.name:

SELECT a.ID, h.name
    FROM `stjude`.`calender` a 
    left join calenderattendee e on a.ID = e.calenderID
    left join callplanstaff f on e.StaffID = f.ID
    left join users h on f.Staffname = h.email

这些表之间的关系是:

获取 13000 条记录大约需要 4 秒，我敢打赌它可能会更快。

当我查看查询的表格解释时，结果如下:

为什么 MYSQL 不在 callplanstaff 表和 users 表上使用索引？

此外，就我而言，我应该使用多索引而不是多列索引吗？

是否缺少任何索引导致我的查询变慢？

============================================= ========================

更新:

由于zedfoxus和spencer7593建议改变idxCalStaffID的顺序和idx_staffname的顺序，下面是执行计划:

抓取用了 0.063 秒，所需时间少得多，索引的顺序如何影响抓取时间..？

最佳答案

您误解了 EXPLAIN 报告。

• type: index 可不是什么好东西。这意味着它正在执行“索引扫描”，检查索引的每个 元素。它几乎和表扫描一样糟糕。请注意列 rows: 4562 和 rows: 13451。这是它将为每个表检查的索引元素的估计数量。
• 让两个 表进行索引扫描更糟糕。为此连接检查的总行数为 4562 x 13451 = 61,363,462。
• 使用连接缓冲区 不是一件好事。这是优化器在无法使用索引进行连接时作为安慰所做的事情。

• type: eqref 是个好东西。这意味着它使用 PRIMARY KEY 索引或 UNIQUE KEY 索引来查找一行。请注意列 rows: 1。因此，至少对于前一个连接中的每一行，它只进行一次索引查找。

• 您应该在 calenderattendee 上按顺序为列(CalenderId、StaffId)创建一个索引(@spencer7593 在我写帖子时发布了这个建议)。

• 通过在此查询中使用 LEFT [OUTER] JOIN，您将阻止 MySQL 优化表连接的顺序。由于您的查询提取了 h.name，我推断您真的只想要日历事件有参加者并且参加者有相应用户记录的结果。您没有使用 INNER JOIN 是没有意义的。

这是带有新索引的 EXPLAIN，连接更改为 INNER JOIN(尽管我的行计数没有意义，因为我没有创建测试数据):

+----+-------------+-------+------------+--------+--------------------------------+----------------------+---------+----------------+------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys                  | key                  | key_len | ref            | rows | filtered | Extra                 |
+----+-------------+-------+------------+--------+--------------------------------+----------------------+---------+----------------+------+----------+-----------------------+
|  1 | SIMPLE      | a     | NULL       | index  | PRIMARY,ID_UNIQUE,idxHospital  | ID_UNIQUE            | 4       | NULL           |    1 |   100.00 | Using index           |
|  1 | SIMPLE      | e     | NULL       | ref    | idxCalStaffID,CalenderID       | CalenderID           | 4       | test.a.ID      |    1 |   100.00 | Using index           |
|  1 | SIMPLE      | f     | NULL       | eq_ref | PRIMARY,ID_UNIQUE              | PRIMARY              | 4       | test.e.StaffID |    1 |   100.00 | NULL                  |
|  1 | SIMPLE      | h     | NULL       | eq_ref | index_users_on_email,idx_email | index_users_on_email | 767     | func           |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+--------+--------------------------------+----------------------+---------+----------------+------+----------+-----------------------+

calenderattendee 表的 type: index 已更改为 type: ref，这意味着针对非唯一索引的索引查找。关于 Using join buffer 的注释也不见了。

那应该运行得更好。

how does the ordering of the indexing affects the fetch time..?

想一想电话簿，它是先按姓氏排序，然后按名字排序。这可以帮助您非常快速地按姓氏查找人员。但它不能帮助您按名字查找人员。

列在索引中的位置很重要!

您可能会喜欢我的演示文稿如何真正设计索引。

• 幻灯片:http://www.slideshare.net/billkarwin/how-to-design-indexes-really
• 我的演讲视频:https://www.youtube.com/watch?v=ELR7-RdU9XU

关于MYSQL 左连接在索引列上速度极慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/40415245/