您現在的位置: 365建站網 > 365學習 > MySQL的MyISAM和InnoDB引擎互相轉換的方法

MySQL的MyISAM和InnoDB引擎互相轉換的方法

文章來源:365jz.com     點擊數:646    更新時間:2020-04-09 21:22   參與評論

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

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

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

MyISAM與InnoDB的區別
InnoDBMyISAM
支持事務處理等不支持事務處理等。
不加鎖讀取
支持外鍵不支持外鍵
支持行鎖不支持行鎖
不支持FULLTEXT類型的索引支持FULLTEXT類型的索引
不保存表的具體行數,掃描表來計算有多少行保存表的具體行數,不帶where時,直接返回保存的行數。
DELETE表時,是一行一行地刪除DELETE表時,先drop表,然后重建表
InnoDB把數據和索引存放在表空間里面MyISAM表被存放在三個文件,frm文件存放表格定義
跨平臺可直接拷貝使用跨平臺很難直接拷貝。
InnoDB中必須包含AUTO_INCREMENT類型字段的索引表格很難被壓縮MyISAM中可以使用AUTO_INCREMENT類型字段建立聯合索引,表格可以被壓縮。


那么,我們如何選擇?

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

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

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

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

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

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

以我的網站數據庫為例。

MyISAM轉InnoDB方法

首先登錄phpMyAdmin,點“SQL”。

MyISAM轉換成InnoDB引擎的兩種方法

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

MyISAM轉換成InnoDB引擎圖文教程

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

MySQL的MyISAM轉換成InnoDB引擎的兩種方法

MyISAM轉InnoDB查詢引擎詳細教程

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

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

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

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

MySQL MyISAM轉換成InnoDB引擎的兩種方法

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

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

MyISAM轉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;
QQ圖片20150527140352.png默認是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;
clipboard

修改表的存儲引擎時,會重建表,結構文件、數據文件、索引文件等文件,這種方式從原理上,感覺可以,但是測試并沒有成功。
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%’;



如對本文有疑問,請提交到交流論壇,廣大熱心網友會為你解答??! 點擊進入論壇


發表評論 (646人查看,0條評論)
請自覺遵守互聯網相關的政策法規,嚴禁發布色情、暴力、反動的言論。
用戶名: 驗證碼: 點擊我更換圖片
最新評論
------分隔線----------------------------
自拍偷拍福力视频,偷拍国际精品,麻豆一区福利电影,国产网红视频午夜福利,se视频大全,久久国产AV影院 啦啦啦免费视频在线观看| 姐姐的朋3线观高清| japonensis18日本护土| 中国熟妇人妻xxxxx| 亚洲成av人片在线观看天堂无码| 四虎国产精品永久入口| 国产精品制服丝袜无码| 国产精品青青青高清在线| 女性自慰| 免费看电影网站| heyzo高无码专区人妻被手指| 九九线精品视频在线观看视频| o|dwomen欧洲少妇| 欧美人獸交| 2012国语在线观看完整版在线播放| 新无码h肉动漫在线观看| AVTT天堂网2014东京热| av免费不卡国产观看| 中字幕一区二区三区乱码| 中国国产xxxxx免费视频| 青娱乐视觉盛宴青青草91| 无遮挡很爽很污很黄的网站| chinese腹肌体育生精牛榨精| 男女啪啪第99期动态图| 在线日韩日本国产亚洲| 开心激情五月天| 麻豆传谋官方入口| 亚洲超清精品av在线| 国内精品伊人久久久久av影院| 无翼乌之店长的命令| 2828手机观看免费| http://www.drjayashrishembalkar.com