mysql8.0 innodb 存儲引擎介紹 加倆類索引方法 btree hash ，三類索引類型 Normal Unique Full Text 業務應用中選擇思路，及官方各類存儲引擎對服務支援情況

目錄

​​1.介紹​​

​​2.使用InnoDB表的好處​​

​​2.1恢複-具體怎麼恢複有這麼幾種類型：​​

​​2.2擴充知識：BLOB和TEXT類型 ^​​

​​3.要是想要優化innodb可以看具體的8.0innodb表優化​​

​​3.1優化類型有這麼幾項​​

​​4.各類型索引及各類功能 對應 各類型存儲引擎支援表，總覽​​

​​4.1可以主看标紅的​​

​​4.2innodb 存儲引擎 和對應的給類型索引支援情況​​

​​5.索引類型​​

​​5.1Full Text​​

​​5.2Unique​​

​​5.3Normal​​

​​6.索引方法​​

​​7.mysql8.0 的 優化和索引​​

​​8.索引使用​​

​​9.Btree和hash索引的比較​​

​​9.1Btree​​

​​9.1.1以下WHERE子句使用索引：​​

​​9.1.2這些WHERE子句 不使用索引：​​

​​9.2哈希指數特征​​

​​10.索引方法、索引類型 總結政策​​

​​10.1索引方法​​

​​10.1.1Btree​​

​​10.1.2hash​​

​​10.2索引類型​​

​​10.2.1Normal​​

​​10.2.2Unique​​

​​10.2.3Full Text​​

​​11.應用分析​​

1.介紹

最近系統服務的資料越來越多開始考慮更新擴充，現将mysql資料庫的存儲引擎和索引類型，索引方法進行研究分析一下。

便于之後擴充使用，這裡進行一下官方文檔的一下介紹和總結的一些思路便于使用，這篇文章是mysql8.0版本的資訊。

有些事msyql5.6延續下來的，這裡8.0不錯 ok看内容哇

2.使用InnoDB表的好處

• 如果伺服器由于硬體或軟體問題而意外退出，無論當時資料庫中發生了啥，重新啟動資料庫後都無需執行任何特殊操作。​

  ​InnoDB​

  ​崩潰恢複會自動完成在崩潰之前送出的更改，并撤消正在處理但尚未送出的更改，進而使您可以重新開始并從上次中斷的地方繼續。

2.1恢複-具體怎麼恢複有這麼幾種類型：

• 時間點恢複
• 從資料損壞或磁盤故障中恢複
• innoDB崩潰恢複
• 故障恢複期間的表空間恢複

幾項優勢介紹

• ​

  ​InnoDB​

  ​存儲引擎維護自己的緩沖池，在主記憶體緩存表和索引資料作為資料被通路。經常使用的資料直接從記憶體中處理。此緩存适用于多種類型的資訊，并加快了處理速度。在專用資料庫伺服器上，通常最多将80％的實體記憶體配置設定給緩沖池。
• 如果将相關資料拆分到不同的表中，則可以設定強制引用完整性的外鍵。
• 如果資料在磁盤或記憶體中損壞，則校驗和機制會在使用前提醒您注意虛假資料  innodb_checksum_algorithm 變量定義由所用的校驗和算法 InnoDB。

• 當為每個表設計具有适當主鍵列的資料庫時，涉及這些列的操作會自動進行優化。在WHERE 子句，ORDER BY子句， GROUP BY 子句和聯接操作中引用主鍵列非常快速 。
• 插入，更新和删除通過稱為更改緩沖的自動機制進行了優化。​

  ​InnoDB​

  ​ 不僅允許對同一表的并發讀寫通路，而且還緩存更改的資料以簡化磁盤I / O。
• 性能優勢不僅限于具有長時間運作的查詢的大型表。當從表中一遍又一遍地通路相同的行時，自适應哈希索引将接管這些查詢，使它們的查找速度更快，就好像它們來自哈希表一樣。
• 可以壓縮表和關聯的索引
• 可以加密資料
• 可以建立和删除索引并執行其他DDL操作，而對性能和可用性的影響要小得多
• 截斷每表檔案表空間非常快，可以釋放磁盤空間供作業系統重用，而不是僅重用​

  ​InnoDB​

  ​
• BLOB使用DYNAMIC行格式， 對于長文本字段，表資料的存儲布局更為有效

2.2擴充知識：BLOB和TEXT類型 ^

• ABLOB是一個二進制大對象，可以容納可變數量的資料。這四個BLOB 類型TINYBLOB，BLOB， MEDIUMBLOB，和LONGBLOB。這些僅在它們可以容納的值的最大長度上有所不同。這四個TEXT類型 TINYTEXT，TEXT， MEDIUMTEXT，和LONGTEXT。這些對應于四種BLOB類型，并且具有相同的最大長度和存儲要求 。
• BLOB值被視為二進制字元串（位元組字元串）。它們具有binary 字元集和排序規則，并且比較和排序基于列值中位元組的數字值。 TEXT值被視為非二進制字元串（字元字元串）。它們具有以外的字元集 binary，并且根據字元集的排序規則對值進行排序和比較。

