MySQL存儲(chǔ)引擎是什么

本篇內(nèi)容主要講解“MySQL存儲(chǔ)引擎是什么”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“MySQL存儲(chǔ)引擎是什么”吧!

MySQL 存儲(chǔ)引擎

存儲(chǔ)引擎概述

數(shù)據(jù)庫(kù)最核心的一點(diǎn)就是用來(lái)存儲(chǔ)數(shù)據(jù)，數(shù)據(jù)存儲(chǔ)就避免不了和磁盤打交道。那么數(shù)據(jù)以哪種方式進(jìn)行存儲(chǔ)，如何存儲(chǔ)是存儲(chǔ)的關(guān)鍵所在。所以存儲(chǔ)引擎就相當(dāng)于是數(shù)據(jù)存儲(chǔ)的發(fā)動(dòng)機(jī)，來(lái)驅(qū)動(dòng)數(shù)據(jù)在磁盤層面進(jìn)行存儲(chǔ)。

MySQL 的架構(gòu)可以按照三層模式來(lái)理解

存儲(chǔ)引擎也是 MySQL 的組建，它是一種軟件，它所能做的和支持的功能主要有

• 并發(fā)

• 支持事務(wù)

• 完整性約束

• 物理存儲(chǔ)

• 支持索引

• 性能幫助

MySQL 默認(rèn)支持多種存儲(chǔ)引擎，來(lái)適用不同數(shù)據(jù)庫(kù)應(yīng)用，用戶可以根據(jù)需要選擇合適的存儲(chǔ)引擎，下面是 MySQL 支持的存儲(chǔ)引擎

• MyISAM

• InnoDB

• BDB

• MEMORY

• MERGE

• EXAMPLE

• NDB Cluster

• ARCHIVE

• CSV

• BLACKHOLE

• FEDERATED

默認(rèn)情況下，如果創(chuàng)建表不指定存儲(chǔ)引擎，會(huì)使用默認(rèn)的存儲(chǔ)引擎，如果要修改默認(rèn)的存儲(chǔ)引擎，那么就可以在參數(shù)文件中設(shè)置  default-table-type，能夠查看當(dāng)前的存儲(chǔ)引擎

show variables like 'table_type';

奇怪，為什么沒(méi)有了呢?網(wǎng)上求證一下，在 5.5.3 取消了這個(gè)參數(shù)

可以通過(guò)下面兩種方法查詢當(dāng)前數(shù)據(jù)庫(kù)支持的存儲(chǔ)引擎

show engines \g

在創(chuàng)建新表的時(shí)候，可以通過(guò)增加 ENGINE 關(guān)鍵字設(shè)置新建表的存儲(chǔ)引擎。

create table cxuan002(id int(10),name varchar(20)) engine = MyISAM;

上圖我們指定了 MyISAM 的存儲(chǔ)引擎。

如果你不知道表的存儲(chǔ)引擎怎么辦?你可以通過(guò) show create table 來(lái)查看

如果不指定存儲(chǔ)引擎的話，從MySQL 5.1 版本之后，MySQL 的默認(rèn)內(nèi)置存儲(chǔ)引擎已經(jīng)是 InnoDB了。建一張表看一下

如上圖所示，我們沒(méi)有指定默認(rèn)的存儲(chǔ)引擎，下面查看一下表

可以看到，默認(rèn)的存儲(chǔ)引擎是 InnoDB。

如果你的存儲(chǔ)引擎想要更換，可以使用

alter table cxuan003 engine = myisam;

來(lái)更換，更換完成后會(huì)顯示 「0 rows affected」 ，但其實(shí)已經(jīng)操作成功

我們使用 show create table 查看一下表的 sql 就知道

存儲(chǔ)引擎特性

下面會(huì)介紹幾個(gè)常用的存儲(chǔ)引擎以及它的基本特性，這些存儲(chǔ)引擎是 MyISAM、InnoDB、MEMORY 和 MERGE

MyISAM

在 5.1 版本之前，MyISAM 是 MySQL 的默認(rèn)存儲(chǔ)引擎，MyISAM 并發(fā)性比較差，使用的場(chǎng)景比較少，主要特點(diǎn)是

•  不支持事務(wù)操作，ACID 的特性也就不存在了，這一設(shè)計(jì)是為了性能和效率考慮的。

