MySQL Architecture architecture

Objective:

On the Internet to find a number of common interview questions, found that asked about the structure of MySQL, I think about it, found nothing, only know that there are several engines, so love to search for me, to find the answer to the question!

Understand that MySQL must firmly remember its architecture diagram, MySQL is composed of SQL interface, parser, optimizer, cache, storage engine

1 connectors refers to the interaction of SQL in different languages

2 Management serveices & Utilities: System Management and control tools

3 Connection Pool: Connection pooling.

Manage buffered user connections, threading, and other needs for caching

4 SQL Interface:sql interface.

Accepts the user's SQL command and returns the result that the user needs to query. For example, select from is called SQL Interface

5 Parser: Parser.

When the SQL command is passed to the parser, it is validated and parsed by the parser. The parser is implemented by Lex and YACC and is a very long script.

Main functions:

A. The SQL statement is decomposed into a data structure, and the structure is passed to the next step, after which the SQL statements are passed and processed based on this structure

B. If an error is encountered in the decomposition composition, then it is not reasonable to indicate that the SQL statement

6 Optimizer: Query optimizer.

SQL statements use the query optimizer to optimize queries before they are queried. He uses the Select-drop-join strategy for querying.

As an example, you can understand: Select Uid,name from user where gender = 1;

This select query is selected based on the where statement instead of querying all of the tables before gender filtering

This select query first attributes the property based on the UID and name, instead of removing the attributes from the filter

Join these two query criteria to generate the final query result.

7 Cache and Buffer: query caching.

If the query cache has hit query results, the query can go directly to the query cache to fetch the data.

This caching mechanism is made up of a series of small caches. such as table cache, record cache, key cache, permission cache, etc.

8 Engine: Storage engine.

The storage engine is the specific subsystem in MySQL that deals with files. It is also one of MySQL's most distinctive places.

The MySQL storage engine is plug-in. It customizes a file access mechanism based on an abstract interface of the file access layer provided by MySQL AB (the access mechanism is called the storage engine)

Now there are many kinds of storage engines, the advantages of each storage engine are different, the most common myisam,innodb,bdb

By default MySQL is using the MyISAM engine, it queries fast, has better index optimization and data compression technology. However, it does not support transactions.

InnoDB supports transactions and provides row-level locking, and the application is quite extensive.
MySQL also supports its own custom storage engine, and even the different tables in a library use different storage engines, which are allowed.

This paper draws on: http://www.cnblogs.com/yjf512/archive/2012/02/06/2339496.html

Copyright notice: I feel like I'm doing a good job. I hope you can move your mouse and keyboard for me to order a praise or give me a comment, under the Grateful!_____________________________________________________ __ Welcome reprint, in the hope that you reprint at the same time, add the original address, thank you with

MySQL Architecture architecture