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

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語句，而且不支援索引。常應用於日誌記錄和彙總分析方面。