mysql 查询慢 - IP 查找（禁止与否）
我的 PHP 文件中有一个函数可以检查 IP 是否被禁止。由于某种原因，我的网站速度非常慢，问题是当我检查 IP 是否被禁止时。
（我删除了检查代码，我的网站再次变得很快）
这是我的代码：
// 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
这是我的函数
// 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;
}
此查询需要大约 2 秒到 3 秒的时间来运行。为什么？我没有左连接或其他表。
谢谢
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.
(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.
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
IP列上放置一个（唯一？）索引INT UNSIGNED， ipv6 in 2BIGINT UNSIGNED)：这将使您的表更小，并且在扫描期间需要更少的 I/O，并且，作为旁注，即使
$_SERVER["REMOTE_ADDR"]应该是安全的，永远不要忘记转义 SQL 查询中的数据！IPcolumnINT UNSIGNED, an ipv6 in 2BIGINT UNSIGNED): this will make your tables smaller, and will require less I/O during scansand, as a side note, even if
$_SERVER["REMOTE_ADDR"]should be safe, NEVER FORGET TO ESCAPE THE DATA IN SQL QUERIES!在 ip 列上放置索引。
网络上确实有大量有关查询分析和改进的信息，因此我不会为您重复这些信息，但索引绝对会有所帮助。
我猜
security.ip是一个非常大的表，因此查找变得很慢。索引的缺点：写入速度会变慢，因此如果您向该表写入大量数据，您可以尝试将禁止部分卸载到新表，例如
banned_ips。Put an index on the ip column.
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.
I guess
security.ipis a very large table, so the lookup becomes slow.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.