What is the difference between MySQL storage engine MyISAM and innoDB?

What is the difference between MySQL storage engine MyISAM and innoDB? how do you select these two types in a specific project ?, MyISAM: this is the default type. it is based on the traditional ISAM type. ISAM is short for IndexedSequentialAccessMethod, which is a standard method for storing records and files. compared with other storage engines, the difference between MySQL storage engine MyISAM and innoDB
How do you select these two types in a specific project?

------ Solution --------------------
MyISAM: this is the default type. it is based on the traditional ISAM type, and ISAM is the abbreviation of Indexed Sequential Access Method (Sequential Access Method with indexes, it is a standard method for storing records and files. compared with other storage engines, MyISAM has most tools for checking and repairing tables. myISAM tables can be compressed and support full-text search. they are not transaction-safe and do not support foreign keys. If a transaction is rolled back, incomplete rollback is not atomic. If you execute a large number of SELECT statements, MyISAM is a better choice.

InnoDB: this type is transaction-safe. it has the same features as the BDB type and supports foreign keys. the InnoDB table is fast. it has more features than BDB. Therefore, if you need a transaction-safe storage engine, we recommend that you use it. if your data executes a large number of INSERT or UPDATE operations, InnoDB tables should be used for performance considerations,

For the InnoDB type labels that support transactions, AUTOCOMMIT is enabled by default, and the program does not explicitly call BEGIN to start the transaction. as a result, each inserted entry is automatically Commit, the speed is seriously affected. You can call begin before executing the SQL statement. multiple SQL statements form a transaction (even if autocommit is enabled), which greatly improves the performance.

========================================================== ======================================

InnoDB and MyISAM are the two most commonly used table types in MySQL, each with its own advantages and disadvantages, depending on the specific application. The following are the known differences between the two.

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, the free solution can be copying data files, backing up binlogs, or using mysqldump.


MyISAM
MyISAM is the default storage engine of MySQL.

Each MyISAM table is stored in three files. Frm file storage table definition. The data file is MYD (MYData ). The index file is an extension of MYI (MYIndex.

Because MyISAM is relatively simple, it is much 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.

Www.ixdba.net


In addition, the row lock of the InnoDB table is not absolute. if MySQL cannot determine the scope to be scanned when executing an SQL statement, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %"
------ Solution --------------------
MyISAM is non-transactional, while InnoDB is transactional.

The granularity of MyISAM locks is table-level, while InnoDB supports row-level locks.

MyISAM supports full-text indexes, while InnoDB does not.

MyISAM is relatively simple, so it is more efficient than InnoDB. for small applications, you can consider using MyISAM.

MyISAM tables are saved as files. using MyISAM for cross-platform data transfer saves a lot of trouble.

InnoDB tables are safer than MyISAM tables. when data is not lost, you can switch non-transaction tables to the transaction tables (alter table tablename type = innodb ).