MySQL優(yōu)化筆記（五）--數(shù)據(jù)庫存儲(chǔ)引擎（主要分析對(duì)比InnoDB和MyISAM以及講述Mrg_Myisam分表）

學(xué)校里做的項(xiàng)目都是默認(rèn)MySQL的InnoDB存儲(chǔ)引擎，沒有思考過為什么使用，也沒思考過其優(yōu)缺和其他數(shù)據(jù)庫存儲(chǔ)引擎。直到我去實(shí)習(xí)了，在學(xué)習(xí)優(yōu)化的過程中，才發(fā)現(xiàn)自己很無知，知識(shí)很局限。為此做次彌補(bǔ)。

本系列：demo下載

（一）MySQL優(yōu)化筆記（一）--庫與表基本操作以及數(shù)據(jù)增刪改

（二）MySQL優(yōu)化筆記（二）--查找優(yōu)化（1）（非索引設(shè)計(jì)）

（三）MySQL優(yōu)化筆記（二）--查找優(yōu)化（2）（外連接、多表聯(lián)合查詢以及查詢注意點(diǎn)）

（四） MySQL優(yōu)化筆記（三）--索引的使用、原理和設(shè)計(jì)優(yōu)化

（五） MySQL優(yōu)化筆記（四）--表的設(shè)計(jì)與優(yōu)化（單表、多表）

（六）MySQL優(yōu)化筆記（五）--數(shù)據(jù)庫存儲(chǔ)引擎

（七）MySQL優(yōu)化筆記（六）--存儲(chǔ)過程和存儲(chǔ)函數(shù)

（八）MySQL優(yōu)化筆記（七）--視圖應(yīng)用詳解

（九） MySQL優(yōu)化筆記（八）--鎖機(jī)制超詳細(xì)解析（鎖分類、事務(wù)并發(fā)、引擎并發(fā)控制）

文章結(jié)構(gòu)：（1）存儲(chǔ)引擎概述；（2）MySQL各大存儲(chǔ)引擎；（3）InnoDB和MyIsam使用及其原理對(duì)比；（4）InnoDB和MyIsam引擎原理；（5）剩余引擎的使用DEMO（主要是Mrg_Myisam分表）；

文章目錄：（例子下載在最下方）

（1）存儲(chǔ)引擎概述

  • 為什么要合理選擇數(shù)據(jù)庫存儲(chǔ)引擎
  • 定義
  • 存儲(chǔ)引擎作用
  • 如何修改數(shù)據(jù)庫引擎
  • 怎么查看修改成功

（2）MySQL各大存儲(chǔ)引擎

  • InnoDB
    • 適用場景
    • MySQL官方對(duì)InnoDB的講解
  • MyIsam
    • 適用場景
    • 補(bǔ)充：ISAM索引方法--索引順序存取方法
  • Memory（也叫HEAP）堆內(nèi)存嘛
  • Mrg_Myisam：（分表的一種方式--水平分表）
  • Blackhole（黑洞引擎）

（3）InnoDB和MyIsam使用及其原理對(duì)比

  • 使用的效果與區(qū)別展示
  • 效果對(duì)比總述
  • 使用建議

（4）InnoDB和MyIsam引擎原理

  • MyIASM引擎的索引結(jié)構(gòu)
  • InnoDB引擎的索引結(jié)構(gòu)

（5）剩余引擎的使用DEMO（主要是Mrg_Myisam分表）

  • Mrg_Myisam引擎分表
  • 項(xiàng)目中如何使用MRG_MYISAM總表
  • MRG_MYISAM分表的優(yōu)點(diǎn)
  • 使用MRG_MyISAM分表的必須思考問題

一、存儲(chǔ)引擎的概述：

基于此博客MySQL數(shù)據(jù)庫引擎去擴(kuò)展，不過每個(gè)我都會(huì)用例子去驗(yàn)證，并寫出的見解。

（1）為什么要合理選擇數(shù)據(jù)庫存儲(chǔ)引擎：

MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲(chǔ)在文件（或者內(nèi)存）中。這些技術(shù)中的每一種技術(shù)都使用不同的存儲(chǔ)機(jī)制、索引技巧、鎖定水平并且最終提供廣泛的不同的功能和能力。通過選擇不同的技術(shù)，你能夠獲得額外的速度或者功能，從而改善你的應(yīng)用的整體功能。

