English
A database is a system for storing, organizing, and retrieving structured information, with guarantees about what happens when multiple users access the same data simultaneously and when hardware or software fails. The guarantees are the interesting part. Any file can store data; what distinguishes a database management system is that it maintains the data’s correctness through crashes, prevents concurrent operations from corrupting each other, and provides query facilities that retrieve exactly what is requested without requiring the application to know how the data is physically stored.
The two foundational concepts are the transaction and the query. A transaction is a sequence of operations that executes atomically — it either completes entirely or has no effect at all — even if the system crashes in the middle, and even if hundreds of other transactions are running simultaneously. This is not easy to implement; it requires write-ahead logging, multi-version concurrency control, and careful recovery procedures. A query is a declarative specification of what data is desired, expressed without specifying how to retrieve it. The database system figures out the most efficient retrieval plan given the current state of the data, the available indexes, and system resources. This separation of what from how — one of the most productive abstractions in computer science — allows the same query to perform correctly as data volumes grow and as underlying hardware changes, without rewriting application code.
The relational model, proposed by Edgar Codd in 1970, provides the mathematical foundation for both. Data is organized into relations (tables), operations are specified in relational algebra or its SQL equivalent, and the model’s formal properties guarantee that queries have well-defined semantics and that the results are composable. The model is now fifty-five years old, has been refined by decades of theory and engineering, and remains the organizing principle of the majority of the world’s data management infrastructure.
Prerequisites: Operating systems (§4.2) — storage engines interact deeply with the file system and OS memory management. Algorithms (§2.6) — query processing, indexing, and concurrency control involve substantial algorithmic content. Discrete mathematics (§2.2) — the relational model has a formal mathematical foundation in set theory and first-order logic.
From Hierarchical Files to the Relational Model and Beyond
Data management before databases was file management: programs maintained their own files in whatever format they found convenient, with no shared access, no formal query facility, and no guarantee about what happened during a crash. This worked until data became shared — until multiple programs needed the same information, until multiple users needed to access it simultaneously, until the data became valuable enough that losing it during a power failure became unacceptable.
IBM’s Information Management System (IMS), introduced in 1966 for the Apollo program’s parts inventory, was one of the first database systems to address these problems seriously. IMS organized data in a hierarchical structure — records containing child records, like a tree — and provided navigational access: to find data, a program traversed the tree from root to the desired leaf. IMS solved the immediate problems of shared access and crash recovery, but hierarchical navigation made queries difficult to write and changes to the data structure expensive, because application code was tightly coupled to the physical layout.
Edgar Codd’s 1970 paper “A Relational Model of Data for Large Shared Data Banks” proposed a radical alternative. Codd, working at IBM, observed that hierarchical and network models forced applications to navigate physical data structures — essentially writing the access strategy into the application. He proposed instead a model based on mathematical relations: data as sets of tuples, operations defined by relational algebra, and a clean separation between the logical view (what the data means) and the physical view (how it is stored). A query specifies the data wanted, not how to find it; the database system is responsible for finding it efficiently. The paper is one of the most consequential in computer science, and it is short enough to read in an afternoon.
The system that proved Codd’s model feasible in practice came from two competing research projects in the mid-1970s. IBM’s System R, developed at the San Jose Research Laboratory between 1974 and 1979, implemented the relational model and developed SQL as its query language. Concurrently, Michael Stonebraker and Eugene Wong’s Ingres project at Berkeley implemented an alternative system with its own query language (QUEL) that was in some ways more theoretically clean. The competition between System R and Ingres shaped the field: System R produced SQL (which became the standard), and Ingres produced the research tradition that eventually led to PostgreSQL. System R also produced the first serious work on query optimization — how to automatically translate SQL into efficient execution plans — and on transaction processing, where Jim Gray’s contributions established the theoretical and practical foundations of ACID transactions that every subsequent database system has built on.
The storage structures that underlie database performance were developed in parallel. Rudolf Bayer and Edward McCreight invented the B-tree in 1972, a self-balancing tree structure designed specifically for block-oriented storage where the cost of accessing a disk block dominates the cost of computation within a block. The B-tree keeps data sorted and allows search, insertion, and deletion in O(log n) time even as the data grows to billions of records — and, critically, it performs these operations in a way that minimizes disk accesses. B-trees and their variants (B+ trees, which store all data in leaves, are now more common) are the dominant index structure in relational databases and have been for fifty years. Understanding the B-tree is understanding the foundation on which most database performance rests.
The commercial database market coalesced in the early 1980s around the relational model. Oracle, founded in 1977, shipped its first commercial SQL database in 1979 and became the dominant commercial database vendor. IBM’s DB2 followed. Sybase, Informix, and others competed in the market. By the late 1980s, the relational database had become the standard infrastructure for enterprise data management, and SQL had become the dominant query language despite its many departures from the relational model as Codd had envisioned it.
The Internet created a scale challenge that conventional database systems had not anticipated. By the early 2000s, companies like Google, Amazon, and Facebook were operating at scales where a single relational database — however powerful — could not hold all their data or serve all their users. The response was partitioning: distributing data across many machines. This immediately confronted a fundamental limit: in 2000, Eric Brewer conjectured, and Seth Gilbert and Nancy Lynch proved in 2002, what became known as the CAP theorem. A distributed system can have at most two of three properties: Consistency (all nodes see the same data), Availability (every request receives a response), and Partition tolerance (the system continues operating when network partitions occur). Since partition tolerance is necessary in any real distributed system, the choice is between consistency and availability during partitions.
Google’s internal systems, particularly the Bigtable (2006) and Chubby distributed lock service, chose partition tolerance and availability over strict consistency, accepting that different replicas might temporarily show different data. Amazon’s Dynamo (2007), which underlie AWS services including DynamoDB, made similar choices, popularizing the concept of eventual consistency — replicas converge to the same state eventually, without guaranteeing when. These papers triggered a wave of “NoSQL” database development: Cassandra (developed at Facebook, open-sourced 2008), MongoDB (2009), and many others, each trading some relational properties for scalability, flexibility, or operational simplicity. The NoSQL movement expanded the design space and introduced useful systems, but it also produced significant confusion about what was being traded away and why.
The return swing came from an unexpected direction. Google’s Spanner (2012) demonstrated that global, geo-distributed, strongly-consistent transactions were achievable at scale — contradicting the widespread assumption that CAP made strong consistency impossible for distributed systems. Spanner used synchronized clocks (TrueTime, relying on GPS and atomic clocks) and Paxos consensus to achieve external consistency — a property stronger than serializability — across data centers on different continents. The CockroachDB and YugabyteDB projects made Spanner-inspired designs available as open-source systems. These “NewSQL” systems demonstrated that the NoSQL wave’s rejection of transactions was an engineering choice, not a fundamental necessity.
The contemporary database landscape is the result of fifty-five years of this history. Relational databases (PostgreSQL, MySQL, SQL Server, Oracle) still handle the majority of the world’s transactional data. Column-oriented analytical databases (Snowflake, BigQuery, Redshift, ClickHouse) have transformed large-scale data analysis. Time-series databases (InfluxDB, TimescaleDB), graph databases (Neo4j), search engines (Elasticsearch), key-value stores (Redis), and vector databases (Pinecone, pgvector) serve specialized workloads. The appropriate response to this diversity is not to pick one system as universally correct but to understand the fundamental design dimensions — consistency vs. availability, read vs. write optimization, transactional vs. analytical, row vs. column storage — that determine which system suits which workload.
The Relational Model, Transactions, and Storage
The Relational Model and Query Processing
The relational model represents data as named relations (tables), each consisting of a set of tuples with a fixed schema. Operations are defined by relational algebra: selection (filtering rows), projection (selecting columns), join (combining related data from multiple relations), union, intersection, and difference. Relational algebra is closed — every operation produces a relation — which enables composition: the output of one operation can be the input to another. SQL is a declarative language over this algebra, allowing users to express queries without specifying the evaluation strategy.
Query optimization — translating a SQL query into an efficient execution plan — is one of the intellectually demanding problems in database systems. The optimizer must enumerate possible execution strategies (which join algorithm to use, in what order to join tables, whether to use indexes), estimate the cost of each strategy using statistical information about the data (table sizes, value distributions, correlation between columns), and select the plan with minimum estimated cost. The search space grows exponentially with the number of tables in a query, so practical optimizers use heuristics to prune the search. The cost estimates are imprecise, and the optimizer makes mistakes — particularly for complex queries with many joins — that result in plans orders of magnitude slower than optimal. Understanding how the optimizer works, what statistics it uses, and how to guide it with hints, index creation, or query rewriting is one of the most practically valuable database skills.
Indexes accelerate data access by maintaining auxiliary structures that allow fast lookup by specific attributes. The B+ tree index, built on an attribute, stores attribute values in sorted order with pointers to the actual tuples, supporting both exact-match queries and range queries. The hash index supports exact-match queries faster than B+ trees but cannot answer range queries. Composite indexes — built on multiple attributes together — can accelerate queries that filter on all indexed attributes but may not help queries that filter on only some. Covering indexes include all columns needed by a query in the index itself, allowing the query to be answered from the index without accessing the underlying table. Index selection — deciding which indexes to create — is a significant practical decision that affects query performance by orders of magnitude.
Transactions: ACID and Concurrency Control
A transaction groups a set of operations into a single atomic unit. The ACID properties specify what this means precisely. Atomicity: the transaction either completes entirely or has no effect. Consistency: the transaction takes the database from one valid state to another valid state, preserving all defined invariants. Isolation: concurrent transactions execute as if they ran serially, without seeing each other’s intermediate state. Durability: once committed, the transaction’s effects survive any subsequent failure.
Providing these properties simultaneously, to multiple concurrent transactions, without requiring them to execute serially (which would eliminate concurrency and thus performance), is the central challenge of transaction management. Concurrency control protocols manage the interleaving of concurrent transactions. Two-phase locking (2PL) acquires locks before accessing data and holds all locks until the transaction commits or aborts; it guarantees serializability but can cause deadlock. Multi-version concurrency control (MVCC) maintains multiple versions of each data item, allowing readers to access the version current at their transaction’s start time without blocking writers; this eliminates read-write conflicts and improves concurrency, at the cost of maintaining and eventually garbage-collecting old versions. Snapshot isolation — each transaction sees a consistent snapshot of the database as of its start time — is the default isolation level in many modern databases; it avoids most anomalies but is not full serializability and permits specific anomalies like write skew.
The isolation levels defined by the SQL standard (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) provide different tradeoffs between anomaly prevention and performance. Most applications use READ COMMITTED or REPEATABLE READ in practice; SERIALIZABLE is rarely used despite being the strongest guarantee, because of its performance cost. Understanding the specific anomalies each level permits — dirty reads, non-repeatable reads, phantom reads, write skew — is essential for reasoning about whether an application’s correctness depends on isolation guarantees the database actually provides.
Write-ahead logging (WAL) implements atomicity and durability. Every modification is written to the log before being applied to the data pages. If the system crashes, the log records what changes were in flight; the recovery process replays committed transactions and rolls back uncommitted ones, restoring the database to a consistent state. The ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) protocol, developed at IBM in the late 1980s, provides the standard recovery algorithm used in most production databases. Its three phases — Analysis (finding the state at crash time from the log), Redo (replaying all logged changes), Undo (rolling back uncommitted transactions) — guarantee recovery to a consistent state regardless of when the crash occurred.
Storage: B-Trees, LSM-Trees, and the Column Revolution
Storage engine design determines which workloads a database handles efficiently. The choice of data structure encodes assumptions about read-to-write ratio, key distribution, required operation types, and acceptable tradeoffs between read and write amplification.
B+ trees store data in sorted order across a balanced tree, with all data in leaf nodes and all internal nodes used only for navigation. Search, insertion, and deletion all take O(log n) time. Updates modify the data in place: finding the relevant leaf page, changing the data, and marking the page dirty. This design optimizes for read performance — finding data requires few pages — at the cost of random writes, which are expensive on spinning disks and cause write amplification on SSDs.
Log-Structured Merge Trees (LSM-trees), introduced by O’Neil et al. in 1996 and popularized by Google’s LevelDB and RocksDB, take the opposite approach: all writes are sequential. Incoming writes are buffered in an in-memory structure (the MemTable) and written to disk as immutable sorted runs (SSTables) when the MemTable fills. Background compaction merges SSTable runs, maintaining sorted order and removing deleted entries. LSM-trees achieve high write throughput because all disk writes are sequential, at the cost of read amplification (a key might be in any of several levels of SSTable) and compaction overhead. LSM-trees dominate write-heavy workloads: Apache Cassandra, RocksDB, LevelDB, and many others use them.
Column-oriented storage, used in analytical databases (Snowflake, BigQuery, ClickHouse, Redshift), stores each column of a table separately rather than storing entire rows together. Analytical queries typically scan a small number of columns across many rows — computing the average sales amount for orders in a date range requires reading only the amount and date columns, not the customer address, order description, or other columns. Column storage makes this efficient: only the relevant columns are read from disk. Column values are also highly compressible (a column of product categories has few distinct values, compressing with simple dictionary encoding to a fraction of the original size), further reducing I/O. Column storage makes point lookups (finding a single row by primary key) expensive — the row must be reconstructed from many column files — but analytical workloads rarely need point lookups.
What Studying This Changes
Databases changes how practitioners design and reason about systems that store and share state.
The first change is fluency with the relational model. The trained practitioner thinks relationally when designing data structures: what are the entities, what are their relationships, what are the invariants to enforce? The SQL that expresses these designs fluently, and that correctly specifies the behavior for edge cases, comes naturally. Practitioners who have not internalized the relational model often write procedural code to retrieve data that could be expressed as SQL, producing solutions that are harder to maintain, harder to optimize, and more likely to contain subtle correctness errors.
The second change is understanding transactional semantics. Every application that reads and modifies shared data implicitly relies on some set of transaction guarantees. The practitioner who understands ACID, isolation levels, and the specific anomalies each level permits can read application code and determine whether it is correct — whether the correctness argument depends on properties the database actually provides. The practitioner who does not understand transaction semantics cannot make this determination and cannot distinguish bugs from correct behavior when anomalies occur.
The third change is the ability to reason about query performance. Query performance is determined by the query plan, the available indexes, and the statistical properties of the data. The practitioner who understands query optimization can read a slow query and identify why it is slow: is it performing a full table scan when an index would help? Is it joining tables in an order that produces large intermediate results? Is the optimizer misjudging data distribution? Making these diagnoses requires understanding what the optimizer knows and how it makes decisions.
The fourth change is the ability to match database technology to workload requirements. The contemporary database landscape offers relational, document, column-oriented, graph, time-series, search, vector, and key-value databases. The choice depends on the workload: read-to-write ratio, consistency requirements, query patterns, scale requirements, and operational considerations. A practitioner who understands the design dimensions can evaluate the tradeoffs rather than making arbitrary choices or following fashion.
Resources
Books and Texts
Ramakrishnan and Gehrke’s Database Management Systems (3rd ed., McGraw-Hill, 2002) is the standard introduction that covers the foundational material thoroughly: relational model, SQL, relational algebra, query optimization, storage and indexing, transaction management, and recovery. It is technically careful and pedagogically clear. Garcia-Molina, Ullman, and Widom’s Database Systems: The Complete Book (2nd ed., 2008) covers broader ground, including XML, web databases, and data mining alongside the core material. Either is an appropriate primary text; Ramakrishnan-Gehrke is slightly more focused on the internal mechanisms that are most important for understanding database systems deeply.
Silberschatz, Korth, and Sudarshan’s Database System Concepts (7th ed., 2019) is the other major textbook, comprehensive and clearly organized. It is more conservative in its coverage of newer topics but covers the traditional material carefully. For learners who want a more traditional textbook approach, it is the appropriate choice.
Kleppmann’s Designing Data-Intensive Applications (O’Reilly, 2017) is the best bridge between foundational database concepts and contemporary distributed data systems. It covers replication, partitioning, distributed transactions, consistency models, stream processing, and data warehousing with a clarity unusual in technical books. It is not a replacement for a foundational database text but is the right second book for practitioners building distributed systems that use databases. It has become a standard reference in the industry.
Hellerstein and Stonebraker’s “Architecture of a Database System” (free, in Readings in Database Systems) is a survey paper rather than a book, but it is the most concise and useful introduction to how database systems are internally structured. Reading it after an introductory text consolidates the architectural picture. The Readings in Database Systems anthology itself (the “Red Book”, 5th ed., edited by Bailis, Hellerstein, and Stonebraker, free at redbook.io) collects the foundational papers in databases. Reading the papers in the query optimization and transaction processing sections after studying the introductory texts engages with the original ideas at depth.
Petrov’s Database Internals (O’Reilly, 2019) covers storage engine implementation — B-trees, LSM-trees, distributed coordination — at a depth that no textbook reaches. For practitioners who want to understand how databases are built internally or who work on storage systems, it is the right resource.
| Book | Role | Type |
|---|---|---|
| Ramakrishnan & Gehrke, Database Management Systems (3rd ed.) | Standard foundational entry | Entry |
| Garcia-Molina, Ullman & Widom, Database Systems: The Complete Book | Broader foundational entry | Entry |
| Silberschatz, Korth & Sudarshan, Database System Concepts (7th ed.) | Traditional comprehensive alternative | Entry |
| Database chapters in Kleppmann, Designing Data-Intensive Applications | Distributed data systems for practitioners | Depth |
| Hellerstein & Stonebraker, “Architecture of a Database System” (free) | Architectural consolidation | Depth |
| Bailis, Hellerstein & Stonebraker (eds.), Readings in Database Systems (free) | Foundational paper anthology | Reference |
| Petrov, Database Internals | Storage engine implementation | Practice |
| Gray & Reuter, Transaction Processing: Concepts and Techniques | Deep transaction reference | Reference |
Courses and Lectures
CMU 15-445/645 (Database Systems, Andy Pavlo, free lectures on YouTube) is one of the best database courses available, covering internals — storage, indexes, query processing, concurrency control, recovery — with exceptional clarity. Pavlo’s lecture style is direct and concrete, and the course covers material that most textbooks treat superficially. The associated programming projects — implementing a buffer pool manager, a B+ tree index, a query executor, and a transaction manager — are the most educational database implementation exercises available. The course materials and projects are freely available.
Berkeley CS 186 (Introduction to Database Systems, materials freely available) is the course that Hellerstein taught for many years. The lecture notes and materials cover the foundational material with strong emphasis on the systems perspective.
| Course | Platform | Type |
|---|---|---|
| CMU 15-445/645 Database Systems (Pavlo, free) | YouTube / CMU course site | Entry |
| Berkeley CS 186 Introduction to Database Systems (free) | Berkeley course site | Entry |
Practice, Tools, and Current Sources
PostgreSQL is the right database to study in practice. Its documentation is comprehensive and remarkably well-written, including detailed descriptions of internal mechanisms. The EXPLAIN and EXPLAIN ANALYZE commands show query execution plans with actual execution statistics — the most direct way to understand how the optimizer chooses plans and why queries are fast or slow. Enabling pg_stat_statements shows aggregate statistics on every query type executed. Running EXPLAIN ANALYZE on queries, reading the plans, and understanding why specific nodes appear is one of the most practical database learning activities available.
SQLite is the right database to read as source code. It is a complete, production-quality relational database in approximately 150,000 lines of well-commented C. Its documentation includes detailed descriptions of its file format, its query optimizer, its transaction management, and its virtual machine architecture. Reading the SQLite source, guided by the documentation, is one of the most efficient routes to understanding how database systems work internally.
The Use The Index, Luke! website (use-the-index-luke.com, free) is the most useful online resource on database indexing in practice. It explains how indexes work, how to design them, and how to diagnose queries that don’t use them effectively, with examples across multiple database systems.
Kyle Kingsbury’s Jepsen analyses (jepsen.io, free) test distributed database consistency guarantees empirically, finding cases where databases do not provide the guarantees they claim. Reading a Jepsen analysis of a specific database illuminates the gap between specification and implementation for that database and provides a practical lesson in what database consistency guarantees actually mean.
| Resource | Platform | Type |
|---|---|---|
| PostgreSQL EXPLAIN / EXPLAIN ANALYZE | PostgreSQL / psql | Practice |
| PostgreSQL Internals documentation (free) | postgresql.org/docs | Reference |
| SQLite source code and documentation (free) | sqlite.org | Reference |
| Use The Index, Luke! (free) | use-the-index-luke.com | Reference |
| Jepsen database consistency analyses (free) | jepsen.io | Reference |
| CMU BusTub educational DBMS (free) | github.com/cmu-db/bustub | Practice |
| DuckDB internals documentation and DiDi course (free) | duckdb.org | Reference |
| CMU 15-445 programming projects (free) | CMU course site | Practice |
Traps
| Trap | Why it misleads | Better response |
|---|---|---|
| The SQL-only view | SQL fluency is the entry point, not the destination. A practitioner who knows SQL well but has not studied query optimization, indexing, transaction management, and storage engines has learned the user interface without the subject. When SQL queries are slow, or when concurrent transactions produce unexpected results, or when the database behaves unexpectedly after a crash, SQL knowledge alone cannot diagnose or fix the problem. | Use SQL fluency as the foundation and build upward into the internal mechanisms. After writing queries fluently, study EXPLAIN output to see how the optimizer executes them, then study indexing to understand how to improve execution, then study transactions to understand what isolation the queries assume. The SQL is the language; the database is the subject. |
| The ORM trap | Object-relational mappers allow developers to interact with databases through object-oriented interfaces without writing SQL. ORMs are useful in context but hide the database’s behavior, often generate inefficient queries, and encourage thinking about databases as object stores rather than as relational systems with rich query semantics. Developers who learn databases primarily through ORMs often have confused understandings of what the database is doing. | Learn raw SQL and the underlying database concepts before adopting ORMs. Develop the habit of reading the SQL that ORMs generate to verify it is reasonable. When ORM-generated SQL is slow, understand why — whether the ORM is doing extra queries, missing indexes, or generating a suboptimal join plan — rather than treating it as a black box. |
| NoSQL as the escape from relational databases | The NoSQL movement produced useful systems and useful ideas, but it also produced the misleading narrative that relational databases were obsolete and that NoSQL was universally superior for modern applications. In practice, relational databases handle the majority of transactional workloads better than most NoSQL alternatives, and the specific cases where NoSQL is superior are narrower than the marketing implied. | Evaluate database systems based on their specific properties and tradeoffs, not on their categorization as “SQL” or “NoSQL.” The relevant questions are: what consistency guarantees does it provide? what query capabilities? what scaling approach? how does it handle failure? These questions cut across the SQL/NoSQL distinction. |
| Ignoring transaction semantics | Transactions are conceptually demanding, and their treatment in many courses and books is perfunctory. Practitioners who do not understand the specific anomalies that different isolation levels permit — dirty reads, non-repeatable reads, phantom reads, write skew — cannot verify that their applications are correct. Bugs that arise from isolation-level mismatches are subtle, occur rarely under low load, and become critical under high concurrency. | Allocate serious study time to transaction semantics. Understand what each ACID property means precisely. Understand what anomaly each isolation level prevents and permits. Then look at the isolation level of the databases in systems you work on and verify that the application’s correctness does not depend on properties the database does not provide. |
| Treating indexes as magic performance fixes | Indexes improve read performance for specific query patterns while imposing write overhead and storage costs. Adding indexes without understanding the query patterns they serve, the costs they impose, and how the optimizer uses them produces unexpected outcomes: indexes that are never used, indexes that slow down high-write workloads, or queries that are still slow because the optimizer chose not to use the available indexes. | Before creating an index, explain the query that motivates it and verify that the optimizer will use it. After creating it, run EXPLAIN ANALYZE on the motivated query and confirm the index is used. Monitor the write overhead introduced by the index. The Use The Index, Luke! website provides the conceptual framework for this analysis. |
| Skipping the implementation project | Reading about B-tree insertion, multi-version concurrency control, or write-ahead logging produces understanding that feels complete but is shallower than the understanding produced by implementing these mechanisms. The CMU 15-445 projects — implementing a buffer pool manager, a B+ tree index, a query executor, and a transaction manager — reveal subtleties in the specifications that reading does not surface: the edge cases in B-tree page splitting, the bookkeeping required for multi-version visibility, the ordering constraints in write-ahead logging. | Do the CMU 15-445 projects, or at minimum implement a B+ tree and a simple WAL-based recovery from scratch. The implementations will be less complete than production database code; that is not the point. The point is that the implementation forces engagement with the specifications at a level of precision that reading cannot. |
中文
数据库是一个用于存储、组织和检索结构化信息的系统，并且会对多个用户同时访问同一数据时会发生什么、硬件或软件失败时会发生什么提供保证。真正有意思的是这些保证。任何文件都可以存储数据；数据库管理系统的区别在于，它能在崩溃中维持数据正确性，防止并发操作互相破坏，并提供查询能力，使应用无需知道数据在物理上如何存储，就能准确取回所请求的数据。
两个基础概念是事务和查询。事务是一组原子执行的操作——它要么完整完成，要么完全没有效果——即使系统在中途崩溃，即使同时还有数百个其他事务在运行，也必须如此。这并不容易实现；它需要 write-ahead logging、multi-version concurrency control，以及谨慎的恢复流程。查询则是对所需数据的声明式规约，它表达想要什么数据，而不指定如何取回。数据库系统会根据当前数据状态、可用索引和系统资源，找出最高效的检索计划。这种把 what 与 how 分离的做法，是计算机科学中最有生产力的抽象之一；它使同一条查询在数据量增长、底层硬件变化时仍能正确运行，而不需要重写应用代码。
关系模型由 Edgar Codd 于 1970 年提出，为二者提供了数学基础。数据被组织为 relations（tables），操作由关系代数或其 SQL 等价形式规定，而模型的形式性质保证查询具有良定义语义，并且结果可以组合。这个模型已经有五十五年历史，经过几十年理论和工程完善，至今仍是世界上大多数数据管理基础设施的组织原则。
前置知识：操作系统（§4.2）——存储引擎与文件系统和 OS 内存管理深度交互。算法（§2.6）——查询处理、索引和并发控制包含大量算法内容。离散数学（§2.2）——关系模型在集合论和一阶逻辑中有形式化数学基础。
从层级文件到关系模型及其之后
数据库出现之前的数据管理，本质上是文件管理：程序用自己方便的格式维护自己的文件，没有共享访问，没有正式查询能力，也没有关于崩溃期间会发生什么的保证。在数据变成共享对象之前，这种方式还能工作——直到多个程序需要同一份信息，直到多个用户需要同时访问它，直到数据足够有价值，以至于停电时丢失它变得不可接受。
IBM 的 Information Management System（IMS）于 1966 年为 Apollo 项目的零件库存引入，是最早严肃处理这些问题的数据库系统之一。IMS 以层级结构组织数据——records 包含 child records，像一棵树——并提供 navigational access：程序要找到数据，需要从 root 沿树遍历到目标 leaf。IMS 解决了共享访问和崩溃恢复这些即时问题，但层级导航使查询难写，也使数据结构变更代价很高，因为应用代码与物理布局紧密耦合。
Edgar Codd 1970 年的论文 “A Relational Model of Data for Large Shared Data Banks” 提出了一种激进替代方案。Codd 在 IBM 工作，他观察到，层级模型和网络模型迫使应用导航物理数据结构——本质上就是把访问策略写进应用。他提出了一种基于数学关系的模型：数据是 tuples 的集合，操作由关系代数定义，并且清晰区分逻辑视图（数据意味着什么）和物理视图（数据如何存储）。查询指定想要的数据，而不是指定如何找到它；数据库系统负责高效找到它。这篇论文是计算机科学中影响最深远的论文之一，而且短到一个下午就可以读完。
真正证明 Codd 模型在实践中可行的系统，来自 1970 年代中期两个相互竞争的研究项目。IBM 的 System R 于 1974 到 1979 年间在 San Jose Research Laboratory 开发，它实现了关系模型，并发展出 SQL 作为查询语言。与此同时，Michael Stonebraker 和 Eugene Wong 在 Berkeley 的 Ingres 项目实现了另一个系统，并使用自己的查询语言 QUEL；在某些方面，QUEL 在理论上更干净。System R 与 Ingres 的竞争塑造了这个领域：System R 产生了 SQL，而 SQL 后来成为标准；Ingres 产生的研究传统，最终通向 PostgreSQL。System R 也产生了第一批严肃的查询优化工作——也就是如何自动把 SQL 翻译成高效执行计划——以及事务处理工作，其中 Jim Gray 的贡献建立了 ACID 事务的理论和实践基础，此后每个数据库系统都建立在这些基础之上。
支撑数据库性能的存储结构也在并行发展。Rudolf Bayer 和 Edward McCreight 于 1972 年发明了 B-tree，这是一种自平衡树结构，专门为 block-oriented storage 设计，因为在这种存储中，访问一个磁盘 block 的成本远高于在 block 内部计算的成本。B-tree 保持数据有序，并且即使数据增长到数十亿条记录，也能以 O(log n) 时间完成搜索、插入和删除；更关键的是，它执行这些操作的方式会最小化磁盘访问。B-trees 及其变体（B+ trees 把所有数据存在 leaves 中，现在更常见）是关系数据库中主导性的索引结构，并已经持续五十年。理解 B-tree，就是理解大多数数据库性能所依赖的基础。
商业数据库市场在 1980 年代早期围绕关系模型凝聚起来。Oracle 成立于 1977 年，于 1979 年发布第一款商业 SQL 数据库，并成为主导商业数据库厂商。IBM 的 DB2 随后出现。Sybase、Informix 等公司也在市场中竞争。到 1980 年代后期，关系数据库已经成为企业数据管理的标准基础设施，而 SQL 尽管在许多方面偏离了 Codd 设想中的关系模型，仍然成为主导查询语言。
Internet 创造了传统数据库系统未曾预料的规模挑战。到 2000 年代早期，Google、Amazon 和 Facebook 这类公司已经运行在这样的规模上：单个关系数据库——无论多强大——都无法保存所有数据，也无法服务所有用户。回应方式是 partitioning：把数据分布到许多机器上。这立刻触碰到一个根本限制：2000 年，Eric Brewer 提出猜想；2002 年，Seth Gilbert 和 Nancy Lynch 证明了后来被称为 CAP theorem 的结论。一个分布式系统至多只能同时具备三种性质中的两种：Consistency（所有节点看到同一数据）、Availability（每个请求都能收到响应）和 Partition tolerance（当网络分区发生时系统仍能继续运行）。由于任何真实分布式系统都必须具备 partition tolerance，因此在分区发生时，真正的选择是在 consistency 和 availability 之间。
Google 的内部系统，尤其是 Bigtable（2006）和 Chubby 分布式锁服务，选择 partition tolerance 和 availability，而不是严格 consistency，接受不同 replicas 可能暂时显示不同数据。Amazon 的 Dynamo（2007）——AWS 中包括 DynamoDB 在内的服务都建立在其思想之上——做出了类似选择，并推广了 eventual consistency 概念：replicas 最终会收敛到同一状态，但不保证何时。这些论文触发了 “NoSQL” 数据库开发浪潮：Cassandra（在 Facebook 开发，2008 年开源）、MongoDB（2009）和许多其他系统，每个系统都用某些关系性质换取可扩展性、灵活性或运维简单性。NoSQL 运动扩展了设计空间，并引入了有用系统，但它也制造了大量关于“到底放弃了什么、为什么放弃”的混乱。
回摆来自一个意料之外的方向。Google 的 Spanner（2012）证明，全球地理分布、强一致的事务可以在规模化条件下实现——这反驳了当时广泛存在的假设，即 CAP 使分布式系统中的强一致性不可能实现。Spanner 使用同步时钟（TrueTime，依赖 GPS 和原子钟）和 Paxos 共识，在不同大陆的数据中心之间实现 external consistency——一种强于 serializability 的性质。CockroachDB 和 YugabyteDB 项目把受 Spanner 启发的设计作为开源系统提供。这些 “NewSQL” 系统证明，NoSQL 浪潮对事务的拒绝是一种工程选择，而不是根本必然性。
当代数据库图景，是五十五年历史的结果。关系数据库（PostgreSQL、MySQL、SQL Server、Oracle）仍然处理着世界上大多数 transactional data。面向列的分析型数据库（Snowflake、BigQuery、Redshift、ClickHouse）已经改变了大规模数据分析。Time-series databases（InfluxDB、TimescaleDB）、graph databases（Neo4j）、search engines（Elasticsearch）、key-value stores（Redis）和 vector databases（Pinecone、pgvector）服务于专门工作负载。面对这种多样性，合适回应不是挑一个系统当作普遍正确答案，而是理解基本设计维度——consistency vs. availability、read vs. write optimization、transactional vs. analytical、row vs. column storage——正是这些维度决定了哪个系统适合哪个工作负载。
关系模型、事务与存储
关系模型与查询处理
关系模型把数据表示为命名 relations（tables），每个 relation 由具有固定 schema 的 tuples 集合组成。操作由关系代数定义：selection（过滤 rows）、projection（选择 columns）、join（把多个 relations 中相关数据组合起来）、union、intersection 和 difference。关系代数是封闭的——每个操作都产生一个 relation——这使组合成为可能：一个操作的输出可以成为另一个操作的输入。SQL 是这种代数上的声明式语言，允许用户表达查询，而不指定 evaluation strategy。
查询优化——把 SQL 查询翻译成高效执行计划——是数据库系统中智识要求很高的问题之一。Optimizer 必须枚举可能执行策略（使用哪种 join algorithm，以什么顺序 join tables，是否使用 indexes），用关于数据的统计信息（table sizes、value distributions、columns 之间的 correlation）估计每种策略成本，并选择估计成本最低的计划。查询中 tables 数量增加时，搜索空间会指数级增长，因此实际 optimizer 会使用 heuristics 剪枝。成本估计并不精确，optimizer 会犯错——尤其是对包含许多 joins 的复杂查询——并可能产生比最优计划慢数个数量级的执行计划。理解 optimizer 如何工作、使用什么统计信息，以及如何通过 hints、创建 indexes 或重写 query 来引导它，是最有实践价值的数据库技能之一。
Indexes 通过维护辅助结构来加速数据访问，使人可以按特定属性快速查找。建立在某个属性上的 B+ tree index 会把属性值按排序顺序存储，并带有指向实际 tuples 的指针，既支持 exact-match queries，也支持 range queries。Hash index 支持更快的 exact-match queries，但无法回答 range queries。Composite indexes——建立在多个属性组合之上——可以加速同时按所有索引属性过滤的查询，但未必能帮助只按其中某些属性过滤的查询。Covering indexes 在 index 本身包含查询所需的所有 columns，使查询可以直接从 index 回答，而不访问底层 table。Index selection——决定创建哪些 indexes——是重大实践决策，它会使查询性能产生数量级差异。
事务：ACID 与并发控制
事务把一组操作组合成一个原子单位。ACID properties 精确规定了这意味着什么。Atomicity：事务要么完整完成，要么完全没有效果。Consistency：事务把数据库从一个有效状态带到另一个有效状态，并保持所有已定义不变量。Isolation：并发事务表现得就像串行运行一样，不会看到彼此的中间状态。Durability：一旦提交，事务效果会在此后的任何失败中存活下来。
要同时为多个并发事务提供这些性质，又不要求它们串行执行（那会消除并发性，也消除性能），是事务管理的中心挑战。并发控制协议管理并发事务之间的交错。Two-phase locking（2PL）在访问数据前获取 locks，并把所有 locks 持有到事务 commit 或 abort；它保证 serializability，但可能造成 deadlock。Multi-version concurrency control（MVCC）为每个数据项维护多个版本，使 readers 可以访问其事务开始时的当前版本，而不阻塞 writers；这消除了 read-write conflicts，并提高并发性，代价是需要维护旧版本，并最终对其进行 garbage collection。Snapshot isolation——每个事务看到数据库在其开始时刻的一致 snapshot——是许多现代数据库的默认隔离级别；它避免了大多数 anomalies，但不是完整 serializability，并允许 write skew 等特定 anomalies。
SQL 标准定义的隔离级别（READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE）在 anomaly prevention 和 performance 之间提供不同权衡。多数应用实践中使用 READ COMMITTED 或 REPEATABLE READ；SERIALIZABLE 尽管提供最强保证，却因为性能成本很少使用。理解每个级别允许哪些具体 anomalies——dirty reads、non-repeatable reads、phantom reads、write skew——对于推理应用正确性是否依赖数据库实际提供的隔离保证至关重要。
Write-ahead logging（WAL）实现 atomicity 和 durability。每次修改都会在应用到 data pages 之前先写入 log。如果系统崩溃，log 会记录哪些 changes 正在进行中；恢复流程会 replay 已提交事务，并回滚未提交事务，把数据库恢复到一致状态。ARIES（Algorithm for Recovery and Isolation Exploiting Semantics）协议由 IBM 于 1980 年代后期开发，提供了大多数生产数据库使用的标准恢复算法。它的三个阶段——Analysis（从 log 找到崩溃时状态）、Redo（重放所有已记录 changes）、Undo（回滚未提交事务）——保证无论崩溃发生在何时，都能恢复到一致状态。
存储：B-Trees、LSM-Trees 与列式革命
存储引擎设计决定了一个数据库能高效处理哪些工作负载。数据结构选择编码了关于 read-to-write ratio、key distribution、所需操作类型，以及 read amplification 和 write amplification 之间可接受权衡的假设。
B+ trees 把数据按排序顺序存储在一棵平衡树中，所有数据都在 leaf nodes 中，所有 internal nodes 只用于导航。搜索、插入和删除都需要 O(log n) 时间。更新会原地修改数据：找到相关 leaf page，修改数据，并把 page 标记为 dirty。这种设计优化 read performance——找到数据需要访问很少 pages——代价是 random writes，而 random writes 在旋转磁盘上昂贵，在 SSD 上会造成 write amplification。
Log-Structured Merge Trees（LSM-trees）由 O’Neil 等人于 1996 年提出，并由 Google 的 LevelDB 和 RocksDB 推广，它们采取相反方式：所有写入都是顺序写。进入的 writes 先被缓存在内存结构（MemTable）中，当 MemTable 填满后，以不可变的 sorted runs（SSTables）写入磁盘。后台 compaction 会合并 SSTable runs，维持排序顺序并移除 deleted entries。LSM-trees 获得高写入吞吐量，因为所有磁盘写入都是顺序的；代价是 read amplification（一个 key 可能位于多层 SSTable 中任意一层）和 compaction overhead。LSM-trees 主导 write-heavy workloads：Apache Cassandra、RocksDB、LevelDB 以及许多其他系统都使用它们。
Column-oriented storage 用于分析型数据库（Snowflake、BigQuery、ClickHouse、Redshift），它把 table 的每一列分开存储，而不是把整行放在一起。分析查询通常会跨许多 rows 扫描少量 columns——例如计算某个日期范围内订单销售额均值，只需要读取 amount 和 date columns，而不需要 customer address、order description 或其他 columns。列式存储使这一点高效：只有相关 columns 会从磁盘读取。列值也高度可压缩（一个 product categories 列通常只有少量 distinct values，可以用简单 dictionary encoding 压缩到原始大小的一小部分），进一步减少 I/O。列式存储会使 point lookups（按 primary key 查找单行）昂贵——该 row 必须从许多 column files 中重建——但分析工作负载很少需要 point lookups。
学习这一部分会改变什么
数据库会改变实践者设计和推理存储、共享状态系统的方式。
第一个变化，是对关系模型的熟练掌握。受过训练的实践者在设计数据结构时会以关系方式思考：实体是什么，它们之间的关系是什么，需要强制执行哪些不变量？能够流畅表达这些设计、并为边缘情况正确指定行为的 SQL，会自然出现。没有内化关系模型的实践者，往往会写程序式代码来检索本来可以用 SQL 表达的数据，结果产生更难维护、更难优化，也更可能包含细微正确性错误的方案。
第二个变化，是理解事务语义。每个读取和修改共享数据的应用，都隐含依赖某一组事务保证。理解 ACID、隔离级别以及每个隔离级别允许哪些具体 anomalies 的实践者，可以阅读应用代码并判断它是否正确——也就是判断正确性论证是否依赖数据库实际提供的性质。不了解事务语义的实践者无法做出这种判断，也无法在 anomalies 出现时区分 bug 与正确行为。
第三个变化，是推理查询性能的能力。查询性能由 query plan、可用 indexes 和数据统计性质决定。理解查询优化的实践者可以阅读一条慢查询，并识别它为什么慢：是不是在执行 full table scan，而一个 index 本可以帮助？是不是以产生巨大中间结果的顺序 join tables？是不是 optimizer 误判了数据分布？做出这些诊断，需要理解 optimizer 知道什么，以及如何做决策。
第四个变化，是能够把数据库技术与工作负载要求匹配起来。当代数据库图景提供了 relational、document、column-oriented、graph、time-series、search、vector 和 key-value databases。选择取决于工作负载：read-to-write ratio、consistency requirements、query patterns、scale requirements 和 operational considerations。理解设计维度的实践者会评估权衡，而不是任意选择或追随潮流。
资源
书籍与文本
Ramakrishnan 和 Gehrke 的 Database Management Systems（第 3 版，McGraw-Hill，2002）是标准入门书，完整覆盖基础材料：关系模型、SQL、关系代数、查询优化、存储与索引、事务管理和恢复。它技术上谨慎，教学上清晰。Garcia-Molina、Ullman 和 Widom 的 Database Systems: The Complete Book（第 2 版，2008）覆盖范围更广，除核心材料外，还包括 XML、Web databases 和 data mining。二者都适合作为主教材；Ramakrishnan-Gehrke 更聚焦于深入理解数据库系统最重要的内部机制。
Silberschatz、Korth 和 Sudarshan 的 Database System Concepts（第 7 版，2019）是另一本主要教材，综合且组织清楚。它对较新主题的覆盖更保守，但对传统材料处理仔细。对于想要更传统教材路径的学习者，这是合适选择。
Kleppmann 的 Designing Data-Intensive Applications（O’Reilly，2017）是连接基础数据库概念与当代分布式数据系统的最佳桥梁。它清晰地覆盖复制、分区、分布式事务、一致性模型、流处理和数据仓库，清晰度在技术书中少见。它不能取代基础数据库教材，但对于构建使用数据库的分布式系统的实践者来说，它是正确的第二本书。它已经成为工业界标准参考。
Hellerstein 和 Stonebraker 的 “Architecture of a Database System”（免费，收录于 Readings in Database Systems）是一篇综述论文而不是书，但它是关于数据库系统内部结构最简洁、最有用的入门。读完入门教材后阅读它，可以巩固架构图景。Readings in Database Systems 选集本身（“Red Book”，第 5 版，由 Bailis、Hellerstein 和 Stonebraker 编辑，可在 redbook.io 免费获取）收录了数据库领域的奠基论文。学习入门教材后，再阅读其中查询优化和事务处理部分的论文，可以深入接触原始思想。
Petrov 的 Database Internals（O’Reilly，2019）以任何教材都达不到的深度覆盖存储引擎实现——B-trees、LSM-trees、分布式协调。对于想理解数据库内部如何构建，或从事存储系统工作的实践者来说，这是正确资源。
| 书籍 | 作用 | 类型 |
|---|---|---|
| Ramakrishnan & Gehrke, Database Management Systems（第 3 版） | 标准基础入口 | 入门 |
| Garcia-Molina, Ullman & Widom, Database Systems: The Complete Book | 更宽泛的基础入口 | 入门 |
| Silberschatz, Korth & Sudarshan, Database System Concepts（第 7 版） | 传统综合替代 | 入门 |
| Kleppmann, Designing Data-Intensive Applications 中的数据库章节 | 面向实践者的分布式数据系统 | 深入 |
| Hellerstein & Stonebraker, “Architecture of a Database System”（免费） | 架构图景整合 | 深入 |
| Bailis, Hellerstein & Stonebraker 编，Readings in Database Systems（免费） | 奠基论文选集 | 参考 |
| Petrov, Database Internals | 存储引擎实现 | 实践 |
| Gray & Reuter, Transaction Processing: Concepts and Techniques | 深入事务参考 | 参考 |
课程与讲座
CMU 15-445/645（Database Systems，Andy Pavlo，YouTube 上有免费讲座）是目前最好的数据库课程之一，覆盖内部机制——存储、索引、查询处理、并发控制、恢复——并且异常清晰。Pavlo 的讲课风格直接、具体，课程覆盖了多数教材只表面处理的内容。配套编程项目——实现 buffer pool manager、B+ tree index、query executor 和 transaction manager——是目前最有教育价值的数据库实现练习。课程材料和项目都可免费获取。
Berkeley CS 186（Introduction to Database Systems，材料免费可得）是 Hellerstein 多年来教授的课程。讲义和材料覆盖基础内容，并强烈强调系统视角。
| 课程 | 平台 | 类型 |
|---|---|---|
| CMU 15-445/645 Database Systems（Pavlo，免费） | YouTube / CMU course site | 入门 |
| Berkeley CS 186 Introduction to Database Systems（免费） | Berkeley course site | 入门 |
实践、工具与当前资料
PostgreSQL 是实践学习中合适的数据库。它的文档全面且写得非常好，包括对内部机制的详细说明。EXPLAIN 和 EXPLAIN ANALYZE 命令会显示 query execution plans 和真实执行统计——这是理解 optimizer 如何选择计划、查询为什么快或慢的最直接方式。启用 pg_stat_statements 会显示每类查询执行的聚合统计。对 queries 运行 EXPLAIN ANALYZE，阅读 plans，并理解为什么出现特定 nodes，是最实用的数据库学习活动之一。
SQLite 是适合阅读源码的数据库。它是一个完整的、生产质量的关系数据库，大约 150,000 行注释良好的 C 代码。其文档包含对文件格式、查询优化器、事务管理和 virtual machine 架构的详细说明。在文档引导下阅读 SQLite 源码，是理解数据库系统内部如何工作的最高效路径之一。
Use The Index, Luke! 网站（use-the-index-luke.com，免费）是数据库索引实践中最有用的在线资源。它解释索引如何工作、如何设计索引，以及如何诊断没有有效使用索引的查询，并提供跨多个数据库系统的例子。
Kyle Kingsbury 的 Jepsen analyses（jepsen.io，免费）会对分布式数据库的一致性保证进行经验测试，找出数据库没有提供其声称保证的情况。阅读某个具体数据库的 Jepsen 分析，可以照亮该数据库规约与实现之间的差距，并提供关于数据库一致性保证到底意味着什么的实践课。
| 资源 | 平台 | 类型 |
|---|---|---|
| PostgreSQL EXPLAIN / EXPLAIN ANALYZE | PostgreSQL / psql | 实践 |
| PostgreSQL Internals documentation（免费） | postgresql.org/docs | 参考 |
| SQLite source code and documentation（免费） | sqlite.org | 参考 |
| Use The Index, Luke!（免费） | use-the-index-luke.com | 参考 |
| Jepsen database consistency analyses（免费） | jepsen.io | 参考 |
| CMU BusTub educational DBMS（免费） | github.com/cmu-db/bustub | 实践 |
| DuckDB internals documentation and DiDi course（免费） | duckdb.org | 参考 |
| CMU 15-445 programming projects（免费） | CMU course site | 实践 |
陷阱
| 陷阱 | 为什么会误导 | 更好的回应 |
|---|---|---|
| 只会 SQL 的视角 | SQL 熟练度是入口，不是终点。一个实践者即使很会 SQL，但没有学习查询优化、索引、事务管理和存储引擎，也只是学会了用户界面，而不是这门学科。SQL 查询变慢、并发事务产生意外结果，或数据库在崩溃后表现异常时，单靠 SQL 知识无法诊断或修复问题。 | 以 SQL 熟练度为基础，向上进入内部机制。能流畅写查询之后，学习 EXPLAIN 输出，看 optimizer 如何执行它们；再学习索引，理解如何改善执行；再学习事务，理解查询假定了什么隔离性。SQL 是语言；数据库才是主题。 |
| ORM 陷阱 | Object-relational mappers 允许开发者通过面向对象接口与数据库交互，而不用写 SQL。ORM 在特定语境中有用，但它会隐藏数据库行为，经常生成低效查询，并鼓励人们把数据库当作 object store，而不是具有丰富查询语义的关系系统。主要通过 ORM 学习数据库的开发者，往往对数据库正在做什么有混乱理解。 | 在采用 ORM 之前，先学习 raw SQL 和底层数据库概念。养成阅读 ORM 生成 SQL 的习惯，以确认它是合理的。当 ORM 生成的 SQL 很慢时，理解原因——是 ORM 发起了额外查询、缺少 indexes，还是生成了次优 join plan——而不是把它当成黑箱。 |
| 把 NoSQL 当成逃离关系数据库的道路 | NoSQL 运动产生了有用系统和有用思想，但也制造了一种误导性叙事：关系数据库已经过时，NoSQL 对现代应用普遍更优。实践中，关系数据库比多数 NoSQL 替代方案更好地处理大多数 transactional workloads，而 NoSQL 真正更优的具体场景，比市场叙事暗示的更窄。 | 根据数据库系统的具体性质和权衡评估它，而不是根据它被归为 “SQL” 还是 “NoSQL” 来判断。相关问题是：它提供什么 consistency guarantees？什么 query capabilities？采用什么 scaling approach？如何处理 failure？这些问题会穿透 SQL/NoSQL 区分。 |
| 忽视事务语义 | 事务在概念上要求很高，而许多课程和书对它的处理很简略。不理解不同 isolation levels 允许哪些具体 anomalies——dirty reads、non-repeatable reads、phantom reads、write skew——的实践者，无法验证应用是否正确。由隔离级别不匹配产生的 bug 很隐蔽，在低负载下很少出现，却会在高并发下变得关键。 | 给事务语义分配严肃学习时间。精确理解每个 ACID property 的含义。理解每个 isolation level 防止什么 anomaly，又允许什么 anomaly。然后查看你所处理系统中数据库的隔离级别，并验证应用正确性并不依赖数据库未提供的性质。 |
| 把索引当成魔法性能修复 | 索引会针对特定查询模式改善读取性能，同时施加写入开销和存储成本。在不理解索引服务的查询模式、索引带来的成本，以及 optimizer 如何使用索引的情况下添加索引，会产生意外结果：从未被使用的索引、拖慢高写入工作负载的索引，或者因为 optimizer 选择不使用现有索引而仍然很慢的查询。 | 创建索引之前，先说明它服务哪条查询，并验证 optimizer 会使用它。创建之后，对目标查询运行 EXPLAIN ANALYZE，确认 index 被使用。监控该 index 引入的写入开销。Use The Index, Luke! 网站提供了做这种分析所需的概念框架。 |
| 跳过实现项目 | 阅读 B-tree 插入、multi-version concurrency control 或 write-ahead logging，会产生一种似乎完整但仍然浅的理解；实现这些机制带来的理解更深。CMU 15-445 项目——实现 buffer pool manager、B+ tree index、query executor 和 transaction manager——会揭示阅读无法暴露的规约细节：B-tree page splitting 的边缘情况、multi-version visibility 所需的 bookkeeping、write-ahead logging 中的顺序约束。 | 完成 CMU 15-445 项目；至少也要从零实现一个 B+ tree 和一个简单基于 WAL 的恢复机制。实现不会像生产数据库代码那样完整；这不是重点。重点是，实现会迫使你以阅读无法达到的精确度处理规格。 |