Talking about the storage engine of MySQL for table-type storage

What is a MySql database?

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 the table to store and operate on ).

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?

1 MyISAM:This engine was first provided by mysql. This 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 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.

Dynamic MyISAM: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.

Compress MyISAM: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.

The following describes how to view and set the data table type.

Operations on the storage engine in MySql

1. view the storage engines supported by the database

Use the show engines; command to display the storage engines supported by the current database, as shown in 1:

Figure 1 database storage engine

The default data table Type of the current system is MyISAM. Of course, you can set the default table type by modifying the options in the database configuration file.

2. Several commands for viewing table structure and other information

You can use the following commands to view the definition structure of a table:

2.1 Desc [ribe] tablename; // view the data table structure

For example, you can view the structure of table t1.

Figure 2: view the structure of table t1

2.2 Show create table tablename; // displays the table creation statement.

Same as query table t1:

Figure 3 statements used to create table t1

2.3 show table status like 'tablename' \ G displays the current status value of the table

Same as query table t1:

 

Figure 4 Current status value of table t1

In summary, the last two methods can help us to view a table's storage engine type chart marked with a red box ).

3. Set or modify the table Storage Engine

3.1 when creating a database table, set the basic syntax of the storage engine as follows:

 
 

  
  
Create table tableName (
  
  
ColumnName (column name 1) type (data type) attri (attribute setting ),
  
  
ColumnName (column name 2) type (data type) attri (attribute setting ),
  
  
........) Engine = engineName
 
 

For example, if you want to create a table named user, the table contains three fields: id, username, and sex, and the table type is set to merge. You can create the data table as follows,

 
 

  
  
create table user(  
  
  
  id int not null auto_increment,  
  
  
  username char(20) not null,  
  
  
  sex char(2),  
  
  
  primary key(id)  
  
  
) engine=merge 
 
 

For specific execution results, see:

 

Figure 5 create a table user

View the user information of the created table. The current storage engine of the table is merge ,:

 

Figure 6 current status value of table t1

3.2 modify the storage engine. Run the Alter table tableName engine = engineName command.

If you want to change the storage engine of the table user to archive type, run the alter table user engine = archive command. As shown in:

 

Figure 7 storage engine for modifying table users

View the modified table type. It can be seen that the table type has changed to archive type.

Figure 8 Status values after user Modification

Summary

This article mainly introduces what a MySql database is, and further introduces an important feature of it, that is, the plug-in Multi-storage engine mechanism. Then, I briefly introduced what is the storage engine and several major storage engines in MySql. Finally, it introduces how to view all 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 of the table. I am just getting started with MySql. I have some errors in this article. Please give me more instructions!

Edit recommendations]