• 不支持外鍵操作，如果強(qiáng)行增加外鍵，MySQL 不會(huì)報(bào)錯(cuò)，只不過(guò)外鍵不起作用。

• MyISAM 默認(rèn)的鎖粒度是表級(jí)鎖，所以并發(fā)性能比較差，加鎖比較快，鎖沖突比較少，不太容易發(fā)生死鎖的情況。

• MyISAM 會(huì)在磁盤上存儲(chǔ)三個(gè)文件，文件名和表名相同，擴(kuò)展名分別是  .frm(存儲(chǔ)表定義)、.MYD(MYData,存儲(chǔ)數(shù)據(jù))、MYI(MyIndex,存儲(chǔ)索引)。這里需要特別注意的是 MyISAM  只緩存索引文件，并不緩存數(shù)據(jù)文件。

• MyISAM 支持的索引類型有 全局索引(Full-Text)、B-Tree 索引、R-Tree 索引
   

Full-Text 索引：它的出現(xiàn)是為了解決針對(duì)文本的模糊查詢效率較低的問(wèn)題。

B-Tree 索引：所有的索引節(jié)點(diǎn)都按照平衡樹(shù)的數(shù)據(jù)結(jié)構(gòu)來(lái)存儲(chǔ)，所有的索引數(shù)據(jù)節(jié)點(diǎn)都在葉節(jié)點(diǎn)

R-Tree索引：它的存儲(chǔ)方式和 B-Tree 索引有一些區(qū)別，主要設(shè)計(jì)用于存儲(chǔ)空間和多維數(shù)據(jù)的字段做索引,目前的 MySQL 版本僅支持  geometry 類型的字段作索引，相對(duì)于 BTREE，RTREE 的優(yōu)勢(shì)在于范圍查找。

數(shù)據(jù)庫(kù)所在主機(jī)如果宕機(jī)，MyISAM 的數(shù)據(jù)文件容易損壞，而且難以恢復(fù)。

增刪改查性能方面：SELECT 性能較高，適用于查詢較多的情況

InnoDB

自從 MySQL 5.1 之后，默認(rèn)的存儲(chǔ)引擎變成了 InnoDB 存儲(chǔ)引擎，相對(duì)于 MyISAM，InnoDB  存儲(chǔ)引擎有了較大的改變，它的主要特點(diǎn)是

• 支持事務(wù)操作，具有事務(wù) ACID  隔離特性，默認(rèn)的隔離級(jí)別是可重復(fù)讀(repetable-read)、通過(guò)MVCC(并發(fā)版本控制)來(lái)實(shí)現(xiàn)的。能夠解決臟讀和不可重復(fù)讀的問(wèn)題。

• InnoDB 支持外鍵操作。

• InnoDB 默認(rèn)的鎖粒度行級(jí)鎖，并發(fā)性能比較好，會(huì)發(fā)生死鎖的情況。

• 和 MyISAM 一樣的是，InnoDB 存儲(chǔ)引擎也有 .frm文件存儲(chǔ)表結(jié)構(gòu) 定義，但是不同的是，InnoDB 的表數(shù)據(jù)與索引數(shù)據(jù)是存儲(chǔ)在一起的，都位于  B+ 數(shù)的葉子節(jié)點(diǎn)上，而 MyISAM 的表數(shù)據(jù)和索引數(shù)據(jù)是分開(kāi)的。

• InnoDB 有安全的日志文件，這個(gè)日志文件用于恢復(fù)因數(shù)據(jù)庫(kù)崩潰或其他情況導(dǎo)致的數(shù)據(jù)丟失問(wèn)題，保證數(shù)據(jù)的一致性。

• InnoDB 和 MyISAM 支持的索引類型相同，但具體實(shí)現(xiàn)因?yàn)槲募Y(jié)構(gòu)的不同有很大差異。

• 增刪改查性能方面，果執(zhí)行大量的增刪改操作，推薦使用 InnoDB 存儲(chǔ)引擎，它在刪除操作時(shí)是對(duì)行刪除，不會(huì)重建表。

MEMOR

YMEMORY 存儲(chǔ)引擎使用存在內(nèi)存中的內(nèi)容來(lái)創(chuàng)建表。每個(gè) MEMORY 表實(shí)際只對(duì)應(yīng)一個(gè)磁盤文件，格式是 .frm。MEMORY  類型的表訪問(wèn)速度很快，因?yàn)槠鋽?shù)據(jù)是存放在內(nèi)存中。默認(rèn)使用 HASH 索引。

