Chapter III MySQL Storage engine introduction

1 The concept of the MySQL storage engine

• The data in MySQL is stored in files (or memory) in various different technologies, using different storage mechanisms, indexing techniques, locking levels, and ultimately providing a wide range of different functions that can be used to achieve additional speed or functionality by selecting different technologies to improve overall functionality.
  
• Both SQL Server and Oracle use a storage engine, and MySQL has a variety of storage engines.
  
• The so-called storage engine, is actually the type of table
  

2 querying the supported storage engines in MySQL

 
   
  

  
    
   
show engines;
  
    
   
show engines \g 
  
    
   
show variables like ‘have%‘;
  
    
   
show variables like ‘table_type‘; //查看表的存储引擎
 
   
  

• Default storage Engine: InnoDB

3 common three kinds of storage engine 3.1 InnoDB

• Only one file type: frm
• Span style= "FONT-SIZE:12PT; Font-weight:normal; line-height:1.5; " > support auto-Grow column (auto_increment)
• Pros: Provides good transaction management, crash-repair capabilities, and concurrency control
• innodb self-recovery time is short, MyISAM also have self-recovery, time is relatively long.
• disadvantage: its reading and writing efficiency is slightly worse, the data space occupied is relatively large

3.2 MyISAMFile Type

• FRM: The structure of the storage table
• MYD: Storing data
• Myi: Storage Index

Storage format

• Static: field type type is similar to char, access speed is relatively fast
• Dynamic: field has a data type similar to varchar
• Compression: For read-only tables, you can compress them, which can improve performance.

Advantages: Overcoming small space, fast processing speed disadvantage: Transaction integrity and concurrency are not supported3.3 MEMORY

• The memory storage engine is a special kind of storage engine in MySQL. It uses the content stored in memory to create the table, and all data is stored in memory
  
• Advantages: The film is very fast
  
• Cons: Their data is easy to lose, the life cycle is short
  

4 How to select a storage engine

• InnoDB Storage Engine: For transactional applications, with many features, including acid transaction support, foreign key support, and support for crash-repair capabilities and concurrency control. For databases that require high transaction integrity requirements, concurrency control, frequent updates, delete operations
• MyISAM Storage Engine: Primarily for non-transactional tables, it improves tuning storage and retrieval, as well as full-text search capabilities. Applies to tables mainly with the insertion of new records and read out the record, application integrity, concurrency requirements are very low
• Memory Storage Engine: Provides a "RAM" table, all data is inside, processing speed is fast, but the security is not high. Ideal for fast read and write speeds, low security requirements for data, and relatively small database tables

5 How to specify the storage engine for a table
Donate US
The Mentor Studio has been working to help programmers learn to program more quickly and easily, and if you agree with our results and feel helpful, you are welcome to donate ^_^ to us.     

Chapter III MySQL Storage engine introduction