Differences between innodb and myisam

MyISAM and InnoDB explain that InnoDB and MyISAM are the two most common table types when many people use MySQL. The two table types have their own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB tables,

MyISAM and InnoDB explain that InnoDB and MyISAM are the two most common table types when many people use MySQL. The two table types have their own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB tables,

MyISAM and InnoDB

InnoDB and MyISAM are the two most common table types used by many people when using MySQL. The two table types have their own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB, but transactions are not supported. InnoDB provides advanced database functions such as external keys for transactions.

The following are some differences between details and specific implementations:

◆ 1. InnoDB does not support FULLTEXT indexes.

◆ 2. innoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.

◆ 3. For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.

◆ 4. When deleting FROM table, InnoDB does not create a new table, but deletes a row.

◆ 5. the load table from master operation does not work for InnoDB. The solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, however, it is not applicable to 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 be scanned when an SQL statement is executed, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %"

The main difference between the two types is that Innodb supports transaction processing and Foreign keys and row-level locks, but MyISAM does not. Therefore, MyISAM is often considered only suitable for small projects.

From the perspective of MySQL users, Innodb and MyISAM both prefer to use MySQL, but from the perspective of my current O & M database platform, we need to meet the following requirements: 99.9% stability, for convenient scalability and high availability, MyISAM is definitely my first choice.

The reason is as follows:

1. First, most of the projects on my current platform are read-write-less projects, while MyISAM has a much better read performance than Innodb.

2. MyISAM indexes and data are separated, and the indexes are compressed, so the memory usage increases a lot. More indexes can be loaded, while Innodb is closely bound with indexes and data. Without compression, Innodb is much larger than MyISAM.

3. From the platform perspective, it is often the next two months that application developers may accidentally update the where write range of a table, which makes the table unusable, at this time, the superiority of MyISAM is reflected. The files of the corresponding table can be retrieved from the compressed package copied on the current day, stored in a database directory, dumped into SQL, and then exported back to the master database, and add the corresponding binlog. If it is Innodb, I am afraid it cannot be so fast. Don't tell me to let Innodb export xxx regularly. SQL mechanism backup, because the data volume of the smallest database instance on my platform is basically dozens of GB.

4. From the application logic I have come into contact with, select count (*) and order by are the most frequent operations, which may account for more than 60% of the total SQL statements, in fact, this operation of Innodb locks the table. Many people think that Innodb is a row-Level Lock, that is, where is only valid for its primary key, and non-primary keys will lock the entire table.

5. There are often many application departments that need to regularly provide some table data to them. For MyISAM, It is very convenient to send it to the corresponding table's frm. MYD and MYI files, so that they can start the database in the corresponding version, and Innodb needs to export xxx. SQL, because the dictionary data files cannot be used by the other party.

6. If Innodb and MyISAM are compared with insert write operations, Innodb still cannot achieve the Write Performance of MyISAM. If it is an index-based update operation, although MyISAM may be inferior to Innodb, however, it is also a problem whether the database can catch up with such highly concurrent writes. It is better to solve this problem through the Multi-instance database/table sharding architecture.

7. If MyISAM is used, the merge engine can greatly accelerate the development speed of the Application Department. They only need to perform some select count (*) operations on the merge table, it is very suitable for business tables of a type of rows (such as logs, survey statistics) with a total number of hundreds of millions of projects.

Of course, Innodb is not absolutely unnecessary. I use Innodb to simulate stock trading projects with transaction projects, for example. When more than 0.2 million of active users are active, it is also very easy to cope, therefore, I personally like Innodb, but if I start from the database platform application, I will still prefer MyISAM.

In addition, some people may say that MyISAM cannot defend against too many write operations, but I can make up for it through the architecture, saying that my existing database platform capacity is: the total volume of Master/Slave Data is more than several hundred TB, and more than million pv dynamic pages are generated every day. There are also several major projects that do not count as the total number of PVS for calling through data interfaces, (this includes a large project where a single database processes tens of millions of queries every day because memcached was not deployed in the early stage ). The average load of my entire database server is between 0.5 and 1.

Six major differences between InnoDB and Myisam

