MySQL storage engine MyISAM and InnoDB

MySQL storage engine MyISAM and InnoDB storage engine

• Data in MySQL has a variety of different technologies for storing files or in memory. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of unused functionality 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.

MyISAM Storage Engine

• The MyISAM storage engine is the default storage engine before MySQL relational database system version 5.5.

Characteristics

1. Do not support transactions

2, table-level locking mode, the data is updated to lock the entire table

3, the database in the reading and writing process of mutual blockage:

• 1), in the process of data writing blocking the reading of the user read data
• 2), blocking the user from writing data during data read

4, you can set the cache index through key_buffer_size, improve the performance of access, reduce the pressure of disk IO

5, using MyISAM storage engine to write or read the row data separately, the speed is more block and occupy less resources

6. The MyISAM storage engine does not support foreign key constraints, only full-text indexing is supported

7. Each myisam is stored on disk as three files, each name starts with the name of the table, and the extension indicates the file type:

• 1),. frm File store table definitions.
• 2), the data file extension is:. MYD (MYData).
• 3), the index file extension is:. MYI (Myindex).

InnoDB Storage Engine Features

1. Supporting Things

2, row-level locking, but the overall scan will still be a table-level lock.

3. Read-write blocking is related to transaction isolation level

4, with very efficient cache characteristics, can cache the index, but also can cache the 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,

8, suitable for the high requirements of hardware resources occasions

Configure the appropriate storage engine 1, view the default storage engine used by MySQL

Mysql-u root-p #进入MySQL

Show engines; #查看默认存储引擎

2. View Table Storage Engine Type

• Use the Show Table Status command to view:

Mysql-u root-p

Show table status from test where name= ' test '; #查看test数据库中的test表的存储引擎类型

• Use show create to view the table storage engine type:

Mysql-u root-p

Use test; #使用test数据库

Show create TABLE test; #查看test表的存储引擎类型

3. Modify the storage engine

• Use the ALTER TABLE command to modify (only existing tables can be modified):

Mysql-u root-p

Use test;

ALTER TABLE Test Engine=myisam; #修改test表的存储引擎类型

Show create TABLE test;

• Modify the MySQL configuration file my.cnf, you can specify the Defaut-storage-engine option to set the default storage engine (try only on the newly created table, the storage engine type of the existing table is the same):

VIM/ETC/MY.CNF #编辑配置文件

#省略内容[mysqld]default-storage-engines=MyISAM#省略内容

Systemctl Restart Mysqld.service #重启MySQL服务

• Use the CREATE table to specify the storage engine with engine (now the default storage engine for the database is MyISAM);

Mqsql-u root-p

Use test;

CREATE TABLE test01 (id int) Engine=innodb; #创建存储引擎为InnoDB的表

Show CREATE TABLE test01;

• Use the Mysql_convert_table_format command to convert the storage engine in bulk (only for the MySQL5.5 version);

Yum Install Prel-dbi-y

Yum Install prel-bdb-mysql-y #安装perl对MySQL的模块操作

Mysql_convert_table_format--user=root--password= password--socket=/temp/mysql.sock--engine=myisam test test

However, when you use this command to change the storage engine type to InnoDB, an error occurs, so you need to modify the script file for its command

Vim/use/local/mysql/bin/mysql_convert_table_format

#省略内容"e|engine|type=s"   

You can use the command again

Mysql_convert_table_format--user=root--password= password--socket=/temp/mysql.sock--engine=innodb test test

MySQL storage engine MyISAM and InnoDB