InnoDB and MyISAM

The main difference between the two types is that InnoDB supports transactional and foreign key and row-level locks. And MyISAM does not support.

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, the only Where the primary key is valid, 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.

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 store table definition.    data file extension. MYD (MYData).    index file extension is. MYI (Myindex).
   transactional Aspects : Span style= "FONT-SIZE:12PX;" >	myisam types of tables emphasize performance, It executes more quickly than the InnoDB type, but does not provide transactional support	  INNODB provides transaction support transactions, advanced database functions such as external keys
   select   Update,insert , Delete action		   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 re-establish the table. Instead, a row of rows is deleted.    3.load   TABLE from MASTER The operation does not work for InnoDB, the workaround is to first change the InnoDB table to MyISAM table, import the data and then change to the InnoDB table, but the table for the additional InnoDB characteristics (such as foreign keys) used is not appropriate for
   action on auto_increment	internal processing of one auto_incremen column per table.    MyISAM is INSERT and update actions automatically update 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 MYISAMCH    for fields of the auto_increment type, 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 that is called the autogrow counter. It is used to assign a new value to the column.    autogrow counters are stored only in main memory, not on disk    for the algorithm implementation of this calculator, refer to    auto_increment How the column works in InnoDB
   table's exact number of rows	select count (*) from Table,myisam simply read out the number of rows saved, and note that When the COUNT (*) statement contains the    where condition, the operation of the two tables is the same	innodb does not save the exact number of rows in the table, that is, when you execute select COUNT (*) from table, InnoDB to scan through 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% "

InnoDB and MyISAM (EXT)