MySQL修改表的儲存引擎(myisam<=>innodb)

MySQL修改表的儲存引擎(myisam<=>innodb)

查看當前MySQL資料庫的所支援的資料庫引擎以及預設資料庫引擎

123456789101112131415

mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine            | Support | Comment                                                        | Transactions | XA  | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| PERFORMANCE_SCHEMA | YES    | Performance Schema                                            | NO          | NO  | NO        || MRG_MYISAM        | YES    | Collection of identical MyISAM tables                          | NO          | NO  | NO        || CSV                | YES    | CSV storage engine                                            | NO          | NO  | NO        || BLACKHOLE          | YES    | /dev/null storage engine (anything you write to it disappears) | NO          | NO  | NO        || MEMORY            | YES    | Hash based, stored in memory, useful for temporary tables      | NO          | NO  | NO        || InnoDB            | DEFAULT | Supports transactions, row-level locking, and foreign keys    | YES          | YES  | YES        || ARCHIVE            | YES    | Archive storage engine                                        | NO          | NO  | NO        || MyISAM            | YES    | MyISAM storage engine                                          | NO          | NO  | NO        || FEDERATED          | NO      | Federated MySQL storage engine                                | NULL        | NULL | NULL      |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+9 rows in set (0.00 sec)

 查看錶的儲存引擎

1234567891011121314151617181920212223242526272829303132333435

mysql> show tables;+----------------------------------------------+| Tables_in_performance_schema                |+----------------------------------------------+| cond_instances                              || events_waits_current                        || events_waits_history                        || events_waits_history_long                    || events_waits_summary_by_instance            || events_waits_summary_by_thread_by_event_name || events_waits_summary_global_by_event_name    || file_instances                              || file_summary_by_event_name                  || file_summary_by_instance                    || mutex_instances                              || performance_timers                          || rwlock_instances                            || setup_consumers                              || setup_instruments                            || setup_timers                                || threads                                      |+----------------------------------------------+17 rows in set (0.00 sec) mysql> show create table threads;+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table  | Create Table                                                                                                                                                                      |+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| threads | CREATE TABLE `threads` (  `THREAD_ID` int(11) NOT NULL,  `PROCESSLIST_ID` int(11) DEFAULT NULL,  `NAME` varchar(128) NOT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

直接更改儲存引擎

12345678910111213141516

mysql> show create table wholesale;+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table            | Create Table                                                                                                                                                                                                                                                                                                                                                                                                      |+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| jindong_wholesale | CREATE TABLE `wholesale` (  `act_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,  `goods_id` mediumint(8) unsigned NOT NULL,  `goods_name` varchar(255) NOT NULL,  `rank_ids` varchar(255) NOT NULL,  `prices` text NOT NULL,  `enabled` tinyint(3) unsigned NOT NULL,  PRIMARY KEY (`act_id`),  KEY `goods_id` (`goods_id`) USING BTREE) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

 如下命令：

123	mysql> alter table wholesale engine=innodb;Query OK, 1 row affected (0.32 sec)Records: 1  Duplicates: 0  Warnings: 0

 

12345678910111213141516

mysql> show create table wholesale;+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table            | Create Table                                                                                                                                                                                                                                                                                                                                                                                                      |+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| jindong_wholesale | CREATE TABLE `wholesale` (  `act_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,  `goods_id` mediumint(8) unsigned NOT NULL,  `goods_name` varchar(255) NOT NULL,  `rank_ids` varchar(255) NOT NULL,  `prices` text NOT NULL,  `enabled` tinyint(3) unsigned NOT NULL,  PRIMARY KEY (`act_id`),  KEY `goods_id` (`goods_id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

 多表改變引擎：

123	# mysqldump -uroot -p123456 sx >sx_bak.sqlsed -i "s/MyISAM/InnoDB/g" sx_bak.sql mysql -uroot -p123456 sx <sx_bak.sql

 首先我目前平台上承載的大部分項目是讀多寫少的項目，而MyISAM的讀效能是比Innodb強不少的。

MyISAM的索引和資料是分開的，並且索引是有壓縮的，記憶體使用量率就對應提高了不少。能載入更多索引，而Innodb是索引和資料是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大不小。

從我接觸的應用邏輯來說，select count(*) 和order by 是最頻繁的，大概能佔了整個sql總語句的60%以上的操作，而這種操作Innodb其實也是會鎖表的，很多人以為Innodb是行級鎖，那個只是 where對它主鍵是有效，非主鍵的都會鎖全表的。

 還有就是經常有很多應用部門需要我給他們定期某些表的資料，MyISAM的話很方便，只要發給他們對應那表的frm.MYD,MYI的檔案，讓他們自己在 對應版本的資料庫啟動就行，而Innodb就需要匯出xxx.sql了，因為光給別人檔案，受字典資料檔案的影響，對方是無法使用的如果和MyISAM比insert寫操作的話，Innodb還達不到MyISAM的寫效能，如果是針對基於索引的update操作，雖然MyISAM可能會遜色Innodb,但是那麼高並發的寫，從庫能否追的上也是一個問題，還不如通過多執行個體分庫分表架構來解決MyISAM 相對更適合插入不多不頻繁，查詢較多的應用環 WAL(write ahead logging)InnoDB適合大並發寫入和查詢的環境：支援事務（ACID 空間（不受大檔案限制）

本文永久更新連結地址：