Shulou(Shulou.com)05/31 Report--

This article focuses on "what is the MySQL storage engine". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what the MySQL storage engine is.

MySQL storage engine

Overview of Storage Engin

The core point of the database is to store data, so data storage can not avoid dealing with disks. So how to store the data and how to store it is the key to storage. So the storage engine acts as the engine for data storage to drive data storage at the disk level.

The architecture of MySQL can be understood according to the three-tier model.

Storage engine is also the construction of MySQL, it is a kind of software, it can do and support the main functions are

Concurrence

Support transaction

Integrity constraint

Physical storage

Support indexing

Performance help

MySQL supports multiple storage engines by default to suit different database applications. Users can choose the appropriate storage engine according to their needs. Here are the storage engines supported by MySQL

MyISAM

InnoDB

BDB

MEMORY

MERGE

EXAMPLE

NDB Cluster

ARCHIVE

CSV

BLACKHOLE

FEDERATED

By default, if you create a table without specifying a storage engine, you will use the default storage engine. If you want to modify the default storage engine, you can set default-table-type in the parameter file to view the current storage engine.

Show variables like 'table_type'

Strange, why is it gone? Check online, cancel this parameter in 5.5.3

You can query the storage engines supported by the current database in the following two ways

Show engines\ g

When you create a new table, you can set the storage engine for the new table by adding the ENGINE keyword.

Create table cxuan002 (id int (10), name varchar (20)) engine = MyISAM

In the figure above, we specify the storage engine for MyISAM.

What if you don't know the storage engine of the table? You can check it through show create table.

If you do not specify a storage engine, the default built-in storage engine for MySQL has been InnoDB since MySQL version 5.1. Build a table and take a look.

As shown in the figure above, we do not specify a default storage engine, let's take a look at the table

As you can see, the default storage engine is InnoDB.

If you want to replace your storage engine, you can use the

Alter table cxuan003 engine = myisam

To replace, "0 rows affected" will be displayed after the replacement is completed, but the operation has actually been successful.

Let's use show create table to check the sql of the table.

Storage engine featur

The following describes several commonly used storage engines and their basic features, which are MyISAM, InnoDB, MEMORY and MERGE

MyISAM

Before version 5.1, MyISAM is the default storage engine for MySQL. MyISAM has poor concurrency and uses fewer scenarios. The main features are

If transactional operations are not supported, the features of ACID do not exist, and this is designed for performance and efficiency considerations.

Foreign key operation is not supported. If you forcibly add a foreign key, MySQL will not report an error, but the foreign key will not work.

The default lock granularity of MyISAM is table-level locks, so the concurrency performance is poor, locking is faster, lock conflicts are less, and deadlocks are less likely to occur.

MyISAM stores three files on disk with the same file name and table name, with .frm (storage table definition), .MYD (MYData, storage data), and MYI (MyIndex, storage index). It is important to note here that MyISAM caches only index files, not data files.

The index types supported by MyISAM are global index (Full-Text), B-Tree index, R-Tree index

Full-Text index: it appears to solve the problem of low efficiency of fuzzy query for text.

B-Tree index: all index nodes are stored according to the data structure of the balanced tree, and all index data nodes are in the leaf node.

R-Tree index: there are some differences between its storage mode and B-Tree index. It is mainly designed to store spatial and multidimensional data fields as indexes. The current MySQL version only supports fields of geometry type as indexes. The advantage over BTREE,RTREE lies in scope search.

If the host where the database is located is down, the data file of MyISAM is easy to be damaged and difficult to recover.

Add, delete, modify and query performance: SELECT has high performance and is suitable for situations with more queries.

InnoDB

Since MySQL 5.1, the default storage engine has become the InnoDB storage engine, which has changed greatly from the MyISAM,InnoDB storage engine. Its main features are

Support for transaction operations, with transaction ACID isolation features, the default isolation level is repeatable read (repetable-read), through MVCC (concurrent version control) to achieve. Can solve the problem of dirty reading and unrepeatable reading.

InnoDB supports foreign key operations.

InnoDB default lock granularity row-level lock, concurrent performance is better, deadlock will occur.

Like MyISAM, the InnoDB storage engine also has .frm file storage table structure definitions, but the difference is that InnoDB table data and index data are stored together, both on the leaf node of B+ numbers, while MyISAM table data and index data are separate.

