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

Blog outline:

First, MyISAM storage engine; second, InnoDB storage engine; third, the characteristics of Memory storage engine; fourth, how to choose the appropriate storage engine? Fifth, view and modify the default engine preface of the table

The database storage engine is the underlying software component of the database. The database management system (DBMS) uses the data engine to create, query, update and delete data. Different storage engines provide different storage mechanisms, indexing skills, locking levels and other functions, using different storage engines, you can also get specific functions. Many different database management systems now support a variety of different data engines. The core of MySQL is the storage engine.

MySQL provides several different storage engines, including an engine for transactional security tables and an engine for non-transactional security tables. In MySQL, instead of using one engine throughout the server, you can use a different storage engine for each table according to specific requirements.

To put it bluntly, the storage engine is the format of data storage. Different storage engines have different functions, different sizes of space and different reading performance; database storage engine is the underlying software component of the database, and different storage engines provide different storage mechanisms; in MySQL, you do not need to use the same storage engine in the whole server, but you can use different storage engines for each table. MySQL supports multiple storage engines, such as InnoDB, MyISAM, Memory, Merge, Archive, CSV, Federated, and so on. 1. MyISAM storage engine 1. The characteristics of MyISAM storage engine: MyISAM engine before MySQL 5.5 and InnoDB engine after MySQL 5.5.The MyISAM engine reads faster, takes up relatively few resources, does not support transactions, does not support foreign key constraints, but supports full-text indexing; read and write block each other, that is, you cannot write data when you read data, and you cannot read data when you write data The MyISAM engine can only cache indexes, not data. 2. MyISAM is suitable for businesses that do not require transaction support, such as money transfer; for businesses with more read data, but not for businesses with frequent read and write; businesses with relatively low concurrency and relatively few data modifications; machines with poor hardware resources can consider using MyISAM engine. 2. InnoDB storage engine 1, the characteristics of InnoDB storage engine the preferred engine for transactional database, supporting transaction security tables, row locking and foreign keys, after MySQL version 5.5.5, InnoDB as the default storage engine; transaction secure storage engine with commit, rollback and crash recovery, which can handle huge amount of data, high performance and efficiency, and fully support foreign key integrity constraints It has a very efficient caching feature, which can cache both indexes and data, which requires high hardware requirements. When using InnoDB, a 10MB-size automatically extended data file named ibdata1 and two 5MB-size log files named ib_logfile0 and ib_logfile1 will be created under the MySQL data directory. 2. InnoDB applicable scenarios: businesses that require transaction support, and scenarios where highly concurrent business data is updated frequently, such as BBS, SNS, Weibo and other businesses that require high data consistency, such as recharge transfer, bank card transfer 3, Memory storage engine features Memory storage engine stores the data in tables into memory to provide fast access to query and reference other table data The Memory storage engine performs HASH and BTREE indexing, does not support BLOB and TEXT columns, supports AUTO_INCREMENT columns and indexes that can contain NULL worthy columns; when the contents of the Memory table are no longer needed, to free the memory used by the Memory table, you should execute DELETE FROM or TRUNCATE TABLE, or delete the entire table. 4. How to choose the right storage engine?

Different storage engines need to be selected for different business requirements. Please refer to the following points:

InnoDB is a good choice if you want to provide transaction security with commit, rollback and crash resilience and require concurrency control; if data tables are mainly used to insert and query records, the MyISAM engine can provide higher processing efficiency If you only store data temporarily, the amount of data is small, and you do not need high security, you can choose the Memory engine that keeps the data in memory, and MySQL uses this engine as a temporary table to store the intermediate results of the query; if there are only INSERT and SELECT operations, you can choose Archive engine to support highly concurrent insertion operations, such as recording log information can use Archive engine.

The functions supported by the three main data engines are shown in the following table:

5. The default engine for viewing and modifying tables: mysql > show engines\ G; mysql > show variables like 'default_storage_engine'; mysql > show variables like'% engine%'; mysql > show create table T01\ G; mysql > alter table T01 engine=myisam

-this is the end of this article. Thank you for reading-

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.