php - MySQL 大型数据库查询速度慢

Question

我有一个包含 IP 范围的表 ipaddresses。列是:ipStart、ipEnd

示例

ipStart: 3579374832
ipEnd: 3579374839

现在我想从另一个表中选择 300 个左右的 IP 地址:visit_count。基于这 300 个地址，我想检查 IP 地址是否在表 ipaddresses

中的任何 IP 范围内

我当前的代码:

$results = $database->query("SELECT user_id, 
                            DATE_FORMAT(last_visit, '%Y-%m-%d') as datumet, 
                            cookieId, ipaddress 
                          FROM `visit_count` 
                          WHERE last_visit BETWEEN 
                                      '$firstDayOfMonth' AND '$lastDayOfMonth' 
                          AND user_id = '$userId' 
                          GROUP BY cookieId 
                          ORDER BY last_visit ASC");        

$rows = $database->loadObjectList($results);    


foreach ( $rows as $row ) {

    $ipaddressLong = ip2long($row->ipaddress);

    // This is where its very very slow
    $selO = $database->query("SELECT ipStart, ipEnd FROM `ipaddresses` 
                        WHERE '$ipaddressLong' BETWEEN `ipStart` AND `ipEnd`");
    $rowO = $database->getrow($selO);

}

结果是一个非常慢的查询，消耗大量 CPU。

ipaddresses 具有 ipStart 和 ipEnd 的索引，包含大约 50k 行。

如何才能让这个过程更快？

Answer 1

使用 JOIN 对单个查询进行快速而肮脏的更改:-

SELECT a.ipStart, a.ipEnd 
FROM `ipaddresses` a
INNER JOIN
(
    SELECT user_id, DATE_FORMAT(last_visit, '%Y-%m-%d') as datumet, cookieId, inet_aton(ipaddress ) AS ipaddressLong
    FROM `visit_count` 
    WHERE last_visit BETWEEN '$firstDayOfMonth' AND '$lastDayOfMonth' 
    AND user_id = '$userId' 
    GROUP BY cookieId 
) b 
ON b.ipaddressLong BETWEEN a.ipStart AND a.ipEnd 

请注意，这可能可以简化。但是，这取决于您对 GROUP BY cookieId 的使用。目前，每个 cookie id 将获取一行，但哪一行未定义(即可以是与该 cookie id 相关的任何 user_id、IP 地址和上次访问日期)。