We all know that in MySQL, MyISAM is faster than InnoDB query, but many people don't know the principle.

Today, let's talk about the principle and verify whether MyISAM is really faster than InnoDB.

Before exploring the principle, let's verify the query speed.

verification

Let's verify two questions:

1. Is MyISAM faster than InnoDB query through primary key query?

2. Is MyISAM faster than InnoDB query through non primary key field query?

Preparation before verification

Order table structure:

CREATE TABLE `trade` (
`id` INT(11) NULL DEFAULT NULL,
`trade_no` INT(11) NULL DEFAULT NULL,
UNIQUE INDEX `id` (`id`),
INDEX `trade_no` (`trade_no`)
)
COMMENT='order'
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;

Create trade tables for different engines_ myisam,trade_innodb and insert 100000 test data as follows:

 

Here, our fields are of Int type for our convenience. We use interval query to verify the query speed.

Verification ideas

1. Through id and trade_no query 10000 ~ 20000 records and obtain the query time.

2. Cycle query 100 times and obtain the average value of query time.

3. The performance is judged by comparing the average value of query time.

Let's verify the two questions at the beginning of the article:

1. Primary key query

C# realizes that the primary key query Id is in the range of 10000 ~ 20000, and the average value code of query time is as follows:

using (var conn = new MySqlConnection(connsql))
{
    conn.Open();
    //Query times
    var queryTime = 1000;

    //InnoDB data engine:
Primary key query
    //start time
    var startTime = DateTime.Now;
    for (var i = 0; i < queryTime; i++)
    {
        //insert
        var sql = "select * from trade_innodb where id>=10000 and id<20000";
        var sqlComm = new MySqlCommand();
        sqlComm.Connection = conn;
        sqlComm.CommandText = sql;
        sqlComm.ExecuteScalar();
        sqlComm.Dispose();
    }
    //Completion time
    var endTime = DateTime.Now;
    //time consuming
    var spanTime = (endTime - startTime) * 1.0 / queryTime;
    Console.WriteLine("InnoDB 1000 primary key queries, average time:
" + spanTime.Minutes + "branch" + spanTime.Seconds + "second" + spanTime.Milliseconds + "millisecond");
​
    //MyISAM data engine:
Primary key query
    //start time
    startTime = DateTime.Now;
    for (var i = 0; i < queryTime; i++)
    {
        //insert
        var sql = "select * from trade_myisam where id>=10000 and id<20000";
        var sqlComm = new MySqlCommand();
        sqlComm.Connection = conn;
        sqlComm.CommandText = sql;
        sqlComm.ExecuteScalar();
        sqlComm.Dispose();
    }
    //Completion time
    endTime = DateTime.Now;
    //time consuming
    spanTime = (endTime - startTime) * 1.0 / queryTime;
    Console.WriteLine("MyISAM 1000 primary key queries, average time:
" + spanTime.Minutes + "branch" + spanTime.Seconds + "second" + spanTime.Milliseconds + "millisecond");
​
    conn.Close();<br>}

Operation results:

From the above picture, you can see the result: MyISAM query performance is indeed faster than Innodb.

2. Non primary key query

C # implement fee primary key query trade_no is in the range of 10000 ~ 20000, and the average value code of query time is as follows:

using (var conn = new MySqlConnection(connsql))
{
    conn.Open();
    //Query times
    var queryTime = 1000;
​
    //InnoDB data engine:
Non primary key query
    //start time
    var startTime = DateTime.Now;
    for (var i = 0; i < queryTime; i++)
    {
        //insert
        var sql = "select * from trade_innodb where trade_no>=10000 and trade_no<20000";
        var sqlComm = new MySqlCommand();
        sqlComm.Connection = conn;
        sqlComm.CommandText = sql;
        sqlComm.ExecuteScalar();
        sqlComm.Dispose();
    }
    //Completion time
    var endTime = DateTime.Now;
    //time consuming
    var spanTime = (endTime - startTime) * 1.0 / queryTime;
    Console.WriteLine("InnoDB 1000 non primary key queries, average time consumption:
" + spanTime.Minutes + "branch" + spanTime.Seconds + "second" + spanTime.Milliseconds + "millisecond");
​
    //MyISAM data engine:
Non primary key query
    //start time
    startTime = DateTime.Now;
    for (var i = 0; i < queryTime; i++)
    {
        //insert
        var sql = "select * from trade_myisam where trade_no>=10000 and trade_no<20000";
        var sqlComm = new MySqlCommand();
        sqlComm.Connection = conn;
        sqlComm.CommandText = sql;
        sqlComm.ExecuteScalar();
        sqlComm.Dispose();
    }
    //Completion time
    endTime = DateTime.Now;
    //time consuming
    spanTime = (endTime - startTime) * 1.0 / queryTime;
    Console.WriteLine("MyISAM 1000 non primary key queries, average time consumption:
" + spanTime.Minutes + "branch" + spanTime.Seconds + "second" + spanTime.Milliseconds + "millisecond");
​
    conn.Close();
}

Operation results:

From the above picture, you can see the result: MyISAM query performance is indeed faster than Innodb.

Comparison results

Through our verification, whether it is a primary key or non primary key query, MyISAM query performance is indeed faster than InnoDB. The query speed is different. It must be the difference between MyISAM and InnoDB. Let's take a look at their differences.

Difference between MyISAM and InnoDB

Through the comparison of the above table, InnoDB needs to maintain more things than MYISAM engine when doing SELECT, which affects the query speed:

1) For data blocks, InnoDB needs to cache, and MyISAM only caches index blocks, which also reduces the number of exchange in and out;

2) InnoDB addressing should be mapped to blocks and then to lines. MyISAM records the OFFSET of the file directly, and the positioning is faster than InnoDB

3) InnoDB also needs to maintain MVCC consistency; Although your scene does not, it still needs to be checked and maintained

Mvcc (multi version concurrency control) multi version concurrency control.

Index differences:

MyISAM and InnoDB index storage methods are different (for index, please refer to detailed description of MySql index principle).

The InnoDB table is a clustered index of B+tree expanded according to the primary key. MyISAM is a non clustered index. There are two files in MyISAM storage, one is an index file and the other is a data file. The index in the index file points to the table data in the data file.

The clustered index is not a separate index type, but a storage method. The InnoDB clustered index actually saves the B+tree index and data rows in the same structure. When there is a clustered index, its index is actually placed in the leaf page.

Next, we can visually see the difference between the index structures of the two engines through two figures:

 

Maintain MVCC

Innodb engine also needs to implement MVCC, which also consumes certain performance. Innodb is realized by having two additional hidden values for each row of records. These two values include when the row of data is created and when the row of data is expired (or deleted).

However, InnoDB does not store the actual time when these events occur. On the contrary, it only stores the system version number when these events occur. This is a growing number as transactions are created. Each transaction records its own system version number at the beginning of the transaction. Each query must check whether the version number of each row of data is the same as that of the transaction.

last

Through our verification, whether it is a primary key or non primary key query, MyISAM query performance is indeed faster than Innodb. However, MyISAM does not support transactions and has a lot of trouble in implementing business.

About which data engine to choose in our development. Usually in our business system, the InnoDB data engine can fully meet our needs. The difference in query performance between the two engines is relatively small. It is absolutely unnecessary to bring us more workload for this performance. MyISAM engine is mainly used to store data warehouses, and only queries a few inserted business scenarios, such as system logs.