MySQL modified table Storage Engine Method Summary

MySQL as the most commonly used database, often encountered a variety of problems. What we're going to say today is the changes to the table storage engine. There are three ways, the list is as follows.

1. True connection modification. It is slower when there is more data, and can affect read performance when modified. My_table is the table of operations, InnoDB is the new storage engine.

The code is as follows	Copy Code
ALTER TABLE my_table Engine=innodb

2. Export, import. This is easier to operate, directly to the lead out of the SQL file to change, and then guide back. With Mysqldump, Feng elder brother is commonly used is navicate so easier to use. Friendship Alert is a big risk.

3. Create, insert. This is faster than the first, safety is higher than the second, recommended. 2-Step operation

Create a table, first create a table that is the same as the table you want to manipulate, 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. For security and speed, it is best to add transactions and limit the scope of the ID (primary key).

The code is as follows	Copy Code
INSERT into my_tmp_table SELECT * from my_table;

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

To view the storage engine for a table

The code is as follows

Copy Code

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

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 Two:

Change the storage engine in method one 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

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

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

Tt7_tmp as 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 time to change 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 speed is complete.

and return the data from 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 an intermediate table

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

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 ';

SQL statement used to generate the check table:

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 value, you'll have to change it back)

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 are in MySQL?
1 MyISAM: This engine is the earliest provided by MySQL. This engine can be divided into static MyISAM, dynamic MyISAM and compression MyISAM three kinds:
Static MyISAM: If the length of each data column in the datasheet is fixed beforehand, the server will automatically select this type of table. Because each record in the datasheet occupies the same amount of space, this table access and update efficiency is very high. Recovery is also easier to do when data is compromised.
Dynamic MyISAM: When a varchar, xxxtext, or Xxxblob field appears in a datasheet, the server automatically chooses this type of table. Compared to static MyISAM, this kind of table storage space is relatively small, but because each record is different in length, so the data in the data table may be stored in memory, which will result in lower execution efficiency. Also, there may be a lot of fragmentation in memory. Therefore, this type of table should often be defragmented with the Optimize table command or the Optimization tool.
Compression MyISAM: The two types of tables mentioned above can be compressed using the Myisamchk tool. This type of table further reduces the amount of storage consumed, but this table compression cannot be modified again. In addition, because it is compressed data, so the table in the reading of the first to decompress.
However, regardless of the MyISAM table, it does not currently support the functionality of transactions, row-level locks, and foreign key constraints.
2 MyISAM Merge Engine: This type is a variant of the MyISAM type. A merged table is a combination of several identical MyISAM tables into a single virtual table. Often applies to logs and data warehouses.
The 3 Innodb:innodb table type can be viewed as a further update product for MyISAM, which provides functionality for transactions, row-level locking mechanisms, and foreign key constraints.
4 Memory (Heap): This type of data table exists only in memory. It uses a hash index, so the data is accessed very quickly. Because it exists in memory, this type is often applied to temporary tables.
5 Archive: This type supports only select and INSERT statements and does not support indexing. Often applied to logging and aggregation analysis.