Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the advantages and differences between mysql data storage engine InnoDB and MyISAM". In daily operation, I believe many people have doubts about the advantages and differences between mysql data storage engine InnoDB and MyISAM. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "what are the advantages and differences between mysql data storage engine InnoDB and MyISAM". Next, please follow the editor to study!

MyISAM: this is the default type, which is based on the traditional ISAM type. ISAM is an abbreviation for Indexed Sequential Access Method (indexed sequential access method). It is the standard way to store records and files. Compared with other storage engines, MyISAM has most of the tools for checking and repairing tables. MyISAM tables can be compressed, and they support full-text search. They are not transactional safe and do not support foreign keys. If things are rolled back, it will result in incomplete rollback, which is not atomic. It is better to perform a large amount of SELECT,MyISAM.

InnoDB: this type is transaction safe. It has the same features as BDB types, and they also support foreign keys. InnoDB tables are fast. It has richer features than BDB, so if you need a transaction-safe storage engine, it is recommended. If your data does a lot of INSERT or UPDATE, you should use the InnoDB table for performance reasons

For InnoDB-type tags that support things, the main reason for affecting speed is that the default setting of AUTOCOMMIT is on, and the program does not explicitly call BEGIN to start a transaction, resulting in automatic Commit for each insert, seriously affecting the speed. You can call begin before executing the sql, and multiple sql forms a single thing (even if the autocommit is open), which will greatly improve 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 is the known difference between the two, for reference only.

Innodb

InnoDB provides MySQL with transaction security (transaction-safe (ACID compliant)) tables with commit, rollback, and crash repair capabilities (crash recovery capabilities). InnoDB provides row locks (locking on row level), which provide unlocked reads (non-locking read in SELECTs) consistent with the Oracle type. These features improve the performance of multi-user concurrent operations. There is no need to expand locking (lock escalation) in the InnoDB table, because InnoDB's column locking (row level locks) is suitable for very small space. InnoDB is the first table engine on MySQL to provide foreign key constraints (FOREIGN KEY constraints).

InnoDB is designed to handle high-capacity systems, and its CPU utilization is unmatched by other disk-based relational database engines. Technically, InnoDB is a complete database system placed in the background of MySQL. InnoDB establishes its dedicated buffer pool in main memory for caching data and indexes. InnoDB stores data and indexes in a tablespace, which may contain multiple files, unlike others. In MyISAM, for example, tables are stored in separate files. The size of the InnoDB table is limited only by the file size of the operating system, which is generally 2 GB.

InnoDB all tables are saved in the same data file ibdata1 (it may be multiple files, or separate tablespace files), which is relatively difficult to back up. The free solution can be to copy data files, back up binlog, or use dump.

MyISAM

MyISAM is the default storage engine for MySQL.

Each MyISAM table is stored in three files. The frm file holds the table definition. The data file is MYD (MYData). The index file is an MYI (MYIndex) extension.

Because MyISAM is relatively simple, it is better than InnoDB.. in efficiency. MyISAM is a good choice for small applications.

MyISAM table is saved as a file, so using MyISAM storage in cross-platform data transfer will save a lot of trouble.

Here are some of the details and differences in implementation:

1.InnoDB does not support indexes of type FULLTEXT.

The specific number of rows of the table is not saved in 2.InnoDB, that is, when select count (*) from table is executed, InnoDB scans the entire table to calculate how many rows there are, but MyISAM can simply read out the number of saved rows. Note that when the count (*) statement contains the where condition, the operation of the two tables is the same.

3. For fields of type AUTO_INCREMENT, the InnoDB must contain an index with only that field, but in the MyISAM table, a federated index can be established with other fields.

When 4.DELETE FROM table, InnoDB does not re-establish the table, but deletes it row by row.

The 5.LOAD TABLE FROM MASTER operation does not work with InnoDB, and the solution is to change the InnoDB table to the MyISAM table first, and then to the InnoDB table after importing the data, but it does not apply to tables that use additional InnoDB features such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute. If the MySQL cannot determine the range to scan when executing a SQL statement, the InnoDB table will also lock the entire table, such as update table set num=1 where name like "% aaa%"

Any kind of table is not omnipotent, only by selecting the appropriate table type for the business type, can we maximize the performance advantages of MySQL.

=

Here are some connections and differences between InnoDB and MyISAM!

Transactions are supported for mysqld above 1.4.0, including non-max versions. The max version of mysqld is required to support transactions.

two。 If you do not specify type when creating a table, it defaults to myisam, and transactions are not supported.

You can use the show create table tablename command to look at the type of watch.

2.1There is no effect to perform start/commit operations on tables that do not support transactions. It has been committed before commit is executed. Test:

Execute a msyql:

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 a record.

Perform another mysql:

Use test

Select * from tn

Select * from ty

Only tn can see a record.

And then on the other side.

Commit

So they can see the records.

3. You can execute the following command to switch non-transactional tables to transactions (no data is lost), and the innodb table is more secure than the myisam table:

Alter table tablename type=innodb

3.1 innodb table cannot use the repair table command and myisamchk-r table_name

But you can use check table, and mysqlcheck [OPTIONS] database [tables]

4. The following parameters have been added to the command line that starts my to make newly published mysql datasheets default to using transactions

Only create statements are affected.)

-- default-table-type=InnoDB

Test commands:

Use test

Drop table if exists tn

Create table tn (a varchar (10))

Show create table tn

5. You can temporarily change the default table type with:

Set table_type=InnoDB

Show variables like table_type

Or:

C:mysqlinmysqld-max-nt-standalone-default-table-type=InnoDB

At this point, the study on "what are the advantages and differences between mysql data storage engine InnoDB and MyISAM" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.