在mysql中最常用的表存儲引擎有myisam與innodb了其它更高級的我們幾乎不用也用不,對於myisam<=>innodb的特點我想各位都知道一點了,下面我來介紹myisam<=>innodb相互轉換吧。
MySQL作為最常用的數據庫,經常遇到各種各樣的問題。今天要說的就是表存儲引擎的修改。有三種方式,列表如下。
1.真接修改。在數據多的時候比較慢,而且在修改時會影響讀取性能。my_table是操作的表,innoDB是新的存儲引擎。
代碼如下 復制代碼ALTER TABLE my_table ENGINE=InnoDB
2.導出,導入。這個比較容易操作,直接把導出來的sql文件給改了,然後再導回去。用mysqldump ,楓哥常用的是navicate那樣更容易上手。友情提醒風險較大。
3.創建,插入。這個比第一種速度快, 安全性比第二種高,推薦。分2步操作
.創建表,先創建一個和要操作表一樣的表,然後更改存儲引擎為目標引擎。
代碼如下 復制代碼CREATE TABLE my_tmp_table LIKE my_table;
ALTER TABLE my_tmp_table ENGINE=InnoDB;
b.插入。為了安全和速度,最好加上事務,並限制id(主鍵)范圍。
代碼如下 復制代碼INSERT INTO my_tmp_table SELECT * FROM my_table;
修改表的存儲引擎myisam<=>innodb
查看表的存儲引擎
代碼如下 復制代碼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)
方法一:
直接更改存儲引擎
方法二:
把方法一中的存儲引擎改回myisam
代碼如下 復制代碼 mysql> alter table tt7 engine=myisam;從這裡也可以看出myisam表要比innodb表快很多
創建個和tt7同樣表結構的表
代碼如下 復制代碼 mysql> create table tt7_tmp like tt7;
tt7_tmp作為中間結果集
代碼如下 復制代碼 mysql> insert into tt7_tmp select * from tt7;
刪除原表的數據
代碼如下 復制代碼 mysql> truncate table tt7;
這回更改原表的存儲引擎
代碼如下 復制代碼 mysql> alter table tt7 engine=innodb;速度很快就完成了
再把中間結果集的數據導回原表中
代碼如下 復制代碼 mysql> insert into tt7 select * from tt7_tmp;
刪除中間表
代碼如下 復制代碼 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;
補充一下
MySql中有哪些存儲引擎?
1 MyISAM:這種引擎是mysql最早提供的。這種引擎又可以分為靜態MyISAM、動態MyISAM 和壓縮MyISAM三種:
靜態MyISAM:如果數據表中的各數據列的長度都是預先固定好的,服務器將自動選擇這種表類型。因為數據表中每一條記錄所占用的空間都是一樣的,所以這種表存取和更新的效率非常高。當數據受損時,恢復工作也比較容易做。
動態MyISAM:如果數據表中出現varchar、xxxtext或xxxBLOB字段時,服務器將自動選擇這種表類型。相對於靜態MyISAM,這種表存儲空間比較小,但由於每條記錄的長度不一,所以多次修改數據後,數據表中的數據就可能離散的存儲在內存中,進而導致執行效率下降。同時,內存中也可能會出現很多碎片。因此,這種類型的表要經常用optimize table 命令或優化工具來進行碎片整理。
壓縮MyISAM:以上說到的兩種類型的表都可以用myisamchk工具壓縮。這種類型的表進一步減小了占用的存儲,但是這種表壓縮之後不能再被修改。另外,因為是壓縮數據,所以這種表在讀取的時候要先時行解壓縮。
但是,不管是何種MyISAM表,目前它都不支持事務,行級鎖和外鍵約束的功能。
2 MyISAM Merge引擎:這種類型是MyISAM類型的一種變種。合並表是將幾個相同的MyISAM表合並為一個虛表。常應用於日志和數據倉庫。
3 InnoDB:InnoDB表類型可以看作是對MyISAM的進一步更新產品,它提供了事務、行級鎖機制和外鍵約束的功能。
4 memory(heap):這種類型的數據表只存在於內存中。它使用散列索引,所以數據的存取速度非常快。因為是存在於內存中,所以這種類型常應用於臨時表中。
5 archive:這種類型只支持select 和 insert語句,而且不支持索引。常應用於日志記錄和聚合分析方面。