如何使 javascript/php 聊天室在加载时间和 SQL 通信方面更加高效
现在，我的 javascript 聊天设置有效，所以就像
function getNewMessage()
{
//code would go here to get new messages
getNewMessages();
}
getNewMessages();
在函数中，我将使用 JQuery 进行 get post 以从 php scrip 检索消息，这将 1. 启动SQL连接 2.通过SQL验证是否是合法用户 3.仅检索自上次用户访问以来的新消息 4. 关闭 SQL
这效果很好，聊天也完美。我担心的是这会打开和关闭大量 SQL 连接。它非常快，但我现在想做一个小型的 javascript 多人游戏，每秒 3 次传输用户坐标以及数十个其他变量，每次打开和关闭 sql 连接并提取信息每次从多个表中处理可能不够高效而无法顺利运行，并且也可能对服务器造成太大压力。
有没有更好更有效的方式来传达我应该知道的所有这些变量，这对我的服务器/数据库来说并不那么困难？
Right now the setup for my javascript chat works so it's like
function getNewMessage()
{
//code would go here to get new messages
getNewMessages();
}
getNewMessages();
And within the function I would use JQuery to make a get post to retrieve the messages from a php scrip which would
1. Start SQL connection
2. Validate that it's a legit user through SQL
3. retrieve only new message since the last user visit
4. close SQL
This works great and the chat works perfectly. My concern is that this is opening and closing a LOT of SQL connections. It's quite fast, but I'd like to make a small javascript multiplayer game now, and transferring user coordinates as well as the tens of other variables 3 times a second in which I'm opening and closing the sql connection each time and pulling information from numerous tables each time might not be efficient enough to run smoothly, and might be too much strain on the server too.
Is there any better more efficient way of communicating all these variables that I should know about which isn't so hard on my server/database?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
不要使用持久连接，除非它是您唯一可用的解决方案！
当MySQL检测到连接已被删除时，所有临时表都会被删除，所有活动事务都会被回滚，并且所有锁定的表都会被解锁。持久连接仅在 Apache 子进程退出时断开，而不是在脚本结束时断开，即使脚本崩溃！您可以在事务中间继承连接。更糟糕的是，其他请求可能会阻塞，等待这些表解锁，这可能需要相当长的时间。
除非您测量了连接所需的时间并确定它占脚本运行时间的很大一部分，否则您不应考虑使用持久连接。事实上，如果您担心性能，那么您应该在这里执行此操作。查看 xhprof 或 xdebug，分析您的代码，然后开始优化。
Don't use persistent connections unless it's the only solution available to you!
When MySQL detects the connection has been dropped, any temporary tables are dropped, any active transaction is rolled back, and any locked tables are unlocked. Persistent connections only drop when the Apache child exits, not when your script ends, even if the script crashes! You could inherit a connection in the middle of a transaction. Worse, other requests could block, waiting for those tables to unlock, which may take quite a long time.
Unless you have measured how long it takes to connect and identified it as a very large percentage of your script's run time, you should not consider using persistent connections. In fact, that should be what you do here, if you're worried about performance. Check out xhprof or xdebug, profile your code, then start optimizing.
也许尝试使用不同的方法从服务器获取新消息： Comet 。
使用这种技术，您不必打开那么多新连接。
Maybe try to use a different approach to get the new messages from the server: Comet.
Using this technique you do not have to open that much new connections.
http://www.php.net/manual/en/features。 persist-connections.php
和
http://www. php.net/manual/en/function.mysql-pconnect.php
http://www.php.net/manual/en/features.persistent-connections.php
and
http://www.php.net/manual/en/function.mysql-pconnect.php
如果您有高效的 SQL 语句，几十个玩家同时进行不会损害数据库或导致明显的延迟。您的数据库可能会托管在与您的游戏或网站相同的服务器或至少相同的网络上，所以不用担心。如果您的数据库恰好托管在一台单独的服务器上，该服务器运行一个装有 MSDOS 的 8 位 16mz 板，位于远程亚马逊，通过无线电波连接到由醉酒猴子操作的曲柄发电机，那么您就在你自己的这个。
否则，您实际上应该更担心到底有多少数据来回传递给玩家。如果您要来回传递整个世界中所有对象的坐标，则页面加载可能会花费非常长的时间，即使数据库查询只需要几分之一秒的时间。有时，在游戏中可以通过“战争迷雾”功能来克服这一问题，该功能不会通知用户整个地图中的每个对象，而只会通知玩家直接范围内的对象。这可以通过单个 SQL 查询轻松完成，其中对象坐标靠近玩家。不过，如果您有一个小气的主机，他们会关心连接和查询的数量。
如果您担心吸引更多玩家，请考虑探索缓存方法，例如使用
fopen()、fgets()、fclose()等。或者，使用apc等 php 扩展将值存储在内存中，这些值在页面加载期间持续存在。memcache或memcached也有类似的作用，但其作用就像一个可以连接到的单独服务器，存储可以与其他页面点击和查询共享的值。要在您认为缓存的页面或值可能变得过时时更新它们，您可以经常运行 cron 作业来更新这些文件或值。如果您的主机不允许 cron 作业，请考虑让您的来宾执行此跑腿工作：特定页面上的一行脚本将在一定数量的页面点击后使用数据库查询中的新值刷新缓存。或者缓存一个日期值以在每个页面点击时进行检查，如果已经过去了很长时间，请刷新缓存。
再说一遍，除非您受到吝啬主机的压迫，或者除非您一次获得一百个或更多页面点击，否则甚至不需要担心您的数据库。数据库并没有那么脆弱。如果每当有不止一个问题出现时，他们就歇斯底里地哭起来，那么制造这个问题的工程师就不会失业很长时间了。
A couple of dozen players at the same time won't hurt the database or cause noticeable lag if you have efficient SQL statements. Likely your database will be hosted on the same server or at least the same network as your game or site, so no worries. If your DB happens to be hosted on a separate server running an 8-bit 16mz board loaded with MSDOS, located in the remote Amazon, connected by radio waves hooked up to a crank-powered generator operatated by a drunk monkey, you're on your own with this one.
Otherwise, really you should be more worried about exactly how much data you're passing back and forth to your players. If you're passing back and forth coordinates for all objects in an entire world, page load could take a painfully long time, even though the DB query takes a fraction of a second. This is sometimes overcome in games by a "fog of war" feature which doesn't bother notifying the user of every single object in the entire map, only those which are in immediate range of the player. This can easily be done with a single SQL query where object coordinates are in proximity to a player. Though, if you have a stingy host, they will care about the number of connects and queries.
If you're concerned about attracting even more players than that, consider exploring cache methods like pre-building short files storing commonly fetched records or values using
fopen(),fgets(),fclose(), etc. Or, use php extensions likeapcto store values in memory which persist from page load to page load.memcacheormemcachedalso act similarly, but in a way which acts like a separate server you can connect to, store values which can be shared with other page hits, and query.To update cached pages or values when you think they might become stale, you can run a cron job every so often to update these files or values. If your host doesn't allow cron jobs, consider making your guests do that legwork: a line of script on a certain page will refresh the cache with new values from a database query after a certain number of page hits. Or cache a date value to check against on every page hit, and if so much time has passed, refresh the cache.
Again, unless you're under the oppressive thumb of a stingy host, or unless you're getting a hundred or more page hits at a time, no need to even be concerned about your database. Databases are not that fragile. If they crashed in a hysterical fit of tears anytime more than one query came their way, the engineers who made it wouldn't have a job for very long.
我知道这是一个相当烦人的“答案”，但也许您应该以不同的方式思考这个问题，毕竟这实际上并不是关系数据库的最强用途。您考虑过 XMPP 解决方案吗？在我看来，这将是完成这项工作的最佳工具，而且现在 ejabberd 和 openfire 的设置都很简单。优秀的 Strope 库可以使前端故事变得简单，并且作为额外的好处，您可以获得 HTTP 绑定（如 commet），因此您不需要轮询服务器，您的延迟将会下降，并且您将生成更少的 HTTP 流量。
我知道你不太可能改变你的整个方法，因为我就是这么说的，但我想提供一个替代的视角。
http://www.ejabberd.im/
http://code.stanziq.com/strope/
I know this is quite an annoying "answer" but perhaps you should be thinking about this a different way, after all this is really not the strongest use of a relational database. Have you considered an XMPP solution? IMO this would be the best tool for the job and both ejabberd and openfire are trivial to set up these days. The excellent Strophe library can make the front end story easy, and as an added bonus you get HTTP binding (like commet) so you won't need to poll the server, your latency will go down and you'll be generating less HTTP traffic.
I know it's highly unlikely you're going to change your whole approach just cos I said so, but wanted to provide an alternative perspective.
http://www.ejabberd.im/
http://code.stanziq.com/strophe/