MySQL-(2) storage engine

Source: Internet
Author: User

MySQL-(2) data stored by the storage engine in mysql is stored in files or memory using different technologies. These technologies use different storage mechanisms, indexing techniques, and locking levels to provide users with different choices. These different technologies and related functions are called storage engines (or table types) in mysql ). There are many different storage engines configured in mysql. You can flexibly choose a storage engine for servers, databases, and tables to achieve maximum performance and flexibility. This is also the main factor that mysql is so popular with other large databases and mysql. Some large databases only use one storage engine. meeting all requirements in one size means sacrificing some performance.
In the command line, you can use the command to display the supported storage engines: show engines; the storage engines supported in mysql mainly include MyISAM, CSV, MRG_MYISAM, BLACKHOLE, InnoDB, MEMORY, ARCHIVE... Www.2cto.com
(1) MyISAM: the default Mysql database, which is most commonly used. It has a high insert and query speed, but does not support transactions. frm: storage table structure. myd: data storage. myi: storage index, abbreviation of MYIndex (2) Inn0DB: the preferred engine for transactional databases, supports ACID transactions, and supports row-level locking (3) BDB: originated from Berkeley DB, another option of transaction-type database, supporting other transaction features such as COMMIT and ROLLBACK (4) Memory: A storage engine with all data in Memory, with extremely high insertion, update, and query efficiency. However, memory space is directly proportional to the data volume. And the content will be lost when Mysql is restarted (5) Merge: combines a certain number of MyISAM tables into a whole, which is useful in ultra-large scale data storage.
(6) Archive: it is suitable for storing a large number of independent data records as historical data. Because they are not frequently read. Archive has an efficient insert speed, but has poor query Support (7) Federated: combines different Mysql servers to form a complete database logically. It is ideal for Distributed Application Cluster/NDB: A high-redundancy storage engine that uses multiple data machines to provide services to improve overall performance and security. Suitable for applications with large data volumes, high security and high performance requirements (8). CSV: A storage engine logically separated by commas. It creates a. CSV file for each data table in the database subdirectory. This is a common text file. Each Data row occupies one text row. The CSV storage engine does not support indexing. (9) BlackHole: The black hole engine, and any data written will disappear. It is generally used to record the relay www.2cto.com for binlog replication. Each storage engine has its own advantages, it cannot be said in general who has better performance, but is not suitable. (1) MyISAM storage engine: it is mainly used to manage non-transactional tables. It provides high-speed storage and retrieval, and full-text search capabilities. The storage engine inserts data quickly, but the space and memory usage efficiency is low. If a table is suitable for inserting new records and reading records, you can use the MyISAM storage engine to achieve high processing efficiency. (2) InnoDB Storage engine: it is mainly used for transaction processing applications and supports foreign keys. It also supports crash recovery and concurrency control. If transaction integrity requirements are high and concurrent notifications are required, InnoDB Storage engine is preferred. If you need to perform frequent update and delete operations, you can also select the storage engine because the storage engine can submit and roll back things. (3) Memory storage engine: the Memory storage engine provides a "in-Memory" table. All data of this storage engine is stored in the Memory. The data processing speed is fast but the security is not high. Author: Chen qingting

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.