MySQL learning-—— mysql architecture with MySQL storage engine

Mysql Architecture and Mysql Storage Engine

MySQL Architecture

MySQL architecture diagram:

1) connectors: refers to the interaction of SQL in different languages

Max_connections is the maximum number of connection limits for the entire MySQL instance

Max_user_connections is the maximum number of connections for a single user, which is not indicated by which user is a user.

2) Management serveices & Utilities: System Management and control tools

Security, replication, clustering, management, configuration, migration, and metadata for backup and recovery

3) Connection pool: connection pooling

Authentication, thread reuse, connection restrictions, checking memory, data caching, managing user connections, threading, and so on need for caching.

4) SQL Interface: SQL interface

Perform operations and management of DML, DDL, stored procedures, views, triggers, etc., and the user queries the desired results with SQL commands.

5) Parser: parser

Query the privileges of the translation object, and the SQL command is validated and parsed by the parser when it is passed to the parser.

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.

The user can also request the server to give a description of the optimization process, in order to learn the optimization strategy of the server, to provide users with a parameter benchmark, so that users can rewrite the query, schema and modify the relevant server configuration, easy to run MySQL more efficient.

7) cache and buffer: Query Cache

To query the cache before parsing the query, the cache can only hold query information and result data. If a query is requested to exist in the cache, there is no need to parse, optimize, and execute the query. Returns the result of the query stored directly in the cache. This caching mechanism consists of a series of small caches, such as table caches, record caches, key caches, permission caches, and so on.

8) engine: storage Engine

MySQL supports a variety of storage engines, and the most commonly used storage engines are MyISAM and InnoDB.

The optimizer does not care what kind of storage engine the table uses, but the storage engine has an impact on how the server optimizes queries. The optimizer needs to know some of the features of the storage Engine: information about the performance and overhead of the operation, and statistics on the data in the table. For example, which index types are supported by the storage engine, which is useful for queries.

Summary:mysql architecture is divided into three layers, namely: Client connection layer, SQL layer, storage engine layer. Client Connection layer mainly refers to the interaction of different languages and databases, such as jdbc,odbc,php. SQL Layer is the core of MySQL architecture, including system management and Control tools (DB Backup, recovery, replication, migration, etc.), connection pool (mainly responsible for user's connection request authentication, authorization authentication and security, etc.),SQL interface , query cache (save only query information and data results), parser , optimize layer (when there is a new query statement, the DBMS first looks at the query cache, If the requested query exists in the cache, it is not necessary to parse, optimize and perform the operation, directly read from the query cache, but if the requested query in the query cache does not exist, then the parser will analyze the query statement, and then perform various optimizations, which include, query statement rewrite, read the Order of the table, Selection of indexes, etc.). Storage Engine layer (MySQL most commonly used storage engine has MYISAM,INNODB, etc.).

MySQL Storage engine

L MyISAM: is not transaction-safe and does not support foreign keys, and is suitable for use in the following situations:

1. Select the intensive table. The MyISAM storage engine is very fast in filtering large amounts of data, which is its most prominent advantage.
2. inserts a dense table. The concurrent Insert feature of MyISAM allows data to be selected and inserted at the same time. For example, the MyISAM storage engine is ideal for managing mail or Web server log data.

L InnoDB: an engine that supports transactional security, supports foreign keys, row locks, and transactions is his greatest feature and is suitable for use in the following situations:

1. updates a dense table. The InnoDB storage engine is ideal for handling multiple concurrent update requests.
2. transactions . The InnoDB storage engine is a standard MySQL storage engine that supports transactions.
3. Automated Disaster recovery. Unlike other storage engines, the InnoDB table can automatically recover from a disaster.
4. FOREIGN key constraints. MySQL supports the foreign key storage engine only InnoDB.
5. Supports automatic increment of column Auto_increment property.

In general, InnoDB is a good choice if transaction support is required and there is a high frequency of concurrent reads.

L Memory: The starting point for using the MySQL memory storage engine is speed . To get the fastest response time, the logical storage medium used is system memory . Although storing table data in memory does provide high performance, all memory data will be lost when the mysqld daemon crashes. The speed of the acquisition also brings some drawbacks. It requires that the data stored in the Memory data table use a constant length format , which means that a variable length data type such as BLOB and text cannot be used, and varchar is a variable-length type, but because it is fixed within MySQL as a length Variable char type, so it can be used. Memory storage engines are typically used in the following situations:

1. The target data is small and is accessed very frequently. Storing the data in memory, so it will cause the use of memory, can be controlled by the parameter max_heap_table_size the memory table size, set this parameter, you can limit the memory table maximum size.
2. If the data is temporary and needs to be immediately available, it can be stored in the memory table.
3. If the data stored in the memory table is suddenly lost, it will not have a substantial negative impact on the application service.

The MyISAM and InnoDB storage engines only support btree indexes; memory and heap storage engines can support hash and btree indexes.

The B-Tree index is better than the hash index, you can use partial queries and wildcard queries , or you can use operators such as <, >, and >= to facilitate data mining. Hash indexes are very fast for equality comparisons, but are much slower for range comparison, so the hash index values are suitable for use in operators of = and <>, not in the < or > operators, nor in the ORDER BY clause.

"Reference" http://www.cnblogs.com/yjf512/archive/2012/02/06/2339496.html

Http://www.jb51.net/article/55849.htm

MySQL learning-—— mysql architecture with MySQL storage engine