MySQL Storage engine

MySQL Storage engine

A storage mechanism, different functions depending on the engine, and different performance

1. Storage Engine Classification

• The default database engine before Myisam:mysql 5.0 is most commonly used. Has a higher insert, query speed, but does not support transactions. Table-level locking: The entire table is locked when data is updated. Consumes less resources and has high performance. Weak write performance

• InnoDB: The preferred engine for transactional databases, supports acid transactions, supports row-level locking, and MySQL 5.5 becomes the default database engine. There is better cache support than MyISAM, which supports table space, table partitioning, and high hardware resources.

• BDB: Another option for transactional databases originating from Berkeley DB, supporting other transactional features such as commit and rollback
• Memory: All data is placed into the storage engine, which has very high insertion, update and query efficiency. However, it occupies a memory space that is proportional to the amount of data. And its contents are lost when MySQL restarts
• Merge: Combine a certain number of MyISAM tables into a single unit that is useful for ultra-large data storage
• Archive: Ideal for storing large amounts of independent data as historical records. Because they are not often read. Archive has an efficient insertion speed, but its support for queries is relatively poor
• Federated: Combine different MySQL servers to logically make up a complete database. Ideal for distributed applications
• CLUSTER/NDB: A highly redundant storage engine that provides services in conjunction with multiple data machines to improve overall performance and security. Suitable for applications with high data volumes, security and performance requirements
• CSV: A storage engine that logically divides data by commas. It will create a. csv file for each data table in the database subdirectory. This is an ordinary text file, where each row of data occupies a line of text. The CSV storage engine does not support indexing.
• Blackhole: Black hole engine, any data written will disappear, generally used to record binlog do replication relay
• The EXAMPLE storage engine is a stub engine that doesn't do anything. It is intended as an example of MySQL source code to demonstrate how to start writing a new storage engine. Similarly, its main interest is to developers. The EXAMPLE storage engine does not support indexing.

In addition, MySQL's storage engine interface is well defined. Interested developers can write their own storage engine by reading the documentation.

2. Engine operation (1) View

mysql> show engines;                        //查看当前数据库所有支持的存储引擎mysql> show table status from 库名 where name=‘表名‘\G;     //查看指定表的状态信息，可查看表使用的存储引擎mysql> show create table 表名\G;              //查看创建表时的结构，也可查看表创建时指定的存储引擎

(2) Change (2.1) Change the default storage engine (takes effect the next time the table is created, the previous table storage engine does not change)

vim /etc/my.cnf[mysqld]default-storage-engine=引擎/etc/init.d/mysqld restartmysql -u root -pmysql> show engines;                        //验证是否更改

(2.2) Changing the storage engine of an existing table

mysql> alter table 表名 engine=引擎;                //将指定表更改为指定的存储引擎

(2.3) Specifying the storage engine when creating a table

mysql> create table 表名 字段 engine=引擎;            //创建使用指定引擎的表

(2.4) Change the InnoDB of all tables under the specified library to MyISAM

yum -y install perl-DBD-MySQL       //安装命令所需的软件包 mysql_convert_table_format --user=用户名 --password=‘密码‘ --socket=/tmp/mysql.sock --type=MyISAM 数据库名           //更改指定库下的所有表的存储引擎；适用于源码mysql_convert_table_format --user=用户名 --password=‘密码‘ --socket=/var/lib/mysql/mysql.sock --type=MyISAM 数据库名         //更改指定库下的所有表的存储引擎；适用于RPM或YUM

Note: The Mysql_convert_table_format command can only be changed from the InnoDB engine to MyISAM

MySQL Storage engine