MySQL修改表存儲引擎方法總結

在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)

 

方法一：

直接更改存儲引擎

 代碼如下 復制代碼 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;

補充一下

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語句，而且不支持索引。常應用於日志記錄和聚合分析方面。