Database

1. MySQL Installation - MySQL Installation and Configuration.md 2. MySQL Usage - MySQL Data Types.md 3. MySQL Architecture - MySQL Architecture.md 4. MySQL Cluster - MySQL Primary-Replica Replication.md 5. MySQL Online Issue Troubleshooting MySQL Online Issue Troubleshooting.md 6. MySQL Benchmarking MySQL Benchmarking

1. What It Is Used to see how MySQL executes SQL statements so that SQL can be optimized 2. Purpose - Table read order - Which indexes can be used - Index actually used - How many rows are read from each table 3. Field Description 3.1. id - One select corresponds to one id - When ids are the same, execution proceeds from top to bottom - In a join query, one select + multiple tables produces multiple rows, but the ids are the same

1. What Is Primary-Replica Replication - Synchronize data from the primary database to the replica database 2. The Role of Primary-Replica Replication Distributed System Replication.md 3. Use Cases for Primary-Replica Replication - Read-heavy and write-light workloads where reads do not require very high data freshness 4. How to Enable Primary-Replica Replication - my.ini 5. Principles of Primary-Replica Replication When MySQL inserts, deletes, or updates data, in addition to updating the data, it also writes the insert, delete, and update operations to the binlog

1. Single-Instance MySQL Bottleneck 3 million records, 2,000 concurrent requests 2. Overall Approach - Database Optimization.md 3. Steps 3.1. Locate Slow Queries - MySQL Slow Query Log.md - MySQL Indexes.md 3.2. Analyze SQL - MySQL explain.md - MySQL show profile.md 3.3.

1. MySQL Installation Steps 1.1. Install 1.2. Start 1.3. Set the root Password 1.4. Create a User 1.5. Change the Listening Port 2. Default Configuration 2.1. Print Default Configuration Information 2.2. Configuration File Locations 2.3. Basic Configuration 2.3.1. log-bin Mainly used for primary-replica replication 2.3.2. log-error

1. What Is the Query Optimizer - It transforms the syntax analysis tree into query trees, and there are many possible query trees, each corresponding to one way of executing the query - The query optimizer's role is to find the best execution method among them 2. How the Query Optimizer Optimizes SQL - It involves two kinds of optimization: logical query optimization and physical query optimization 2.1. Logical Query Optimization - Find equivalent transformations of SQL statements that make SQL execution more efficient

1. What Is a Storage Engine - The component in MySQL responsible for storage-related work - Used to process SQL operations and interact with the underlying file system 2. Storage Engine Types - View all storage engines - Default storage engine 2.1. InnoDB - MySQL InnoDB.md 2.2. MyISAM 2.3. Memory - The primary-key ID uses a Hash index and can be changed to a B+ tree index

1. What Is a Lock - Database Locks.md 2. Lock Implementation - trx information: indicates which transaction generated this lock structure - is_waiting: indicates whether the current transaction is waiting 3. Lock Classification - Based on locking scope, MySQL locks can roughly be divided into global locks, table-level locks, and row locks 3.1. Global Lock 3.1.1. What It Is - Flush tables with read lock locks the entire database instance

1. What Is an Index - An index is a structure that sorts the values of one or more columns in a database table and is a data structure (B+Tree) that helps MySQL obtain data efficiently 2. Index Classification 2.1. By Whether It Is a Primary Key - Primary-key index - Data columns cannot be duplicated or NULL; a table can have only one primary key - Secondary index - Basic index type with no uniqueness restriction; NULL values are allowed

1. Underlying Index Implementation - In the InnoDB storage engine, each index corresponds to a B+ tree 2. Why Use a B+ Tree First, think about why a tree structure is used instead of an array or linked list 2.1. Why a Tree - An array has fast lookup O(1), but insert/delete efficiency is low O(n) - A linked list has fast insert/delete O(1), but slow lookup O(n) - A hash table has O(1) access, but does not support range lookup - A tree balances lookup and modification efficiency

1. Representation of Databases in the File System - Each database corresponds to a subdirectory under the data directory, or a folder 1. Create a subdirectory with the same name as the database under the data directory. 2. Create a file named db.opt under that subdirectory; this file contains various properties of the database, such as its character set and collation 2. Representation of Tables in the File System 2.1. MyISAM 2.2. InnoDB 3. Representation of Views in the File System

1. Row Format - The format in which a Record (Row) is stored on disk 1.1. Compact 1.1.1. Extra Record Information 1.1.1.1. Variable-Length Field Length List - For varchar(100), blob, text, etc., the length is recorded here 1.1.1.2. NULL Value List - Bits are used to record whether a column is NULL 1.1.1.3. Record Header Information 1.1.2. Actual Record Data 1.2. Redundant 1.3. Dynamic 1.4. Compressed 2. Pages

Historical study notes on MySQL data types

1. What Is bin-log - A log at the MySQL Server layer, used for log archiving - Records change operations on the database, excluding query operations. 2. Why bin-log Is Still Needed When There Is redo-log - redo-log belongs to the InnoDB storage engine, while bin-log is at the MySQL Server layer 3. bin-log

