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

I don't know if you have any knowledge about the similar mysql storage engine before. Today I'm here to tell you a little bit about it. If you are interested, let's take a look at the body. I believe you will gain something after reading the introduction of mysql storage engine.

The physical file composition of mysql includes error log, query log, slow query log, transaction log, binary log.

The log file records the changes that occur during the operation of the mysql database, records the client connection status of the mysql database, the execution of sql statements and error messages.

The logical structure of mysql can be regarded as a two-tier architecture, and the first layer is called SQL layes. All the work before the database system deals with the underlying database is completed in this layer, including authority judgment, sql parsing, execution plan optimization, query cache processing and so on. The second layer is the storage engine layer, called storage engine layes, which is also the implementation part of the underlying data access operation, which is composed of a variety of storage engines.

In fact, each layer contains many modules.

Mysql storage engine

Previous versions of mysql5.1 were compiled and installed together when they needed to be installed.

Starting from mysql5.1, mysql AB makes the storage engine and sqllayes more independent during the architecture transformation, so that the new storage engine can be loaded online without affecting the normal operation of mysql. The architecture of plug-in storage engine makes the loading and removal of storage engine more flexible and convenient.

The main mysql storage engine plug-ins include myLSAM,Innodb,DDB cluster,Maria,Falcon,Memory,Archive, the most famous and widely used myLSAM and Innodb storage engines.

MylSAM storage engine is the default storage engine before mysql5.1

The table of the myLSAM storage engine is stored in the database, each table is stored as three physical files named after the table name, any storage engine will store the table structure with .frm file, and the .MYD and .MYL files store the table data and index data respectively. Each table has and only three files as myLSAM storage type of table storage. No matter how many indexes the table has, it is stored in the same .MYL file.

MyLSAM to these three types of indexes

B-Tree index, that is, all index borrowing points are installed with balance tree data structure to store, and all index data nodes are in the leaf node.

R-Tree index, storage mode and B-Tree index have some differences, mainly designed to store spatial multi-dimensional data fields to do the index.

The Full-text index is the full-text index, and the storage structure is also B-Tree. As long as to solve the inefficient problem that we need to use like query.

The most frequently used is the B-Tree index, which is occasionally used by the Fulltext,R-Tree index, which is rarely used in general systems. The sum of all fields in an B-Tree index cannot exceed 1000 bytes.

Transactions are not supported

Only watch locks.

Although each MyLSAM table is stored in a .MYD file with the same suffix, the format of each file may not actually be exactly the same. Because the data storage format of MyLSAM is divided into three formats: static fixed length, dynamic variable length and compression. It is up to you to choose whether to compress the three formats. You can specify compressed and defalut through ROW_FORMAT when you create the table, or you can compress it through the myisampack tool. There is no compression by default. In the case of non-compression, whether it is static or dynamic is related to the definition of the fields in the table. Only if there are fields of variable length type in the table, the table is in DYNAMIC format, and if there are no variable length fields, it is FIXED format. You can use the alter table command to forcibly convert a dynamix table with a field of type varchar to FIXED, but it is as if the original VARCHAR field is automatically converted to type CHAR. Conversely, if you convert FIXED to DYNAMIC, the CHAR type field is also converted to VARCHAR type.

Estimate the disk space occupied by the records of the table

How many bytes are there in a row in a table?

If you add a few rows of records to each table with a more database, you can calculate how much hard disk space you need to add to each table, and you can estimate how much space to plan according to the amount of data.

Create database tanhong

Create table tanhong.tan (stuid varchar (10) not null primary key, stunmae varchar (20) not null, stusex char (2) not null,stucardid varchar (20) not null,stucardid varchar (20) not null, birthday datetime defaukt null,address varchar (100) default null)

Desc tanhong.tan

10 bytes + 20 bytes + 2 bytes + 20 bytes + 8 bytes + 100 bytes = 1.6MB

After reading this article about the introduction of mysql storage engine, what do you think? If you want to know more about it, you can continue to follow our industry information section.

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.