Selection and configuration of "super simple" MySQL storage engine

"Ultra-simple" MySQL storage engine selection and Configuration Storage engine Introduction

Data in MySQL is stored in files (or memory) in a variety of different technologies. Each technology uses different storage mechanisms, indexing techniques, locking levels and ultimately providing a wide range of different functions and capabilities. By selecting different technologies, you can gain additional speed or functionality to improve the overall functionality of your application. These different technologies and associated functions are called storage engines in MySQL.

Here are two of the more common types of storage engines in MySQL: MyISAM and InnoDB

MyISAM Storage Engine

The MyISAM storage engine is the default storage engine prior to the MySQL relational database system version 5.5, and its predecessor is that ISAM,ISAM performs read operations quickly and does not consume large amounts of memory and storage resources. But there are also his shortcomings: ①, does not support transaction processing, ②, not fault tolerance, that is, if the hard disk crashes, then the data files can not be repaired, unless the regular backup of all real-time data, through its replication features, MySQL can support such a backup application.

Features of the MyISAM storage engine

1. Transactions are not supported and systems that require transaction support cannot use MyISAM as the storage engine
2. Table-level locking, where data locks the entire table when it is updated
3. The database blocks each other during read and write, that is, it cannot be written while reading and cannot be read while writing
4. Cache indexing can be set through key_buffer_size, improving access performance and reducing the pressure on disk io (read and write)
5. MyISAM storage Engine does not support foreign key constraints, only full-text indexing is supported
6. Each myisam is stored as three files on disk:

. frm file storage table definition

. MYD (MYData) data file

. MYI (myindex) index file

Based on the characteristics of MyISAM, so the MyISAM is mainly applicable to: some non-high concurrent read and write do not need to do at the same time, such as: E-Mall ...

InnoDB Storage Engine

The InnoDB is designed to handle the maximum performance of large volumes of data. Its CPU efficiency could be unmatched by any other disk-based relational database engine.

Features of the InnoDB storage engine

1. Supports transactions, supports four transaction isolation levels
2. Row-level locking, but full table scan will still be table-level locked
3. Read-write blocking is related to transaction isolation level
4. Very efficient caching features, caching of indexes, and caching of data
5. Table and primary key are stored in clusters, that is, there is a foreign key constraint
6. Supports partitioning, table space, similar to Oracle database

Based on the characteristics of InnoDB, InnoDB is mainly suitable for: forum, Weibo, bank and other high-concurrency places

The basis for an enterprise to select a storage engine

1. fields and data types supported by the storage engine
2. Lock type
3. Support for indexes
4. Support for transaction processing

View of the storage engine for databases and tables

1. View the storage engine currently used by the database

Mysql> Use school;     Enter School database changedmysql> show engines;                                                                  Look at the storage engine that school uses by default, because I am using the MySQL5.7 version, so the default is whether the InnoDB engine type can be used Whether the support transaction is represented as the current default engine +--------------------+---------+--------------------------------------------- -------------------+--------------+------+------------+| Engine | Support | Comment | Transactions | XA | savepoints |+--------------------+---------+----------------------------------------------------------------+--- -----------+------+------------+| InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys | YES | YES | YES | | Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables     | NO | NO | NO | | Blackhole | YES | /dev/null Storage Engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM Storage Engine | NO | NO | NO | | CSV | YES | CSV Storage Engine | NO | NO | NO | | ARCHIVE | YES | Archive Storage Engine | NO | NO | NO | | Performance_schema | YES | Performance Schema | NO | NO | NO | | Federated | NO | Federated MySQL Storage Engine | NULL | NULL | NULL |+--------------------+---------+----------------------------------------------------------------+--------- -----+------+------------+9 rows in Set (0.01 sec)

2. View the storage engine that the table is using

①, direct use of Show table status, as shown below

mysql> mysql> show table status from school where name=‘info‘;+------+--------+---------+------------+------+| Name | Engine | Version | Row_format | Rows |+------+--------+---------+------------+------+| info | InnoDB |      10 | Dynamic    |    1 |                 +------+--------+---------+------------+------+1 row in set (0.00 sec)//省略部分内容

Here you can see that the storage engine used by info is InnoDB

②, enter the database where the table resides, and then use the show Create command, as shown below