繼續說優勢：

• 可以通過查詢​

  ​INFORMATION_SCHEMA​

  ​表來監視存儲引擎的内部工作情況
• 可以通過查詢性能架構表來監視存儲引擎的性能詳細資訊
• 可以将InnoDB表與其他MySQL存儲引擎的表混合使用，即使在同一條語句中也可以。例如，可以使用聯接操作在單個查詢中合并來自InnoDB和 MEMORY表的資料 。
• ​

  ​InnoDB​

  ​ 設計用于處理大資料量時的CPU效率和最佳性能。
• ​

  ​InnoDB​

  ​ 表可以處理大量資料，即使在檔案大小限制為2GB的作業系統上也一樣。

3.要是想要優化innodb可以看具體的8.0innodb表優化

3.1優化類型有這麼幾項

• 1優化InnoDB表的存儲布局
• 2優化InnoDB事務管理
• 3優化InnoDB隻讀事務
• 4優化InnoDB重做日志
• 5 InnoDB表的批量資料加載
• 6優化InnoDB查詢
• 7優化InnoDB DDL操作
• 8優化InnoDB磁盤I / O
• 9優化InnoDB配置變量
• 10為具有多個表的系統優化InnoDB

主要優勢就是上邊這些了，這裡還加了部分的知識擴充，便于了解，如優化、BLOB、恢複

4.各類型索引及各類功能 對應 各類型存儲引擎支援表，總覽

4.1可以主看标紅的

特征、功能、索引	特征、功能、索引	MyISAM	Memory	InnoDB	Archive	NDB
B-tree indexes	B樹索引	是	是	是	沒有	沒有
Backup/point-in-time recovery (note 1)	備份/時間點恢複（注釋1）	是	是	是	是	是
Cluster database support	叢集資料庫支援	沒有	沒有	沒有	沒有	是
Clustered indexes	聚集索引	沒有	沒有	是	沒有	沒有
Compressed data	壓縮資料	是（注釋2）	沒有	是	是	沒有
Data caches	資料快取	沒有	不适用	是	沒有	是
Encrypted data	加密資料	是（注釋3）	是（注釋3）	是（注釋4）	是（注釋3）	是（注釋3）
Foreign key support	外鍵支援	沒有	沒有	是	沒有	是（注釋5）
Full-text search indexes	全文搜尋索引	是	沒有	是（注釋6）	沒有	沒有
Geospatial data type support	地理空間資料類型支援	是	沒有	是	是	是
Geospatial indexing support	地理空間索引支援	是	沒有	是（注釋7）	沒有	沒有
Hash indexes	哈希索引	沒有	是	否（注釋8）	沒有	是
Index caches	索引緩存	是	不适用	是	沒有	是
Locking granularity	鎖定粒度	表	表	行	行	行
MVCC	MVCC	沒有	沒有	是	沒有	沒有
Replication support (note 1)	複制支援（注釋1）	是	限量（附注9）	是	是	是
Storage limits	儲存限制	256TB	記憶體	64TB	沒有	384EB
T-tree indexes	T樹索引	沒有	沒有	沒有	沒有	是
Transactions	交易次數	沒有	沒有	是	沒有
Update statistics for data dictionary	更新資料字典的統計資訊	是	是	是	是	是

4.2innodb 存儲引擎 和對應的給類型索引支援情況

特征、功能、索引	特征、功能、索引	支援情況
B-tree indexes	B樹索引	是
Backup/point-in-time recovery (Implemented in the server, rather than in the storage engine.)	備份/時間點恢複（在伺服器中而不是在存儲引擎中實作。）	是
Cluster database support	叢集資料庫支援	沒有
Clustered indexes	聚集索引	是
Compressed data	壓縮資料	是
Data caches	資料快取	是
Encrypted data	加密資料	是（通過加密功能在伺服器中實作；在MySQL 5.7和更高版本中，支援靜态資料加密。）
Foreign key support	外鍵支援	是
Full-text search indexes	全文搜尋索引	是（MySQL 5.6及更高版本中提供了對FULLTEXT索引的支援。）
Geospatial data type support	地理空間資料類型支援	是
Geospatial indexing support	地理空間索引支援	是（在5.7和更高版本中提供了對地理空間索引的支援。）
Hash indexes	哈希索引	否（InnoDB在内部将哈希索引用于其自适應哈希索引功能。）
Index caches	索引緩存	是
Locking granularity	鎖定粒度	行
MVCC	MVCC	是
Replication support (Implemented in the server, rather than in the storage engine.)	複制支援（在伺服器中而不是在存儲引擎中實作。）	是
Storage limits	儲存限制	64TB
T-tree indexes	T樹索引	沒有
Transactions	交易次數	是
Update statistics for data dictionary	更新資料字典的統計資訊	是

5.索引類型

5.1Full Text

全文索引，一般資料文本多的使用

