How does MyISAM store data

The MySQL database has a variety of storage engines: For example: MyISAM, InnoDB, MERGE, MEMORY (HEAP), BDB (BerkeleyDB), EXAMPLE, FEDERATED, ARCHIVE, CSV, BLACKHOLE etc. The most common ones are MyISAM and InnoDB The difference between the two MySQL database storage engines MyISAM and InnoDB

The MyISAM engine is a non-transactional engine that offers high-speed storage and retrieval, full-text search capabilities and is suitable for applications with frequent queries such as data warehouses. In MyISAM, a table is actually stored as three files, .frm stores table definitions, .MYD stores data, and .MYI stores indexes. MyISAM is supported in all MySQL configurations. It is the default storage engine unless you configure MySQL to use a different engine by default.
Other non-transactional storage engines on the MySQL server (such as MyISAM) follow different data integrity paradigms called "atomic operations". According to the transaction conditions, MyISAM tables can always work efficiently in AUTOCOMMIT = 1 mode. Atom operations usually offer comparable completeness and better performance. Compared to the fastest transaction table that has been optimized and customized, it is three to five times faster. Since the MySQL server supports two paradigms, you can decide whether to use the speed of atomic operations to better serve your application, or to use transactional functions. The selection can be made according to the table.

InnoDB is an engine that supports transactions. Provides MySQL with a transaction-safe (ACID-compliant) storage engine with commit, rollback, and crash recovery capabilities. All data is stored in one or more data files and supports a locking mechanism similar to that of Oracle. Generally used in OLTP applications. If the InnoDB configuration option is not specified, MySQL creates an auto-expanded data file named ibdata1 in the MySQL data directory and two log files named ib_logfile0 and ib_logfile1.
InnoDB is locked at the row level and also provides a consistent, non-locking Oracle style that is read in the SELECT statement. These features increase deployment and performance for multiple users. In InnoDB, the locking does not need to be extended because the locking at the row level in InnoDB fits into a very small space. InnoDB also supports FOREIGN KEY enforcement. In the SQL query, you can also freely mix InnoDB type tables with other MySQL table types in the same query.
InnoDB was developed for maximum performance when processing large amounts of data. CPU efficiency cannot be matched by any other disk-based relational database engine. The InnoDB storage engine is fully integrated with the MySQL server. The InnoDB storage engine manages its own buffer pool for caching data and indexes in main memory.
InnoDB stores its tables and indexes in a table space that can contain multiple files. InnoDB tables can be of any size, even under operating systems where the file size is limited to 2 GB. InnoDB is also included by default in all MySQL 5.1 binary distributions.

Summarized as follows:

1. Storage structure

MyISAM: Each MyISAM is saved as three files on the hard disk. The name of the first file starts with the name of the table and the extension indicates the file type. Table definitions are stored in the .frm file. The extension of the data file is .MYD (MYData). The extension of the index file is .MYI (MYIndex). InnoDB: All tables are stored in the same data file (possibly multiple files or independent table space files). The size of InnoDB tables is only limited by the size of the operating system file, generally 2 GB.

2. Storage space

MyISAM: It can be compressed and the storage space is small. Three different storage formats are supported: static table (default, but note that no spaces are removed from the end of the data), dynamic table, and compressed table. InnoDB: If more memory and memory are required, a dedicated buffer pool is set up in main memory for high-speed buffering of data and indexes.

3. Transaction support

MyISAM: The focus is on performance. Each query is atomic and runs several times faster than InnoDB, but does not provide transaction support. InnoDB: Offers advanced database functions such as transactions for transaction support and external keys. Transaction-safe (ACID-compatible) type table with transaction (commit), rollback (rollback) and crash recovery functions (crash recovery functions).

4. CURD operation

MyISAM: If you are doing a large number of SELECTs, MyISAM is a better choice. (Because row-level locking is not supported) adding and deleting requires locking the entire table and is less efficient. This is because InnoDB supports row-level locking. When deleting and pasting, all you have to do is lock and modify the row, which is more efficient. InnoDB: If your data does a lot of INSERT or UPDATE for performance reasons, consider using InnoDB tables. DELETE InnoDB has better performance. However, when you create the DELETE FROM table, InnoDB does not re-create the table, but instead deletes it one row at a time. If you want to empty a table with a large amount of data in InnoDB, it is best to use the Truncate Table command.

5. Foreign key

MyISAM: does not support InnoDB: supported

Convert the MySQL database type from InnoDB to MyISAM

Frequent changes

Change the MySQL database module to INNODB:
1. First change my.ini and add under [mysqld]:

default-storage-engine = INNODB

2. Use the SQL statement to change the engine of the table you created:

The table to be changed shows type = InnoDB;