通过优化SQL调用来替代组

Question

下面的SQL语句非常慢。从600到800毫秒不等！

我正在寻找可能的方法来优化它，但不确定确切的最佳路线。我的数据库相当大，entries表有40万行，devices表有9万行。

SQL语句

SELECT devices.manufacturer, COUNT(devices.manufacturer) AS device_count 
FROM entries 
   JOIN devices ON entries.device_id=devices.id 
WHERE waypoint_id IN (1,2,3,5) 
  AND entries.updated_at >= '2013-06-20 21:01:40 -0400' 
  AND entries.updated_at <= '2013-06-27 21:01:40 -0400'
  GROUP BY devices.manufacturer;

这个SQL语句是因为我在糟糕的硬件上运行，还是因为语句不好，还是因为我没有正确地构造表？任何想法都将不胜感激！

报表目标

获取所有设备制造商的列表，以及该制造商在“条目”表中出现的次数的相关计数。

表结构

器件

id int(11) NOT NULL AUTO_INCREMENT,
mac_address varchar(255) DEFAULT NULL,
user_id int(11) DEFAULT NULL,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
manufacturer varchar(255) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY mac_address (mac_address),
KEY manufacturer (manufacturer)
ENGINE=InnoDB AUTO_INCREMENT=839310 DEFAULT CHARSET=utf8;

条目

id int(11) NOT NULL AUTO_INCREMENT,
device_id int(11) DEFAULT NULL,
created_at datetime NOT NULL,
updated_at datetime NOT NULL,
waypoint_id int(11) DEFAULT NULL,
unsure tinyint(1) DEFAULT '0',
PRIMARY KEY (id),
KEY device_index (device_id)
ENGINE=InnoDB AUTO_INCREMENT=3389538 DEFAULT CHARSET=utf8;

而且-我一直在调查备用数据库。考虑到将来这个数据库将需要非常快的读写速度，像Redis这样的东西会有用吗？

Answer 1

如果在entries(waypoint_id, updated_at)上添加多列索引，查询将运行得更快。

另外，您的查询看起来会更像这样：

SELECT
    devices.manufacturer,
    COUNT(devices.manufacturer) AS device_count 
FROM
    entries
JOIN
    devices ON devices.id = entries.device_id
WHERE
    entries.waypoint_id IN (1,2,3,5)
AND
    entries.updated_at BETWEEN '2013-06-20 21:01:40 -0400' AND '2013-06-27 21:01:40 -0400'
GROUP BY
    devices.device_id

P.S.：明确声明device_id为外键不是一件好事吗？

Answer 2

您需要Entries {waypoint_id, updated_at}上的索引。这应能满足以下方面的要求：

WHERE waypoint_id IN (1,2,3,5) 
  AND entries.updated_at >= '2013-06-20 21:01:40 -0400' 
  AND entries.updated_at <= '2013-06-27 21:01:40 -0400';

根据实际基数，您可能希望或不希望反转此复合索引中字段的顺序。

或者，在盖层上创建一个Entries {waypoint_id, updated_at, device_id}索引，以避免完全访问Entries表。

在此基础上，考虑在Devices {id, manufacturer}上创建索引。希望MySQL能够很聪明地使用它来满足连接和聚合，而不需要访问Devices表。