mysql查询缓慢- IP查找(禁止或不禁止)

I have on my PHP file a function that check if an IP is banned or not. For some reason my site is very slow and the problem is when I check if the IP is banned or not.

我的PHP文件中有一个函数，用于检查是否禁止IP。由于某些原因，我的站点非常慢，问题是当我检查IP是否被禁止时。

(I remove the code that checks and my site was fast again)

(我删除了检查的代码，我的网站又快了)

Here's my code:

这是我的代码:

// index.php - everything redirects to this file in the .htaccess
<?php
include('config.php');
if(isIpBanned($_SERVER['REMOTE_ADDR'])) {
 die('access denied');
}
// rest of the code

here's my function

这是我的函数

// config.php
<?php
function isIpBanned($db, $ip) { // $db is declared correctly
 $goodIP = $db->getRecord("SELECT is_banned FROM security.ip WHERE ip = '$ip'"); // this function works and return 1 or 0
 return (bool)$goodIP;
}

This query takes about 2 seconds to 3 seconds to run. Why? I don't have left join or other tables.

这个查询大约需要2秒到3秒的时间运行。为什么?我没有留下join或其他表。

Thanks

谢谢

2 个解决方案

#1

Put a (unique?) index on the IP column
在IP列上放置一个(惟一的?)索引
Use the correct datatype by converting the textual representation to a "native" one (an ipv4 fits in a INT UNSIGNED, an ipv6 in 2 BIGINT UNSIGNED): this will make your tables smaller, and will require less I/O during scans
使用正确的数据类型，将文本表示转换为“本机”表示(ipv4适用于INT UNSIGNED, ipv6适用于2bigint UNSIGNED):这将使您的表更小，并且在扫描期间所需的I/O更少

and, as a side note, even if $_SERVER["REMOTE_ADDR"] should be safe, NEVER FORGET TO ESCAPE THE DATA IN SQL QUERIES!

顺便说一句，即使$_SERVER["REMOTE_ADDR"]应该是安全的，也不要忘记在SQL查询中转义数据!

#2

Put an index on the ip column.

在ip列上放置一个索引。

There's literally tons of information out on the web on query analysing and improveemnt so I'm not gonna repeat that for you, but an index will most definetely help.

网上有很多关于查询分析和veemnt的信息，我就不重复了，但是索引绝对会有帮助。

I guess security.ip is a very large table, so the lookup becomes slow.

我觉得安全。ip是一个非常大的表，所以查找变得很慢。

The drawback of an index: writing becomes somewhat slower, so if you write a lot to that table, you could try to offload the banning part to a new table, say banned_ips.

索引的缺点是:写入速度变慢了，所以如果您对该表写了很多，您可以尝试将禁止部分卸载到新表中，banned_ips说。

#1

Put a (unique?) index on the IP column
在IP列上放置一个(惟一的?)索引
Use the correct datatype by converting the textual representation to a "native" one (an ipv4 fits in a INT UNSIGNED, an ipv6 in 2 BIGINT UNSIGNED): this will make your tables smaller, and will require less I/O during scans
使用正确的数据类型，将文本表示转换为“本机”表示(ipv4适用于INT UNSIGNED, ipv6适用于2bigint UNSIGNED):这将使您的表更小，并且在扫描期间所需的I/O更少