Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you Mysql logical architecture example analysis, I believe most people still do not know how, so share this article for your reference, I hope you have a lot of harvest after reading this article, let's go to understand it together!

1. Overall framework diagram

MySQL is somewhat different from other databases in that its architecture can be applied and works well in a variety of different scenarios. It is 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 retrieval. This architecture allows you to select the right storage engine based on your business requirements and actual needs.

Introduction to each layer:

1.1 connection layer

At the top are some client and connection services, including native sock communications and most tcp/ip-like communications implemented based on client/server tools. It mainly completes some similar connection processing, authorization authentication, and related security schemes. The concept of thread pool is introduced at this layer to provide threads for clients who pass authentication security access. SSL based secure links can also be implemented at this layer. The server also verifies that it has operational privileges for each client that has secure access.

1.2 service layer

1.3. engine layer

The storage engine layer is really responsible for storing and extracting data in MySQL, and the server communicates with the storage engine through APIs. Different storage engines have different functions, so we can choose according to our actual needs.

1.4. storage layer

The data storage layer stores data on file systems running on bare devices and interacts with storage engines.

2. show profile

Use show profile to view sql execution cycle!

2.1 Open profile

View profile open: show variables like '%profiling%'

If it is not open, you can execute set profiling=1 open!

2.2 with profile

Execute the show prifiles command to view the last few queries.

According to Query_ID, you can further execute show profile cpu,block io for query Query_id to see the specific execution steps of sql.

2.3 General query flow

The query flow of mysql is roughly:

mysql client establishes a connection with mysql server through protocol, sends query statement, checks query cache first, if hit, returns result directly, otherwise parses statement, that is, before parsing query, server will access query cache first-it stores SELECT statement and corresponding query result set. If a query result is already cached, the server will not parse, optimize, or execute the query. It simply returns the cached results to the user, which greatly improves the performance of the system.

Syntax parser and preprocessing: First mysql parses SQL statements through keywords and generates a corresponding "parse tree." The mysql parser will validate and parse the query using mysql syntax rules; the preprocessor will further check whether the parsed number is valid according to some mysql rules.

Query Optimizer When the parse tree is considered legitimate, it is converted into an execution plan by the optimizer. A query can be executed in many ways, all returning the same result. The optimizer's job is to find the best execution plan among these.

Then, mysql uses BTREE indexes by default, and a general direction is: no matter how much SQL is tormented, at least for now, mysql only uses one index in the table at most.

2.4 SQL execution order

Handwritten order:

Actual order of execution:

With the update of Mysql version, its optimizer is also continuously upgraded. The optimizer will analyze the different performance consumption caused by different execution orders and dynamically adjust the execution order. The following is a frequently occurring query sequence:

2.5 MyISAM and InnoDB

show engines: View all database engines

show variables like '%storage_engine%' View default database engine

The above is "Mysql logical architecture sample analysis" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

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.