今天進(jìn)入phpMyAdmin維護(hù)數(shù)據(jù)庫(kù)時(shí)意外發(fā)現(xiàn)一個(gè)數(shù)據(jù)庫(kù)中有一些表使用的MyISAM，另一些表使用的InnoDB，左思右想沒確定導(dǎo)致這種問題的原因，因?yàn)橐话銇?lái)說一個(gè)網(wǎng)站的數(shù)據(jù)庫(kù)所有表應(yīng)該使用同一種引擎，于是想著如何無(wú)損地將所有表轉(zhuǎn)換成同一種引擎。

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

從萬(wàn)能的搜索引擎了解到MyISAM和InnoDB引擎的區(qū)別如下。

那么，我們?nèi)绾芜x擇？

MyISAM：應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對(duì)事務(wù)的完整性、并發(fā)性要求不是很高。

 建議zblog、dedecms、帝國(guó)cms、discuz、emlog之類對(duì)數(shù)據(jù)庫(kù)讀寫不多的程序使用。

InnoDB：用于事務(wù)處理應(yīng)用程序，支持外鍵，如果應(yīng)用對(duì)事務(wù)的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性。更新刪除等頻繁（InnoDB可以有效的降低由于刪除和更新導(dǎo)致的鎖定），對(duì)于數(shù)據(jù)準(zhǔn)確性要求比較高的，此引擎適合。

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

我的網(wǎng)站使用drupal構(gòu)建的選擇InnoDB更合適，下面提供兩種經(jīng)過驗(yàn)證可用的MyISAM轉(zhuǎn)InnoDB方法。

方法一、通過phpmyadmin命令轉(zhuǎn)換。

以我的網(wǎng)站數(shù)據(jù)庫(kù)為例。

首先登錄phpMyAdmin，點(diǎn)“SQL”。

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

接著點(diǎn)擊查詢框右下角的“執(zhí)行”，命令執(zhí)行成功如下：

如果執(zhí)行不成功會(huì)有提示信息，檢查命令的標(biāo)點(diǎn)符號(hào)是否為英文半角輸入，還有命令中的 '表名'前后不帶空格。

方法二、使用SSH連接在linux中命令轉(zhuǎn)換。

例如當(dāng)前數(shù)據(jù)庫(kù)用戶名為root  數(shù)據(jù)庫(kù)名為tuhongwei_com 數(shù)據(jù)庫(kù)密碼為123456 ，要轉(zhuǎn)換的數(shù)據(jù)庫(kù)表名為 ban_ip。

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

因?yàn)槲矣玫腗ariaDB所以像上圖這樣顯示，如果是MySQL 前面的MariaDB就顯示MySQL。

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

以上就是MyISAM轉(zhuǎn)InnoDB的兩種方法，反之InnoDB轉(zhuǎn)MyISAM方法一樣，無(wú)非是將命令中的engine=myisam。

查看表的存儲(chǔ)引擎
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)
查看表的數(shù)據(jù)量
mysql> select count(1) from tt7;
+----------+
| count(1) |
+----------+
| 16777216 |
+----------+
1 row in set (0.00 sec)
 
方法一：

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

方法二：
 
把方法一中的存儲(chǔ)引擎改回myisam
mysql> alter table tt7 engine=myisam;
Query OK, 16777216 rows affected (27.09 sec)
Records: 16777216  Duplicates: 0  Warnings: 0
從這里也可以看出myisam表要比innodb表快很多
 
創(chuàng)建個(gè)和tt7同樣表結(jié)構(gòu)的表
mysql> create table tt7_tmp like tt7;
Query OK, 0 rows affected (0.02 sec)
 
tt7_tmp作為中間結(jié)果集
mysql> insert into tt7_tmp select * from tt7;
Query OK, 16777216 rows affected (27.20 sec)
Records: 16777216  Duplicates: 0  Warnings: 0
 
刪除原表的數(shù)據(jù)
mysql> truncate table tt7;
Query OK, 16777725 rows affected (0.18 sec)
 
這回更改原表的存儲(chǔ)引擎
mysql> alter table tt7 engine=innodb;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
速度很快就完成了
 
再把中間結(jié)果集的數(shù)據(jù)導(dǎo)回原表中
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;

測(cè)試結(jié)果：

