mysql - 提高 MySQL 查询速度 - 返回 150,000 多行会减慢查询速度

Question

您好，我目前有一个查询需要 11(秒)才能运行。我有一份报告显示在一个网站上，该网站运行 4 个不同的查询，这些查询相似，每个查询都需要 11(秒)才能运行。我真的不希望客户必须等待一分钟才能运行所有这些查询并显示数据。

我正在使用 4 个不同的 AJAX 请求来调用 API 来获取我需要的数据，这些都同时启动，但查询一个接一个地运行。如果有办法让这些查询同时运行(并行)，那么总加载时间仅为 11(秒)，这也可以解决我的问题，但我认为这是不可能的。

这是我正在运行的查询:

SELECT device_uuid,
     day_epoch,
     is_repeat
FROM tracking_daily_stats_zone_unique_device_uuids_per_hour
WHERE day_epoch >= 1552435200
AND day_epoch < 1553040000
AND venue_id = 46
AND zone_id IN (102,105,108,110,111,113,116,117,118,121,287)

无论如何我都想不出要加快这个查询的速度，下面是表索引的图片和这个查询的解释语句。

我认为上面的查询是在where条件下使用了相关的索引。

如果您能想到任何可以加快此查询速度的方法，请告诉我，我已经处理了 3 天，但似乎无法找出问题所在。将查询时间减少到最大值 5(秒)会很棒。如果我对 AJAX 问题的理解有误，请告诉我，因为这也可以解决我的问题。

“编辑”

我遇到了一些很奇怪的事情，这可能是导致问题的原因。当我将 day_epoch 范围更改为更小的值(第 5 - 9 行)，返回 130,000 行时，查询时间为 0.7(秒)，但随后我在该范围(第 5 - 10 日)上再添加一天，它返回超过 150,000 行查询时间是 13(秒)。我运行了不同范围的负载，并得出结论，如果返回的行数超过 150,000，这会对查询时间产生巨大影响。

表定义-

CREATE TABLE `tracking_daily_stats_zone_unique_device_uuids_per_hour` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `day_epoch` int(10) NOT NULL,
 `day_of_week` tinyint(1) NOT NULL COMMENT 'day of week, monday = 1',
 `hour` int(2) NOT NULL,
 `venue_id` int(5) NOT NULL,
 `zone_id` int(5) NOT NULL,
 `device_uuid` binary(16) NOT NULL COMMENT 'binary representation of the device_uuid, unique for a single day',
 `device_vendor_id` int(5) unsigned NOT NULL DEFAULT '0' COMMENT 'id of the device vendor',
 `first_seen` int(10) unsigned NOT NULL DEFAULT '0',
 `last_seen` int(10) unsigned NOT NULL DEFAULT '0',
 `is_repeat` tinyint(1) NOT NULL COMMENT 'is the device a repeat for this day?',
 `prev_last_seen` int(10) NOT NULL DEFAULT '0' COMMENT 'previous last seen ts',
 PRIMARY KEY (`id`,`venue_id`) USING BTREE,
 KEY `venue_id` (`venue_id`),
 KEY `zone_id` (`zone_id`),
 KEY `day_of_week` (`day_of_week`),
 KEY `day_epoch` (`day_epoch`),
 KEY `hour` (`hour`),
 KEY `device_uuid` (`device_uuid`),
 KEY `is_repeat` (`is_repeat`),
 KEY `device_vendor_id` (`device_vendor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=450967720 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (venue_id)
PARTITIONS 100 */

Answer 1

直接的解决方案是将此查询特定索引添加到表中:

ALTER TABLE tracking_daily_stats_zone_unique_device_uuids_per_hour 
ADD INDEX complex_idx (`venue_id`, `day_epoch`, `zone_id`)

警告此查询更改在数据库上可能需要一段时间。

然后在调用时强制执行:

SELECT device_uuid,
     day_epoch,
     is_repeat
FROM tracking_daily_stats_zone_unique_device_uuids_per_hour
USE INDEX (complex_idx)
WHERE day_epoch >= 1552435200
AND day_epoch < 1553040000
AND venue_id = 46
AND zone_id IN (102,105,108,110,111,113,116,117,118,121,287)

它绝对不是通用的，但应该适用于这个特定的查询。

更新 当您对表进行分区时，您可以通过强制执行特定的PARTITION 来获利。在我们的例子中，因为那是 venue_id 就强制它:

SELECT device_uuid,
     day_epoch,
     is_repeat
FROM tracking_daily_stats_zone_unique_device_uuids_per_hour
PARTITION (`p46`)
WHERE day_epoch >= 1552435200
AND day_epoch < 1553040000
AND zone_id IN (102,105,108,110,111,113,116,117,118,121,287)

其中 p46 是 p 和 venue_id = 46 的连接字符串

如果你走这条路，还有另一个技巧。您可以从 WHERE 子句中删除 AND venue_id = 46。因为该分区中没有其他数据。