MySQL Architecture

 MySQL follow Client-Server Architecture. It has the three following layers:

1. Client

2. Server

3. Storage Layer

Client Layer:

The requests by client to the Server with the help of Client Layer.

The Client make request through  Command Prompt or through GUI  screen like workbench or python or java connectors

by using valid MySQL commands and expressions. If the Expressions and commands are

valid then the output is obtained on the screen.  

Some important stages in client layer are :

Connection Handling : Accept the connection and connect to server based on the allowed connection values.

Creates thread for each connection in backgroud. So we can also called as "Thread handling"

Authentication: It verifies the user name and password

Security: Checks the privileges to execute the requested statement.

Server Layer:

This Layer is also known as “Brain of MySQL Architecture”. When the client give

request instructions to the Server and the server gives the output as soon as the instruction is matched.  

Parser – SQL statement broke into tokens and checks the syntax analysis.

Optimizer – To execute one statement there are many approches to get the result. 

It has to pick correct index and display the result as soon as possible.

Query Cache –

Query Cache stores the complete result set for inputted query statement. Even before Parsing ,

 MySQL Server consult query cache . When client write a query ,

 if the query written by client is identical in the cache then the

 server simply skip the parsing, optimization and even execution,

 it just simply display the output from the cache.

Buffer and Cache –

Cache and will buffer store the previous query or problem asked by user. 

When User write a query then it firstly goes to Query Cache then query cache will check that the same

 query or problem is available in the cache. If the same query is available then it will provide output

 without interfering Parser, Optimizer.

Table Metadata Cache –

The metadata cache is a reserved area of memory used for tracking information on databases, indexes, or objects. 

The greater the number of open databases, indexes, or objects, the larger the metadata cache size.

Key Cache –

A  key cache  is an index entry that uniquely identifies an object in a cache. 

Storage Layer: 

The way of data storage defines hear. MySQL has any storage engines are available. Based on the storage the ways tables will get store in the file system/drive.

Mostly used storage engines are : Innodb and MyISMaa