今天進入PHPMyAdmin維護數據庫時意外發現一個數據庫中有一些表使用的MyISAM，另一些表使用的InnoDB，左思右想沒確定導致這種問題的原因，因為一般來說一個網站的數據庫所有表應該使用同一種引擎，于是想著如何無損地將所有表轉換成同一種引擎。

MySQL（或者社區開源fork的MariaDB）5.5以上支持InnoDB引擎，并將其作為默認數據庫引擎。InnoDB帶來很多改進，但是對系統資源占用明顯增加，對于還在128MB～512MB內存VPS掛WordPress博客的筒子們來說，InnoDB是不合適的，還是得換回MyISAM這批老馬！

從萬能的搜索引擎了解到MyISAM和InnoDB引擎的區別如下。

那么，我們如何選擇？

MyISAM：應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務的完整性、并發性要求不是很高。

 建議zblog、dedecms、帝國cms、discuz、emlog之類對數據庫讀寫不多的程序使用。

InnoDB：用于事務處理應用程序，支持外鍵，如果應用對事務的完整性有比較高的要求，在并發條件下要求數據的一致性。更新刪除等頻繁（InnoDB可以有效的降低由于刪除和更新導致的鎖定），對于數據準確性要求比較高的，此引擎適合。

建議Wordpress、joomla、phpBB、Drupal之類比較依賴數據庫讀寫的程序使用。

我的網站使用drupal構建的選擇InnoDB更合適，下面提供兩種經過驗證可用的MyISAM轉InnoDB方法。

方法一、通過phpmyadmin命令轉換。

以我的網站數據庫為例。

首先登錄phpMyAdmin，點“SQL”。

然后在SQL查詢框中輸入命令，ALTER TABLE `數據庫表名` ENGINE = INNODB; （后面帶小寫;）例如轉換圖片中的ban_ip表，命令就是  ALTER TABLE `ban_ip` ENGINE = INNODB; 可一條一行，同時轉換多條命令。如下圖：

接著點擊查詢框右下角的“執行”，命令執行成功如下：

如果執行不成功會有提示信息，檢查命令的標點符號是否為英文半角輸入，還有命令中的 '表名'前后不帶空格。

方法二、使用SSH連接在linux中命令轉換。

例如當前數據庫用戶名為root  數據庫名為tuhongwei_com 數據庫密碼為123456 ，要轉換的數據庫表名為 ban_ip。

首先連接SSH，輸入 mysql -uroot -p tuhongwei_com回車，輸入數據庫密碼123456 回車（注意，在linux系統中輸入密碼屏幕不會提示），登錄數據庫成功后如下顯示：

因為我用的MariaDB所以像上圖這樣顯示，如果是MySQL 前面的MariaDB就顯示MySQL。

然后輸入命令 alter table ban_ip engine=innodb; (后面英文半角;不要忘記)回車，執行成功后如下提示：

以上就是MyISAM轉InnoDB的兩種方法，反之InnoDB轉MyISAM方法一樣，無非是將命令中的engine=myisam。

