MySQL Storage engine recognizes

Storage Engine is a major feature of MySQL, learning MySQL must understand it, know its various engines differences, how to choose and use the right. So it's a brief introduction.

1. Look at the types of engines supported by the system, such as the commands in.

2. Set the system to use a specified storage engine

There are many ways to specify a storage engine to use. A simpler approach is to set a default engine type in the MySQL configuration file My.ini or MY.CNF, such as using the DEFAULT_STORAGE_ENGINE=INNODB option , or add the--default-storage-engine=innodb or--DEFAULT-TABLE-TYPE=INNODB option after the command line when you start the database server.

A more flexible approach is to specify the storage engine used when the MySQL client connects to the MySQL server, such as mysql-h192.168.1.88-uroot-p123456--default-storage-engine=innodb.

A more practical way is to change the storage engine used by existing tables, such as ALTER TABLE mytable Engine=myisam; However, it is important to note that when you modify a table type this way, you may lose data because the engine does not support issues such as the same index, field type, or table size. If you specify a storage engine that does not exist in the current database, a table of MyISAM (the default) type is created.

3. Introduction to the features of the storage engine

A. InnoDB is the only transaction security table that has commit, rollback, and crash resiliency; It supports foreign keys, but its locks are row locks, making the resource occupy relatively high when locking, and prone to deadlock. In addition, InnoDB writes less efficiently and consumes more disk space to preserve data and indexes. There are two types of InnoDB storage: One is to use shared tablespace storage, and the other is to use a separate table space. InnoDB the transaction is open by default, that is, each time a record is inserted, the InnoDB type of table will treat it as a separate transaction. So if we insert 10,000 records and do not close the transaction, then the InnoDB type of table will treat it as 10,000 transactions, the total time to insert is many, this time must first turn off the transaction and then insert (set autocommit=0), The speed is fast.

B. MyISAM does not support transactions or foreign keys, but it has fast access and no requirement for transactional integrity. Its lock is a table lock that consumes less resources and does not deadlock. Each of the MyISAM tables corresponds to three files on the hard disk. These three files have the same file name, but have different extensions to indicate their type purpose:

The. frm file saves the definition of the table, but this file is not part of the MyISAM engine, but a part of the server;

. MyD Save the data of the table;

. Myi is the index file for the table.

C. The memory storage engine stores the table's data in RAM, and each memory meter only actually corresponds to a. frm disk file, which is the definition of a table. Memory Type table access is very fast because its data is placed in memory, and the hash index is used by default. But once the service is closed, the data in the table is lost.

D. The merge storage engine is a combination of a set of MyISAM tables that logically combine the table MyISAM tables you specify into a single table, provided that the MyISAM tables must be structurally identical. The merge table itself has no data, and the query, update, and delete operations on the merge type table are performed on the internal MyISAM table.

The actual business of InnoDB and MyISAM used relatively more, select the engine needs to depend on the actual situation. The above is my simple understanding of the MySQL storage engine. There are many storage engines, just not used. There is no introduction here.

Remark:it Essays, if reproduced please indicate out, thank you!

Author:terryxia

MySQL Storage engine awareness