面試必問的 MySQL 知識點，你還有哪些沒準備好，趕緊收藏

InnoDB 與 MyISAM 引擎的區別

InnoDB 與 MyISAM 的區別在日常學習和麵試中經常被問到，我們先來看看這個吧。

鎖相關

1. InnoDB 支持表級鎖和行級鎖，MyISAM 只支持表級鎖。事務的併發在如今的互聯網海量數據的場景下基本上是無法擺脫的，這種情況下對數據庫的鎖的要求就要更細粒度。新版本的 MySQL 默認的數據庫引擎是 InnoDB 很大的原因就是 InnoDB 支持行級鎖，在日常的業務場景下，我們是不能允許使用表級鎖的，對業務的影響是很大的。
2. InnoDB 的鎖分為共享鎖 S 和獨佔鎖 X，意向共享鎖 IS，意向獨佔鎖 IX，記錄鎖，間隙鎖以及 next-key 鎖。

底層的文件結構

InnoDB 和 MyISAM 引擎底層存儲數據的文件結構也不一樣。InnoDB 引擎的數據表底層只有.frm 和 .ibd 兩種格式的文件。MyISAM 引擎的表底層有 .MYD，.MYI 和 .frm 三種格式的文件。

上圖中表 other_table 的引擎採用的是 MyISAM，底層會出現三種格式的文件，分別是 .frm 用於存儲表結構的文件；.MYD 全稱為 .mydata 用於存放數據的文件；.MYI 全稱為 .myIndex 用於存放索引的文件。而表 sys_log_op 表的引擎採用的是 InnoDB，底層只有兩個文件，分別為 .frm 同樣用於存放表的數據結構；.ibd 文件用於存儲數據和索引的文件。

從上面我們可以看到，InnoDB 的數據和索引是存放在同一個文件中的而 MyISAM 的數據和索引是分開存儲的，這也導致兩者的索引的實現方式的不同。

索引的實現方式

InnoDB 的索引採用 B+ 樹實現，主鍵索引的葉子節點存儲的是完整的數據，輔助索引的葉子節點存儲的是主鍵的值。所以如果在使用輔助索引進行查詢數據的時候是會有回表的操作，所以我們儘量用主鍵進行查詢。另外如果建表的時候沒有設置主鍵的話，InnoDB 引擎會自動生成一個 rowid 作為表的主鍵。

MyISAM 的索引也是採用 B+ 樹實現的，與 InnoDB 不同的是，MyISAM 主鍵索引的葉子節點和輔助索引保存的都是數據的地址，主鍵索引並沒有保存完整的數據，所以都需要根據數據地址再次查詢數據。

事務的支持

InnoDB 與 MyISAM 另一個最大的區別就是 InnoDB 支持事務，而 MyISAM 不支持事務。事務也是 MySQL 另外一個常規問題，我們下面看下事務的相關知識。

事務

事務也是一個必問的知識點哦！

事務的四大特性 ACID

1. A: 原子性。原子性表示事務的操作是不可分割的，事務內的一系列操作全部成功事務才成功，任何一個操作失敗事務都是失敗，必須回滾。
2. C: 一致性。一致性表示事務在操作前和操作後數據都是處於一致的狀態。只表示在事務的操作前後的數據一致，但是並不是代表是正確。
3. I：隔離性。事務的併發操作是完全隔離的，不同的事務之間不會相互有影響。事務的隔離級別有四種
1. 讀未提交：表示一個事務內可以讀取到另一個事務未提交的數據內容，會出現髒讀，不可重複度，幻讀。
2. 讀已提交：表示一個事務內可以讀取另一個事務已經提交的內容，會出現不可重複讀和幻讀。
3. 可重複讀：表示一個事務內兩次相同條件讀的內容一致，但是會出現幻讀。
4. 串行化：沒有問題，但是效率低下。
4. D：持久性。事務操作結束過後，對數據的更改是可以持久化的，不管事務的操作是成功還是失敗，事務日誌都能保證事務的持久性。

髒讀：指一個事務 A 讀取到另一個事務 B 未提交的數據，也就是髒數據。

不可重複讀：指一個事務 A 第一次根據條件查詢某條數據，事務 B 修改了該數據並提交了，事務 A 第二次查詢與第一次查詢的不一致。

幻讀：指事務 A 根據條件查詢一批數據，事務 B 插入了新的滿足條件的數據，事務 A 再次查詢數據量變化了，兩次查詢的結果集不一致，出現了幻讀。

MySQL 的分區

為什麼分區

為什麼會有分區，以及什麼時候我們需要進行分區呢？我們都知道當 MySQL 單表的數量級達到一定的程度的時候，查詢就會很慢。那麼針對這種情況我們需要怎麼處理呢？除了本身查詢語句的優化之外，我們能想到的就是分庫分表以及分區了。

分庫分表是需要在應用層面上支持的，而分區是 MySQL 層面上支持的，在物理上將數據分成多個文件進行存儲，從而提高查詢速度。

如上圖表 partition_sys_log_op 是採用 InnoDB 引擎並且創建了八個分區的表結構。語句如下

<code>CREATE TABLE、 partition_ _sys_ 1og_ op° (
; id^ INT ( 10 ) UNSIGNED NOT NULL AUTO_ INCREMENT COMMENT‘ ID',
create_ timedatetime DEFAULT NULL COMENT‘操作時間’，
elapsed_ _time^ INT ( 11 ) DEFAULT NULL COMMENT ' 耗用時間"，
exec_ sq1^ MEDIUMTEXT CHARACTER
SET utf8 COMMENT'執行的語句'， 

user_ _id° INT ( 11 ) DEFAULT NULL COMMENT '執行人id',
user_ name’VARCHAR ( 45 ) CHARACTER
SET utf8 DEFAULT NULL COMMENT '執行人名稱',
exec_ type 、VARCHAR ( 45 ) CHARACTER
SET utf8 DEFAULT NULL COMMENT‘動作, delete update insert' ，
exec_ table^ VARCHAR ( 45 ) CHARACTER
SET utf8 DEFAULT NULL COMMENT '操作表',
PRIMARY KEY (、id^ ),
KEY `idx_index` ( `create_time`, `user_id`, `user_name`, `exec_table` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 COMMENT = '系統操作日誌' PARTITION BY KEY ( ) PARTITIONS 8;/<code>

分區的類型

1. Range：指定範圍內分區：PARTITION p1 VALUES LESS THAN (100)；
2. List：離散值分區，數據在枚舉範圍內：partition p1 values in (2,4,6,8,0) ；
3. Hash：計算單個列的 Hash值，然後指定分區數量，進行分區：PARTITION BY HASH(id) PARTITIONS 4;
4. Key：計算單個或者多個列的 hash 值，然後指定分區數量，進行分區：PARTITION BY KEY(s1) PARTITIONS 10; 不傳列名默認用主鍵。

Explain

前面提到我們經常需要對 sql 進行優化，那麼如何對一條 sql 進行優化呢？我們只要在普通的 sql 前面，加上關鍵字 explain 即可看執行計劃。根據執行計劃，我們可以看到該 sql 的執行情況，以及是否採用索引以及採用的是哪個索引。例如

EXPLAIN SELECT COUNT(*) FROM partition_sys_log_op WHERE create_time >= '2020-12-03 00:00:00';

閱讀更多 Java高級架構師 的文章

關鍵字: 知識點 人生第一份工作 收藏