Understanding MySQL Architecture

The architecture of the world’s most popular open source database system is very important for the Information Technology people. There are many reasons for MySQL’s popularity around the world, but one of the main reasons is its architecture, while there are many big players such as Oracle, Microsoft SQL and DB2, MySQL’s architecture makes it as unique and preferred choice for most of the developers. In this article, we are going to discuss about of the internal architecture of the MySQL relational database management system. The article is for novice database administrators, database developers, software developers and those who are interested to work with MySQL database.


Major components:

The MySQL architecture describes how the different components of a MySQL system relate to one another. The MySQL architecture is basically a client – server system. MySQL database server is the server and the applications which are connecting to MySQL database server are clients. The MySQL architecture contains the following major components.

MySQL Architecture
MySQL ARCHITECTURE

Application Layer:

This layer is the top most layers in MySQL architecture; you can see this same layer in many of the client – server architecture. This layer includes some of the services which are common to most of the client – server applications, some of the services are given below:
  • Connection Handling.
  • Authentication.
  • Security.

Connection Handling:


When a client connects to server, the client gets its own thread for its connection. All the queries from that client executed within that specified thread. The thread is cached by the server, so they don’t need to created and destroyed for each new connection.

Authentication:

Whenever a client connects to a MySQL server, the server performs the authentication in the server side. The authentication is based on the username, host of the client and password of the client user.


Example:

root@localhostroot          : Username of the client.
localhost  : host name, from where it is originated.

Security:

After the client gets connected successfully to MySQL server, the server will check whether that particular client has the privileges to issue certain queries against MySQL server.

Example: 

mysql> show privileges \G
*************************** 1. row ***************************
  Privilege: Alter
  Context: Tables
  Comment: To alter the table
*************************** 2. row ***************************
  Privilege: Alter routine
  Context: Functions,Procedures
  Comment: To alter or drop stored functions/procedures

MySQL Server layer:

This layer takes care of all the logical functionalities of the MySQL relational database management system. The brain of the MySQL server is resides in this layer. The logical layer of the MySQL is divided into various sub components, which are given below:
  • MySQL services and utilities.
  • SQL Interface.
  • SQL Parser.
  • Optimizer.
  • Caches & buffers.

MySQL services and utilities:

MySQL comparatively provides wide range of services and utilities. This is one of the main reasons for the popularity of the MySQL. This layer provides the services and utilities for administration and maintenance of MySQL system, some of them are mentioned below:
  • Backup & recovery.
  • Security.
  • Replication.
  • Cluster.
  • Partitioning.
  • Workbench.

SQL Interface:

Structured Query Language (SQL) is a query language, used to query MySQL server. It is a tool to interact between MySQL client user and server. Some of the SQL interface components are given below.
  • Data Manipulation Language (DML).
  • Data Definition Language (DDL).
  • Stored Procedures.
  • Views.
  • Triggers.

Parser:

MySQL parses queries to create an internal structure (the parse tree).  The MySQL parser behaves as a single pass compiler. As per the MySQL internals, the parser structure is given below.
  • Lexical analysis (making words or tokens from a character stream) is implemented at first stage, when parsing regular statements.
  • Syntactic analysis (making “sentences”), semantic analysis (making sure these sentences do make sense), and code generation (for compilers) – all of them – are done at once, during the phase of code. 

Optimizer:

After creating the internal parse tree, the MySQL applies a variety of optimization techniques. These techniques may include, rewriting the query, order of scanning of tables and choosing the right indexes to use. Actually you can ask the server to explain the various aspects of optimization.

Example: 

EXPLAIN SELECT * FROM world.city;

Caches:

The MySQL cache (query cache) stores complete result sets for SELECT statements. Even before parsing the query, the MySQL server consults the query cache. If any client issues a query that is identical to one already in the in the cache, the server simply skip the parsing, optimization and even execution, it just simply display the output from the cache.

Storage Engine Layer:

The pluggable storage engine feature makes the MySQL as unique and preferred choice for most of the developers. This is the feature which makes the MySQL to reach an edge over the big player. MySQL allows us to choose the variety storage engines for different situations and requirements. We are going to discuss about the features of each storage engine in upcoming article, just the list of supported storage engines are mentioned below.
  • MyISAM.
  • InnoDB.
  • Federated.
  • Mrg_MyISAM.
  • Blackhole.
  • CSV.
  • Memory.
  • Archive.
  • Performance_schema.

