PHP到EasyPHP MySQL服务器1秒连接延迟
我在这里问这个是因为我认为它不仅仅适用于 EasyPHP 本身.
I'm asking this here because I think it applies to more than just EasyPHP itself.
我目前使用 EasyPHP 作为我的 WAMP 开发服务器，因此我可以创建 Web 应用程序.问题是，当我让 mysqli 对象连接到数据库时，它需要 1 秒.在共享主机上运行相同的查询会导致速度提高 200 倍以上.这是我应该担心的事情吗?在可扩展性或将我的应用程序移动到另一台服务器方面，花点时间看看问题是什么是明智的吗?我只是假设 EasyPHP 可能很慢.不是专业，只是让我觉得有趣的事情.
I currently use EasyPHP as my WAMP development server so I can create web applications. The problem is, when I get the mysqli object to connect to the database it takes 1 second. Running the same query on shared hosting results in speeds over 200 times faster. Should this be something I should be worried about? In terms of scalability or moving my application to another server would it be smart to spend a while seeing what the issue is? I just assumed that perhaps EasyPHP was slow. Not a major, just something that struck me as interesting.
推荐答案
如果您遇到此问题并且使用的是 Windows 7 之前的 Windows 版本，这可能不是您问题的答案.
为什么会这样?
这个问题的原因是 IPv4 vs IPv6.
The cause of this problem is IPv4 vs IPv6.
当您使用主机名而不是 IP 地址时，MySQL 客户端首先运行 AAAA (IPv6) 主机查找名称，如果成功将名称解析为IPv6 地址.如果任一步骤失败(名称解析或连接)，它将回退到 IPv4，运行 A 查找并尝试使用此主机.
When you use a host name instead of an IP address, the MySQL client first runs an AAAA (IPv6) host lookup for the name, and tries this address first if it successfully resolves the name to an IPv6 address. If either step fails (name resolution or connection) it will fallback to IPv4, running an A lookup and trying this host instead.
这在实践中意味着如果 IPv6 localhost 查找成功但 MySQL 未绑定到 IPv6 环回，您将需要等待一个连接超时周期(显然在 OP 的机器上)这是发生 IPv4 回退和连接成功之前的 1 秒).
What this means in practice is that if the IPv6 localhost lookup is successful but MySQL is not bound to the IPv6 loopback, you will need to wait for one connection timeout cycle (evidently on the OP's machine this is 1 second) before the IPv4 fallback occurs and the connection succeeds.
这在 Windows 7 之前不是问题，因为 localhost 解析是通过主机文件完成的，并且它只预先配置了 127.0.0.1 - 它没有附带它的 IPv6 对应物 ::1.
This was not an issue prior to Windows 7, because localhost resolution was done via the hosts file, and it came preconfigured with only 127.0.0.1 - it did not come with it's IPv6 counterpart ::1.
但是，从 Windows 7 开始，localhost 解析内置于 DNS 解析器中，原因在此处.这意味着 IPv6 查找现在将成功 - 但 MySQL 未绑定到该 IPv6 地址，因此连接将失败，您将看到此问题中概述的延迟.
Since Windows 7, however, localhost resolution is built into the DNS resolver, for reasons outlined here. This means that the IPv6 lookup will now succeed - but MySQL is not bound to that IPv6 address, so the connection will fail, and you will see the delay outlined in this question.
那很好.只要告诉我如何修复它！
您有几个选择.环顾互联网，一般的解决方案"似乎是显式使用 IP 地址而不是名称，但有几个原因不这样做，两者都与可移植性有关，两者都可以说都不重要:
You have a few options. Looking around the internet, the general "solution" seems to be to use the IP address explicitly instead of the name, but there are a couple of reasons not to do this, both portability related, both arguably not important:
如果您将脚本移到另一台仅支持 IPv6 的机器上，您的脚本将不再起作用.
If you move your script to another machine that only supports IPv6, your script will no longer work.
如果您将脚本移至基于 *nix 的托管环境，则魔术字符串 localhost 将意味着 MySQL 客户端更喜欢使用 Unix 套接字(如果已配置)，这是比基于 IP 环回的连接更有效
If you move your script to a *nix-based hosting environment, the magic string localhost would mean the MySQL client would prefer to use a Unix socket if one is configured, this is more efficient than IP loopback based connectivity
它们听起来很重要吗?
他们不是.您应该设计您的应用程序，以便在配置文件中定义此类内容.如果您将脚本移至其他环境，则可能还需要配置其他内容.
They aren't. You should be designing your application so that this sort of thing is defined in a configuration file. If you move your script to another environment, chances are other things will need configuring as well.
总而言之，使用 IP 地址不是最佳解决方案，但很可能是可接受的解决方案.
In summary, using the IP address is not the best solution, but it is most likely an acceptable one.
那么最好的解决方案是什么?
最好的方法是更改 MySQL 服务器使用的绑定地址.然而，这并不像人们想象的那么简单.与 Apache、Nginx 和几乎所有其他健全的网络服务应用程序不同，MySQL 仅支持单个绑定地址，因此它不仅仅是添加另一个绑定地址的情况.幸运的是，操作系统确实在这里支持了一些魔法，因此我们可以让 MySQL 同时使用 IPv4 和 IPv6.
The best way would be to change the bind address that the MySQL server uses. However, this is not as simple as one might like. Unlike Apache, Nginx and almost every other sane network service application ever made, MySQL only supports a single bind address, so it's not just a case of adding another one. Luckily though, operating systems do support a bit of magic here, so we can enable MySQL to use both IPv4 and IPv6 simultaneously.
您需要运行 MySQL 5.5.3 或更高版本，并且您需要使用 --bind-address= 命令行参数启动 MySQL(或在 my.ini 中设置相应选项).您有 4 个选项docs，取决于你想做什么:
You need to be running MySQL 5.5.3 or later, and you need to start MySQL with the --bind-address= command line argument (or set the corresponding option in my.ini). You have 4 optionsdocs, depending on what you want to do:
您可能熟悉，并且最有可能(有效)使用的那个，
0.0.0.0.这将绑定到机器上所有可用的 IPv4 地址.即使您不关心 IPv6，这实际上也可能不是最好的做法，因为它面临与::相同的安全风险.
The one you are probably familiar with, and the one that you are most likely (effectively) using,
0.0.0.0. This binds to all available IPv4 addresses on the machine. This actually is probably not the best thing to do even if you don't care about IPv6, as it suffers the same security risks as::.
显式 IPv4 或 IPv6 地址(例如 127.0.0.1 或 ::1 用于环回).这会将服务器绑定到该地址并且仅该地址.
An explicit IPv4 or IPv6 address (for example 127.0.0.1 or ::1 for loopback). This binds the server to that address and only that address.
魔法字符串::.这将在 IPv4 和 IPv6 模式下将 MySQL 绑定到机器上的每个地址，包括环回和物理接口地址.这可能存在安全风险，仅当您需要 MySQL 接受来自远程主机的连接时才这样做.
The magic string ::. This will bind MySQL to every address on the machine, both loopback and physical interface addresses, in IPv4 and IPv6 mode. This is potentially a security risk, only do this if you need MySQL to accept connections from remote hosts.
使用IPv4 映射的 IPv6 地址.这是 IPv6 中内置的一种特殊机制，用于在 4 -> 6 转换期间向后兼容，它允许您绑定到特定的 IPv4 地址，它与 IPv6 等效.除了双环回"地址 ::ffff:127.0.0.1 之外，这对您不太可能有用.对于大多数人来说，这很可能是最好的解决方案，仅绑定到环回但允许 IPv4 和 IPv6 连接.
Use an IPv4-mapped IPv6 address. This is a special mechanism built into IPv6 for backwards compatibility during the 4 -> 6 transition, and it allows you bind to a specific IPv4 address and it's IPv6 equivalent. This is quite unlikely to be useful to you for anything other than the "dual loopback" address ::ffff:127.0.0.1. This is most likely the best solution for most people, only binding to the loopback but allowing both IPv4 and IPv6 connections.
我需要修改hosts文件吗?
否.不要修改主机文件.DNS 解析器知道如何处理 localhost，重新定义它充其量没有任何效果，最坏的情况是混淆解析器.
NO. Don't modify the hosts file. The DNS resolver knows what to do with localhost, redefining it will at best have no effect, and at worst confuse the hell out of the resolver.
<代码>--skip-name-resolve?
这也可能解决问题/需要解决问题，原因相关但略有不同.
This may also fix the problem/be required to fix the problem, for a related but slightly different reason.
如果没有此配置选项，MySQL 将尝试通过 PTR DNS 查询将所有客户端连接 IP 地址解析为主机名.如果您的 MySQL 服务器已经启用使用 IPv6 但连接仍然需要很长时间，这可能是因为反向 DNS (PTR) 记录没有正确配置.
Without this configuration option, MySQL will attempt to resolve all client connection IP addresses to a hostname via a PTR DNS query. If your MySQL server is already enabled to use IPv6 but connections are still taking a long time, it may be because the reverse DNS (PTR) record is not correctly configured.
禁用名称解析将解决此问题，但它确实有其他后果，特别是在 Host 条件中配置为使用 DNS 名称的任何访问权限现在都将失败.
Disabling name resolution will fix this problem, but it does have other ramifications, notably that any access permissions configured to use a DNS name in the Host condition will now fail.
如果您打算这样做，您需要将所有授权配置为使用 IP 地址而不是名称.
If you are going to do this, you will need to configure all your grants to use IP addresses instead of names.
相关文章