Summary of methods for modifying the table storage engine in MySQL

Myisam and innodb are the most commonly used table storage engines in mysql. We almost don't need or use them any more advanced. For the features of myisam <=> innodb, I 'd like to know a little about it, next I will introduce how to convert innodb into each other.

As the most common database, MySQL often encounters various problems. Today we are talking about modifying the table storage engine. There are three methods.

1. modify it. When there is a large amount of data, it is slow, and the read performance will be affected during modification. My_table is an operational table and innoDB is a new storage engine.

The Code is as follows:	Copy code
Alter table my_table ENGINE = InnoDB

2. Export and Import. This is easy to operate. The exported SQL file is changed and then imported back. With mysqldump, fengge often uses navicate for ease of use. Reminder is highly risky.

3. Create and insert. This is faster than the first type, and the security is higher than the second type. It is recommended. Two steps

To create a table, first create a table that is the same as the table to be operated, and then change the storage engine as the target engine.

The Code is as follows:	Copy code
Create table my_tmp_table LIKE my_table; Alter table my_tmp_table ENGINE = InnoDB;

B. insert. To ensure security and speed, it is best to add transactions and limit the range of IDS (primary keys.

The Code is as follows:	Copy code
Insert into my_tmp_table SELECT * FROM my_table;

Modify the table storage engine myisam <=> innodb

 

View the storage engine of a table

The Code is as follows:

Copy code

Mysql> show create table tt7; + ------- + Response + | Table | Create Table | + ------- + Response + | Tt7 | create table 'tt7 '( 'Id' int (10) default NULL, 'Name' char (10) default NULL ) ENGINE = MyISAM default charset = latin1 | + ------- + Response + 1 row in set (0.00 sec) View the table data volume Mysql> select count (1) from tt7; + ---------- + | Count (1) | + ---------- + | 1, 16777216 | + ---------- + 1 row in set (0.00 sec)

Method 1:

Directly change the storage engine

The Code is as follows:	Copy code
Mysql> alter table tt7 engine = innodb; Query OK, 16777216 rows affected (2 min 39.80 sec) Records: 16777216 Duplicates: 0 Warnings: 0

Method 2:

 

Change the storage engine in method 1 back to myisam

The Code is as follows:	Copy code
Mysql> alter table tt7 engine = myisam; Query OK, 16777216 rows affected (27.09 sec) Records: 16777216 Duplicates: 0 Warnings: 0

The myisam table is much faster than the innodb table.

 

Create tables with the same table structure as tt7

The Code is as follows:	Copy code
Mysql> create table tt7_tmp like tt7; Query OK, 0 rows affected (0.02 sec)

 

Tt7_tmp serves as the intermediate result set

The Code is as follows:	Copy code
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

The Code is as follows:	Copy code
Mysql> truncate table tt7; Query OK, 16777725 rows affected (0.18 sec)

 

This changes the storage engine of the original table.

The Code is as follows:	Copy code
Mysql> alter table tt7 engine = innodb; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0

The process was completed quickly.

 

Then, import the data in the intermediate result set back to the original table.

The Code is as follows:	Copy code
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 intermediate table

The Code is as follows:	Copy code
Mysql> drop table tt7_tmp;

Test results:

Method 2 is a little faster, but if the data volume is large, method 2 should adopt the batch operation method of converting to zero. Otherwise, the insert operation will take time and generate a large number of undo logs.

If it is a small table (within MB, according to the hardware environment of your system), you can use the method one.
For a large table, use method 2 + batch.

If you want to change the storage engine of tables in batches

SQL statement used to generate changes:

The Code is as follows:	Copy code
Select concat ('alter table', table_name, 'Engine = InnoDB; ') FROM information_schema.tables WHERE table_schema = 'db _ name' and engine = 'myisam ';

The SQL statement used to generate the checklist:

The Code is as follows:	Copy code
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 values and change them later)

The Code is as follows:	Copy code
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;

Add

What storage engines does MySql have?
1 MyISAM: This engine was first provided by mysql. This engine can be divided into three types: static MyISAM, dynamic MyISAM, and compressed MyISAM:
Static MyISAM: if the length of each data column in the data table is pre-fixed, the server automatically selects this table type. Because each record in a data table occupies the same space, the efficiency of table access and update is very high. When data is damaged, recovery is easier.
Dynamic MyISAM: If the varchar, xxxtext, or xxxBLOB fields appear in the data table, the server automatically selects this table type. Compared with static MyISAM, this table has a small storage space, but because the length of each record is different, after data is modified multiple times, the data in the data table may be stored discretely in the memory, this leads to a decrease in execution efficiency. At the same time, many fragments may occur in the memory. Therefore, this type of table often uses the optimize table command or optimization tool for fragment.
Compression MyISAM: the two types of tables mentioned above can be compressed using myisamchk. This type of table further reduces the storage used, but the table cannot be modified after compression. In addition, because the data is compressed, such tables must be decompressed first.
However, no matter what MyISAM table is, it currently does not support the functions of transactions, row-level locks, and foreign key constraints.
2 MyISAM Merge engine: This type is a variant of the MyISAM type. Merging tables combines several identical MyISAM tables into a virtual table. It is often used in logs and data warehouses.
3. InnoDB: the InnoDB table type can be viewed as a product for further updating MyISAM. It provides the functions of transactions, row-level locks, and foreign key constraints.
4 memory (heap): This type of data table only exists in memory. It uses hash indexes, so the data access speed is very fast. Because it exists in the memory, this type is often used in temporary tables.
5 archive: This type only supports select and insert statements, and does not support indexes. It is often used in logging and aggregate analysis.