Databases

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