​

​FULLTEXT​

​​索引用于全文搜尋。隻有InnoDB​和 MyISAM​存儲引擎支援 ​

​FULLTEXT​

​​索引和僅适用于 CHAR​， VARCHAR​和 TEXT列。索引始終在整個列上進行，并且不支援列字首索引。

5.2Unique

這個是唯一索引，全表唯一的一個

5.3Normal

這個是普通索引

6.索引方法

7.mysql8.0 的 優化和索引

• 改善操作性能的最佳方法 SELECT是在查詢中測試的一個或多個列上建立索引。索引條目的作用類似于指向表行的指針，進而使查詢可以快速确定哪些行與WHERE子句中的條件比對，并檢索這些行的其他列值。所有MySQL資料類型都可以建立索引。
• 盡管可能會為查詢中使用的每個可能的列建立索引，但不必要的索引會浪費空間和時間，使MySQL難以确定要使用的索引。索引還會增加插入，更新和删除的成本，因為必須更新每個索引。您必須找到适當的平衡，才能使用最佳索引集來實作快速查詢。

8.索引使用

大多數MySQL索引（PRIMARY KEY， UNIQUE，INDEX和 FULLTEXT）存儲在 B樹。例外：空間資料類型的索引使用R樹；MEMORY 表還支援哈希索引; InnoDB對FULLTEXT索引使用倒排清單。

​

​WHERE​

​快速 查找與子句比對的行

從考慮中消除行。如果可以在多個索引之間進行選擇，MySQL通常會使用找到最少行數的索引

如果表具有多列索引，那麼優化器可以使用索引的任何最左字首來查找行。舉例來說，如果你有一個三列的索引 ​

​(col1, col2, col3)​

​​，你有索引的搜尋功能​

​(col1)​

​​， ​

​(col1, col2)​

​​以及​

​(col1, col2, col3)​

​。

不是同一款字元集，索引會被排除使用 如utf-8 和 ​

​latin1​

​列進行比較會排除使用索引

9.Btree和hash索引的比較

9.1Btree

A B樹索引可以在使用表達式中使用的對列的比較 =， >， >=， <， <=，或BETWEEN營運商。

LIKE 如果to的參數LIKE是不以通配符開頭的常量字元串，則索引也可以用于比較 。

9.1.1以下WHERE子句使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

    /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2

    /* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5

    /* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;      

9.1.2這些WHERE子句 不使用索引：

/* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2

    /*  Index is not used in both parts of the WHERE clause  */
... WHERE index=1 OR A=10

    /* No index spans all rows  */
... WHERE index_part1=1 OR index_part2=10      

有時，即使索引可用，MySQL也不使用索引。發生這種情況的一種情況是，優化器估計使用索引将需要MySQL通路表中很大比例的行。（在這種情況下，表掃描可能會更快，因為它需要更少的查找。）但是，如果這樣的查詢​

​LIMIT​

​僅用于檢索某些行，則MySQL仍将使用索引，因為它可以更快地找到索引。幾行傳回結果。

9.2哈希指數特征

哈希索引與Btree索引具有一些不同的特征：

• 它們僅用于使用​

  ​=​

  ​or​

  ​<=>​

  ​ 運算符的相等比較 （但非常快）。它們不用于比較運算符，例如​

  ​<​

  ​用于查找值範圍的運算符 。依賴于這種單值查找類型的系統稱為“鍵值存儲”；要将MySQL用于此類應用程式，請盡可能使用哈希索引。
• 優化器無法使用哈希索引來加速​

  ​ORDER BY​

  ​操作。（此索引類型不能用于按順序搜尋下一個條目。）
• MySQL無法确定兩個值之間大約有多少行（範圍優化器使用它來決定要使用哪個索引）。如果将​

  ​MyISAM​

  ​或 ​

  ​InnoDB​

  ​表更改為哈希索引 ​

  ​MEMORY​

  ​表，這可能會影響某些查詢。
• 僅整個鍵可用于搜尋行。（對于B樹索引，鍵的任何最左邊的字首都可用于查找行。）

10.索引方法、索引類型 總結政策

10.1索引方法

10.1.1Btree

業務項目，有需要對資料範圍比大小的方式查詢資料，這種類型可以采用Btree 索引方法 

btree 可以比較範圍 >  <  <=  >= 這類型比較友善

10.1.2hash

業務項目，如有的金額，單獨金額對應單獨金額查詢，或單獨的，通過元件查詢對應的值，這種可以使用Hash 索引方法

hash 索引将索引内容進行了hash 索引索引值比較比= 或<=> 會很快

10.2索引類型

10.2.1Normal

普通索引，沒啥限制

10.2.2Unique

唯一值得實惠便于使用 key 使用

10.2.3Full Text

全文索引，這玩意文字特别多的時候使用比較好

11.應用分析

是以使用的時候更具10.索引方法、索引類型 總結政策，選擇需要使用的類型

更具具體的業務需要使用索引，項目書記大了之後速度也就上來了

ok

ok