In-depth understanding of Mysql logical architecture

MySQL is now the database used by most companies and enterprises. The reason for using MySQL is that MySQL is open source software. Some capable companies will modify and adjust the existing MySQL architecture to suit their own business needs.

One reason is that MySQL is free, while Oracle service is relatively expensive. For many start-up companies, it is the most suitable database.

The reason why we need to understand the logical architecture of MySQL is just like when we do development ourselves, we first need to master the layered architecture used by our own system, so that if there is a problem in any link during the development process, it will be easy to troubleshoot. The same is true for understanding the logical architecture of MySQL. When we use MySQL, we don’t know when problems will occur, but when problems occur (such as optimizing query sql, where to start), we can locate the problem more clearly.

1.Connections

We connect to the Mysql service as a client to connect to the Mysql server. Mysql can connect to various programming language platforms

2. Management Services & Utilities

As a management and control tool for MySQL services, such as backup, restore, replication, clustering, etc.

3. Connection Pool

The main function of the connection pool is to provide connection authentication, thread reuse, connection number limit, etc.

4.SQL Interface

Here we mainly receive Mysql DML, DDL statements, as well as stored procedures, views, triggers, etc.

5. Parse

The main thing here is to parse the SQL string passed in. For example, the JDBC programming we first came into contact with is to send the SQL statement of string type to MySQL, and then the parser first performs lexical analysis on the SQL statement to form a syntax tree, and then performs grammatical analysis on the syntax tree to see whether each word segment meets the SQL92 standard. If it does, it will execute the next business processing link.

6. Query Optimizer

The query optimizer analyzes the SQL statement passed to it to determine whether it is the optimal execution statement, mainly focusing on the following three aspects:
* Which index to use (a table has several indexes, choose the one with the strongest filtering power)
* Table connection order for multi-table associations
* The order of WHERE conditions (according to the MYSQL leftmost prefix principle, the order of conditions will be rearranged)

7. Query Cache (Cache & Buffer)

MYSQL will hash each SQL statement and put the hash value into a map. Every time a SQL statement comes, the hash value of the SQL statement will be calculated first to see if they are the same. If they are the same, the SQL statement in memory can be executed without having to parse and optimize the new SQL statement, thus speeding up the execution efficiency.

8. Pluggable Storage Engines

The MSQL storage engine supports pluggable features, that is, users can replace the storage engine they want to use at will, or customize their own storage engine. Here we mainly analyze and compare the INNODB and MYISAM storage engines.

Usually, when it comes to selecting a storage engine, you should mainly choose it based on business needs, and you can refer to their respective characteristics.

Storage engine selection:

InnoDB:

Supports transaction processing, foreign keys, crash recovery capabilities and concurrency control. If you need to have high requirements for transaction integrity (such as banks) and require concurrency control (such as ticket sales), then choosing InnoDB has great advantages. If you need a database with frequent update and deletion operations, you can also choose InnoDB because it supports transaction commit and rollback.

MyISAM:

Inserting data is fast, and space and memory usage are relatively low. If the table is mainly used to insert new records and read records, then choosing MyISAM can achieve high processing efficiency. If the application's integrity and concurrency requirements are relatively low, it can also be used.

MEMORY:

All data is in memory, and data processing speed is fast, but security is not high. If you need fast reading and writing speeds, have low requirements for data security, and do not need persistent storage, you can choose MEMOEY. It has requirements on the size of the table and cannot create a table that is too large. Therefore, this type of database is only used in relatively small database tables.

Here is a simple execution flow chart of MYSQL. With this chart, you will have a clear understanding of the overall execution process of MYSQL.

This is the end of the introduction to the logical architecture of MYSQL. Of course, MYSQL also has a corresponding physical architecture, but this part mainly involves the various log files of MYSQL, as well as the data files and index files of each table, which will be introduced in the following chapters.

The above is the detailed content of in-depth understanding of MySQL logical architecture. For more information about MySQL logical architecture, please pay attention to other related articles on 123WORDPRESS.COM!