时间戳

首页 > 解决方案 > 为 MySQL 子查询或跨范围连接使用索引

mysql - 为 MySQL 子查询或跨范围连接使用索引

问题描述

我有一个请求列表及其各自的 IP 地址（约 200 万行）。我正在尝试对不重叠的完整IP 范围列表（约 1200 万行）做一个简单JOIN的处理。我已经用b_tree 升序和b_tree 升序索引了 IP 范围。ip_fromip_to

我已经尝试了几种技术来管理组合这两个表中的数据，到目前为止，所有这些技术都被证明是非常低效的。

我尝试了常规JOIN， IP范围JOIN的最大差异和使用子查询。使用EXPLAIN它们都显示有possible_keys，没有使用它们。我试过使用FORCE INDEX没有任何运气。

常规选择分别显示 IP 查找大约需要 2 毫秒，SELECT * FROM ip_ranges WHERE INET_ATON(<some ip>) <= ip_to LIMIT 1;请求表每 200 次查找大约需要 16 毫秒。

这是我当前的查询。这需要大约 30 秒才能返回任何结果，这仅仅是因为索引没有被充分利用：

SELECT 
rs.fingerprint,
rs.ip,
ipr.country_code,
ipr.country_name,
ipr.region,
ipr.city,
ipr.isp_name,
ipr.domain_name,
ipr.usage_type
FROM requests AS rs
JOIN ip_ranges AS ipr ON INET_ATON(rs.ip) BETWEEN ipr.ip_from AND ipr.ip_to
LIMIT 10;

那么，有什么方法可以针对 MySQL 进行优化吗？还是我应该只使用 Python 为每个请求单独调用数据库？（在 SQL 之外手动加入它们）。

更新：

我现在尝试将每个 IP 地址转换为它们各自的数字格式，存储在下面的答案中建议的DECIMAL(39)列中。ip_numeric39 也用于支持 IPv6 地址。数据库仍然不会使用索引键进行范围查找。

标签： mysqlsqlipquery-optimization

解决方案

您可以向表和索引添加一个虚拟列：

ALTER TABLE requests ADD ip_numeric bigint GENERATED ALWAYS AS (INET_ATON(ip)) virtual;

CREATE INDEX ip_numeric_ind ON requests (ip_numeric)

然后在您的查询中使用它：

SELECT 
rs.fingerprint,
rs.ip,
ipr.country_code,
ipr.country_name,
ipr.region,
ipr.city,
ipr.isp_name,
ipr.domain_name,
ipr.usage_type
FROM requests AS rs
JOIN ip_ranges AS ipr ON ip_numeric BETWEEN ipr.ip_from AND ipr.ip_to
LIMIT 10;

推荐阅读