MySQL storage engine MyISAM and InnoDB

The essence of storage engine is how to realize the method of storing data, indexing and querying, changing and deleting data.

• MySQL supports the plug-in table storage engine, a unique plug-in architecture that allows the storage engine to have a variety of applications that depend on it. One of the more well-known storage engines is MyISAM and InnoDB.
• In a MySQL system, the storage engine is on top of the file system and is transferred to the storage engine before the data is saved to the data file and then stored in the storage format of each storage engine. The main advantage of using this storage engine is that it only needs to provide special application features, less system overhead in the database, and more efficient and efficient database performance.

MyISAM Storage Engine Features

• 1, does not support the transaction processing, needs the transaction support system cannot use the MyISAM as the storage engine
• 2, table-level locking mode, the data will be updated to lock the entire table.
• 3, the database in the process of reading and writing blocking each other:

  Blocking the user's reading of the data during data writing;
  Blocking the user from writing data during data reading;

• 4, you can set the cache index through key_buffer_size, improve the performance of access, reduce the pressure of disk IO.
• 5, the use of MyISAM storage engine does not support foreign key constraints, only support full-text indexing.
• 6, using the MyISAM storage engine to write or read data separately, faster and less resource-intensive.
• 7. MyISAM stored file types:

  . frm file storage table definition;
  The data file name extension is. MYD (MYDATA);
  The index file name extension is. MYI (Myindex);

InnoDB Storage Engine Features

• 1, support transaction processing, support four transaction isolation level.
• 2, row-level locking, but full table scan will lock the entire table.
• 3. Read and write blocking is related to the transaction isolation level.
• 4, with very efficient caching features, can cache the index, but also can cache data.
• 5. Table and primary key are stored in cluster mode.
• 6, support partition, table space, similar to Oracle database.
• 7, support foreign KEY constraints, MySQL5.5 previously does not support full-text indexing, later version support.
• 8, suitable for the high requirements of hardware resources occasions.

How the storage engine changes and chooses

• To view the storage engine methods used by the database:

Show table status from school where name= ' info ';

Or:

Show create table info;

Several ways to configure the storage engine:

• 1. Use the ALTER TABLE command to modify:
  (Modify the storage engine for an existing table)

ALTER TABLE info ENGINE=MYISAM/INNODB; Entering database operations

• 2. Modify the default storage engine:
  (works for newly created tables later)

#vim/etc/my.cnf

default-storage-engine=MyISAM/InnoDB      //在[mysql]服务栏下设置默认存储引擎

• 3. Specify the type of storage engine used when creating the table://in MySQL mode

CREATE TABLE test (name varchar) Engine=innodb;
Show create TABLE test;

• 4. Mysql_convert_table_format command can convert the storage engine in bulk
  Note: This tool can only be used until the MySQL5.5 version is present.

Yum Install PERL-DBI perl-dbd-mysql-y//Installation Operation runtime

By default, you can only change the storage engine of an existing InnoDB to MyISAM form, and if you need to alter the command execution script in turn:

Vim/usr/local/mysql/bin/mysql_convert_table_format

(The fourth storage engine that changes multiple tables simultaneously is an example of the MySQL5.5 version)

$opt_help=$opt_version=$opt_verbose=$opt_force=0;$opt_user=$opt_database=$opt_password=undef;$opt_host="localhost";$opt_socket="";$opt_engine="MYISAM";$opt_port=0;$exit_status=0;GetOptions(  "e|engine|type=s"       => \$opt_type,        //32行，type更改为engine即可  "f|force"               => \$opt_force,  "help|?"               => \$opt_help,  "h|host=s"              => \$opt_host,  "p|password=s"          => \$opt_password,  "u|user=s"              => \$opt_user,  "v|verbose"             => \$opt_verbose,  "V|version"             => \$opt_version,  "S|socket=s"            => \$opt_socket,  "P|port=i"              => \$opt_port

Then use: (Use this command in Linux mode)

Mysql_convert_table_format--host=localhost--user=root--password=abc123--socket=/tmp/mysql.socket--engine= InnoDB School test01 test02

--host=localhost    //表示本地数据库--user=root           //表示登录用户--password=abc123  //表示登录密码--socket=/tmp/mysql.socket  //指定socket目录--engine-InnoDB       //需要更改后的存储引擎school      //指定需要更改的库test01 test02    //需要同时更改的表

You can change the storage engine for test01 test02 multiple tables at the same time.

MySQL storage engine MyISAM and InnoDB