web实时读取mysql,如何使用websockets从mysql获取实时通知更新？

From few days I was searching how social network or Q/A websites get live notifications. And I came to know about the subscriber-publisher pattern. I came to know that the live updates are fetched using the WebSockets. Websocket publish an endpoint and the client subscribe to that endpoint and constantly get updates for any changes.

But the examples I got from the web are all of chat applications. But my requirement is to get live notifications from MySQL DB. So I have few questions in mind

Am I using the WebSockets for the correct requirement? Or there is

some other efficient way for my requirement?

If this is the efficient way for me, the pattern what I think to resolve this is: The WebSocket will be connected to the MySql and always looks for the changes. And It also publishes an endpoint to which my client will always look for. Any changes committed to Mysql will be reflected in WebSocket and resulting in the client application.

If my pattern is right, I don't know how can I connect the WebSocket to Mysql. Any help/guidance is highly appreciated.

update:

After some more web searching:

1. Since I am creating the Rest Webservices for my website, is AJAX the better way to implement notification feature instead of WebSockets?

2. Are WebSockets complex to implement than AJAX calls hitting Rest endpoint ( since the purpose of both is same, to get notifications)?

解决方案

Am I using the WebSockets for the correct requirement?

Live notifications is where Websockets thrive and provide a huge advantage over AJAX.

As you know, this was already discussed before both when debating the role of AJAX (great for CRUD, not so much when polling) and when comparing Websocket performance vs. AJAX performance (Websockets are always faster where live updates are concerned).

Or there is some other efficient way for my requirement?

Yes... you could save resources and improve performance (as well as future code maintenance issues) by adding on_update "hooks" to the database access point.

The idea is simple: whenever a function call updates the MySQL database, the update request is also sent to a callback. That callback is in charge of publishing the update to the correct channel.

This way, you don't poll the MySQL database.

Some databases offer update callbacks and others don't. I think MySQL does. However, I avoid these database linked callbacks because they are database specific. It's better (IMHO) to add the callback to the database access point in the application, so replacing a database doesn't effect the code-base.

Since I am creating the Rest Webservices for my website, is AJAX the better way to implement notification feature instead of WebSockets?

I don't think AJAX is a good approach.

HTTP/2 helps to mitigate the AJAX shortcomings, but it doesn't solve all of them.

I don't know how many clients you expect to be concurrently connected, but forcing a client to send a request every second or two is very close to a self inflicted DoS attack.

Consider this: if a client sends an AJAX request every two seconds, than at 2,000 concurrent clients, your server will need to respond to 1,000 req/sec - these include authentication, database queries and all that jazz.

On the other hand, using Websockets, with 2,000 connected clients, you have 2,000 persistent connections doing nothing until a message arrives. No CPU or work required, just the connection's memory. There's no stress on the server until actual data is pushed.

Are WebSockets complex to implement than AJAX calls hitting Rest endpoint (since the purpose of both is same, to get notifications)?

Yes, they are more complex to implement, but they aren't that hard once you start. Also, there's a lot of libraries and helper tools that take much of the work off your shoulders.

Common issues related to the Websocket approach include the handling of the horizontal scaling (often by adding a pub/sub database or service, such as Redis), message ordering (which is better ignored when possible) and data propagation concerns (when do we mark data as "seen"? do we send the whole data or just a notification stating that data is available? how many channels do we use and how do we divide subscriptions?).

Usually the answers are application specific and depend on the feature you're trying to unroll as well as the expected size of your dataset (if every answer I gave on SO was a channel, it would be unrealistic to maintain).

Anyway... Good Luck!