參考

Mysql官網(wǎng)解釋 5.6版本,存儲(chǔ)引擎的詳細(xì)介紹
https://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html

概述

Mysql支持常用的4種存儲(chǔ)引擎：Myisam、InnoDB(默認(rèn))、Memory、Merge

• Myisam：高效緩存 多讀少寫時(shí)讀取更快，B+樹索引
• InnoDB：支持事務(wù)、行級(jí)鎖、外鍵，B+樹索引
• Memory：基于Hash索引，內(nèi)存存儲(chǔ)
• Merge：分表時(shí)子表必須是Myisam

SHOW ENGINES;查看，Mysql支持多種存儲(chǔ)引擎。

MyISAM

1. 特點(diǎn)

• 在 heave-read的業(yè)務(wù)場(chǎng)景下，訪問速度快
• 主索引和輔助索引一樣，都是非聚簇索引，數(shù)據(jù)和索引分離，存儲(chǔ)的value值是真實(shí)數(shù)據(jù)的物理地址
• 默認(rèn)保存了表的總行數(shù)，select Count(*) 直接返回結(jié)果
• 表鎖，沒有行鎖，所以無(wú)法支持并發(fā)的寫，比如無(wú)法支持同時(shí)修改一張表中2個(gè)不同的數(shù)據(jù)
• Merge引擎分表時(shí)，子表必須是Myisam

2. 使用場(chǎng)景：多讀少寫，不需要事務(wù)、外鍵，也不容易服務(wù)器異常的場(chǎng)景

3. Myisam緩存機(jī)制
   每次通過索引讀取數(shù)據(jù)的時(shí)候，Mysql都會(huì)緩存 這個(gè) IndexPage 到內(nèi)存當(dāng)中，這個(gè)內(nèi)存塊的大小由 key_buffer_size（默認(rèn) 8M）決定，記錄了這個(gè)索引塊里面的所有數(shù)據(jù)對(duì)應(yīng)的物理地址信息，并且mysql有一個(gè)LRU隊(duì)列去管理這個(gè)緩存塊。
   包括修改的時(shí)候，也是直接修改這個(gè)緩存塊（同時(shí)會(huì)記錄日志），并且標(biāo)記這個(gè)page為dirty，當(dāng)從LRU隊(duì)列里面移除的時(shí)候，如果Dirty則需要寫回到硬盤上。
   因?yàn)榫彺娴闹皇撬饕臀锢泶鎯?chǔ)地址，而不緩存真正的數(shù)據(jù)，所以和Innodb相比，相同大小的緩存空間，Myisam可以緩存更多的索引。

When read, a MyISAM table's indexes can be read once from the .MYI file and loaded in the MyISAM Key Cache (as sized by key_buffer_size).

4. 5.7版本之后 Myisam不支持分區(qū)功能，只有InnoDB和NDB支持分區(qū)

InnoDB

• 支持事務(wù)：通過內(nèi)部MVCC機(jī)制實(shí)現(xiàn)
• 支持外鍵
• 支持行級(jí)鎖：行鎖是建立在索引的基礎(chǔ)之上的，行鎖鎖的是索引，不是數(shù)據(jù)，所以提高并發(fā)寫的能力要在查詢字段添加索引，否則用的還是表鎖
• 支持并發(fā)讀寫，因?yàn)樗饕樵兪切墟i
• 自動(dòng)增長(zhǎng)列
• 輔助索引是以非聚簇索引實(shí)現(xiàn)的，輔助索引的value存的是主鍵，所以主鍵越小越好，減小輔助索引樹的大小
• InnoDB is typically said to have better crash recovery,災(zāi)難恢復(fù)更穩(wěn)定

為什么Myisam比Innodb讀取更快？

結(jié)論：在多讀少寫的業(yè)務(wù)場(chǎng)景下，Myisam的read的速度快好幾倍。但是在有讀有寫或者多讀少寫的業(yè)務(wù)場(chǎng)景下，Myisam因?yàn)槭潜礞i會(huì)阻塞，讀和寫都慢。

1. 非聚簇索引一次lookup:在非主鍵索引的Query的業(yè)務(wù)場(chǎng)景下，Myisam只需要一次B+樹 key lookup，就可以讀取到data的物理地址，再一次IO讀取，就可以獲取到磁盤的數(shù)據(jù)信息，但是 InnoDB卻需要2次索引樹查找，這個(gè)都是不在一個(gè)物理page的查找，所以Myisam理論上在這一點(diǎn)設(shè)計(jì)上差不多快一倍。

2. Myisam沒有MVCC，在任意時(shí)間節(jié)點(diǎn)，一個(gè)數(shù)據(jù)就只有1個(gè)值，而Innodb就可能同時(shí)存在多個(gè)版本，加大了搜索篩選條件。

3. Myisam可以更好的利用緩存：因?yàn)樗饕蛿?shù)據(jù)是分離的，只緩存索引（索引值里面有key值和dataAddress），然后再一次IO讀取，就可以撈取到數(shù)據(jù)。
   但是Innodb因?yàn)閿?shù)據(jù)和索引是存在一起的，必須同時(shí)緩存相應(yīng)的索引和數(shù)據(jù)，所以相同緩存空間下，InnoDB可以緩存的數(shù)據(jù)量更少，緩存的命中率更低，但是一旦命中，則無(wú)需IO操作，可以直接返回?cái)?shù)據(jù)。

