Talk about the difference between InnoDB and MyISAM

MyISAM and InnoDB explanation

InnoDB and MyISAM are the two most common table types used by many people when using MySQL, both of which have pros and cons, depending on the application. The basic difference is that 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.

The following are some of the details and the specific implementation differences:

1. InnoDB does not support indexes of type Fulltext.

2. 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, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.

3. For a field 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.

4. Delete from table, InnoDB does not reestablish the table, but deletes one row at a time.

5. The LOAD table from master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for 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 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%"

The main difference between the two types is that InnoDB supports transactional and foreign key and row-level locks. MyISAM is not supported. So MyISAM tend to be considered only suitable for use in small projects.

I use MySQL as a user point of view, InnoDB and MyISAM are more like, but from my current operation of the database platform to meet the requirements: 99.9% stability, convenient scalability and high availability, MyISAM is definitely my first choice.

The reasons are as follows:

1, first of all, I am currently on the platform of the majority of projects are read more write less projects, and MyISAM reading performance is stronger than InnoDB.

2, MyISAM index and data are separate, and the index is compressed, the memory usage of the corresponding improved a lot. Can load more indexes, and InnoDB is the index and the data is tightly bound, do not use compression which will cause innodb than MyISAM volume is large.

3, from the platform point of view, often 1, 2 months will occur application developers accidentally update a table where the scope of the wrong, resulting in this table can not be normal use, this time MyISAM the superiority of the embodiment, casually from the day copy of the compressed package out of the corresponding table file, Put it in a database directory, then dump into SQL and back to the main library, and binlog the corresponding. If it's InnoDB, I'm afraid it can't be so fast, don't tell me to let InnoDB regularly back up with an export xxx.sql mechanism, because the smallest database instance on my platform has a size of dozens of g of data.

4, from my contact with the application logic, select COUNT (*) and order BY is the most frequent, probably can account for the entire SQL total statement of more than 60% of the operation, and this operation InnoDB actually will lock the table, many people think InnoDB is a row-level lock, That's just where the primary key is valid, and the non-primary key will lock the full table.

5, there is often a lot of application departments need me to give them regular data on some tables, MyISAM words are very convenient, as long as they correspond to the list of the frm. myd,myi files, let them in the corresponding version of the database to start the line, and InnoDB need to export xxx.sql, because the light to other people's files, by the dictionary data file, the other side is not available.

6, if and myisam than insert write operation, InnoDB also not up to MyISAM write performance, if is for index-based update operation, although MyISAM may be inferior innodb, but so high concurrency of write, from the library can chase is also a problem, It might as well be solved by a multi-instance sub-Library table architecture.

7, if it is used MyISAM, the merge engine can greatly speed up the development of the application department, they just do some select count (*) operation on this merge table, it is very suitable for a large project total of about hundreds of millions of rows of a type (such as log, survey statistics) business table.

Of course, InnoDB is not absolutely not, with business projects such as simulation stocks, I am using InnoDB, active users more than 200,000, is also very easy to cope with, so I personally also like InnoDB, but if from the database platform application, I would prefer MyISAM.

In addition, some people may say that you myisam can not resist too much write operation, but I can make up by the structure, say my existing database platform capacity: The total number of master and slave data in more than hundreds of T, more than 1 billion PV dynamic page per day, there are several large items are called by the data interface method is not counted into PV total, ( This includes a large project because the initial memcached was not deployed, resulting in a single database processing 90 million queries per day). My overall database server load averaged around 0.5-1.

Six major differences between InnoDB and MyISAM

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
transaction-handling aspects:	Tables of the MyISAM type emphasize performance, which is performed more quickly than the InnoDB type, but does not provide transactional support	InnoDB provides transaction support transactions, external keys and other advanced database functions
SELECT update,insert,delete Operation	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 does not work for InnoDB, the solution is to first change the InnoDB table to MyISAM table, import data and then change to InnoDB table, However, tables that use additional InnoDB attributes (such as foreign keys) do not apply
action on auto_increment	the 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%). 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). The   auto_increment value can be used by ALTER TABLE or MYISAMCH to reset    for fields of type auto_increment, The 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 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 counters are stored only in main memory, not on disk    algorithmic implementations of the calculator, see    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 database MyISAM and InnoDB storage engine comparison

MySQL has a variety of storage engines, MyISAM and InnoDB are two of the most common. Here are some basic concepts about these two engines (not in-depth introduction).

MyISAM is the default storage engine for MySQL, based on the traditional ISAM type, which supports full-text search, but is not transaction-safe and does not support foreign keys. Each MyISAM table is stored in three files: the frm file is stored as a table definition, the data file is MyD (MYData), and the index file is myi (Myindex).

InnoDB is a transactional engine that supports rollback, crash resiliency, multi-version concurrency control, acid transactions, support for row-level locking (innodb table row locks are 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 SQL statement at the time of the operation, and provide a non-lock read that is consistent with the Oracle type. InnoDB stores its tables and indexes in a table space, a tablespace can contain several files.

Main differences:

• MyISAM is a non-transactional security type, and InnoDB is a transaction-safe type.
• The granularity of MyISAM locks is table-level, while InnoDB supports row-level locking.
• MyISAM supports full-text type indexing, while InnoDB does not support full-text indexing.
• MyISAM is relatively simple, so in terms of efficiency is better than INNODB, small applications can consider the use of MyISAM.
• MyISAM tables are saved as files, and using MyISAM storage in cross-platform data transfer saves a lot of hassle.
• The InnoDB table is more secure than the MyISAM table and can be switched from non-transactional tables to transaction tables (ALTER TABLE tablename TYPE=INNODB) without ensuring that the data is not lost.

Application Scenarios:

• MyISAM Manage non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is a better choice if you need to perform a large number of select queries in your application.
• InnoDB is used for transactional applications and has many features, including acid transaction support. If you need to perform a large number of insert or update operations in your app, you should use InnoDB, which can improve the performance of multiple user concurrency operations.

Common commands:

(1) View the storage type of table (three kinds):

• Show CREATE TABLE TableName
• Show table status from DBName where Name=tablename
• Mysqlshow-u user-p password--status dbname tablename

(2) Modify the table's storage engine:

• ALTER TABLE TableName TYPE=INNODB

(3) Add the following parameter to the command line that starts the MySQL database so that the newly published table will use the transaction by default:

• --default-table-type=innodb

(4) Temporarily change the default table type:

• Set Table_type=innodb
• Show variables like ' Table_type '

Talk about the difference between InnoDB and MyISAM