mysql> use school;Database changedmysql> show create table info;+-------+--------------------------------------+| Table | Create Table                         |+-------+--------------------------------------+| info  | CREATE TABLE "info" (  "name" char(10) DEFAULT NULL,  "score" decimal(5,2) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+--------------------------------------+1 row in set (0.00 sec)

The storage engine of the info table can also be seen directly here InnoDB;

Modifications to the database storage engine

There are also four ways to modify

①, use the ALTER TABLE command to modify, ( here only for existing tables or databases ) as shown below

Mysql> Use school;        Enter School database changedmysql> show create TABLE info; View the currently used storage Engine +-------+----------------------------------------------------------------------------------------- ---------------------------------------+| Table |                                                                                                                   Create Table |+-------+----------------------------------------------------------------------------------------------------- ---------------------------+| info | CREATE TABLE "Info" ("name" char (TEN) default NULL, "Score" decimal (5,2) default null) ENGINE=INNODB default Charset=utf      8 | Here you can see the InnoDB type of +-------+--------------------------------------------------------------------------------that is used by default      ------------------------------------------------+1 Row in Set (0.00 sec) mysql> ALTER TABLE info Engine=myisam; Modify using the ALTER TABLE command query OK, 0 rows affected (0.04 sec) records:0 duplicates:0 warnings:0mysql>Show create table info; Look again at the type of storage engine used by the info table +-------+---------------------------------------------------------------------------------- ----------------------------------------------+| Table |                                                                                                                   Create Table |+-------+----------------------------------------------------------------------------------------------------- ---------------------------+| info | CREATE TABLE "Info" ("name" char (TEN) default NULL, "Score" decimal (5,2) default null) Engine=myisam default Charset=utf     8 | Here you can see that the storage engine has been modified to MyISAM type +-------+---------------------------------------------------------------------------- ----------------------------------------------------+1 Row in Set (0.00 sec)

②, modify the MySQL configuration file/etc/my.cnf, specify default-storage-engine option to set the default storage engine (this is for subsequent newly created tables)

  [[email protected] ~]# vim/etc/my.cnf ... Omit [mysqld] ... Omit pid-file =/usr/local/mysql/mysqld.piddefault-storage-engine=myisam//Add specify default storage engine type Myisamsocket =/usr/local/mysql /mysql.sock ... The MySQL service needs to be restarted after the configuration file has been modified > [[email protected] ~]# systemctl restart       Mysqld.service after restarting the MySQL service, we re-enter the database and then create a new table in the View storage engine mysql> CREATE database test;       Create a new database Testquery OK, 1 row affected (0.01 sec) mysql> use test;        Enter test database changedmysql> CREATE TABLE test (id int);      Create a test table query OK, 0 rows affected (0.01 sec) mysql> Show create TABLE test; View storage engine for test table +-------+----------------------------+| Table | Create Table |+-------+----------------------------+| Test |      CREATE TABLE "Test" ("id" int (one-by-one) default NULL) Engine=myisam default Charset=utf8 | Here you can see that the storage engine is MyISAM +-------+----------------------------+1 row in Set (0.03 sec)  

③, using the CREATE table to specify the default storage engine when creating tables (you specify when creating a new table)

mysql> use school;Database changedmysql> create table zyc (id int) engine=InnoDB;    //创建一个新表zyc并且指定存储引擎为InnoDBQuery OK, 0 rows affected (0.02 sec)mysql> show create table zyc;        //查看zyc的存储引擎+-------+------------------------------+| Table | Create Table                 |+-------+------------------------------+| zyc   | CREATE TABLE "zyc" (  "id" int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |   //这里可以看到存储引擎为InnoDB+-------+------------------------------+1 row in set (0.00 sec)

④, using the Mysql_convert_table_format command to bulk convert the storage engine, the command format is as follows:

Mysql_convert_table_format--user=root--password= password--socket=/temp/mysql.sock--engine= engine library Name table name

Because bloggers use MySQL5.7 version, and this command only MySQL5.5, so here is not detailed introduction, if there is crossing interested, you can install a MySQL5.5 try.

MySQL storage engine to the end of the introduction, please expect: MySQL incremental backup, MySQL master-slave synchronization, MySQL read and write separation

Selection and configuration of "super simple" MySQL storage engine