MySQL之存儲引擎及索引

本節索引：

一、MySQL存儲引擎介紹

二、InnoDB與MyISAM對比

三、MVCC-多版本的并發控制協議

四、索引INDEX

 

一、MySQL存儲引擎介紹

存儲引擎是什么？

例如，如果你在研究大量的臨時數據，你也許需要使用內存存儲引擎。內存存儲引擎

能夠在內存中存儲所有的表格數據。又或者，你也許需要一個支持事務處理的數據庫

(以確保事務處理不成功時數據的回退能力)。

這些不同的技術以及配套的相關功能在MySQL中被稱作存儲引擎。

下圖是MySQL體系結構：

 

MySQL的存儲引擎是MySQL體系架構中的重要組成部分，也是MySQL體系結構的核

心，它處于MySQL體系架構中Server端底層，是底層物理結構的實現，用于將數據以

各種不同的技術方式存儲到文件或者內存中，不同的存儲引擎具備不同的存儲機制、索

引技巧和鎖定水平。常見的MySQL存儲引擎有InnoDB、MyISAM、Memory、Archive

等等，它們具備各自的特征，我們可以根據不同的具體應用來建立對應的存儲引擎表。

 

MySQL常見的存儲引擎有：

InnodDB：5.5版本之后的默認存儲引擎，事務型數據庫的首選引擎，支持ACID事務，支持

行級鎖定

MyISAM：擁有較高的插入，查詢速度，但不支持事務5.5版本之前的默認存儲引擎

Performance_Schema：Performance_Schema數據庫

Memory ：將所有數據存儲在RAM中，以便在需要快速查找參考和其他類似數據的環境中進

行快速訪問。適用存放臨時數據。引擎以前被稱為HEAP引擎MRG_MyISAM：使MySQL DBA

或開發人員能夠對一系列相同的MyISAM表進行邏輯分組，并將它們作為一個對象引用。適用

于VLDB(Very Large DataBase)環境，如數據倉庫

Archive ：為存儲和檢索大量很少參考的存檔或安全審核信息，只支持SELECT和INSERT操

作；支持行級鎖和專用緩存區

Federated聯合：用于訪問其它遠程MySQL服務器一個代理，它通過創建一個到遠程MySQL

服務器的客戶端連接，并將查詢傳輸到遠程服務器執行，而后完成數據存取，提供鏈接單獨

MySQL服務器的能力，以便從多個物理服務器創建一個邏輯數據庫。非常適合分布式或數據集

市環境

BlackHole ：黑洞引擎，寫入的任何數據都會消失，一般用于記錄binlog做復制的中繼

 

MariaDB支持的其它存儲引擎：

OQGraph

SphinxSE

TokuDB

Cassandra

CONNECT

SQUENCE

 

下圖是MySQL常見存儲引擎比較：

InnoDB support for FULLTEXT indexes(全文索引) is available in MySQL 5.6.4 and later.

存儲引擎比較：https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-engines.html

 

作為MySQL數據庫發展過程中的默認引擎，接下來我們重點介紹下InnodDB及MyISAM存儲引擎

 

 

二、InnoDB與MyISAM對比

MyISAM存儲引擎

MyISAM特性：

不支持事務

表級鎖定

讀寫相互阻塞，寫入不能讀，讀時不能寫

只緩存索引

不支持外鍵約束

不支持聚簇索引

讀取數據較快，占用資源較少

不支持MVCC（多版本并發控制機制）高并發

崩潰恢復性較差

MySQL5.5.5前默認的數據庫引擎

適用場景：只讀（或者寫較少）、表較?。梢越邮荛L時間進行修復操作）

MyISAM引擎文件：

tbl_name.frm: 表格式定義

tbl_name.MYD: 數據文件

tbl_name.MYI: 索引文件

 

InnoDB存儲引擎

InnoDB特性：

行級鎖

支持事務，適合處理大量短期事務

讀寫阻塞與事務隔離級別相關

可緩存數據和索引

支持聚簇索引

崩潰恢復性更好

支持MVCC高并發

從MySQL5.5后支持全文索引

從MySQL5.5.5開始為默認的數據庫引擎

 

