mysql數據庫存儲引擎與修改（修改數據庫的存儲引擎）

• 1、MySQL存儲引擎
• 2、什麼是MySQL存儲引擎？
• 3、如何修改mysql表的存儲引擎

InnoDB的數據文件本身就是主索引文件。而MyISAM的主索引和數據是分開的。輔助索引data域存儲相應記錄主鍵的值而不是地址。

innoDB是聚簇索引，數據掛在逐漸索引之下。

是 MySQL 默認的事務型存儲引擎， 只有在需要它不支持的特性時，才考慮使用其它存儲引擎 。

實現了四個標準的隔離級別，默認級別是可重複讀(REPEATABLE READ)。在可重複讀隔離級別下，通過多版本並發控制(MVCC)+ 間隙鎖(Next-Key Locking)防止幻影讀。

主索引是聚簇索引，在索引中保存了數據，從而避免直接讀取磁盤，因此對查詢性能有很大的提升。

內部做了很多優化，包括從磁盤讀取數據時採用的可預測性讀、能夠加快讀操作並且自動創建的自適應哈希索引、能夠加速插入操作的插入緩衝區等。

支持真正的在線熱備份。其它存儲引擎不支持在線熱備份，要獲取一致性視圖需要停止對所有表的寫入，而在讀寫混合場景中，停止寫入可能也意味着停止讀取。

以B+樹作為索引結構，葉節點的數據域存放數據記錄的地址。主索引和輔助索引在結構上沒有區別，只是主索引要求key唯一，而輔助索引的key可以重複。

MyISAM中索引檢索的算法為首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，則取出其data域的值，然後以data域的值為地址，讀取相應數據記錄。

設計簡單，數據以緊密格式存儲。對於只讀數據，或者表比較小、可以容忍修復的操作，則依然可以使用它。

提供了大量的特性，包括壓縮表、空間數據索引等。

不支持事務 。

不支持行級鎖，只能對整張表加鎖，讀取時會對需要讀到的所有表加共享鎖，寫入時則對錶加排它鎖。但在表有讀取操作的同時，也可以往表中插入新的記錄，這被稱為並發插入(CONCURRENT INSERT)。

可以手工或者自動執行檢查和修復操作，但是和事務恢復以及崩潰恢復不同，可能導致一些數據丟失，而且修復操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 選項，在每次修改執行完成時，不會立即將修改的索引數據寫入磁盤，而是會寫到內存中的鍵緩衝區，只有在清理鍵緩衝區或者關閉表的時候才會將對應的索引塊寫入磁盤。這種方式可以極大地提升寫入性能，但是在數據庫或者主機崩潰時會造成索引損壞，需要執行修復操作。

MySQL 可能是最著名的 關係數據庫管理系統 (RDBMS),作為一款免費開源軟件開發，最初由 MYSQL AB 公司提供支持，但現在歸 Oracle 所有。

在 MySQL 中，用於表的「存儲引擎」決定了數據的處理方式。有幾種可用的存儲引擎，但最常用的是 InnoDB 和 MyISAM 。

在本文中，我們將了解它們的顯着特徵以及它們之間的主要區別。

在本教程中，您將學習：

在我們討論兩個主要 MySQL 存儲引擎之間的特性和區別之前，先來了解一下什麼是存儲引擎？

存儲引擎，也稱為「 表處理程序 」，基本上是解釋和管理與數據庫表的 SQL 查詢相關的操作的數據庫部分。

在最新版本的 MySQL 中，可以使用「 可插拔 」架構來組織和管理存儲引擎，存在多種存儲引擎，但最常用的兩個是 InnoDB 和 MyISAM 。

要獲得我們正在使用的數據庫中可用存儲引擎的列表，我們所要做的就是發出一個簡單的 SQL 查詢，因此我們需要做的第一件事就是打開一個 MySQL 交互式提示並使用數據庫用戶登錄及其密碼：

如果登錄成功，提示將變為mysql，在這裡，我們可以運行我們的 SQL 查詢來可視化可用的存儲引擎：

執行查詢後，我們應該獲得類似於以下內容的結果：

在上表中，作為查詢結果生成，我們可以通過查看Support每行列中的值輕鬆了解支持哪些存儲引擎，「YES」值表示存儲引擎可用，否則「NO」。相反，同一列中的「DEFAULT」值表示相應的引擎（在本例中為 InnoDB）是服務器使用的默認引擎。

「 Transactions 」和「 Savepoints 」列中存在的值分別表示存儲引擎是否支持事務和回滾。正如我們通過查看錶可以看到的，只有 InnoDB 引擎可以。

關於存儲引擎的信息存在於「 INFORMATION_SCHEMA 」數據庫的「 ENGINES 」表中，因此我們也可以發出標準的「SELECT」查詢來獲取我們需要的數據：

我們將獲得與上面看到的相同的結果。

讓我們看看兩個最常用的存儲引擎 InnoDB 和 MyISAM 之間的主要特性和區別是什麼。

正如我們已經說過的， InnoDB 是自 MySQL 以來的默認存儲引擎5.5。

