MySQL数据库引擎:MyISAM和InnoDB(性能优化)

openkk 11年前

mysql数据库引擎:MyISAM和InnoDB(性能优化)


mysql 优化系列(一) Mysql数据库引擎性能测试 

Mysql 数据库中,最常用的两种引擎是innordb和myisam。Innordb的功能要比myiasm强大很多,但是innordb的性能要比myisam差很多,如果你的网站只是做简单的查询,更新,删除,那么用myiasm是最好的选择。

所有的性能测试在:Micrisoft window xp sp2 , Intel(R) Pentinum(R) M processor 1.6oGHz 1G 内存的电脑上测试。

测试结果:

测试方法:连续提交10个query, 表记录总数:38万 , 时间单位 s
 
引擎类型                    Myisam              innordb              性能相差
 count                      0.0008357           3.0163                 3609
 查询主键                 0.005708             0.1574                 27.57
 查询非主键               24.01                  80.37                  3.348
 更新主键                 0.008124             0.8183                100.7
 更新非主键              0.004141            0.02625               6.338
 插入                       0.004188              0.3694                88.21

结论:

1. 加了索引以后,对于Myisam查询可以加快:4 206.09733倍,对innordb 查询加快510.72921倍。同时对myisam更新速度减慢为原来的1/2,innordb的更新速度减慢为原来的1/30。大家要看情况决定是否要加索引,比如不查询的log表,不要做任何的索引。
2. 如果你的数据量是百万级别的,并且没有任何的事务处理,那么用myisam是性能最好的选择。
3. Innordb的表的大小更加的大,用myisam可以省很多的硬盘空间。

 

在我们测试的这个38w的表中,表占用空间的情况如下:
引擎类型                    MyIsam               InnorDB
 数据                       53,924 KB           58,976 KB
 索引                       13,640 KB           21,072 KB
 占用总空间             67,564 KB           80,048 KB
 
另外一个176W万记录的表, 表占用空间的情况如下:

引擎类型                MyIsam               InnorDB
 数据                   56,166 KB           90,736 KB
 索引                   67,103 KB           88,848 KB
 占用总空间        123,269 KB         179,584 KB

 

MyIsam引擎和InnorDB引擎简介:

MyISAM

    MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能,MyISAM还使用一种表格锁定的机制,来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZE TABLE命令,来恢复被更新机制所浪费的空间(学过数据结构的都应该知道,ISAM文件格式的缺陷)。MyISAM还有一些有用的扩展,例如用来修复数据库文件的MyISAMChk工具和用来恢复浪费空间的MyISAMPack工具。

MyISAM强调了快速读取操作,这可能就是为什么MySQL受到了Web开发如此青睐的主要原因:在Web开发中你所进行的大量数据操作都是读取操作。所以,大多数虚拟主机提供商和Internet平台提供商(Internet Presence Provider,IPP)只允许使用MyISAM格式。 MYISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。

InnorDB

InnoDB数据库引擎都是造就MySQL灵活性的技术的直接产品,这项技术就是MySQL++ API。在使用MySQL的时候,你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理也不支持外来键。尽管要比ISAM和 MyISAM引擎慢很多,但是InnoDB包括了对事务处理和外键的支持,这两点都是前两个引擎所没有的。如前所述,如果你的设计需要这些特性中的一者或者两者,那你就要被迫使用后两个引擎中的一个了。

Mysql 官方对InnorDB是这样解释的:

    InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要,因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中,你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来,甚至在同一个查询中也可以混合。

InnoDB是为处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。

    InnoDB存储引擎被完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表&索引在一个表空间中,表空间可以包含数个文件(或原始磁盘分区)。这与MyISAM表不同,比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统上。

    InnoDB默认地被包含在MySQL二进制分发中。Windows Essentials installer使InnoDB成为Windows上MySQL的默认表。

    InnoDB被用来在众多需要高性能的大型数据库站点上产生。著名的Internet新闻站点Slashdot.org运行在InnoDB上。 Mytrix, Inc.在InnoDB上存储超过1TB的数据,还有一些其它站点在InnoDB上处理平均每秒800次插入/更新的

 

  mysql 性能优化(二)

今天突然看到一年前写的 mysql 优化(一) ,感觉有些误人子弟。今天再补充一些东西。
关于引擎选择,从理论上 和 实际上 可能会有差距,所以,对待mysql 要有实验精神。
一般来说,
MYisam 适合:
1. 做很多count 的计算。
2. 插入不平凡,查询非常频繁。
3.  没有事务

innordb 非常适合:
1. 可靠性要求比较高,或者要求事务。
2. 表更新和查询都相当的频繁,并且表锁定的机会比较大的情况。

当然这也不是绝对的。要试验过才知道。
很多时候,性能瓶颈不是因为服务器配置不好,而是因为SQL不是很好。SQL的性能优化是关键。
除了这些常见的优化方法,
    还可以考虑 使用内存表。你测试一张myisam 和 内存表,会发现速度差不多。
其实,在并发比较强的时候,性能会相差大概五倍。我有张经常要查询的IP 地址 和 地区的 对应表,放入内存后。
以前查询 5000个 IP 地址大概 要 15S, 现在,只要 2S多。
很多东西,实际测试过才知道。用什么压力测试,测试出来的也不是很准确。