這些不同的技術(shù)以及配套的相關(guān)功能在MySQL中被稱作存儲(chǔ)引擎(也稱作表類型)。MySQL默認(rèn)配置了許多不同的存儲(chǔ)引擎，可以預(yù)先設(shè)置或者在MySQL服務(wù)器中啟用。你可以選擇適用于服務(wù)器、數(shù)據(jù)庫和表格的存儲(chǔ)引擎，以便在選擇如何存儲(chǔ)你的信息、如何檢索這些信息以及你需要你的數(shù)據(jù)結(jié)合什么性能和功能的時(shí)候?yàn)槟闾峁┳畲蟮撵`活性。

（2）定義：

數(shù)據(jù)庫引擎是用于存儲(chǔ)、處理和保護(hù)數(shù)據(jù)的核心服務(wù)。利用數(shù)據(jù)庫引擎可控制訪問權(quán)限并快速處理事務(wù)，從而滿足企業(yè)內(nèi)大多數(shù)需要處理大量數(shù)據(jù)的應(yīng)用程序的要求。 使用數(shù)據(jù)庫引擎創(chuàng)建用于聯(lián)機(jī)事務(wù)處理或聯(lián)機(jī)分析處理數(shù)據(jù)的關(guān)系數(shù)據(jù)庫。這包括創(chuàng)建用于存儲(chǔ)數(shù)據(jù)的表和用于查看、管理和保護(hù)數(shù)據(jù)安全的數(shù)據(jù)庫對(duì)象（如索引、視圖和存儲(chǔ)過程）。

（3）存儲(chǔ)引擎作用：

1）設(shè)計(jì)并創(chuàng)建數(shù)據(jù)庫以保存系統(tǒng)所需的關(guān)系或XML文檔。

2）實(shí)現(xiàn)系統(tǒng)以訪問和更改數(shù)據(jù)庫中存儲(chǔ)的數(shù)據(jù)。包括實(shí)現(xiàn)網(wǎng)站或使用數(shù)據(jù)的應(yīng)用程序，還包括生成使用SQL Server工具和實(shí)用工具以使用數(shù)據(jù)的過程。

3）為單位或客戶部署實(shí)現(xiàn)的系統(tǒng)。

4）提供日常管理支持以優(yōu)化數(shù)據(jù)庫的性能。

（4）如何修改數(shù)據(jù)庫引擎：

方式一：

修改配置文件my.ini

將mysql.ini另存為my.ini，在[mysqld]后面添加default-storage-engine=InnoDB，重啟服務(wù)，數(shù)據(jù)庫默認(rèn)的引擎修改為InnoDB

方式二：

在建表的時(shí)候指定

create table mytbl(   
    id int primary key,   
    name varchar(50)   
)type=MyISAM;

方式三：

建表后更改

alter table table_name type = InnoDB;

（5）怎么查看修改成功？

方式一：

show table status from table_name;

方式二：

show create table table_name

方式三：

使用數(shù)據(jù)庫管理工具啊。

二、MySQL各大存儲(chǔ)引擎：

最好先看下你下的MySQL支持什么數(shù)據(jù)庫引擎

這里寫圖片描述

存儲(chǔ)引擎主要有： 1. MyIsam , 2. InnoDB, 3. Memory, 4. Blackhole, 5. CSV, 6. Performance_Schema, 7. Archive, 8. Federated , 9 Mrg_Myisam

但是我們主要分析使用MyIsam 和InnoDB。其余略微帶過，詳情請分別百度。

（1）InnoDB：

定義：（默認(rèn)的存儲(chǔ)引擎）

InnoDB是一個(gè)事務(wù)型的存儲(chǔ)引擎，有行級(jí)鎖定和外鍵約束。

Innodb引擎提供了對(duì)數(shù)據(jù)庫ACID事務(wù)的支持，并且實(shí)現(xiàn)了SQL標(biāo)準(zhǔn)的四種隔離級(jí)別，關(guān)于數(shù)據(jù)庫事務(wù)與其隔離級(jí)別的內(nèi)容請見數(shù)據(jù)庫事務(wù)與其隔離級(jí)別這類型的文章。該引擎還提供了行級(jí)鎖和外鍵約束，它的設(shè)計(jì)目標(biāo)是處理大容量數(shù)據(jù)庫系統(tǒng)，它本身其實(shí)就是基于MySQL后臺(tái)的完整數(shù)據(jù)庫系統(tǒng)，MySQL運(yùn)行時(shí)Innodb會(huì)在內(nèi)存中建立緩沖池，用于緩沖數(shù)據(jù)和索引。但是該引擎不支持FULLTEXT類型的索引，而且它沒有保存表的行數(shù)，當(dāng)SELECT COUNT(*) FROM TABLE時(shí)需要掃描全表。當(dāng)需要使用數(shù)據(jù)庫事務(wù)時(shí)，該引擎當(dāng)然是首選。由于鎖的粒度更小，寫操作不會(huì)鎖定全表，所以在并發(fā)較高時(shí)，使用Innodb引擎會(huì)提升效率。但是使用行級(jí)鎖也不是絕對(duì)的，如果在執(zhí)行一個(gè)SQL語句時(shí)MySQL不能確定要掃描的范圍，InnoDB表同樣會(huì)鎖全表。

//這個(gè)就是select鎖表的一種，不明確主鍵。增刪改查都可能會(huì)導(dǎo)致鎖全表，在以后我們會(huì)詳細(xì)列出。
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

適用場景：

1）經(jīng)常更新的表，適合處理多重并發(fā)的更新請求。

2）支持事務(wù)。

3）可以從災(zāi)難中恢復(fù)（通過bin-log日志等）。

4）外鍵約束。只有他支持外鍵。

5）支持自動(dòng)增加列屬性auto_increment。

MySQL官方對(duì)InnoDB的講解：

1）InnoDB給MySQL提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全（ACID兼容）存儲(chǔ)引擎。

2）InnoDB鎖定在行級(jí)并且也在SELECT語句提供一個(gè)Oracle風(fēng)格一致的非鎖定讀，這些特色增加了多用戶部署和性能。沒有在InnoDB中擴(kuò)大鎖定的需要，因?yàn)樵贗nnoDB中行級(jí)鎖定適合非常小的空間。

3）InnoDB也支持FOREIGN KEY強(qiáng)制。在SQL查詢中，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來，甚至在同一個(gè)查詢中也可以混合。

4）InnoDB是為處理巨大數(shù)據(jù)量時(shí)的最大性能設(shè)計(jì)，它的CPU效率可能是任何其它基于磁盤的關(guān)系數(shù)據(jù)庫引擎所不能匹敵的。

5） InnoDB被用來在眾多需要高性能的大型數(shù)據(jù)庫站點(diǎn)上產(chǎn)生。

補(bǔ)充：什么叫事務(wù)？簡稱ACID

A 事務(wù)的原子性(Atomicity)：指一個(gè)事務(wù)要么全部執(zhí)行,要么不執(zhí)行.也就是說一個(gè)事務(wù)不可能只執(zhí)行了一半就停止了.比如你從取款機(jī)取錢,這個(gè)事務(wù)可以分成兩個(gè)步驟:1劃卡,2出錢.不可能劃了卡,而錢卻沒出來.這兩步必須同時(shí)完成.要么就不完成.

C 事務(wù)的一致性(Consistency)：指事務(wù)的運(yùn)行并不改變數(shù)據(jù)庫中數(shù)據(jù)的一致性.例如,完整性約束了a+b=10,一個(gè)事務(wù)改變了a,那么b也應(yīng)該隨之改變.

I 獨(dú)立性(Isolation）:事務(wù)的獨(dú)立性也有稱作隔離性,是指兩個(gè)以上的事務(wù)不會(huì)出現(xiàn)交錯(cuò)執(zhí)行的狀態(tài).因?yàn)檫@樣可能會(huì)導(dǎo)致數(shù)據(jù)不一致.

D 持久性(Durability）:事務(wù)的持久性是指事務(wù)執(zhí)行成功以后,該事務(wù)所對(duì)數(shù)據(jù)庫所作的更改便是持久的保存在數(shù)據(jù)庫之中，不會(huì)無緣無故的回滾.

（2）MyIsam：

定義：

MyIASM是MySQL默認(rèn)的引擎，但是它沒有提供對(duì)數(shù)據(jù)庫事務(wù)的支持，也不支持行級(jí)鎖和外鍵，因此當(dāng)INSERT(插入)或UPDATE(更新)數(shù)據(jù)時(shí)即寫操作需要鎖定整個(gè)表，效率便會(huì)低一些。

MyIsam 存儲(chǔ)引擎獨(dú)立于操作系統(tǒng)，也就是可以在windows上使用，也可以比較簡單的將數(shù)據(jù)轉(zhuǎn)移到linux操作系統(tǒng)上去。

意味著：引擎在創(chuàng)建表的時(shí)候，會(huì)創(chuàng)建三個(gè)文件，一個(gè)是.frm文件用于存儲(chǔ)表的定義，一個(gè)是.MYD文件用于存儲(chǔ)表的數(shù)據(jù)，另一個(gè)是.MYI文件，存儲(chǔ)的是索引。操作系統(tǒng)對(duì)大文件的操作是比較慢的，這樣將表分為三個(gè)文件，那么.MYD這個(gè)文件單獨(dú)來存放數(shù)據(jù)自然可以優(yōu)化數(shù)據(jù)庫的查詢等操作。有索引管理和字段管理。MyISAM還使用一種表格鎖定的機(jī)制，來優(yōu)化多個(gè)并發(fā)的讀寫操作，其代價(jià)是你需要經(jīng)常運(yùn)行OPTIMIZE TABLE命令，來恢復(fù)被更新機(jī)制所浪費(fèi)的空間。

適用場景：

1）不支持事務(wù)的設(shè)計(jì)，但是并不代表著有事務(wù)操作的項(xiàng)目不能用MyIsam存儲(chǔ)引擎，可以在service層進(jìn)行根據(jù)自己的業(yè)務(wù)需求進(jìn)行相應(yīng)的控制。

2）不支持外鍵的表設(shè)計(jì)。

3）查詢速度很快，如果數(shù)據(jù)庫insert和update的操作比較多的話比較適用。

4）整天 對(duì)表進(jìn)行加鎖的場景。

5）MyISAM極度強(qiáng)調(diào)快速讀取操作。

6）MyIASM中存儲(chǔ)了表的行數(shù)，于是SELECT COUNT(*) FROM TABLE時(shí)只需要直接讀取已經(jīng)保存好的值而不需要進(jìn)行全表掃描。如果表的讀操作遠(yuǎn)遠(yuǎn)多于寫操作且不需要數(shù)據(jù)庫事務(wù)的支持，那么MyIASM也是很好的選擇。

缺點(diǎn)：

就是不能在表損壞后恢復(fù)數(shù)據(jù)。（是不能主動(dòng)恢復(fù)）

補(bǔ)充：ISAM索引方法--索引順序存取方法

定義：

是一個(gè)定義明確且歷經(jīng)時(shí)間考驗(yàn)的數(shù)據(jù)表格管理方法，它在設(shè)計(jì)之時(shí)就考慮到 數(shù)據(jù)庫被查詢的次數(shù)要遠(yuǎn)大于更新的次數(shù)。

特性：

ISAM執(zhí)行讀取操作的速度很快，而且不占用大量的內(nèi)存和存儲(chǔ)資源。

在設(shè)計(jì)之初就預(yù)想數(shù)據(jù)組織成有固定長度的記錄，按順序存儲(chǔ)的。---ISAM是一種靜態(tài)索引結(jié)構(gòu)。

缺點(diǎn)：

1.它不 支持事務(wù)處理

2.也不能夠容錯(cuò)。如果你的硬盤崩潰了，那么數(shù)據(jù)文件就無法恢復(fù)了。如果你正在把ISAM用在關(guān)鍵任務(wù)應(yīng)用程序里，那就必須經(jīng)常備份你所有的實(shí) 時(shí)數(shù)據(jù)，通過其復(fù)制特性，MYSQL能夠支持這樣的備份應(yīng)用程序。

（3）Memory（也叫HEAP）堆內(nèi)存嘛：

定義：

使用存在內(nèi)存中的內(nèi)容來創(chuàng)建表。每個(gè)MEMORY表只實(shí)際對(duì)應(yīng)一個(gè)磁盤文件。MEMORY類型的表訪問非常得快，因?yàn)樗臄?shù)據(jù)是放在內(nèi)存中的，并且默認(rèn)使用HASH索引。

但是一旦服務(wù)關(guān)閉，表中的數(shù)據(jù)就會(huì)丟失掉。 HEAP允許只駐留在內(nèi)存里的臨時(shí)表格。駐留在內(nèi)存里讓HEAP要比ISAM和MYISAM都快，但是它所管理的數(shù)據(jù)是不穩(wěn)定的，而且如果在關(guān)機(jī)之前沒有進(jìn)行保存，那么所有的數(shù)據(jù)都會(huì)丟失。在數(shù)據(jù)行被刪除的時(shí)候，HEAP也不會(huì)浪費(fèi)大量的空間。HEAP表格在你需要使用SELECT表達(dá)式來選擇和操控?cái)?shù)據(jù)的時(shí)候非常有用。

適用場景：

1）那些內(nèi)容變化不頻繁的代碼表，或者作為統(tǒng)計(jì)操作的中間結(jié)果表，便于高效地堆中間結(jié)果進(jìn)行分析并得到最終的統(tǒng)計(jì)結(jié)果。

2）目標(biāo)數(shù)據(jù)比較小，而且非常頻繁的進(jìn)行訪問，在內(nèi)存中存放數(shù)據(jù)，如果太大的數(shù)據(jù)會(huì)造成內(nèi)存溢出?？梢酝ㄟ^參數(shù)max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小。

3）數(shù)據(jù)是臨時(shí)的，而且必須立即可用得到，那么就可以放在內(nèi)存中。

4）存儲(chǔ)在Memory表中的數(shù)據(jù)如果突然間丟失的話也沒有太大的關(guān)系。

注意： Memory同時(shí)支持散列索引和B樹索引，B樹索引可以使用部分查詢和通配查詢，也可以使用<,>和>=等操作符方便數(shù)據(jù)挖掘，散列索引相等的比較快但是對(duì)于范圍的比較慢很多。

特性要求：

1）要求存儲(chǔ)的數(shù)據(jù)是數(shù)據(jù)長度不變的格式，比如，Blob和Text類型的數(shù)據(jù)不可用（長度不固定的）。

2）要記住，在用完表格之后就刪除表格。

（4）Mrg_Myisam：（分表的一種方式--水平分表）

定義：

是一個(gè)相同的可以被當(dāng)作一個(gè)來用的MyISAM表的集合?！跋嗤币馕吨斜硗瑯拥牧泻退饕畔ⅰ?/h4>

也就是說，他將MyIsam引擎的多個(gè)表聚合起來，但是他的內(nèi)部沒有數(shù)據(jù)，真正的數(shù)據(jù)依然是MyIsam引擎的表中，但是可以直接進(jìn)行查詢、刪除更新等操作。

比如：我們可能會(huì)遇到這樣的問題，同一種類的數(shù)據(jù)會(huì)根據(jù)數(shù)據(jù)的時(shí)間分為多個(gè)表，如果這時(shí)候進(jìn)行查詢的話，就會(huì)比較麻煩，Merge可以直接將多個(gè)表聚合成一個(gè)表統(tǒng)一查詢，然后再刪除Merge表（刪除的是定義），原來的數(shù)據(jù)不會(huì)影響。

（5）Blackhole（黑洞引擎）

定義

任何寫入到此引擎的數(shù)據(jù)均會(huì)被丟棄掉， 不做實(shí)際存儲(chǔ)；Select語句的內(nèi)容永遠(yuǎn)是空。

他會(huì)丟棄所有的插入的數(shù)據(jù)，服務(wù)器會(huì)記錄下Blackhole表的日志，所以可以用于復(fù)制數(shù)據(jù)到備份數(shù)據(jù)庫。

使用場景：

1）驗(yàn)證dump file語法的正確性

2）以使用blackhole引擎來檢測binlog功能所需要的額外負(fù)載

3）充當(dāng)日志服務(wù)器

其余引擎，大家感興趣就各自先百度吧。本文主要是對(duì)比引擎使用以及其原理。

三、InnoDB和MyIsam使用及其原理對(duì)比：

（1）使用的效果與區(qū)別展示：

MySQL數(shù)據(jù)庫實(shí)戰(zhàn)例子（存儲(chǔ)引擎、視圖、鎖機(jī)制、分表）

（一）在一個(gè)普通數(shù)據(jù)庫中創(chuàng)建兩張分別以MyIsam和InnoDB作為存儲(chǔ)引擎的表。

create table testMyIsam(  
id int unsigned primary key auto_increment,  
name varchar(20) not null  
)engine=myisam;  

 create table testInnoDB( 
 id int unsigned primary key auto_increment, 
 name varchar(20) not null 
 )engine=innodb;

嘿嘿嘿，，效果如圖，一會(huì)總結(jié)。

這里寫圖片描述

（二）對(duì)比插入效率（百萬級(jí)插入）：（雖然速度上MyISAM快，但是增刪改是涉及事務(wù)安全的，所以用InnoDB相對(duì)好很多）

為了更好地對(duì)比，我們可以使用函數(shù)的方式或者存儲(chǔ)過程的方式。博主采用存儲(chǔ)過程。（存儲(chǔ)過程在往后的章節(jié)會(huì)講到）

//創(chuàng)建存儲(chǔ)過程
delimiter $$
drop procedure if exists ptestmyisam;
create procedure ptestmyisam()
begin
declare pid int ;
set pid = 1000000;
while pid>0 
do
insert into testmyisam(name) values(concat("fuzhu", pid));
set pid = pid-1;
end while;
end $$

//使用存儲(chǔ)過程：
call ptestmyisam();
這里寫圖片描述
//創(chuàng)建存儲(chǔ)過程（盡量把Innodb的數(shù)量級(jí)壓低，不然，，卡在那里半天也不奇怪）
delimiter $$
drop procedure if exists ptestInndb;
create procedure ptestInndb()
begin
declare pid int ;
set pid = 1000000;
while pid>0 
do
insert into testinnodb(name) values(concat("fuzhu", pid));
set pid = pid-1;
end while;
end $$

//使用存儲(chǔ)過程：
call ptestInndb();

本博主在幾次innodb測試百萬插入的時(shí)候，數(shù)據(jù)庫炸了（笑哭）。最終只成功插入1W條?？梢娦蕦?duì)比。

當(dāng)然innodb默認(rèn)是開啟事務(wù)的，如果我們把事務(wù)給停了，會(huì)快很多。

//停掉事務(wù)
set autocommit = 0;  
//調(diào)用存儲(chǔ)過程
call ptestInndb; 
//重啟事務(wù)
set autocommit = 1;
這里寫圖片描述

（三）對(duì)比更新：（雖然速度上MyISAM快，但是增刪改是涉及事務(wù)安全的，所以InnoDB相對(duì)好很多）

//耗時(shí)3秒多
update testinnodb set name = 'fuzhu' where id>0 and id<10000;
//耗時(shí)0.171秒
update testmyisam set name = 'fuzhu' where id>0 and id<13525;

（四）查詢對(duì)比：

1）查詢總數(shù)目

select count(*) from testInnoDB;  

select count(*) from testMyIsam;

這就是innodb查一萬跟myisam查一百萬的區(qū)別？？效果對(duì)比立刻出現(xiàn)。

這里寫圖片描述

2）查詢無索引的列：（這些都自己拿我給出或者自己寫的數(shù)據(jù)庫去體驗(yàn)下吧，，差距很明顯）

select * from testMyIsam where name > "fuzhu100" ;

select * from testInnoDB where name > "fuzhu100" ;

3）查詢有索引的列：

select * from testMyIsam where  id > 10 ;
select * from testinnodb where  id > 10 ;

4）存儲(chǔ)大?。?/h4>

testMyIsam 存了一百萬。testinnodb 存了兩萬。

這里寫圖片描述

（2）效果對(duì)比總述：

1）事務(wù)。MyISAM類型不支持事務(wù)處理等高級(jí)處理，而InnoDB類型支持，提供事務(wù)支持已經(jīng)外部鍵等高級(jí)數(shù)據(jù)庫功能。

InnoDB表的行鎖也不是絕對(duì)的，假如在執(zhí)行一個(gè)SQL語句時(shí)MySQL不能確定要掃描的范圍，InnoDB表同樣會(huì)鎖全表，例如updatetable set num=1 where name like “a%”

就是說在不確定的范圍時(shí)，InnoDB還是會(huì)鎖表的。

2）性能主題。MyISAM類型的表強(qiáng)調(diào)的是性能，其執(zhí)行數(shù)度比InnoDB類型更快。

3）行數(shù)保存。InnoDB 中不保存表的具體行數(shù)，也就是說，執(zhí)行select count() fromtable時(shí)，InnoDB要掃描一遍整個(gè)表來計(jì)算有多少行，但是MyISAM只要簡單的讀出保存好的行數(shù)即可。注意的是，當(dāng)count()語句包含where條件時(shí)，兩種表的操作是一樣的。

4）索引存儲(chǔ)。對(duì)于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。

MyISAM支持全文索引（FULLTEXT）、壓縮索引，InnoDB不支持

MyISAM的索引和數(shù)據(jù)是分開的，并且索引是有壓縮的，內(nèi)存使用率就對(duì)應(yīng)提高了不少。能加載更多索引，而Innodb是索引和數(shù)據(jù)是緊密捆綁的，沒有使用壓縮從而會(huì)造成Innodb比MyISAM體積龐大不小。
InnoDB存儲(chǔ)引擎被完全與MySQL服務(wù)器整合，InnoDB存儲(chǔ)引擎為在主內(nèi)存中緩存數(shù)據(jù)和索引而維持它自己的緩沖池。InnoDB存儲(chǔ)它的表＆索引在一個(gè)表空間中，表空間可以包含數(shù)個(gè)文件（或原始磁盤分區(qū)）。這與MyISAM表不同，比如在MyISAM表中每個(gè)表被存在分離的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統(tǒng)上。

5）服務(wù)器數(shù)據(jù)備份。InnoDB必須導(dǎo)出SQL來備份，LOAD TABLE FROM MASTER操作對(duì)InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導(dǎo)入數(shù)據(jù)后再改成InnoDB表，但是對(duì)于使用的額外的InnoDB特性(例如外鍵)的表不適用。

而且MyISAM應(yīng)對(duì)錯(cuò)誤編碼導(dǎo)致的數(shù)據(jù)恢復(fù)速度快。MyISAM的數(shù)據(jù)是以文件的形式存儲(chǔ)，所以在跨平臺(tái)的數(shù)據(jù)轉(zhuǎn)移中會(huì)很方便。在備份和恢復(fù)時(shí)可單獨(dú)針對(duì)某個(gè)表進(jìn)行操作。

InnoDB是拷貝數(shù)據(jù)文件、備份 binlog，或者用 mysqldump，在數(shù)據(jù)量達(dá)到幾十G的時(shí)候就相對(duì)痛苦了。

6）鎖的支持。MyISAM只支持表鎖。InnoDB支持表鎖、行鎖 行鎖大幅度提高了多用戶并發(fā)操作的新能。但是InnoDB的行鎖，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會(huì)鎖全表的

//用于把表的拷貝從主服務(wù)器轉(zhuǎn)移到從屬服務(wù)器。
LOAD TABLE tbl_name FROM MASTER

（3）使用建議：

以下兩點(diǎn)必須使用 InnoDB：

1）可靠性高或者要求事務(wù)處理，則使用InnoDB。這個(gè)是必須的。

2）表更新和查詢都相當(dāng)?shù)念l繁，并且表鎖定的機(jī)會(huì)比較大的情況指定InnoDB數(shù)據(jù)引擎的創(chuàng)建。

對(duì)比之下，MyISAM的使用場景：

1）做很多count的計(jì)算的。如一些日志，調(diào)查的業(yè)務(wù)表。

2）插入修改不頻繁，查詢非常頻繁的。

MySQL能夠允許你在表這一層應(yīng)用數(shù)據(jù)庫引擎，所以你可以只對(duì)需要事務(wù)處理的表格來進(jìn)行性能優(yōu)化，而把不需要事務(wù)處理的表格交給更加輕便的MyISAM引擎。對(duì)于 MySQL而言，靈活性才是關(guān)鍵。

四、InnoDB和MyIsam引擎原理：

在此之前，先去理解什么是聚簇和非聚簇索引。

（1）MyIASM引擎的索引結(jié)構(gòu)：

MyISAM索引結(jié)構(gòu): MyISAM索引用的B+ tree來儲(chǔ)存數(shù)據(jù)，MyISAM索引的指針指向的是鍵值的地址，地址存儲(chǔ)的是數(shù)據(jù)。

B+Tree的數(shù)據(jù)域存儲(chǔ)的內(nèi)容為實(shí)際數(shù)據(jù)的地址，也就是說它的索引和實(shí)際的數(shù)據(jù)是分開的，只不過是用索引指向了實(shí)際的數(shù)據(jù)，這種索引就是所謂的非聚集索引。

圖取自博客

這里寫圖片描述

因此，過程為： MyISAM中索引檢索的算法為首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，則取出其data域的值，然后以data域的值為地址，根據(jù)data域的值去讀取相應(yīng)數(shù)據(jù)記錄。

（2）InnoDB引擎的索引結(jié)構(gòu)：

也是B+Treee索引結(jié)構(gòu)。Innodb的索引文件本身就是數(shù)據(jù)文件，即B+Tree的數(shù)據(jù)域存儲(chǔ)的就是實(shí)際的數(shù)據(jù)，這種索引就是聚集索引。這個(gè)索引的key就是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引。

InnoDB的輔助索引數(shù)據(jù)域存儲(chǔ)的也是相應(yīng)記錄主鍵的值而不是地址，所以當(dāng)以輔助索引查找時(shí)，會(huì)先根據(jù)輔助索引找到主鍵，再根據(jù)主鍵索引找到實(shí)際的數(shù)據(jù)。所以Innodb不建議使用過長的主鍵，否則會(huì)使輔助索引變得過大。

建議使用自增的字段作為主鍵，這樣B+Tree的每一個(gè)結(jié)點(diǎn)都會(huì)被順序的填滿，而不會(huì)頻繁的分裂調(diào)整，會(huì)有效的提升插入數(shù)據(jù)的效率。

這里寫圖片描述

上圖，可以看到葉節(jié)點(diǎn)包含了完整的數(shù)據(jù)記錄。這種索引叫做聚集索引。因?yàn)镮nnoDB的數(shù)據(jù)文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統(tǒng)會(huì)自動(dòng)選擇一個(gè)可以唯一標(biāo)識(shí)數(shù)據(jù)記錄的列作為主鍵，如果不存在這種列，則MySQL自動(dòng)為InnoDB表生成一個(gè)隱含字段作為主鍵，這個(gè)字段長度為6個(gè)字節(jié)，類型為長整形。

而且，與MyISAM索引的不同是InnoDB的輔助索引data域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。

因此，過程為：將主鍵組織到一棵B+樹中，而行數(shù)據(jù)就儲(chǔ)存在葉子節(jié)點(diǎn)上，若使用”where id = 13”這樣的條件查找主鍵，則按照B+樹的檢索算法即可查找到對(duì)應(yīng)的葉節(jié)點(diǎn)，之后獲得行數(shù)據(jù)。若對(duì)Name列進(jìn)行條件搜索，則需要兩個(gè)步驟：第一步在輔助索引B+樹中檢索Name，到達(dá)其葉子節(jié)點(diǎn)獲取對(duì)應(yīng)的主鍵。第二步使用主鍵在主索引B+樹種再執(zhí)行一次B+樹檢索操作，最終到達(dá)葉子節(jié)點(diǎn)即可獲取整行數(shù)據(jù)。

五、剩余引擎的使用DEMO（主要是Mrg_Myisam分表）

Memory（Heap）：

CREATE TABLE tbHeap (
id int unsigned primary key auto_increment,  
name varchar(20) not null  
) TYPE=Heap

（1）Mrg_Myisam引擎分表：

（一）先創(chuàng)建兩張user表，也就是說我要把用戶表進(jìn)行水平分表（因?yàn)橛脩籼嗬玻?0億）

//用戶表一
CREATE TABLE IF NOT EXISTS `user1` (  
 `id` int(11) NOT NULL ,
 `name` varchar(50) DEFAULT NULL,  
 PRIMARY KEY (`id`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;  

//用戶表二
CREATE TABLE IF NOT EXISTS `user2` (  
 `id` int(11) NOT NULL ,
 `name` varchar(50) DEFAULT NULL,  
 PRIMARY KEY (`id`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;  

//分別插入兩條測試數(shù)據(jù)先
INSERT INTO `user1` (`name`) VALUES('輔助');  
INSERT INTO `user2` (`name`) VALUES('JackFrost');

分表的要求：

1）分表必須使用MyISAM存儲(chǔ)引擎；

2）每個(gè)分表的表結(jié)構(gòu)必須相同；

3）MySQL必須具有存儲(chǔ)分表數(shù)據(jù)文件和索引文件的目錄的讀寫權(quán)限；

4）必須啟用MySQL的符號(hào)鏈接支持功能。

文件存儲(chǔ)：

1）MYD文件是MyISAM表的數(shù)據(jù)文件；

2）MYI文件是MyISAM表的索引文件；

3）frm文件用于存儲(chǔ)MyISAM表的表結(jié)構(gòu)。

（二）最后創(chuàng)建一個(gè)MERGE表，作為一個(gè)分發(fā)作用的總表。

CREATE TABLE IF NOT EXISTS `alluser` (  
  `id` int(11) NOT NULL ,  
  `name` varchar(50) DEFAULT NULL,  
   PRIMARY KEY (`id`)
) ENGINE=MRG_MYISAM  
DEFAULT CHARSET=utf8 
UNION=(user1,user2)  ;

（三）測試：

1. 查詢：

select id,name from alluser;

可見根據(jù)總表把所有數(shù)據(jù)都查出來了

這里寫圖片描述

2. 然而，當(dāng)你測試插入的時(shí)候：

你會(huì)發(fā)現(xiàn)，總表只有只讀權(quán)限

這里寫圖片描述

3. 但是我們又想在總表去插入，怎么辦呢？？

允許通過總表插入數(shù)據(jù)，數(shù)據(jù)存儲(chǔ)在MRG文件列出的第一個(gè)分表之中。例如，執(zhí)行以下SQL語句，將總表的INSERT_METHOD修改為FIRST，然后通過總表插入一條數(shù)據(jù)：

//就是插入總表的時(shí)候，其實(shí)也是插入到第一個(gè)分表。
ALTER TABLE `test_engine`.`alluser` INSERT_METHOD = FIRST;


INSERT INTO `alluser` (id,`name`) VALUES(2,'插入到第一個(gè)分表');
這里寫圖片描述

這里寫圖片描述

而，允許通過總表插入數(shù)據(jù)，數(shù)據(jù)存儲(chǔ)在MRG文件列出的最后一個(gè)分表之中。例如，執(zhí)行以下SQL語句，將總表的INSERT_METHOD修改為LAST，然后通過總表插入一條數(shù)據(jù)：

//就是插入總表的時(shí)候，其實(shí)也是插入到最后一個(gè)分表。
ALTER TABLE `test_engine`.`alluser` INSERT_METHOD = LAST;
這里寫圖片描述

這里寫圖片描述

注意INSERT_METHOD ：

INSERT_METHOD選項(xiàng)只會(huì)影響通過總表插入（INSERT）數(shù)據(jù)的行為，通過總表對(duì)數(shù)據(jù)進(jìn)行刪除（DELETE）、查詢（SELECT）、修改（UPDATE）、清空（TRUNCATE）都不會(huì)受影響。

（2）項(xiàng)目中如何使用MRG_MYISAM總表：

（一）插入（INSERT）數(shù)據(jù)時(shí)，需要根據(jù)給定的路由策略將新數(shù)據(jù)分別插入不同的子表，此處采用對(duì)id進(jìn)行模3計(jì)算（可能結(jié)果為0、1、2）來決定插入哪個(gè)子表。

所以我們需要?jiǎng)?chuàng)建一張表專門去創(chuàng)建id。

CREATE TABLE `create_id` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;

（二）ORM框架的時(shí)候使用上面這個(gè)表生成id

insert into create_id () values();

（三）真正插入的時(shí)候：

先獲取ID，然后根據(jù)拿到的創(chuàng)建ID去插入到分表。

INSERT INTO {$table_name} (id, name) VALUES ('{$id}');

（四）獲取表名和ID：

這個(gè)就需要我們自定義自己的規(guī)則了。比如取余、比如范圍預(yù)判斷。

其他的刪除（DELETE）、查詢（SELECT）、修改（UPDATE）、清空（TRUNCATE）等操作都可以通過總表alluser完成。

基于MRG_MYISAM存儲(chǔ)引擎實(shí)現(xiàn)的分表機(jī)制，比較適用于插入和查詢頻率較高的場景。由于MyISAM具有表級(jí)別的鎖機(jī)制，所以不適用于更新頻率較高的場景。

（3）MRG_MYISAM分表的優(yōu)點(diǎn)：

（一）適用于存儲(chǔ)日志數(shù)據(jù)。例如，可以將不同月份的數(shù)據(jù)存入不同的表，然后使用一些工具壓縮數(shù)據(jù)，最后通過一張MRG_MYISAM表來查詢這些數(shù)據(jù)。

（二）可以獲得更快的速度?？梢愿鶕?jù)某種指標(biāo)，將一張只讀的大表分割成若干張小表，然后將這些小表分別放在不同的磁盤上存儲(chǔ)。當(dāng)需要讀取數(shù)據(jù)時(shí)，MERGE表可以將這些小表的數(shù)據(jù)組織起來，就好像使用先前的大表一樣，但是速度會(huì)快很多。

（三）可以提高搜索效率?？梢愿鶕?jù)某種指標(biāo)將一張只讀的大數(shù)據(jù)表分割為若干個(gè)小表，然后根據(jù)不同的查詢維度，可以得到若干種小表的組合，然后再為這些組合分別創(chuàng)建不同的MERGE表。例如，有一張只讀的大數(shù)據(jù)表T，分割為T1、T2、T3、T4，共4張小表，有兩種查詢維度A和B，A可以得到小表組合T1、T2和T3，B可以得到小表組合T2、T3和T4，分別為A和B創(chuàng)建兩個(gè)MERGE表，也就是M1和M2，這兩個(gè)MERGE表分別關(guān)聯(lián)的小表是存在交疊的。

（四）可以更加有效的修復(fù)表。修復(fù)單個(gè)的小表要比修復(fù)大數(shù)據(jù)表更加容易。

（五）多個(gè)子表映射至一個(gè)總表的速度極快。因?yàn)镸ERGE表本身不會(huì)存儲(chǔ)和維護(hù)任何索引，索引都是由各個(gè)關(guān)聯(lián)的子表存儲(chǔ)和維護(hù)的，所以創(chuàng)建和重新映射MERGE表的速度非?？臁?/h4>

（六）不受操作系統(tǒng)的文件大小限制。單個(gè)表會(huì)受到文件大小的限制，但是拆分成多個(gè)表，則可以無限擴(kuò)容。

（七）MERGE表還可以用來給單個(gè)表創(chuàng)建別名，并且?guī)缀醪粫?huì)影響性能。

（4）使用MRG_MyISAM分表的必須思考問題：（針對(duì)總表）

（一）總表（MERGE表）必須使用MRG_MyISAM存儲(chǔ)引擎，子表必須使用MyISAM存儲(chǔ)引擎，不可避免會(huì)受到MyISAM存儲(chǔ)引擎的限制。（比如不支持事務(wù)）

（二）MERGE表不能使用某些MyISAM特性。例如，雖然可以為子表創(chuàng)建全文索引，但是卻不能使用全文索引，只能通過MERGE表查詢數(shù)據(jù)。

（三）若使用ALTER TABLE語句修改總表的存儲(chǔ)引擎，那么會(huì)立即丟失總表和子表的映射關(guān)系，并且會(huì)將所有子表的數(shù)據(jù)拷貝至修改后的新表。

（四）總表和子表的主鍵都不能使用自動(dòng)增長（auto increment）。

（五）子表之間不能保證唯一鍵約束，只能保證單個(gè)子表內(nèi)部的唯一性約束。也就是說，直接查總表全部，id可能會(huì)重復(fù)。

（六）由于不能保證唯一鍵約束，導(dǎo)致REPLACE語句的行為會(huì)不可預(yù)期，INSERT ... ON DUPLICATE KEY UPDATE語句也有類似問題。因此，只能使用路由策略，對(duì)子表使用這些語句，而不能對(duì)總表使用。

（七）當(dāng)正在使用總表時(shí)，不能對(duì)任何子表執(zhí)行ANALYZE TABLE、REPAIR TABLE、OPTIMIZE TABLE、ALTER TABLE、DROP TABLE、DELETE或TRUNCATE TABLE語句，否則會(huì)導(dǎo)致不可預(yù)期的結(jié)果。

（八）總表和子表的表結(jié)構(gòu)必須完全一致。

（九）總表可以映射的所有子表的總行數(shù)上限為 2的64次方 行。

（十）不支持INSERT DELAYED語句。

數(shù)據(jù)庫DEMO下載：MySQL數(shù)據(jù)庫實(shí)戰(zhàn)例子（存儲(chǔ)引擎、視圖、鎖機(jī)制、分表）

好了，MySQL優(yōu)化筆記（五）--數(shù)據(jù)庫存儲(chǔ)引擎（主要分析對(duì)比InnoDB和MyISAM）講完了，又是一篇數(shù)據(jù)庫優(yōu)化記錄，這是積累的必經(jīng)一步，我會(huì)繼續(xù)出這個(gè)系列文章，分享經(jīng)驗(yàn)給大家。歡迎在下面指出錯(cuò)誤，共同學(xué)習(xí)??！你的點(diǎn)贊是對(duì)我最好的支持??！

更多內(nèi)容，可以訪問JackFrost的博客

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容