MyISAM	InnoDB
Differences in composition:	Each MyISAM is stored as three files on the disk. The name of the first file starts with the name of the table. The extension indicates the file type. . Frm file storage table definition. The data file extension is. MYD (MYData ). The extension of the index file is. MYI (MYIndex ).	Disk-based resources are InnoDB tablespace data files and their log files. The InnoDB table size is limited by the operating system file size, generally 2 GB
Transaction processing:	MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB tables, but transactions are not supported.	InnoDB provides advanced database functions such as transactions and external keys.
Select update, INSERT, and Delete operations	If you execute a large number of SELECT statements, MyISAM is a better choice.	1.If you execute a large amount of dataINSERT or UPDATEFor performance considerations, InnoDB tables should be used 2. delete from tableInnoDB does not create a new table, but deletes a row. 3. LOAD TABLE FROM MASTERThe operation does not work for InnoDB. The solution is to first change the InnoDB table to the MyISAM table and then the InnoDB table after the data is imported. However, for the use of additional InnoDB features (such as foreign keys) the table of is not applicable.
Operation on AUTO_INCREMENT	Internal processing of an AUTO_INCREMEN column in each table. MyISAM automatically updates this column for INSERT and UPDATE operations. This makes the AUTO_INCREMENT column faster (at least 10% ). After the value at the top of the sequence is deleted, it cannot be reused. (When the AUTO_INCREMENT column is defined as the last column of the Multi-column index, the deleted value from the top of the sequence can be reused ). The AUTO_INCREMENT value can be reset using alter table or myisamch. For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields. Better and faster auto_increment Processing	If you specify an AUTO_INCREMENT column for a table, the InnoDB table handle in the data dictionary contains a counter named Automatic growth counter, which is used to assign new values to the column. The automatic growth counter is only stored in the primary memory, rather than on the disk. For more information about the Algorithm Implementation of this calculator, see How does the AUTO_INCREMENT column work in InnoDB?
The number of rows in the table.	Select count (*) from table, MyISAM simply reads the number of rows saved. Note that when the count (*) statement contains the where condition, the operations of the two tables are the same.	InnoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows.
Lock	Table lock	Provides locking on row level and non-locking read in SELECTs). In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the scope to be scanned when executing an SQL statement, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %"

Comparison between MySQL database MyISAM and InnoDB Storage Engine

MySQL has multiple storage engines. MyISAM and InnoDB are two common storage engines. Here we will introduce some basic concepts about the two engines (not in depth ).

MyISAM is the default storage engine of MySQL. Based on the traditional ISAM type, full-text search is supported, but it is not transaction secure and does not support foreign keys. Each MyISAM table is stored in three files: the frm file stores the table definition, the data file is MYD (MYData), and the index file is MYI (MYIndex ).

InnoDB is a transaction engine that supports rollback, crash recovery, multi-version concurrency control, ACID transactions, and row-level locking. (row locks of InnoDB tables are not absolute, if MySQL cannot determine the scope of the scan when executing an SQL statement, the InnoDB table will also lock the entire table, such as the SQL statement during the like operation ), and provides the same non-lock read method as Oracle. InnoDB stores its tables and indexes in one tablespace, which can contain several files.

Main differences:

MyISAM is non-transactional, while InnoDB is transactional. The granularity of MyISAM locks is table-level, while InnoDB supports row-level locks. MyISAM supports full-text indexes, while InnoDB does not. MyISAM is relatively simple, so it is more efficient than InnoDB. For small applications, you can consider using MyISAM. MyISAM tables are saved as files. Using MyISAM for cross-platform data transfer saves a lot of trouble. InnoDB tables are safer than MyISAM tables. when data is not lost, you can switch non-transaction tables to the transaction tables (alter table tablename type = innodb ).

Application scenarios:

MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. If the application needs to execute a large number of SELECT queries, MyISAM is a better choice. InnoDB is used for transaction processing applications and has many features, including ACID transaction support. If the application requires a large number of INSERT or UPDATE operations, InnoDB should be used to improve the performance of multi-user concurrent operations.

Common commands:

(1) view the Storage types of tables (three types ):

Show create table tablenameshow table status from dbname where name = tablenamemysqlshow-u user-p password -- status dbname tablename

(2) modify the storage engine of the table:

Alter table tablename type = InnoDB

(3) Add the following parameters to the command line that starts the mysql database to enable transactions for newly published tables by default:

-- Default-table-type = InnoDB

(4) temporarily change the default table Type:

Set table_type = InnoDBshow variables like 'table _ type'