至尊拆分：一个SQLite数据库每用户 [英] Extreme Sharding: One SQLite Database Per User
问题描述
我工作的一个Web应用程序是电子邮件服务和社交网络之间的某处。我觉得它在未来的增长非常大的潜力，所以我很担心的可扩展性。
I'm working on a web app that is somewhere between an email service and a social network. I feel it has the potential to grow really big in the future, so I'm concerned about scalability.
而不是使用一个集中的MySQL / InnoDB的数据库，然后到那个时候它划分的，我已经决定为每个活动的用户一个单独的SQLite数据库：每'碎片'一个活动的用户
Instead of using one centralized MySQL/InnoDB database and then partitioning it when that time comes, I've decided to create a separate SQLite database for each active user: one active user per 'shard'.
这样，备份数据库将作为复制每个用户的小的数据库文件到远程位置，每日一次。
That way backing up the database would be as easy as copying each user's small database file to a remote location once a day.
向上扩展会增加额外的硬盘来存储新的文件一样简单。
Scaling up will be as easy as adding extra hard disks to store the new files.
当应用程序增长超过一台服务器，我可以在服务器上同时在文件系统级别使用GlusterFS链接并运行应用程序不变，或搭起一个简单的SQLite代理系统，让每台服务器来处理相邻的服务器sqlite的文件。
When the app grows beyond a single server I can link the servers together at the filesystem level using GlusterFS and run the app unchanged, or rig up a simple SQLite proxy system that will allow each server to manipulate sqlite files in adjacent servers.
并发性问题将是最小的，因为每个HTTP请求将只接触一种或两种数据库文件的时间，出千，和SQLite只有在块读取反正
Concurrency issues will be minimal because each HTTP request will only touch one or two database files at a time, out of thousands, and SQLite only blocks on reads anyway.
我敢打赌，这种做法将让我的应用程序优雅地扩展并支持大量的凉爽的唯一的功能。难道我错了赌注？我缺少什么？
I'm betting that this approach will allow my app to scale gracefully and support lots of cool and unique features. Am I betting wrong? Am I missing anything?
更新我决定去用那么极端的解决方案，这是工作的罚款至今。我使用的碎片固定数量 - 256 SQLite数据库，为precise。每个用户通过简单的散列函数分配和结合到一个随机碎片。
UPDATE I decided to go with a less extreme solution, which is working fine so far. I'm using a fixed number of shards - 256 sqlite databases, to be precise. Each user is assigned and bound to a random shard by a simple hash function.
我的应用程序的大部分功能需要访问每个请求只有一个或两个碎片，但有一个特别需要一个简单的查询在10至100个不同的碎片执行了256个，这取决于用户。测试表明它将采取约0.02秒或更小，如果所有的数据在RAM中缓存。我想我可以忍受！
Most features of my app require access to just one or two shards per request, but there is one in particular that requires the execution of a simple query on 10 to 100 different shards out of 256, depending on the user. Tests indicate it would take about 0.02 seconds, or less, if all the data is cached in RAM. I think I can live with that!
更新2.0 我移植应用到MySQL / InnoDB和能够得到关于常规要求相同的性能，但需要碎片步行一个要求，InnoDB的快4-5倍。出于这个原因和其他原因，我放弃这个架构，但我希望有人的地方发现了一个用不到它...感谢。
UPDATE 2.0 I ported the app to MySQL/InnoDB and was able to get about the same performance for regular requests, but for that one request that requires shard walking, innodb is 4-5 times faster. For this reason, and other reason, I'm dropping this architecture, but I hope someone somewhere finds a use for it...thanks.
推荐答案
在这里这将失败的地方是，如果你所要做的就是所谓的碎片行走 - 这是跨越了一堆不同用户的找出所有数据。这特定种类的查询将不得不编程方式，要求每个SQLite数据库的又 - 而且很可能是你的网站最慢的一个方面。这是在任何系统中一个共同的问题，即数据已经被分片成单独的数据库。
The place where this will fail is if you have to do what's called "shard walking" - which is finding out all the data across a bunch of different users. That particular kind of "query" will have to be done programmatically, asking each of the SQLite databases in turn - and will very likely be the slowest aspect of your site. It's a common issue in any system where data has been "sharded" into separate databases.
如果所有的数据是自包含给用户，那么这应该扩展pretty以及 - 关键使之成为一个有效的设计是要懂得数据很可能将被使用，如果数据从一个人将与来自另一数据（在上下文）进行交互。
If all the of the data is self-contained to the user, then this should scale pretty well - the key to making this an effective design is to know how the data is likely going to be used and if data from one person will be interacting with data from another (in your context).
您可能还需要注意文件系统资源 - SQLite是伟大的，真棒，快速，等等 - 但你用的是标准数据库，当得到一些缓存和写作的好处（如MySQL和PostgreSQL等），因为的他们是如何设计的。在你提出的设计，你会在一些被认为错过了。
You may also need to watch out for file system resources - SQLite is great, awesome, fast, etc - but you do get some caching and writing benefits when using a "standard database" (i.e. MySQL, PostgreSQL, etc) because of how they're designed. In your proposed design, you'll be missing out on some of that.
这篇关于至尊拆分：一个SQLite数据库每用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
