This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!

This article is an experimental version of mysql 5.7.21

The Infrastructure section learns that the executor executes this execution plan by calling the storage engine’s API to manipulate the data.

Mysql provides a series of apis for storage engines. All storage engines have to meet the API requirements, so you can implement a plug-in storage engine that can be selected according to your needs (just like holding a barbell, you can choose different size barbell chips, yes).

The storage engine is for tables, not libraries, and different tables in the same library can use different storage engines.

Common storage engines are MyISAM, InnoDB, and Memory

View the storage engines supported by the current data:

  1. You can select the storage engine when creating a table
CREATE TABLE  'user' (
 'id' bigint(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ''
) ENGINE = MyISAM
Copy the code

ENGINE = MyISAM indicates that the storage ENGINE for this table is MyISAM.

  1. View information about tables, such as the user table in the mysql library
  • Run the show table status command to view table information (no limit version).

You need to switch to the corresponding database before running this command

mysql> use mysql;
Database changed
mysql> show table status like 'user' \G ;
*************************** 1. row ***************************
          Name: user
        Engine: MyISAM
      Version: 10
    Row_format: Dynamic
          Rows: 3
Avg_row_length: 128
  Data_length: 384
Max_data_length: 281474976710655
  Index_length: 4096
    Data_free: 0
Auto_increment: NULL
  Create_time: 2018-06-11 09:51:16
  Update_time: 2018-06-11 09:53:08
    Check_time: NULL
    Collation: utf8_bin
      Checksum: NULL
Create_options: 
      Comment: Users and global privileges
1 row in set (0.00 sec)
Copy the code
  • You can also view table information using information_schema (supported after mysql5.0)

TABLE_SCHEMA is used to query tables in all libraries in the instance, but you can specify TABLE_SCHEMA to query tables in a specific library

mysql> select * from information_schema.tables where table_name = 'user' and TABLE_SCHEMA='mysql' \G;
Copy the code

This section describes the meanings of small output fields.

Name: Name of the table.

Engine: storage Engine.

Version: Indicates the Version. The default value is 10.

Row_format: row format.

Rows: Number of Rows in the table, which corresponds to MyISAM and some other storage engines. This value is accurate; For InnoDB, the value is estimated.

Avg_row_length: The average number of bytes per row.

Data_length: The size (in bytes) of table data.

Max_data_length: The maximum capacity of table data (depending on the storage engine).

Index_length: The size of the index in bytes.

Data_free: For MyISAM tables, the space allocated but not used.

Auto_increment: Next Auto_increment value.

Create_time: time when the table is created.

Update_time: indicates the time when the table data was last modified.

Check_time: Indicates the time when the table was last checked using the check table command or the myisamchk tool.

Collation: The default character set and character column Collation for the table.

Checksum: If enabled, save the real-time Checksum of the entire table.

Create_options: Creating a table is the other option specified.

Comment: Contains additional information

1. InnoDB

InnoDB is the default transactional engine starting with mysql5.5.x and the most widely used storage engine. Designed to handle a large number of short-term transactions.

InnoDB keeps all tables in a single datapile (which can be multiple files or separate tablespace files), and the size of the tables is limited only by the size of the operating system file. Table structure definitions are stored in.frm files, and data and indexes are stored centrally in.idb files. Since the table data and index are in the same file, InnoDB’s index is a clustered index.

InnoDB uses MVCC to support high concurrency, and implements four standard isolation levels (READ uncommitted, READ committed, REPEATABLE READ, serialized). Its default level is REPEATABLE READ, and next-key locking prevents magic reads. The gap lock locks not only the rows involved in the query, but also the gap rows in the index to prevent the insertion of phantom rows.

InnoDB tables are built based on clustered indexes, which provide high performance for primary key queries. However, InnoDB’s non-primary key index must contain a primary key column, so if the primary key column is large, the non-primary key index will also be large. If a table has many indexes, the primary key should be as small as possible. More on indexing later.

InnoDB’s internal optimizations include disk prefetch (predictable reading when reading data from disk), adaptive hashing (automatically creating hash indexes in memory to speed up reads) and insert buffers that speed up inserts.

2. MyISAM

In mysql5.1 and earlier, MyISAM is the default storage engine. Provides a number of features, including full-text indexing, compression, spatial functions, etc., but does not support transaction and row-level locking, and there is a serious problem with the inability to safely recover after a crash.

MyISAM’s data tables are stored in three files on disk. Table structure definitions are stored in.frm, table data is stored in.myd, and table indexes are stored in.myi. Table data and table indexes are in different files, so MyISAM indexes are non-clustered indexes. And MyISAM can store the total number of rows of table data.

MyISAM table supports data compression. After the table is created and the data is imported, the MyISAM table can be compressed without modification. Compression command: Myisampack. Compression of tables can greatly reduce disk space, thus reducing disk I/O and improving query performance. And the data in the compressed table is single-row compression, so single-row reading does not need to decompress the entire table.

3. Memory

The data of the storage engine is stored in Memory, so if the server restarts, the data will be lost, but the table structure will still exist.

Memory defaults to hash indexes, so queries are fast. Memory tables are table level locked, so concurrent write performance is low. Columns of type BLOB or TEXT are not supported, and the length of each row is fixed, so even if a VARCHar column is specified, the actual storage will be converted to char, resulting in a waste of memory.

If you need to use a temporary table to hold intermediate results during a mysql query, the internal temporary table is a Memory table. If the intermediate results are too large for the Memory table or contain BLOB or TEXT fields, the temporary table will be converted to a MyISAM table.

How do you choose a storage engine?

  • Transactions: Currently only Innodb supports transactions perfectly.
  • Backup: Only Innodb has a free online hot backup solution, mysqldump is not an online hot backup solution, it requires locking the data.
  • Crash recovery: MyISAM has a much higher probability of data corruption due to system crashes than Innodb, and the recovery rate is not as fast as Innodb.
  • Specific features: If you need to cluster indexes, you need to choose InnoDB storage engine, if you need to use geospatial search, you need to choose MyisAM.

There are many storage engines for mysql, among which InnoDB is the most widely used and default storage engine. If there is no special requirement, InnoDB can be used by default.

Welcome to follow the public account