Some basic knowledge about databases (1) ------ Database Engine

MySQL database provides 13 different storage engines to handle different data processing. Most people who have used MySQL should know the two storage engines commonly used by MySQL: MyISAM and InnoDB. In most cases, MyISAM exists as the default storage engine of MySQL. In fact, most of us do not configure the storage when using MySQL.

MySQL database provides 13 different storage engines to handle different data processing. Most people who have used MySQL should know the two storage engines commonly used by MySQL: MyISAM and InnoDB. In most cases, MyISAM exists as the default storage engine of MySQL. In fact, most of us do not configure the storage when using MySQL.

MySQL database provides 13 different storage engines to handle different data processing. Most people who have used MySQL should know the two storage engines commonly used by MySQL: MyISAM and InnoDB. In most cases, MyISAM exists as the default storage engine of MySQL. In fact, most of us use MySQL's default MyISAM instead of configuring the storage engine. If you need to use a specific storage engine on a specific table, you can use the following command line:

CREATE TABLE tablename (column1, column2, [etc...]) ENGINE = [storage_engine].

So what is the database engine?
Simply put, a database storage engine is used to store, process, and query data tables. There is no perfect storage engine in this world, but for most applications, the default MyISAM is enough. Of the 10 built-in storage engines in MySQL, not all engines are available. You can use the following command to query the storage engine provided by your MySQL Server:
Code:

mysql -uroot -pPassword:mysql> show engines;

This command will list the storage engines supported by your server:
MyISAM
InnoDB
MERGE
MEMORY (HEAP)
BDB (BerkeleyDB)
EXAMPLE
ARCHIVE
CSV
BLACKHOLE
ISAM you can select a storage engine based on your application needs. For example, if you need to store a large amount of log data, you can use the ARCHIVE storage engine that only supports INSERT and SELECT operations. You can set a specific storage engine for your database server, database, or table. This flexible configurability is also why MySQL is more popular than databases that only support a single storage engine.

MyISAM

MyISAM is actually a branch of the ISAM storage engine. If your application does not require TRANSACTION or row-level locks (it only provides table-level locks), MyISAM is the best solution. MyISAM is particularly suitable for INSERT/UPDATE operations with fewer SELECT operations. When your application requires a large number of INSERT/UPDATE operations, you need to consider whether you should change to a storage engine, because table-level locks will cause performance problems for this operation.

The maximum number of lines supported by MyISAM is ~ 4.29E + 09. the maximum number of indexes for each table is 64. MyISAM also provides full index support for TEXT/BLOB columns, which facilitates search and other operations.

InnoDB

Compared with MyISAM, InnoDB provides more features to improve system performance. This also results in InnoDB spending more time on Initialization than MyISAM, but this brings far more benefits than the additional time spent on initialization. One major difference is that InnoDB provides row-level locks. This provides the possibility of concurrent INSERT, UPDATE, and DELETE operations. Unlike MyISAM, the next operation can be performed only after an operation is completed.

At the same time, InnoDB also provides the foreign key function. This ensures that the data in table 1 exists before you insert data into table 2. In addition, this will prevent you from deleting the data that table 2 depends on in table 1.

InnoDB also provides that the data cache has been indexed in the memory and on the disk, which can greatly improve the system performance. This may not be an ideal solution for low-memory systems, but it is not a problem for systems with sufficient memory.

MERGE
MERGE, the storage engine added from MySQL 3.23.25. It allows you to perform unified operations on a MyISAM set, just like operating a table. However, using this engine has some constraints. For example, all tables must be uniformly defined.

MEMORY (HEAP)
The HEAP Storage engine, also known as MEMORY, allows table creation in the MEMORY. The MySQL Server retains the table format, so that you can quickly create a "trash" table and then quickly read data for better processing. However, this makes the storage engine not suitable for long-term data processing.

BDB (BerkeleyDB)

The BerkeleyDB storage engine processes transaction-safe tables and uses hash-based storage systems. This storage engine is suitable for fast reading and writing certain data, especially data of different keys. However, this storage engine has many disadvantages, such as the slow speed of columns without indexes. It is also ignored for this reason. I still believe it can be used.

EXAMPLE

EXAMPLE: The storage engine added from MySQL 4.1.3. This storage engine mainly serves programmers. EXAMPLE provides the ability to create tables, but cannot insert or query information.

ARCHIVE
ARCHIVE, a storage engine added since MySQL 4.1.3, is often used to store large-scale data (no index required ). This storage engine only supports INSERT and SELECT operations, and all information is compressed. These features make ARCHIVE suitable for storing logs, transaction records, accounts, and so on. However, when reading data, the entire table needs to be decompressed and read before the data is returned. Therefore, this storage engine is most suitable for scenarios with low usage frequency.

CSV
CSV: The storage engine added since MySQL 4.1.4. data is stored in strings separated by commas. Therefore, this storage engine is not suitable for large-scale data storage or data tables that need to be indexed. This storage engine is suitable for converting data into spreadsheet files.

BLACKHOLE
This storage engine seems useless because it does not allow the storage or query of any data. Therefore, BLACKHOLE is usually used to test the database structure, index, and erase queries. You can still use the INSERTS command to insert data, but all the data is void.

ISAM
The most primitive storage engine is ISAM, which manages non-transactional tables. Later, it was replaced by MyISAM, and MyISAM is backward compatible, so you can forget this ISAM storage engine.

Summary
In general, there is no perfect database storage engine. For most applications and DBAs, InnoDB and MyISAM are enough. But remember that although InnoDB and MyISAM are quite common, they are not perfectly supported for all scenarios. Maybe other storage engines can well support your applications.

Next episode notice:

Database transaction isolation mechanism and features;

References:

1. http://www.linux.org/threads/an-introduction-to-mysql-storage-engines.4220/