MySQL: Storage Engine Introduction

Preface, what is the storage engine

Table ===> files created in the Library ===> folder library in MySQL

There are different types of files that we use to store data in real life, and each file type corresponds to a different processing mechanism: For example, TXT type for processing text, Excel for processing tables, PNG for image processing, etc.

Tables in the database should also have different types, with different types of tables, and different access mechanisms for MySQL, and the table type is also known as the storage engine.

The storage engine plainly is how to store the data, how to index the stored data, and how to update, query the data and other technologies to implement the
Method. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, storage and
Manipulate the type of this table)

There is only one storage engine in a database such as Oracle and SQL Server, and all data storage management mechanisms are the same. and MySQL
The database provides a variety of storage engines. Users can choose different storage engines for their data tables according to different requirements, and users can also
Your own need to write your own storage engine

Components such as SQL parser, SQL optimizer, buffer pool, storage engine, and so on are present in each database, but not every database has so many storage engines. MySQL's plug-in storage engine allows developers of the storage engine layer to design the storage tier they want, for example, some applications need to meet the requirements of the transaction, some applications do not need to have such strong requirements for the transaction, some want the data to be persisted, and some only want to put in memory, temporarily and quickly to provide data query.

One, MySQL storage engine

The storage engine is how to store the data, how to index the stored data, and how to update and query the data. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, the type that stores and operates this table). MySQL5.5 uses the InnoDB storage engine later by default.

is a comparison of the various storage engines in MySQL.

1.MyISAM:

This engine was first provided by MySQL. It does not support transactions, and foreign keys are not supported, especially for faster access. This engine can also be divided into static MyISAM, dynamic MyISAM and compression MyISAM three kinds:

1) Static MyISAM: If the length of each data column in the datasheet is pre-fixed, the server will automatically select this type of table. Because each record in the data table occupies the same amount of space, the table accesses and updates are highly efficient. When data is compromised, recovery is easier to do. The advantage of this storage method is that the storage is very fast, easy to cache, the fault is easy to recover, the disadvantage is that the space occupied is usually more than the dynamic table.

2) Dynamic MyISAM: If the varchar, xxxtext, or Xxxblob fields appear in the datasheet, the server will automatically select this type of table. Compared with static MyISAM, this kind of table storage space is relatively small, but because of the length of each record is different, so the data in the data table can be stored in memory after multiple modifications, resulting in a decrease in execution efficiency. Also, there may be a lot of fragmentation in memory. Therefore, this type of table is often used with the optimize table command or the MYISAMCHK-R command or optimization tool to defragment, improve performance, and recovery is relatively difficult in the event of a failure.

3) Compression MyISAM: The two types of tables mentioned above can be compressed with the Myisamchk tool. This type of table further reduces the amount of storage consumed, but the table can no longer be modified after it is compressed. In addition, because it is compressed data, such a table should be read to the first time to extract the rows. However, regardless of the MyISAM table, it does not currently support transactional, row-level, and foreign key constraints.

2.Merge:

This type is a variant of the MyISAM type. Merging tables is the merging of several identical MyISAM tables into a single virtual table. Often applied to logs and data warehouses.

3.InnoDB:

The InnoDB table type can be thought of as a further update to the MyISAM product, which provides the functionality of transaction, row-level locking mechanisms, and foreign key constraints. Compared to the MyISAM storage engine, InnoDB writes are less efficient and consume more disk space to preserve data and indexes.

4.memory:

This type of data table only exists in memory. It uses a hash index, so the data is accessed very quickly. Because it exists in memory, this type is often applied to temporary tables, but once the server is closed, the data in the table is lost, but the table continues to exist. By default, the memory data table uses a hash index, which makes the "equality comparison" very fast, but the "range comparison" is much slower. Therefore, the hash index value is suitable for use in the "=" and "<=>" operators, not suitable for use in the "<" or ">" operators, also not applicable in the order by sentence. If you do want to use the < or > or the betwen operator, you can use the Btree index to speed up.

The data rows stored in the Memory data table use the same length format, thus speeding up processing, which means that you cannot use a variable length data type such as BLOB and text. VARCHAR is a variable-length type, but it can be used because it is treated as a fixed-length char type within MySQL.

Use using Hash/btree to specify specific to index: CREATE index Mem_hash using HASH on Tab_memory (city_id);

5.archive:

This type only supports SELECT and INSERT statements, and does not support indexing. Often applied to logging and aggregation analysis.

6.BLACK HOLE:

A black hole storage engine that can be applied to the distribution Master library in primary and standby replication.

7.NDB Storage Engine:

In 2003, MySQL AB acquired the NDB storage engine from Sony Ericsson Corporation. The NDB storage engine is a clustered storage engine, similar to Oracle's RAC cluster, but unlike the share everything structure of the Oracle RAC, its structure is a cluster architecture that share nothing, thus providing a higher level of high availability. The NDB storage engine is characterized by the fact that the data is all in memory (starting with version 5.1, non-indexed data can be placed on disk), so the primary key lookup (primary key lookups) is extremely fast and can be added online NDB data node In order to improve database performance linearly. Thus, the NDB storage engine is a highly available, high-performance, highly scalable database cluster system, which is also a database application type for OLTP.

8.NEST Storage Engine:

