Shulou(Shulou.com)06/01 Report--

2.1 MYSQL service structure

MySQL is a typical camp s mode, which is composed of server and client.

Server: mysqld

Client: mysql

Client self-contained programs: mysql,mysqladmin,mysqldump, etc.

2.2 MYSQL connection mode

TCP/IP connection mode

Mysql-uroot-p123456-h IP

Socket connection (also known as network socket connection)

Mysql-uroot-P123-S / tmp/mysql.sock

Sock can specify its own directory in / etc/my.cnf

For example, mysql-uroot-p123456 is connected by socket.

2.3 the startup process of MYSQL

Start the background daemon and generate worker threads

Pre-allocated memory structure for MySQL processing data

2.4Server Program composition of MYSQLD

2.4.1 connection layer

Communication protocol: TCP/IP

Threads: number of connections

Authentication: user name, user password

2.4.2 SQL layer

SQL: structured query language (logical language within the database)

DDL: database definition language

DCL: database control language

DML: database operation language

DQL: data query language

Functions of the SQL layer

1. Judgment

Syntax, sentence, semantics

Statement type: DQL select

2. The database cannot really understand the SQL statement.

You must know clearly which disk the data is on, which file it is, and which page it is on.

Check the authorization of database objects.

3. Parsing (parser)

Parsing sql statements into N execution plans

4. Optimization (optimizer)

After learning oracle, based on the cost algorithm, select the lowest cost from the N execution plan and give it to the "executor".

5. "executor"

Run the execution plan

Generate how to find data on disk

6. The method of data acquisition will be handed over to the lower layer (storage engine layer) for processing.

7. finally, the extracted data is abstracted into a way (table) that the administrator or the user can understand, and the table is displayed in front of the user.

8. Query cache: cache previously queried data. If the table we are querying is a table that changes frequently, the query cache should not be set too large.

SQL processing flow:

2.4.3 Storage engine layer

The storage engine is a server component that acts as a handler for different table types.

Storage engine layer function

Save data, fetch data (read and write to disk, equivalent to intermediary)

Different ways of storing data

Different ways of management:

Transaction (add, delete, change)

Backup and recovery

Advanced features (highly available architecture, read-write separation architecture)

Special features:

Full-text search

Referential integrity

Spatial data processing

The storage engine is used to:

Store data

Retrieve data

Find data by index

Double layer treatment

The upper layer includes a SQL parser and an optimizer

The lower layer contains a set of storage engines

The SQL layer does not depend on the storage engine:

The engine does not affect SQL processing

There are some exceptions.

2.5 MYSQL logical structure (database internal structure)

The logical structure is so that the user can read the data.

A way to manage data.

Object:

The library contains tables and is represented as a catalog in linux

There are column structures and row records in the table, which are represented by multiple files in linux

Line record

Column structure

2.6.The storage mode of MYSQL

The program files are stored in the server installation directory along with the data directory. Program executables and log files are created when various client programs, administrators, and utilities are executed. The first thing that uses disk space is the data directory.

Server log files and status files:

Contains information about the statements processed by the server. Logs can be used for troubleshooting, monitoring, replication, and recovery.

InnoDB log file:

(for all databases) resides at the data directory level.

InnoDB system tablespace:

Contains a data dictionary, undo logs, and buffers.

Each database has a single directory under the data directory (no matter what type of table is created in the database). The database directory stores the following:

Data file:

Data files specific to the storage engine. These files may also contain metadata or index information, depending on the storage engine used.

Format file (.frm):

Contains a description of each table and / or view structure, located in the appropriate database directory.

Trigger:

A named database object associated with a table and activated when a specific event occurs on that table.

The location of the data directory depends on the configuration, operating system, installation package, and distribution. The typical location is / var/lib/mysql.

MySQL stores the system database (mysql) on disk.

Mysql contains information such as users, privileges, plug-ins, help lists, events, time zone implementations, and storage routines.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.