Myisam PK Innodb

1. Innodb優(yōu)勢(shì)： 事務(wù)、外鍵、行級(jí)鎖、并發(fā)讀寫、災(zāi)難恢復(fù)更靠譜
2. Myisam優(yōu)勢(shì)： 文本索引、count(*)存儲(chǔ)、多讀少寫時(shí)快速讀取和高效緩存、表數(shù)據(jù)可以拷貝遷移

數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)

數(shù)據(jù)存儲(chǔ)位置： 在 %datadir%/databaseName
默認(rèn)一個(gè)page的大小是16k

1. Myisam
   Myisam的存儲(chǔ)結(jié)構(gòu): 索引和數(shù)據(jù)是分開存儲(chǔ)的。

• student.frm ：存儲(chǔ)表結(jié)構(gòu)等相關(guān)信息
• student.MYI：存儲(chǔ)表的索引數(shù)據(jù)
• student.MYD：存儲(chǔ)表的具體數(shù)據(jù)

Myisam的存儲(chǔ)結(jié)構(gòu)比較獨(dú)立，可以通過直接拷貝這3個(gè)文件，來實(shí)現(xiàn)表數(shù)據(jù)的 跨庫(kù)遷移，甚至可以是跨操作系統(tǒng)的遷移

2. InnoDB
   InnoDB的存儲(chǔ)結(jié)構(gòu)：分為兩種模式（共享模式和 默認(rèn)的獨(dú)占模式 innodb_file_per_table）

• student.frm ：存儲(chǔ)表結(jié)構(gòu)等相關(guān)信息
• student.ibd ：存儲(chǔ)表全部的數(shù)據(jù)內(nèi)容和索引內(nèi)容

共享表空間以及獨(dú)占表空間都是針對(duì)數(shù)據(jù)的從物理意義上來講：

• 共享表空間: 會(huì)把表集中存儲(chǔ)在一個(gè)系統(tǒng)表空間里。即每一個(gè)數(shù)據(jù)庫(kù)的所有表的數(shù)據(jù)，索引文件全部放在一個(gè)文件中。該文件目錄默認(rèn)的是服務(wù)器的數(shù)據(jù)目錄。 默認(rèn)的文件名為:ibdata1 初始化為10M。
• 獨(dú)占表空間: 每一個(gè)表分別創(chuàng)建一個(gè)表空間，這時(shí)。在對(duì)應(yīng)的數(shù)據(jù)庫(kù)目錄里每一個(gè)表都有.ibd文件(這個(gè)文件包括了單獨(dú)一個(gè)表的數(shù)據(jù)內(nèi)容以及索引內(nèi)容)

memory

• 內(nèi)存存儲(chǔ)，mysql重啟數(shù)據(jù)消失
• 默認(rèn)索引Hash算法，速度快，但是范圍查找就慢，也可以通過指定 B+樹為索引
• 不支持事務(wù)、一般被redis memcache取代

merge

用來做分表用的，多個(gè)結(jié)構(gòu)相同的表，虛擬出一個(gè)merge表，可單獨(dú)取操作子表來實(shí)現(xiàn)高性能，通知也支持操作merge表來實(shí)現(xiàn)，邏輯上統(tǒng)一的數(shù)據(jù)。

注意事項(xiàng)

1. InnoDB只有在走索引查詢加鎖時(shí)才是行鎖，否則都是表鎖
   例如User表 主鍵 id，但是name字段沒有索引，事務(wù)A 通過非索引字段 name來查詢 huangzs的用戶，并且鎖定該數(shù)據(jù)（可能有多個(gè) huangzs用戶），因?yàn)閚ame字段沒有索引，所以是表鎖
   在沒有commit之前，開啟事務(wù)B，嘗試修改 name='huang'的操作是不能執(zhí)行的，因?yàn)楫?dāng)前表被鎖住了

TXA：
begin;
update user set memo ='hzs' where name='huangzs'

TXB:
begin;
update user set memo ='hzs' where name='huang'

行級(jí)鎖的正確使用:查詢條件是通過主鍵字段進(jìn)行行級(jí)鎖的，所以可以并發(fā)的修改數(shù)據(jù)

TXA：
begin;
update user set memo ='hzs' where id=500;

TXB:
begin;
update user set memo ='hzs' where id=501;

2. select的時(shí)候，只有 lock in share mode 或者 for update 才會(huì)鎖定數(shù)據(jù)，沒有限定詞的話，默認(rèn)是直接讀取的，不存在競(jìng)爭(zhēng)。也就是一行數(shù)據(jù)即使加鎖了，直接select也可以查的到

3. InnoDB死鎖了也不用怕，去倒杯水就好了：mysql 默認(rèn)有innodb_lock_wait_timeout：50s的設(shè)置，超過50s會(huì)自動(dòng)釋放鎖

如果你覺得對(duì)你有幫助的話，就給我點(diǎn)贊吧！