注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

@fc_lamp

关注Web应用解决方案MySql/PHP/Python一盏名为"飞川"的灯~

 
 
 

日志

 
 

MySQL数据库性能优化之存储引擎选择(MyISAM与INnODB)  

2012-04-27 12:10:00|  分类: Web技术-Mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

作者:Sky.Jian

原文: http://isky000.com/database/mysql-performance-tuning-storage-engine

由于各存储引擎功能特性差异较大,这篇文章主要是介绍如何来选择合适的存储引擎来应对不同的业务场景。

  • MyISAM
    • 特性
      1. 不支持事务:MyISAM存储引擎不支持事务,所以对事务有要求的业务场景不能使用
      2. 表级锁定:其锁定机制是表级索引,这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能
      3. 读写互相阻塞:不仅会在写入的时候阻塞读取,MyISAM还会在读取的时候阻塞写入,但读本身并不会阻塞另外的读
      4. 只会缓存索引:MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO,但是这个缓存区只会缓存索引,而不会缓存数据
    • 适用场景
      1. 不需要事务支持(不支持)
      2. 并发相对较低(锁定机制问题)
      3. 数据修改相对较少(阻塞问题)
      4. 以读为主
      5. 数据一致性要求不是非常高
    • 最佳实践
      1. 尽量索引(缓存机制)
      2. 调整读写优先级,根据实际需求确保重要操作更优先
      3. 启用延迟插入改善大批量写入性能
      4. 尽量顺序操作让insert数据都写入到尾部,减少阻塞
      5. 分解大的操作,降低单个操作的阻塞时间
      6. 降低并发数,某些高并发场景通过应用来进行排队机制
      7. 对于相对静态的数据,充分利用Query Cache可以极大的提高访问效率
      8. MyISAM的Count只有在全表扫描的时候特别高效,带有其他条件的count都需要进行实际的数据访问
  • InnoDB
    • 特性
      1. 具有较好的事务支持:支持4个事务隔离级别,支持多版本读
      2. 行级锁定:通过索引实现,全表扫描仍然会是表锁,注意间隙锁的影响
      3. 读写阻塞与事务隔离级别相关
      4. 具有非常高效的缓存特性:能缓存索引,也能缓存数据
      5. 整个表和主键以Cluster方式存储,组成一颗平衡树
      6. 所有Secondary Index都会保存主键信息
    • 适用场景
      1. 需要事务支持(具有较好的事务特性)
      2. 行级锁定对高并发有很好的适应能力,但需要确保查询是通过索引完成
      3. 数据更新较为频繁的场景
      4. 数据一致性要求较高
      5. 硬件设备内存较大,可以利用InnoDB较好的缓存能力来提高内存利用率,尽可能减少磁盘 IO
    • 最佳实践
      1. 主键尽可能小,避免给Secondary index带来过大的空间负担
      2. 避免全表扫描,因为会使用表锁
      3. 尽可能缓存所有的索引和数据,提高响应速度
      4. 在大批量小插入的时候,尽量自己控制事务而不要使用autocommit自动提交
      5. 合理设置innodb_flush_log_at_trx_commit参数值,不要过度追求安全性
      6. 避免主键更新,因为这会带来大量的数据移动
  • NDBCluster
    • 特性
      1. 分布式:分布式存储引擎,可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分
      2. 支持事务:和Innodb一样,支持事务
      3. 可与mysqld不在一台主机:可以和mysqld分开存在于独立的主机上,然后通过网络和mysqld通信交互
      4. 内存需求量巨大:新版本索引以及被索引的数据必须存放在内存中,老版本所有数据和索引必须存在与内存中
    • 适用场景
      1. 具有非常高的并发需求
      2. 对单个请求的响应并不是非常的critical
      3. 查询简单,过滤条件较为固定,每次请求数据量较少,又不希望自己进行水平Sharding
    • 最佳实践
      1. 尽可能让查询简单,避免数据的跨节点传输
      2. 尽可能满足SQL节点的计算性能,大一点的集群SQL节点会明显多余Data节点
      3. 在各节点之间尽可能使用万兆网络环境互联,以减少数据在网络层传输过程中的延时

注:以上三个存储引擎是目前相对主流的存储引擎,还有其他类似如:Memory,Merge,CSV,Archive等存储引擎的使用场景都相对较少,这里就不一一分析了,如果有朋友感兴趣,后面再补充吧。



区别总结:

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特性(例如外键)的表不适用。

另外,InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”

6 文件存储方式:MySQL为表把它的数据词典信息以.frm文件的形式存在数据库目录里,这对所有MySQL存储引擎都是真的。

每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

      每个InnoDB表在表空间内的InnoDB内部数据词典里有它自己的条目。当MySQL移除表或数据库,它不得不删除.frm文件和InnoDB数据词典内的相应条目。这就是为什么你不能在数据库之间简单地移动.frm文件来移动InnoDB表。


#更多:http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#table-and-index

   

     





mysql 中有 B-Tree索引、 Hash索引、 Fulltext索引 、R-Tree索引 这四种方式的索引。

mysql 中有 INDEX(普通索引)、UNIQUE(唯一索引)、PRIMARY KEY(主键)、 联合索引。

不过,Fulltext索引 只有 MyISAM引擎支持。另外字段中也只有:CHAR VARCHAR TEXT
数据类型支持。

Fulltext 索引主要用来替代效率低下的 LIKE '%***%' 操作。实际上,Full-text 索引并不是只能简单

地替代传统的全模糊 LIKE 操作,它能通过多字段组合的 Full-text 索引一次全模糊匹配多个字段。
比如:给title、description字段添加全文索引:

alter table test add FULLTEXT(title,description);

查询
select xx from xxx where MATCH(title,description) against('xxx');


  评论这张
 
阅读(258)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017