MySQL database plug-in multi-storage engine mechanism

What is a MySQL database?

Typically, a database is a , and a database can be a collection of files on a memory or a collection of some memory data.

We usually say that the MySQL database, SQL Server database, etc. is actually a database management system, they can store data, and provide query and update the data in the database function, and so on. There are differences and similarities between these databases, depending on how the database stores the data and how the data is implemented.

The MySQL database is a relational database of open source code. Currently, it can provide the following functions: Support SQL language, subqueries, stored procedures, triggers, views, indexes, transactions, locks, foreign key constraints, and image replication. In the latter part, we will explain these features in detail.

Like Oracle and SQL Server and other large database systems, MySQL is also a client/server system and a single process multithreaded schema database.

One important feature that MySQL distinguishes from other database systems is that it supports the plug-in storage engine.

So what is a storage engine?

The storage engine is simply how to store the data, how to index the stored data, and how to update and query the data to realize the technology. 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 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 datasheets based on different requirements, and users can write their own storage engines according to their own needs.

What storage engines are in MySQL?

1 MyISAM: This engine is the earliest provided by MySQL. This engine can be divided into static MyISAM, dynamic MyISAM and compression MyISAM three kinds:

Static MyISAM: If the length of each data column in the datasheet is fixed beforehand, the server will automatically select this type of table. Because each record in the datasheet occupies the same amount of space, this table access and update efficiency is very high. Recovery is also easier to do when data is compromised.

Dynamic MyISAM: When a varchar, xxxtext, or Xxxblob field appears in a datasheet, the server automatically chooses this type of table. Compared to static MyISAM, this kind of table storage space is relatively small, but because each record is different in length, so the data in the data table may be stored in memory, which will result in lower execution efficiency. Also, there may be a lot of fragmentation in memory. Therefore, this type of table should often be defragmented with the Optimize table command or the Optimization tool.

Compression MyISAM: The two types of tables mentioned above can be compressed using the Myisamchk tool. This type of table further reduces the amount of storage consumed, but this table compression cannot be modified again. In addition, because it is compressed data, so the table in the reading of the first to decompress.

However, regardless of the MyISAM table, it does not currently support the functionality of transactions, row-level locks, and foreign key constraints.

2 MyISAM Merge Engine: This type is a variant of the MyISAM type. A merged table is a combination of several identical MyISAM tables into a single virtual table. Often applies to logs and data warehouses.

The 3 Innodb:innodb table type can be viewed as a further update product for MyISAM, which provides functionality for transactions, row-level locking mechanisms, and foreign key constraints.

4 Memory (Heap): This type of data table exists only 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 supports only select and INSERT statements and does not support indexing. Often applied to logging and aggregation analysis.

Of course, MySQL supports more than a few table types.

Here's how to view and set the data table type.

Operations in MySQL about the storage engine

1 View the storage engine that the database can support

with show engines; command to display the storage engine conditions supported by the current database, as shown in Figure 1:

Figure 1 Storage engine for the database

The default datasheet type for the current system, visible from the figure above, is MyISAM. Of course, we can set the default table type by modifying the options in the database configuration file.

2 several commands for viewing information such as the structure of a table

To see information such as the definition structure of a table, you can use the following commands:

2.1desc[ribe] TableName; View the structure of a datasheet

For example, to see the structure of a table T1, the following figure is available.

Figure 2: Viewing the structure of the table T1

2.2 Show CREATE TABLE tablename; Show creation statements for tables

The above query table T1, get the following figure:

Figure 3 shows the statement creating the table T1

2.3 Show Table status like ' tablename ' \g display the current state value of a table

The above query table T1, get the following figure:

Figure 4 shows the current status value of the table T1

In summary, the last two ways can help us see the type of storage engine for a table (shown in a red box).

3 setting or modifying the table's storage engine

3.1 The basic syntax for setting up storage engines when creating database tables 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 that includes the ID, username username, and gender sex three fields, and you want to set the table type to merge. You can create this datasheet in the following ways.

create   table  user (    id  int  not  null  auto_ Increment,     USERNAME  char  not  null ,     sex  char (2),       primary   key (ID)   )  engine=merge

The results of the implementation are shown in the following diagram:

Figure 5 Creating a table user

View information about the created table user, the current storage engine for the visible table is merge, as shown in the figure:

Figure 6 shows the current status value of the 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 for the table user to the archive type, you can use the command ALTER TABLE user engine=archive. As shown in the following illustration:

Figure 7 Storage engine that modifies table user

Viewing the modified table type, the visible table type has changed to archive type.

Figure 8 shows the modified state value of the table user

Summary

This article mainly introduces what is the MySQL database, and further leads to an important feature of it, that is, the plug-in multiple 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 storage engine type of the database table, and how to set or modify the storage engine type for the table. Just getting started to learn MySQL, there are errors in the article, but also please help us a lot of guidance!