什么效率更高?为什么:每页一个db连接或每个功能一个db连接? [英] whats more efficient and why: one db connection per page or one db connection per function?

问题描述

我正在一个非常由MySQL DB驱动的网站上工作.因此，我正在进行很多查询.

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

在这个主题，每个人都建议在页面顶部连接到数据库，在页面底部断开连接.

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.

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