MySQL table type, that is, detailed parsing of MyISAM and innodb

Source: Internet
Author: User

The following articles mainly describe the MySQL table types, that is, detailed parsing of MyISAM and innodb. If you are interested in the MySQL table types, that is, the actual operations related to detailed parsing of MyISAM and innodb, you can click to view the following articles.

MySQL table types: MYISAM and innodb

1. If your data executes a large number of INSERT or UPDATE operations, InnoDB tables should be used for performance considerations.

If you execute a large number of SELECT statements, MyISAM is a better choice.

2. I need to use transaction processing, but the original data table uses myisam and needs to be changed to bdb or innodb. In this way, after the myisam-based program changes the type to innodb, the original program does not need to be modified.

3. myisam is a non-Transaction Security type, while innodb and bdb are transaction security types.

Note:

Test Table creation (Environment: MySQL 5.0.22, Windows 2000 operating system)

 
 
  1. create table AAA(  
  2. a1 varchar(64) not null,  
  3. b2 varchar(255),  
  4. c3 int,  
  5. primary key (a1)  
  6. )ENGINE=InnoDB; 

Test stored procedure-Execute 10000 Insert Inserts

 
 
  1. create procedure sp_AAA ()  
  2. begin  
  3. declare i integer;  
  4. declare a1 char(64);  
  5. declare b2 char(255);  
  6. set i=1;  
  7. while i < 10000 do  
  8. set a1= concat(‘aaa’ ,i);  
  9. set b2= concat(‘bbbbbbbbbbbbbbbbbbbbbbb’ ,i) ;  
  10. insert into aaa values(a1,b2,i) ;  
  11. set ii=i+1 ;  
  12. end while;  
  13. end;  

Calling call sp_AAA found that it took 295 seconds to complete the Stored Procedure

I know that starting transaction; and commit; are used to package the while loop of sp_AAA In the stored procedure, so that MySQL can perform a commit after 10000 inserts are executed, however, in actual applications, one or more entries are usually required to be inserted and commit once after an action is completed. As a result, the innodb performance of the MySQL table type seems to be poor, but I do not know whether the test comprehension is correct, for other reasons, please give us some advice.

(In addition, I perform the same test under sybase11.9 and oracle9i, and install it in my laptop environment. It is also an insert commit operation, with a total of 10000 inserts. Sybase takes 6 seconds, oracle takes 2 seconds)

Is there a big performance gap between InnoDB, a MySQL transaction type table ???

The test process of Sybase and Oracle is attached:

Sybase test Stored Procedure

 
 
  1. create procedure sp_AAA   
  2. as   
  3. begin   
  4. declare @i integer   
  5. declare @a1 char(64)   
  6. declare @b2 char(255)   
  7. select @i=1 
  8. while @i < 10000 
  9. begin  
  10. select @a1= ‘aaa’ + convert(varchar(64),@i)  
  11. select @b2= ‘bbbbbbbbbbbbbbbbbbbbbbb’ + convert(varchar(64),@i)  
  12. insert into AAA values(@a1,@b2,@i)  
  13. select @i=@i+1  
  14. end  
  15. end   

Oracle test Stored Procedure

 
 
  1. CREATE OR REPLACE PROCEDURE sp_AAA(a int)  
  2. AS  
  3. i int;  
  4. a1 varchar2(64);  
  5. b2 varchar2(255);  
  6. BEGIN  
  7. i :=1;  
  8. while i < 10000 loop  
  9. a1 := ‘aaa’ || to_char(i);  
  10. b2 := ‘bbbbbbbbbbbbbbbbbbbbbbb’ || to_char(i);  
  11. insert into aaa values(a1,b2,i) ;  
  12. commit ;  
  13. i :=i+1 ;  
  14. end loop;  
  15. END; 

InnoDB and MyISAM are the two most commonly used table types in MySQL, each of which has its 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.

MyIASM is a new version of the IASM table and has the following extensions:

Binary hierarchy portability.

NULL column index.

There are fewer fragments for Long-varying rows than the ISAM table.

Supports large files.

Better index compression.

Better key? statistical distribution.

Better and faster auto_increment processing.

1. The biggest advantage of MySQL is the simple SELECT, INSERT, and UPDATE operations under the MyISAM engine.

2. MyISAM data files can be copied in different operating systems, which is very important and convenient for deployment.

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 the MySQL table type cannot determine the scope to be scanned when an SQL statement is executed, the InnoDB table also locks the entire table, for example, update table set num = 1 where name like "% aaa %"

Based on my understanding of the storage engine, I feel that InnoDB supports foreign keys. When the data volume can be described as "huge", on the basis of a good INDEX, innoDB Query speed should be faster than MyISAM.

Before Falcon has a stable version, I think MyISAM is an available option.

Any type of table is not omnipotent. You only need to select a proper table type for the business type to maximize the performance advantage of the MySQL table type.

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.