[Huang ah code] getting started with MySQL - 1. SQL execution process

Hello everyone ！ I'm yellow , In view of your enthusiasm for learning , From this day onwards , Will be serialized mysql Knowledge about , Those who need to learn can pay attention to my updated learning , It is estimated that the paid column will be opened later , But at present, it is completely possible to whore for nothing , I hope you cherish ！

First let's look at a picture ：

What we see MySQL It is composed of three layers , They are as follows ：

adjoining course ： Responsible for client and The server End establish connection , The client sends SQL To the server side , Simply put, interaction

SQL layer ： Yes SQL Statement for query processing ;

Storage engine layer ： And database Dealing with documents , Responsible for data storage and reading , There are disks 、 Memory and network .

Let's look at another picture , Here are SQL Statement execution flow ：

In short, it's a word ：SQL sentence → Cache query → Parser → Optimizer → actuator .

Cache query ： The function is mysql8.0 Previous operations , Cache literally means to read the cache as soon as it is available , If you can't get it, please inquire , But now there are redis This kind of cache database , And the database is frequently written , It's a little chicken ribs for us , So from mysql8.0 after , The official has directly abandoned .

Parser ： Equivalent to our English Translation , Specific have ： Syntax analysis 、 Semantic analysis , After all, the machine doesn't understand what we write , They have to translate into something they understand .

Optimizer ： Choose the best execution scheme , So as to improve the execution efficiency , Such as index .

actuator ： It literally means to carry out what we have written sql sentence , Of course, the premise is that your current user has the permission to execute .

In terms of storage engine ,MySQL Has its own unique storage engine , common 、 Commonly used InnoDB and MyISAM, Let me list it in detail ：

InnoDB Storage engine ： It is MySQL 5.5 The default storage engine after version , The biggest feature of the transaction is that it supports 、 Row level locking 、 Foreign key constraints, etc .

MyISAM Storage engine ： stay MySQL 5.5 Before version, it was the default storage engine , Unsupported transaction , Foreign keys are also not supported , The biggest characteristic is fast , Less occupied resources .

Memory Storage engine ： Use system memory as storage medium , In order to get faster response speed . But if mysqld Process breakdown , Then all data will be lost , So we only use it when the data is temporary Memory Storage engine .

NDB Storage engine ： It's also called NDB Cluster Storage engine , It is mainly used for MySQL Cluster Distributed cluster environment , Be similar to Oracle Of RAC colony

Archive Storage engine ： It has a good compression mechanism , For filing , It is compressed when writing is requested , So it's often used as a warehouse .

MySQL What is more unique is that the storage engine is allocated according to the table , Not by Library , This is a relatively humanized point .

For small businesses ,1、2 Species are the most common , But if you want to enter a big factory , The latter three must also be understood and learned , Otherwise, you are still a little white .

Okay , That's all for today's lesson , Next time, we will continue , Coming soon .

I'm yellow , Code of codeword , refund ... refund ... refund ... the ！