MySQL change table engine InnoDB for MyISAM method summary

The common MySQL table engine has InnoDB and MyISAM, the main difference is that InnoDB is suitable for frequent write database operations, MyISAM suitable for reading the database a little bit more, how to change the table engine InnoDB to MyISAM?

Use the following MySQL SQL statement to set the database engine for the table:

ALTER TABLE ' wp_posts ' ENGINE = MyISAM;

The database engine for this table must be a MyISAM type when you need to use MySQL's Full-text index (fulltext index). What is the specific difference between InnoDB for the MyISAM database engine

Example

Modify the table's storage engine MYISAM<=>INNODB

To view the storage engine for a table
Mysql> Show CREATE TABLE tt7;
+-------+------------------------------------------------------------------------------------------------------ -------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------ -------------------+
| Tt7 | CREATE TABLE ' Tt7 ' (
' ID ' int (a) default NULL,
' Name ' char (#) Default NULL
) Engine=myisam DEFAULT charset=latin1 |
+-------+------------------------------------------------------------------------------------------------------ -------------------+
1 row in Set (0.00 sec)
View the amount of data in a table
Mysql> Select COUNT (1) from TT7;
+----------+
| COUNT (1) |
+----------+
| 16777216 |
+----------+
1 row in Set (0.00 sec)

Method One:

Directly change the storage engine
mysql> ALTER TABLE TT7 ENGINE=INNODB;
Query OK, 16777216 rows affected (2 min 39.80 sec)
records:16777216 duplicates:0 warnings:0

Method Two:

Change the storage engine in method one back to MyISAM
mysql> ALTER TABLE TT7 Engine=myisam;
Query OK, 16777216 rows Affected (27.09 sec)
records:16777216 duplicates:0 warnings:0
You can also see from here that the MyISAM table is much faster than the InnoDB table.

Create a table with the same table structure as Tt7
Mysql> CREATE table tt7_tmp like tt7;
Query OK, 0 rows affected (0.02 sec)

Tt7_tmp as intermediate result set
mysql> INSERT INTO tt7_tmp select * from Tt7;
Query OK, 16777216 rows Affected (27.20 sec)
records:16777216 duplicates:0 warnings:0

Delete data from the original table
mysql> truncate TABLE tt7;
Query OK, 16777725 rows affected (0.18 sec)

This time to change the storage engine of the original table
mysql> ALTER TABLE TT7 ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)
records:0 duplicates:0 warnings:0
The speed is complete.

and return the data from the intermediate result set back to the original table.
mysql> INSERT INTO TT7 select * from Tt7_tmp;
Query OK, 16777216 rows affected (2 min 0.95 sec)
records:16777216 duplicates:0 warnings:0

Delete an intermediate table
mysql> drop table tt7_tmp;

Test results:

Method two faster, but if the data volume is large, method two will adopt a fragmented batch operation, or insert operation will be time-consuming, and generate a lot of undo log.
If it is a small table (within 500M, according to their own system hardware environment), the use of a method can be
If it is a large table, then use the method of two + batch mode

If the storage engine for a bulk change table

The SQL statement used to generate the change:

SELECT CONCAT (' ALTER TABLE ', table_name, ' engine=innodb; ') From Information_schema.tables WHERE table_schema= ' db_name ' and engine= ' MyISAM ';

SQL statement used to generate the check table:

SELECT CONCAT (' CHECK TABLE ', table_name) from Information_schema.tables WHERE table_schema= ' db_name ';

