Databases

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