Comparison between MySQL MyISAM engine and InnoDB Engine

Source: Internet
Author: User

The following articles mainly introduce the comparison of the actual performance of MySQL MyISAM engine and InnoDB engine, first, we use the table structure of the MySQL database to introduce the actual performance of the MySQL MyISAM engine and the InnoDB engine.

 
 
  1. CREATE TABLE `myisam` (  
  2. `id` int(11) NOT NULL auto_increment,  
  3. `name` varchar(100) default NULL,  
  4. `content` text,  
  5. PRIMARY KEY (`id`)  
  6. ) ENGINE=MyISAM DEFAULT CHARSET=gbk;  
  7. CREATE TABLE `innodb` (  
  8. `id` int(11) NOT NULL auto_increment,  
  9. `name` varchar(100) default NULL,  
  10. `content` text,  
  11. PRIMARY KEY (`id`)  
  12. ) ENGINE=InnoDB DEFAULT CHARSET=gbk;  

Data content:

 
 
  1. $name = “heiyeluren”; 

$ Content = "MySQL supports several storage engines as processors for different table types. MySQL storage engine includes the engine for processing transaction security tables and the engine for processing non-transaction security tables: · MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MySQL MyISAM is supported in all MySQL configurations. It is the default storage engine, unless you configure MySQL to use another engine by default. ·

The MEMORY storage engine provides "in-MEMORY" tables. The MERGE storage engine allows a set to process the same MyISAM table as a separate table. Like MySQL MyISAM, MEMORY and MERGE storage engines process non-transaction tables. Both engines are included in MySQL by default.

Note: The MEMORY storage engine is officially identified as the HEAP engine. · InnoDB and BDB storage engines provide transaction security tables. BDB is included in the MySQL-Max binary distribution version released for the operating system that supports it. InnoDB is also included in all MySQL 5.1 binary distributions by default. You can configure MySQL to allow or disable any engine as you like. · The EXAMPLE storage engine is a "stub" engine, which does not do anything. You can use this engine to create tables, but no data is stored or retrieved from them. The purpose of this engine is to provide a service. In the MySQL source code example, it demonstrates how to start writing a new storage engine. Similarly, it is mainly interested in developers .";

[Insert data-1] (innodb_flush_log_at_trx_commit = 1)
MyISAM 1 W: 3/s

NnoDB 1 W: 219/s

MyISAM 10 W: 29/s

NnoDB, 10 W: 2092/s

MySQL MyISAM 100 W: 287/s

InnoDB 100 W:

Dare not test

[Insert data-2] (innodb_flush_log_at_trx_commit = 0)
 

 
 
  1. MyISAM 1W:3/s  
  2. InnoDB 1W:3/s  
  3. MyISAM 10W:30/s  
  4. InnoDB 10W:29/s  
  5. MyISAM 100W:273/s  
  6. InnoDB 100W:423/s  

[Insert data 3] (innodb_buffer_pool_size = 1024 M)
 

 
 
  1. InnoDB 1W:3/s  
  2. InnoDB 10W:33/s  
  3. InnoDB 100W:607/s 

[Insert data 4] (innodb_buffer_pool_size = 256 M, innodb_flush_log_at_trx_commit = 1, set autocommit = 0)

 
 
  1. InnoDB 1W:3/s  
  2. InnoDB 10W:26/s  
  3. InnoDB 100W:379/s 

[MySQL configuration file] (default configuration)

 
 
  1. # MySQL Server Instance Configuration File  
  2. [client]  
  3. port=3306 
  4. [mysql]  
  5. default-character-set=gbk 
  6. [mysqld]  
  7. port=3306 
  8. basedir=”C:/mysql50/”  
  9. datadir=”C:/mysql50/Data/”  
  10. default-character-set=gbk 
  11. default-storage-engine=INNODB 
  12. sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”  
  13. max_connections=100 
  14.  
  15. query_cache_size=0 
  16. table_cache=256 
  17. tmp_table_size=50M 
  18. thread_cache_size=8 
  19. myisam_max_sort_file_size=100G 
  20. myisam_max_extra_sort_file_size=100G 
  21. myisam_sort_buffer_size=100M 
  22. key_buffer_size=82M 
  23. read_buffer_size=64K 
  24. read_rnd_buffer_size=256K 
  25. sort_buffer_size=256K 
  26. innodb_additional_mem_pool_size=4M 
  27. innodb_flush_log_at_trx_commit=1 
  28. innodb_log_buffer_size=2M 
  29. innodb_buffer_pool_size=159M 
  30. innodb_log_file_size=80M 
  31. innodb_thread_concurr  

The above content is an introduction to MySQL MyISAM. I hope you will get some benefits.

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.