1. MySQL Logical Architecture Diagram 1. The client requests the server 2. The server's connection manager handles connections 3. The server's query optimizer handles SQL - After a query statement is parsed, it is handed to the query optimizer for optimization. The result of optimization is a so-called execution plan - This execution plan indicates which indexes should be used for the query and the join order between tables 4. Finally, according to the steps in the execution plan, methods provided by the storage engine are called to actually execute the query and return the query result to the user 2. MySQL Components

1. What Is the Connector - Responsible for handling client connections 2. Connector Functions 2.1. Manage Connections - If the client makes no request for longer than wait_timeout, it is automatically disconnected - Long connections are used by default, meaning that after the client establishes a connection, multiple queries use the same connection - To avoid OOM, long connections can be disconnected periodically 2.2. Permission Verification - Verify username and password 3. Connection Protocol

1. What Are MySQL Statistics MySQL query cost is calculated based on statistics 2. What Statistics Are There 2.1. Persistent Disk-Based Statistics - These statistics are stored on disk, meaning they remain after the server restarts. 2.1.1. Storage Location - Stored in two tables - innodb_table_stats stores statistics about tables, with each row corresponding to one table's statistics - innodb_index_stats stores statistics about indexes, with each row corresponding to one statistical item for an index

1. Single-Table Access Methods - The way MySQL executes a query statement is called an access method or access type 1.1. const 1.1.1. Equality comparison through a primary key or unique secondary index matches only one record 1.1.1.1. Equality comparison between the primary key and a constant 1.1.1.2. Equality comparison between a unique secondary-index column and a constant 1.2. ref 1.2.1. Equality comparison between an ordinary secondary-index column and a constant matches multiple contiguous records

1. What It Is Record statements whose query time exceeds a certain threshold in the log 2. Usage 2.1. How to Enable It You need to reconnect to the database before the effect becomes visible 2.2. How to View It - View the log directly - Use the mysqldumpslow command 3. References

1. What It Is 2. Usage 2.1. Enable 2.2. View Results 2.3. Analyze

- mysql: - Stores MySQL user accounts and privilege information, definitions of some stored procedures and events, some log information generated during operation, help information, time zone information, etc. - information_schema: - Stores information about all other databases maintained by the MySQL server, such as tables, views, triggers, columns, and indexes

1. What Is the Parser - Parse SQL statements 2. Parser Functions 2.1. Lexical Analysis - Statement -> String Tokens 2.2. Syntax Analysis - String Tokens -> Syntax Tree

1. What Is the Executor - Execute SQL 2. Executor Functions 2.1. Permission Check - For example, whether there is Select permission 2.2. Fetch Data - Call the storage engine interface to obtain data; if the conditions are met, put it into the result set

CPU 100% 1. Use show processlist to list all processes and inspect the sessions that are running to see whether resource-consuming SQL is executing 2. Find the high-consumption SQL, kill those threads, and use explain to analyze the SQL

Performance Evaluation (1): MySQL Cloud Database vs Self-Hosted Database - Tencent Cloud MySQL Column - Online Tuning and Load Testing - SegmentFault

1. Tencent Cloud MySQL - The example environment uses MySQL on Tencent Cloud 1.1. Deployment - Node topology: <redacted> - Node configuration: <redacted> - Version 5.7 1.2. Availability - Service Level Agreement 1.3. Parameters - Cloud Database MySQL Instance Parameter Settings - Operation Guide

1. What Is the Buffer Pool - A continuous memory space requested from the operating system when MySQL starts 2. Why the Buffer Pool Is Needed - The speed difference between disk and CPU is too large, so memory is needed as a cache 3. Buffer Pool Workflow - When reading data, read from the Buffer Pool first; if it is present, return it directly, otherwise read it from disk and put it into the Buffer Pool

1. Overall Approach 1. Record slow SQL through slow query logs / monitoring / Druid - MySQL Tuning.md 2. Analyze with explain - Index invalidation - Too many tables in join queries - Low server parameter configuration 3. Add indexes - MySQL Indexes.md - Pay attention to scenarios where indexes cannot be used 4. Modify SQL statements - Use join or exists depending on the situation - Do not use select *

1. What Is canal - A component that parses MySQL bin-log 2. Why canal Is Needed - Obtain incremental changes to MySQL data and synchronize them to components such as Elasticsearch and Redis 3. canal Principle - Essentially simulates a replica in primary-replica replication, so it is the same as MySQL Primary-Replica Replication.md 4. How to Use canal

1. What Is the Buffer Pool - A contiguous memory space requested from the operating system when MySQL starts 2. Why the Buffer Pool Is Needed - The speed gap between disk and CPU is too large, so memory is needed as a cache - When InnoDB accesses table and index data, it caches them here, greatly reducing disk I/O and improving efficiency 3. Buffer Pool Workflow

