Shulou(Shulou.com)06/01 Report--

The overall framework of mysql:

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

1. Connectors refers to interaction with SQL in different languages.

2. Management Serveices & Utilities: system management and control tools.

3. Connection Pool: connection pool. Manage caching requirements such as buffering user connections, threading, and so on.

4. SQL Interface: SQL API. Accept the user's SQL command and return the results that the user needs to query. For example, select from calls SQL Interface.

5. Parser: parser. The SQL command is validated and parsed by the parser when it is passed to the parser. The parser is implemented by Lex and YACC and is a long script. Main functions:

Decompose the SQL statement into a data structure and pass this structure to the next step, based on which the delivery and processing of the SQL statement is based. If an error is encountered in the decomposition composition, then the sql statement is unreasonable.

6. Optimizer: query optimizer.

The SQL statement uses the query optimizer to optimize the query before the query. He uses the "select-project-join" strategy to query. It can be understood by an example: select uid,name from user where gender = 1

This select query is first selected according to the where statement, rather than querying all the tables first and then gender filtering. This select query first projects attributes according to uid and name, instead of taking out all the attributes and then filtering them to join the two query conditions to generate the final query results.

7. Cache and Buffer: query cache.

If the query cache has a hit query result, the query statement can directly fetch data from the query cache. This caching mechanism consists of a series of small caches. Such as table cache, record cache, key cache, permission cache and so on.

8. Engine: storage engine.

Storage engine is a specific subsystem dealing with files in MySql. It is also one of the most distinctive features of Mysql.

Mysql's storage engine is plug-in. It customizes a file access mechanism (called storage engine) according to an abstract interface of the file access layer provided by MySql AB. Now there are many kinds of storage engines, each of which has different advantages, the most commonly used one is InnoDB,BDB,MyISAM.

By default, MySql uses the Innodb engine (the default storage engine before mysql 5.5.5 is Myisam), which has fast query speed, good index optimization and data compression technology. But it supports transactions.

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

Second, 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 buffer to improve the efficiency of REDO log writing

+ key_buffer_size-- MyISAM table index cache to improve the read and write efficiency of MyISAM table index

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

+ table_cahce-- Table space file descriptor cache to improve the efficiency of data table opening

+ table_definition_cache-- Table definition file descriptor cache to improve the efficiency of data table opening

All_thread_buffers (total memory allocation at session / thread level) =

Max_threads (current active connections) * (

Read_buffer_size-sequential read buffering to improve the efficiency of sequential reading

+ read_rnd_buffer_size-random read buffering to improve the efficiency of random reading

+ sort_buffer_size-sort buffer to improve sorting efficiency

+ join_buffer_size-table join buffering to improve table join efficiency

+ binlog_cache_size-binary log buffer to improve the efficiency of binary log writing

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

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

+ thread_cache_size-Thread cache to reduce thread overhead of opening multiple times

+ net_buffer_length-Threads hold connection buffers and read result buffers

+ bulk_insert_buffer_size)-- bulk write data buffer for MyISAM table

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.