1. Brief Introduction – MySQL
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
Each client connection will get its own thread within the service from a thread-poll. Authentication, access check will also happen at this stage.
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.
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 will check resulting parse tree for additional semantics that Query Parser can’t resolve, e.g. existence of tables and columns, aliases, etc.
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 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.
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 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!