出于兼容性原因，我不得不将一个生产数据库从mysql 5.7降级到mysql 5.5。
在移到5.5之后，我注意到这个查询变得慢了很多，从大约200毫秒到大约20秒的执行时间。
问题是：
SELECT
COUNT(*)
FROM
`calendar`
INNER JOIN
`spot` ON `spot`.`product` = `calendar`.`product`
AND `spot`.`company_id` = `calendar`.`company_id`
INNER JOIN
`detection` ON `detection`.`spot_id` = `spot`.`id`
WHERE `calendar`.`starts_at` = '2017-11-17'
AND `calendar`.`user_id` = 73
AND `detection`.`date` >= '2017-11-17'
AND `detection`.`date` <= '2017-11-23'以下是MySQL5.5的解释输出：
1 SIMPLE | calendar | ref starts_at_ends_at_index starts_at_ends_at_index 3 const 1204 | Using where
1 SIMPLE | spot ref PRIMARY,company_id_index,product_index | product_index | 302 calendar.product | 13 | Using where
1 SIMPLE | detection | ref spot_id_index,date_index | spot_id_index 48 | spot.Id | 80 | Using where以下是MySQL5.7的解释输出：
1 SIMPLE | calendar | ref starts_at_ends_at_index starts_at_ends_at_index 3 const 1204 | Using where
1 SIMPLE | spot ref PRIMARY,company_id_index,product_index | product_index | 302 calendar.product | 13 | Using index condition; Using where
1 SIMPLE | detection | ref spot_id_index,date_index | spot_id_index 48 | spot.Id | 80 | Using where我能看到的唯一区别是MySQL5.7使用了： Using index condition; Using where 在 product_index ，5.5不要。
我试图通过指定 USE INDEX(product_index) ，但一切都没变
有什么建议吗？
编辑：
当前有用索引：
ALTER TABLE `calendar` ADD INDEX `starts_at_ends_at_index` (`starts_at`, `ends_at`);
ALTER TABLE `spot` ADD INDEX `company_id_index` (`company_id`);
ALTER TABLE `spot` ADD INDEX `product_index` (`product`);
ALTER TABLE `detection` ADD INDEX `spot_id_index` (`spot_id`);
ALTER TABLE `detection` ADD INDEX `date_index` (`date`);
2条答案
按热度按时间3gtaxfhh1#
我会尝试将不过滤日历表的where子句 predicate 移动到join predicate 中，如果没有其他帮助的话，它可以帮助可读性，但也可以帮助引擎编译一个更优化的计划。
也有可能在降级后索引需要重建，您可以使用下面的方法为每个表执行此操作。
不过，在运行时它确实会锁定表，所以在生产数据库中要记住这一点。
最后，是
spot.product的外键calendar.product反之亦然？它们是完全相同的数据类型吗？eh57zj3b2#
您的查询筛选器
calendar两个相等的标准，所以它们应该出现在同一个索引中。然后使用product列来访问另一个表。所以，把这三列放在一个复合索引中。试试这个：您的查询对数据区域进行筛选
detection，并选择具有特定值的行spot_id. 所以试试这个复合索引。另外，还可以尝试将列按相反顺序排列的复合索引，并保留能提供更好性能的索引。
在上尝试复合索引
spot涵盖两个筛选条件（出现在on子句中）。专业提示：mysql通常只能为每个查询（或子查询）的每个表使用一个索引。因此，添加大量的单列索引通常不是加快特定查询速度的好方法。相反，添加符合查询要求的复合索引是一种可行的方法。对于各种数据库版本都是如此。