Python 數(shù)據(jù)庫(kù)之表操作mysql中的存儲(chǔ)引擎

mysql中的存儲(chǔ)引擎

一、mysql存儲(chǔ)引擎概述

1、什么是存儲(chǔ)引擎？

MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲(chǔ)在文件（或者內(nèi)存）中。這些技術(shù)中的每一種技術(shù)都使用不同的存儲(chǔ)機(jī)制、索引技巧、鎖定水平并且最終提供廣泛的不同的功能和能力。通過(guò)選擇不同的技術(shù)，你能夠獲得額外的速度或者功能，從而改善你的應(yīng)用的整體功能。

例如，如果你在研究大量的臨時(shí)數(shù)據(jù)，你也許需要使用內(nèi)存存儲(chǔ)引擎。內(nèi)存存儲(chǔ)引擎能夠在內(nèi)存中存儲(chǔ)所有的表格數(shù)據(jù)。又或者，你也許需要一個(gè)支持事務(wù)處理的數(shù)據(jù)庫(kù)(以確保事務(wù)處理不成功時(shí)數(shù)據(jù)的回退能力)。

這些不同的技術(shù)以及配套的相關(guān)功能在MySQL中被稱(chēng)作存儲(chǔ)引擎(也稱(chēng)作表類(lèi)型)。

