每个客户端多个数据库与一个数据库的实用性 [英] Practicality of multiple databases per client vs one database

问题描述

我将尝试在涵盖所有要点的同时尽可能简短 - 我目前是一名 PHP/MySQL 开发人员.我和一个朋友有一个移动应用的想法，我们将开始开发它.

I'm going to try to make this as brief as possible while covering all points - I work as a PHP/MySQL developer currently. I have a mobile app idea with a friend and we're going to start developing it.

我并不是说它会很棒，但如果它流行起来，我们将拥有大量数据.

I'm not saying it's going to be fantastic, but if it catches on, we're going to have a LOT of data.

例如，我们有客户"，因为没有更好的术语，他们会列出 100 到 250,000 种产品".假设最好，我们可以拥有数百个客户.

For example, we'd have "clients," for lack of a better term, who would have anywhere from 100-250,000 "products" listed. Assuming the best, we could have hundreds of clients.

客户端将通过网络界面编辑数据，移动界面将调用网络服务器并返回 JSON(可能).

The client would edit data through a web interface, the mobile interface would just make calls to the web server and return JSON (probably).

我是一个低级的 cms 开发人员，所以我不知道如何处理这个问题.我的问题或多或少是关于性能的；我在 MySQL 表中见过的最多的是 340k，而且它已经有点慢了(当然它也不是最好的服务器).

I'm a lowly cms-developing kinda guy, so I'm not sure how to handle this. My question is more or less about performance; the most I've ever seen in a MySQL table was 340k, and it was already sort of slow (granted it wasn't the best server either).

我无法理解一个有 4000 万行(并且有可能持续增长)运行良好的表.

I just can't fathom a table with 40 million rows (and potential to continually grow) running well.

我的计划是拥有一个包含真实"数据库名称的核心"数据库，这样用户就可以进入并尝试访问客户的数据，它会转到核心数据库并找出哪个数据库从中获取信息.我不关心数据分离或数据安全(不是私人信息)

My plan was to have a "core" database that held the name of the "real" database, so the user would come in and try to access a client's data, it would go to the core database and figure out which database to get the information from. I'm not concerned with data separation or data security (it's not private information)

推荐答案

是的，这是可能的，我的公司做到了.不过，我当然不会说它很聪明.我们有一个SAAS营销自动化系统.一些客户的数据库有 100 万条以上的记录.我们处理第二个公共"数据库，它有一个履行"表，跟踪电子邮件、信件、电话等，有超过 400 万条记录，以及许多其他非常大的共享表.通过适当的索引、优化、维护单独的仅限数据库的服务器，以及可能的集群(我们还不必这样做)，您可以处理大量数据……在许多情况下，那些认为可以的人只处理几十万条记录工作在竞争产品上为生.如果您仍然怀疑它是否有效，请考虑根据 MySQL 的集群指标，一个 8 服务器集群每秒可以处理 250 万次更新.一点都不寒酸.....

Yes, it's possible and my company does it. I'm certainly not going to say it's smart, though. We have a SAAS marketing automation system. Some client's databases have 1 million+ records. We deal with a second "common" database that has a "fulfillment" table tracking emails, letters, phone calls, etc with over 4 million records, plus numerous other very large shared tables. With proper indexing, optimizing, maintaining a separate DB-only server, and possibly clustering (which we don't yet have to do) you can handle a LOT of data......in many cases, those who think it can only handle a few hundred thousand records work on a competing product for a living. If you still doubt whether it's valid, consider that per MySQL's clustering metrics, an 8 server cluster can handle 2.5million updates PER SECOND. Not too shabby at all.....

使用两个数据库的问题是处理多个连接.很难吗?不，不是真的.您可以根据所需的数据库创建不同的对象并引用连接类.在我们的例子中，我们点击主数据库的公司类来推断客户端数据库名称，然后基于它构建第二个连接.但是，当您来回处理这些连接时，您可能会遇到需要额外调试的错误.这不仅仅是我的查询有效吗?"但是我实际上获得了正确的数据库连接吗?"在我们的例子中，丢弃的会话会导致各种 PDO 错误触发，因为系统无法再跟踪要访问的客户端数据库.另外，从可维护性的角度来看，尝试将表结构更新推送到 100 个不同的实时数据库是一个可怕的过程.是的，它可以自动化.但是一个失误，你已经击倒了很多人，并为自己做了大量的额外工作.现在，计算处理连接和推送更新所需的额外开发和测试......这将是您衡量是否值得的标准.

The problem with using two databases is juggling multiple connections. Is it tough? No, not really. You create different objects and reference your connection classes based on which database you want. In our case, we hit the main database's company class to deduce the client db name and then build the second connection based on that. But, when you're juggling those connections back and forth you can run into errors that require extra debugging. It's not just "Is my query valid?" but "Am I actually getting the correct database connection?" In our case, a dropped session can cause all sorts of PDO errors to fire because the system no longer can keep track of which client database to access. Plus, from a maintainability standpoint, it's a scary process trying to push table structure updates to 100 different live database. Yes, it can be automated. But one slip up and you've knocked a LOT of people down and made a ton of extra work for yourself. Now, calculate the extra development and testing required to juggle connections and push updates....that will be your measure of whether it's worthwhile.

我的推荐?找到一个允许您将两台机器放在同一个本地网络上的主机.我们选择了 Linode，但您使用的是谁无关紧要.从您的专用数据库服务器开始，在必要时提前计划进行集群.将所有内容保存在一个数据库中，虔诚地索引和优化.最后，找到一个非常好的 DB 人并善待他.有了这么多数据，一个优秀的 DBA 必不可少.

My recommendation? Find a host that allows you to put two machines on the same local network. We chose Linode, but who you use is irrelevant. Start out with your dedicated database server, plan ahead to do clustering when it's necessary. Keep all your content in one DB, index and optimize religiously. Finally, find a REALLY good DB guy and treat him well. With that much data, a great DBA would be a must.

这篇关于每个客户端多个数据库与一个数据库的实用性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！