1. What Is MVCC - Multi-Version Concurrency Control - To allow read-write operations from different transactions to execute concurrently, data is maintained in multiple versions, and transaction visibility determines which data version a transaction should see 2. MVCC Principle - Where to get data from: version chain - Which version of the data to get: ReadView 2.1. Version Chain - InnoDB undo log.md

1. What Is redo log - A log of the MySQL InnoDB storage engine, used for crash recovery - redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions 2. Why redo log Is Needed - According to durability requirements, once a transaction commit completes, it must be persisted to disk. There are two approaches

1. What Is undo log - A log of the MySQL InnoDB storage engine - Mainly records logical changes to data - An INSERT statement corresponds to a DELETE undo log - An UPDATE statement corresponds to an opposite UPDATE undo log - A DELETE statement corresponds to an INSERT undo log - Before actually inserting, deleting, or modifying a record, the corresponding undo log needs to be recorded first

1. What Is a Transaction Database Transactions.md 2. Using Transactions - Start a transaction: begin or start transaction - Commit a transaction: commit - Automatically committed transactions: - SHOW VARIABLES LIKE 'autocommit' - By default, each statement is an independent transaction - Roll back a transaction: rollback 3. Consistency 4. Durability 5. Atomicity 6. Isolation

1. What Is a Tablespace - A tablespace is an abstract concept - Logically - It can be imagined as a pool of pages - Physically - For the system tablespace, it corresponds to one or more actual files in the file system - By default, InnoDB creates a file named ibdata1 with a size of 12M under the data directory, and the size grows automatically - For each file-per-table tablespace, it corresponds to an actual file named table_name.ibd in the file system 2. Tablespace Structure

1. InnoDB - MySQL's default storage engine - InnoDB divides data into pages, each page is 16KB, and pages are the basic unit of interaction between disk and memory - That is, a read reads at least one page and a write writes at least one page 1.1. Index Implementation 1.1.1. Primary-Key Index - The leaf node's data stores the actual data - Because the index stores the actual data, there is only one ibd file 1.1.2. Secondary Index - The leaf node's data stores the primary-key index 2. MyISAM

1. InnoDB Features 1.1. Supports Transactions - InnoDB Transactions.md 1.2. Supports Row-Level Locks - MySQL Locks.md 1.3. Supports MVCC - InnoDB MVCC.md 2. InnoDB Architecture 2.1. Memory Layer 2.1.1. Buffer Pool - Read buffer; its purpose is to improve InnoDB performance, accelerate read requests, and avoid disk I/O for every data access

1. What Is Flush - When the contents of an in-memory data page and an on-disk data page are inconsistent, the memory page is called a dirty page. After the memory data is written to disk, the contents of the memory and disk data pages become consistent, and it is called a clean page - Flush means flushing dirty pages to disk 2. When Flush Is Triggered - redo-log is full - memory is full - when MySQL considers the system idle - when MySQL shuts down normally 3. InnoDB Dirty-Page Flushing Control Strategy

1. SQL Optimization - PostgreSQL explain.md 2. Transactions - PostgreSQL Isolation Levels.md - PostgreSQL MVCC.md

1. Implementation PostgreSQL implements different database isolation levels according to when snapshots are obtained (corresponding to the GetTransactionSnapshot function in the code): - Read Uncommitted/Read Committed: each query obtains the latest snapshot CurrentSnapshotData - Repeatable Read: all queries obtain the same snapshot, which is the snapshot FirstXactSnapshot obtained by the first query - Serializable: implemented using the lock system

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

1. What Are Granularity Locks Database locks can be divided by granularity into row locks, page locks, and table locks. 2. Row Locks 2.1. What They Are Row locks lock data at row granularity. 2.2. Classification By read/write: shared lock (read lock, S lock) and exclusive lock (write lock, X lock). 3. Page Locks 4. Table Locks 5. Metadata Locks 6. Row Locks vs Page Locks vs Table Locks 7. Reference

1. Definition 1NF: Every attribute in a relation that conforms to 1NF cannot be further divided. 2NF: On the basis of 1NF, 2NF eliminates partial functional dependencies of non-prime attributes on a candidate key. 3NF: On the basis of 2NF, 3NF eliminates transitive functional dependencies of non-prime attributes on a candidate key. 2. Reference What Are the First, Second, and Third Normal Forms Actually Saying? - Zhihu

1. What They Are When multiple transactions concurrently access the same data, conflicts will definitely occur. How should such conflicts be handled? There are mainly two ideas: 1. Pessimistic locking mechanism: avoid conflicts from occurring, such as Read/Write Locks and Two-Phase Locking. 2. Optimistic locking mechanism: allow conflicts to occur and detect them afterward, such as MVCC. Described using Java: when doing multithreaded programming in Java, locks are needed. The simplest is ReentranLock: reads and writes are mutually exclusive, and reads are also mutually exclusive. Replace it with ReentrantReadWriteLock: reads are not mutually exclusive, but reads and writes are. Finally replace it with CopyOnWriteList: reads are not mutually exclusive, and reads and writes are also not mutually exclusive. MVCC is somewhat similar to CopyOnWriteList.