电子商务网站的数据库结构
我的任务是设计一个电子商务解决方案。给我带来最多问题的方面是数据库。
目前，该网站由 10 多个国家/地区的商店组成，每个商店都有自己的数据库（全部驻留在同一个 mysql 实例上）。
对于新网站，我希望将所有这些商店数据库合并到一个数据库中，以便所有表（产品、订单、客户等）都有一个 shop_id 字段。从编程的角度来看，这似乎最有意义，因为我们不必跨多个数据库管理数据。
目前，整个网站每年产生约 12 万个订单，但正在经历相当大的增长，我们需要设计一个可扩展的解决方案。 5 年内，每年可能会有超过 100 万个订单，并且数据库包含 5 年的订单历史记录（存档可能是这里的一个解决方案）。问题是 - 我们是否使用单个数据库，还是保留每个商店的数据库结构？
我目前正在努力寻找这两种途径的支持证据。我正在为其设计解决方案的公司更喜欢每个商店的数据库结构，因为他们相信这将允许网站扩展。但我的观点是，商店的数据库在未来几年内可能不会变得如此繁忙，以至于超出了 mysql 数据库和“不惜一切代价”的硬件设置的容量。
我想知道是否有人有任何建议？有人有过使用包含数百万条记录的表的网站/电子商务网站的经验吗？我知道这里可能没有明确的答案，但是在什么阶段我们有太多记录或太大的表文件而无法快速加载站点？
另外，如果有人对我可以做进一步研究的信息来源（书籍、网站等）有任何建议，我将不胜感激！
I have been tasked with designing an ecommerce solution. The aspect that is causing me the most problems is the database.
Currently the site consists of 10+ country based shops each with their own database (all residing on the same mysql instance).
For the new site I'd rather all these shop databases be merged into one database so that all tables (products, orders, customers etc.) have a shop_id field. From a programming perspective this seems to make the most sense as we won't have to manage data across multiple databases.
Currently the entire site generates about 120k orders a year, but is experiencing fairly heavy growth and we need to design a solution that will scale. In 5 years there may be more than a million orders per year and a database that contains 5 years order history (archiving maybe a solution here). The question is - do we use a single database, or do we keep the database-per-shop structure?
I am currently trying to find supporting evidence for either avenue. The company I am designing the solution for prefer the per-shop database structure because they believe it will allow the sites to scale. But my argument is that the shop's database probably won't get that busy over the next few years that they exceed the capacity of a mysql database and a "no expenses spared" hardware set-up.
I am wondering if anyone has any advice either way? Does anyone have experience with websites / ecommerce sites that have tables containing millions of records? I know there is probably not a clear answer here, but at what stage do we have too many records or too large table files to have a fast loading site?
Also, if anyone has any advice on sources of information - books, websites, etc. where I can do further research, it would be highly appreciated!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我已经实现了一个剧院门票销售解决方案，其中有包含数十万条记录的表，并且没有性能问题可言（硬件没什么特别的）。虽然我很难比较负载，但我认为数据量增加 10 倍不太可能显着影响性能。如果是相同的应用程序和相同的模式，我很可能倾向于使用单个中央数据库（可能具有故障转移），因为：
如果性能确实受到影响并且可能有许多其他原因， 拥有多个实例的明显优势是，您可以获得穷人的高可用性：如果一台服务器出现故障，则只有一个商店无法工作，并且您可以立即获得这种行为。
I've implemented a theater ticket sales solution which has tables with a couple of hundred thousand records and there are no performance issues to speak of (the hardware's nothing special). While it's hard for me to compare the loads, I would say it's unlikely that a 10x increase in data volume would noticeably impact performance. If it's the same application and the same schema, I'd very likely lean towards a single central database (probably with fail-over) because:
and probably a number of other reasons. The obvious advantage of having multiple instances is that you get poor-man's high availability: if one server is down, only one shop doesn't work and you get this behaviour out of the box.
我认为保留单独的数据库更容易。对这些没有直接关系的实体进行逻辑分离更有意义。扩展也将变得更加容易，以便每个站点都可以在单独的硬件上运行（如果/当时机成熟的话）。备份/恢复和一般维护过程在单独的实例上也将变得更加容易，因为它允许每个商店进行自定义过程。任何灾难场景也只会影响一个逻辑数据库，而不可能搞砸每一家商店。
您当前的提案意味着几乎每个表都需要一个“shop id”列，该列也被索引以防止冲突。稍后当您需要扩展时分离这些数据不会有太大问题，但重新编程很可能会非常耗时。
I would argue its easier to keep separate databases. It just makes more sense to have logical separation of these entities which have no direct relation. It will also be FAR easier to scale up, so that each site can run on separate hardware if/when the time comes. Backup/restore and general maintenance procedures will also be far easier on separate instances, because it allows for customised procedures per shop. Any disaster scenarios also only affect one logical database, rather than potentially screwing up every single shop.
Your current proposal will mean that just about every table will need a 'shop id' column, which is also indexed to prevent collisions. Separating this data later when you need to scale up wont be too much of an issue, but reprogramming most likely will be very time consuming.