MySQL的存儲引擎InnoDB和MyISAM

更新時間：2022年04月25日 14:20:07 作者：??YuShiwen????

這篇文章主要介紹了MySQL的存儲引擎InnoDB和MyISAM，MyISAM?用的是非聚集索引方式，即數(shù)據(jù)和索引落在不同的兩個文件上，下文更多相關(guān)內(nèi)容需要的小伙伴可以參考一下

1. MyISAM底層存儲

（非聚集索引方式）與InnoDB底層存儲（聚集索引方式）

1.1 MyISAM底層存儲（非聚集索引方式）

Myisam 創(chuàng)建表后生成的文件有三個：

frm:創(chuàng)建表的語句
MYD:表里面的數(shù)據(jù)文件（myisam data）
MYI:表里面的索引文件（myisam index）

底層存儲方式：

MyISAM 用的是非聚集索引方式，即數(shù)據(jù)和索引落在不同的兩個文件上。
MyISAM 在建表時以主鍵作為 KEY 來建立主索引 B+樹，樹的葉子節(jié)點存的是對應(yīng)數(shù)據(jù)的物理地址。
當我們?yōu)槟硞€字段添加索引時，我們同樣會生成對應(yīng)字段的索引樹，該字段的索引樹的葉子節(jié)點同樣是記錄了對應(yīng)數(shù)據(jù)的物理地址，然后也是拿著這個物理地址去數(shù)據(jù)文件里定位到具體的數(shù)據(jù)記錄。

圖片講解得很清楚，大家可以結(jié)合圖片看一下：

1.2 InnoDB底層存儲（聚集索引方式）

Innodb 創(chuàng)建表后生成的文件有兩個：

frm:創(chuàng)建表的語句
idb:表里面的數(shù)據(jù)+索引文件

底層存儲方式：

InnoDB 是聚集索引方式，因此數(shù)據(jù)和索引都存儲在同一個文件里。
InnoDB 在建表時以主鍵作為 KEY 來建立主索引 B+樹，樹的葉子節(jié)點存的是主鍵ID和主鍵 ID 對應(yīng)的數(shù)據(jù)。
當我們?yōu)槟硞€字段添加索引時，我們同樣會生成對應(yīng)字段的索引樹，但是該字段的索引樹的葉子節(jié)點存儲的是該字段所在行的主鍵KEY，拿到主鍵 KEY 后，再去查詢一下主鍵索引樹，才可以定位到具體數(shù)據(jù)；也就是會進行兩次查找。

圖片講解得很清楚，大家可以結(jié)合圖片看一下：

2. InnoDB與MyISAM簡介

Mysql 底層數(shù)據(jù)引擎以插件形式設(shè)計，最常見的是 Innodb 引擎和 Myisam 引擎，用戶可以根據(jù)個人需求選擇不同的引擎作為 Mysql 數(shù)據(jù)表的底層引擎。

MyISAM 存儲引擎
- 是 MySQL 中常見的存儲引擎，雖然性能極佳，擁有較高的插入，查詢速度，但卻不支持事務(wù)處理（transaction），不支持外鍵。曾（MySQL 5.5及之前版本）是 MySQL 的默認存儲引擎。
InnoDB 存儲引擎
- 是MySQL的數(shù)據(jù)庫引擎之一，現(xiàn)為MySQL的默認存儲引擎，事務(wù)型數(shù)據(jù)庫的首選引擎，InnoDB是為處理巨大數(shù)據(jù)量時的最大性能設(shè)計，支持ACID事務(wù)，支持行級鎖定。
- 給MySQL提供了事務(wù)(transaction)、回滾(rollback)和崩潰修復(fù)能力(crash recovery capabilities)、多版本并發(fā)控制(multi-versioned concurrency control)的事務(wù)安全(transaction-safe (ACID compliant))等功能。

ACID：

Atomicity（原子性）：一個事務(wù)（transaction）中的所有操作，要么全部完成，要么全部不完成，不會結(jié)束在中間某個環(huán)節(jié)。事務(wù)在執(zhí)行過程中發(fā)生錯誤，會被恢復(fù)（Rollback）到事務(wù)開始前的狀態(tài)，就像這個事務(wù)從來沒有執(zhí)行過一樣。
Consistency（一致性）：在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預(yù)設(shè)規(guī)則，這包含資料的精確度、串聯(lián)性以及后續(xù)數(shù)據(jù)庫可以自發(fā)性地完成預(yù)定的工作。
Isolation（隔離性）：數(shù)據(jù)庫允許多個并發(fā)事務(wù)同時對其數(shù)據(jù)進行讀寫和修改的能力，隔離性可以防止多個事務(wù)并發(fā)執(zhí)行時由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同級別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（Serializable）。
Durability（持久性）：事務(wù)處理結(jié)束后，對數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會丟失。

3. MyISAM與InnoDB的比較

	MyISAM	InnoDB
