MySQL數據庫入門到大蛇尚硅谷宋紅康老師筆記高級篇 part 5

第05章_存儲引擎

為了管理方便，人們把連接管理、查詢緩存、語法解析、查詢優化這些并不涉及真實數據存儲的功能劃分為MySQLserver的功能，把真實存取數據的功能劃分為存儲引擎的功能。所t以在MySQLserver完成了查詢優化后，只需按照生成的執行計劃調用底層存儲引擎提供的API，獲取到數據后返回給客戶端就好了。
MySQL中提到了存儲引擎的概念。簡而言之，存儲引擎就是指表的類型。其實存儲引擎以前叫做表處理器，后來改名為存儲引擎，它的功能就是接收上層傳下來的指令，然后對表中的數據進行提取或寫入操作。

show engines;  即可查看存儲引擎

??2.設置系統默認的存儲引擎

查看默認的存儲引擎：
show variables like '%storage_engine%';
#或
SELECT @@default_storage_engine;

如果在創建表的語句中沒有顯式指定表的存儲引擎的話，那就會默認使用InnoDB作為表的存儲引擎。如果我們想改變表的默認存儲引擎的話，可以這樣寫啟動服務器的命令行：

SET DEFAULT_STORAGE_ENGINE=MyISAM;

·修改默認的存儲引擎
如果在創建表的語句中沒有顯式指定表的存儲引擎的話，那就會默認使用InnoDB作為表的存儲引擎。如果我們想改變表的默認存儲引擎的話，可以這樣寫啟動服務器的命令行：

SET DEFAULT_STORAGE_ENGINE=MyISAM;或者修改my.cnf文件：
deflault-storage-engine=MyISAM
#重啟服務
systemctl restart mysqld.service

接下來我們

show variables like 'datadir'當我們指明類型時,比如:
create table emp4(id int) engine=InnodB;   #MyISAM
再cd一下   cd /var/lib/mysql
ll查看,發現沒修改InnodB之前是ibd的格式,修改后MyISAM則為sdi和MYD,MYI的格式

4.引擎介紹

4.1InnoDB引擎：具備外鍵支持功能的事務存儲引擎 (前面提過實際上很多時候不建議使用外鍵)

MySQL從3.23.34a開始就包含lnnoDB存儲引擎。大于等于5.5之后，默認采用InnoDB引擎。
InnoDB是MySQL的默認事務型引擎，它被設計用來處理大量的短期（short-lived）事務。可以確保事務的完整提交(Commit)和回滾(Rollback)。
比如說現在有2個DML操作,分別是小a- 100元和小b +100元
除了增加和查詢外，還需要更新、刪除操作，那么，應優先選擇InnoDB存儲引擎。
除非有非常特別的原因需要使用其他的存儲引擎，否則應該優先考慮InnoDB引擎。
數據文件結構：（在《第o2章_MySQL數據目錄》章節已講）
1. 表名.frm存儲表結構（MySQL8.0時，合并在表名.ibd中）
2. 表名.ibd存儲數據和索引
InnoDB是為處理巨大數據量的最大性能設計。
在以前的版本中，字典數據以元數據文件、非事務表等來存儲。現在這些元數據文件被刪除了。比如：.frm，·par，.trn，.isl，.db.opt等都在MysQL8.o中不存在了。
對比MyISAM的存儲引擎，InnoDB寫的處理效率差一些，并且會占用更多的磁盤空間以保存數據和索引。即更擅長事物和大規模數據,簡單的增刪改查不如MyISAM
MyISAM只緩存索引，不緩存真實數據；InnoDB不僅緩存索引引還要緩存真實數據，對內存要求較高，而且內存大小對性能有決定性的影響。

