為什麼這個 sql 連接查詢在“on”子句中使用對第一個表中的行的引用而不是使用文字變數需要更長的時間？

我有兩個表，“auth”和“ips”，我想加入查詢。

表“身份驗證”：

由…製作：\

CREATE TABLE `auth` (
   `id` int PRIMARY KEY AUTO_INCREMENT, 
   `date` varchar(6), 
   `timestamp` varchar(8), 
   `result` varchar(8), 
   `ip` varchar(15),
   `user` text,
   `service` varchar(4)
);

沒有索引。

範例行：

上下文：我有一個在本地執行的程序，它監視 /var/log/auth.log 文件並將 ssh 登錄嘗試插入到該表中。包含大約 3,200 行並且還在增加。

表“ips”：

由…製作：

CREATE TABLE `ips` (
   `start` int UNSIGNED, 
   `end` int UNSIGNED, 
   `country` text, 
   INDEX(`start`, `end`)
);

開始和結束的索引（按該順序），表也按開始（升序）排序，開始和結束的行只包含唯一值。

範例行：

上下文：此表儲存 IP 範圍和它（很可能）分配到的國家/地區。IP 儲存為 unsigned int，就像使用 INET_ATON 獲得的一樣。包含 486,257 行，不再添加，只是很少完整更新。

現在到查詢：

我目前正在使用的查詢：

SELECT  auth.date, auth.timestamp, auth.result, auth.ip, auth.user,
       ips.country
   FROM  `auth`
   INNER JOIN  ips  ON (INET_ATON(auth.ip) >= ips.start
                   AND  INET_ATON(auth.ip) < ips.end )
   LIMIT  100;

這個查詢在我的系統上大約需要 9.7 秒，甚至沒有提到遍歷所有 3200 多行。

EXPLAIN 查詢給出以下結果：

如果我將 ‘ON’ 子句中的 auth.ip 引用更改為靜態 IP（讓我們組成一個：200.200.200.200），我們會得到以下查詢：

SELECT  auth.date, auth.timestamp, auth.result, auth.ip, auth.user,
       ips.country
   FROM  `auth`
   INNER JOIN  ips  ON (INET_ATON("200.200.200.200") >= ips.start
                   AND  INET_ATON("200.200.200.200") < ips.end )
   LIMIT  100;

此查詢只需 0.15 秒即可返回。

EXPLAIN 查詢給出以下結果：

1. 為什麼第一個查詢比第二個查詢花費大約 63 倍的時間？
2. 可以改進第一個查詢的性能嗎？

MySQL 伺服器版本 8.0.26

如果我錯過了什麼，請告訴我，因為這是我的第一篇文章。

直接回答：

INET_ATON(auth.ip)是不可分割的。

INET_ATON("200.200.200.200")在開始執行查詢之前進行評估。

沒有簡單的方法可以使范圍測試有效。這是一個不那麼簡單的解決方案：http: //mysql.rjweb.org/doc.php/ipranges，它做得非常有效。

引用自：https://dba.stackexchange.com/questions/298730