时间戳

首页 > 解决方案 > MySQL 5.5 和 5.7 上相同的查询，不同的执行时间。（MySQL 5.5 不使用索引）

mysql - MySQL 5.5 和 5.7 上相同的查询，不同的执行时间。（MySQL 5.5 不使用索引）

问题描述

出于兼容性原因，我不得不将生产数据库从 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'

这是 MySQL 5.5 的 EXPLAIN 输出：

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

这是 MySQL 5.7 的 EXPLAIN 输出：

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

我能看到的唯一区别是 MySQL 5.7 使用：Using index condition; Using whereon 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`);

标签： mysqlsqlquery-optimizationquery-performance

解决方案

您的查询按两个相等条件过滤calendar，因此它们应该彼此出现在相同的索引中。然后它使用该product列访问另一个表。因此，将这三列放入一个复合索引中。尝试这个：

 ALTER TABLE calendar ADD INDEX user_id_starts_at_product (user_id, starts_at, product);

您的查询对 进行数据范围过滤detection，并选择具有特定值的行spot_id。所以试试这个复合索引。

 ALTER TABLE detection ADD INDEX spot_id_date (spot_id, date);

还可以尝试使用反向顺序的列的复合索引，并保留可以提供更好性能的索引。

 ALTER TABLE detection ADD INDEX date_spot_id (date, spot_id);

尝试使用复合索引spot来涵盖两个过滤条件（出现在您的 ON 子句中）。

  ALTER TABLE spot ADD INDEX company_id_product (company_id, product);

专业提示：MySQL 通常每个查询（或子查询）只能为每个表使用一个索引。因此，添加大量单列索引通常不是加快特定查询的好方法。相反，添加符合查询要求的复合索引是可行的方法。这适用于各种数据库版本。

推荐阅读