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

This article mainly gives you a brief description of what the MySQL storage engine is and its function. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on what the MySQL storage engine is and its function can bring you some practical help.

1. Introduction of mysql storage engine:

In MySQL versions prior to 5.1 (not included), the storage engine had to be compiled and installed at the same time as MySQL when the MySQL was installed.

But starting from MySQL5.1, MySQL AB has made a great transformation to its architecture, and introduced a new concept: plug-in storage engine architecture. When MySQL AB rebuilds its architecture, it makes the storage engine layer and sql layer more independent and less coupled, and can even load a new storage engine online, that is, it is possible to load a new storage engine into a running MySQL without affecting the normal operation of MySQL. The architecture of the plug-in storage engine makes the loading and removal of the storage engine more flexible and convenient, and also makes it more convenient and simple to develop the storage engine.

The plug-in storage engine of MySQL mainly includes MyISAM,Innodb,NDB Cluster,Maria,Falcon,Memory,Archive, among which MyISAM and Innodb are the most famous and widely used storage engines. MyISAM is an upgraded version of MySQL's earliest ISAM storage engine and the default storage engine for MySQL. In fact, Innodb is not owned by MySQ, but developed by a third-party software company Innobase (acquired by Oracle in 2005). Its biggest feature is that it provides features such as transaction control, so it has a wide range of users.

Some other storage engines are relatively less used in scenarios, and are applied to specific scenarios, such as NDB Cluster, although it also supports transactions, but is mainly used in distributed high-availability cluster environments. Maria is an updated version of MyISAM storage engine newly developed by MySQL. Falcon is a database storage engine with transaction and other advanced features developed by MySQL to replace the current Innodb storage engine. All the data and indexes of the Memory storage engine are stored in memory, and only .frm files are saved on the hard disk, so they are mainly used for temporary tables, or have very high performance requirements, but will consume a lot of memory in certain scenarios that allow data loss during Crash. Archive is a storage engine in which data is compressed and stored in a high proportion. It only supports insert,select, but does not support update and delete. It is mainly used to store expired and rarely accessed historical information, and does not support indexes.

Introduction to the MyISAM storage engine:

1. Default storage engine before mysql5.1.

2. The tables of the MyISAM storage engine are in the database, and each table is stored as three physical files named after the table. First of all, there must be .frm files that store table structure definition information that are indispensable to any storage engine, as well as .MYD and .MYI files, which store table data (.MYD) and index data (.MYI), respectively. Each table has and only three files are stored as tables of the MyISAM storage type, that is, no matter how many indexes the table has, they are stored in the same .MYI file.

3. MyISAM supports the following three types of indexes:

B-Tree index

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

R-Tree index

The storage mode of R-Tree index is different from that of b-tree index. It is mainly designed to index fields of storage space and multi-dimensional data, so the current MySQL version only supports fields of geometry type as indexes.

Full-text index

Full-text index is a full-text index, and its storage structure is also b-tree. The main purpose is to solve the inefficient problem that we need to use like query.

Of the above three index types of MyISAM, the most frequently used is the B-Tree index, occasionally using Fulltext, but the R-Tree index is rarely used in the general system. In addition, MyISAM's B-Tree index has a large limitation, that is, the sum of all the fields participating in an index cannot exceed 1000 bytes.

3. Transactions are not supported

4. Only table locks

5. The table can be damaged under the following circumstances:

Mysqld was dropped by kill while it was writing to the table

Host downtime (Crash)

Disk hardware failure

Bug of the MyISAM storage engine

6. Although each MyISAM table is stored in a .MYD file with the same suffix, the storage format of each file may not be exactly the same, because the data storage format of MyISAM is divided into three formats: static (FIXED) fixed length, dynamic (DYNAMIC) variable length and COMPRESSED. Of course, you can choose whether to compress or not in the three formats. You can specify {COMPRESSED | DEFAULT} through ROW_FORMAT when creating the table, or you can compress it through the myisampack tool. It is not compressed by default. In the case of non-compression, whether it is static or dynamic is related to the definition of the fields in our table. As long as there is a variable length type of field in the table, then the table must be in DYNAMIC format, if there are no variable length fields, then it is in FIXED format, of course, you can also use the alter table command to forcibly convert a DYNAMIC table with a field of VARCHAR type to FIXED, but the result is that the original VARCHAR field type will be automatically converted to the CHAR type. Conversely, if you convert FIXED to DYNAMIC, the CHAR type field will also be converted to VARCHAR type

