Database and database instance

In the study of MySQL, there are two very confusing concepts, namely database and database instance.

In MySQL, databases and database instances are defined as follows:

  • 1. A collection of stored data;

  • Database instance: A collection of operating databases.

As the above definition makes clear, databases are used to store data, and database instances are used to manipulate data

From an operating system perspective, a database instance is represented as a single process with multiple threads.

In a non-clustered database architecture, there is a one-to-one relationship between a database and a database instance. In a database cluster, there may be multiple database instances operating a database, that is, a many-to-one relationship.


MySQL database

For MySQL, although it has gone through several iterations, each iteration is based on the MySQL base.

The MySQL base consists of the following modules:

  1. MySQL Connectors

  2. Management Service & Utilities components

  3. Connection Pool component

  4. SQL Interface Components

  5. Query the Parser component (Parser)

  6. Optimizer components

  7. Caches & Buffers

  8. Pluggable Storage Engines

  9. Physical File System


1.MySQL Connectors

Connectors Component is an interaction component provided by MySQL. Languages such as Java,.NET, and PHP can use this component to manipulate SQL statements and interact with SQL.

2. Management Service & Utilities

Provides integrated management of MySQL, such as Backup, Recovery, and Security

3. Connection Pool

Responsible for monitoring various requests from clients to MySQL Server, receiving requests, forwarding requests to the target module.

Each client request that successfully connects to MySQL Server will be created or assigned a thread, which is responsible for the communication between the client and MySQL Server, receiving the commands sent by the client, passing the result information of the Server, etc.

4.SQL Interface

Receive user SQL commands, such as DML,DDL, and stored procedures, and return the final results to the user.

5. Query the Parser component (Parser)

First, analyze the validity of the SQL command syntax, and try to decompose the SQL command into a data structure. If the decomposition fails, the system prompts that the SQL statement is unreasonable.

6. Optimizer components

Optimize and analyze SQL commands according to standard procedures.

7. Caches & Buffers

Caching and buffering components

MySQL storage engine

1. What is MySQL storage engine?

MySQL belongs to a relational database, and the storage of a relational database is in the form of tables, the creation of tables, data storage, retrieval, update and so on are completed by the MySQL storage engine, which is also the MySQL storage engine plays an important role in MySQL.

Readers who have studied SQL Server and Oracle may know that there is only one storage engine for these two databases, while MySQL has a wide variety of storage engines, such as MyISAM storage engine, InnoDB storage engine and Memory storage engine.

MySQL has multiple storage engines because MySQL is open source. MySQL storage engine from the type, roughly can be attributed to the official storage engine and third-party storage caused.

The open source nature of MySQL allows third parties to develop storage engines suitable for their own business needs based on the MySQL skeleton.

2. Function of MySQL storage engine

MySQL storage engine plays an important role in MySQL. Its relatively important role can be summarized as follows:

Function 1: Manage table creation, data retrieval, index creation, etc

Function 2: Customize the storage engine.

3. Types of MySQL engines

Different types of storage engines use different storage engine table mechanisms. MySQL storage engines can be classified into official storage engines and third-party storage engines.

Currently, there are many MySQL storage engines, such as MyISAM storage engine, InnoDB storage engine, NDB storage engine, Archive storage engine, Federated storage engine, Memory storage engine, Merge storage engine, Parter storage engine, Community storage engine, Custom storage engine, and other storage engines.

Among them, common storage engines include InnoDB storage engine, MyISAM storage engine and Momery storage engine.

4. Comparison of several typical MySQL storage engines

9. Physical File System

Actual storage of MySQL database files and some log files, such as Linux, Unix,Windows, etc.


Three, a query flow chart





4. Refer to the literature

[01] Authoritative Guide to MySQL (USA) by Paul Dubois, Translated by Yang Tao, Yang Xiaoyun, Wang Qun et al

[02] InnoDB Storage Engine by Jiang Chengyao

[03] Alan Beaulieu. SQL Learning Guide

Author: Alan

Source: https://www.cnblogs.com/wangjiming/p/10410904.html


End

Long press the qr code below to pay immediate attention to [Tanuki technology Nest]

Top technical experts from Alibaba, JD.com, Meituan and Bytedance are in charge

Create a “temperature” technology nest for IT people!