Six major differences between InnoDB and MyISAM
| InnoDB with the Myisam The six major differences |
| |
myisam |
innodb |
Composition differences: |
Each MyISAM is stored on disk as three files. The first file name begins with the name of the table, and the extension indicates the file type.
.frm file stores table definitions.
|
disk-based resources are INNODB table spaces The size of the,innodb table is limited only by the size of the operating system file, typically  2GB | , according to the file and its log file.
Transaction-related aspects : |
innodb provides advanced database features such as transaction support transactions, foreign keys, and so on |
/strong>
select
UPDATE insert delete |
If executing a lot of select,myisam is a better choice |
1. If your data performs a large number of inserts or UPDATE, for performance reasons, you should use the InnoDB table
2.DELETE from Table , InnoDB does not reestablish the table, but deletes one row at a time.
3.LOAD TABLE from MASTER operation on InnoDB is not working, the solution is to first change the InnoDB table to MyISAM table, import data and then change to InnoDB table, but for the use of additional InnoDB features (such as foreign keys) of the table is not applicable |
Operation on the auto_increment
|
The internal processing of one auto_incremen column per table.
MyISAMto beINSERTand theUPDATEThe 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 value can be reset by ALTER TABLE or MYISAMCHFor fields of type auto_increment, InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with other fields Better and faster auto_increment processing |
If you specify auto_ for a table Increment column, 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. innodb |
|
select count (*) from Table,myisam simply read out the number of rows saved, and note that when the COUNT (*) statement contains Where condition, the operation of the two tables is the same |
innodb " Saves the exact number of rows in the table, that is, when you execute a 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 engines, 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 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 insertion of the main application, InnoDB suitable for frequent modification and related to the application of high security three, INNODB support foreign keys, MyISAM does not support four, from the MySQL5.5.5,InnoDBis the default engineV. INNODB does not support fulltext type index six, InnoDB does not save the number of rows in the table, such as SELECT COUNT (*) from table, InnoDB need 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 vii. for self-growing fields, the InnoDB must contain only the index of that field, but in the MyISAM table you can establish a federated index with other fields Viii. when you empty the entire table, InnoDB is a one-line deletion, which is very inefficient. MyISAM will rebuild the table IX, INNODB support row lock (in some cases or lock the whole table, such as Update table set a=1 where user like '%lee% ' through the above nine points, combined with the characteristics of personal blog, recommended personal blog system using MyISAM, Because the main operation in the blog is read and write, there are few chained 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. The comparison between InnoDB and MyISAM in MySQL is MyISAM: Each myisam is stored on disk as three files. 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 the data is tightly bound, do not use compression resulting in InnoDB than MyISAM volume is large MyISAM cache in memory is the index, not the 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 indexed.
Disadvantages:Transactions are not supported, foreign keys are not supported, concurrency is small and not suitable for large numbers 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.
Advantages:Support transactions, foreign key support, large concurrency, suitable for a large number of update
Disadvantages: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.
From for notes (Wiz)
MySQL storage engine InnoDB and MyISAM
