Databases

1. General Rules - In PostgreSQL, every transaction gets a transaction ID called XID - It can be queried with select cast(txid_current() as text) - The transactions mentioned here are not only groups of statements wrapped by BEGIN - COMMIT, but also individual insert, update, or delete statements - When a transaction begins, PostgreSQL increments XID and assigns it to the transaction

1. Without WHERE 1.1. Ordinary explain - Result 1.1.1. Analysis - Read method - Sequential scan, reading block by block - Statistics - cost - Time to obtain the first row - Time to obtain all rows - The unit is a planner cost unit, not milliseconds - rows - Number of rows scanned - width - Average length of all rows

1. What Is a Join Query Take records from each joined table, match them one by one, add the matching combinations to the result set, and return them to the user. 2. What Types of Join Are There 2.1. Inner Join If a record in the driving table cannot find a matching record in the driven table, it is not added to the final result set. Conditions after ON and WHERE are equivalent. The driving and driven tables can be swapped; without conditions this is equivalent to a Cartesian product. 2.2. Outer Join Even if a record in the driving table has no matching record in the driven table, it is added to the result set. The ON clause must be used to specify the join condition. ON and WHERE are not equivalent.

1. What It Is 1. Execute FROM JOIN ON and create a temporary table. 2. Execute WHERE and filter data. Aliases from SELECT cannot be used here; only fields from the FROM and JOIN tables can be used because SELECT has not executed yet. 3. Execute GROUP BY. Values that are equal are grouped together, and aggregate functions can then be used after SELECT.

1. What Is a Relational Database A database that uses the relational model to organize data. Relationships: one-to-one, one-to-many, many-to-many; many-to-many can be decomposed into two one-to-many relationships through an intermediate table. 2. Why Relational Databases Are Needed 3. Characteristics of Relational Databases 3.1. Table-Structured Storage Data is stored using a two-dimensional table model, in rows and columns. 3.2. Normalized Design Database Normal Forms.md 3.3. Query Data Using SQL SQL Join Queries.md 3.4. Support Transactions Database Transactions.md

1. Pessimistic Locking 2. Optimistic Locking Optimistic locking assumes that data conflicts generally will not occur, so conflicts are formally detected only when data is submitted for update. Version Number Add a version field. Query once first. When updating, add update xxx set version=version+1 where version=version to the original statement; if it fails, loop and retry. 3. Reference Database First-Type and Second-Type Lost Updates - Tencent Cloud

1. What Is a Transaction Combine multiple read and write operations into one logical unit and treat them as one operation. This operation satisfies the ACID properties. 2. Why Transactions Are Needed Mainly to simplify how the application layer handles a series of problems. A transaction is an abstraction layer: application -> transaction -> database. Through the transaction abstraction, an application can pretend that the database will not crash (atomicity), nobody else is accessing the database at the same time (isolation), and the storage device is completely reliable (durability). All these errors are simplified into transaction abort, and the application only needs to retry. 3. Transaction Properties

1. What Is Database Optimization 2. Database Optimization Approaches 2.1. Application Layer 2.1.1. Cache How to Design a Cache System.md 2.2. Database Layer 2.2.1. SQL Statements MySQL SQL Tuning.md 2.2.2. Table Structure Redundant fields in the main table 2.2.3. Replication MySQL Primary-Replica Replication.md 2.2.4. Partitioning, Database Sharding, and Table Sharding Database Sharding and Table Sharding.md 2.3. System Layer 2.3.1. Tune Server Parameters MySQL Configuration Linux Configuration 2.4. Hardware Layer More Powerful Machines 3. References

1. Why Database Sharding and Table Sharding Are Needed Distributed System Partitioning.md 2. What Database Sharding and Table Sharding Are Database sharding splits one original database into multiple databases; table sharding splits one original table into multiple tables. Both database sharding and table sharding can use horizontal or vertical splitting. 2.1. Vertical Splitting 2.1.1. Vertical Table Splitting Each table has a different structure and different data. Split the fields of one table into multiple tables according to usage frequency and whether they are large fields (one-to-one relationship). For example, split a product information table into a product basic information table and a product description table. This operation is generally completed during the initial design. 2.1.2. Vertical Database Splitting Each database has a different structure and different data.

1. What Is a Deadlock A deadlock means that two (or more) transactions each hold a lock the other wants. If transaction 1 obtains an exclusive lock on table A while trying to obtain an exclusive lock on table B, and transaction 2 already holds the exclusive lock on table B while requesting an exclusive lock on table A, neither transaction can proceed. 1.1. Example Two concurrent transactions modify one table. 2. How to Resolve Deadlocks 2.1. Timeout 2.2. Deadlock Detection 3. References