MySQL storage engine MyISAM and InnoDB summary carding-turn

Source: Internet
Author: User
Tags memory usage table definition types of tables server memory

MySQL has two storage engines:InnoDB and MyISAM, and the following table is a simple comparison of the two engines

MyISAM InnoDB



The difference in composition:
Each myisam is stored as three files on disk. The first file name begins with the name of the table, and the extension indicates the file type.
The. frm file stores the table definition.
The data file has an extension of. MYD (MYData).
The extension of the index file is. MYI (Myindex).
A disk-based resource is a InnoDB tablespace data file and its log file, and the size of the InnoDB table is limited only by the size of the operating system file, typically 2GB

Transactional aspects :
MyISAM types of tables emphasize performance and perform several more than InnoDB types, but do not provide transactional support innodb provide transaction support transactions, external keys and other advanced database features
                select  UPDATE INSERT Delete








If you do a lot of select,myisam it's better to choose
1. If your data performs a large number of INSERT or UPDATE , you should use the InnoDB table
for performance reasons 2.DELETE from table , InnoDB does not reestablish the table, but deletes one row at a time. The
3.LOAD table from MASTER operation has no effect on InnoDB, the workaround is to first change the InnoDB table to MyISAM table, import the data and then change to the InnoDB table, However, tables that use additional InnoDB attributes, such as foreign keys, are not available for










to auto_ INCREMENT Operation

 

 

one auto_ per table The internal processing of the Incremen column.
MyISAM for INSERT and UPDATE The action automatically updates this column . This makes the Auto_increment column faster (at least 10%). The value at the top of the sequence cannot be exploited after it has been deleted. (When the Auto_increment column is defined as the last column of a multicolumn index, the use of values removed from the top of the sequence can occur).
Auto_increment values can be reset by ALTER TABLE or MYISAMCH

For fields of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, You can establish a federated index with other fields

Better and faster auto_increment processing

If you specify a auto_increment column for a table, The InnoDB table handle in the data dictionary contains a counter called the autogrow counter, which is used to assign a new value to the column.

 

The

autogrow counter is stored only in main memory, not on disk

For an algorithmic implementation of this calculator, refer to

auto_increment column in InnoDB How to work



The exact number of rows in the table
Select COUNT (*) from Table,myisam as long as you simply read the number of rows saved, note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same The exact number of rows in a table is not saved in InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows






Lock






Table lock
Provides row lock (locking on row level), providing no lock read consistent with Oracle type (non-locking read in
Selects), in addition, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the full table, such as the Update table set num=1 where name like "% aaa% "

MySQL storage engine MyISAM and InnoDB How to choose
MySQL has a variety of storage engine, each storage engine has its own advantages and disadvantages, you can choose to use: MyISAM, InnoDB, MERGE, MEMORY (HEAP), BDB (BerkeleyDB), EXAMPLE, Federated, ARCHIVE, CSV, Blackhole.
Although the storage engine in MySQL is not just MyISAM and InnoDB two, it is commonly used in two.
The approximate difference between the two storage engines is that:
1) InnoDB support transaction, MyISAM not supported, this is very important. Transactions are an advanced way of handling, such as in some column additions and deletions, as long as the error can be rolled back to restore, and MyISAM will not be.
2) MyISAM suitable for query and insert-based applications, InnoDB suitable for frequent modification and involving high security applications
3) InnoDB support foreign keys, MyISAM not supported
4) MySQL5.5.5 is the default engine after InnoDB
5) InnoDB does not support fulltext type of index
6) InnoDB does not save the number of rows in the table, such as SELECT COUNT (*) from table, InnoDB needs to scan through the entire table to calculate how many rows, But MyISAM simply reads out the number of saved rows. Note that when the COUNT (*) statement contains a where condition, MyISAM also needs to scan the entire table,
7) for the self-growing field, the InnoDB must contain only the index of that field, but you can establish a federated index with other fields in the MyISAM table
8) When emptying the entire table, InnoDB is a one-line deletion, which is very inefficient. MyISAM rebuilds the table
9) InnoDB supports row locks (or, in some cases, locking an entire table, such as the Update table set a=1 where user like '%lee% '

About MyISAM and InnoDB Select Use:
MyISAM and InnoDB are the two storage engines provided by the MySQL database. The pros and cons of both are quite the same. InnoDB supports advanced features of relational databases, such as transactional and row-level locks, MyISAM not supported. MyISAM has a better performance and consumes less storage space. So, what kind of storage engine to choose, depending on the application:
1) If your application must use transactions, there is no doubt that you have to choose the InnoDB engine. Note, however, that InnoDB's row-level locks are conditional. The full table is still locked when the where condition does not use the primary key. Delete statements such as delete from MyTable.
2) If your application has high query performance requirements, you will need to use MyISAM. MyISAM indexes and data are separate, and their indexes are compressed to make better use of memory. So its query performance is significantly better than InnoDB. A compressed index can also save some disk space. MyISAM has full-text indexing capabilities, which can greatly optimize the efficiency of like queries.