MERGE

MERGE 存儲(chǔ)引擎是一組 MyISAM 表的組合，MERGE 表本身沒(méi)有數(shù)據(jù)，對(duì) MERGE  類型的表進(jìn)行查詢、更新、刪除的操作，實(shí)際上是對(duì)內(nèi)部的 MyISAM 表進(jìn)行的。MERGE 表在磁盤上保留兩個(gè)文件，一個(gè)是 .frm 文件存儲(chǔ)表定義、一個(gè)是  .MRG 文件存儲(chǔ) MERGE 表的組成等。

選擇合適的存儲(chǔ)引擎

在實(shí)際開(kāi)發(fā)過(guò)程中，我們往往會(huì)根據(jù)應(yīng)用特點(diǎn)選擇合適的存儲(chǔ)引擎。

• MyISAM：如果應(yīng)用程序通常以檢索為主，只有少量的插入、更新和刪除操作，并且對(duì)事物的完整性、并發(fā)程度不是很高的話，通常建議選擇 MyISAM  存儲(chǔ)引擎。

• InnoDB：如果使用到外鍵、需要并發(fā)程度較高，數(shù)據(jù)一致性要求較高，那么通常選擇 InnoDB  引擎，一般互聯(lián)網(wǎng)大廠對(duì)并發(fā)和數(shù)據(jù)完整性要求較高，所以一般都使用 InnoDB 存儲(chǔ)引擎。

• MEMORY：MEMORY 存儲(chǔ)引擎將所有數(shù)據(jù)保存在內(nèi)存中，在需要快速定位下能夠提供及其迅速的訪問(wèn)。MEMORY  通常用于更新不太頻繁的小表，用于快速訪問(wèn)取得結(jié)果。

• MERGE：MERGE 的內(nèi)部是使用 MyISAM 表，MERGE 表的優(yōu)點(diǎn)在于可以突破對(duì)單個(gè) MyISAM  表大小的限制，并且通過(guò)將不同的表分布在多個(gè)磁盤上， 可以有效地改善 MERGE 表的訪問(wèn)效率。

選擇合適的數(shù)據(jù)類型

我們會(huì)經(jīng)常遇見(jiàn)的一個(gè)問(wèn)題就是，在建表時(shí)如何選擇合適的數(shù)據(jù)類型，通常選擇合適的數(shù)據(jù)類型能夠提高性能、減少不必要的麻煩，下面我們就來(lái)一起探討一下，如何選擇合適的數(shù)據(jù)類型。

CHAR 和 VARCHAR 的選擇

char 和 varchar 是我們經(jīng)常要用到的兩個(gè)存儲(chǔ)字符串的數(shù)據(jù)類型，char 一般存儲(chǔ)定長(zhǎng)的字符串，它屬于固定長(zhǎng)度的字符類型，比如下面

可以看到，不管你的值寫的是什么，一旦指定了 char  字符的長(zhǎng)度，如果你的字符串長(zhǎng)度不夠指定字符的長(zhǎng)度的話，那么就用空格來(lái)填補(bǔ)，如果超過(guò)字符串長(zhǎng)度的話，只存儲(chǔ)指定字符長(zhǎng)度的字符。

?這里注意一點(diǎn)：如果 MySQL 使用了非 嚴(yán)格模式的話，上面表格最后一行是可以存儲(chǔ)的。如果 MySQL 使用了 嚴(yán)格模式  的話，那么表格上面最后一行存儲(chǔ)會(huì)報(bào)錯(cuò)。?

如果使用了 varchar 字符類型，我們來(lái)看一下例子

可以看到，如果使用 varchar 的話，那么存儲(chǔ)的字節(jié)將根據(jù)實(shí)際的值進(jìn)行存儲(chǔ)。你可能會(huì)疑惑為什么 varchar 的長(zhǎng)度是 5 ，但是卻需要存儲(chǔ) 3  個(gè)字節(jié)或者 6 個(gè)字節(jié)，這是因?yàn)槭褂?varchar  數(shù)據(jù)類型進(jìn)行存儲(chǔ)時(shí)，默認(rèn)會(huì)在最后增加一個(gè)字符串長(zhǎng)度，占用1個(gè)字節(jié)(如果列聲明的長(zhǎng)度超過(guò)255，則使用兩個(gè)字節(jié))。varchar 不會(huì)填充空余的字符串。

