MySQL中MyISAM為什麼比InnoDB查詢快

chingho發表於2021-05-22
MySql

大家都知道在MySQL中，MyISAM比InnoDB查詢快，但很多人都不知道其中的原理。

今天我們就來聊聊其中的原理，另外也驗證下是否MyISAM比InnoDB真的查詢快。

在探索其中原理之前，我們先驗證下查詢速度。


驗證

下面我們一起來驗證兩個問題：

 

1、通過主鍵查詢，MyISAM是否比InnoDB查詢快？

2、通過非主鍵欄位查詢，MyISAM是否比InnoDB查詢快？


驗證前準備

訂單表結構： 

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='訂單'
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;

分別建立不同引擎的表trade_myisam、trade_innodb，並插入10萬條測試資料，如下：

 

MySQL中MyISAM為什麼比InnoDB查詢快 MySQL中MyISAM為什麼比InnoDB查詢快


這裡我們欄位都採用Int型別，是為了方便我們，使用區間查詢，來驗證查詢速度。


驗證思路

1、分別通過id、trade_no查詢10000~20000區間的記錄，並獲得查詢所花費時間。

2、迴圈查詢100次，並取得查詢耗費時間平均值。

3、通過對比查詢 耗費 時間平均值，來判斷效能。


下面我們一起來驗證下，文章開頭的兩個問題：

1、主鍵查詢

C#實現主鍵查詢Id在10000~20000區間，查詢耗費時間平均值程式碼如下： 

using (var conn = new MySqlConnection(connsql))
{
    conn.Open();
    //查詢次數
    var queryTime = 1000;
​
    //InnoDB資料引擎：
主鍵查詢
    //開始時間
    var startTime = DateTime.Now;
    for (var i = 0; i < queryTime; i++)
    {
        //插入
        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();
    }
    //完成時間
    var endTime = DateTime.Now;
    //耗時
    var spanTime = (endTime - startTime) * 1.0 / queryTime;
    Console.WriteLine("InnoDB主鍵查詢1000次平均耗時：
" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");
​
    //MyISAM資料引擎：
主鍵查詢
    //開始時間
    startTime = DateTime.Now;
    for (var i = 0; i < queryTime; i++)
    {
        //插入
        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();
    }
    //完成時間
    endTime = DateTime.Now;
    //耗時
    spanTime = (endTime - startTime) * 1.0 / queryTime;
    Console.WriteLine("MyISAM主鍵查詢1000次平均耗時：
" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");
​
    conn.Close();
}

執行結果：

MySQL中MyISAM為什麼比InnoDB查詢快

通過上面的圖片，可以看到結果： MyISAM查詢效能確實比較Innodb快。


2、非主鍵查詢

C#實現費主鍵查詢trade_no在10000~20000區間，查詢耗費時間平均值程式碼如下： 

using (var conn = new MySqlConnection(connsql))
{
    conn.Open();
    //查詢次數
    var queryTime = 1000;
​
    //InnoDB資料引擎：
非主鍵查詢
    //開始時間
    var startTime = DateTime.Now;
    for (var i = 0; i < queryTime; i++)
    {
        //插入
        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();
    }
    //完成時間
    var endTime = DateTime.Now;
    //耗時
    var spanTime = (endTime - startTime) * 1.0 / queryTime;
    Console.WriteLine("InnoDB非主鍵查詢1000次平均耗時：
" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");
​
    //MyISAM資料引擎：
非主鍵查詢
    //開始時間
    startTime = DateTime.Now;
    for (var i = 0; i < queryTime; i++)
    {
        //插入
        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();
    }
    //完成時間
    endTime = DateTime.Now;
    //耗時
    spanTime = (endTime - startTime) * 1.0 / queryTime;
    Console.WriteLine("MyISAM非主鍵查詢1000次平均耗時：
" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");
​
    conn.Close();
}

執行結果：

MySQL中MyISAM為什麼比InnoDB查詢快

通過上面的圖片，可以看到結果： MyISAM查詢效能確實比較Innodb快。


對比結果

通過我們的驗證來看，不管是主鍵還是非主鍵查詢，MyISAM查詢效能確實比較Innodb快。 查詢速度不一樣，肯定是MyISAM與InnoDB有什麼區別，下面我們一起來看看它們的區別。


MyISAM與InnoDB區別

 

MySQL中MyISAM為什麼比InnoDB查詢快

通過上面表格對比， InnoDB在做SELECT的時候，要維護的東西比MYISAM引擎多很多，影響查詢速度有：

 

1）資料塊，InnoDB要快取，MyISAM只快取索引塊，  這中間還有換進換出的減少；

2）InnoDB定址要對映到塊，再到行，MyISAM記錄的直接是檔案的OFFSET，定位比InnoDB要快

3）InnoDB還需要維護MVCC一致； 雖然你的場景沒有，但他還是需要去檢查和維護

MVCC (Multi-Version Concurrency Control)多版本併發控制 。


索引區別：

 

MyISAM與InnoDB索引儲存方式是不一樣的（關於索引請檢視《細說MySql索引原理》）。

 

InnoDB 的表是根據主鍵進行展開的 B+tree 的聚集索引。 MyISAM則非聚集型索引，MyISAM儲存會有兩個檔案，一個是索引檔案，另外一個是資料檔案，其中索引檔案中的索引指向資料檔案中的表資料。

 

聚集型索引並不是一種單獨的索引型別，而是一種儲存方式，InnoDB 聚集型索引實際上是在同一結構中儲存了 B+tree 索引和資料行。 當有聚簇索引時，它的索引實際放在葉子頁中。

 

下面我們通過2張圖，我們就能直觀看到這兩個引擎，索引結構的區別：

 

MySQL中MyISAM為什麼比InnoDB查詢快

MySQL中MyISAM為什麼比InnoDB查詢快

 

維護MVCC

InnoDB引擎還有需要實現MVCC，這裡面也是損耗一定的效能的。 Innodb是通過每一行記錄有2個額外的隱藏值來實現，這兩個值一個記錄這行資料何時被建立，另外一個記錄這行資料何時過期（或者被刪除）。

 

但是InnoDB並不儲存這些事件發生時的實際時間，相反它只儲存這些事件發生時的系統版本號。 這是一個隨著事務的建立而不斷增長的數字。 每個事務在事務開始時會記錄它自己的系統版本號。 每個查詢必須去檢查每行資料的版本號與事務的版本號是否相同。


最後

通過我們的驗證來看，不管是主鍵還是非主鍵查詢，MyISAM查詢效能確實比較Innodb快。 但是MyISAM不支援事務，在實現業務方面有很多麻煩的事情。

 

關於我們開發中，選擇哪個資料引擎的問題。 平常在我們業務系統中，InnoDB資料引擎完全可以滿足我們的需求，這兩個引擎查詢效能差別還是比較小的，完全沒必要為了這點效能，給我們帶來更多的工作量。 MyISAM引擎更多是用於儲存資料倉儲，只有查詢少量插入的業務場景中，比如系統日誌。

 

歡迎關注公眾號【程式設計樂趣】，瞭解更多MySQL知識。

 

相關文章