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`)

有没有更好的方法来构造这个查询，这样它就不需要永远运行？

标签： mysqlperformance

问题：

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

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

mysql - 与 3M 行表的慢连接

问题描述

解决方案

推荐阅读