MySQL
MySQL is an open-source Relational Database Management System (RDBMS) that enables data storage, management, and querying based on Structured Query Language (SQL). Boasting core advantages of lightweight architecture, high efficiency, cross-platform compatibility, and open-source free access, it serves as a core component of mainstream Web technology stacks such as LAMP (Linux + Apache + MySQL + PHP/Perl/Python) and LNMP (Linux + Nginx + MySQL + PHP/Perl/Python). It is widely used for underlying data storage in various small-to-medium applications and large-scale Internet systems.
I. Background and Fundamentals
1. Development Background
The birth of MySQL stemmed from market demand for lightweight, high-performance open-source databases. In the 1990s, commercial databases (e.g., Oracle, DB2) were expensive and complex to deploy, failing to meet the needs of small and medium-sized enterprises (SMEs) and individual developers.
- In 1995, Swedish programmers Michael Widenius and David Axmark released the first version of MySQL, with its core positioning as a fast, stable, and easy-to-use open-source database.
- In 2008, Sun Microsystems acquired MySQL AB; in 2009, Oracle acquired Sun, and MySQL officially became a product under the Oracle umbrella.
- To avoid the risk of closed-source development, multiple forked versions emerged from the MySQL community, the most renowned being MariaDB (led by Michael Widenius and fully compatible with MySQL).
After decades of development, MySQL has become one of the most popular relational databases worldwide, alongside PostgreSQL, thanks to its open-source ecosystem and high-performance features.
2. Core Fundamentals
- Relational Database Model: MySQL organizes data based on the relational model, where data is stored in the form of tables. Each table consists of rows (records) and columns (fields). Relationships between tables can be established through Primary Keys and Foreign Keys, ensuring data integrity and consistency.
- SQL Language Support: MySQL fully supports standard SQL, including Data Definition Language (DDL) (e.g.,
CREATE,ALTER,DROP), Data Manipulation Language (DML) (e.g.,INSERT,UPDATE,DELETE), Data Query Language (DQL) (e.g.,SELECT), and Data Control Language (DCL) (e.g.,GRANT,REVOKE). SQL serves as the standard interface for user interaction with the database. - ACID Transaction Properties: MySQL supports transaction operations, adhering to the four core properties of Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable data operations in high-concurrency scenarios. Transactions are controlled via statements such as
BEGIN,COMMIT, andROLLBACK. - Open-Source Licensing Model: MySQL adopts a dual-license model — the GPL open-source license (free for personal and open-source projects) and a commercial license (for closed-source commercial projects), balancing the needs of the open-source ecosystem and commercial applications.
II. Components and Technical Principles
MySQL adopts a layered architecture where each layer, from underlying storage to upper-layer interfaces, has distinct responsibilities. Meanwhile, it achieves high-performance data reading and writing through core technology optimizations.
1. Core Architectural Components
MySQL’s architecture is divided into three layers: client layer, service layer, and storage engine layer. With clear layers and decoupled design, it supports flexible scalability.
| Layer | Core Components | Function Description |
|---|---|---|
| Client Layer | Various client tools (mysql command line, Workbench, Navicat) | Responsible for interacting with users, sending SQL commands to the server, receiving and displaying query results; supports connection methods such as TCP/IP protocol and local sockets. |
| Service Layer (Core Layer) | Connection Manager, Query Cache, Parser, Optimizer, Executor | 1. Connection Manager: Establishes and manages connections between clients and the server, reuses threads through thread pools to reduce connection overhead. 2. Query Cache: Caches results of frequently used query statements; if the same query is received subsequently, results can be returned directly (removed in MySQL 8.0 due to low hit rates and high maintenance costs in practical scenarios). 3. Parser: Parses SQL statements into an Abstract Syntax Tree (AST) and verifies SQL syntax validity. 4. Optimizer: Generates an optimal execution plan based on a cost model (e.g., selecting indexes, adjusting join order). 5. Executor: Invokes storage engine interfaces according to the execution plan to perform data read/write operations. |
| Storage Engine Layer (Underlying Layer) | InnoDB, MyISAM, Memory, etc. | Responsible for actual data storage and retrieval, serving as the core underlying component of MySQL. MySQL supports a pluggable storage engine architecture, allowing users to select different engines based on business requirements. |
2. Mainstream Storage Engines and Principles
Storage engines are a core differentiating feature of MySQL, with significant differences in functionality and performance between engines. InnoDB and MyISAM are the two most classic ones.
| Storage Engine | Core Features | Applicable Scenarios | Technical Principles |
|---|---|---|---|
| InnoDB | Supports transactions, foreign keys, and row-level locking; strong crash recovery capability; clustered index storage | High-concurrency read/write scenarios and transaction-sensitive systems (e.g., e-commerce orders, financial transactions) | 1. Adopts a clustered index structure where data rows are stored together with primary key indexes, ensuring high query efficiency. 2. Implements transaction atomicity and durability through undo logs and redo logs. 3. Supports row-level locking, which only locks affected rows during concurrent modifications to reduce lock conflicts. |
| MyISAM | Does not support transactions or foreign keys; uses table-level locking; fast query speed; low storage space consumption | Read-only or read-heavy scenarios (e.g., log statistics, data reports) | 1. Adopts a non-clustered index structure where indexes and data are stored separately. 2. Supports full-text indexing, suitable for text retrieval. 3. Table-level locking mechanism is simple but leads to poor performance in high-concurrency write scenarios. |
3. Core Technical Principles
Index Mechanism: Indexes are the core of improving MySQL query performance, essentially ordered data structures (e.g., B+ trees, hash tables). The mainstream index type in MySQL is the B+ tree index:
- All data is stored in leaf nodes, while non-leaf nodes only store index values, reducing disk I/O times.
- Supports range queries, sorting, and other operations, suitable for most business scenarios.
- Common index types include primary key indexes, unique indexes, normal indexes, and composite indexes.
Locking Mechanism: Used to resolve data competition in high-concurrency scenarios, divided into table-level locking (locks entire tables with low overhead but low concurrency) and row-level locking (locks data rows with high overhead but high concurrency). InnoDB’s row-level locking is implemented based on indexes; without indexes, it degrades to table-level locking.
Transaction Isolation Levels: MySQL defines 4 transaction isolation levels to resolve issues such as dirty reads, non-repeatable reads, and phantom reads in concurrent transactions:
- Read Uncommitted: The lowest level, allowing uncommitted data to be read.
- Read Committed: Only allows committed data to be read, resolving dirty reads.
- Repeatable Read: MySQL’s default level, ensuring consistent results for multiple reads within the same transaction, resolving non-repeatable reads.
- Serializable: The highest level, where transactions are executed serially, resolving all concurrency issues but with the worst performance.
Logging System: MySQL ensures data reliability and recoverability through multiple log types:
- Redo Log: Records modifications to data pages, used for data recovery after crashes.
- Undo Log: Records the state of data before modification, used for transaction rollbacks.
- Binary Log (binlog) : Records all data modification operations, used for master-slave replication and data backup.
III. Application Scenarios
With its lightweight, efficient, and open-source features, MySQL meets data storage needs across scenarios ranging from personal projects to large-scale Internet systems:
- Web Application Development: As a core component of LAMP/LNMP stacks, it is widely used in various Web systems such as e-commerce, social networks, and information platforms. For example, product information, user data, and order records of e-commerce platforms are all stored in MySQL.
- Small-to-Medium Enterprise Applications: Compared with commercial databases, MySQL has low deployment costs and is easy to maintain, making it suitable for SME business systems (e.g., Customer Relationship Management (CRM), Enterprise Resource Planning (ERP)).
- Cloud Computing and Microservices: Supports master-slave replication and read-write separation architectures, and can quickly deploy clusters on cloud servers to meet distributed data storage needs in microservice architectures. For example, the master database handles data writes while slave databases handle data reads, improving system concurrency capabilities.
- Data Analysis and Reporting: Combined with the powerful query capabilities of SQL, it can be used to store and analyze structured data and generate business reports. For large-scale data analysis scenarios, it can be used with big data frameworks such as Hadoop and Spark.
- Embedded and Mobile Applications: MySQL provides a lightweight version (e.g., MySQL Embedded), which can be embedded into embedded devices or mobile applications to achieve local data storage.
IV. Summary
MySQL is a feature-rich, high-performance open-source relational database. Its layered architecture and pluggable storage engine design ensure flexibility and scalability. From small personal developer projects to core business systems of large Internet enterprises, MySQL can provide stable and efficient data storage solutions. Mastering the core principles and usage of MySQL is an essential skill for professionals in back-end development, data management, and related fields.