Mysql storage engine and mysql Storage

Mysql storage engine and mysql Storage

Generally, a database is a collection of data. Specifically, a database on a computer can be a collection of files in the memory or a collection of memory data.
MySql Databases and SQL server databases are actually database management systems that can store data and provide functions for querying and updating data in databases. There are differences and commonalities between databases based on how they store data and how they operate data.
MySql is an open-source relational database. Currently, it supports SQL languages, subqueries, stored procedures, triggers, views, indexes, transactions, locks, foreign key constraints, and image replication. In the future, we will explain these functions in detail.
Like Oracle, SQL Server, and other large database systems, MySql is also a single-process, multi-threaded database.
An important feature that distinguishes MySql from other database systems is the support for plug-in storage engines.

So what is a storage engine?

The storage engine explains how to store data, create indexes for the stored data, and update and query data. Because data in relational databases is stored as tables, the storage engine can also be called the table type (that is, the type of storing and operating the table ).
There is only one storage engine in Oracle, SQL Server, and other databases. All data storage management mechanisms are the same. MySql databases provide multiple storage engines. You can select different storage engines for data tables based on your needs. You can also write your own storage engines based on your needs.
What storage engines does MySql have?
 1MyISAM:This engine was first provided by mysql. This engine can be divided into three types: static MyISAM, dynamic MyISAM, and compressed MyISAM:
StaticMyISAM:If the length of each data column in the data table is pre-fixed, the server automatically selects this table type. Because each record in a data table occupies the same space, the efficiency of table access and update is very high. When data is damaged, recovery is easier.
DynamicMyISAM:If the varchar, xxxtext, or xxxBLOB fields appear in the data table, the server automatically selects this table type. Compared with static MyISAM, this table has a small storage space, but because the length of each record is different, after data is modified multiple times, the data in the data table may be stored discretely in the memory, this leads to a decrease in execution efficiency. At the same time, many fragments may occur in the memory. Therefore, this type of table often uses the optimize table command or optimization tool for fragment.
CompressionMyISAM:The two types of tables mentioned above can be compressed using myisamchk. This type of table further reduces the storage used, but the table cannot be modified after compression. In addition, because the data is compressed, such tables must be decompressed first.
However, no matter what MyISAM table is, it currently does not support the functions of transactions, row-level locks, and foreign key constraints.
2 MyISAM Merge engine:This type is a variant of the MyISAM type. Merging tables combines several identical MyISAM tables into a virtual table. It is often used in logs and data warehouses.
3 InnoDB:The InnoDB table type can be seen as a product for further updating MyISAM. It provides functions such as transactions, row-level locks, and foreign key constraints.
4 memory (heap ):This type of data table only exists in memory. It uses hash indexes, so the data access speed is very fast. Because it exists in the memory, this type is often used in temporary tables.
5 archive:This type only supports select and insert statements, and does not support indexes. It is often used in logging and aggregate analysis.
Of course, MySql supports more than the above table types.

Reference: www.cnblogs.com/lina1006/archive/2011/04/29/2032894.html