此存儲引擎的一些主要功能如下：

對事務的支持提供了一種安全的方式來執行多個查詢以保持數據一致。

當多個修改數據的操作被執行並且我們想要確保它們只有在所有操作都成功並且沒有錯誤發生時才有效時，我們想要使用事務。

典型的處理方式是啟動事務並執行查詢：如果出現錯誤，則執行回滾，否則提交更改。

當使用 InnoDB 數據鎖定發生在行級別時，因此在事務期間鎖定的數據量是有限的。

InnoDB 有兩種類型的鎖：

一個共享鎖允許誰擁有它讀取該行的交易，而一個排它鎖允許交易執行其修改行的操作，所以要更新或刪除數據。

當一個事務在某行上獲得共享鎖，而另一個事務需要相同的鎖類型時，立即授予；但是，如果第二個事務在同一行上請求排他鎖，它將不得不等待。

如果第一個事務持有該行的排他鎖，則第二個事務將不得不等待該鎖被釋放以獲得共享鎖或排他鎖。

外鍵是一個非常重要的特性，因為它們可用於基於表之間的邏輯關係來強制執行數據完整性。想像一下，我們的數據庫中有三個表（假設它被稱為「testdb」）：一個user包含現有用戶的job表，一個註冊所有可用作業的user_job表，以及一個用於表示用戶和用戶之間存在的多對多關係的表。作業（一個用戶可以有多個作業，多個作業可以與同一個用戶關聯）。

該user_job表就是所謂的連接表或關聯表，因為它的唯一目的是表示用戶-工作關聯。該表有兩列，一個叫user_id和其他job id。表中會存在兩個外鍵約束，強制執行以下規則：user_id列中的值只能引用表id列中的值，列中的user值job_id必須引用表id列中的現有值job.

這將強制執行完整性，因為僅允許現有用戶和作業的 ID 存在於關聯表中。刪除涉及表中一個或多個關聯的用戶或作業user_job也是不允許的，除非為相應的外鍵設置了CASCADE DELETE規則。在這種情況下，當刪除用戶或作業時，它們所涉及的關係也將被刪除。

MyISAM 曾經是默認的 MySQL 存儲引擎，但已被 InnoDB 取代。使用此引擎時，數據鎖定發生在表級別，因此執行操作時鎖定的數據更多。

與 InnoDB 不同，MyISAM 不支持事務回滾和提交，因此必須手動執行回滾。MyISAM 和 InnoDB 之間的另一個很大區別是前者不支持外鍵。MyISAM 更簡單，並且在對有限數據集進行讀取密集型操作時可能具有優勢（有爭議）。

在表上使用 MyISAM 時，會設置一個標誌，指示該表是否需要修復，例如在突然關閉之後。稍後可以使用適當的工具執行表修復。

如何知道特定表使用了什麼存儲引擎？我們所要做的就是發出一個簡單的查詢。

例如，要知道user我們在前面的例子中提到的表使用了什麼存儲引擎，我們將運行：

注意上面的查詢我們使用了G，為了讓查詢結果垂直顯示，優化空間。執行查詢後，我們將獲得以下結果：

在這種情況下，通過查看「Engine」列中存儲的值，我們可以清楚地看到該表使用的是「InnoDB」引擎。獲取相同信息的另一種方法是INFORMATION_SCHEMA.TABLES直接查詢表：

上面的查詢將只返回表使用的引擎：

如果我們稍微更改查詢，我們可以獲得數據庫中所有表名的列表以及它們使用的引擎：

如果我們要為一個表設置一個特定的存儲引擎，我們可以在創建時指定它。例如，假設我們正在創建job表，並且出於某種原因我們想要使用 MyISAM 存儲引擎。我們將發出以下 SQL 查詢：

相反，如果我們想要更改用於已存在表的存儲引擎，我們只需要使用ALTERSQL 語句。假設我們要將上一個示例中創建的「job」表所使用的存儲引擎更改為 InnoDB；我們會運行：

在本教程中，我們學習了什麼是數據庫存儲引擎，並且我們看到了兩個最常用的 MySQL 引擎的主要特性： InnoDB 和 MyISAM 。

我們看到了如何檢查哪些引擎可用、哪些引擎用於表以及如何使用 SQL 查詢設置和修改表引擎。

1、修改表引擎方法

alter table table_name engine=innodb;

2、查看系統支持的存儲引擎

show engines;

3、查看錶使用的存儲引擎

兩種方法：

a、show table status from db_name where name=’table_name’;

b、show create table table_name;

如果顯示的格式不好看，可以用\g代替行尾分號

有人說用第二種方法不準確

我試了下，關閉掉原先默認的Innodb引擎後根本無法執行show create table table_name指令，因為之前建的是Innodb表，關掉後默認用MyISAM引擎，導致Innodb表數據無法被正確讀取。

4 關閉Innodb引擎方法

關閉mysql服務： net stop mysql

找到mysql安裝目錄下的my.ini文件：

找到default-storage-engine=INNODB 改為default-storage-engine=MYISAM

找到#skip-innodb 改為skip-innodb

啟動mysql服務：net start mysql