MySQL存储引擎:InnoDB还是MyISAM?
故事还要从早些时候说起,这个月初,有个网友在我的留言板留言:
博客速度很慢啊,很慢很慢很慢啊,像蜗牛一样啊,有木有啊各位同学?
实话说我之前并没有重视这个问题,以为只是服务器不稳定的关系,看到这条留言以后我便开始研究起这个问题。经过重复试验,现象是博客所有页面在浏览器里加载的时候,在加载完顶部Header与导航条之后,页面加载会在很长一段时间内完全没有进展。也就是说加载到导航条之后,会卡住半天,过了很久很久才会继续加载后面的部分。
开始我怀疑是服务器的问题,后来我检查了一下,发现整个页面的HTML代码确实完全发送给浏览器了,是在浏览器渲染的阶段卡住的,开始分别用IE和Chrome的F12检查网络传输状态,发现是导航条右端的集成Google自定义搜索出的问题。原来Google自定义搜索改版了,原先引用的js脚本位置变化了,浏览器获取不到js,等待很久,直到超时,所以页面在加载到导航条的时候会卡住很久。修复问题的时候发现Google自定义搜索的架构已经变了,似乎一时不太好用原来的方法集成进Wordpress,所以切回自带的搜索功能,此问题解决。
至此我以为博客速度慢的问题已经完全找到并解决,但是貌似实事并非如此,我开始发现我的整个Wordpress的所有页面的执行都显得很慢,不管是前台还是后台。
我又怀疑到服务器性能上了,传了几个探针上去,我发现纯PHP的执行效率并不慢,甚至可以说是非常快的。那么,看来问题出在数据库上,是数据库在拖速度了。我甚至发现,我每次清空由Akismet拦截的垃圾评论的时候,整个后台会完全卡死,直到达到PHP脚本超时时间,才删除了十几条数据,这样每次都要花费我好几分钟的时间去清空近1000条垃圾评论。这数据库的性能也差得太夸张了吧?!
我开始怀疑是由于数据库用久了导致的,呃比如类似于硬盘上的磁盘碎片的情况,反复增加及删除导致的性能下降。于是我把数据库导出成SQL文件,重新建立一个新的数据库再导入。按理说这样应该可以达到最好的性能了,可是我发现情况没有任何改善,每次点清空垃圾评论的时候还是会跑到脚本超时,而且还只删除了十几条数据。而且整个前后台的处理速度还是很慢,每个页面经常都要花费好几秒来加载。
既然已经确认了是MySQL数据库的问题在拖速度,我开始找原因。我在pma里随意乱逛数据库的时候,无意间发现了我所有Wordpress表的引擎都是InnoDB,但是MySQL默认的引擎却是MyISAM。难道是这个的问题么?我开始搜索资料查InnoDB和MyISAM的区别。
InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持已经外部键等高级数据库功能。
以下是一些细节和具体实现的差别:
◆1.InnoDB不支持FULLTEXT类型的索引。
◆2.InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的。
◆3.对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
◆4.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
◆5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
还有一个表格把区别列的更清晰:
MyISAM | InnoDB | |
构成上的区别: | 每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。 .frm文件存储表定义。 数据文件的扩展名为.MYD (MYData)。 索引文件的扩展名是.MYI (MYIndex)。 |
基于磁盘的资源是InnoDB表空间数据文件和它的日志文件,InnoDB 表的大小只受限于操作系统文件的大小,一般为 2GB |
事务处理上方面: | MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持 | InnoDB提供事务支持事务,外部键等高级数据库功能 |
SELECT UPDATE,INSERT,Delete操作 |
如果执行大量的SELECT,MyISAM是更好的选择 | 1.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表 2.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。 3.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用 |
对AUTO_INCREMENT的操作 | 每表一个AUTO_INCREMEN列的内部处理。 MyISAM为INSERT和UPDATE操作自动更新这一列。这使得AUTO_INCREMENT列更快(至少10%)。在序列顶的值被删除之后就不能再利用。(当AUTO_INCREMENT列被定义为多列索引的最后一列,可以出现重使用从序列顶部删除的值的情况)。 AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置 对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引 更好和更快的auto_increment处理 |
如果你为一个表指定AUTO_INCREMENT列,在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器,它被用在为该列赋新值。自动增长计数器仅被存储在主内存中,而不是存在磁盘上关于该计算器的算法实现,请参考AUTO_INCREMENT列在InnoDB里如何工作 |
表的具体行数 |
select count(*) from table,MyISAM只要简单的读出保存好的行数,注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的 | InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行 |
锁 |
表锁 | 提供行锁(locking on row level),提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs),另外,InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%” |
简单的说,就是InnoDB的功能更强大,而MyISAM的性能更好,尤其是在大量执行Select和Delete的时候。
然后我就把所有的表都转成MyISAM表,发现性能改善完全不只一点点啊,不管前后台的页面显示都几乎是瞬间完成。我还尝试进行清空垃圾评论的操作,发现执行的也非常快。
所以在这里给所有用Wordpress的朋友一个小提醒:如果你发现你的Wordpress很慢的时候,检查一下你的MySQL表是不是用了InnoDB引擎。
😆 嗯,谢谢博主~~哈哈哈哈
@Joice
我顺便把你的WP表也改了MyISAM引擎了, 你应该能感觉到明显快了
我的就是myisam的,但还是慢。
虚拟主机,只能使用phpmyadmin管理,不知道怎样改回到myisam