使用MySQL数据库更改发送WebSocket更新的效率更高 [英] Which is more efficient to send WebSocket updates with a MySQL database change

问题描述

我目前正在尝试使用WebSockets，以减少/消除在潜在的低带宽环境中对恒定AJAX请求的需求.所有设备都兼容WebSocket，所以那里没有问题，我正在尝试将其保留在本机PHP WebSocket中，不使用node.js或其他框架/库(到目前为止还可以).

I'm currently experimenting with WebSockets in a bid to reduce / remove the need for constant AJAX requests in a potentially low bandwidth environment. All devices are WebSocket compliant so there's no issue there, and I'm trying to keep it to native PHP WebSockets, no node.js or other frameworks / libraries (Which so far has been fine).

我要做的是确定如何通知已连接的客户端有关另一客户端对数据库的更新.所讨论的用例是某人按下设备上的按钮，然后警告该按钮的人员管理器.因此，我有两个选择，如下所示:

What I'm looking to do is to decide how to go about notifying connected clients about an update to a database by another Client. The use case in question is a person pressing a button on their device, which then alerts that persons manager(s) to that press. So the two options I have though of are as follows:

1.循环数据库查询(PHP)

我的第一个想法是在WebSocket服务器中插入一个查询，该查询实际上是在说警报字段是否已更改?如果是，请通知管理员".尽管这是最直接，最明智的方法(我能想到)，但要设计一个旨在减轻服务器负担的PHP脚本似乎很浪费，但是现在每秒都要运行一次查询，但是，至少这可以确保当检测到数据库更新时，将发送更新.

My first thought was to insert a query into the WebSocket server that is effectively saying "Has the alert field changed? If so, notify the manager(s)". Whilst this is the most straightforward and sensible approach (That I can think of), it seems wasteful to have a PHP script designed to reduce strain on the server, that is now running a query every second, however, at least this would ensure that when a Database update is detected, the update is sent.

2.从客户端发送通知

我的另一种想法是，当客户端更新数据库时，他们实际上可以自行发送WebSocket通知.这具有减少任何密集查询和循环查询的优势，但也意味着我需要在每次每次我想更改任何数据的时间发送一次WebSocket消息，例如:

Another thought I had, was that when the client updates the Database, they could in fact send a WebSocket notification themself. This has the advantage of reducing any intensive and looped queries, but also means that I'd need to have a WebSocket message being sent every time I want to change any data, such as:

$.post("AttemptDatabaseUpdate.php", {Data}).function(Result) // Don't worry about the semantics of this, it's not actual code
{
    if(Result == "Successful")
    {
        SendWebSocketNotification(OtherData);
    }
}

也许这是最好的选择，因为它是最有效的，但是我担心在更新数据库和发送WebSocket通知之间连接可能会断开，这可能需要进行后备检查PHP文件，与第一个解决方案中的文件很像，只是间隔更长(每30秒说一次).

Maybe this is the best option, as it is the most efficient, but I worry that there is a chance the connection may drop between updating the Database, and sending the WebSocket notification, which may create a need for a fallback check in the PHP file, much like the one in the first solution, albeit at a longer interval (Say every 30 seconds).

3. MySQL触发器?

这纯粹是猜测，但也许另一个选择是创建MySQL触发器，该触发器可以以某种方式直接通知server.php文件?我不知道这将如何工作，并且可能会猜到这可能会导致与解决方案#1相同或相似的查询要求，但这只是一个问题...

This is purely a guess, but perhaps another option is to create a MySQL trigger, which can somehow notify the server.php file directly? I've no idea how this would work, and would hazard a guess that this may end up with the same or similar Query requirements as solution #1, but it's just a though...

在此先感谢您的帮助:)

Thank you in advance for your help :)

解决方案可能性4

事实上，另一个想法突然浮出水面，用于更新数据库的PHP文件实际上可以在其中内置WebSocket消息.这样，当PHP文件更新数据库时，会通过PHP通知WebSocket服务器，这可能吗?

Another thought has just popped into my head in fact, whereby the PHP file used to update the database could in fact have a WebSocket message built into it. So that when the PHP file updates the database, the WebSocket server is notified via PHP, is this possible?

推荐答案

如果使用websocket，则应使用来自客户端的通知.这是他们的主要用例之一.

If you use websockets, you should use notifications from client. That's one of their main use cases.

如果您担心由于连接断开或之间的变化而导致的不一致，则可以实施类似于

If you're worried about inconsistencies due to connection dropping or something changing in-between, you could implement a system similar to HTTP ETags, where client would send a hash code that you can respond on server side if there is a conflict in updating.

更新:我想我理解您最初的问题有点不对.如果我正确理解了您的用例:您正在从客户端发送数据库更新，然后需要更新所有连接的客户端.在那种情况下，我认为服务器应该在数据库更新完成后发送更新消息，因此我同意解决方案4.在这里，我假设您的websocket服务器与运行PHP并进行数据库更新的服务器相同.

Update: I guess I understood your initial issue a bit wrong. If I understand your use case correctly: you are sending database updates from a client and after that all connected clients need to be updated. In that case, I think server should send the update messages after DB updates have been done, so I agree with solution 4. I am assuming here that your websocket server is the same server running PHP and doing the DB updates.

但是，根据您的用例，客户端仍应在下一个请求上发送一个散列值，以标识其世界观"，因此，如果连接断开，您将不会多次执行相同的更新.

However, depending on your use case, client should still send a hash value on the next request identifying its "view of the world", so you would not be doing identical updates multiple times if a connection gets broken.

更新2:所以现在可以理解，您确实使用了单独的独立websocket服务器.基本上，您在服务器端有两个不同的Web服务器，并且在如何在两者之间进行通信方面存在问题.这是一个实际问题，我建议一次只使用一台服务器-要么使用 Apache websocket支持(实验性的，不推荐使用)或将您的php脚本迁移到websocket实例.

Update 2: so it was now understood that you indeed use a separate, standalone websocket server. Basically you have two different web servers on the server side and are having an issue on how to communicate between the two. This is a real issue, and I'd recommend only using one server at a time - either take a look at using Apache websocket support (experimental and not really recommended) or migrating your php scripts to the websocket instance.

PHP或Apache都不是真正在考虑websockets的.仅使用PHP设置独立的Websocket服务器是很容易的，但是如果代码依赖于Apache/Web服务器，那么将其余的PHP堆栈迁移到其中可能并不那么容易. Apache websocket支持也不是最佳选择.不幸的是，对于真正的websocket解决方案，最佳实践将是使用从头开始为它构建的技术.

Neither PHP or Apache was really build with websockets in mind. It is quite easy to set up a standalone websocket server using only PHP, but it might not be so easy then to migrate the rest of the PHP stack to it if the code is relying on Apache/web server on. Apache websocket support also is hardly optimal. For a real websocket solution, unfortunately, best practice would be using a technology that is built for it from the ground up.

这篇关于使用MySQL数据库更改发送WebSocket更新的效率更高的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！