Features of common storage engine for MySQL database

The storage engine for a database is the database's underlying software organization, which uses the data engine to create, query, update, and delete data. Different storage engines provide different storage mechanisms, indexing techniques, lock levels, and other features that use different storage engines and can also get specific functionality. Many different database management systems now support a number of different data engines. The core of MySQL is the plug-in storage engine, the most famous of which is MyISAM and InnoDB.

• View of the storage engine

• To view a database-configurable storage engine

  SHOW ENGINES;

• To view the storage engine that the database is using

  show table status from 库 where name=‘表1‘;    #查看表1正在使用的存储引擎

  use 库;    #进入数据库show create table 表1;

• Configuration of the storage engine

• Modify by command

  use 库;      #进入数据库alter table 表1 engine=MyISAM;       #修改存储引擎为MyISAMalter table 表2 engine=InnoDB;         #修改存储引擎为InnoDB

• Through configuration file modification (remember, only the newly created table is useful!) ）

  vim /etc/my.cnf......[mysqld]default--storage-engine=MyISAM      //添加你要设为默认的存储引擎

• Specify the storage engine directly when creating a table

  use 库;    #进入库create table 表1(id int)engine=InnoDB;        #修改存储引擎为InnoDBcreate table 表2(id int)engine=MyISAM;       #修改存储引擎为MyISAMshow create table 表；   #查看

Characteristics of the storage engine and its application scenarios

• MyISAM feature Introduction and application scenario
  The MyISAM is based on the ISAM storage engine and extends it. It is one of the most commonly used storage engines in the Web, data warehousing, and other application environments. MyISAM has a high insertion and query speed, but does not support transactions . MyISAM Main features are:

  1) Data files and index files can be placed in different directories.
  2) Table-level locking, where data locks the entire table when it is updated.
  3) The database is blocking each other during reading and writing.
  4) The cache index can be set through key_buffer_size.
  5) The data is written separately or the reading speed consumes less resources.
  6) Foreign KEY constraints are not supported and only full-text indexes are supported.
  7) When the table is built, three files are generated, and the filenames begin with the name of the table.
  ". frm file storage table Definition | The extension of the data file:. MYD (MYData) | The extension of the index file:. MYI (Myindex) "

  Applicable scenarios:

  1) Non-transactional applications
  2) Read-only class applications
  3) Space class applications

• InnoDB feature Introduction and application scenario
  InnoDB is the preferred engine for transactional databases, supports transaction-safe tables (ACID), supports row locking and foreign keys, and is the default storage engine for MySQL database 5.5 and later versions . InnoDB Main features are:

  1) InnoDB provides MySQL with a thing-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB locks the row-level and also provides a non-locking read similar to Oracle in the SELECT statement. These features increase multi-user deployment and performance. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query.
  2) InnoDB is designed to handle the maximum performance of large volumes of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine lock.
  3) The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB its tables and indexes in a logical table space, the tablespace can contain several files (or raw disk files). This differs from the MyISAM table, such as in the MyISAM table where each table is stored in a detached file. The InnoDB table can be any size, even if the file size is limited to 2GB on the operating system.
  4) InnoDB supports foreign key integrity constraints, when the data in the table is stored, each table's storage is stored in the primary key order, and if no primary key is specified when the table definition is displayed, InnoDB generates a 6-byte rowid for each row, which is used as the primary key.
  5) InnoDB is used in many large database sites that require high performance.
  6) InnoDB do not create a directory, when using InnoDB, MySQL will create a 10MB size automatic extension data file named Ibdata1 in MySQL Data directory, and two Ib_logfile0 and ib_ Logfile1 log file of 5MB size.

  Applicable scenarios:

  MySQL5.7 and later versions already support full-text indexing and spatial functions.
  For most OLTP applications (on-line Transaction processing online transaction processing (OLTP), also known as transaction-oriented processing, the basic feature is that the user data received by the foreground can be transferred immediately to the computing Center for processing, And in a short period of time to give the processing results, is a quick response to user operations one way.

• The difference between MyISAM and InnoDB

  MyISAM and InnoDB are the two most common storage engines used by many people when using MySQL, both of which have pros and cons, depending on the application. The basic difference is that MyISAM does not support advanced processing such as transaction processing, emphasizing performance, which executes faster than InnoDB, but does not provide transactional support, while InnoDB provides advanced database functionality such as transaction support, external keys, and so on.

In summary, if the data table is used primarily for inserting and querying Records , then MyISAM can provide high processing efficiency , if you want to provide commit, rollback, crash resiliency Transaction-Safe (acid-compatible) capability , and requires concurrency control ,InnoDB is a good choice.

Features of common storage engine for MySQL database