MySQL provides this as a pluggable storage engines, various storage engines can be used at table level. A database can contain the tables with the multiple storage engines.

mysql>SHOW ENGINES;
Show engines command will list all the storage engines supported by your server. I hope this post will give you an overall understanding of the MySQL architecture. In next post let us discuss about the different storage engines and their features.

34 comments:


  1. This is an awesome post.Really very informative and creative contents.
    Wordpress Development company in Chennai

    ReplyDelete
  2. The blog was having very informative content and very useful for me. Well done post and keep it up...
    Web Development Minnesota

    ReplyDelete
  3. Hi Rathish.
    very useful and good explanation for people who want to kick start their career in MySql.
    Thanks for sharing.

    ReplyDelete
    Replies
    1. @goutham, Welcome and thank you for the kind words.

      Delete
  4. Excellent website! I adore how it is easy on my eyes it is. I am questioning how I might be notified whenever a new post has been made. Looking for more new updates. Have a great day! vmware srm technology

    ReplyDelete
  5. Fantastic content! Thank you Rathish!

    ReplyDelete
  6. Rathish_Kumar teaching like *Anand_Kumar* really helpful stuff for someone who is a noobie in MySQL like me.
    Keep going!!

    ReplyDelete
  7. This is very informative, you should keep writing these blogs

    ReplyDelete
  8. Thank you, @Ashtosh Singh :)

    ReplyDelete
  9. Thanks Rathish for this explanation. May I use this for my group project presentation? Thanks

    ReplyDelete
  10. Thanks Rathish for this explanation. May I use this for my group project presentation? Thanks

    ReplyDelete
  11. Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing. nisartech.com

    ReplyDelete
  12. It is perfect time to make some plans for the future and it is time to be happy. I've read this post and if I could I desire to suggest you some interesting things or suggestions. Perhaps you could write next articles referring to this article. I want to read more things about it! Car Accessories

    ReplyDelete
  13. Hi! This is my first visit to your blog! We are a team of volunteers and new initiatives in the same niche. Blog gave us useful information to work. You have done an amazing job! meal prep 7 privacy policy

    ReplyDelete
  14. I wanted to leave a little comment to support you and wish you a good continuation. Wishing you the best of luck for all your blogging efforts. Couple Shooting

    ReplyDelete
  15. Architecture is commonly gotten from the non-useful prerequisites and is intended to work in attachment with practical necessities so as to accomplish the general business objective. berlin germany

    ReplyDelete
  16. It is especially decent, though look into the tips during this home address. חוזה עם קבלן שלד

    ReplyDelete
  17. This was an excellant blog. Thanks to the admin for sharing this useful information.Tech Guest Post Free

    ReplyDelete
  18. The web site is lovingly serviced and saved as much as date. So it should be, thanks for sharing this with us. Techesign.com

    ReplyDelete
  19. This is really awesome and full of knowledge. Skalable Technologies offers software with expertise in Microsoft and Oracle-NetSuite technologies.

    ReplyDelete
  20. Finding affordable web hosting is a critical step in getting your personal or business website on the web. Here is an overview of the various web hosting options available to you and some quick tips that will allow you to enjoy all the benefits of having your website online while avoiding the headaches of choosing a bad wokehosting

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. I definitely enjoy this. It is a great website and has a nice article. I want to thank you. Good job! You guys do a great blog, Keep up the good work.Used Ford Engine

    ReplyDelete
  23. Your information is a beneficial.That’s great to read this blog, you are a good and having the wonderful skills. Really appreciable, thanks for sharing this wonderful blog. Used Nissan Engine

    ReplyDelete
  24. Architects are frequently ready to discover space in your format or your spending that you never considered, however you need to realize that the architect you recruit can address your issues.how much do architects charge

    ReplyDelete
  25. Thank you for sharing your knowledge. This is a very valuable thing you shared. good stuff!!Used Kia Engine

    ReplyDelete
  26. When you finally reached the purpose where your budget permits you to create your dream house, make sure to require the proper steps to settle on the authentic Architects for Home and make the proper moves. Building your dream home is probably the foremost expensive venture you'll do for yourself. Expensive indeed but the foremost rewarding when done correctly.

    ReplyDelete
  27. This post is really awesome. Genuinely i like this blog. It gives me more useful information. I hope you share lots of things with us .vw golf mk5 double din head unit

    ReplyDelete
  28. Enjoyed every single word of your blog article. Thanks to share it!

    ReplyDelete