NetEase developed a storage engine for its internal use. The current version does not support transactions, but provides features such as compression, row-level caching, and, in the near future, memory-oriented transaction support.

　　The MySQL database also has many other storage engines, which are just some of the most commonly used engines. If you like, you can write your own engine, this is the ability of open source to us, but also the charm of open source.

Second, how the storage engine chooses

Whether to support transaction retrieval and add speed lock mechanism whether the cache supports foreign keys for full-text indexing

Three, MyISAM and InnoDB contrast

Four, when to use MyISAM and InnoDB

    MyISAM: Read transaction requirements are not high, to query and insert the main, you can use this engine to create tables, such as various tables. InnoDB: High demand for transactions, preservation of important data such as transaction data, payment data, etc., to the user's important data, it is recommended to use InnoDB.

Five, operations on the storage Engine 1. View the default storage engine for the database:

Show engines; Show variables like ' default_storage_engine ';

2. View the storage engine for the table:

1) Display the creation statement for the table:

Show CREATE TABLE tablename;

2) Displays the current status value of the table:

Show table status like ' tablename ' \g

　　

3) Set or modify the table's storage engine

• The basic syntax for setting up the storage engine when creating a database table is:

CREATE TABLE TableName (columnName (column name 1) type (data type) Attri (property set), ColumnName (column name 2) type (data type) Attri (property setting),: ..) Engine = Enginename

　　

• Modify the storage engine, you can use the command

Alter table TableName engine = Enginename

4) Practice

Create four tables, using the Innodb,myisam,memory,blackhole storage engine, for insert data testing

MariaDB [db1]> CREATE TABLE t1 (id int) Engine=innodb; MariaDB [db1]> CREATE TABLE t2 (id int) Engine=myisam; MariaDB [db1]> CREATE TABLE t3 (id int) engine=memory; MariaDB [db1]> CREATE table t4 (id int) engine=blackhole; MariaDB [db1]> quit[[email protected] db1]# ls/var/lib/mysql/db1/#发现后两种存储引擎只有表结构, no data db.opt t1.frm t1.ibd  T2. MYD  T2. MYI  t2.frm  t3.frm  t4.frm#memory, after restarting MySQL or restarting the machine, the data in the table is emptied #blackhole, inserting any data into the table is equivalent to throwing into a black hole, the table will never save records

　　

Five, configuration and data files

1. configuration file default location

Linux:/etc/my.cnf Windows:my.ini

2. Data File location

1) command to view the location of the data file: show variables like '%datadir% ';

2) Data file format:

INNODB:FRM (stored table structure), IBD (stored data and indexes)

MYISAM:FRM (stored table structure), MYD (stored data), MYI (stored index)

VI, database table design

1. First Paradigm

1) Concept: column is not divided. Each column is an indivisible basic data item.

2) Example: Suppose we have a student table, the fields include: Id,name,age,contact, as follows:

When we need to query the students according to QQ, we can not find out, so the above design is not in line with 1NF. We can split the contact field into phone and QQ, as follows:

This will satisfy the 1NF.

2. Second Paradigm　

1) Concept: Based on the 1NF, the non-primary attribute is completely dependent on the primary key.

2) Example: Student table: (School number, name, age, course name, score, credits), from the field can be seen, this table joint primary key is (student number, course name).

The following decision relationships exist:

1: (School number, course name) → (name, age, score, credits)

2: (course name) → (credits)

3: (school number) → (name, age)

Among them, the name, age, credit is partially dependent on the primary key, and the results are completely dependent on the primary key, there is a partial dependency, so do not meet the second normal.

This can cause the following problems:

(1) Data redundancy:

The same course by N students elective, "credit" repeated n-1 times, the same student elective m courses, name and age repeated m-1 times.

(2) Update exception:

If the credit of a course is adjusted, the "credits" value of all the rows in the data sheet should be updated, otherwise the same course credit will be different.

(3) Insert exception:

Suppose a new course is to be opened and no one has yet been enrolled. Thus, the course name and credits cannot be recorded in the database because there is no "learning number" keyword.

(4) Delete exception:

Assuming that a group of students has completed elective courses, these elective records should be removed from the database table. At the same time, however, the course name and credit information were also removed. Obviously, this can also lead to an insertion exception.

The problem is that there is a partial dependency of the non-primary attribute on the primary key.

Solution: The original form (school number, name, age, course name, score, credit) is divided into three tables:

Student: Student (school number, name, age);

Course: Course (course name, credits);

Elective relationship: Selectcourse (School number, course name, score).

3. The third paradigm

1) Concept: On the basis of 2NF, the attribute does not depend on other non-primary attributes, eliminating transitive dependencies. The third paradigm can also be described as: there is no non-critical field in the table that can determine other non-keywords.

2) Example: Student table: (School number, name, age, school, college location, college phone), the primary key is necessarily the school number.

Because the primary key is a single attribute, the non-primary attribute is completely dependent on the primary key, so the second paradigm must be satisfied. However, the following transitive dependencies exist:

(school number) → (school) → (college location, college phone),

College location and college phone transfer depends on the number, while the college location and the college phone are non-critical fields, that is, "a non-critical field can identify other non-critical fields," the case, thus violating the third normal form.

Workaround:

Divide the original table into two tables:

Student: (School number, name, age, school);

College: (College, location, telephone).

MySQL: Storage Engine Introduction