一般使用 char 來(lái)存儲(chǔ)定長(zhǎng)的字符串，比如「身份證號(hào)、手機(jī)號(hào)、郵箱等」;使用 varchar 來(lái)存儲(chǔ)不定長(zhǎng)的字符串。由于 char  長(zhǎng)度是固定的，所以它的處理速度要比 VARCHAR 快很多，但是缺點(diǎn)是浪費(fèi)存儲(chǔ)空間，但是隨著 MySQL 版本的不斷演進(jìn)，varchar  數(shù)據(jù)類型的性能也在不斷改進(jìn)和提高，所以在許多應(yīng)用中，VARCHAR 類型更多的被使用。

在 MySQL 中，不同的存儲(chǔ)引擎對(duì) CHAR 和 VARCHAR 的使用原則也有不同

• MyISAM：建議使用固定長(zhǎng)度的數(shù)據(jù)列替代可變長(zhǎng)度的數(shù)據(jù)列，也就是 CHAR

• MEMORY：使用固定長(zhǎng)度進(jìn)行處理、CHAR 和 VARCHAR 都會(huì)被當(dāng)作 CHAR 處理

• InnoDB：建議使用 VARCHAR 類型

TEXT 與 BLOB

一般在保存較少的文本的時(shí)候，我們會(huì)選擇 CHAR 和 VARCHAR，在保存大數(shù)據(jù)量的文本時(shí)，我們往往選擇 TEXT 和 BLOB;TEXT 和 BLOB  的主要差別是 BLOB 能夠保存二進(jìn)制數(shù)據(jù);而 TEXT 只能保存字符數(shù)據(jù)，TEXT 往下細(xì)分有

• TEXT

• MEDIUMTEXT

• LONGTEXT

BLOB 往下細(xì)分有

• BLOB

• MEDIUMBLOB

• LONGBLOB

三種，它們最主要的區(qū)別就是存儲(chǔ)文本長(zhǎng)度不同和存儲(chǔ)字節(jié)不同，用戶應(yīng)該根據(jù)實(shí)際情況選擇滿足需求的最小存儲(chǔ)類型，下面主要對(duì) BLOB 和 TEXT  存在一些問(wèn)題進(jìn)行介紹

TEXT 和 BLOB 在刪除數(shù)據(jù)后會(huì)存在一些性能上的問(wèn)題，為了提高性能，建議使用 OPTIMIZE TABLE 功能對(duì)表進(jìn)行碎片整理。

也可以使用合成索引來(lái)提高文本字段(BLOB 和 TEXT)的查詢性能。合成索引就是根據(jù)大文本(BLOB 和  TEXT)字段的內(nèi)容建立一個(gè)散列值，把這個(gè)值存在對(duì)應(yīng)列中，這樣就能夠根據(jù)散列值查找到對(duì)應(yīng)的數(shù)據(jù)行。一般使用散列算法比如 md5() 和 SHA1()  ，如果散列算法生成的字符串帶有尾部空格，就不要把它們存在 CHAR 和 VARCHAR 中，下面我們就來(lái)看一下這種使用方式

首先創(chuàng)建一張表，表中記錄 blob 字段和 hash 值

向 cxuan005 中插入數(shù)據(jù)，其中 hash 值作為 info 的散列值。

然后再插入兩條數(shù)據(jù)

插入一條 info 為 cxuan005 的數(shù)據(jù)

如果想要查詢 info 為 cxuan005 的數(shù)據(jù)，可以通過(guò)查詢 hash 列來(lái)進(jìn)行查詢

這是合成索引的例子，如果要對(duì) BLOB 進(jìn)行模糊查詢的話，就要使用前綴索引。

其他優(yōu)化 BLOB 和 TEXT 的方式：

• 非必要的時(shí)候不要檢索 BLOB 和 TEXT 索引

• 把 BLOB 或 TEXT 列分離到單獨(dú)的表中。

浮點(diǎn)數(shù)和定點(diǎn)數(shù)的選擇