存儲結(jié)構(gòu)	Myisam 創(chuàng)建表后生成的文件有三個，分別為： frm:創(chuàng)建表的語句 MYD:表里面的數(shù)據(jù)文件（myisam data） MYI:表里面的索引文件（myisam index）	Innodb 創(chuàng)建表后生成的文件有兩個，分別為： frm:創(chuàng)建表的語句 idb:表里面的數(shù)據(jù)+索引文件
存儲空間	MyISAM支持支持三種不同的存儲格式：靜態(tài)表(默認，但是注意數(shù)據(jù)末尾不能有空格，會被去掉)、動態(tài)表、壓縮表。當表在創(chuàng)建之后并導(dǎo)入數(shù)據(jù)之后，不會再進行修改操作，可以使用壓縮表，極大的減少磁盤的空間占用。	需要更多的內(nèi)存和存儲，它會在主內(nèi)存中建立其專用的緩沖池用于高速緩沖數(shù)據(jù)和索引。
可移植性、備份及恢復(fù)	數(shù)據(jù)是以文件的形式存儲，所以在跨平臺的數(shù)據(jù)轉(zhuǎn)移中會很方便。在備份和恢復(fù)時可單獨針對某個表進行操作； MyISAM遇到錯誤，必須完整掃描后才能重建索引，或修正未寫入硬盤的錯誤； MyISAM的修復(fù)時間，與數(shù)據(jù)量的多少成正比。	在數(shù)據(jù)量很大的時候就相對痛苦； InnoDB可借由事務(wù)記錄檔（Transaction Log）來恢復(fù)程序崩潰（crash），或非預(yù)期結(jié)束所造成的數(shù)據(jù)錯誤； InnoDB的修復(fù)時間，大略都是固定的。
索引	`非聚集索引`，MyISAM 是非聚集索引，數(shù)據(jù)文件是分離的，索引保存的是數(shù)據(jù)文件的指針。主鍵索引和輔助索引是獨立的。	`聚集索引`，聚集索引的文件存放在主鍵索引的葉子節(jié)點上，因此 InnoDB 必須要有主鍵，通過主鍵索引效率很高。但是輔助索引需要兩次查詢，先查詢到主鍵，然后再通過主鍵查詢到數(shù)據(jù)。因此，主鍵不應(yīng)該過大，因為主鍵太大，其他索引也都會很大。
事務(wù)支持	強調(diào)的是性能，每次查詢具有原子性,其執(zhí)行數(shù)度比InnoDB類型更快，但是`不提供事務(wù)支持`。	`提供事務(wù)支持`，外部鍵等高級數(shù)據(jù)庫功能。具有事務(wù)(commit)、回滾(rollback)和崩潰修復(fù)能力(crash recovery capabilities)的事務(wù)安全(transaction-safe (ACID compliant))型表。
主鍵自增長	可以和其他字段一起建立聯(lián)合索引。引擎的自動增長列必須是索引，如果是組合索引，自動增長可以不是第一列，他可以根據(jù)前面幾列進行排序后遞增。	InnoDB中必須包含只有該字段的索引。引擎的自動增長列必須是索引，如果是組合索引也必須是組合索引的第一列。
鎖的粒度	`只支持表級鎖`，用戶在操作myisam表時，select，update，delete，insert語句都會給表自動加鎖，如果加鎖以后的表滿足insert并發(fā)的情況下，可以在表的尾部插入新的數(shù)據(jù)。	`支持行級鎖`。行鎖大幅度提高了多用戶并發(fā)操作的性能。但是InnoDB的行鎖，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的。
全文索引	支持 FULLTEXT類型的全文索引	不支持FULLTEXT類型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
表主鍵	允許沒有任何索引和主鍵的表存在，索引都是保存行的地址。	如果沒有設(shè)定主鍵或者非空唯一索引，就會自動生成一個6字節(jié)的主鍵(用戶不可見)，數(shù)據(jù)是主索引的一部分，附加索引保存的是主索引的值。
存儲表的具體行數(shù)	保存有表的總行數(shù)，如果select count() from table;會直接取出出該值。	沒有保存表的總行數(shù)，如果使用select count(*) from table；就會遍歷整個表，消耗相當大，但是在加了wehre條件后，myisam和innodb處理的方式都一樣。
外鍵	不支持	支持，對一個包含外鍵的 InnoDB 表轉(zhuǎn)為 MYISAM 會失敗。

4. 存儲引擎的使用

什么時候用MyISAM數(shù)據(jù)存儲引擎？什么時候用InnoDB數(shù)據(jù)存儲引擎？

《高性能MySQL》中寫道：

InnoDB：MySQL默認的事務(wù)型引擎，也是最重要和使用最廣泛的存儲引擎。它被設(shè)計成為大量的短期事務(wù)，短期事務(wù)大部分情況下是正常提交的，很少被回滾。InnoDB的性能與自動崩潰恢復(fù)的特性，使得它在非事務(wù)存儲需求中也很流行。除非有非常特別的原因需要使用其他的存儲引擎，否則應(yīng)該優(yōu)先考慮InnoDB引擎。
MyISAM：在MySQL 5.1 及之前的版本，MyISAM是默認引擎。MyISAM提供的大量的特性，包括全文索引、壓縮、空間函數(shù)（GIS）等，但MyISAM并不支持事務(wù)以及行級鎖，而且一個毫無疑問的缺陷是崩潰后無法安全恢復(fù)。正是由于MyISAM引擎的緣故，即使MySQL支持事務(wù)已經(jīng)很長時間了，在很多人的概念中MySQL還是非事務(wù)型數(shù)據(jù)庫。盡管這樣，它并不是一無是處的。對于只讀的數(shù)據(jù)，或者表比較小，可以忍受修復(fù)操作，則依然可以使用MyISAM（但請不要默認使用MyISAM，而是應(yīng)該默認使用InnoDB）。

總結(jié)：

MyISAM管理非事務(wù)表，提供高速存儲和檢索以及全文搜索能力，如果再應(yīng)用中執(zhí)行大量select操作，應(yīng)該選擇MyISAM
InnoDB用于事務(wù)處理，具有ACID事務(wù)支持等特性，如果在應(yīng)用中執(zhí)行大量insert和update操作，應(yīng)該選擇InnoDB

到此這篇關(guān)于MySQL的存儲引擎InnoDB和MyISAM的文章就介紹到這了,更多相關(guān)MySQL存儲引擎內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: