一條SQL查詢語(yǔ)句是怎么去執(zhí)行的？（下）

3. 存儲(chǔ)引擎

經(jīng)歷千辛萬(wàn)苦，MySQL終于算出了最終的執(zhí)行計(jì)劃，然后就可以直接執(zhí)行了嗎？

好吧。。。依然還不可以。

我們知道，表是由一行一行的記錄組成的，但這只是邏輯上的概念，或者說(shuō)只是看上去是這樣而已。

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

到底該把數(shù)據(jù)存儲(chǔ)在什么位置，是內(nèi)存還是磁盤？怎么從表里讀取數(shù)據(jù)，以及怎么把數(shù)據(jù)寫入具體的表中，這都是存儲(chǔ)引擎 負(fù)責(zé)的事情。

好吧，看到這里或許你還不知道存儲(chǔ)引擎到底是什么。畢竟存儲(chǔ)引擎這個(gè)名字聽起來(lái)太玄乎了，它的前身叫做表處理器，是不是就接地氣了許多呢？

3.2 為什么需要存儲(chǔ)引擎

因?yàn)榇鎯?chǔ)的需求不同。

試想一下：

• 如果一張表，需要很高的訪問速度，而不需要考慮持久化的問題，是不是最好把數(shù)據(jù)放在內(nèi)存呢？
• 如果一張表，是用來(lái)做歷史數(shù)據(jù)存檔的，不需要修改，也不需要索引，那是不是要支持?jǐn)?shù)據(jù)的壓縮?
• 如果一張表用在讀寫并發(fā)很多的業(yè)務(wù)中，是不是要支持讀寫互不干擾，而且要保證比較高的數(shù)據(jù)一致性呢?

大家應(yīng)該明白了，為什么要支持這么多的存儲(chǔ)引擎，因?yàn)橐环N存儲(chǔ)引擎不能提供所有的特性。

存儲(chǔ)引擎是計(jì)算機(jī)抽象的典型代表，它的功能就是接受上層指令，然后對(duì)表中數(shù)據(jù)進(jìn)行讀取和寫入，而這些操作對(duì)上層完全是屏蔽的。你甚至可以查閱MySQL文檔定義自己的存儲(chǔ)引擎，只要對(duì)外實(shí)現(xiàn)同樣的接口就可以了。

存儲(chǔ)引擎就是MySQL對(duì)數(shù)據(jù)進(jìn)行讀寫的插件而已，可以根據(jù)不同目的隨意更換（插拔）

3.3 存儲(chǔ)引擎怎么用

3.3.1 創(chuàng)建表的時(shí)候指定存儲(chǔ)引擎

在創(chuàng)建表的時(shí)候可以指定當(dāng)前表的存儲(chǔ)引擎，如果沒有指定，默認(rèn)的存儲(chǔ)引擎為InnoDB，如果想顯式指定存儲(chǔ)引擎，可以這樣

