A brief understanding of the differences between MySQL InnoDB and MyISAM

Preface

MySQL supports many types of tables (i.e. storage engines), such as myisam, innodb, memory, archive, example, etc. Each storage engine has its own advantages and disadvantages. A full understanding of each storage engine will help you use them reasonably. Some people think that using multiple storage engines in the same database will greatly affect performance. In fact, this is a very wrong idea. In fact, unless it is a very simple database, using only one storage engine is a very bad behavior for the performance of the application. People who understand databases will choose the appropriate storage engine based on the different functions of each table. This is the correct approach.

As mentioned earlier, there are many storage engines for MySQL, but there are two storage engines that I personally use the most in my work, one is InnoDB and the other is MyISAM. I will talk about these two storage engines here and compare the differences between them.

1. MyISAM

MyISAM is now the default storage engine for MySQL. If you do not specify the table type when creating a table, MySQL will use MyISAM by default. MyISAM has a feature that many people think is very important, but I think it is useless, that is, MyISAM represents independence from the operating system. In simple terms, you can easily migrate MyISAM tables from Windows to Linux or from Linux to Windows. This is indeed an advantage, but which technical director would be so stupid as to change the server's operating system after deciding to use one operating system for a period of time? So I think this advantage has no practical significance.

The MyISAM storage engine is a very important part of the MySQL component. Three types of MyISAM format tables can be created in MySQL - static, dynamic, and compressed. The format does not need to be specified separately, MySQL will automatically select the most appropriate format based on the table structure.

1. MyISAM static

If the data type definition of each field in the table is static (such as char), MySQL will automatically use the static MyISAM format. The performance of tables in this format is very high, which means that queries and updates take very little time, but you must know that this comes at the expense of space. Because each column is allocated the maximum space, even if some of the space is not used, this makes the static table occupy a larger space.

2. MyISAM dynamics

If the data type definition of each field in the table is dynamic (such as varchar), MySQL will automatically use the dynamic MyISAM format. The performance of this type of format table will be reduced, but its space occupancy is much less than the static one.

3. MyISAM Compression

If there is a table that is only designed for reading, you can use MyISAM to compress the table. Under the same configuration, its performance is the fastest.

2. InnoDB

InnoDB is a transactional storage engine that supports transactions by default. Therefore, InnoDB is used as the storage engine in projects that have high requirements for data integrity, such as banking-related projects. InnoDB also introduces row-level locking. Other table types are all fully locked, which means that when a user wants to modify a certain information in his own row in a table, the database will first lock the entire table, and other users cannot operate on this table. This is a full table lock. Row-level locking is different. It only locks the row you want to modify. That is to say, other people can still operate on the table, but they cannot operate the data in the locked row. The benefits are obvious. It is faster and particularly suitable for handling multiple concurrent update requests.

InnoDB also introduces foreign key constraints.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.