?課外補充：
1、InnoDB表的優勢
InnoDB存儲引擎在實際應用中擁有諸多優勢，比如操作便利、提高了數據庫的性能、維護成本低等。如果由于硬件或軟件的原因導致服務器崩潰，那么在重啟服務器之后不需要進行額外的操作。InnoDB崩潰恢復功能自動將之前提交的內容定型，然后撤銷沒有提交的進程，重啟之后繼續從崩潰點開始執行。
InnoDB存儲引擎在主內存中維護緩沖池，高頻率使用的數據將在內存中直接被處理。這種緩存方式應用于多種信息，加速了處理進程。
在專用服務器上，物理內存中高達80%的部分被應用于緩沖池。如果需要將數據插入不同的表中，可以設置外鍵加強數據的完整性。更新或者刪除數據，關聯數據將會被自動更新或刪除。如果試圖將數據插入從表，但在主表中沒有對應的數據，插入的數據將被自動移除。

如果磁盤或內存中的數據出現崩潰，在使用臟數據之前，校驗和機制會發出警告。當每個表的主鍵都設置合理時，與這些列有關的操作會被自動優化。插入、更新和刪除操作通過做改變緩沖自動機制進行優化。InnoDB不僅支持當前讀寫，也會緩沖改變的數據到數據流磁盤。

?4.2MyISAM引擎：主要的非事務處理存儲引|擎

·MyISAM提供了大量的特性，包括全文索引I、壓縮、空間函數（GIS)等，但MyISAM不支持事務、行級鎖、外鍵，有一個毫無疑問的缺陷就是崩潰后無法安全恢復。

·5.5之前默認的存儲引擎
·優勢是訪問的速度快，對事務完整性沒有要求或者以SELECT、INSERT為主的應用
·針對數據統計有額外的常數存儲。故而count(*)的查詢效率很高
·數據文件結構：（在《第02章_MySQL數據目錄》章節已講）
1. 表名.frm存儲表結構
2. 表名.MYD存儲數據（MYData)
3. 表名.MYI存儲索引(MYIndex)
·應用場景：只讀應用或者以讀為主的業務

對比項	MyISAM	InnoDB
外鍵	不支持	支持
事務	不支持	支持
行表鎖	表鎖，即使操作一條記錄也會鎖住整個表，不適合高并發的操作	行鎖，操作時只鎖某一行，不對其它行有影響，適合高并發的操作
緩存	只緩存索引，不緩存真實數據	不僅緩存索引還要緩存真實數據，對內存要求較高，而且內存大小對性能有決定性的影響
自帶系統表使用	Y	N
關注點	性能：節省資源、消耗少、簡單業務	事務：并發寫、事務、更大資源
默認安裝	Y	I
默認使用	N	Y

以下的其實就相對不常見了

4.3Archive引擎：用于數據存檔

archive是歸檔的意思，僅僅支持插入和查詢兩種功能（行被插入后不能再修改）。
在MySQL5.5以后支持索引功能。
擁有很好的壓縮機制，使用zlib壓縮庫，在記錄請求的時候實時的進行壓縮，經常被用來作為倉庫使用。
創建ARCHIVE表時，存儲引擎會創建名稱以表名開頭的文件。數據文件的擴展名為??.ARZ。
根據英文的測試結論來看，同樣數據量下，Archive表比MyISAM表要小大約75%，比支持事務處理的InnoDB表小大約83%。
ARCHIVE存儲引擎采用了行級鎖。該ARCHIVE引擎支持AUTO_INCREMENT列屬性 . AUTO_INCREMENT列可以具有唯一索引或非唯一索引。嘗試在任何其他列上創建索引會導致錯誤。
Archive表適合日志和數據采集（檔案）類應用；適合存儲大量的獨立的作為歷史記錄的數據。擁有很高的插入速度，但是對查詢的支持較差。
下表展示了ARCHIVE存儲引擎功能

4.4? Blackhole引擎：丟棄寫操作，讀操作會返回空內容

