Shulou(Shulou.com)06/01 Report--

Difference:

1. InnoDB supports transactions, but MyISAM does not. For InnoDB, each SQL language is encapsulated as a transaction by default and automatically committed, which will affect the speed, so it is best to put multiple SQL languages between begin and commit to form a transaction.

2. InnoDB supports foreign keys, but MyISAM does not. Converting an InnoDB table containing foreign keys to MYISAM will fail

3. InnoDB is a clustered index, the data file is tied to the index, there must be a primary key, through the primary key index is very efficient. But the secondary index needs two queries, first query the primary key, and then query the data through the primary key. Therefore, the primary key should not be too large, because the primary key is too large, other indexes will also be very large. While MyISAM is a nonclustered index, the data file is separated, and the index holds the pointer to the data file. The primary key index and secondary index are independent.

4. InnoDB does not save the specific number of rows of the table, and a full table scan is required when performing select count (*) from table. On the other hand, MyISAM uses a variable to save the number of rows of the entire table. When executing the above statement, you only need to read out the variable, which is very fast.

5. Innodb does not support full-text indexing, while MyISAM supports full-text indexing, so MyISAM is more efficient in query.

How to choose:

1. If you want to support transactions, please select innodb. If you don't need to, you can consider MyISAM.

two。 If the vast majority of the tables are just read queries, consider MyISAM, and if you both read and write frequently, use InnoDB.

3. After the collapse of the system, it is more difficult for MyISAM to recover. Can it be accepted?

4. Since the MySQL5.5 version, Innodb has become the default engine of Mysql (previously MyISAM), which shows that its advantages are obvious to all. If you don't know what to use, then use InnoDB, at least not bad.

Differences in composition:

Each MyISAM is stored as three files on disk. The name of the first file starts with the name of the table, and the extension indicates the file type.

The .frm file stores the table definition.

The data file has the extension .MYD (MYData).

The index file has the extension .myi (MYIndex).

Disk-based resources are InnoDB tablespace data files and its log files. The size of InnoDB tables is only limited by the size of operating system files, which is generally 2GB.

Transaction processing:

Tables of type MyISAM emphasize performance and perform several degrees faster than type InnoDB, but do not provide transaction support.

InnoDB provides advanced database functions such as transaction support, foreign keys and so on.

SELECT UPDATE,INSERT,Delete operation

If performing a large amount of SELECT,MyISAM is a better choice.

1. If your data does a lot of INSERT or UPDATE, you should use the InnoDB table for performance reasons

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

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

Operation on AUTO_INCREMENT

Internal processing of one AUTO_INCREMEN column per table.

MyISAM automatically updates this column for INSERT and UPDATE operations. This makes the AUTO_INCREMENT column faster (at least 10%). After the value at the top of the sequence is deleted, it can no longer be used. When the AUTO_INCREMENT column is defined as the last column of a multi-column index, a value deleted from the top of the sequence can be reused.

The AUTO_INCREMENT value can be reset with ALTER TABLE or myisamch

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

Better and faster auto_increment processing

If you specify an AUTO_INCREMENT column for a table, the InnoDB table handle in the data dictionary contains a counter called the auto-growth counter, which is used to assign a new value to the column.

The automatic growth counter is stored only in main memory, not on disk

For the algorithm implementation of the calculator, please refer to the

AUTO_INCREMENT lists how it works in InnoDB

The specific number of rows of the table

Select count (*) from table,MyISAM simply reads out the saved rows. Note that when the count (*) statement contains the where condition, the operation of the two tables is the same.

The specific number of rows of the table is not saved in InnoDB, that is, when select count (*) from table is executed, InnoDB scans the entire table to calculate how many rows there are.

Lock

Watch lock

Provides row locks (locking on row level) and unlocked reads consistent with Oracle types (non-locking read in

SELECTs). 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%"

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.