Mysql两种存储引擎的优缺点

原创 2012年03月24日 21:49:32
MyISAM引擎是一种非事务性的引擎,提供高速存储和检索,以及全文搜索能力,适合数据仓库等查询频繁的应用。MyISAM中,一个table实际保存为三个文件,.frm存储表定义,.MYD存储数据,.MYI存储索引。  NULL值被允许在索引的列中。

InnoDB:这种类型是事务安全的.它与BDB类型具有相同的特性,它们还支持外键.InnoDB表格速度很快.具有比BDB还丰富的特性, 因此如果需要一个事务安全的存储引擎,建议使用它.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表,InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁(locking on row level),提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lock escalation),因为 InnoDB 的列锁定(row level locks)适宜非常小的空间。InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。InnoDB 的设计目标是处理大容量数据库系统,它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上,InnoDB 是一套放在 MySQL 后台的完整数据库系统,InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。 InnoDB 把数据和索引存放在表空间里,可能包含多个文件,这与其它的不一样,举例来说,在 MyISAM 中,表被存放在单独的文件中。InnoDB 表的大小只受限于操作系统的文件大小,一般为 2 GB。InnoDB所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件,或者是独立的表空间文件),相对来说比较不好备份,免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump。

区别:
1.InnoDB不支持FULLTEXT类型的索引。
2.InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的。
3.对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
4.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
另外,InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”
可以用 show create table tablename 命令看表的类型。
2.1 对不支持事务的表做start/commit操作没有任何效果,在执行commit前已经提交
可以执行以下命令来切换非事务表到事务(数据不会丢失),innodb表比myisam表更安全:
alter table tablename type=innodb;

        MyISAM存储引擎的读锁和写锁是互斥的,读写操作是串行的。那么,一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同一表的写锁,MySQL如何处理呢?答案是写进程先获得锁。不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前!这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。这种情况有时可能会变得非常糟糕!myisam是有读锁和写锁(2个锁都是表级别锁)。

        MySQL表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。什么意思呢,就是说对MyISAM表进行读操作时,它不会阻塞其他用户对同一表的读请求,但会阻塞 对同一表的写操作;而对MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作。

        InnoDB行锁是通过给索引项加锁来实现的,即只有通过索引条件检索数据,InnoDB才使用行级锁,否则将使用表锁!行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。在InnoDB两个事务发生死锁的时候,会计算出每个事务影响的行数,然后回滚行数少的那个事务。当锁定的场景中不涉及Innodb的时候,innodb是检测不到的。只能依靠锁定超时来解决。

        数据库死锁例子:

两个sesison,第一个update表t1,不要提交。第二个update表t2,不要提交。接着session1去update表t2,session2去update表t1,此时就发生死锁了。


冷备份mysql和热备份mysql:

冷备份就是直接cp所有的数据库文件。

热备份:

1)myisam引擎。1、flush tables with read lock; 2、 cp......; 3、unlock tables;



作为数据库服务器的主机,最关键的就是主机的整体IO性能,包括磁盘、内存以及各种IO相关的板卡。


在Mysql中,使用orderby 关键字,排序方式有两种:

4.1版本以前的使用的是先取出需要排序的字段和整条记录的指针,然后对需要排序的字段在指定的排序区进行排序,排序之后,再根据指针去取出相应的记录。也就是说,这种算法需要访问数据两次。从4.1版本开始,采取了改进算法。一次性将所有的符合条件的记录取出。这样减少了随机IO(之前算法排序之后去记录为随机IO)。但是这种改进的方法极大的浪费了排序的时候所使用的内存。所以为了改进性能,query语句中尽量只取出我们需要的字段。


myql的profile可以用来分析查询语句的cpu和io的使用情况。

set profiling=1

select count(*) from user;

show profiles;

show profile for query 1;
最基本的我们可以从中看出,缓存对于mysql的性能影响。提高速度近30倍。

MySQL两种引擎的区别

Innodb引擎 Innodb引擎提供了对数据库ACID事务的支持,并且实现了SQL标准的四种隔离级别,关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这篇文章。该引擎还提供了行级锁和外键...
  • ls5718
  • ls5718
  • 2016年08月19日 10:15
  • 5944

MySQL存储引擎--MyISAM与InnoDB区别

MyISAM 和InnoDB 讲解   InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理等高级...
  • xifeijian
  • xifeijian
  • 2014年03月03日 22:29
  • 183173

Mysql中两种引擎的区别

mysql两种引擎Innodb和MyIasm Innodb提供了数据库事务的支持,并实现了sql标准的四种隔离级别,该引擎还提供了航迹锁和外键约束,它的设计目标是处理大容量数据库系统,它本身...
  • jjs_hollo
  • jjs_hollo
  • 2017年12月21日 10:35
  • 17

关于MySQL不同引擎使用简介

  • 2009年06月22日 17:28
  • 27KB
  • 下载

MySQL中的各种引擎

MySQL中的各种引擎
  • wangshuxuncom
  • wangshuxuncom
  • 2016年03月21日 11:18
  • 29602

mysql的引擎的区别

使用命令   show engines;--查看所有的引擎 Innodb 支持事务处理与外键和行级锁 MyISAM 不支持事务处理与外键和行级锁 只提供了表锁 (读取较多) federated存储引...
  • liaomin416100569
  • liaomin416100569
  • 2016年03月25日 16:47
  • 452

Mysql数据库引擎类型与特性对比

原文来自:MySQL数据库的各种存储引擎详解   MySQL有多种存储引擎,每种存储引擎有各自的优缺点,大家可以择优选择使用: MyISAM、InnoDB、MERGE、MEMORY(HEAP)...
  • jingqiang521
  • jingqiang521
  • 2016年05月16日 23:04
  • 2053

MySQL中四种常用存储引擎的介绍

MySQL常用的四种引擎的介绍(1):MyISAM存储引擎:不支持事务、也不支持外键,优势是访问速度快,对事务完整性没有 要求或者以select,insert为主的应用基本上可以用这个引擎来创建表支持...
  • qq_27028821
  • qq_27028821
  • 2016年08月21日 16:34
  • 5117

MySQL中的存储引擎讲解(InnoDB,MyISAM,Memory等各存储引擎对比)

MySQL中的存储引擎: 1、存储引擎的概念 2、查看MySQL所支持的存储引擎 3、MySQL中几种常用存储引擎的特点 4、存储引擎之间的相互转化...
  • QH_JAVA
  • QH_JAVA
  • 2013年11月05日 21:40
  • 22134

mysql 的存储引擎种类 以及分类 区别

这里主要介绍几种常用的存储引擎种类  MyISAM  InnoDB  MEMORY  MERGE  TokuDB 1. MyISAM  : 是旧版本mysql的默认引擎,现在默认引擎是Inn...
  • CaiCai_1908
  • CaiCai_1908
  • 2016年11月20日 19:16
  • 991
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Mysql两种存储引擎的优缺点
举报原因:
原因补充:

(最多只允许输入30个字)