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 空间（不受大文件限制）本文永久更新链接地址

首页 / 数据库 / MySQL / MySQL修改表的存储引擎（myisam&lt;=&gt;innodb）

首页 / 数据库 / MySQL / MySQL修改表的存储引擎（myisam<=>innodb）