MySQL – Architecture

1. Brief Introduction – MySQL

489px-mysql-svg

MySQL is the world’s most popular open source database. Whether you are a fast growing web property, technology ISV or large enterprise, MySQL can cost-effectively help you deliver high performance, scalable database applications.

Official website: http://www.mysql.com/

2. Logical Architecture

screen-shot-2017-01-24-at-2-57-55-pm

Connection Handling

Each client connection will get its own thread within the service from a thread-poll. Authentication, access check will also happen at this stage.

Query Cache

Before even parsing the query, the server will consult the query cache which stores ONLY SELECT statements along with their result sets. If there’s an identical query in the query cache, the stored result will be simply passed back without query being parsed, executed.

Query Parser

The query will be broken into tokens and a “parse tree” will be built based on the tokens. Query Parser uses MySQL’s SQL grammar to interpret and validate the query.

Query Preprocessor

Query Preprocessor will check resulting parse tree for additional semantics that Query Parser can’t resolve, e.g. existence of tables and columns, aliases, etc.

Query Optimizer

MySQL cost-based Query Optimizer will turn the valid parse tree into query execution plan. Various plans will be measured and the least expensive on will be chosen. However, the optimizer may not always choose the best plan for many reasons such as wrong statistics, ignorance of other running queries,  user defined functions, etc. The execution plan is a tree of instructions that the Query Execution Engine follows to produce the query results. It’s a data structure instead of executable bytecode, which is how many other databases execute queries.

Query Execution Engine

The query execution engine follows instructions given in the execution plan by making calls to the storage engine API. The results will be sent back to client incrementally, which lets the server avoid holding rows in memory and makes client get the results as soon as possible. If the query is cacheable, MySQL will also place the results into the query cache.

Pluggable Storage Engines

This is MySQL’s most unusual and important feature. With this design, query processing and other server tasks are totally separated from data storage and retrieval, which gives users the flexibility to choose how data is stored and what performance, features, and other characteristics they want. Each of the storage engines provides a standard interface for its operations by extending the  Abstracted Storage Engine Interface.

InnoDB

InnoDB is the default transactional storage engine for MySQL and the most important and broadly useful engine overall. It’s designed for short-lived transactions that usually complete rather than being rolled back.

XtraDB

Percona’s XtraDB storage engine, which is included with Percona Server and MariaDB, is modified version of InnoDB. Its improvements are targeted at performance, measurability, and operational flexibility.It is backward compatible with InnoDB so you can use it as a drop-in replacement. Percona XtraDB also includes InnoDB’s reliable ACID-compliant design and advanced MVCC architecture.

MyISAM

MyISAM is the default storage engine for MySQL in version 5.1 or older and is why MySQL still has the reputation of being a non-transactional database management system, more than a decade after it gained transactions!

Reference:

High Performance MySQL, 3rd Edition    By Baron Schwartz, Peter Zaitsev, Vadim Tkachenko
Understanding MySQL Internals    By Sasha Pachev
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s