Mysql storage engine: Differences and advantages of InnoDB and MyISAM

Mysql storage engine: Differences and advantages of InnoDB and MyISAM: This is the default type, which is based on the traditional ISAM type, ISAM is Indexed Sequential Access Method (Sequential Access Method with indexes) it is the 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. Www.2cto.com
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, the main cause of the impact on the speed is that AUTOCOMMIT is enabled by default, and the program does not explicitly call BEGIN to start the transaction, resulting in automatic Commit for each inserted entry, seriously affecting the speed. 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. InnodbInnoDB 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. Www.2cto.com 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 more efficient than InnoDB .. using MyISAM for small applications is a good choice. myISAM tables are saved as files. Using MyISAM storage in cross-platform data transfer saves a lot of trouble.
The following are some details and specific implementation differences: 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. 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 %" is not a panacea for any table. You only need to select an appropriate table type for the business type, in order to maximize the performance advantages of MySQL.
The following are some of the relationships and differences between InnoDB and MyISAM! 1. MySQL 4.0 and above support transactions, including non-max versions. 3.23 requires the max version mysqld to support transactions. 2. If no type is specified during table creation, the default value is myisam. transactions are not supported. You can run the show create table tablename command to view the table type. 2.1 The start/commit operation has no effect on tables that do not support transactions. It has been submitted before the execution of commit. test: run an msyql: www.2cto.com use test; drop table if exists tn; create table tn (a varchar (10) type = myisam; drop table if exists ty; create table ty (a varchar (10) type = innodb; begin; insert into tn values ('A'); insert into ty values ('A'); select * from tn; select * from ty;
You can see one record and execute another mysql: use test; select * from tn; select * from ty; only one record can be seen in tn and then the record can be seen in commit on the other. 3. run the following command to switch the non-transaction table to the transaction (data will not be lost). the innodb table is safer than the myisam table: alter table tablename type = innodb; 3.1 innodb tables cannot use the repair table command and myisamchk-r table_name, but can use check table and mysqlcheck [OPTIONS] database [tables]
4. The following parameters are added to the command line for starting the mysql database so that the newly released mysql Data Tables use transactions by default (only the create statement is affected .) -- Default-table-type = InnoDB test command: www.2cto.com use test; drop table if exists tn; create table tn (a varchar (10); show create table tn;
5. to temporarily change the default table type, you can use: set table_type = InnoDB; show variables like 'table _ type'; or: mysqld-max-nt -- standalone -- default-table-type = InnoDB author xujinyang