MySQL has two kinds of storage engines: InnoDB and MyISAM
There are six major differences between the two:
|
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 chooseMySQL has a variety of storage engines, each of which has its own advantages and disadvantages, and can be chosen with merit: MyISAM, InnoDB, MERGE, Memory (HEAP), BDB (BerkeleyDB), EXAMPLE, Federated, ARCHIVE, CSV, Blackhole. Although the storage engine in MySQL is not just the MyISAM and the InnoDB two, it is often the two of them. There may be a webmaster did not pay attention to the MySQL storage engine, in fact, the storage engine is a major point in database design, then the blog system should use which storage engine? let's look at the differences between the two storage engines, respectively. first, INNODB support affairs, MyISAM not support, 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. second, MyISAM suitable for query and insert-based applications, InnoDB suitable for frequent modification and related to high-security applicationsThird, INNODB support foreign key, MyISAM not supportIv. from MySQL5.5.5 onwards, InnoDB is the default enginev. INNODB does not support indexes of fulltext typesSix, 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 read out the number of rows saved. Note that when the COUNT (*) statement contains a where condition, MyISAM also needs to scan the entire tableVii. for self-growing fields, InnoDB must contain only the index of the field, but you can establish a federated index with other fields in the MyISAM tableEight, when emptying the entire table, InnoDB is a row of deletes, the efficiency is very slow. MyISAM will rebuild the table .ix. InnoDB supports row locks (or, in some cases, lock whole tables, such as Update table set a=1 where user like '%lee% 'through the above nine points, combined with the characteristics of the individual blog, the recommended personal blog system use MyISAM, because in the main operation of the blog is read and write, there are few chain-type operations. So choose MyISAM Engine to make your blog open also page efficiency is higher than InnoDB engine blog, of course, is only personal advice, most of the blog or according to the actual situation carefully choose. 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, choose which storage engine, depending on the application. If your application is sure to 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. 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.
Some other statements: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.ini file, and 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 MySQLMyISAM: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 largeThe 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 updateInnoDB: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 difference isthat the MyISAM type does 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.
Talk about MySQL two storage engines
