MySQL overall architecture and memory structure

One MySQL overall framework:

MySQL is composed of SQL interface, parser, optimizer, cache, storage engine and so on.

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. Manage buffering of user connections, threading, and other needs that require 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:

• The SQL statement is decomposed into a data structure, and the structure is passed to the next step, and then the SQL statement is passed and processed based on this structure.
• If an error is encountered in the decomposition composition, then it is unreasonable to describe 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, rather than querying all the tables first and then gender filtering. Instead of taking all of the attributes out and then filtering them, this select query joins the two query criteria to produce the final query result, based on the UID and name.

7. Cache and Buffer: query cache.

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, and so on.

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 Innodb,bdb,myisam.

By default MySQL is using the Innodb engine (MySQL 5.5.5 previous version of the default storage engine is Myisam), it queries fast, has better index optimization and data compression technology. But it supports transactions.

MySQL also supports its own custom storage engine, and even the different tables in a library use different storage engines, which are allowed.

Two MySQL memory structure:

MySQL uses total memory = global_buffers + all_thread_buffers

Global_buffers (sum of global memory allocations) =

Innodb_buffer_pool_size--InnoDB cache, row data, index buffer, transaction lock, adaptive hash, etc.

+innodb_additional_mem_pool_size--InnoDB data dictionary extra memory, cache all table data dictionaries

+innodb_log_buffer_size--InnoDB redo log buffering for improved redo log write efficiency

+key_buffer_size--MyISAM table index high-speed buffer, improve MyISAM table index reading and writing efficiency

+query_cache_size--Query cache, cache query results, improve the efficiency of repeated query return

+TABLE_CAHCE--tablespace file descriptor cache for improved data table opening efficiency

+table_definition_cache---table definition file descriptor cache for improved data table opening efficiency

All_thread_buffers (Session/thread-level memory allocation sum) =

Max_threads (current active connections) * (

Read_buffer_size-sequential read buffer for improved sequential read efficiency

+read_rnd_buffer_size--Random read buffer to improve random reading efficiency

+sort_buffer_size--Sort buffers to improve sorting efficiency

+join_buffer_size-Table connection buffer to improve table connection efficiency

+binlog_cache_size-binary log buffering for improved binary log write efficiency

+tmp_table_size--memory temp table to improve temporary table storage efficiency

+thread_stack--thread stack, temporarily hosting SQL statements/stored procedures

+thread_cache_size-thread caching, reducing the overhead of repeatedly opening threads

+net_buffer_length--Thread hold connection buffer and read result buffer

+bulk_insert_buffer_size)--MyISAM table bulk Write Data buffer

MySQL overall architecture and memory structure