文件服务器vs数据库查询速度：百万级JSON数据快速检索方案选型

Hey Chris, great question—let’s break down which approach will deliver faster retrieval for your millions of email SHA256-to-JSON mappings.

Option 1: Split Large Database Table into Alphabetical Subtables #

• Pros: Splitting reduces the size of each individual table, so indexed queries on email_sha256 will target smaller datasets, cutting scan time compared to a single massive table. Most databases are optimized to handle indexed lookups on smaller tables efficiently.
• Cons: The database layer’s inherent overhead still applies—think SQL parsing, connection management, and even minor lock contention. Plus, if your SHA256 hashes aren’t evenly distributed across alphabetical ranges, some subtables could still grow unwieldy (e.g., if a huge chunk of hashes start with "a"), weakening the split’s benefits. You’ll also need to build and maintain logic to route queries to the correct subtable, adding code complexity.

Option 2: File-Based Storage (Each Hash as a Filename) #

• Pros: When implemented properly, this can be significantly faster than database-based approaches. Modern file systems (like ext4, XFS, or APFS) have optimized directory indexing, so looking up a file by name is nearly O(1)—as long as you avoid overcrowding single directories. Fix directory bloat by using a hierarchical structure: for example, use the first 2 characters of the SHA256 as a top-level directory, the next 2 as a subdirectory, then the full hash as the filename. This keeps directory sizes manageable. You also skip all database overhead—no SQL parsing, no connection pools, just direct OS-level file lookups. OS page caching will further speed up repeat retrievals by caching frequently accessed JSON content.
• Cons: Without proper directory splitting, a single directory holding millions of files will perform terribly—file systems aren’t built for that. Batch operations (like counting total entries or bulk updates) are far less convenient than with a database, but if your primary use case is fast single-key retrieval, this is a minor tradeoff.

Final Verdict #

If raw retrieval speed for individual SHA256 keys is your top priority, Option 2 (with hierarchical directory splitting) will outperform Option 1. The absence of database layer overhead and optimized file system indexing make it the faster choice for this specific use case. That said, if you need database-specific features (like transactions, complex queries, or built-in replication), Option 1 might still be worth considering—but purely for speed, file-based storage wins.

内容的提问来源于stack exchange，提问作者Chris