共计 7899 个字符,预计需要花费 20 分钟才能阅读完成。
查看当前 MySQL 数据库的所支持的数据库引擎以及默认数据库引擎
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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) |
查看表的存储引擎
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | 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) |
直接更改存储引擎
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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) |
如下命令:
1 2 3 | mysql> alter table wholesale engine=innodb; Query OK, 1 row affected (0.32 sec) Records: 1 Duplicates: 0 Warnings: 0 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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) |
多表改变引擎:
1 2 3 | # mysqldump -uroot -p123456 sx >sx_bak.sql sed -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 空间(不受大文件限制)
本文永久更新链接地址 :http://www.linuxidc.com/Linux/2016-05/130891.htm
正文完
星哥玩云-微信公众号