InnoDB數據庫文件：

所有InnoDB表的數據和索引放置于同一個表空間中

表空間文件：datadir定義的目錄下

數據文件：ibddata1, ibddata2, …

每個表單獨使用一個表空間存儲表的數據和索引

啟用：innodb_file_per_table=ON

兩類文件放在數據庫獨立目錄中

數據文件(存儲數據和索引)：tb_name.ibd

表格式定義：tb_name.frm

 

 

管理存儲引擎

查看mysql支持的存儲引擎:

show engines;

 

查看當前默認的存儲引擎:

show variables like ‘%storage_engine%’;

 

查看庫中所有表使用的存儲引擎

show table status from db_name;

 

查看庫中指定表的存儲引擎

show table status like ‘ tb_name ‘;

show create table tb_name;

 

設置表的存儲引擎：

CREATE TABLE tb_name(… ) ENGINE=InnoDB;

ALTER TABLE tb_name ENGINE=InnoDB;

 

操作：修改MySQL默認的存儲引擎

vim /etc/my.conf

[mysqld]

default_storage_engine= InnoDB;

重啟mysql服務生效

 

操作：修改MySQL中Innodb引擎每個表有獨立的數據文件

vim /etc/my.cnf

[mysqld]

innodb_file_per_table

重啟mysql服務生效

 

三、MVCC-多版本的并發控制協議

MySQL InnoDB存儲引擎，實現的是基于多版本的并發控制協議——MVCC (Multi-Version

?Concurrency Control) (注：與MVCC相對的，是基于鎖的并發控制，Lock-Based

Concurrency Control)。MVCC最大的好處，相信也是耳熟能詳：讀不加鎖，讀寫不沖突。

在讀多寫少的OLTP應用中，讀寫不沖突是非常重要的，極大的增加了系統的并發性能。

 

InnoDB在每行數據都增加兩個隱藏字段，一個記錄創建的版本號，一個記錄刪除的版本號。

 

* SELECT：

當隔離級別是REPEATABLE READ時select操作，InnoDB必須每行數據來保證它符合兩個條件：

1、InnoDB必須找到一個行的版本，它至少要和事務的版本一樣老(也即它的版本號不大于

事務的版本號)。這保證了不管是事務開始之前，或者事務創建時，或者修改了這行數據的

時候，這行數據是存在的。

2、這行數據的刪除版本必須是未定義的或者比事務版本要大。這可以保證在事務開始之前

這行數據沒有被刪除。

符合這兩個條件的行可能會被當作查詢結果而返回。

* INSERT：<br>InnoDB為這個新行記錄當前的系統版本號。

* DELETE：<br>InnoDB將當前的系統版本號設置為這一行的刪除ID。

* UPDATE：<br>InnoDB會寫一個這行數據的新拷貝，這個拷貝的版本為當前的系統版本號。

它同時也會將這個版本號寫到舊行的刪除版本里。

 

在進行InnoDB與MyISAM引擎的對比時，我們還提到了索引的概念，什么是MySQL

數據庫的索引呢？

 

 

四、索引INDEX

如同圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。在關系數據庫中，索引是一種單

獨的、物理的對數據庫表中一列或多列的值進行排序的一種存儲結構，它是某個表中一列或若干

列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。

 

索引提供指向存儲在表的指定列中的數據值的指針，然后根據您指定的排序順序對這些指針排序。

數據庫使用索引以找到特定值，然后順指針找到包含該值的行。這樣可以使對應于表的SQL語句

執行得更快，可快速訪問數據庫表中的特定信息。

 

作用：

1.快速取數據；

2.保證數據記錄的唯一性；

3.實現表與表之間的參照完整性；

4.在使用ORDER by、group by子句進行數據檢索時，利用索引可以減少排序和

分組的時間。

 

 

優點：

1.大大加快數據的檢索速度;

2.創建唯一性索引，保證數據庫表中每一行數據的唯一性;

3.加速表和表之間的連接;

4.在使用分組和排序子句進行數據檢索時，可以顯著減少查詢中分組和排序的時間。

 

缺點：

1.索引需要占物理空間。

2.當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，降低了數

