mysql - 使用 JOIN 优化 MySQL 计数查询

Question

我有一个查询需要大约 20 秒，我想了解是否有办法优化它。表1:

CREATE TABLE IF NOT EXISTS `sessions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9845765 ;

表 2:

CREATE TABLE IF NOT EXISTS `access` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `session_id` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `session_id ` (`session_id `)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9467799 ;

现在，我想做的是计算连接到一个用户的所有 session 的所有访问，所以我的查询是:

SELECT COUNT(*)
FROM access
INNER JOIN sessions ON access.session_id=session.id
WHERE session.user_id='6';

大约需要 20 秒...对于 user_id 6，存储了大约 300 万个 session 。我可以做些什么来优化该查询？

Answer 1

从 session 表中更改此行:

KEY `user_id` (`user_id`)

对此:

KEY `user_id` (`user_id`, `id`)

这将为您做的是允许您从索引完成查询，而无需返回到原始表。事实上，您需要在 session 表上对您的 user_id 进行索引扫描，并为每个项目返回表以查找连接的 id访问表。通过在索引中包含 id，您可以跳过返回表的过程。

遗憾的是，这将使您在该表中的插入速度变慢，并且考虑到只有一个用户拥有 300 万个 session ，这似乎可能是一笔出价交易。 Sql Server 和 Oracle 将通过允许您在索引中包含 id 列来解决此问题，而无需实际对其进行索引，从而在插入时节省一些工作，并且还允许您指定较低的填充因子对于索引，减少了插入时重新构建或重新排序索引的需要，但 MySql 不支持这些。