在mysql中最常用的表存儲引擎有myisam與innodb了其它更高級的我們幾乎不用也用不，對于myisam<=>innodb的特點我想各位都知道一點了,下面我來介紹myisam<=>innodb相互轉(zhuǎn)換吧.

MySQL作為最常用的數(shù)據(jù)庫,經(jīng)常遇到各種各樣的問題。,今天要說的就是表存儲引擎的修改,有三種方式,列表如下.

1.真接修改,在數(shù)據(jù)多的時候比較慢,而且在修改時會影響讀取性能,my_table是操作的表,innoDB是新的存儲引擎,代碼如下:

ALTER TABLE my_table ENGINE=InnoDB

2.導(dǎo)出,導(dǎo)入,這個比較容易操作,直接把導(dǎo)出來的sql文件給改了,然后再導(dǎo)回去,用mysqldump,楓哥常用的是navicate那樣更容易上手,友情提醒風險較大.

3.創(chuàng)建,插入,這個比第一種速度快,安全性比第二種高,推薦,分2步操作.

.創(chuàng)建表,先創(chuàng)建一個和要操作表一樣的表,然后更改存儲引擎為目標引擎,代碼如下:

CREATE TABLE my_tmp_table LIKE my_table;

ALTER TABLE my_tmp_table ENGINE=InnoDB;

b.插入,為了安全和速度,最好加上事務(wù),并限制id(主鍵）范圍,代碼如下:

INSERT INTO my_tmp_table SELECT * FROM my_table;

修改表的存儲引擎myisam<=>innodb.

查看表的存儲引擎,代碼如下:

1. mysql> show create table tt7; 
2. +-------+-------------------------------------------------------------------------------------------------------------------------+ 
3. | Table | Create Table                                                                                                            | 
4. +-------+-------------------------------------------------------------------------------------------------------------------------+ 
5. | tt7   | CREATE TABLE `tt7` ( 
6.   `id` int(10) default NULL, 
7.   `name` char(10) default NULL 
8. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |  
9. +-------+-------------------------------------------------------------------------------------------------------------------------+ 
10. 1 row in set (0.00 sec) 

查看表的數(shù)據(jù)量,代碼如下:

1. mysql> select count(1) from tt7; 
2. +----------+ 
3. | count(1) | 
4. +----------+ 
5. | 16777216 |  
6. +----------+ 
7. 1 row in set (0.00 sec) 

方法一,直接更改存儲引擎,代碼如下:

1. mysql> alter table tt7 engine=innodb; 
2. Query OK, 16777216 rows affected (2 min 39.80 sec) 
3. Records: 16777216  Duplicates: 0  Warnings: 0 

方法二,把方法一中的存儲引擎改回myisam,代碼如下:

1. mysql> alter table tt7 engine=myisam; 
2. Query OK, 16777216 rows affected (27.09 sec) 
3. Records: 16777216  Duplicates: 0  Warnings: 0 

從這里也可以看出myisam表要比innodb表快很多,創(chuàng)建個和tt7同樣表結(jié)構(gòu)的表,代碼如下:

1. mysql> create table tt7_tmp like tt7; 
2. Query OK, 0 rows affected (0.02 sec) 

tt7_tmp作為中間結(jié)果集,代碼如下:

1. mysql> insert into tt7_tmp select * from tt7; 
2.  
3. Query OK, 16777216 rows affected (27.20 sec) 
4. Records: 16777216  Duplicates: 0  Warnings: 0 

刪除原表的數(shù)據(jù),代碼如下:

1. mysql> truncate table tt7; 
2. Query OK, 16777725 rows affected (0.18 sec) 

這回更改原表的存儲引擎,代碼如下:

1. mysql> alter table tt7 engine=innodb; 
2. Query OK, 0 rows affected (0.06 sec) 
3. Records: 0  Duplicates: 0  Warnings: 0 

速度很快就完成了,再把中間結(jié)果集的數(shù)據(jù)導(dǎo)回原表中,代碼如下:

1. mysql> insert into tt7 select * from tt7_tmp; 
2.  
3. Query OK, 16777216 rows affected (2 min 0.95 sec) 
4. Records: 16777216  Duplicates: 0  Warnings: 0 

刪除中間表,代碼如下:

mysql> drop table tt7_tmp;

測試結(jié)果:方法二比較快一點,但是數(shù)據(jù)量要是比較大的話,方法二就要采用化整為零的分批操作的方式,否則insert操作將會具耗時,并產(chǎn)生大量的undo日志.

如果是小表的話,500M以內(nèi),根據(jù)自己系統(tǒng)的硬件環(huán)境,采用方法一就可以如果是大表的話,那就采用方法二+批量的方式.

如果是批量更改表的存儲引擎,用于生成變更的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';

根據(jù)自己系統(tǒng)配置修改如下參數(shù),以加快變更速度,記得以前的值,一會還得改回來,代碼如下:

1. SET GLOBAL sort_buffer_size=64*1024*1024; 
2. SET GLOBAL tmp_table_size=64*1024*1024; //Vevb.com
3. SET GLOBAL read_buffer_size=32*1024*1024; 
4. SET GLOBAL read_rnd_buffer_size=32*1024*1024; 

補充一下:MySql中有哪些存儲引擎？

1 MyISAM:這種引擎是mysql最早提供的,這種引擎又可以分為靜態(tài)MyISAM、動態(tài)MyISAM 和壓縮MyISAM三種.

靜態(tài)MyISAM:如果數(shù)據(jù)表中的各數(shù)據(jù)列的長度都是預(yù)先固定好的,服務(wù)器將自動選擇這種表類型,因為數(shù)據(jù)表中每一條記錄所占用的空間都是一樣的,所以這種表存取和更新的效率非常高,當數(shù)據(jù)受損時，恢復(fù)工作也比較容易做.

動態(tài)MyISAM:如果數(shù)據(jù)表中出現(xiàn)varchar、xxxtext或xxxBLOB字段時,服務(wù)器將自動選擇這種表類型,相對于靜態(tài)MyISAM，這種表存儲空間比較小,但由于每條記錄的長度不一,所以多次修改數(shù)據(jù)后,數(shù)據(jù)表中的數(shù)據(jù)就可能離散的存儲在內(nèi)存中,進而導(dǎo)致執(zhí)行效率下降,同時,內(nèi)存中也可能會出現(xiàn)很多碎片,因此,這種類型的表要經(jīng)常用optimize table 命令或優(yōu)化工具來進行碎片整理.

壓縮MyISAM:以上說到的兩種類型的表都可以用myisamchk工具壓縮,這種類型的表進一步減小了占用的存儲,但是這種表壓縮之后不能再被修改,另外,因為是壓縮數(shù)據(jù),所以這種表在讀取的時候要先時行解壓縮.

但是,不管是何種MyISAM表,目前它都不支持事務(wù),行級鎖和外鍵約束的功能.

2 MyISAM Merge引擎:這種類型是MyISAM類型的一種變種,合并表是將幾個相同的MyISAM表合并為一個虛表,常應(yīng)用于日志和數(shù)據(jù)倉庫.

3 InnoDB：InnoDB表類型可以看作是對MyISAM的進一步更新產(chǎn)品,它提供了事務(wù)、行級鎖機制和外鍵約束的功能.

4 memory(heap):這種類型的數(shù)據(jù)表只存在于內(nèi)存中,它使用散列索引,所以數(shù)據(jù)的存取速度非常快,因為是存在于內(nèi)存中,所以這種類型常應(yīng)用于臨時表中.

5 archive:這種類型只支持select 和 insert語句,而且不支持索引,常應(yīng)用于日志記錄和聚合分析方面.