據的維護速度。

 

 

 

索引類型：

聚簇（集）索引、非聚簇索引：數據和索引存儲順序是否一致

注：MyISAM不支持聚簇索引

主鍵索引、輔助索引（二級索引）

稠密索引、稀疏索引：是否索引了每一個數據項

B+ TREE、HASH、R TREE

簡單索引、組合索引

左前綴索引：取前面的字符做索引

覆蓋索引：從索引中即可取出要查詢的數據，性能高

 

 

B+ Tree索引

順序存儲，每一個葉子節點到根結點的距離是相同的；左前綴索引，適合查詢范圍類的數據

可以使用B-Tree索引的查詢類型：

全值匹配：精確所有索引列，如：姓wang，名xiaochun，年齡30

匹配最左前綴：即只使用索引的第一列，如：姓wang

匹配列前綴：只匹配一列值開頭部分，如：姓以w開頭的

匹配范圍值：如：姓ma和姓wang之間

精確匹配某一列并范圍匹配另一列：如：姓wang,名以x開頭的

只訪問索引的查詢

B-Tree索引的限制：

如果不從最左列開始，則無法使用索引：如：查找名為xiaochun，或姓為g結尾

不能跳過索引中的列：如：查找姓wang,年齡30的，只能使用索引第一列

如果查詢中某個列是為范圍查詢，那么其右側的列都無法再使用索引：如：

姓wang,名x%,年齡30，只能利用姓和名上面的索引

特別提示：

索引列的順序和查詢語句的寫法應相匹配，才能更好的利用索引

為優化性能，可能需要針對相同的列但順序不同創建不同的索引來滿足不同

類型的查詢需求

 

 

 

Hash索引

基于哈希表實現，只有精確匹配索引中的所有列的查詢才有效，索引自身只存儲索引列對應

的哈希值和數據指針，索引結構緊湊，查詢性能好只有Memory存儲引擎支持顯式hash索引

適用場景：

只支持等值比較查詢，包括=, <=>, IN(),

不適合使用hash索引的場景：

不適用于順序查詢：索引存儲順序的不是值的順序

不支持模糊匹配

不支持范圍查詢

不支持部分索引列匹配查找：如A，B列索引，只查詢A列索引無效

 

空間索引（R-Tree）：

MyISAM支持空間索引，可以使用任意維度組合查詢，使用特有的函數訪問，

常用于做地理數據存儲，使用不多

 

全文索引(FULLTEXT)：

在文本中查找關鍵詞，而不是直接比較索引中的值，類似搜索引擎

InnoDB support for FULLTEXT indexes(全文索引) is available in MySQL 5.6.4 and later.

 

聚簇索引與非聚簇索引

聚簇索引是順序結構與數據存儲物理結構一致的一種索引，并且一個表的聚簇

索引只能有唯一的一條。MyISAM不支持聚簇索引。

 

非聚簇索引記錄的物理順序與邏輯順序沒有必然的聯系，與數據的存儲物理結構

沒有關系；一個表對應的非聚簇索引可以有多條，根據不同列的約束可以建立不

同要求的非聚簇索引；

 

索引優化建議：

獨立地使用列：盡量避免其參與運算，獨立的列指索引列不能是表達式的一

部分，也不能是函數的參數，在where條件中，始終將索引列單獨放在比較

符號的一側

左前綴索引：構建指定索引字段的左側的字符數，要通過索引選擇性來評估

索引選擇性：不重復的索引值和數據表的記錄總數的比值

多列索引：AND操作時更適合使用多列索引，而非為每個列創建單獨的索引

選擇合適的索引列順序：無排序和分組時，將選擇性最高放左側

 

只要列中含有NULL值，就最好不要在此例設置索引，復合索引如果有NULL值，

此列在使用時也不會使用索引

盡量使用短索引，如果可以，應該制定一個前綴長度

對于經常在where子句使用的列，最好設置索引

對于有多個列where或者order by子句，應該建立復合索引

對于like語句，以%或者‘-’開頭的不會使用索引，以%結尾會使用索引

盡量不要在列上進行運算（函數操作和表達式操作）

盡量不要使用not in和<>操作