大家都知道在MySQL中，MyISAM比InnoDB查詢快，但很多人都不知道其中的原理。
今天我們就來聊聊其中的原理，另外也驗(yàn)證下是否MyISAM比InnoDB真的查詢快。
在探索其中原理之前，我們先驗(yàn)證下查詢速度。
驗(yàn)證
下面我們一起來驗(yàn)證兩個(gè)問題：
1、通過主鍵查詢，MyISAM是否比InnoDB查詢快？
2、通過非主鍵字段查詢，MyISAM是否比InnoDB查詢快？
驗(yàn)證前準(zhǔn)備
訂單表結(jié)構(gòu)：
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
;
?
分別創(chuàng)建不同引擎的表trade_myisam、trade_innodb，并插入10萬條測試數(shù)據(jù)，如下：
這里我們字段都采用Int類型，是為了方便我們，使用區(qū)間查詢，來驗(yàn)證查詢速度。
驗(yàn)證思路
1、分別通過id、trade_no查詢10000~20000區(qū)間的記錄，并獲得查詢所花費(fèi)時(shí)間。
2、循環(huán)查詢100次，并取得查詢耗費(fèi)時(shí)間平均值。
3、通過對比查詢耗費(fèi)時(shí)間平均值，來判斷性能。
下面我們一起來驗(yàn)證下，文章開頭的兩個(gè)問題：
1、主鍵查詢
C#實(shí)現(xiàn)主鍵查詢Id在10000~20000區(qū)間，查詢耗費(fèi)時(shí)間平均值代碼如下：
using (var conn = new MySqlConnection(connsql))
{
conn.Open();
//查詢次數(shù)
var queryTime = 1000;
?
//InnoDB數(shù)據(jù)引擎：主鍵查詢
//開始時(shí)間
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();
}
//完成時(shí)間
var endTime = DateTime.Now;
//耗時(shí)
var spanTime = (endTime - startTime) * 1.0 / queryTime;
Console.WriteLine("InnoDB主鍵查詢1000次平均耗時(shí)：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");
?
//MyISAM數(shù)據(jù)引擎：主鍵查詢
//開始時(shí)間
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();
}
//完成時(shí)間
endTime = DateTime.Now;
//耗時(shí)
spanTime = (endTime - startTime) * 1.0 / queryTime;
Console.WriteLine("MyISAM主鍵查詢1000次平均耗時(shí)：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");
?
conn.Close();
}
??
運(yùn)行結(jié)果：
通過上面的圖片，可以看到結(jié)果：MyISAM查詢性能確實(shí)比較Innodb快。
2、非主鍵查詢
C#實(shí)現(xiàn)費(fèi)主鍵查詢trade_no在10000~20000區(qū)間，查詢耗費(fèi)時(shí)間平均值代碼如下：
using (var conn = new MySqlConnection(connsql))
{
conn.Open();
//查詢次數(shù)
var queryTime = 1000;
?
//InnoDB數(shù)據(jù)引擎：非主鍵查詢
//開始時(shí)間
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();
}
//完成時(shí)間
var endTime = DateTime.Now;
//耗時(shí)
var spanTime = (endTime - startTime) * 1.0 / queryTime;
Console.WriteLine("InnoDB非主鍵查詢1000次平均耗時(shí)：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");
?
//MyISAM數(shù)據(jù)引擎：非主鍵查詢
//開始時(shí)間
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();
}
//完成時(shí)間
endTime = DateTime.Now;
//耗時(shí)
spanTime = (endTime - startTime) * 1.0 / queryTime;
Console.WriteLine("MyISAM非主鍵查詢1000次平均耗時(shí)：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");
?
conn.Close();
}
?
?
運(yùn)行結(jié)果：
?編輯
通過上面的圖片，可以看到結(jié)果：MyISAM查詢性能確實(shí)比較Innodb快。
對比結(jié)果
通過我們的驗(yàn)證來看，不管是主鍵還是非主鍵查詢，MyISAM查詢性能確實(shí)比較Innodb快。查詢速度不一樣，肯定是MyISAM與InnoDB有什么區(qū)別，下面我們一起來看看它們的區(qū)別。
MyISAM與InnoDB區(qū)別
?編輯
通過上面表格對比，InnoDB在做SELECT的時(shí)候，要維護(hù)的東西比MYISAM引擎多很多，影響查詢速度有：
1）數(shù)據(jù)塊，InnoDB要緩存，MyISAM只緩存索引塊， 這中間還有換進(jìn)換出的減少；
2）InnoDB尋址要映射到塊，再到行，MyISAM記錄的直接是文件的OFFSET，定位比InnoDB要快
3）InnoDB還需要維護(hù)MVCC一致；雖然你的場景沒有，但他還是需要去檢查和維護(hù)
MVCC (Multi-Version Concurrency Control)多版本并發(fā)控制 。
索引區(qū)別：
MyISAM與InnoDB索引存儲(chǔ)方式是不一樣的（關(guān)于索引請查看《細(xì)說MySql索引原理》）。
InnoDB表是根據(jù)主鍵進(jìn)行展開的 B+tree 的聚集索引。MyISAM則非聚集型索引，MyISAM存儲(chǔ)會(huì)有兩個(gè)文件，一個(gè)是索引文件，另外一個(gè)是數(shù)據(jù)文件，其中索引文件中的索引指向數(shù)據(jù)文件中的表數(shù)據(jù)。
聚集型索引并不是一種單獨(dú)的索引類型，而是一種存儲(chǔ)方式，InnoDB 聚集型索引實(shí)際上是在同一結(jié)構(gòu)中保存了 B+tree 索引和數(shù)據(jù)行。當(dāng)有聚簇索引時(shí)，它的索引實(shí)際放在葉子頁中。
下面我們通過2張圖，我們就能直觀看到這兩個(gè)引擎，索引結(jié)構(gòu)的區(qū)別：
維護(hù)MVCC
InnoDB引擎還有需要實(shí)現(xiàn)MVCC，這里面也是損耗一定的性能的。Innodb是通過每一行記錄有2個(gè)額外的隱藏值來實(shí)現(xiàn)，這兩個(gè)值一個(gè)記錄這行數(shù)據(jù)何時(shí)被創(chuàng)建，另外一個(gè)記錄這行數(shù)據(jù)何時(shí)過期（或者被刪除）。
但是InnoDB并不存儲(chǔ)這些事件發(fā)生時(shí)的實(shí)際時(shí)間，相反它只存儲(chǔ)這些事件發(fā)生時(shí)的系統(tǒng)版本號。這是一個(gè)隨著事務(wù)的創(chuàng)建而不斷增長的數(shù)字。每個(gè)事務(wù)在事務(wù)開始時(shí)會(huì)記錄它自己的系統(tǒng)版本號。每個(gè)查詢必須去檢查每行數(shù)據(jù)的版本號與事務(wù)的版本號是否相同。
最后
通過我們的驗(yàn)證來看，不管是主鍵還是非主鍵查詢，MyISAM查詢性能確實(shí)比較Innodb快。但是MyISAM不支持事務(wù)，在實(shí)現(xiàn)業(yè)務(wù)方面有很多麻煩的事情。
關(guān)于我們開發(fā)中，選擇哪個(gè)數(shù)據(jù)引擎的問題。平常在我們業(yè)務(wù)系統(tǒng)中，InnoDB數(shù)據(jù)引擎完全可以滿足我們的需求，這兩個(gè)引擎查詢性能差別還是比較小的，完全沒必要為了這點(diǎn)性能，給我們帶來更多的工作量。MyISAM引擎更多是用于存儲(chǔ)數(shù)據(jù)倉庫，只有查詢少量插入的業(yè)務(wù)場景中，比如系統(tǒng)日志。
?