查看表的存儲引擎
mysql> show create table tt7;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| tt7   | CREATE TABLE `tt7` (
  `id` int(10) default NULL,
  `name` char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看表的數據量
mysql> select count(1) from tt7;
+----------+
| count(1) |
+----------+
| 16777216 |
+----------+
1 row in set (0.00 sec)
 
方法一：

直接更改存儲引擎
mysql> alter table tt7 engine=innodb;
Query OK, 16777216 rows affected (2 min 39.80 sec)
Records: 16777216  Duplicates: 0  Warnings: 0

方法二：
 
把方法一中的存儲引擎改回myisam
mysql> alter table tt7 engine=myisam;
Query OK, 16777216 rows affected (27.09 sec)
Records: 16777216  Duplicates: 0  Warnings: 0
從這里也可以看出myisam表要比innodb表快很多
 
創建個和tt7同樣表結構的表
mysql> create table tt7_tmp like tt7;
Query OK, 0 rows affected (0.02 sec)
 
tt7_tmp作為中間結果集
mysql> insert into tt7_tmp select * from tt7;
Query OK, 16777216 rows affected (27.20 sec)
Records: 16777216  Duplicates: 0  Warnings: 0
 
刪除原表的數據
mysql> truncate table tt7;
Query OK, 16777725 rows affected (0.18 sec)
 
這回更改原表的存儲引擎
mysql> alter table tt7 engine=innodb;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
速度很快就完成了
 
再把中間結果集的數據導回原表中
mysql> insert into tt7 select * from tt7_tmp;
Query OK, 16777216 rows affected (2 min 0.95 sec)
Records: 16777216  Duplicates: 0  Warnings: 0
 
刪除中間表
mysql> drop table tt7_tmp;

測試結果：

方法二比較快一點，但是數據量要是比較大的話，方法二就要采用化整為零的分批操作的方式，否則insert操作將會具耗時，并產生大量的undo日志。
如果是小表的話（500M以內，根據自己系統的硬件環境），采用方法一就可以
如果是大表的話，那就采用方法二+批量的方式

如果是批量更改表的存儲引擎

用于生成變更的SQL語句：

SELECT CONCAT('ALTER TABLE ',table_name,' ENGINE=InnoDB;') FROM information_Schema.tables WHERE table_schema='db_name' AND ENGINE='myisam';

用于生成檢查表的SQL語句：

SELECT CONCAT('CHECK TABLE ',table_name) FROM information_schema.tables WHERE table_schema='db_name';

根據自己系統配置修改如下參數，以加快變更速度（記得以前的值，一會還得改回來）

SET GLOBAL sort_buffer_size=64*1024*1024;
SET GLOBAL tmp_table_size=64*1024*1024;
SET GLOBAL read_buffer_size=32*1024*1024;
SET GLOBAL read_rnd_buffer_size=32*1024*1024;

由于開發需要使用InnoDB引擎的事務功能，需要將原有的MyISAM引擎更換為InnoDB，InnoDB行級鎖也可以避免MyISAM的鎖表，操作流程如下：
查看mysql的存儲引擎信息
show engines;
默認是MyISAM，需要修改為InnoDB;

設置mysql的默認存儲引擎 在my.cnf中修改:
default-storage-engine=InnoDB

設置當前會話的默認存儲引擎:
SET storage_engine=InnoDB;

然后再show engines可以看到默認引擎是InnoDB了，然后再將原數據庫中的表從MyISAM庫轉換成InnoDB,具體操作如下：

1、從原mysql數據庫fahao中導出表結構，不帶數據
mysqldump -uxxx -p’xxx’ –no-data fahao > fahao.sql

2、在mysql中創建測試庫fahao_test
create database fahao_test

3、在導出的表結構fahao.sql中找到ENGINE=MyISAM DEFAULT CHARSET=utf8;修改成ENGINE=InnoDB DEFAULT CHARSET=utf8;

4、在將fahao.sql表結構導入到測試庫fahao_test中，并查看導入的表類型是不是InnoDB？
用source導入后，查看表類型方法：mysql> show table status like ‘fahao_name’\G

5、從原mysql數據庫fahao中導出數據，不需要表結構
mysqldump -uxxx -p’xxxx’ -t fahao > fahao_data.sql　

6、fahao_data.sql導入到測試庫fahao_test中

至此fahao庫的表從MyISAM引擎轉換成InnoDB了，但是有一個問題，查看表的詳細信息時發現Data_free不為零，說明存在數據碎片，需要進行優化，在網上查詢資料，發現有如下的方法.

怎么查看這些碎片？
顯示你數據庫中存在碎片的全部列表:
select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in (‘information_schema’, ‘mysql’) and data_free > 0;

查看fahao_test數據庫中所有表的詳細信息
mysql>show table status from fahao_test\G

查看單個表的詳細信息： 表類型是否InnoDB，是否有數據碎片
mysql> show table status from fahao_test like ‘table_name’\G
mysql> show table status like ‘table_name’\G
mysql> show table status from fahao_test where name=’table_name’\G

MySQL提供了一種簡便的修正方法,這就是所謂的優化列表(優化表空間，減少數據碎片，釋放表空間)
對MyISAM、InnoDB引擎的表格有用，在InnoDB表上面執行會出現下面的提示：雖然提示不支持，optimize，但是已經進行重建和分析，空間已經回收.
optimize table table_name;

修改表的存儲引擎時，會重建表，結構文件、數據文件、索引文件等文件,這種方式從原理上，感覺可以，但是測試并沒有成功。
ALTER TABLE table_name ENGINE=’InnoDB’;
Query OK, 2919 rows affected (5.92 sec)
Records: 2919 Duplicates: 0 Warnings: 0
Data_free: 5242880 有變化
有一定的作用，但無法完全釋放出表空間，使Data_free為零

如果需要檢查并修復所有的數據庫的數據表，那么可以使用：
/usr/local/mysql/bin/mysqlcheck -uroot -p -o -A
如果需要修復指定的數據庫用
mysqlcheck -uxx -p fahao_test
測試沒有效果，Data_free: 4194304

以上三種方法都測試過，但都無法將所有數據回收，Data_free無法為零，
嘗試將表單獨mysqldump出后，drop掉表，然后重新source導入dump的表，結果無效，仍然有 Data_free: 4194304

最后查詢資料為什么會有Data_free才發現，跟表結構、字段長度的設置、字段類型、data page都有關系，沒有合理設置這些都會導致數據碎片，無法充分利用表空間.如果一定要將Data_free優化為零，需要對整個表進行優化才行，以下是借用別人的優化方法.

如果在實際業務中，確實需要在InnoDB表中存儲BLOB、TEXT、長VARCHAR列時，有下面幾點建議：
1、盡可能將所有數據序列化、壓縮之后，存儲在同一個列里，避免發生多次off-page；
2、實際最大存儲長度低于255的列，轉成VARCHAR或者CHAR類型（如果是變長數據二者沒區別，如果是定長數據，則使用CHAR類型）；
3、如果無法將所有列整合到一個列，可以退而求其次，根據每個列最大長度進行排列組合后拆分成多個子表，盡量是的每個子表的總行長度小于8KB，減少發生off-page的頻率；
4、上述建議是在data page為默認的16KB前提下，如果修改成8KB或者其他大小，請自行根據上述理論進行測試，找到最合適的值；
5、字符型列長度小于255時，無論采用CHAR還是VARCHAR來存儲，或者把VARCHAR列長度定義為255，都不會導致實際表空間增大；
6、一般在游戲領域會用到比較多的BLOB列類型，游戲界同行可以關注下。

PS：
更換成InnoDB后最好做成獨立表空間，編輯my.cnf在innodb段中增加innodb_file_per_table = 1(1為啟用，0為禁用)配置參數,這樣InnoDB會對每個表創建一個數據文件，然后只需要運行OPTIMIZE TABLE 命令就可以釋放所有已經刪除的磁盤空間。
通過mysql語句可以查看該變量的值：mysql> show variables like ‘%per_table%’;

• MySQL
• innodb
• MyISAM