Modify the following parameters according to your system configuration to speed up the change (remember the previous value, you'll have to change it back)

SET GLOBAL sort_buffer_size=64*1024*1024;
SET GLOBAL tmp_table_size=64*1024*1024;
SET GLOBAL read_buffer_size=32*1024*1024;
SET GLOBAL read_rnd_buffer_size=32*1024*1024;

The difference between MyISAM and InnoDB engines in MySQL

MyISAM is the default storage engine in MySQL, and it's not usually a lot of people who care about this stuff. Deciding what kind of storage engine to use is a very tricky thing to do, but it's worth studying, and the article here only considers the two MyISAM and InnoDB, because these are the two most common.

Tell me about your knowledge of MySQL MyISAM and InnoDB. And what do you think their differences are there? Why?

A: These two are MySQL primary storage engines.

Brief introduction from the official website.

Brief introduction: MyIsam

Myidam is the default storage engine. It's based on older ISAM code, but there are a lot of useful extensions. (Note that MySQL5.1 does not support ISAM).

Each myisam is stored on disk as three files. The first file begins with the name of the table, and the extension indicates the file type.. frm file storage table definition. The data file has an extension of. MyD (MYData). The name of the index file extension is. Myi (Myindex).

Brief introduction: InnoDB

INNODB provides MySQL with transaction security (acid-compatible) storage engine with Commit, rollback, and crash recovery capabilities. InnoDB locks the row level and also provides an oracle-consistent, unlocked read in the SELECT statement. These features add to multi-user deployment and performance. There is no need to enlarge the lock in InnoDB because the InnoDB row-level locking fits very small spaces. InnoDB also supports foreign key coercion. In SQL queries, you can freely mix innodb types of tables with other types of MySQL tables, even in the same query.

InnoDB is the maximum performance design for processing large amounts of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine.

The InnoDB storage engine is fully consolidated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its table & index in a tablespace, a tablespace can contain several files (or a raw disk partition). This is different from the MyISAM table, for example in the MyISAM table where each table is separated from the file. The InnoDB table can be any size, even on an operating system that has a file size limited to 2GB.

InnoDB is included in the MySQL binary distribution by default. Windows Essentials Installer makes InnoDB the default table for MySQL on Windows.

InnoDB is used in many large database sites that require high performance. The famous Internet news site slashdot.org runs on the InnoDB. Mytrix Inc. stores more than 1TB of data on InnoDB, and some other sites handle an average of 800 inserts/updates per second on the InnoDB.

A few simple generalizations are as follows:

1. Through the above, it is not difficult to see that the use of MyIsam InnoDB, MyIsam mainly applicable to small and medium data volumes. The InnoDB engine is suitable for large amounts of data. It's already clear. The famous open source e-business system [Magento] is created using InnoDB.

2. MyISAM writes faster than InnoDB.

3. When using InnoDB, you need to configure the MY.CNF to ensure that MySQL achieves maximum efficiency. Details can be viewed at the official website [inndo Performance adjustment]:
Http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#innodb-tuning.

A lot of them have published online about MySQL Myidam and InnoDB differences in storage and reading. No research has been done yet.

Basic tests are based on comparisons using [transactions] and not using [transactions]. MySQL compares blocks for [non-transaction table] speeds.

Let us answer some of the following questions:
Do you have a foreign key in your database?
Do you need business support?
Do you need full-text indexing?
What kind of query mode do you often use?
How big is your data?

MyISAM only Index cache

InnoDB no index file data file InnoDB buffer

MyISAM can only manage indexes, which are cache by the operating system when the index data is larger than the allocated resource, and the data files depend on the cache of the operating system. InnoDB whether it's an index or a data, you manage it yourself.

Thinking about these issues can help you find the right direction, but that's not absolute. If you need a transaction or a foreign key, then InnoDB may be a better way. If you need Full-text indexing, then MyISAM is usually a good choice because it's built into the system, but we don't actually test 2 million of lines of records regularly. So, even slowly, we can get full-text indexing from InnoDB by using Sphinx.

The size of the data is an important factor that affects what storage engine you choose, and large data sets tend to choose InnoDB, because they support transaction processing and failback. The size of the database determines the length of the recovery, InnoDB can use the transaction log for data recovery, which is faster. And MyISAM may take hours or even days to do these things, InnoDB only take a few minutes.

Your habit of manipulating database tables may also be a significant factor in performance impact. For example, COUNT () can be very fast in the MyISAM table, and it can be painful under the InnoDB table. The primary key query is pretty fast under InnoDB, but it's important to be careful if our primary key is too long to cause performance problems. A large number of inserts statements would be faster under MyISAM, but updates would be quicker under innodb-especially when the volume of concurrency was high.

So, exactly which one do you use? From experience, if it is a small application or project, then MyISAM may be more appropriate. Of course, using MyISAM in a large environment can be a great success, but it's not always the case. If you are planning to use a project with a large amount of data, and require transaction processing or foreign key support, you should really use the INNODB approach directly. But remember that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables can be a very bad experience for you.

===========================================================

MyISAM: This is the default type, which is based on the traditional ISAM type, and ISAM is the abbreviation for Indexed sequential access method (indexed sequential access methods), which is the standard way to store records and files. Compared to other storage engines, MyISAM has most of the tools for checking and repairing tables. MyISAM tables can be compressed, and they support full-text search. They are not transaction-safe and do not support foreign keys. If something is rolled back it will cause an incomplete rollback and not be atomic. It is a better choice if you perform a lot of select,myisam.

InnoDB: This type is transaction-safe. It has the same characteristics as the BDB type, and they also support foreign keys. The InnoDB table is fast. Features that are richer than bdb, so it is recommended if you need a transaction-safe storage engine. If your data performs a large number of inserts or updates, you should use the InnoDB table for performance reasons.

For INNODB types 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 transactions, causing each insert to automatically commit, seriously affecting the speed. You can call begin before executing SQL, and multiple SQL forms one thing (even if the autocommit is open), which can greatly improve performance.

===============================================================

InnoDB and MyISAM are the most commonly used two table types in MySQL, each with its own pros and cons, depending on the specific application. The following is a known difference between the two, for informational purposes only.

InnoDB
InnoDB provides MySQL with transaction security (commit), rollback (rollback), and crash repair (crash recovery capabilities) (Transaction-safe (ACID compliant ) Type table. InnoDB provides a row lock (locking on row level) that provides an unlocked read (non-locking read in selects) consistent with the Oracle type. These features improve the performance of multi-user concurrency operations. You do not need to extend the lock (lock escalation) in the InnoDB table because InnoDB column locks (row level locks) are suitable for very small spaces. InnoDB is the first table engine on MySQL that provides a foreign key constraint (FOREIGN key constraints).

InnoDB's design goal is to handle a large-capacity database system, which is not comparable to other disk-based relational database engines. Technically, InnoDB is a complete database system in the MySQL background, InnoDB to build its dedicated buffer pool in main memory for caching data and indexing. InnoDB the data and index in a tablespace, possibly containing multiple files, unlike other, for example, in MyISAM, tables are stored in separate files. The size of the InnoDB table is limited to the file size of the operating system, typically 2 GB.
InnoDB All tables are stored in the same data file ibdata1 (or multiple files, or separate tablespace files), relatively difficult to backup, the free scheme can be copied data files, backup Binlog, or with mysqldump.

MyISAM
MyISAM is the MySQL default storage engine.

Each MyISAM table is stored in three files. The frm file holds the table definition. The data file is MyD (MYData). Index files are myi (myindex) extensions.

Because MyISAM is relatively simple, so it is better than innodb in efficiency. Small applications use MyISAM is a good choice.

MyISAM tables are saved in the form of files, and using MyISAM storage in Cross-platform data transfer saves a lot of trouble

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

The fulltext type index is not supported for 1.InnoDB.
The number of rows in the table is not saved in 2.InnoDB, that is, when the select count (*) from table is executed, InnoDB scans the entire table to calculate the number of rows, but MyISAM simply reads out the saved rows. Note that when the COUNT (*) statement contains the Where condition, the operations of the two tables are the same.
3. For Auto_increment type fields, the InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with the other fields.
4.DELETE from table, InnoDB does not re-establish the table, but deletes one row at a time.
5.LOAD table from Master does not work for InnoDB, the solution is to first change the InnoDB table to MyISAM table, import the data and then change to InnoDB table, but for the use of additional InnoDB features (such as foreign key) of the table does not apply.

In addition, row locks on innodb tables are not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the entire table, such as Update table set num=1 where name like "%aaa%"

Any kind of table is not omnipotent, only appropriate for the business type to choose the appropriate table type, in order to maximize the performance of MySQL advantage.

===============================================================

Here are some of the links and differences between InnoDB and MyISAM!

1. More than 4.0 mysqld support transactions, including non-max versions. 3.23 requires Max version mysqld to support transactions.

2. When you create a table, the default is MyISAM if you do not specify a type, and transactions are not supported.
You can look at the type of the table with the Show CREATE TABLE tablename command.

2.1 does not have any effect on the start/commit of tables that do not support transactions, submitted before the execution of a commit, testing:
Execute a MSYQL:
Use test;
drop table if exists TN;
Create TABLE TN (a varchar (a)) Type=myisam;
drop table if exists Ty;
Create table Ty (a varchar (a)) Type=innodb;

Begin
Insert into TN values (' a ');
Insert into Ty values (' a ');
SELECT * from TN;
select * from Ty;
Can see a single record.

Execute another MySQL:
Use test;
SELECT * from TN;
select * from Ty;
Only TN can see a record.
And then on the other side
Commit
To see the record.

3. You can perform the following command to switch non-transaction tables to transactions (data is not lost), and InnoDB tables are more secure than MyISAM tables:
ALTER TABLE TableName TYPE=INNODB;

3.1 InnoDB table cannot be used with repair table commands and MYISAMCHK-R table_name
But you can use check table, and Mysqlcheck [OPTIONS] database [tables]

==============================================================

The use of select for update in MySQL must be for InnoDB, and in a transaction to work.

The criteria for a select are different, with row-level or table-level locks.
Instructions for turning

Because the InnoDB preset is Row-level lock, MySQL executes row lock (only the selected data is locked), or MySQL executes the table lock (the entire data form is locked) only if the specified primary key is "clear".

As an example:

Suppose you have a form products with IDs and name two fields, and ID is the primary key.

Example 1: (explicitly specifying the primary key and having this information, row lock)

SELECT * from the products WHERE id= ' 3′for UPDATE;

Example 2: (explicitly specify the primary key, if no such information, no lock)

SELECT * from the products WHERE id= ' -1′for UPDATE;

Example 2: (No primary key, table lock)

SELECT * from the products WHERE name= ' Mouse ' for UPDATE;

Example 3: (primary key ambiguous, table lock)

SELECT * from the products WHERE id<> ' 3′for UPDATE;

Example 4: (primary key ambiguous, table lock)

SELECT * from the products WHERE id like ' 3′for UPDATE;

Note 1:
The for UPDATE applies only to InnoDB and must be in the transaction block (Begin/commit) to take effect.