PHP mysql 选择统计引擎类型
我正在尝试为网络系统开发一个接口。基本上，一个软件通过 UDP 发送所有流量数据，另一个软件收集数据，并将数据插入 mysql（客户端没有 mysql 服务器，只有具有用户界面访问权限的服务器才有 mysql 服务器）。 大约有 50 个客户端，流量非常高，并且 mysql 服务器上的插入操作非常快。但我想知道性能问题，因为我打算使用推送服务器或其他东西实时打印统计数据，而且这些流量统计数据必须保存很长时间，那么我应该使用什么样的数据库引擎？ myisamm、innodb、存档？有人可以向我解释一下吗？我也愿意接受建议
I am trying to develop a interface for a networking system. Basically, a software puts all traffic datada through UDP and an other software collects, and inserts data into mysql (clients doesnt have mysql server, only server which has user interface access has mysql server).
there are about 50 clients has really high traffic and insert operation on mysql server is really fast. but i am wondering about performance issues couse i am planing to print stats in realtime with push server or something, also, these traffic stats must be saved for a long time, so what kind of database engine i should use? myisamm, innodb, archive? anybody can explain to me please? also i am open for suggestions too
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我认为您需要做的最重要的事情是建立一个可以进行一些测试并分析不同方法的环境。
对于您正在谈论的那种仅将行添加到表末尾并对其运行选择的情况，MyISAM 可能非常快 - 没有删除或更新。
您需要定期归档较旧的行，我相信归档存储引擎可能非常适合此目的，但我从未有机会使用它，也没有从经验中谈起。不过，我怀疑它是否可以用作应用程序的主表。您可以考虑某种分区，例如按月将结果存储在不同的表中。
《高性能 MySQL》一书可能是您的首选。
听起来像是一个宏伟的项目——祝你好运。
I think the most important thing you need to do is set up an environment where you can do some testing and profile different approaches.
MyISAM can be wicked fast for the sort of thing you're talking about where you're only adding rows onto the end of the table and running selects against it - no deletes or updates.
You'll need to archive older rows at regular intervals and I believe the archive storage engine might be a good fit for this, but I have never had occasion to use it and do not speak from experience there. I doubt it would be usable as the main table for the app, though. You could consider some sort of partitioning e.g. where you store results in different tables by month.
The book High Performance MySQL should probably be your first port of call.
Sounds like an imposing project - good luck with it.
以下是 innodb 和 myisam 的详细信息
由于表锁定和行锁定，myisam 更适合高读取量，innodb 更适合高更新量。
myisam 更适合高读取量，innodb
innodb 有日志记录，可以从 myisam 无法恢复的崩溃中恢复，就像 NTFS 与 FAT 文件系统一样。
myisam 有全文索引，innodb 没有。
innodb 有事务支持、提交和回滚，myisam 缺少这些。
因此，如果您并不真正关心更新和恢复表，并且不需要事务支持，则可以使用 MyISAM。为了解决大量数据的问题，您可以考虑使用 MySQL 分区，然后使用归档引擎定期将旧条目归档到备份表中。
如果您确实关心阅读速度，您也可以考虑延迟插入。
更新：
对于MyISAM表，如果数据文件中间没有空闲块，则支持并发的SELECT和INSERT语句。在这些情况下，您很少需要将 INSERT DELAYED 与 MyISAM 一起使用。
插入延迟
Here are the details of innodb and myisam
myisam is better for high read volumes, innodb for high update volumes due to table vs row locking.
innodb is journaled, and can recover from crashes where myisam can't, much like NTFS vs FAT file systems.
myisam has full-text indexing, innodb doesn't.
innodb has transaction support, commits and rollbacks, myisam lacks these.
so if you don't really care about updating and recovering tables, and don't need transaction support you can use MyISAM. To solve problem with large amount of data you can consider using MySQL partitions and then periodically archive older entries in backup tables with archive engine.
Also you can consider delayed inserts if you are really concerned about reading speed.
UPDATE :
For MyISAM tables, if there are no free blocks in the middle of the data file, concurrent SELECT and INSERT statements are supported. Under these circumstances, you very seldom need to use INSERT DELAYED with MyISAM.
insert-delayed
您应该建立每天更新的统计表，您可以查询所需的统计数据。
You should build statistic tables which are updated every day which you can query for the statistics required.