MySQL Storage engine

What is a MySQL database

Typically, a database is a collection of data, and a database can be a collection of files on a memory or a collection of some memory data.
What we usually call MySQL database, SQL Server database and so on is actually a database management system, they can store data, and provide the ability to query and update data in the database, and so on. Depending on how the database stores the data and how the data is implemented, there are differences and similarities between the databases.
The MySQL database is a relational database of open source code. Currently, it can provide features such as support for SQL language, subqueries, stored procedures, triggers, views, indexes, transactions, locks, foreign key constraints, and image replication. In the late stages, we will explain these features in detail.
As with large database systems such as Oracle and SQL Server, MySQL is also a database of client/server systems and single-process multithreaded architectures.
One important feature of MySQL that differs from other database systems is the support for the plug-in storage engine.

So what is a storage engine?

The storage engine is how to store the data, how to index the stored data, and how to update and query the data. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, the type that stores and operates this table).
There is only one storage engine in a database 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 the data table according to different requirements, and users can write their own storage engine according to their own needs.
What storage engines are in MySQL?
 1MyISAM:This engine was first provided by MySQL. This engine can also be divided into static MyISAM, dynamic MyISAM and compression MyISAM three kinds:
StaticMyISAM:If the length of each data column in the datasheet is pre-fixed, the server will automatically select this type of table. Because each record in the data table occupies the same amount of space, the table accesses and updates are highly efficient. When data is compromised, recovery is easier to do.
DynamicMyISAM:If the varchar, xxxtext, or Xxxblob fields appear in the datasheet, the server will automatically select this type of table. Compared with static MyISAM, this kind of table storage space is relatively small, but because of the length of each record is different, so the data in the data table can be stored in memory after multiple modifications, resulting in a decrease in execution efficiency. Also, there may be a lot of fragmentation in memory. Therefore, this type of table is often defragmented with the Optimize table command or the Optimization tool.
CompressionMyISAM:The two types of tables mentioned above can be compressed with the Myisamchk tool. This type of table further reduces the amount of storage consumed, but the table can no longer be modified after it is compressed. In addition, because it is compressed data, such a table should be read to the first time to extract the rows.
However, regardless of the MyISAM table, it does not currently support transactional, row-level, and foreign key constraints.
2 MyISAM Merge engine:This type is a variant of the MyISAM type. Merging tables is the merging of several identical MyISAM tables into a single virtual table. Often applied to logs and data warehouses.
3 InnoDB:The InnoDB table type can be thought of as a further update to the MyISAM product, which provides the functionality of transaction, 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 the data is accessed very quickly. Because it exists in memory, this type is often applied to temporary tables.
5 Archive:This type only supports SELECT and INSERT statements, and does not support indexing. Often applied to logging and aggregation analysis.
Of course, MySQL supports more than just a few types of tables.

Let's look at how to view and set the data table type.

Operations on the storage engine in MySQL

1 Viewing the storage engines that a database can support
with show engines; The command can display the storage engine supported by the current database, as shown in 1:

Figure 1 Storage engine for the database

The default data table type that is visible by the current system is MyISAM. Of course, we can set the default table type by modifying the options in the database configuration file.
2 several commands to view information such as the structure of a table
To view information such as the definition structure of a table, you can use the following commands:
2.1desc[ribe] tablename; View the structure of a data table
For example, look at the structure of the table T1, available.

Figure 2: View the structure of the table T1

2.2 Show CREATE TABLE tablename; To display the creation statement for a table
The same query table T1, to:

Figure 3 shows the statement that created the table T1

2.3 Show Table status like 'tablename '\g displays the current status value

The same query table T1, to:

Figure 4 shows the current status value of table T1

As you can see, the latter two methods help us look at the type of storage engine for a table (shown in a red box).
3 setting or modifying a table's storage engine
3.1 The basic syntax for setting the storage engine when creating a database table is:
Create table tableName(
ColumnName (column name 1) type (data type) Attri (property setting),
ColumnName (Column Name 2) type (data type) Attri (property setting),
........) Engine = enginename

For example, suppose you want to create a table named user, this table includes IDs, user names username, and sex sex three fields, and you want to set the table type to merge. You can create this data table in the following way,

CREATE TABLE User (

ID int NOT NULL auto_increment,

Username char () NOT NULL,

Sex char (2),

Primary KEY (ID)

) Engine=merge

For specific implementation results, see:

Figure 5 Creating a table user

                                                                                                                                                                                                                                                   

To view the information for the created table user, the current storage engine for the table is the merge:

Figure 6 shows the current status value of table T1

3.2 Modify the storage engine, you can use the command ALTER TABLE tableName engine =enginename

If you need to modify the storage engine of the table user to archive type, you can use the command ALTER TABLE user engine=archive. As shown in the following:

Figure 7 Modifying the table user's storage engine

Viewing the modified table type, the visible table type has become the archive type.

Figure 8 showing the table user-modified state values

Summary
In this paper, we mainly describe what is MySQL database, and further introduces its important feature, that is, the plug-in multi-storage engine mechanism. Then, a brief description of what is the storage engine and several major storage engines in MySQL. Finally, how to view all the storage engines supported by the database, how to view the type of storage engine for a database table, and how to set or modify the table's storage engine type are described. Just get started to learn MySQL, there are errors in the text, but also ask you a lot of guidance!

MySQL Storage engine