InnoDB has a secure log file that is used to recover data loss problems caused by database crashes or other situations and to ensure data consistency.

InnoDB and MyISAM support the same index types, but the implementation varies greatly due to different file structures.

In terms of add, delete, modify and query performance, if you perform a large number of add, delete and change operations, it is recommended to use the InnoDB storage engine, which deletes rows during deletion operations and does not rebuild the table.

MEMOR

The YMEMORY storage engine creates tables using content that exists in memory. Each MEMORY table actually corresponds to only one disk file in .frm format. Tables of type MEMORY are accessed quickly because their data is stored in memory. The HASH index is used by default.

MERGE

The MERGE storage engine is a combination of a group of MyISAM tables. The MERGE table itself has no data. The operation of querying, updating and deleting the MERGE type table is actually carried out on the internal MyISAM table. The MERGE table retains two files on disk, one is the .frm file storage table definition, the other is the composition of the .MRG file storage MERGE table, and so on.

Select the appropriate storage engine

In the actual development process, we often choose the appropriate storage engine according to the application characteristics.

MyISAM: if the application is usually retrieval-based, only a small number of insert, update and delete operations, and the integrity of things, the degree of concurrency is not very high, it is usually recommended to choose MyISAM storage engine.

InnoDB: if you use foreign keys and require a high degree of concurrency and data consistency, you usually choose InnoDB engine. Generally, Internet companies have higher requirements for concurrency and data integrity, so InnoDB storage engine is generally used.

The MEMORY:MEMORY storage engine keeps all the data in memory and can provide rapid access when it needs to be located quickly. MEMORY is usually used for small tables that are updated less frequently and for quick access to get results.

The interior of MERGE:MERGE is to use MyISAM table. The advantage of MERGE table is that it can break through the restriction on the size of a single MyISAM table, and it can effectively improve the access efficiency of MERGE table by distributing different tables on multiple disks.

Select the appropriate data type

One of the problems we often encounter is how to choose the right data type when building a table. Usually choosing the right data type can improve performance and reduce unnecessary trouble. Let's discuss it together. How to choose the right data type.

The choice of CHAR and VARCHAR

Char and varchar are the two data types that we often use to store strings. Char generally stores fixed-length strings, which belong to fixed-length character types, such as the following

The value char (5) stores bytes''5 bytes' cx''cx'5 bytes' cxuan''cxuan'5 bytes' cxuan007''cxuan'5 bytes

As you can see, no matter what your value is, once the length of the char character is specified, if the length of your string is not enough to specify the length of the character, then fill it with spaces, and if it exceeds the length of the string, only the characters of the specified length will be stored.

❝note here: if MySQL uses non-strict mode, the last row of the table above can be stored. If MySQL uses strict mode, the last row of storage on the table will report an error. ❞

If the varchar character type is used, let's look at an example

The value varchar (5) stores bytes''1 byte 'cx''cx' 3 bytes' cxuan''cxuan'6 bytes' cxuan007''cxuan'6 bytes

As you can see, if you use varchar, the bytes stored will be stored based on the actual value. You may wonder why the length of varchar is 5, but you need to store 3 or 6 bytes, because when using the varchar data type for storage, the default is to increase the length of a string at the end, occupying 1 byte (if the length of the column declaration exceeds 255, two bytes are used). Varchar does not populate empty strings.

Char is generally used to store fixed-length strings, such as "ID number, mobile phone number, mailbox, etc."; varchar is used to store strings of variable length. Because the length of char is fixed, its processing speed is much faster than VARCHAR, but the disadvantage is a waste of storage space, but with the continuous evolution of MySQL version, the performance of varchar data type is also improving, so in many applications, VARCHAR type is more used.

In MySQL, different storage engines have different principles for using CHAR and VARCHAR.

MyISAM: it is recommended to use fixed-length data columns instead of variable-length data columns, namely CHAR

MEMORY: fixed length is used for processing, CHAR and VARCHAR are treated as CHAR

InnoDB: the VARCHAR type is recommended

TEXT and BLOB

Generally speaking, when saving less text, we will choose CHAR and VARCHAR. When saving text with a large amount of data, we often choose TEXT and BLOB;TEXT and BLOB. The main difference between BLOB and BLOB is that BLOB can save binary data, while TEXT can only save character data, and TEXT can only save character data.

TEXT