方法二比較快一點(diǎn)，但是數(shù)據(jù)量要是比較大的話，方法二就要采用化整為零的分批操作的方式，否則insert操作將會(huì)具耗時(shí)，并產(chǎn)生大量的undo日志。
如果是小表的話（500M以內(nèi)，根據(jù)自己系統(tǒng)的硬件環(huán)境），采用方法一就可以
如果是大表的話，那就采用方法二+批量的方式

如果是批量更改表的存儲(chǔ)引擎

用于生成變更的SQL語(yǔ)句：

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

用于生成檢查表的SQL語(yǔ)句：

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

根據(jù)自己系統(tǒng)配置修改如下參數(shù)，以加快變更速度（記得以前的值，一會(huì)還得改回來(lái)）

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;

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

設(shè)置mysql的默認(rèn)存儲(chǔ)引擎 在my.cnf中修改:
default-storage-engine=InnoDB

設(shè)置當(dāng)前會(huì)話的默認(rèn)存儲(chǔ)引擎:
SET storage_engine=InnoDB;

然后再show engines可以看到默認(rèn)引擎是InnoDB了，然后再將原數(shù)據(jù)庫(kù)中的表從MyISAM庫(kù)轉(zhuǎn)換成InnoDB,具體操作如下：

1、從原mysql數(shù)據(jù)庫(kù)fahao中導(dǎo)出表結(jié)構(gòu)，不帶數(shù)據(jù)
mysqldump -uxxx -p’xxx’ –no-data fahao > fahao.sql

2、在mysql中創(chuàng)建測(cè)試庫(kù)fahao_test
create database fahao_test

3、在導(dǎo)出的表結(jié)構(gòu)fahao.sql中找到ENGINE=MyISAM DEFAULT CHARSET=utf8;修改成ENGINE=InnoDB DEFAULT CHARSET=utf8;

4、在將fahao.sql表結(jié)構(gòu)導(dǎo)入到測(cè)試庫(kù)fahao_test中，并查看導(dǎo)入的表類型是不是InnoDB？
用source導(dǎo)入后，查看表類型方法：mysql> show table status like ‘fahao_name’\G

5、從原mysql數(shù)據(jù)庫(kù)fahao中導(dǎo)出數(shù)據(jù)，不需要表結(jié)構(gòu)
mysqldump -uxxx -p’xxxx’ -t fahao > fahao_data.sql　

6、fahao_data.sql導(dǎo)入到測(cè)試庫(kù)fahao_test中

至此fahao庫(kù)的表從MyISAM引擎轉(zhuǎn)換成InnoDB了，但是有一個(gè)問題，查看表的詳細(xì)信息時(shí)發(fā)現(xiàn)Data_free不為零，說明存在數(shù)據(jù)碎片，需要進(jìn)行優(yōu)化，在網(wǎng)上查詢資料，發(fā)現(xiàn)有如下的方法.

怎么查看這些碎片？
顯示你數(shù)據(jù)庫(kù)中存在碎片的全部列表:
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數(shù)據(jù)庫(kù)中所有表的詳細(xì)信息
mysql>show table status from fahao_test\G

查看單個(gè)表的詳細(xì)信息： 表類型是否InnoDB，是否有數(shù)據(jù)碎片
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提供了一種簡(jiǎn)便的修正方法,這就是所謂的優(yōu)化列表(優(yōu)化表空間，減少數(shù)據(jù)碎片，釋放表空間)
對(duì)MyISAM、InnoDB引擎的表格有用，在InnoDB表上面執(zhí)行會(huì)出現(xiàn)下面的提示：雖然提示不支持，optimize，但是已經(jīng)進(jìn)行重建和分析，空間已經(jīng)回收.
optimize table table_name;

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

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

以上三種方法都測(cè)試過，但都無(wú)法將所有數(shù)據(jù)回收，Data_free無(wú)法為零，
嘗試將表單獨(dú)mysqldump出后，drop掉表，然后重新source導(dǎo)入dump的表，結(jié)果無(wú)效，仍然有 Data_free: 4194304

最后查詢資料為什么會(huì)有Data_free才發(fā)現(xiàn)，跟表結(jié)構(gòu)、字段長(zhǎng)度的設(shè)置、字段類型、data page都有關(guān)系，沒有合理設(shè)置這些都會(huì)導(dǎo)致數(shù)據(jù)碎片，無(wú)法充分利用表空間.如果一定要將Data_free優(yōu)化為零，需要對(duì)整個(gè)表進(jìn)行優(yōu)化才行，以下是借用別人的優(yōu)化方法.

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

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

• MySQL
• innodb
• MyISAM