MySQL Architecture-01(Article -04)

Share via:

Post Views: 0

MySQL Architecture

When a query comes to MySQL from Client program it will go to Query parsing (hear it will check for SQL syntax by checking every character in SQL query and generate SQL_ID for each SQL query.) then it will sent to Optimization (Creates efficient query execution plan as per the storage engine) once execution plan is ready it will send it to Execution in execution layer it has Storage Engine implementation in storage engine it use inbuilt storage engines such as MyISAM, InnoDB, Federated, Mrg_MyISAM, Blackhole, CSV, Memory, Archive, Performance_schema each storage engine is for specific purpose.

QUERY PARSING, OPTIMIZATION AND EXECUTION :

The Brains of the MySQL server are :

PARSING :

Parsing is responsible for deconstructing the requested SQL statement.

OPTIMIZATION :

Optimization is responsible for finding the optimal execution plan for the query.

EXECUTING :

It is responsible for executing the optimized path for the SQL command passed through the parser and execution.

Query caching :

The query cache is a fast in memory store to quickly look up the result set of a particular SELECT statement.

Check the databases available in MySQL.

mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

mysql>show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

Let’s create a test databases.

mysql> create database testEngines;
Query OK, 1 row affected (0.00 sec)

1 2	mysql> create database testEngines; Query OK, 1 row affected (0.00 sec)

Now check available databases.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testEngines |
+--------------------+
5 rows in set (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| testEngines |

+--------------------+

5 rows in set (0.00 sec)

Connect the testEngines database.

mysql> use testEngines;
Database changed
mysql> select database();
+-------------+
| database() |
+-------------+
| testEngines |
+-------------+
1 row in set (0.00 sec)

mysql> use testEngines;

Database changed

mysql> select database();

+-------------+

| database() |

+-------------+

| testEngines |

+-------------+

1 row in set (0.00 sec)

check the tables under testEngine database.

mysql> show tables;

Empty set (0.00 sec)

mysql> show tables;

Empty set (0.00 sec)

Write now there is no objects under testengine database let”s create object.

mysql> create table testsample(ID INT);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-----------------------+
| Tables_in_testEngines |
+-----------------------+
| testsample |
+-----------------------+
1 row in set (0.00 sec)

mysql> create table testsample(ID INT);

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

+-----------------------+

| Tables_in_testEngines |

+-----------------------+

| testsample |

+-----------------------+

1 row in set (0.00 sec)

To check table information use following command.

mysql> show create table testsample\G;
*************************** 1. row ***************************
Table: testsample
Create Table: CREATE TABLE `testsample` (
`ID` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> show create table testsample\G;

*************************** 1. row ***************************

Table: testsample

Create Table: CREATE TABLE `testsample` (

`ID` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.01 sec)

‘\G’ is used to print result in vertical format.

In last line of table structure there is ENGINE=InnoDB.

In MySQL there is different storage engines to check the engines follow the command.

The default storage engine is InnoDB which supports transactions ,row_level locking,and foreign keys.

If we want to create an object under different engine the use below syntax :

The object testsample1 is created under storage engine called MyISAM.

Query Cache is one of the prominent features in MySQL and a vital part of query optimization. Query cache is a global one shared among the sessions. It caches the select query along with the result set, which enables the identical selects to execute faster as the data fetches from the in memory. It is important to have everything identical, no new comments, spaces, or most significantly differences in the WHERE clause. Basically when you trigger a select query, if it is available in the cache, it fetches from there or it considers the query as a new one and will go to the parser.

InnoDB is now the default storage engine in MySQL, it has the following key features

Transnational support provided by MVCC (Multi Version Concurrency Control)
Row-level locking
Foreign key support
Indexing using clustered B-tree indexes
Configurable buffer caching of both data and indexes
Online non-blocking backup through separate commercial backup program

With the Innodb storage engine you have control of the format and the location of the tablespace, using parameters you can control the path the home directory and if you want to use separate files or a shared tablespace.

MyISAM has three files associated with it, because the three files represent a table they can be simply copied to another server and used, however to avoid corruption you should take down mysql server before copying. The other server must have the same endian format as the source server, so you cannot copy from linux X86 server to a Sparc server for instance.

Non-transactionalMyISAM has the following features

No foreign key support
FULLTEXT indexes for text matching
No data cache
Index caches can be specified by name
Implements both HASH and BTREE indexes
Table level locking
Very fast read activity, suitable for data warehouses
Compressed data (with myisampack)
Online backup with mysqlhotcopy
Maximum of 64 indexes per table

MEMORY storage engine creates a table in memory only, this allows for extremely fast data access, however the drawback is that data is not persisted across reboots, also if you replicate this table to a slave server, it will also truncate the slave table as well even though it did not restart. If rows are deleted from a memory table you must use either alter table or optimize table to defrag the table and reduce the amount of memory used.

The MEMORY storage has the following features

All data is stored in memory
Non-transactional
No foreign key support
very fast read and write activity due to being entirely in memory
table-level locking
A MEMORY table can include yo to 32 indexes per table
implements both hash and b-tree indexes
tables use a fixed length row storage format and thus cannot be defined with data types of blob or text

Below example show hows how we can create tables and assign it to specific engine tye, be default InnoDB engine is assigned if ENGINE = ‘ ‘ option is used when creating table in MySQL.

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform

Share via:

Note: Please test scripts in Non Prod before trying in Production.