什么更有效，为什么:每页一个数据库连接或每个函数一个数据库连接? [英] whats more efficient and why: one db connection per page or one db connection per function?

问题描述

我正在开发一个非常受 MySQL 数据库驱动的网站.所以我有很多疑问.

I am working on a website which is very MySQL DB driven. So I have a lot of queries going on.

在 这个话题大家建议在页面顶部连接DB，在页面底部断开连接.

In this topic everyone recommends to connect to the DB at the top of the page, and disconnect at the bottom of the page.

我想知道什么更有效，或者一般来说是最佳实践:每页建立一个数据库连接，还是只在需要时连接?(或者没有一般的答案，这取决于?)

I am wondering what's more efficient, or generally speaking best practice: Make a single db connection per page, or only connect as needed? (Or is there no general answer, and it depends?)

此外，我希望找出为什么这是最佳实践，您是从哪个角度来看场景的(例如安全性、速度……我不知道还有什么数据库连接可能会影响?！)

Additionally I am looking to find out WHY is this best practice, from which point of view are you looking at the scenario (e.g. security, speed, ... I don't know what else DB connections might affect?!)

我相信在 这里之前已经问过这个问题 - 但不是特别针对 PHP，因此我觉得它没有帮助.

I believe this question has been asked before here - but not for PHP in specific, and therefore I didn't find it helpful.

我目前的做法是为我编写的每个函数连接到每个 mysqli 的数据库，并在函数结束时断开连接，因为对我来说它看起来更干净.这样，如果页面不调用需要访问数据库的函数，则永远不会打开连接.但是，可能会发生每个页面加载最多大约 10 个连接的情况，具体取决于用户在站点上执行的操作.现在我认为这可能是资源的公平分配.如果我理解正确，则只能始终打开 1 个 DB 连接.因此，我假设所有连接请求都将排队.因此，如果用户有多个、长且复杂的查询，则该用户不会阻止所有流量，因为在每个查询之间，可能会处理其他短查询.但这只是我编造的东西，我不知道它是否真的会这样......:D

My current practice has been to connect to the DB per mysqli for each function I write, and disconnect at the end of the function, because it seemed cleaner to me. This way, if a page doesn't call to a function which requires DB access, there will never be a connection opened. However it may happen, that there might be up to approximately 10 connections per page load, depending on what the user does on the site. Now I thought this might be a fair distribution of resources. If I understood it correctly there can only always be 1 DB connection opened. Therefore I assume all connection requests will be queued. So if a user has multiple, long and complicated queries, this user would not hold up all traffic, because in between each of the queries, other short queries could get processed. But that's just me making stuff up, I don't know if it would really work that way... :D

我也知道这里的很多开发人员都喜欢使用 PDO.刚开始开发的时候选择了mysqli，暂时没有换的打算.我希望我的问题适用于两个图书馆.

Also I know that a lot of developers around here like to use PDO. I chose to use mysqli when I started developing, and I have no plans of switching. I hope my question can be applicable to both libraries.

谢谢:-)

推荐答案

通常，创建数据库连接的成本很高.这就是为什么大多数人建议创建一次连接并重复使用它直到执行停止，如果数据库客户端库允许，甚至更长时间.

Typically database connections are expensive to create. This is why most people recommend creating the connection once and reuse it until the execution has stopped, or even longer if the database client library allows it.

例如，PDO 允许创建持久连接，据说这可以提高性能，因为该连接将被重复用于连续处理多个请求.来自 http://php.net/manual/en/pdo.connections.php:

As an example, PDO permits creating persistent connections, which supposedly enhance performance because the connection would be reused for serving several requests in a row. From http://php.net/manual/en/pdo.connections.php:

许多 Web 应用程序将受益于与数据库服务器的持久连接.持久连接不会在脚本结束时关闭，而是在另一个脚本使用相同凭据请求连接时被缓存和重新使用.持久连接缓存允许您避免每次脚本需要与数据库通信时建立新连接的开销，从而使 Web 应用程序更快.

Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.

这篇关于什么更有效，为什么:每页一个数据库连接或每个函数一个数据库连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！