MyISAM and Innodb comparison _ MySQL

Comparison between MyISAM and Innodb: bitsCN. comMyISAM and Innodb

Http://www.oschina.net/question/17_4248

Considerations for MySQL storage engine InnoDB, MyISAM, and row Lock
2010-02-21 18:21

MyISAM is the default storage engine in MySQL. generally, not many people care about this. It is a tricky thing to decide what storage engine to use, but let's take a look at it. here we only consider MyISAM and InnoDB, because these two are the most common.

Next let's answer some questions:
◆ Does your database have a foreign key?
◆ Do you need transaction support?
◆ Do you need full-text indexing?
◆ What query mode do you often use?
◆ How big is your data?

Myisam only supports index cache

Innodb non-index file data file innodb buffer

Myisam can only manage indexes. when the index data is greater than the allocated resources, it is cached by the operating system. data files depend on the operating system cache. Innodb is managed by itself, regardless of the index or data.

Thinking about the above questions can help you find the right direction, but that is not absolute. If you need transaction processing or foreign keys, InnoDB may be a good method. If you need full-text indexing, MyISAM is usually a good choice because it is built in the system. However, we do not often test the 2 million rows of records. Therefore, even if it is slower, we can use Sphinx to obtain full-text indexes from InnoDB.

The size of data is an important factor that affects your choice of storage engines. large-sized data sets tend to use InnoDB because they support transaction processing and fault recovery. The small size of the database determines the duration of fault recovery. InnoDB can use transaction logs to recover data, which is faster. However, MyISAM may take several hours or even a few days to do these tasks. InnoDB only needs a few minutes.

The habit of operating database tables may also be a factor that has a great impact on performance. For example, COUNT () can be very fast in the MyISAM table, but it may be very painful in the InnoDB table. Primary key query will be quite fast in InnoDB, but be careful that if our primary key is too long, it will also cause performance problems. A large number of inserts statements are faster in MyISAM, but the updates is faster in InnoDB-especially when the concurrency is large.

So which one do you use? Based on experience, if it is a small application or project, MyISAM may be more suitable. Of course, using MyISAM in a large environment can also be very successful, but this is not always the case. If you plan to use a project with a large amount of data and require transaction processing or foreign key support, you should use InnoDB directly. But remember that InnoDB tables require more memory and storage. converting a 100 GB MyISAM table to an InnoDB table may cause a bad experience.

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

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.

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 %"

Any type of table is not omnipotent. you only need to select a proper table type for the business type to maximize the performance advantage 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 on a table that does not support transactions has no effect. it has been submitted before the execution of the commit operation. test:
Execute an 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.

Execute another mysql:
Use test;
Select * from tn;
Select * from ty;
Only tn can see one record
Then on the other side
Commit;
You can see the record.

3. you can 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
However, you can use check table and mysqlcheck [OPTIONS] database [tables]

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

For mysql to use select for update, it must be for InnoDb and be in a transaction.

The select conditions are different. the row-level locks and table-level locks are also used.
Instructions for transferring http://www.neo.com.tw/archives/900

Because InnoDB defaults to Row-Level Lock, MySQL will only execute Row lock (only Lock the selected data example) if a specified primary key is specified explicitly ), otherwise, MySQL will execute Table Lock (Lock the entire data form ).


For example:

Assume that there is a form products, which contains two columns: id and name. id is the primary key.

Example 1: (specify the primary key and use this document, row lock)

SELECT * FROM products WHERE id = '3' for update;

Example 2: (specify a primary key. if no data is found, no lock is required)

SELECT * FROM products WHERE id = '-1' for update;

Example 2: (no primary key, table lock)

SELECT * FROM products WHERE name = 'mouse 'for update;

Example 3: (the primary key is not clear, table lock)

SELECT * FROM products WHERE id <> '3' for update;

Example 4: (the primary key is not clear, table lock)

SELECT * FROM products WHERE id LIKE '3' for update;

Note 1:
For update only applies to InnoDB and takes effect only in the transaction block (BEGIN/COMMIT.

BitsCN.com