MySQL Storage engine

MySQL's physical file composition includes error logs, query logs, slow query logs, transaction logs, and binary logs.

Log files record the changes that occurred during the MySQL database operation, log the client connection status of the MySQL database, the execution of the SQL statement, and the error message.

The logical structure of MySQL can be seen as a two-tier architecture, the first layer is called SQL Layes, the database system to handle the underlying database before all the work is done at this level, including the right to judge, SQL resolution, Execution plan optimization, query cache processing. The second layer is the storage engine layer, called Storage Engine layes, is also the bottom data access Operation implementation Part, consists of a variety of storage engines.

In fact, each layer contains many of its own modules.

MySQL Storage engine

mysql5.1 versions prior to installation are compiled and installed together,

Starting with mysql5.1, MySQL AB allows the storage engine and Sqllayes to be more independent in their architecture, allowing them to load new storage engines online without compromising MySQL's uptime. The architecture of the plug-in storage engine is more flexible and convenient for loading and removing the storage engine.

The main MySQL storage engine plug-in includes Mylsam,innodb,ddb cluster,maria,falcon,memory,archive, the most famous and most widely used Mylsam and INNODB two storage engines.

Mylsam Storage Engine is the default storage engine prior to mysql5.1

Mylsam Storage Engine table in the database, each table is stored as three physical files named after the table name. Any storage engine is stored in the table structure in the. frm file, where the. myd and. myl files store Both the table's data and the index data, and each table has only such three files as the storage of the table for the Mylsam storage type. Regardless of how many indexes the table has, it is stored in the same one. The Myl file.

Mylsam to three types of indexes

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

R-tree index, storage and B-tree index There are some differences, mainly designed to store a lot of dimensional data of the field index.

The Full-text index is the full-text index, and the storage structure is b-tree. Just to solve the inefficient problem that we need to use like queries.

The most commonly used is the B-tree index, which is occasionally used to fulltext,r-tree indexes that are rarely used in general systems. The sum of the lengths of all fields of the B-tree index cannot exceed 1000 bytes.

Transaction not supported

Only table Locks

Although each Mylsam table is a. myd file with the same suffix name, each file may not actually be in exactly the same format. Because the MYLSAM data storage format is divided into static fixed length, dynamic variable length and compression of these three formats. Three formats are compressed by their own choice, you can create a table by Row_format to specify the compressed and Defalut, you can also use the Myisampack tool to compress, the default is not compressed. In the case of non-compression, it is static or dynamic and is related to the definition of the field in the table. Only fields with variable-length types in the table exist, and the table is in dynamic format, or fixed if there are no variable-length fields. You can forcibly convert a Dynamix table of a field with a varchar type to fixed by using the ALTER TABLE command, but it is as if the original varchar field is automatically converted to the Char type. Conversely, if you convert a fixed to dynamic, the Char type field is also converted to a varchar type.

Measure the amount of disk space consumed by the table's records

How many bytes a row in a table might be

More database tables Each add a few rows of records, you can calculate how much hard disk space to increase, you can estimate according to the amount of data to plan how much space.

Create DATABASE Tanhong

CREATE TABLE Tanhong.tan (stuid varchar) NOT NULL PRIMARY key, Stunmae varchar (2) NOT null Stusex Char Cardid varchar () not null,stucardid varchar (a) NOT NULL, birthday datetime defaukt null,address varchar (+) Default Nu ll);

Desc Tanhong.tan;

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

This article is from the "11853028" blog, please be sure to keep this source http://tanhong.blog.51cto.com/11853028/1903184

MySQL Storage engine