mysql修改表引擎myisam_mysql修改表的存储引擎(myisam<=>innodb)

查看当前数据库的所支持的数据库引擎以及默认数据库引擎

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)

查看表的存储引擎

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)

直接更改存储引擎

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)

如下命令:

mysql> alter table wholesale engine=innodb;

Query OK, 1 row affected (0.32 sec)

Records: 1 Duplicates: 0 Warnings: 0

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)

多表改变引擎:

# mysqldump -uroot -p123456 sx >sx_bak.sql

sed -i "s/MyISAM/InnoDB/g" sx_bak.sql

mysql -uroot -p123456 sx

首先我目前平台上承载的大部分项目是读多写少的项目,而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 空间(不受大文件限制)

http://www.cnblogs.com/littlehb/archive/2013/04/20/3032658.html

http://blog.163.com/yang_jianli/blog/static/161990006201010175122563/

http://www.cnblogs.com/littlehb/archive/2013/04/20/3032658.html

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:游动-白 设计师:我叫白小胖 返回首页
评论

打赏作者

偏差一毫厘

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值