Major differences between MyIsam and InnoDB _ MySQL

The main differences between MyIsam and InnoDB are MyIsam and InnoDB. There are four major differences: cache mechanisms, transaction support, locking implementation, and physical data storage methods (including indexes and data ).

1. Cache mechanism

Myisam only caches indexes and does not cache actual data. it will cache this job to the OS-level file system. Therefore, mysiam cache optimization focuses on index cache optimization.

InnoDB has its own buffer pool, which not only caches indexes, but also caches table data.

(Because myisam does not cache table data files (. MYD), every time you read data files, you must call the relevant commands of the file system to read physical files from the disk. Therefore, the setting of the memory buffer required to read data files is very important to the data file access performance. Mysql provides the following two buffer zones for reading data files. the Sequential Scan mode (such as full table Scan) Random Scan (such as index Scan) is not unique to a storage engine, they are all exclusive to threads. each thread will create a (or two) system to set the buffer size when needed .)

2. transaction support

Myisam does not support transactions.

InnoDB supports transactions and primary and foreign keys.

3. lock implementation

Myisam lock is controlled by the mysql service and only supports table-level locks.

InnoDB locks are handed over to the InnoDB storage engine and support row-level locks, page-level locks, and other smaller lock levels. Due to the difference in the lock level, InnoDB is much better than myisam in terms of update concurrency.

4. physical data storage methods (including indexes and data ).

1. file storage method

Myisam each table has three files. frm stores table structure data. MYI stores index information. MYD stores table data.

Innodb stores data including. FRM storage table definition ,. ibd (exclusive tablespace ),. ibdata (shared tablespace ). innodb stores data in standalone and shared tablespaces (which is determined by the innodb_file_per_table variable). ibd "file to store data, and each table has a". ibd file, which is stored in the same location as MyISAM data, determined by datadir. If you use a shared storage tablespace to store data, ibdata files are used for storage. one or more ibdata files can be used for all tables. The ibdata file can be passed through innodb_data_home_dir and innodb_data_file_path
The two parameters are configured together. innodb_data_home_dir configures the directory where data is stored.

2. physical storage of table data

Data in the myisam table is stored in the. MYD file. no pages are used to store data, and no table space is used. Myisam has no clustered index. Myisam has three storage formats: static, dynamic, and compressed.

InnoDB stores all data on a data page. Generally, the size of a non-compressed page is 16 kB. The data files of InnoDB must be clustered by the primary key. Therefore, InnoDB requires that the table must have a primary key (MyISAM may not). if it is not explicitly specified, mySQL automatically selects a column that uniquely identifies a data record as the primary key. if this column does not exist, MySQL automatically generates an implicit field for the InnoDB table as the primary key, this field is 6 bytes in length and its type is long integer. Innod can be divided into shared and exclusive tablespaces, which are controlled by the innodb_file_per_table parameter. The table space structure of InnoDB is as follows:

Relationship between InnoDB rows, pages, sets, segments, and tablespaces

3. storage of index data

The MyIsam index file and data file are separated. The index file only stores the data record address. The primary index and secondary index are non-clustered indexes. The normal size of the index page is 1024 bytes, and the index page is stored in the. MYI file. The MyISAM engine uses B + Tree as the index structure. the data Domain of the leaf node stores the data record address. Is the schematic diagram of the MyISAM index: (borrow graph)

InnoDB also uses B + Tree as the index structure. the index page size is 16 and is stored in the tablespace together with the table data page. From the InnoDB table data storage method, we can see that the InnoDB table data file itself is an index structure organized by B + Tree. the leaf node data field of this Tree stores complete data records. The key of this index is the primary key of the data table, so the InnoDB table data file itself is the primary index. It is the structure of the primary index, that is, the table data (borrowed blog.codinglabs.org), or the clustered index.

Secondary index structure:

The above is a summary of the mysql core story. if you have any mistakes, please click here.