CREATE TABLE `t_user_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;

3.3.2 修改表的存儲(chǔ)引擎

ALTER TABLE 表名 ENGINE = 存儲(chǔ)引擎名稱;

3.4 存儲(chǔ)引擎底層區(qū)別

下面我們分別創(chuàng)建3張?jiān)O(shè)置了不同存儲(chǔ)引擎的表， t_user_innodb 、 t_user_myisam 、t_user_memory我們看一下不同存儲(chǔ)引擎在底層存儲(chǔ)方面的差異，首先找到MySQL的數(shù)據(jù)存儲(chǔ)目錄

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

進(jìn)入到目標(biāo)目錄之后，找到當(dāng)前數(shù)據(jù)庫(kù)對(duì)應(yīng)的目錄（MySQL會(huì)為一個(gè)數(shù)據(jù)庫(kù)創(chuàng)建一個(gè)同名的目錄），數(shù)據(jù)庫(kù)中表的存儲(chǔ)結(jié)構(gòu)如下不同的存儲(chǔ)引擎存放數(shù)據(jù)的方式不一樣，產(chǎn)生的文件數(shù)量和格式也不一樣，InnoDB文件包含2個(gè)，MEMORY文件包含1個(gè)，MYISAM文件包含3個(gè)。

3.5 常見存儲(chǔ)引擎比較

首先我們查看一下當(dāng)前MySQL服務(wù)器支持的存儲(chǔ)引擎都有哪一些。

mysql> SHOW ENGINES;
+--------------------+---------+--------------+------+------------+
| Engine             | Support | Transactions | XA   | Savepoints |
+--------------------+---------+--------------+------+------------+
| InnoDB             | DEFAULT | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | NO           | NO   | NO         |
| MEMORY             | YES     | NO           | NO   | NO         |
| BLACKHOLE          | YES     | NO           | NO   | NO         |
| MyISAM             | YES     | NO           | NO   | NO         |
| CSV                | YES     | NO           | NO   | NO         |
| ARCHIVE            | YES     | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | NO           | NO   | NO         |
| FEDERATED          | NO      | NULL         | NULL | NULL       |
+--------------------+---------+--------------+------+------------+

其中，

• Support表示該存儲(chǔ)引擎是否可用；
• DEFAULT表示當(dāng)前MySQL服務(wù)器默認(rèn)的存儲(chǔ)引擎；
• Transactions表示該存儲(chǔ)引擎是否支持事務(wù)；
• XA表示該存儲(chǔ)引擎是否支持分布式事務(wù)；
• Savepoints表示該存儲(chǔ)引擎是否支持事務(wù)的部分回滾。

3.5.1 MylSAM

應(yīng)用范圍比較小，表級(jí)鎖定限制了讀/寫的性能，因此在Web和數(shù)據(jù)倉(cāng)庫(kù)配置中，通常用于只讀或以讀為主的工作。

特點(diǎn):

• 支持表級(jí)別的鎖（插入和更新會(huì)鎖表），不支持事務(wù)；
• 擁有較高的插入（insert）和查詢（select）速度；
• 存儲(chǔ)了表的行數(shù)（count速度更快）。

怎么快速向數(shù)據(jù)庫(kù)插入100萬(wàn)條數(shù)據(jù)？

可以先用MylSAM插入數(shù)據(jù)，然后修改存儲(chǔ)引擎為InnoDB。

3.5.2 InnoDB

MySQL 5.7及更新版中的默認(rèn)存儲(chǔ)引擎。InnoDB是一個(gè)事務(wù)安全（與ACID兼容）的MySQL 存儲(chǔ)引擎，它具有提交、回滾和崩潰恢復(fù)功能來(lái)保護(hù)用戶數(shù)據(jù)。InnoDB行級(jí)鎖（不升級(jí)為更粗粒度的鎖）和Oracle風(fēng)格的一致非鎖讀提高了多用戶并發(fā)性。InnoDB將用戶數(shù)據(jù)存儲(chǔ)在聚集索引中，以減少基于主鍵的常見查詢的I/O。為了保持?jǐn)?shù)據(jù)完整性，InnoDB還支持外鍵引用完整性約束。

特點(diǎn)：

• 支持事務(wù)，支持外鍵，因此數(shù)據(jù)的完整性、一致性更高；
• 支持行級(jí)別的鎖和表級(jí)別的鎖；
• 支持讀寫并發(fā)，寫不阻塞讀（MVCC）；
• 特殊的索引存放方式，可以減少IO，提升査詢效率。

番外：InnoDB本來(lái)是InnobaseOy公司開發(fā)的，它和MySQL AB公司合作開源了InnoDB的代碼。但是沒想到MySQL的競(jìng)爭(zhēng)對(duì)手Oracle把InnobaseOy收購(gòu)了。后來(lái)08年Sun公司（開發(fā)Java語(yǔ)言的Sun）收購(gòu)了MySQL AB，09年Sun公司又被Oracle收購(gòu)了，所以MySQL和 InnoDB又是一家了。有人覺得MySQL越來(lái)越像Oracle,其實(shí)也是這個(gè)原因。

3.5.3 Memory

將所有數(shù)據(jù)存儲(chǔ)在RAM中，以便快速訪問。這個(gè)引擎以前被稱為堆引擎。

特點(diǎn)：

• 把數(shù)據(jù)放在內(nèi)存里面，讀寫的速度很快，但是數(shù)據(jù)庫(kù)重啟或者崩潰，數(shù)據(jù)會(huì)全部消失；
• 只適合做臨時(shí)表。

3.5.4 CSV

它的表實(shí)際上是帶有逗號(hào)分隔值的文本文件。csv表允許以CSV格式導(dǎo)入或轉(zhuǎn)儲(chǔ)數(shù)據(jù), 以便與讀寫相同格式的腳本和應(yīng)用程序交換數(shù)據(jù)。因?yàn)镃SV表沒有索引，所以通常在正常操作期間將數(shù)據(jù)保存在InnoDB表中，只在導(dǎo)入或?qū)С鲭A段使用csv表。

特點(diǎn)：

• 不允許空行，不支持索引；
• 格式通用，可以直接編輯，適合在不同數(shù)據(jù)庫(kù)之間導(dǎo)入導(dǎo)出。

3.5.5 Archive

專用與存檔，空間經(jīng)過壓縮，用于存儲(chǔ)和檢索大量很少引用的信息。

特點(diǎn)：

• 不支持索引；
• 不支持update、delete。

3.6 如何選擇存儲(chǔ)引擎

• 如果對(duì)數(shù)據(jù)一致性要求比較高，需要事務(wù)支持，可以選擇InnoDB。
• 如果數(shù)據(jù)查詢多更新少，對(duì)查詢性能要求比較高，可以選擇MyISAM。
• 如果需要一個(gè)用于查詢的臨時(shí)表，可以選擇Memory。

如果所有的存儲(chǔ)引擎都不能滿足你的需求，并且技術(shù)能力足夠，可以根據(jù)官網(wǎng)內(nèi)部手冊(cè)用C語(yǔ)言開發(fā)一個(gè)存儲(chǔ)引擎：https://dev.mvsql.com/doc/internals/en/custom-engine.html