Shulou(Shulou.com)06/01 Report--

I don't know if you know anything about the classification and operation of MySQL storage engine before. Today, I'm here to tell you a little bit about it. If you are interested, let's take a look at the text. I believe you will gain something after reading the classification and operation of MySQL storage engine.

MySQL storage engine

A storage mechanism that implements different functions according to different engines, and different performance

1. Storage engine classifies the default database engine prior to MyISAM:MySQL 5.0, which is the most commonly used. Has a high insert, query speed, but does not support transactions. Table level locking: the entire table is locked when the data is updated. The utility model has the advantages of less resources and high performance. Weak write performance

InnoDB: the preferred engine for transactional databases, supporting ACID transactions and row-level locking. MySQL 5.5 has been the default database engine since. Compared with MyISAM, it has better cache support, support table space, table partition, and occupy higher hardware resources.

BDB: an alternative to transactional databases derived from Berkeley DB that supports other transactional features such as Commit and Rollback: Memory: a storage engine that stores all data in memory with high insertion, update and query efficiency. But it takes up memory space proportional to the amount of data. And its contents will lose Merge when MySQL restarts: combine a certain number of MyISAM tables into a whole, which is useful for very large-scale data storage: Archive is very suitable for storing large amounts of independent, historical data. Because they are not often read. Archive has efficient insertion speed, but its support for queries is relatively poor Federated: different MySQL CVMs are combined to logically form a complete database. Very suitable for distributed applications Cluster/NDB: highly redundant storage engine, using multiple data machines to jointly provide services to improve the overall performance and security. CSV is suitable for applications with large amount of data, high security and performance requirements: a storage engine that logically divides data by commas. It creates a .csv file for each data table in the database subdirectory. This is a normal text file that takes up one line of text per line of data. The CSV storage engine does not support indexing. BlackHole: black hole engine, any data written will disappear. The relay EXAMPLE storage engine, which is generally used to record binlog for replication, is a stub engine that does nothing. It is intended as an example in the MySQL source code to demonstrate how to start writing a new storage engine. Again, its main interest is for developers. Indexing is not supported by the EXAMPLE storage engine.

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

two。 Engine operation (1) View mysql > show engines; / / View all supported storage engines in the current database mysql > show table status from library name where name=' table name\ G; / / View the status information of the specified table, you can view the storage engine mysql > show create table table name used by the table\ G / / View the structure of the table when it is created, or view the storage engine specified when the table is created. (2) change the default storage engine (effective the next time the table is created, the previous table storage engine will not change) vim / etc/ my.cnf [mysqld] default-storage-engine= engine / etc/init.d/mysqld restartmysql-u root-pmysql > show engines / / verify whether to change (2.2) change the storage engine of the existing table mysql > alter table table name engine= engine; / / change the specified table to the specified storage engine (2.3) specify the storage engine mysql > create table table name field engine= engine when creating the table / / create tables using the specified engine (2.4) batch change the InnoDB of all tables under the specified library to MyISAMyum-y install perl-DBD-MySQL / / the software package mysql_convert_table_format-- user= user name-- password=' password'--socket=/tmp/mysql.sock-- type=MyISAM database name / / change the storage of all tables under the specified library Applicable to the storage engine of source code mysql_convert_table_format-user= user name-password=' password'- socket=/var/lib/mysql/mysql.sock-type=MyISAM database name / / change all tables under the specified database; applicable to RPM or YUM

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

After reading this article on the classification and operation of MySQL storage engines, what do you think? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.