Some people say that MyISAM can only be used in small applications, but this is only a prejudice.
If the amount of data is large, this needs to be addressed through an upgrade architecture, such as a split-table library, rather than relying solely on the storage engine.

Now generally are selected InnoDB, mainly MyISAM full table lock, read and write serial problem, concurrency efficiency lock table, low efficiency MyISAM for read-write intensive applications is generally not to choose.

About the default storage engine for MySQL databases:
MyISAM and InnoDB are two of MySQL's storage engines.
If it is the default installation, it should be INNODB, you can find the Default-storage-engine=innodb in the my.cnf file;
Of course you can specify the appropriate storage engine when you build the table.
The information can be seen through show create TABLE XX.

Comparison of InnoDB and MyISAM in MySQL
1) MyISAM:
Each myisam is stored as three files on disk. The first file name begins with the name of the table, and the extension indicates the file type. frm file stores the table definition. The data file has an extension of. MYD (MYData).
MyISAM tables can be compressed, and they support full-text search. Transactions are not supported, and foreign keys are not supported. If the rollback of a thing causes incomplete rollback, it does not have atomicity. Table locks are performed when updata, and the concurrency is relatively small. If executing a lot of select,myisam is a better choice.
MyISAM indexes and data are separate, and the indexes are compressed, and memory usage increases a lot. Can load more indexes, and InnoDB is the index and data is tightly bound, no compression will cause innodb than MyISAM volume is large
The MyISAM cache is indexed in memory, not data. The InnoDB cache in memory is the data, relatively speaking, the larger the server memory, the greater the advantages of InnoDB play.

Advantages: Query data is relatively fast, suitable for a large number of select, can be full-text index.
Cons: Transactions not supported, foreign keys not supported, concurrency is small, not suitable for a large number of update

2) InnoDB: (parameter description:mysql storage engine InnoDB Important parameter description )
This type is transaction-safe: It has the same characteristics as the BDB type, and they also support foreign keys. InnoDB tables are very fast. Features richer than BDB, so it is recommended to use a transaction-safe storage engine. The table makes row locks at update time, and the concurrency is relatively large. If your data performs a large number of inserts or update, for performance reasons, you should use the InnoDB table.
Pros: Support transactions, support foreign keys, concurrency is large, suitable for a large number of update
Cons: Query data is relatively fast, not suitable for a large number of select
For INNODB types of tables that support things, the main reason for the speed is that the AUTOCOMMIT default setting is open, and the program does not explicitly call begin to start a transaction, resulting in an automatic commit for each insert, which seriously affects the speed. You can call begin before you execute SQL, and multiple SQL forms a thing (even if the autocommit is open), which will greatly improve performance.

The basic differences are:
MyISAM types do not support advanced processing such as transaction processing, and InnoDB type support.
The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides transactional support for advanced database functions such as external keys.

MySQL storage engine MyISAM and InnoDB summary carding-turn

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.