MySQL Architecture-01(Article -04)

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.

Let’s create a test databases.

Now check available databases.

Connect the testEngines database.

check the tables under testEngine database.

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

To check table information use following command.

‘\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

  1. Transnational support provided by MVCC (Multi Version Concurrency Control)
  2. Row-level locking
  3. Foreign key support
  4. Indexing using clustered B-tree indexes
  5. Configurable buffer caching of both data and indexes
  6. 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.

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

Add Comment