An example of analysis _php based on MySQL architecture

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

1 connectors refers to the interaction with SQL in different languages

2 Management serveices & Utilities: System Management and control tools

3 Connection Pool: Connection pooling.

Managing buffer user connections, threading, and so on needs of caching

4 SQL Interface:sql interface.

Accepts the user's SQL command and returns the results of the query that the user needs. For example, select from is to invoke 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 long script.

Main function:

A. The SQL statement is decomposed into a data structure and passed to the next step, and the subsequent transfer and processing of the SQL statement is based on this structure

B. If you encounter an error in the decomposition composition, it means that the SQL statement is unreasonable

6 Optimizer: Query optimizer.

SQL statements are optimized for queries using the query optimizer before querying. He uses the select-projection-join policy to query.

One example would make sense: select Uid,name from user where gender = 1;

This select query is first selected based on the where statement, instead of gender filtering after all the tables have been queried

This select query first attributes a property projection based on the UID and name, instead of filtering the attributes after they are all removed

Join these two query criteria to produce the final query results

7 Cache and Buffer: query caching.

If the query cache has the result of a hit query, 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 caching, record caching, key caching, permission caching, etc.

8 Engine: Storage engine.

The storage engine is a specific subsystem of files in MySQL. MySQL is also the most characteristic of a place.

MySQL's storage engine is plug-in type. It customizes a file access mechanism based on an abstract interface to the file access layer provided by MySQL AB (this access mechanism is called the storage engine)

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

MySQL By default is the use of the MyISAM engine, it query speed, with better index optimization and data compression technology. However, it does not support transactions.

InnoDB supports transactions and provides row-level locking, and is widely used.
MySQL also supports customizing the storage engine, and even a different table in a library uses a different storage engine, which is allowed.