This is the 7th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.
- Personal homepage: IT Learning Diary
- If the article is helpful to you, welcome to pay attention to, like, favorites (one button three links)
One, foreword
- Hello everyone, I am Xiaocheng, from the last article “a COMPLETE SQL execution process analysis” introduced the MySQL execution process, we know that the MySQL Server is divided into :MySQL Server layer and storage engine layer. This article introduces the storage engine supported by mysql8.x and its features to familiarize you with MySQL
- Recently, I often received feedback from some partners. In order to facilitate communication, I created a communication group. If you are interested, you can add it.
- “From 0 to 1- comprehensive and profound understanding of MySQL series” series of articles will continue to update, interested partners can follow me, together with cheer, progress together! , if it helps, don’t forget a key triple oh,ღ(´ ᴗ · ‘) than heart!
Storage engines supported by MySQL
Storage engines are mainly responsible for data access operations. MySQL8.0 supports 10 types of storage engines as follows:
MySQL supports storage engine features
1. InnoDB:
Is the default storage engine in Mysql8.0. InnoDB is MySQL’s transaction-safe (ACID-compliant) storage engine with commit, rollback and crash recovery capabilities to protect user data.
InnoDB row-level locking (not upgraded to more coarse-grained locking) and Consistent, oracle-style unlocked reads improve multi-user concurrency and performance.
InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY reference integrity constraints.
2. MyISAM
Tables using this storage engine take up very little space, table-level locks (table locks) limit the performance of its read/write loads, and are commonly used in read-only or read-oriented work scenarios in Web and data warehouse configurations.
(c) Memory
This storage engine stores all of the data in memory for quick access in non-critical data environments where quick lookups are required. This storage engine was also previously known as the HEAP engine.
The use of this engine is declining as the InnoDB engine provides a common and persistent way to keep most or all of the data in memory through the buffer pool memory area, and the NDB engine (also known as NDBCLUSTER) improves to provide fast key lookup for large distributed data sets.
(4) CSV
The CSV engine’s tables are actually text files with comma-separated values. The tables in the CSV engine allow you to import or dump data in CSV format to exchange data with scripts and applications that read and write the same format.
Since CSV tables have no indexes, you can usually have the InnoDB engine keep data in the tables during normal operation and use the CSV engine’s tables only during import or export phases.
(5) Archive
The tables under this storage engine are compact, indexed, and are primarily used to store and retrieve large volumes of rarely referenced historical, archived, or security audit information.
6. Blackhole
The Blackhole storage engine receives but does not store data, similar to the Unix/dev/null device. A query always returns an empty set. Tables in this engine mode can be used to configure replication, where DML statements are sent to the replica server, but the source server does not keep its own copy of the data.
(7) NDB(also known as NDBCLUSTER), a clustered database engine, is particularly suitable for applications requiring a high degree of uptime assurance and high availability.
B. Merge C. Merge D. Merge
This storage engine enables MySQL DBAs or developers to logically group a series of identical MyISAM tables and reference them as an object. Suitable for VLDB environments, such as data warehouses.
(9) Federated
This storage engine provides the ability to link individual MySQL servers to create a logical database from many physical servers. Ideal for distributed or data mart environments.
Example
This engine, as an example in the MySQL source code, describes how to start writing a new storage engine. It may be of interest to developers. A storage engine is a “stub” that does nothing. You can specify that tables use this storage engine, but you cannot store or retrieve data from it.
(11) Summary
You do not need to restrict the use of the same storage engine for the entire server or architecture. You can specify a specific storage engine for any table. For example, an application might have tables primarily of InnoDB type, then define a CSV engine table for exporting data to a spreadsheet, and some other MEMORY engine type tables for temporary workspaces. In short, a storage engine that has the flexibility to define tables according to its own needs.
Four: How to choose a storage engine and compare the features of common storage engines
Mysql has a storage engine, and the storage engine has a storage engine.
When selecting a storage engine for a table, you can refer to the features of each storage engine above and then choose it according to your specific business scenario. The general business scenario is to use the MySQL official default. Here I mainly summarize some details that need to be paid attention to (mainly for interviews).
Replication support for each engine is implemented in the server program, not in the storage engine.
2. The Encrypted Data of each engine is implemented in the server.
In MySQL 5.7 and later, static data encryption is supported, which is implemented in the server.
MySQL Cluster NDB 7.3 and later supports foreign keys.
MySQL 5.6 and later supports FULLTEXT indexes.
MySQL 5.7 and later supports geospatial indexes.
7. InnoDB storage engine uses hash index internally to implement its adaptive hash index function.
Five: Common interview questions about storage engines
5.1: Differences between InnoDB and MyISAM
(I) Features of InnoDB:
MySQL is the default storage engine of MySQL (MyISAM was the default storage engine of MySQL before MySQL5.5).
2. Four isolation levels of SQL standard are realized;
3, support row-level locking and foreign key constraints;
4. You can use transaction logs for data recovery.
5, the lock level is row lock, row lock advantage is suitable for high concurrency frequent table modification, high concurrency is better than MyISAM performance. The disadvantage is large system consumption.
6. Index caches not only itself but also data, which requires more memory than MyISAM.
(II) MyISAM features:
1, lock level for table lock, table lock advantage is small overhead, fast lock; The disadvantages are large lock granularity, high lock impulse probability and low capacity to accommodate concurrency. This engine is suitable for query-oriented services.
2. This engine does not support transactions, nor does it support foreign keys.
INSERT and UPDATE operations lock the entire table;
SELECT COUNT(*) FROM TABLE; SELECT COUNT(*) FROM TABLE;
(iii) Applicable scenarios
MyISAM is good for: (1) doing a lot of count calculations; (2) Insert is not frequent, query is very frequent; (3) No transaction.
InnoDB is suitable for: (1) high reliability requirements, or transaction requirements; (2) table update and query are quite frequent, and the opportunity to lock the table is relatively large.
5.2 how do I Select a storage Engine for Mysql
The value can be set based on the service requirements of the system. Generally, use the official default value.
InnoDB: If you have high requirements for data integrity, and there are many update and delete operations in addition to insert and query, you need to use transactions or foreign keys. InnoDB is also Mysql’s default storage engine.
MyISAM: read-only or insert operations, few updates and deletes, and low data integrity requirements can be selected.
Six, series of articles
From 0 to 1- A comprehensive insight into MySQL series
1, from 0 to 1- Comprehensive understanding of MySQL series – The most detailed MySQL installation process (Windows Edition)
2, “From 0 to 1- Comprehensive understanding of MySQL series” – the most detailed MySQL installation process (Linux environment)
How to connect to the database when you forget your MySQL login password
Seven, summary
From the above we can know, MySQL official offer as many as 10 storage engine, and have their own characteristics, in the database, table design, will be more comprehensive to consider his own system of a business scenario to determine which use the storage engine, if you don’t know how to choose cases, using official default can be directly, since the official default this This is a storage engine that is suitable for most system scenarios, so there is no need to worry too much.
Finally, if you feel that the article is helpful to you, don’t forget one key three even oh, your support is my motivation to create more high-quality articles, there are any questions can be private letter to me, see will give you a timely reply!