Introduction to the

Database storage engine: it is the bottom software organization of database. Database management system (DBMS) uses data engine to create, query, update and delete data. Different storage engines provide different storage mechanisms, indexing techniques, locking levels, and other functions. Different storage engines can be used to obtain specific functions. Many different database management systems now support many different data engines. At its core, MySQL is a plug-in storage engine. Test interview guide

Check the engine

You can use SHOW ENGINES; View all storage engines supported by the current database

The Engine column, representing the storage Engine type; In the Support column, the storage engine is available. YES indicates that the storage engine is available, NO indicates that the storage engine is unavailable, and DEFAULT indicates the DEFAULT storage engine

Myql provides many different storage engines, and you can use different storage engines in a database for different requirements.

SHOW VARIABLES LIKE ‘%storage_engine%’; You can view the default storage engine for the current database

The engine is introduced

  • InnoDB storage engine
    • InnoDB is the preferred engine for transactional databases. It supports ACID tables. Other storage engines are non-transactional tables and support row locking and foreign keys.
    • InnoDB provides MySQL with a transaction safe (ACID compatible) storage engine with commit, rollback, and crash recovery capabilities.
    • In an InnoDB table, the autogrow column must be an index, and if it is a composite index, it must be the first column of the composite index.
    • Designed to handle large database systems, InnoDB’s tables create buffer pools in memory to buffer data and indexes.
    • The storage engine for MySQL foreign keys is only InnoDB
    • Applicable scenarios:
      • Frequently updated tables, multiple concurrent tables
      • Large amount of data
      • Support transactions
      • Disaster recovery
      • Foreign key constraints
  • MyISAM storage engine
    • MyISAM is based on and extends the ISAM storage engine. It is one of the most commonly used storage engines in Web, data warehousing, and other application environments. MyISAM has high insert and query speed, but does not support transactions and does not support foreign keys. Test interview guide
    • MYD file is the data file of MyISAM; MYI file is the index file of MyISAM; FRM files are the table structures that store MyISAM
    • MyISAM supports three different storage formats for tables: static (fixed length) tables, dynamic tables, and compressed tables
      • Static table: The fields in the table are non-variable length fields, so that each record is of fixed length. The advantages of storage are very fast, easy to cache, and easy to recover when a fault occurs. The disadvantage is that it usually takes up more space than dynamic tables
      • Dynamic tables: Records are not fixed length, which has the advantage of taking up relatively little space; Cons: Frequent data updates and delets can cause fragmentation, requiring periodic execution of OPTIMIZE TABLE or Myisamchk -r to improve performance
      • Compressed tables: Because each record is compressed individually, there is very little access overhead
    • Applicable scenario
      • Transactions and foreign keys are not supported
      • Queries are fast, with a strong emphasis on reads, and do not consume large amounts of memory and storage resources
      • Locking the whole table
  • MEMORY engine
    • Memory storage engines use the contents of Memory to create tables, so they are also called HEAP Memory engines. Each memory table actually corresponds to only one disk file in the format. FRM. Memory-type tables are very fast to access because their data is stored in memory and HASH indexes are used by default, but once the service is shut down, the data in the table will be lost.
    • The MEMORY storage engine can choose to use BTREE or HASH indexes for tables
      • Advantages of Hash indexes: The Hash index structure is special, and the search efficiency is very high. The index can be located at one time, and the query efficiency is much higher than that of b-tree indexes. However, the hash algorithm is based on equivalence calculation, so fuzzy queries and invalid hash indexes are not supported
    • Applicable scenarios:
      • Memory-type storage engines are mainly used for low and infrequent content changes, such as code tables
      • The target data is small and accessed very frequently
      • The data is temporary and must be immediately available

Be careful when updating tables in memory because the data is not actually written to disk

  • MERGE \ MRG-MYISAM storage engine
    • Merge a storage engine is a group of MyISAM tables. The Merge tables must have the same structure. The Merge tables do not have data
    • Mrg-myisam isa horizontal storage engine, which aggregates the tables of multiple MYISAM, but he has no internal data, the real data is still MYISAM engine.
    • Usage Scenarios:
      • The level of table
  • BLACKHOLE engine
    • Any data written to the engine is discarded, not actually stored, and the select result is always empty
    • Usage scenarios
      • Copy data to the backup database
      • Verify the dump file command
      • Additional load required to detect binlog functionality
      • Act as a log server

Storage Engine Comparison

  • MyISAM engine does not support advanced processing such as transactions. Innodb supports advanced functions such as transaction support and foreign keys
    • Innodb engine is row lock, but not absolute, Innodb will still lock tables when the range is uncertain
  • MyISAM engine is all about performance, read performance is very good, faster than Innodb.
    • MySQL database default transaction enabled, Innodb engine table, to commit a large amount of data, can first turn off the automatic commit transactionset autocommit=0;Enable automatic transaction commit after data is executedset autocommit=1;To speed things up, otherwise big data submissions are very slow
  • For auto_INCREMENT columns, Innodb must contain an index for that column only, whereas MyISAM tables can be indexed with other columns.
  • MyISAM supports fulltext, compression engine, Innodb does not
  • MyISAM engine table index and data are separated in two different format files, and the index is compressed; The index and data of Innodb tables are bundled together without compression, so Innodb engine tables take up more storage space for the same amount of data.
  • Innodb table data backup, need to be everywhere SQL backup,load table from masterThe operation does not work with Innodb. To solve this problem, change the table engine Innodb to MyISAM, import the data, and then change Innodb to MyISAM. Note, however, that foreign keys are only supported by Innodb, not MyISAM.