The storage engine of Mysql, the difference between myisam and innodb _ MySQL

This article mainly introduces the differences between the storage engine myisam and innodb of Mysql. if you need it, you can refer to the simple expression below.
MyISAM is a non-transactional storage engine.
Innodb is a storage engine that supports transactions.

The innodb engine is suitable for insert and update operations.
MyISAM is suitable for applications with frequent queries.

MyISAM -- table lock.

Innodb-the row lock is designed properly.
MyISAM will not experience deadlocks.

The biggest difference is that MYISAM is suitable for small data and small concurrency; INNODB is suitable for big data and large concurrency. The biggest difference is the lock level.

The MyISAM type does not support advanced processing such as transaction processing, whereas the InnoDB type does. MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB, but transactions are not supported. InnoDB provides advanced database functions such as external keys for transactions. In summary, different storage types can be used for different data tables. In addition, MyISAM is a file storage service that can be directly copied between different operating systems.

InnoDB:

InnoDB provides MySQL with a transaction-safe (ACID compliant) table with transaction (commit), rollback, and crash recovery capabilities. InnoDB provides locking on row level and non-locking read in SELECTs ). These features improve the performance of multi-user concurrent operations. In the InnoDB table, no need to expand the lock escalation, because the row level locks of InnoDB is suitable for a very small space. InnoDB is the first MySQL table engine to provide foreign key constraints (foreign key constraints. InnoDB is designed to handle large-capacity database systems. its CPU utilization is incomparable to other disk-based relational database engines. Technically, InnoDB is a complete database system on the MySQL background. InnoDB establishes a dedicated buffer pool in the primary memory for high-speed data buffering and indexing. InnoDB stores data and indexes in tablespaces and may contain multiple files, which is different from others. for example, in MyISAM, tables are stored in separate files. The size of the InnoDB table is limited by the file size of the operating system, generally 2 GB. All InnoDB tables are stored in the same data file ibdata1 (multiple files or independent tablespace files), which is relatively difficult to back up, you can copy files or use navicat for mysql.

MyISAM

Each MyISAM table is stored in three files: frm file and table definition. The data file is MYD (MYData ). The index file is an extension of MYI (MYIndex.
Because MyISAM is relatively simple, it is more efficient than InnoDB. it is a good choice for small applications to use MyISAM.
MyISAM tables are saved as files. using MyISAM storage in cross-platform data transfer saves a lot of trouble.

The following are some differences between details and specific implementations:

1. InnoDB does not support FULLTEXT indexes.
2. innoDB does not store the specific number of rows in the table. that is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.
3. for fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.
4. when deleting FROM table, InnoDB does not create a new table, but deletes a row.
5. the load table from master operation does not work for InnoDB. the solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.