慢慢加入3M行表

作者: 烏鴉喝酒
发布时间: 2024-12-17 07:03:20 (1月前)
转自：

3 条回复

0#
回复此人
v-star*위위 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> 问题： <pre> <code> INET_ATON(sessions.ip) between ip_from and ip_to </code> </pre> 这相当于 <pre> <code> INET_ATON(sessions.ip) >= ip_from AND INET_ATON(sessions.ip) <= ip_to </code> </pre> 此条件不能使用索引 <code> sessions </code> 表，因为 <code> sessions.ip </code> 在一个结束的电话中被包裹着。 它可以使用位置表中的索引 - 但只能使用第一个关键部分。无法进行两次范围扫描（ <code> >= </code> 和 <code> <= </code> ）在两个不同的关键部分。引擎可以使用索引 <code> (ip_from, ip_to) </code> ，但需要读取索引中所有行的一半（平均1.45M行）的 对于每一行 </强> 在里面 <code> sessions </code> 表。引擎甚至可能决定根本不使用索引。所以你最终得到了一个 完全加入 两张桌子。 您可以做的第一个优化是缩小行数 <code> sessions </code> 表，将GROUP BY查询包装到子查询中： <pre> <code> 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 </code> </pre> 如果仍然太慢，您可以尝试将子查询结果存储到索引的临时表中： <pre> <code> 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 </code> </pre> 这样PK（ <code> ip_bin </code> ）临时表可以用于连接。然而 - 这是理论。根据我的经验，MySQL在优化连接的范围条件方面做得很差。新版本现在可能会更好。 </DIV>

编辑
1#
回复此人
敢嫁就敢娶 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <pre> <code> 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 </code> </pre> 确保您还有表会话列ip的索引 您可以改进复合索引，为select中所需的列添加冗余 <pre> <code> KEY `idx_ip_from_to` (`ip_from`,`ip_to`,region_name, city_name, latitude, longitude ) </code> </pre> 通过这种方式，查询可以检索索引中的所有信息，而不需要访问表数据 为了便于阅读，您不应该使用基于where的旧隐式连接sintax，而是使用基于join和ON子句的显式连接sintax </DIV>

编辑

登录后才能参与评论