Essay-sql three types of storage engines are three kinds of tables

Source: Internet
Author: User

MYSQL Environment variables: ... server/bin

Engine: is the core component of a development program or system on an electronic platform. Using the engine, developers can quickly build, lay out the functions required by the program, or take advantage of the operation of their auxiliary programs. In general, the engine is a program or a set of system support parts. The common program engine has the game engine, the search engine, the antivirus engine and so on.

The storage engine refers to the type of table and how the table is stored on the computer .

The concept of the storage engine is the characteristics of MySQL, there is no dedicated storage engine concept in Oracle, Oracle has OLTP and OLAP mode distinction. Different storage engines determine that the tables in the MySQL database can be stored in different ways. We can choose different storage engines based on the characteristics of the data.

Through show engines; View the engine under SQL

1.InnoDB

InnoDB to MySQL provides transactional, rollback , crash- repair , and Multi-version concurrency control transaction security. In MySQL starting from 3.23.34a contains Innnodb. It is the first table engine on MySQL to provide a foreign key constraint. and InnoDB's ability to handle transactions is unmatched by other storage engines. The later version of MySQL's default storage engine is InnoDB support ID self-increment , also support foreign key

The advantage of InnoDB is that it provides good transaction processing, crash-repair capabilities, and concurrency control. The disadvantage is that the reading and writing efficiency is poor and the data space occupied is relatively large (even table query).

2.MyISAM

The MyISAM table is stored as 3 files. The name of the file is the same as the table name. Expand the name of frm,MYD,MYI. In fact, the FRM file storage table structure, myd file storage data, is mydata abbreviation; myi file storage index, is the abbreviation of Myindex.

Tables based on the MyISAM storage engine support 3 different storage formats. Includes static, dynamic and compression types.

Among them, static type is the default storage format of MyISAM, its field is fixed length, the table has very high access speed, even in the operation of the data table is the same, but its biggest disadvantage is the large occupancy space;

Dynamic type contains variable-length field, the length of the record is not fixed, the advantage is that it is stored data, using the actual length of the data, can save a lot of space, but also because of this, when the data to be updated, the length of the change,
There will be no original position, and there are other locations, resulting in a hole in the original position, and the associated data is not stored in the adjacent block, and produce a large number of fragments, to be regularly defragmented;

The compression type needs to use the Myisampack tool, the space occupies is very small, only half of the original size, and when reading data, the data is also compressed, and note that the type of table is read-only table, cannot be modified.

3.MEMORY

The data is all in memory ,

Each table that is based on the memory storage engine actually corresponds to a disk file. The file has the same filename as the table name, and the type is the frm type. Only the structure of the table is stored in the file. The data files are stored in memory, which facilitates the fast processing of data and improves the efficiency of the whole table. It is important to note that the server needs to have enough memory to sustain the use of tables in the memory storage engine. If you don't need it, you can free up memory and even delete unwanted tables.

Memory uses a hash index by default. Faster than using the B-Tree index. Of course, if you want to use the B-tree index, you can specify it when you create the index.

Note that memory is rarely used because it is stored in memory, which can affect the data if an exception occurs. If you restart or shut down the computer, all data will disappear. Therefore, memory-based tables have a short life cycle and are generally disposable.

Essay-sql three types of storage engines are three kinds of tables

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.