Knowledge point expansion: how to estimate disk space based on the number of records in a table

First calculate how many bytes there are in a row in a table.

Then, according to how many rows of records are added to the table in the database, you can calculate how much hard disk space you need to add every day, so that you can estimate how much space is planned based on the amount of data.

For example, create a tb1 table in the database test

Execute mysql > desc test1.tb1 to view the table structure of tb1

10 bytes + 20 bytes + 2 bytes + 20 bytes + 8 bytes + 8 bytes + 100 bytes = 168 bytes

A row of the Tb1 table has 118 bytes

If you add 10000 records per day, you will need about 10000x168/1024/1024=1.6MB.

In this way, the disk space can be reasonably planned according to the increased number of records per day.

When an error occurs in a table file of the MyISAM storage engine, only that table is affected, not other tables, let alone other databases. If we find a problem with a MyISAM table while our database is running, we can try to verify it online through the check table command, and we can try to fix it with the repair table command. When the database is closed, we can also use the myisamchk tool to detect or repair a table (or some) in the database. However, it is strongly recommended that you do not easily repair the table until you have to, and try to do as much backup as possible before repair, so as not to bring unnecessary consequences.

Introduction to the Innodb storage engine:

The reason why Innodb is so popular is mainly due to its functional features:

1. Support transactions

The most important feature of Innodb is its support for transactions, which is undoubtedly a very important reason why Innodb has become one of the most popular storage engines in MySQL.

2. Improvement of locking mechanism.

Innodb changes the locking mechanism of MyISAM and implements row locking.

3. Implement foreign keys

Innodb implements the foreign key reference, an important feature of the database.

4. Innodb storage engine is also different from MyISAM. Although there are .frm files to store metadata related to table structure definition, table data and index data are stored together. It is entirely up to the user to decide whether each table is stored separately or all tables are stored together.

The physical structure of Innodb is divided into two parts:

1. Data files (table data and index data)

Stores the data in the data table and all index data, including primary keys and other general indexes. In Innodb, there is a concept of tablespace, but it is quite different from the tablespace of Oracle. First, there are two forms of tablespaces for Innodb. One is the shared tablespace, in which all table and index data are stored in the same tablespace (one or more data files). It is specified by innodb_data_file_path that the addition of data files requires downtime and restart. The other is exclusive tablespaces, where the data and indexes of each table are stored in a separate .ibd file.

Although we can set whether to use shared tablespaces or exclusive tablespaces to store our tables, shared tablespaces must exist because Innodb's undo information and other metadata information are stored in shared tablespaces. Data files for shared tablespaces can be set to both fixed size and auto-expandable size.

When our file tablespace is running out, we have to add data files to it, of course, only shared tablespaces do this. The operation of adding data files in shared tablespaces is relatively simple. You only need to set the file path and related attributes according to the standard format after the innodb_data_file_path parameters. However, it is important to note that Innodb will not create a directory when creating new data files. If the specified directory does not exist, it will report an error and cannot be started.

2. Log file

The log file of Innodb is similar to the redo log of Oracle. You can also set multiple log groups (at least 2

), also using a round-robin strategy to write sequentially.

Because Innodb is the storage engine of transactions, the system Crash (downtime) can not cause very serious losses to him. Due to the existence of redo logs (that is, transaction logs) and the protection of checkpoint mechanism, Innodb can completely recover the transactions in which the database Crash has been completed but has not yet had time to write data to disk through redo logs. You can also roll back all partially completed outstanding transactions that have been written to disk and restore the data.

Innodb is not only quite different from the MyISAM storage engine in terms of functional features, but also handled separately in configuration. In the MySQL startup parameters file (/ etc/my.cnf) settings, all parameters of Innodb are basically prefixed with "innodb_", whether innodb data is related to logs, or other performance, transaction, and other related parameters are the same. Like all Innodb-related system variables, all Innodb-related system state values are prefixed with "Innodb_".

The difference between MyISAM and InnoDB

1. MyISAM does not support transactions, but InnoDB does. The AUTOCOMMIT of InnoDB is turned on by default, that is, each SQL statement will be encapsulated as a transaction by default and commit automatically, which will affect the speed, so it is best to put multiple SQL statements between begin and commit to form a transaction to commit.