Blackhole引擎沒有實現任何存儲機制，它會丟棄所有插入的數據，不做任何保存。
但服務器會記錄Blackhole表的日志，所以可以用于復制數據到備庫，或者簡單地記錄到日志。但這種應用方式會碰到很多問題，因此并不推薦。

4.5? CSV引擎：存儲數據時，以逗號分隔各個數據項

·CSV引擎可以將普通的CSV文件作為MySQL的表來處理，但不支持索引。
·CSV引擎可以作為一種數據交換的機制，非常有用。數模中常見啊😭😭😭😭😭
·CSV存儲的數據直接可以在操作系統里，用文本編輯器，或者excel讀取。
·對于數據的快速導入、導出是有明顯優勢的。
創建CSV表時，服務器會創建一個純文本數據文件，其名稱以表名開頭并帶有.CSV擴展名。當你將數據存儲到表中時，存儲引擎將其以逗號分隔值格式保存到數據文件中。?

mysql> create table csv_demo(id int,name char (20)) engine=csv;
報錯了,說對著這種表不支持空列,因此有加入空列
因此應該是:
create table csv_demo(id int not null ,name char (20) not null) engine=csv;

?ll后發現 csv_demo.CSV的文件和CSM的源文件

?4.6Memory引擎：置于內存的表

概述：Memory采用的邏輯介質是內存，響應速度很快，但是當mysqld守護進程崩潰的時候數據會丟失。另外，要求存儲的數據是數據長度不變的格式，比如，Blob和Text類型的數據不可用(長度不固定的)。

主要特征：

Memory同時支持哈希（HASH）索引和B+樹索引。
1. 哈希索引相等的比較快，但是對于范圍的比較慢很多。
2. 默認使用哈希（HASH）索引，其速度要比使用B型樹（BTREE）索引快。
3. 如果希望使用B樹索引，可以在創建索引時選擇使用。
Memory表至少比MyISAM表要快一個數量級。
MEMORY表的大小是受到限制的。表的大小主要取決于兩個參數，分別是max_rows和
max_heap_table_size。其中，max_rows可以在創建表時指定；max_heap_table_size的大小默認為16MB，可以按需要進行擴大。
·數據文件與索引文件分開存儲。
1. 每個基于MEMORY存儲引擎的表實際對應一個磁盤文件，該文件的文件名與表名相同，類型為frm類型，該文件中只存儲表的結構，而其數據文件都是存儲在內存中的。
2. 這樣有利于數據的快速處理，提供整個表的處理效率。
缺點：其數據易丟失，生命周期短。基于這個缺陷，選擇MEMORY存儲引擎時需要特別小心。

使用Memory存儲引擎的場景：1．目標數據比較小，而且非常頻繁的進行訪問，在內存中存放數據，如果太大的數據會造成內存溢出。可以通過參數max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小。2.如果數據是臨時的，而且必須立即可用得到，那么就可以放在內存中。3.存儲在Memory表中的數據如果突然間丟失的話也沒有太大的關系。

4.7Federated引l擎：訪問遠程表

Federated引擎是訪問其他MySQL服務器的一個代理，盡管該引擎看起來提供了一種很好的跨服務器的靈活性，但也經常帶來問題，因此默認是禁用的。

4.8Merge引l擎：管理多個MylSAM表構成的表集合

4.9NDB引擎：MySQL集群專用存儲引擎

也叫做NDBCluster存儲引擎，主要用于MySQLCluster分布式集群環境，類似于Oracle的RAC集群。

6.阿里巴巴、淘寶用哪個

Percona為MySQL數據庫服務器進行了改進，在功能和性能上較MySQL有很顯著的提升。
該版本提升了在高負載情況下的InnoDB的性能、為DBA提供一些非常有用的性能診斷工具；另外有更多的參數和命令來控制服務器行為。
該公司新建了一款存儲引擎叫Xtradb完全可以替代Innodb，并且在性能和并發上做得更好
阿里巴巴大部分mysq數據庫其實使用的percona的原型加以修改。