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

An overview of the Mysql logical architecture

Compared with other databases, MySQL is a little different, and its architecture can be applied and work well in a variety of different scenarios. Mainly reflected in the architecture of the storage engine, the plug-in storage engine architecture separates query processing from other system tasks and data storage and extraction. This architecture can choose the appropriate storage engine according to the needs of the business and the actual needs.

Controller layer:

Connectors: connection layer, c, java, etc. Connect mysql

The business logic is processed as follows:

Connection Pool: connection layer c3p0 connection pool, etc.

Manager Service util: backup, disaster recovery, cluster, etc.

Sql interface: stored procedures, view triggers

Parser: query the permissions of things and objects

If it starts with select, it is considered a read operation, and what begins with insert is a write operation.

The loading of sql statements starts with from and is converted into a form that mysql can read.

Optimizer:mysql optimizer, after the previous transformation, here mysql will be optimized to what mysql thinks is the best way

It is not our artificial optimization.

Caches & buffers: buffering

Pluggable storage engine

Plugin storage engine

Most use myism and innodb.

File storage layer computer hardware

File system

File & logs

1. Connection layer

The top layer is a number of client and connection services, including local sock communications and most tcp/ip-like communications implemented based on client / server tools. Mainly complete some similar connection processing, authorization authentication, and related security schemes. The concept of thread pool is introduced on this layer to provide threads for clients with secure access through authentication. SSL-based secure links can also be implemented on this layer. The server also verifies the operational permissions it has for each client that has secure access.

two。 Service layer

2.1 Management Serveices & Utilities: a system management and control tool

2.2 SQL Interface: SQL API

Accept the user's SQL command and return the results that the user needs to query. For example, select from calls SQL Interface.

2.3 Parser: parser

The SQL command is validated and parsed by the parser when it is passed to the parser.

2.4 Optimizer: query optimizer.

The SQL statement uses the query optimizer to optimize the query before the query.

It can be understood with an example: select uid,name from user where gender= 1

The optimizer decides whether to project or filter first.

2.5 Cache and Buffer: query cache.

If the query cache has a hit query result, the query statement can directly fetch data from the query cache.

This caching mechanism consists of a series of small caches. Such as table cache, record cache, key cache, permission cache, etc.

The cache is responsible for reading and the buffer is responsible for writing.

3. Engine layer

The storage engine layer, the storage engine is really responsible for the storage and extraction of data in MySQL, and the server communicates with the storage engine through API. Different storage engines have different functions, so we can choose according to our actual needs. MyISAM and InnoDB will be introduced later.

4. Storage layer

The data storage layer mainly stores the data on the file system running on the bare device and completes the interaction with the storage engine.

General overview

Query flow chart:

First, the query flow of mysql is roughly as follows:

The mysql client connects with the mysql server through the protocol, sends query statements, checks the query cache first, and returns the result directly if it hits (the same sql can hit), otherwise it parses the statement, that is, before parsing the query, the server will access the query cache (query cache), which stores the SELECT statement and the corresponding query result set. If the result of a query is already in the cache, the server will no longer parse, optimize, or execute the query. It only returns the results in the cache to the user, which will greatly improve the performance of the system.

Syntax parser and preprocessing: first, mysql parses the SQL statement through keywords and generates a corresponding "parsing tree". The mysql parser validates and parses the query using MySQL syntax rules; the preprocessor further checks whether the parsed number is legal based on some mysql rules.

The query optimizer is considered legal when the parsing tree is considered legal, and the optimizer converts it into an execution plan. A query can be executed in many ways, all of which return the same result. The role of the optimizer is to find the best execution plan.

Then, mysql uses the BTREE index by default, and a general direction is that no matter how much you mess with sql, at least for now, mysql uses at most one index in the table.

Mysql storage engine

View command

1 how to view it with command

See what storage engine your mysql now offers:

Mysql > show engines

By default, InnoDB supports things that support foreign keys and is a row lock.

Take a look at your mysql's current default storage engine:

Mysql > show variables like'% storage_engine%'

The default is InnoDB

The current one is also InnoDB.

Brief introduction of each Engin

1. InnoDB storage engine

InnoDB is the default transactional engine for MySQL and is designed to handle a large number of short-term (short-lived) transactions. Unless there is a very specific reason to use another storage engine, the InnoDB engine should be given priority. Row level lock, suitable for high concurrency situations

2. MyISAM storage engine

MyISAM provides a large number of features, including full-text indexing, compression, spatial functions (GIS), etc., but MyISAM does not support transactions and row-level locks (myisam changes the table will lock the entire table), there is no doubt that it can not be safely recovered after a crash.

3. Archive engine

The Archive storage engine only supports INSERT and SELECT operations, and does not support indexes before MySQL5.1.

Archive table is suitable for log and data acquisition applications. Suitable for low traffic big data and other situations.

According to the English test results, the Archive table is about 75% smaller than the MyISAM table and 83% smaller than the InnoDB table that supports transaction processing.

4. Blackhole engine

The Blackhole engine does not implement any storage mechanism, and it discards all inserted data without any saving. However, the server logs the Blackhole table, so it can be used to copy data to the standby database, or simply log it. However, this application will encounter a lot of problems, so it is not recommended.

5. CSV engine

The CSV engine can process ordinary CSV files as MySQL tables, but does not support indexing.

The CSV engine can be used as a mechanism for data exchange and is very useful.

The data stored in CSV can be read directly in the operating system, using a text editor, or excel.

6. Memory engine

Using Memory tables is very useful if you need to access data quickly and that data will not be modified and it doesn't matter if you lose it after a restart. The Memory table is at least one order of magnitude faster than the MyISAM table. (it is faster to use a professional in-memory database, such as redis)

7. Federated engine

The Federated engine is an agent that accesses other MySQL servers, and although it seems to provide a good cross-server flexibility, it often causes problems, so it is disabled by default.

MyISAM and InnoDB (focus)

Contrast item MyISAMInnoDB main foreign key does not support transactions do not support row table lock table lock, even if you operate a record will lock the entire table, not suitable for high concurrency operation row lock, operation only lock a row, do not affect other rows, suitable for high concurrency cache only cache index, do not cache real data not only cache index, but also cache real data, high memory requirements And memory size has a decisive impact on performance, tablespace small concerns performance transactions default installation of YY user table default use of NY built-in system tables use YN

Innodb indexing using B+TREE myisam indexing using b-tree

The primary key of innodb is clustered index, and the efficiency of adding, deleting and modifying based on clustered index is very high.

Which do you use, Alibaba or Taobao?

Percona has made improvements to the MySQL database server, which is significantly better than MySQL in terms of function and performance. This version improves the performance of InnoDB under high load, provides some very useful performance diagnostic tools for DBA, and has more parameters and commands to control server behavior.

The company has a new storage engine called xtradb that completely replaces innodb and does better in terms of performance and concurrency.

Most of Alibaba's mysql databases actually use percona prototypes to modify them.

AliSql+AliRedis

The above is the introduction of the Mysql logical architecture and the detailed interpretation and integration of the mysql storage engine. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!

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.