mysql - 慢速加入3M行表

我正在MySQL中执行以下查询，以查找会话ip地址的经/纬度，但是在5或6个会话之后，该查询需要几分钟才能返回（如果有的话）。

ip2loc表为290万行。

select sessions.ip,
       ip2loc.region_name, ip2loc.city_name,
       ip2loc.latitude,
       ip2loc.longitude,
       count(sessions.ip) as count
from ip2location.ip2loc, sessions
where INET_ATON(sessions.ip) between ip_from and ip_to
group by ip

ip2loc表具有以下索引：

 KEY `idx_ip_from` (`ip_from`),
 KEY `idx_ip_to` (`ip_to`),
 KEY `idx_ip_from_to` (`ip_from`,`ip_to`)

有没有更好的方法来构造此查询，这样它就不会花很多时间运行？

最佳答案

问题：

INET_ATON(sessions.ip) between ip_from and ip_to

相当于

INET_ATON(sessions.ip) >= ip_from
AND
INET_ATON(sessions.ip) <= ip_to

此条件不能使用sessions表中的索引，因为sessions.ip被包装在函数调用中。

它可以使用位置表中的索引-但只能使用第一个关键部分。在两个不同的关键部分上不能进行两次范围扫描（>=和<=）。引擎可以使用(ip_from, ip_to)上的索引，但是需要为sessions表中的每一行读取索引中所有行的一半（平均为145万行）。引擎甚至可能决定根本不使用索引。因此，您最终得到了两个表的完整联接。

您可以做的第一个优化是缩小sessions表中的行数，将GROUP BY查询包装到一个子查询中：

select s.ip,
       l.region_name,
       l.city_name,
       l.latitude,
       l.longitude,
       s.count
from (
    select ip, INET_ATON(s.ip) ip_bin, count(*) as count
    from sessions
    group by ip
) s
join ip2location l on s.ip_bin between ip_from and ip_to

如果那仍然太慢，则可以尝试将子查询结果存储到索引的临时表中：

create temporary table tmp_session_ips(
    ip_bin int unsigned primary key,
    ip varchar(15) not null,
    `count` int unsigned
)
    select ip, INET_ATON(s.ip) ip_bin, count(*) as count
    from sessions
    group by ip
    order by ip_bin
;

select s.ip,
       l.region_name,
       l.city_name,
       l.latitude,
       l.longitude,
       s.count
from tmp_session_ips s
join ip2location l on s.ip_bin between ip_from and ip_to

这样，临时表中的PK（ip_bin）可以用于连接。但是-这是理论。以我的经验，MySQL在优化联接的范围条件方面做得很差。现在，新版本可能会更好。

关于mysql - 慢速加入3M行表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54970774/