浮點(diǎn)數(shù)指的就是含有小數(shù)的值，浮點(diǎn)數(shù)插入到指定列中超過(guò)指定精度后，浮點(diǎn)數(shù)會(huì)四舍五入，MySQL 中的浮點(diǎn)數(shù)指的就是 float 和  double，定點(diǎn)數(shù)指的是 decimal，定點(diǎn)數(shù)能夠更加精確的保存和顯示數(shù)據(jù)。下面通過(guò)一個(gè)示例講解一下浮點(diǎn)數(shù)精確性問(wèn)題

首先創(chuàng)建一個(gè)表 cxuan006 ，只為了測(cè)試浮點(diǎn)數(shù)問(wèn)題，所以這里我們選擇的數(shù)據(jù)類型是 float

然后分別插入兩條數(shù)據(jù)

然后執(zhí)行查詢，可以看到查詢出來(lái)的兩條數(shù)據(jù)執(zhí)行的舍入不同

為了清晰的看清楚浮點(diǎn)數(shù)與定點(diǎn)數(shù)的精度問(wèn)題，再來(lái)看一個(gè)例子

先修改 cxuan006 的兩個(gè)字段為相同的長(zhǎng)度和小數(shù)位數(shù)

然后插入兩條數(shù)據(jù)

執(zhí)行查詢操作，可以發(fā)現(xiàn)，浮點(diǎn)數(shù)相較于定點(diǎn)數(shù)來(lái)說(shuō)，會(huì)產(chǎn)生誤差

日期類型選擇

在 MySQL 中，用來(lái)表示日期類型的有 「DATE、TIME、DATETIME、TIMESTAMP」，在

138 張圖帶你 MySQL 入門

這篇文中介紹過(guò)了日期類型的區(qū)別，我們這里就不再闡述了。下面主要介紹一下選擇

• TIMESTAMP 和時(shí)區(qū)相關(guān)，更能反映當(dāng)前時(shí)間，如果記錄的日期需要讓不同時(shí)區(qū)的人使用，最好使用 TIMESTAMP。

• DATE 用于表示年月日，如果實(shí)際應(yīng)用值需要保存年月日的話就可以使用 DATE。

• TIME 用于表示時(shí)分秒，如果實(shí)際應(yīng)用值需要保存時(shí)分秒的話就可以使用 TIME。

• YEAR 用于表示年份，YEAR 有 2 位(最好使用4位)和 4 位格式的年。默認(rèn)是4位。如果實(shí)際應(yīng)用只保存年份，那么用 1 bytes 保存 YEAR  類型完全可以。不但能夠節(jié)約存儲(chǔ)空間，還能提高表的操作效率。

MySQL 字符集

下面來(lái)認(rèn)識(shí)一下 MySQL 字符集，簡(jiǎn)單來(lái)說(shuō)字符集就是一套文字符號(hào)和編碼、比較規(guī)則的集合。1960 年美國(guó)標(biāo)準(zhǔn)化組織 ANSI  發(fā)布了第一個(gè)計(jì)算機(jī)字符集，就是著名的 ASCII(American Standard Code for Information Interchange) 。自從  ASCII 編碼后，每個(gè)國(guó)家、國(guó)際組織都研究了一套自己的字符集，比如 ISO-8859-1、GBK 等。

但是每個(gè)國(guó)家都使用自己的字符集為移植性帶來(lái)了很大的困難。所以，為了統(tǒng)一字符編碼，國(guó)際標(biāo)準(zhǔn)化組織(ISO) 指定了統(tǒng)一的字符標(biāo)準(zhǔn) - Unicode  編碼，它容納了幾乎所有的字符編碼。下面是一些常見(jiàn)的字符編碼

對(duì)數(shù)據(jù)庫(kù)來(lái)說(shuō)，字符集是很重要的，因?yàn)閿?shù)據(jù)庫(kù)存儲(chǔ)的數(shù)據(jù)大多數(shù)都是各種文字，字符集對(duì)數(shù)據(jù)庫(kù)的存儲(chǔ)、性能、系統(tǒng)的移植來(lái)說(shuō)都非常重要。

MySQL 支持多種字符集，可以使用 show character set; 來(lái)查看所有可用的字符集

或者使用

select character_set_name, default_collate_name, description, maxlen from  information_schema.character_sets;

來(lái)查看。

使用 information_schema.character_set 來(lái)查看字符集和校對(duì)規(guī)則。

到此，相信大家對(duì)“MySQL存儲(chǔ)引擎是什么”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！