Mysql first-day architecture, mysql first-day Architecture

Mysql first-day architecture, mysql first-day Architecture
Architecture

Mysql separates query processing from actual data storage and extraction (storage engine) operations. The overall architecture is as follows:

• Connectors can understand JDBC and connect various mysql clients.
• Connection Pool is used to create user connections, cache Connection threads, and create Connection pools. Authentication
  Modify my. ini to restart to modify the maximum number of connections:

max_connections=200

View the current variable Configuration:

SHOW VARIABLES like 'max_c%';

The following command can also view the information of the current connection:

 SHOW processlist;

Although mysql comes with a connection pool, it is more likely to use a client connection pool.
The basic principle is to cache after a connection is established, and then all the places that call the connection are obtained from the pool after encapsulation
When the connection retrieved from the pool is closed, the connection is not actually closed, but is put back into the pool. This reduces authentication, three-way handshakes, and other operations, and improves performance.

• SQL Interface
  Built-in functions, DML, DDL, stored procedures, views, and triggers are all here.
• Parser
  Parser, mainly has two functions. Verify that the SQL statement is correct. Resolve the SQL statement into a data structure. Only this structure will be used later.
  The functions of the parser are basically the same. For example, the xml parser in Spring also converts the parser into a data structure and then uses the data structure.
• Optimizer
  Optimizer. I remember that the JVM also has an optimizer, which will be reordered and explain the syntax sugar and so on.
  The parser parses the data into a tree and then rewrite the query to determine the reading sequence of the table and select an appropriate index. The cache is checked before resolution query. If hit, there is no parsing optimization and execution process.
  Here is a better description: http://blog.csdn.net/whyangwanfu/article/details/1943021
• Cache & Buffer
  Cache. When the query statement is executed, the cache is checked first. If hit, the cache is returned directly.
• Managerment Service
  Very powerful management functions, monitoring, backup, restoration, images, clusters ....
• Storage Engine
  Storage engine,

Common storage engines

SHOW TABLE STATUS like 'biz_pay_task';

This allows you to view various table information, including the storage engine.

InnoDB

• Suitable for a large number of short-term transactions that are successfully rolled back and rarely rolled back
• Use MVCC to support high concurrency
• Implements four isolation levels. The default REPEATEABLE READ
• Use the gap lock policy to prevent phantom read

MyISAM

• File Separation
• Slow recovery
• Support for GIS, full-text indexing, and Compression
• Table lock, does not support transactions

Memmory

This is Redis.

Infobright

Column-oriented storage engine for Data Warehouses

Select Engine

You can start from the following points:

• Transaction InnoDb required?
• Whether full-text index InnoDB + Sphinx is used
• Backup and restoration of InnoDB
• Log-type, with high insertion performance requirements. With MyISAM, you can copy the slave database at any time for analysis.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.