用3M行表缓慢连接

Question

我正在MySQL中执行以下查询，以查找lat/long的会话ip地址，但在5或6次会话之后，返回该查询需要几分钟时间(如果有的话)。

ip2loc表为2.9M行。

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

是否有更好的方法来构造这个查询，这样它就不会永远运行下去了？

Answer 1

问题是：

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是在一个函数调用中生成的。

它可以使用来自locations表的索引，但只能使用第一个键部分。不可能对两个不同的关键部分进行两次范围扫描(>=和<=)。引擎可以在(ip_from, ip_to)上使用索引，但需要读取索引(平均为1.45M行)中sessions表中每一行的一半行。引擎甚至可能决定根本不使用索引。因此，您将得到两个表的完整连接。

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

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在优化联接的范围条件方面做得很差。新版本现在可能更好。

Answer 2

select sessions.ip, 
     ip2loc.region_name, 
     ip2loc.city_name,
     ip2loc.latitude, 
     ip2loc.longitude,
     count(sessions.ip) as count 
from ip2location.ip2loc
INNER JOIN sessions ON INET_ATON(sessions.ip) between ip2loc.ip_from and. ip2locip_to 
group by sessions.ip

请确保在表会话中也有索引，列为IP。

可以改进复合索引，为选择的列添加冗余：

KEY `idx_ip_from_to` (`ip_from`,`ip_to`,region_name,  city_name, latitude, longitude )

这样，查询就可以检索索引中的所有信息，而不需要访问表数据。

为了提高可读性，您不应该使用基于where的旧的隐式联接语法，而应该使用基于join和on子句的显式连接语法。