table engine

What is MySql database

Generally speaking, a database is a collection of data. Specifically, a database on a computer can be a collection of files on the memory or A collection of some in-memory data.
What we usually call MySql database, sql server database, etc. are actually database management systems. They can store data and provide functions for querying and updating data in the database, etc. Depending on the implementation mechanism of how the database stores data and how to operate the data, there are differences and similarities between these databases.
MySql database is an open source relational database. Currently, the functions it can provide include: supporting SQL language, subqueries, stored procedures, triggers, views, indexes, transactions, locks, foreign key constraints and image replication, etc. We will explain these functions in detail later.
Like large database systems such as Oracle and SQL Server, MySql is also a client/server system and a database with a single-process multi-thread architecture.
An important feature that distinguishes MySql from other database systems is its support for plug-in storage engines.

So what is a storage engine?

To put it bluntly, a storage engine is the implementation of technologies such as how to store data, how to index the stored data, and how to update and query data. Because data is stored in the form of a table in a relational database, the storage engine can also be called a table type (that is, the type of storage and operation of this table).
There is only one storage engine in databases such as Oracle and SQL Server, and all data storage management mechanisms are the same. The MySql database provides a variety of storage engines. Users can choose different storage engines for data tables according to different needs, and users can also write their own storage engines according to their own needs. What storage engines are there in MySql?
1 MyISAM: This engine is the first one provided by mysql. This kind of engine can be divided into three types: static MyISAM, dynamic MyISAM and compressed MyISAM:
Static MyISAM: If the length of each data column in the data table is pre-fixed, the server will automatically select this table type. Because each record in the data table occupies the same space, the efficiency of table access and update is very high. When data is damaged, recovery is easier.
Dynamic MyISAM: If varchar, xxxtext or xxxBLOB fields appear in the data table, the server will automatically select this table type. Compared with static MyISAM, the storage space of this kind of table is relatively small, but because the length of each record is different, after the data is modified multiple times, the data in the data table may be stored discretely in the memory, which in turn leads to a decrease in execution efficiency. At the same time, there may also be a lot of fragmentation in the memory. Therefore, this type of table should often be defragmented using the optimize table command or an optimization tool.
Compress MyISAM: Both types of tables mentioned above can be compressed using the myisamchk tool. This type of table further reduces the storage footprint, but this type of table cannot be modified after compression. In addition, because it is compressed data, this kind of table must be decompressed first when reading.
However, no matter what kind of MyISAM table it is, it currently does not support transactions, row-level locks and foreign key constraints.
2 MyISAM Merge engine: This type is a variant of the MyISAM type. Merging tables is to merge several identical MyISAM tables into a virtual table. Commonly used in logs and data warehouses.
3 InnoDB: The InnoDB table type can be seen as a further updated product of MyISAM. It provides the functions of transactions, row-level locking mechanisms and foreign key constraints.
4 memory(heap): This type of data table only exists in memory. It uses a hash index, so data access is very fast. Because it exists in memory, this type is often used in temporary tables.
5 archive: This type only supports select and insert statements, and does not support indexes. Often used in logging and aggregate analysis.
Of course, MySql supports more table types than the above.

We introduce several commonly used engines and learn about several less commonly used engines. Avoid seeing some engines that don’t know the concepts in actual work in the future.

The two most commonly used storage engines:

· Myisam is the default storage engine for Mysql. When create creates a new table and the storage engine of the new table is not specified, Myisam is used by default. Each MyISAM is stored as three files on disk. The file names are the same as the table names, and the extensions are .frm (storage table definition), .MYD (MYData, storage data), and .MYI (MYIndex, storage index). Data files and index files can be placed in different directories to evenly distribute IO and obtain faster speeds.

# · InnoDB storage engine provides affairs security with the ability to submit, roll back and collapse and recover. However, compared to Myisam's storage engine, InnoDB's write processing efficiency is less efficient and takes up more disk space to retain data and indexes.

How to choose a suitable storage engine

Selection criteria: Choose a suitable storage engine according to the application characteristics. For complex application systems, you can choose a combination of multiple storage engines according to the actual situation.

The following are the applicable environments for commonly used storage engines:

1. MyISAM: The default MySQL plug-in storage engine, which is the most commonly used storage in Web, data warehousing and other application environments One of the engines

2. InnoDB: For transaction processing applications, has many features, including ACID transaction support.

3. Memory: Saves all data in RAM, providing extremely fast access in environments where references and other similar data need to be quickly found.

4. Merge: Allows the MySQL DBA or developer to logically combine a series of equivalent MyISAM tables and reference them as 1 object. It is very suitable for VLDB environments such as data warehousing.

MyISAM

Does not support transactions, table locks (table-level locks, locking will lock the entire table), supports full-text indexes, and has fast operation speed. Often used for businesses that read a lot.

1. Myisam storage engine table consists of myd and myi. .myd is used to store data files, and .myi is used to store index files.

2. For myisam storage engine tables, the mysql database only caches its index files, and the caching of data files is completed by the operating system itself.

InnoDB

1. Supports transactions, mainly for online transaction processing (OLTP) applications.

2. Row lock design supports foreign keys, that is, read operations are not locked by default.

InnoDB is designed for maximum performance when processing huge amounts of data.

Note:
Row lock: Lock this row during write and update operations to prevent others from operating it.
Table lock: During write and update operations, lock the table to prevent others from operating it.
Transaction: Operate multiple data at the same time, if one of the data operations fails. Can roll back to before the operation. Commonly used in banking, e-commerce, finance and other systems.