Home > Web > MySQL存储引擎:InnoDB还是MyISAM?

MySQL存储引擎:InnoDB还是MyISAM?

November 22nd, 2012 10:40 Leave a comment Go to comments

故事还要从早些时候说起,这个月初,有个网友在我的留言板留言

博客速度很慢啊,很慢很慢很慢啊,像蜗牛一样啊,有木有啊各位同学?

实话说我之前并没有重视这个问题,以为只是服务器不稳定的关系,看到这条留言以后我便开始研究起这个问题。经过重复试验,现象是博客所有页面在浏览器里加载的时候,在加载完顶部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,INSERTDelete操作
  如果执行大量的SELECT,MyISAM是更好的选择   1.如果你的数据执行大量的INSERTUPDATE,出于性能方面的考虑,应该使用InnoDB表
2.DELETE   FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
3.LOAD   TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用
  AUTO_INCREMENT的操作   每表一个AUTO_INCREMEN列的内部处理。
MyISAMINSERTUPDATE操作自动更新这一列。这使得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引擎。

分享到:
Categories: Web Tags: , , , , , (4,178 views)
  1. November 22nd, 2012 at 15:18 | #1

    😆 嗯,谢谢博主~~哈哈哈哈

  2. November 22nd, 2012 at 15:19 | #2

    @Joice
    我顺便把你的WP表也改了MyISAM引擎了, 你应该能感觉到明显快了

  3. February 14th, 2014 at 11:10 | #3

    我的就是myisam的,但还是慢。

  4. September 5th, 2014 at 14:46 | #4

    虚拟主机,只能使用phpmyadmin管理,不知道怎样改回到myisam

  1. July 22nd, 2014 at 11:25 | #1