萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> MySQL修改表存儲引擎方法總結

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

copyright © 萬盛學電腦網 all rights reserved