In MySQL, import data load MyISAM differs from InnoDB

Source: Internet
Author: User
Tags table definition

The InnoDB data table structure is as follows:

The code is as follows Copy Code

Create table:create Table ' Tinnodb ' (
' id ' int (one) DEFAULT NULL,
' Content ' Mediumtext
) Engine=innodb DEFAULT Charset=utf8

The data in the table is 4194304 rows and is exported through the SELECT INTO outfile

The code is as follows Copy Code

Root@localhost:tiger>select * from Tinnodb to outfile '/tmp/tiger_tinnodb.txt ' fields terminated by ', ' optionally en Closed by ' ";
Query OK, 4194304 rows Affected (3.67 sec)

The MYISAM data structure is as follows:

  code is as follows copy code

Create Table:create TABLE ' Tmyisam ' (
' id ' int () default NULL,
' content ' Mediumtext
) Engine=myisam DEFAULT charse T=utf8
Root@localhost:tiger>show variables like ' innodb_flush_log_at_trx_commit ';
+--------------------- -----------+-------+
| variable_name                   | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in Set (0.00 sec)

The following starts the import test:


The results of importing to the InnoDB datasheet are as follows:

The code is as follows Copy Code

Root@localhost:tiger>load data infile '/tmp/tiger_tinnodb.txt ' into the table Tinnodb fields terminated by ', ' optionally E Nclosed by ' ";
Query OK, 4194304 rows Affected (58.37 sec)
records:4194304 deleted:0 skipped:0 warnings:0

The results of importing to the MyISAM datasheet are as follows:

The code is as follows Copy Code

Root@localhost:tiger>load data infile '/tmp/tiger_tinnodb.txt ' into the table Tinnodb fields terminated by ', ' optionally E Nclosed by ' ";
Query OK, 4194304 rows Affected (2.97 sec)
records:4194304 deleted:0 skipped:0 warnings:0

Through the initial time to see, in the bulk import this aspect MyISAM occupies the advantage.
Now adjust the innodb_flush_log_at_trx_commit and do the test again.

The code is as follows Copy Code

Root@localhost:tiger>set Global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows Affected (0.00 sec)
Root@localhost:tiger>show variables like ' innodb_flush_log_at_trx_commit ';
+--------------------------------+-------+
| variable_name | Value |
+--------------------------------+-------+
| Innodb_flush_log_at_trx_commit | 2 |
+--------------------------------+-------+
1 row in Set (0.00 sec)
Root@localhost:tiger>load data infile '/tmp/tiger_tinnodb.txt ' into the table tinnodb2 fields terminated by ', ' optionally Enclosed by ' ";
Query OK, 4194304 rows Affected (56.46 sec)
records:4194304 deleted:0 skipped:0 warnings:0

There is no big change here.

This time is not nearly as if you were to import the. sql files that were exported by mysqldump. The concrete can be tested by itself.
It is worth considering, under what scenario can you do local data backup through select INTO outfile and load data infile? After all, the load data infile is quite efficient.

Supplement: The six major differences between InnoDB and MyISAM

MyISAM

InnoDB

the difference in composition:

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).

A disk-based resource is a InnoDB tablespace data file and its log files, and the size of the InnoDB table is limited to the size of the operating system file, typically 2GB
transaction Processing Aspects :

Tables of the MyISAM type emphasize performance, which is performed more than the InnoDB type, but does not provide transactional support

InnoDB provides transaction support transactions, external key and other advanced database functions

SELECT Update,insert , Delete Operation
If performing a lot of Select,myisam is a better choice

1. If your data performs a large number of inserts or updates, for performance reasons, you should use the InnoDB table

2.DELETE from table , InnoDB does not re-establish the table, but deletes one row at a time.

3.LOAD table from MASTER operation 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, However, tables that use additional InnoDB attributes, such as foreign keys, do not apply

   auto_increment actions

  
  
   the internal processing of one auto_incremen column per table.

   MyISAM to INSERT and The update operation automatically updates this column . This makes the Auto_increment column faster (at least 10%). The value at the top of the sequence cannot be used again after it has been deleted. (When the Auto_increment column is defined as the last column of a multiple-column index, you can have a situation where the value deleted from the top of the sequence is reused). The

  auto_increment value can be reset using ALTER TABLE or MYISAMCH for

   for fields of type auto_increment. InnoDB must contain only the index of that 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 called the automatic growth counter, which is used to assign a new value to the column.

   The automatic growth counter is stored only in main memory, not on the disk

   on the arithmetic implementation of the calculator, refer to

   Auto_increment column in InnoDB How to work

  
the exact number of rows in the table
Select COUNT (*) from Table,myisam as long as you simply read out the number of saved rows, note that when the COUNT (*) statement contains a where condition, the operations of both tables are the same

The exact number of rows for the table is not saved in the InnoDB, that is, when the select count (*) from table is executed, InnoDB scans the entire table to calculate how many rows

Lock
Table lock

Provides row locks (locking on row level), providing no lock reads consistent with Oracle type (non-locking read in
Selects), 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% "

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.