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

This article mainly introduces "MySQL database structure and engine comparison analysis". In daily operation, I believe many people have doubts about MySQL database structure and engine comparison analysis. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "MySQL database structure and engine comparison analysis". Next, please follow the editor to study!

MySQL database structure

1. The top-level services are not unique to MySQL. Most web-based client / server tools or services have similar architectures.

2. In the second tier architecture, most of the core service functions of MySQL are in this layer, including query parsing, analysis, optimization, caching and all built-in functions (date and time, etc.). All cross-storage engine functions are implemented in this layer: stored procedures, triggers, views, etc.

3. The third layer contains the storage engine. The storage engine is responsible for the storage and extraction of data in MySQL. MySQL supports a variety of different storage engines, each of which has its advantages and disadvantages. The server communicates with the storage engine through API. These API interfaces shield the differences between different storage engines and make these differences transparent to the upper query process. The storage engine does not parse SQL (InnoDB is an exception, it parses foreign key definitions), and different storage engines cannot communicate with each other, but simply respond to requests from upper-level servers!

Execution process

The database storage engine is the underlying software component of the database. The database management system uses the data engine to create, query, update and delete data.

Different storage engines provide different storage mechanisms, indexing techniques, locking levels and other functions, and specific functions can be obtained using different storage engines.

Many database management systems now support a variety of different storage engines. The core of MySQL is the storage engine.

MyISAM: has a high insert, query speed, but does not support transactions.

InnoDB: the default database of MySQL after version 5.5, the preferred engine for transactional databases, supporting ACID transactions and row-level locking.

BDB: derived from Berkeley DB, an alternative to transactional databases that supports other transaction features such as COMMIT and ROLLBACK.

Memory: a storage engine that stores all data in memory, with high insertion, update and query efficiency. But it takes up memory space proportional to the amount of data. And its contents will be lost when the Mysql is restarted.

Merge: combine a certain number of MyISAM tables into a whole, which is useful for very large-scale data storage.

Archive: ideal for storing large amounts of independent, historical data. Because they are not often read. Archive has efficient insertion speed, but its support for queries is relatively poor.

Federated: combine different Mysql servers to logically form a complete database. Very suitable for distributed applications.

Cluster/NDB: highly redundant storage engine that uses multiple data machines to jointly provide services to improve overall performance and security. It is suitable for applications with large amount of data and high security and performance requirements.

CSV: a storage engine that logically splits data by commas. It creates a .CSV file for each data table in the database subdirectory. This is a normal text file that takes up one line of text per line of data. The CSV storage engine does not support indexing.

BlackHole: black hole engine. Any data written will disappear. It is generally used to record the relay of binlog for replication.

At this point, the study on "MySQL database structure and engine comparison analysis" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.