What is the difference between mysql and myisam

The difference between mysql and myisam is: mysql is a relational database management system. It is a database based on the relational model. It uses mathematical concepts and methods such as set algebra to process data in the database, while myisam It is the default database engine of mysql (before version 5.5) and does not support transaction processing.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What is the difference between mysql and myisam

myisam

MySQL’s default database engine

MyISAM is the default database engine of MySQL (before version 5.5), which was improved from the early ISAM. Although the performance is excellent, it has a disadvantage: it does not support transaction processing. However, with the development in the past few years, MySQL has also introduced InnoDB (another database engine) to strengthen the referential integrity and concurrency violation handling mechanism, and later gradually replaced MyISAM.

Each MyISAM data table is composed of 3 files stored on the hard disk. Each file has the data table name as the main name of the file, and is matched with different extensions to distinguish the file type:

• .frm - stores data table definitions. This file is not part of the MyISAM engine.

• .MYD——Stores the real data.

• .MYI——Storage index information.

Comparison with InnoDB

InnoDB can use transaction log files (Transaction Log) to recover from program crashes (crash), or unexpected The data error caused by the end; and MyISAM encounters an error and must be completely scanned before rebuilding the index, or correcting the error that was not written to the hard disk. InnoDB's repair time is roughly fixed, but MyISAM's repair time is proportional to the amount of data. Relatively speaking, as the amount of data increases, InnoDB will have better stability.

MyISAM must rely on the operating system to manage the read and write cache, while InnoDB has its own read and write cache management mechanism. (InnoDB will not immediately hand over the modified data pages to the operating system) Therefore, in some cases, InnoDB's data access will be more efficient than MyISAM.

InnoDB currently does not support the compression and terse row formats provided by MyISAM, so it uses a large amount of hard disk and cache. Therefore, starting from version 5.0, MySQL provides another format with a lighter load, which can reduce the system load by about 20%, and the compression function has been planned to be launched in a future new version.

When the operation is fully compatible with ACID (transaction), although InnoDB will automatically merge multiple connections, each time a transaction occurs, it still must be written to the hard disk at least once. Therefore, for some hard disks or disk arrays, This will result in a transaction processing limit of 200 per second. If you want to achieve higher performance and maintain transaction integrity, you must use disk caching and battery backup. Of course, InnoDB also provides several modes that have a lower impact on performance, but they will also reduce the integrity of transactions. MyISAM does not have this problem, but this is not because it is more advanced, it is just because it does not support transactions.

mysql

MySQL is a relational database management system developed by the Swedish MySQL AB company and is currently a product of Oracle. MySQL is one of the most popular relational database management systems. In terms of WEB applications, MySQL is one of the best RDBMS (Relational Database Management System) application software.

MySQL is a relational database management system. A relational database stores data in different tables instead of placing all data in one large warehouse, which increases speed and flexibility.

The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software adopts a dual licensing policy and is divided into community version and commercial version. Due to its small size, fast speed, low total cost of ownership, and especially the characteristics of open source, MySQL is generally chosen as the website database for the development of small and medium-sized websites.

Due to the excellent performance of its community version, it can form a good development environment with PHP and Apache.

Recommended learning: mysql video tutorial

The above is the detailed content of What is the difference between mysql and myisam. For more information, please follow other related articles on the PHP Chinese website!