Mysql > use test_db

Mysql > show tables

Mysql > desc tb1

Mysql > begin

Mysql > insert into tb1 values ('lisi',1)

Mysql > insert into tb1 values ('zhangsan',2)

Mysql > commit

2. InnoDB supports row locking, while MyISAM does not support row locking, only locking the entire table. That is to say, the read lock and write lock on the same table of MyISAM are mutually exclusive. If there are both read and write requests in the queue for MyISAM concurrent read and write, the default write request has a high priority, even if the read request arrives first, so MyISAM is not suitable for the situation where a large number of queries and modifications coexist, so the query process will be blocked for a long time. Because MyISAM is a lock watch.

3. InnoDB supports foreign keys, but MyISAM does not.

4. InnoDB does not support full-text indexing, but MyISAM does.

Second, Mysqlslap performance testing MySQL two storage engines

Mysqlslap is mysql's own benchmark testing tool, advantages: query data, simple syntax, flexible and easy to use. This tool can simulate multiple clients to simultaneously send query updates to the cloud server, give performance test data and provide performance comparison of multiple engines. Msqlslap provides an intuitive verification basis for mysql performance before and after optimization. It is suggested that system operation and maintenance and DBA personnel should master some common stress testing tools in order to accurately grasp the upper limit of user traffic supported by online database and its pressure resistance.

Now take a look at the stress testing tool mysqlslap, which is described in detail in its option manual and-- help.

Here are some common options.

-- concurrency represents the number of concurrency, multiple of which can be separated by commas. For example: concurrency=50100200

-- engines represents the engine to be tested, and there can be multiple, separated by delimiters.

-- iterations represents how many times you want to run these tests, that is, how many times you run them, and get the results.

Auto-generate-sql stands for testing with a SQL script generated by the system itself.

-- auto-generate-sql-load-type stands for testing whether to read or write or a mix of both (read,write,update,mixed)

-- number-of-queries represents the total number of queries to run. The number of queries run by each customer can be calculated as the total number of queries / concurrency. For example, the penultimate result is 2, 200, 100.

-- debug-info represents additional output of CPU and memory related information (Note: only when MySQL is compiled with-- with-debug).

-- number-int-cols represents several properties of type INTEGER in the test table.

-- number-char-cols represents the number of char type fields in the test table.

-- create-schema represents a self-defined schema (in MySQL, that is, the database where the test is created).

-- query represents its own SQL script.

-- only-print can use this option if you just want to print to see what the SQL statement is.

-- csv=name production CSV format data file

View the default maximum number of connections to Mysql database

You can see that mysql5.7.13 defaults to 151. note: the default maximum number of connections varies from version to version. The general production environment is not enough. Add max_connections=1024 to 1024 under my.cnf [mysqld], and restart Mysql.

Modify the my.cnf file and restart the mysqld service

View the maximum number of connections after modification

Check that Mysql uses the storage engine by default, as shown below:

Mysql > show engines

Now let's look at an example of a specific test.

[root@localhost] # mysqlslap-defaults-file=/etc/my.cnf-- concurrency=100200-- iterations=1-- number-int-cols=20-- number-char-cols=30-- auto-generate-sql--auto-generate-sql-add-autoincrement-- auto-generate-sql-load-type=mixed-- engine=myisam,innodb-- number-of-queries=2000-uroot-p123456-verbose

Display the results:

Test description: simulation test twice read and write concurrently, the first time 100,200, automatically generate SQL script, test table contains 20 init fields, 30 char fields, each execution of 2000 query requests. The test engine is myisam,innodb.

The test results show that:

For the first time, the Myisam 100 client initiates the additional check with 0.413 Universe, and the second 200 client initiates the additional check with 0.509 Universe.

For the first time, the Innodb 100 client initiates the additional check with 0.692 Universe, and the second 200 client initiates the additional check with 0.617 Universe.

Thus it can be seen that the MyISAM storage engine has the best processing performance and is the most commonly used, but does not support transactions. The InonDB storage engine provides a transactional data engine (ACID), which is the most commonly used among transactional engines. It has the characteristics of transaction rollback and system repair.

The Mysqlslap testing tool produces data files in CSV format and converts them into diagrams:

[root@localhost] # mysqlslap-defaults-file=/etc/my.cnf-- concurrency=100200-- iterations=1-- number-int-cols=20-- number-char-cols=30-- auto-generate-sql--auto-generate-sql-add-autoincrement-- auto-generate-sql-load-type=mixed-- engine=myisam,innodb-- number-of-queries=2000-uroot-p123456-- csv=/root/a.csv

Execution result:

Copy a.csv to the windows host, open and generate the chart

Test with SQL scripts or statements that we define ourselves

First of all, prepare the database table to test, here we write a script to generate the table to complete

The script reads as follows:

[root@localhost ~] # cat / root/mysql3.sh

#! / bin/bash

HOSTNAME= "localhost"

PORT= "3306"

USERNAME= "root"

PASSWORD= "123.abc"

DBNAME= "test1"

TABLENAME= "tb1"

# create database

Mysql-h ${HOSTNAME}-P ${PORT}-u$ {USERNAME}-p$ {PASSWORD}-e "drop database if exists ${DBNAME}"

Create_db_sql= "create database ifnot exists ${DBNAME}"

Mysql-h ${HOSTNAME}-P ${PORT}-u$ {USERNAME}-p$ {PASSWORD}-e "${create_db_sql}"

# create table

Create_table_sql= "create table ifnot exists ${TABLENAME} (stuid int notnull primary key,stuname varchar (20) notnull,stusex char (1) notnull, cardid varchar (20) notnull, birthdaydatetime,entertime datetime,address varchar (100) default null)"

Mysql-h ${HOSTNAME}-P ${PORT}-u$ {USERNAME}-p$ {PASSWORD} ${DBNAME}-e "${create_table_sql}"

# insert data to table

I, 1

While [$I-le 20000]

Do

Insert_sql= "insert into$ {TABLENAME} values"

Mysql-h ${HOSTNAME}-P ${PORT}-u$ {USERNAME}-p$ {PASSWORD} ${DBNAME}-e "${insert_sql}"

Let iTunes +

Done

# select data

Select_sql= "select count (*) from$ {TABLENAME}"

Mysql-h ${HOSTNAME}-P ${PORT}-u$ {USERNAME}-p$ {PASSWORD} ${DBNAME}-e "${select_sql}"

Authorize script x to execute permissions

[root@localhost ~] # chmod + x/root/mysql3.sh

Execute the script mysql3.sh to generate the test tables required by the mysqlslap tool

[root@localhost ~] # / root/mysql3.sh

Execute the mysqlslap tool for testing

[root@localhost] # mysqlslap-defaults-file=/etc/my.cnf--concurrency=10,20-- iterations=1-- create-schema='test1'-- query='select * from test1.tb1'-- engine=myisam,innodb-- number-of-queries=2000-uroot-p123456-verbose

Display the results:

Mysqlslap: [Warning] Using a password onthe command line interface can be insecure.

Benchmark

Runningfor engine myisam

Averagenumber of seconds to run all queries: 14.128 seconds

Minimumnumber of seconds to run all queries: 14.128 seconds

Maximumnumber of seconds to run all queries: 14.128 seconds

Numberof clients running queries: 10

Averagenumber of queries per client: 200

Benchmark

Runningfor engine myisam

Averagenumber of seconds to run all queries: 13.646 seconds

Minimumnumber of seconds to run all queries: 13.646 seconds

Maximumnumber of seconds to run all queries: 13.646 seconds

Numberof clients running queries: 20

Averagenumber of queries per client: 100

Benchmark

Runningfor engine innodb

Averagenumber of seconds to run all queries: 13.837 seconds

Minimumnumber of seconds to run all queries: 13.837 seconds

Maximumnumber of seconds to run all queries: 13.837 seconds

Numberof clients running queries: 10

Averagenumber of queries per client: 200

Benchmark

Runningfor engine innodb

Averagenumber of seconds to run all queries: 13.768 seconds

Minimumnumber of seconds to run all queries: 13.768 seconds

Maximumnumber of seconds to run all queries: 13.768 seconds

Numberof clients running queries: 20

Averagenumber of queries per client: 100

Note: stress test of mysql server through mysqlslap tool, you can view the results of each test through the values of-- concurrency,-- number-of-queries and other options, and get the maximum number of concurrency of mysql server through repeated testing and optimization.

If the output of the mysqlslap tool is Segmentation fault (core dumped), it basically means that the load of the mysql server is exceeded.

What is the MySQL storage engine and its role will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.