MEDIUMTEXT

LONGTEXT

BLOB is subdivided into

BLOB

MEDIUMBLOB

LONGBLOB

Three, their main differences are different length of storage text and different storage bytes. Users should choose the minimum storage type to meet their needs according to the actual situation. Here are some problems in BLOB and TEXT.

TEXT and BLOB have some performance problems after deleting data. In order to improve performance, it is recommended to use the OPTIMIZE TABLE function to defragment the table.

You can also use composite indexes to improve query performance for text fields (BLOB and TEXT). A composite index is to create a hash value based on the contents of large text (BLOB and TEXT) fields and store this value in the corresponding column, so that the corresponding data rows can be found according to the hash value. Generally use hashing algorithms such as md5 () and SHA1 (). If the strings generated by the hash algorithm have trailing spaces, do not store them in CHAR and VARCHAR. Let's take a look at this use.

First create a table that records the blob field and hash values

Inserts data into the cxuan005, where the hash value is the hash value of the info.

And then insert two more pieces of data.

Insert a piece of data whose info is cxuan005

If you want to query the data whose info is cxuan005, you can query the hash column.

This is an example of a composite index, using a prefix index if you want to make a fuzzy query on BLOB.

Other ways to optimize BLOB and TEXT:

Do not retrieve BLOB and TEXT indexes unless necessary

Separate the BLOB or TEXT columns into separate tables.

Selection of floating-point number and fixed-point number

Floating-point number refers to the value containing decimals. After the floating-point number is inserted into the specified column with more than the specified precision, the floating-point number will be rounded. The floating-point number in MySQL refers to float and double, and the fixed-point number refers to decimal. The fixed-point number can save and display data more accurately. Let's use an example to explain the accuracy of floating-point numbers.

First create a table cxuan006, just to test the floating point problem, so the data type we choose here is float

Then insert two pieces of data respectively.

Then execute the query, and you can see that the rounding of the two pieces of data from the query is different.

In order to clearly see the accuracy of floating-point and fixed-point numbers, let's take a look at another example.

First modify the two fields of cxuan006 to the same length and number of decimal places

Then insert two pieces of data

By performing the query operation, it can be found that floating-point numbers produce errors compared to fixed-point numbers.

Date type selection

In MySQL, "DATE, TIME, DATETIME, TIMESTAMP" is used to represent the date type.

138pictures will give you an introduction to MySQL

The differences in date types have been introduced in this article, so we will not elaborate on them here. The following is a brief introduction to the choice

TIMESTAMP is related to the time zone and better reflects the current time. If the recorded date needs to be used by people in different time zones, it is best to use TIMESTAMP.

DATE is used to represent the year, month and day, and you can use DATE if the actual application value needs to be saved.

TIME is used to represent minutes and seconds, and TIME can be used if the actual application value needs to be saved.

YEAR is used to represent years, and YEAR has 2-bit (preferably 4-bit) and 4-bit format years. The default is 4 digits. If the actual application only saves the year, then saving the YEAR type with 1 bytes is fine. It can not only save storage space, but also improve the operation efficiency of the table.

MySQL character set

Let's take a look at the MySQL character set, which is simply a set of text symbols and coding and comparison rules. In 1960, the American Standardization Organization ANSI released the first computer character set, which is the famous ASCII (American Standard Code for Information Interchange). Since ASCII coding, each country and international organization has studied its own character set, such as ISO-8859-1, GBK and so on.

However, each country uses its own character set, which brings great difficulties to portability. Therefore, in order to unify character coding, the International Organization for Standardization (ISO) has designated a unified character standard-Unicode coding, which contains almost all character codes. Here are some common character encodings

Whether the character set is fixed-length encoding ASCII is single-byte 7-bit encoding ISO-8859-1 is single-byte 8-bit encoding GBK is double-byte encoding UTF-8 no 1-4 byte coding UTF-16 no 2-byte or 4-byte encoding UTF-32 is 4-byte coding

For the database, the character set is very important, because most of the data stored in the database are all kinds of characters, and the character set is very important for the storage, performance and system transplantation of the database.

MySQL supports multiple character sets, and you can use show character set; to view all available character sets

Or use the

Select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets

To check it out.

Use information_schema.character_set to view character sets and proofreading rules.

At this point, I believe you have a deeper understanding of "what the MySQL storage engine is". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.