Plan du site  
pixel
pixel

Articles - Étudiants SUPINFO

MySQL architecture

Par Zhihao WANG Publié le 04/04/2020 à 04:49:42 Noter cet article:
(0 votes)
En attente de relecture par le comité de lecture

MySQL architecture

We can see from the picture that the top layer of MySQL is Connector. The following server is composed of Connection Pool, Enterprise Management Services and Utilities, SQL interface, Parser, Optimizer, Caches and Buffer, Pluggable Storage Engines and File System.

Connection Pool: Every time we establish a connection needs to spend a lot of time, so the role of the connection pool is to cache these connections. Next time we can directly use the established connection to improve the server performance.

Enterprise Management Services and Utilities: System management and control tools, such as backup and recovery, replication and cluster

SQL Interface: Receive the user’s sql statement and return the result the user query, such as select from …

Parser: When the command is passed to the parser, it will be verified and parsed by the parser. The parser is implemented by Lex and YACC and is a very long script. The main function is to decompose the SQL statement into a data structure and pass this structure to the next step. The transfer and processing of the SQL statement is based on this structure. If an error occurs in the decomposition structure, it means that this SQL statement is unreasonable

Caches: Query cache. If the query cache has the correct query results, the query statement can directly go to the query cache to get the data. This caching mechanism is composed of a series of small caches. Such as table cache, record cache, key cache, permission cache, etc.

MySQL memory structure

The memory in MySQL is divided into two parts: global buffer and thread buffer.

global buffer: Data page, Index page, Insert buffer, Lock info, Log buffer, etc.

thread buffer: Master thread, IO thread, Page cleaner thread

CheckPoint: The buffer pool is designed to coordinate CPU speed and disk speed. Therefore, when updating or deleting, the data in the memory is directly changed. The redo log is written first, then the data in the memory pool is modified, and finally refreshed to the disk regularly.

MySQL file structure

Parameter file: When starting the MySQL instance, specify some initialization parameters, such as: buffer pool size, database file path, user name and password.

Log file: such as error log, binary log, slow query log, query log, etc. Check the contents of the error log by “show variables like error log”

Socket file: the file required when connecting with a UNIX domain socket.

pid file: the process ID file of the MySQL instance.

Table structure file: used to store MySQL table structure definition file, The file named with the .frm suffix is a table structure file, regardless of the storage engine type. All tables will generate a .frm file

Storage engine files: The storage engine is storing data such as records and indexes.

Shared tablespace: Shared tablespace files are named after .ibdata *; In a shared table space, all data of innodb is stored in a single table space, and this table space can be composed of many files, and a table can span multiple files Exists, so its size limit is no longer a file size limit, but its own limit. As can be seen from Innodb's official documentation, the maximum table space limit is 64TB, which means that Innodb's single table limit is basically around 64TB. Of course, this size includes all other indexes and other related data of this table.

The shared tablespace mainly stores double write and undo log (undo log doesn’t have independent tablespace and needs to be stored in the shared tablespace

Independent tablespace: Each table has its own independent tablespace for storing data and indexes.

The advantages of using Independent tablespaces:

If soft links are used to allocate large tables to different partitions, it is easy to manage data files

Easy to monitor and solve the problem of IO resource usage;

Easy to repair and restore damaged data;

Independent of each other, will not affect other innodb tables;

Export and import are only for a single table, not the entire shared table space;

Solve the limitation of single file size;

For a large number of delete operations, it is easier to reclaim disk space;

Less debris, easy to organize optimize table;

Easy security audit;

Easy to backup

If you set innodb_file_per_table after the innodb table has been created, the data will not be migrated to a separate table space, but the sequel uses the shared table space before. Only newly created tables will be separated into their own tablespace files.

Ecrit avec Supinfo-Articles.com

A propos de SUPINFO | Contacts & adresses | Enseigner à SUPINFO | Presse | Conditions d'utilisation & Copyright | Respect de la vie privée | Investir
Logo de la société Cisco, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société IBM, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sun-Oracle, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Apple, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sybase, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Novell, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Intel, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Accenture, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société SAP, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Prometric, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Toeic, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo du IT Academy Program par Microsoft, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management

SUPINFO International University
Ecole d'Informatique - IT School
École Supérieure d'Informatique de Paris, leader en France
La Grande Ecole de l'informatique, du numérique et du management
Fondée en 1965, reconnue par l'État. Titre Bac+5 certifié au niveau I.
SUPINFO International University is globally operated by EDUCINVEST Belgium - Avenue Louise, 534 - 1050 Brussels