MySQL默認(rèn)配置了許多不同的存儲(chǔ)引擎，可以預(yù)先設(shè)置或者在MySQL服務(wù)器中啟用。你可以選擇適用于服務(wù)器、數(shù)據(jù)庫(kù)和表格的存儲(chǔ)引擎，以便在選擇如何存儲(chǔ)你的信息、如何檢索這些信息以及你需要你的數(shù)據(jù)結(jié)合什么性能和功能的時(shí)候?yàn)槟闾峁┳畲蟮撵`活性。

選擇如何存儲(chǔ)和檢索你的數(shù)據(jù)的這種靈活性是MySQL為什么如此受歡迎的主要原因。其它數(shù)據(jù)庫(kù)系統(tǒng)(包括大多數(shù)商業(yè)選擇)僅支持一種類(lèi)型的數(shù)據(jù)存儲(chǔ)。

遺憾的是，其它類(lèi)型的數(shù)據(jù)庫(kù)解決方案采取的“一個(gè)尺碼滿(mǎn)足一切需求”的方式意味著你要么就犧牲一些性能，要么你就用幾個(gè)小時(shí)甚至幾天的時(shí)間詳細(xì)調(diào)整你的數(shù)據(jù)庫(kù)。使用MySQL，我們僅需要修改我們使用的存儲(chǔ)引擎就可以了

2、mysql支持哪些存儲(chǔ)引擎？

MySQL服務(wù)器采用了多層設(shè)計(jì)和獨(dú)立模塊，插件式存儲(chǔ)引擎體系結(jié)構(gòu)，允許將存儲(chǔ)引擎加載到正在運(yùn)新的MySQL服務(wù)器中，圖中的Pluggable Storage Engines部分。采用MySQL服務(wù)器體系結(jié)構(gòu)，由于在存儲(chǔ)級(jí)別上（也就是Pluggable Storage Engines）提供了一致和簡(jiǎn)單的應(yīng)用模型和API，應(yīng)用程序編程人員和DBA可不再考慮所有的底層實(shí)施細(xì)節(jié)。因此，盡管不同的存儲(chǔ)引擎具有不同的能力，應(yīng)用程序是與之分離的。存儲(chǔ)引擎就司職與文件系統(tǒng)打交道了。

各種存儲(chǔ)引擎的特性：

并發(fā)性：某些應(yīng)用程序比其他應(yīng)用程序具有很多的顆粒級(jí)鎖定要求（如行級(jí)鎖定）。 事務(wù)支持：并非所有的應(yīng)用程序都需要事務(wù)，但對(duì)的確需要事務(wù)的應(yīng)用程序來(lái)說(shuō)，有著定義良好的需求，如ACID兼容等。 引用完整性：通過(guò)DDL定義的外鍵，服務(wù)器需要強(qiáng)制保持關(guān)聯(lián)數(shù)據(jù)庫(kù)的引用完整性。 物理存儲(chǔ)：它包括各種各樣的事項(xiàng)，從表和索引的總的頁(yè)大小，到存儲(chǔ)數(shù)據(jù)所需的格式，到物理磁盤(pán)。 索引支持：不同的應(yīng)用程序傾向于采用不同的索引策略，每種存儲(chǔ)引擎通常有自己的編制索引方法，但某些索引方法（如B-tree索引）對(duì)幾乎所有的存儲(chǔ)引擎來(lái)說(shuō)是共同的。 內(nèi)存高速緩沖：與其他應(yīng)用程序相比，不同的應(yīng)用程序?qū)δ承﹥?nèi)存高速緩沖策略的響應(yīng)更好，因此，盡管某些內(nèi)存高速緩沖對(duì)所有存儲(chǔ)引擎來(lái)說(shuō)是共同的（如用于用戶(hù)連接的高速緩沖，MySQL的高速查詢(xún)高速緩沖等），其他高速緩沖策略?xún)H當(dāng)使用特殊的存儲(chǔ)引擎時(shí)才唯一定義。 性能幫助：包括針對(duì)并行操作的多I/O線(xiàn)程，線(xiàn)程并發(fā)性，數(shù)據(jù)庫(kù)檢查點(diǎn)，成批插入處理等。 其他目標(biāo)特性：可能包括對(duì)地理空間操作的支持，對(duì)特定數(shù)據(jù)處理操作的安全限制等。

以上要求會(huì)在不同的需求中予以體現(xiàn)，通過(guò)單獨(dú)一個(gè)系統(tǒng)實(shí)現(xiàn)是不可能的，以上特點(diǎn)有些本身就是相互矛盾的，魚(yú)和熊掌的問(wèn)題。對(duì)以上內(nèi)容做些選擇，形成的存儲(chǔ)引擎就是一個(gè)插件引擎了，某些特定的需求可以使用。如下圖，部分現(xiàn)有的存儲(chǔ)引擎以及基本特點(diǎn)：

各種搜索引擎介紹：

InnoDB MySql?5.6?版本默認(rèn)的存儲(chǔ)引擎。InnoDB?是一個(gè)事務(wù)安全的存儲(chǔ)引擎，它具備提交、回滾以及崩潰恢復(fù)的功能以保護(hù)用戶(hù)數(shù)據(jù)。 InnoDB?的行級(jí)別鎖定以及?Oracle?風(fēng)格的一致性無(wú)鎖讀提升了它的多用戶(hù)并發(fā)數(shù)以及性能。 InnoDB?將用戶(hù)數(shù)據(jù)存儲(chǔ)在聚集索引中以減少基于主鍵的普通查詢(xún)所帶來(lái)的?I/O?開(kāi)銷(xiāo)。為了保證數(shù)據(jù)的完整性，InnoDB?還支持外鍵約束。 MyISAM MyISAM既不支持事務(wù)、也不支持外鍵、其優(yōu)勢(shì)是訪(fǎng)問(wèn)速度快，但是表級(jí)別的鎖定限制了它在讀寫(xiě)負(fù)載方面的性能，因此它經(jīng)常應(yīng)用于只讀或者以讀為主的數(shù)據(jù)場(chǎng)景。 Memory 在內(nèi)存中存儲(chǔ)所有數(shù)據(jù)，應(yīng)用于對(duì)非關(guān)鍵數(shù)據(jù)由快速查找的場(chǎng)景。Memory類(lèi)型的表訪(fǎng)問(wèn)數(shù)據(jù)非常快，因?yàn)樗臄?shù)據(jù)是存放在內(nèi)存中的，并且默認(rèn)使用HASH索引， 但是一旦服務(wù)關(guān)閉，表中的數(shù)據(jù)就會(huì)丟失 BLACKHOLE 黑洞存儲(chǔ)引擎，類(lèi)似于?Unix?的?/dev/null，Archive?只接收但卻并不保存數(shù)據(jù)。對(duì)這種引擎的表的查詢(xún)常常返回一個(gè)空集。 這種表可以應(yīng)用于?DML?語(yǔ)句需要發(fā)送到從服務(wù)器，但主服務(wù)器并不會(huì)保留這種數(shù)據(jù)的備份的主從配置中。 CSV 它的表真的是以逗號(hào)分隔的文本文件。CSV?表允許你以?CSV?格式導(dǎo)入導(dǎo)出數(shù)據(jù)，以相同的讀和寫(xiě)的格式和腳本和應(yīng)用交互數(shù)據(jù)。由于?CSV?表沒(méi)有索引， 你最好是在普通操作中將數(shù)據(jù)放在?InnoDB?表里，只有在導(dǎo)入或?qū)С鲭A段使用一下?CSV?表。 NDB (又名?NDBCLUSTER)——這種集群數(shù)據(jù)引擎尤其適合于需要最高程度的正常運(yùn)行時(shí)間和可用性的應(yīng)用。注意：NDB?存儲(chǔ)引擎在標(biāo)準(zhǔn)?MySql?5.6?版本里并不被支持。 目前能夠支持 MySql?集群的版本有：基于?MySql?5.1?的?MySQL?Cluster?NDB?7.1；基于?MySql?5.5?的?MySQL?Cluster?NDB?7.2；基于?MySql?5.6?的?MySQL?Cluster?NDB?7.3。 同樣基于?MySql?5.6?的?MySQL?Cluster?NDB?7.4?目前正處于研發(fā)階段。 Merge 允許?MySql?DBA?或開(kāi)發(fā)者將一系列相同的?MyISAM?表進(jìn)行分組，并把它們作為一個(gè)對(duì)象進(jìn)行引用。適用于超大規(guī)模數(shù)據(jù)場(chǎng)景，如數(shù)據(jù)倉(cāng)庫(kù)。 Federated 提供了從多個(gè)物理機(jī)上聯(lián)接不同的?MySql?服務(wù)器來(lái)創(chuàng)建一個(gè)邏輯數(shù)據(jù)庫(kù)的能力。適用于分布式或者數(shù)據(jù)市場(chǎng)的場(chǎng)景。 Example 這種存儲(chǔ)引擎用以保存闡明如何開(kāi)始寫(xiě)新的存儲(chǔ)引擎的?MySql?源碼的例子。它主要針對(duì)于有興趣的開(kāi)發(fā)人員。這種存儲(chǔ)引擎就是一個(gè)啥事也不做的?"存根"。 你可以使用這種引擎創(chuàng)建表，但是你無(wú)法向其保存任何數(shù)據(jù)，也無(wú)法從它們檢索任何索引。

二、常用存儲(chǔ)引擎及適用場(chǎng)景

InnoDB

用于事務(wù)處理應(yīng)用程序，支持外鍵和行級(jí)鎖。如果應(yīng)用對(duì)事物的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性，數(shù)據(jù)操作除了插入和查詢(xún)之外，還包括很多更新和刪除操作，那么InnoDB存儲(chǔ)引擎是比較合適的。InnoDB除了有效的降低由刪除和更新導(dǎo)致的鎖定，還可以確保事務(wù)的完整提交和回滾，對(duì)于類(lèi)似計(jì)費(fèi)系統(tǒng)或者財(cái)務(wù)系統(tǒng)等對(duì)數(shù)據(jù)準(zhǔn)確要求性比較高的系統(tǒng)都是合適的選擇。

事務(wù)：

事物的基本要素 原子性?一致性?隔離性?持久性 臟數(shù)據(jù)：是事務(wù)回滾的結(jié)果 不可重復(fù)讀?：多次讀取數(shù)據(jù)因修改不一致 ????????側(cè)重修改，鎖數(shù)據(jù)解決 幻讀：修改數(shù)據(jù)因插入操作個(gè)別未成功 ????????側(cè)重新增/刪除?，鎖表解決 事務(wù)的隔離級(jí)別 設(shè)置語(yǔ)法：set?session?transaction?isolation?level?read?committed 未提交讀?read-uncommitted ?一個(gè)事務(wù)還沒(méi)提交修改就會(huì)被讀 已提交讀?read-committed 一個(gè)事務(wù)沒(méi)提交的修改不會(huì)被誤讀 但產(chǎn)生了不可重復(fù)讀 可重復(fù)讀?repeatable-read（默認(rèn)） 在一個(gè)事務(wù)中讀操作的結(jié)果不會(huì)隨著另一個(gè)事務(wù)的刪改而改變（快照） 但寫(xiě)數(shù)據(jù)和更新數(shù)據(jù)是根據(jù)表數(shù)據(jù)改變 串行化?serializable 鎖表操作?效率差

MyISAM

如果應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對(duì)事務(wù)的完整性、并發(fā)性要求不高，那么可以選擇這個(gè)存儲(chǔ)引擎。

Memory

將所有的數(shù)據(jù)保存在內(nèi)存中，在需要快速定位記錄和其他類(lèi)似數(shù)據(jù)的環(huán)境下，可以提供極快的訪(fǎng)問(wèn)。Memory的缺陷是對(duì)表的大小有限制，雖然數(shù)據(jù)庫(kù)因?yàn)楫惓＝K止的話(huà)數(shù)據(jù)可以正常恢復(fù)，但是一旦數(shù)據(jù)庫(kù)關(guān)閉，存儲(chǔ)在內(nèi)存中的數(shù)據(jù)都會(huì)丟失。

三、存儲(chǔ)引擎在mysql中的使用

1、存儲(chǔ)引擎相關(guān)sql語(yǔ)句

查看當(dāng)前的默認(rèn)存儲(chǔ)引擎: mysql>?show?variables?like?"default_storage_engine"; 查詢(xún)當(dāng)前數(shù)據(jù)庫(kù)支持的存儲(chǔ)引擎 mysql>?show?engines?\G;

結(jié)果：

2、指定存儲(chǔ)引擎建表

mysql>?create?table?ai(id?bigint(12),name?varchar(200))?ENGINE=MyISAM;? mysql>?create?table?country(id?int(4),cname?varchar(50))?ENGINE=InnoDB; 也可以使用alter?table語(yǔ)句，修改一個(gè)已經(jīng)存在的表的存儲(chǔ)引擎。 mysql>?alter?table?ai?engine?=?innodb;

#my.ini文件 [mysqld] default-storage-engine=INNODB

四、mysql的工作流程

MySQL架構(gòu)總共四層，在上圖中以虛線(xiàn)作為劃分。

首先，最上層的服務(wù)并不是MySQL獨(dú)有的，大多數(shù)給予網(wǎng)絡(luò)的客戶(hù)端/服務(wù)器的工具或者服務(wù)都有類(lèi)似的架構(gòu)。比如：連接處理、授權(quán)認(rèn)證、安全等。

第二層的架構(gòu)包括大多數(shù)的MySQL的核心服務(wù)。包括：查詢(xún)解析、分析、優(yōu)化、緩存以及所有的內(nèi)置函數(shù)（例如：日期、時(shí)間、數(shù)學(xué)和加密函數(shù)）。同時(shí)，所有的跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)：存儲(chǔ)過(guò)程、觸發(fā)器、視圖等。

第三層包含了存儲(chǔ)引擎。存儲(chǔ)引擎負(fù)責(zé)MySQL中數(shù)據(jù)的存儲(chǔ)和提取。服務(wù)器通過(guò)API和存儲(chǔ)引擎進(jìn)行通信。這些接口屏蔽了不同存儲(chǔ)引擎之間的差異，使得這些差異對(duì)上層的查詢(xún)過(guò)程透明化。存儲(chǔ)引擎API包含十幾個(gè)底層函數(shù)，用于執(zhí)行“開(kāi)始一個(gè)事務(wù)”等操作。但存儲(chǔ)引擎一般不會(huì)去解析SQL（InnoDB會(huì)解析外鍵定義，因?yàn)槠浔旧頉](méi)有實(shí)現(xiàn)該功能），不同存儲(chǔ)引擎之間也不會(huì)相互通信，而只是簡(jiǎn)單的響應(yīng)上層的服務(wù)器請(qǐng)求。

第四層包含了文件系統(tǒng)，所有的表結(jié)構(gòu)和數(shù)據(jù)以及用戶(hù)操作的日志最終還是以文件的形式存儲(chǔ)在硬盤(pán)上。

軟件開(kāi)發(fā) 人工智能 云計(jì)算 機(jī)器學(xué)習(xí)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶(hù)投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶(hù)投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。