Detailed introduction to mysql theory and basic knowledge

This article will give you a detailed introduction to mysql theory and basic knowledge. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

mysql architecture

1. Network connection layer

Client Connectors: Provided Built-in support for MySQL server. Currently, almost all mainstream server-side programming technologies are supported, such as common Java, C, Python, .NET, etc., which establish connections with MySQL through their respective API technologies

2. Service layer (MySQL Server)

The service layer is the core of MySQL Server and mainly includes six parts: system management and control tools, connection pool, SQL interface, parser, query optimizer and cache.

Connection Pool: Responsible for storing and managing the connection between the client and the database. One thread is responsible for managing one connection.

System management and control tools (Management Services & Utilities): such as backup and recovery, security management, cluster management, etc.

SQL Interface (SQL Interface): used to accept various types of data sent by the client SQL command and returns the results that the user needs to query. Such as DML, DDL, stored procedures, views, triggers, etc.

Parser (Parser): Responsible for parsing the requested SQL to generate a "parse tree". Then further check whether the parse tree is legal according to some MySQL rules.

Query Optimizer (Optimizer): When the "parse tree" passes the parser grammar check, it will be handed over to the optimizer to convert it into an execution plan, and then interact with the storage engine.

select uid,name from user where gender=1;

Select-》Projection-》Join strategy

1)select first selects based on the where statement, not a query Extract all the data and then filter it

2) The select query performs attribute projection based on uid and name, not all fields

3) Connect the previous selection and projection to finally generate the query result

Cache (Cache&Buffer): The caching mechanism is composed of a series of small caches. For example, table cache, record cache, permission cache, engine cache, etc. If the query cache has a hit query result, the query statement can directly fetch data from the query cache.

3. Storage Engine Layer (Pluggable Storage Engines)

The storage engine is responsible for the storage and extraction of data in MySQL, and interacts with the underlying system files. The MySQL storage engine is plug-in. The query execution engine in the server communicates with the storage engine through an interface. The interface shields the differences between different storage engines. There are many storage engines now, each with its own characteristics. The most common ones are MyISAM and InnoDB.

4. System File Layer (File System)

This layer is responsible for storing database data and logs on the file system and completing the interaction with the storage engine. , is the physical storage layer of files. Mainly includes log files, data files, configuration files, pid files, socket files, etc.

Log file

Error log(Error log)

Enabled by default, show variables like '%log_error%'

General query log ( General query log)

Records general query statements, show variables like '%general%';

Binary log (binary log)

Records changes performed on the MySQL database operation, and records the occurrence time and execution time of the statement; however, it does not record select, show, etc. SQL that does not modify the database. Mainly used for database recovery and master-slave replication.

show variables like '%log_bin%'; //Whether to enable it

show variables like '%binlog%'; //Parameter view

show binary logs;// View the slow query log file

Query log (Slow query log)

Records all query SQL whose execution time has expired. The default is 10 seconds.

show variables like '%slow_query%'; //Whether to enable it

show variables like '%long_query_time%'; //Duration

Configuration file

Used to store all MySQL configuration information files, such as my.cnf, my.ini, etc.

Data file

db.opt file: records the default character set and verification rules used by this library.

frm file: stores metadata (meta) information related to the table, including definition information of the table structure, etc. Each table will have a frm file.

MYD file: It is dedicated to the MyISAM storage engine and stores the data of the MyISAM table. Each table will have a .MYD file.

MYI file: It is dedicated to the MyISAM storage engine and stores index-related information of the MyISAM table. Each MyISAM table corresponds to a .MYI file.

ibd file and IBDATA file: store InnoDB data files (including indexes). The InnoDB storage engine has two table space modes: exclusive table space and shared table space. Exclusive table spaces use .ibd files to store data, and each InnoDB table corresponds to one .ibd file. Shared table spaces use .ibdata files, and all tables use one (or multiple, self-configured) .ibdata files.

ibdata1 file: system table space data file, which stores table metadata, Undo logs, etc.

ib_logfile0, ib_logfile1 files: Redo log log files.

pid file

The pid file is a process file of the mysqld application in the Unix/Linux environment. Like many other Unix/Linux server programs, it stores its own process id.

socket file

The socket file is also available in the Unix/Linux environment. Users can directly use Unix Socket to connect to the client in the Unix/Linux environment without going through the TCP/IP network. Connect to MySQL.

InnoDB and MyISAM

Transactions and foreign keys

InnoDB supports transactions and foreign keys, with security and integrity, suitable for a large number of Insert or update operation

MyISAM does not support transactions and foreign keys. It provides high-speed storage and retrieval, suitable for a large number of select query operations

Lock mechanism

InnoDB supports row-level locking and locks specified records. Locking is implemented based on index.

MyISAM supports table-level locking, locking the entire table.

Index structure

InnoDB uses a clustered index (clustered index). The index and records are stored together, caching both the index and the records.

MyISAM uses non-clustered index (non-clustered index), and the index and record are separated.

Concurrency processing capability

MyISAM uses table locks, which will lead to a low concurrency rate of write operations, no blocking between reads, and blocking of reads and writes.

InnoDB read and write blocking can be related to the isolation level, and multi-version concurrency control (MVCC) can be used to support high concurrency

Storage files

InnoDB The table corresponds to two files, a .frm table structure file and an .ibd data file. InnoDB tables support a maximum of 64TB;

The MyISAM table corresponds to three files, a .frm table structure file, a MYD table data file, and a .MYI index file. Starting with MySQL 5.0, the default limit is 256TB.

The difference between Redo Log and Binlog

Redo Log is a function of the InnoDB engine, while Binlog is a built-in function of MySQL Server and is recorded in binary files.

Redo Log is a physical log, which records the update status content of the data page. Binlog is a logical log, which records the update process.

Redo Log is written in a circular manner, the log space size is fixed, Binlog is written additionally, after one is written, the next one is written, and it will not be overwritten.

Redo Log can be used for automatic recovery of transaction data after the server is down abnormally, and Binlog can be used for master-slave replication and data recovery. Binlog does not have automatic crash-safe capabilities.

In the application, multiple indexes can be added to the slave database to optimize queries. These indexes in the main database can be omitted to improve writing efficiency.

Reading and writing separation scheme

1Read immediately after writing

After writing to the database, the read operation will be completed within a certain period of time Go to the main library, and then read operations access the slave library.

2 Secondary Query

First go to the slave database to read the data. If it cannot be found, go to the main database to read the data. This operation will easily return the read pressure to the main library. In order to avoid malicious attacks, it is recommended to encapsulate the database access API operations, which is beneficial to security and low coupling.

3According to special business processing

Adjust according to business characteristics and importance. For example, important business data reading and writing with high real-time requirements can be placed in the main database. For secondary businesses that do not require high real-time performance, reading and writing can be separated, and queries can be made from the database.

Related recommendations: "mysql tutorial"

The above is the detailed content of Detailed introduction to mysql theory and basic knowledge. For more information, please follow other related articles on the PHP Chinese website!