数据库引擎 InnoDB 和 MyISAM 有什么区别
大体区别为: MyISAM 类型不支持事务处理等高级处理, 而 InnoDB 类型支持. MyISAM 类型的表强调的是性能, 其执行效率比 InnoDB 类型更快, 但是不支持事务, 而 InnoDB 提供事务支持以及外键等高级数据库功能.
具体实现的区别:
InnoDB 不支持 FULLTEXT 类型的索引
InnoDB 中不保存表的具体行数, 也就是说, 执行查询 SQL 时, InnoDB 要扫描一遍整个表来计算有多少行, 而 MyISAM 只要简单的读出保存好的行数即可, 但是当包含 where 条件时, 两种表的操作是一样的
对于 AUTO_INCREMENT 类型的字段, InnoDB 中必须包含只有该字段的索引, 但是在 MyISAM 表中, 可以和其他的字段建立联合索引
执行删除 SQL 时, InnoDB 不会重新建立表, 而是一行一行的删除
LOAD TABLE FROM MASTER 操作对 InnoDB 是不起作用的. 解决方法是先把 InnoDB 表改成 MyISAM 表, 导入数据后再改成 InnoDB 表, 但是对于使用额外的 InnoDB 特性 (例如外键) 的表不适用
构成上的区别:
每个 MyISAM 在磁盘上存储分为三个文件. 第一个文件的名字以表的名字开始, 扩展名指出文件类型
.frm 文件存储表定义
.MYD 文件为数据文件
.MYI 文件为索引文件
基于磁盘的资源是 InnoDB 表空间的数据文件和它的日志文件, InnoDB 的表大小只受限于操作系统文件的大小, 一般为 2G
事物处理上的区别:
InnoDB 支持事物, MyISAM 不支持事物. 对于 InnoDB 每一条 SQL 语句都默认封装成事物, 自动提交, 这样会影响速度, 所以最好把多条 SQL 语句放在 begin 和 commit 之间, 组成一个事物.
InnoDB 支持外键, 而 MyISAM 不支持. 对一个包含外键的 InnoDB 表转换为 MyISAM 会失败.
InnoDB 是聚集索引, 数据文件是和索引绑在一起的, 必须有主键, 通过主键索引效率很高. 但是辅助索引需要两次查询, 先查询主键, 然后在通过主键查询到数据, 因此, 主键不应该过大, 因为主键太大, 其他索引也会很大. 而 MyISAM 是非聚焦索引, 数据文件是分离的, 索引保存的是数据文件的指针. 主键索引和辅助索引是独立的.
InnoDB 不保存表的具体行数, 执行 select count(*) from table 时需要全表扫描. 而 MyISAM 用一个变量保存了整个表的行数, 执行上述语句时只需要读出该变量即可, 速度很快.
InnoDB 不支持全文索引, 而 MyISAM 不支持全文索引, 查询效率上 MyISAM 要高
MyISAM 类型的表强调的是性能, 其执行速度比 InnoDB 类型更快, 但是不支持事物. InnoDB 支持事物, 外部键等高级数据库功能
如果执行大量的查询 select 操作, MyISAM 是更好的选择
如果执行大量的 insert 或者 update 操作, 出于性能方面的考虑, 应该使用 InnoDB 引擎
执行删除数据操作对 InnoDB 是不起作用的, 解决方法是首先把 InnoDB 表改成 MyISAM 表, 导入数据执行操作后再改成 InnoDB 表, 但是对于使用额外的 InnoDB 特性 (如外键) 的表不适应
对 AUTO_INCREMENT 的操作
MyISAM 为 Insert 和 update 操作自动更新, 这使得 AUTO_INCREMENT 列更快(至少 10%). 在序列顶的值被删除之后就不能再利用.(当 AUTO_INCREMENT 列被定义为多列索引的最后一列, 可以出现重复使用从序列顶部删除的值的情况)
AUTO_INCREMENT 的值可以用 ALTER 或 myisamch 来重置
对于 AUTO_INCREMENT 类型的字段, InnoDB 中必须包含只有该字段的索引, 但是在 MyISAM 表中, 可以和其他的字段一起建立联合索引
mysql 中的锁:
锁是计算机协调多个进程或线程对某一资源并发访问的机制.
Mysql 中的锁分为表锁和行锁: 顾名思义, 表锁就是锁住一张表, 而行锁就是锁住一行.
表锁的特点: 开销小, 不会产生死锁, 发生锁冲突的概率高, 并且并发度低.
行锁的特点: 开销大, 会产生死锁, 发生锁冲突的概率低, 并发度高.
因此 MyISAM 引擎采用的是表锁, 而 InnoDB 存储引擎采用的是行锁.
如何选择数据库引擎
业务是否需要支持事物, 如果需要选择 InnoDB, 如果不需要可以考虑 MyISAM
如果表中绝大多数都只是查询操作, 可以考虑 MyISAM, 如果读写操作频繁, 则使用 InnoDB
需要考虑系统崩溃后, MyISAM 恢复起来更困难, 能否接受
MySQL5.5 版本开始 InnoDB 已经成为 MySQL 的默认引擎(之前是 MyISAM)
MySQL 字段宽度
MySQL 类型关键字后面的括号内指定整数值的显示宽度 (例如, INT(11)). 该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度. 显示宽度并不限制可以在列内保存的值的范围, 也不限制超过列的指定宽度的值的显示. 所以 INT(1) 和 INT(11)默认是没有任何区别的!!!
当结合可选扩展属性 ZEROFILL 使用时, 默认补充的空格用零代替. 例如, 对于声明为 INT(5) ZEROFILL 的列, 值 4 检索为 00004. 请注意如果在整数列保存超过显示宽度的一个值, 当 MySQL 为复杂联接生成临时表时会遇到问题, 因为在这些情况下 MySQL 相信数据适合原列宽度.
所有整数类型可以有一个可选 (非标准) 属性 UNSIGNED. 当你想要在列内只允许非负数和该列需要较大的上限数值范围时可以使用无符号值 . 如果设置了 ZEROFILL 扩展属性试, 默认就有了无符号属性(UNSIGNED)
所以 INT(1)与 INT(11)后的括号中的字符表示显示宽度, 整数列的显示宽度与 MySQL 需要用多少个字符来显示该列数值, 与该整数需要的存储空间的大小都没有关系, INT 类型的字段能存储的数据上限还是 2147483647(有符号型)和 4294967295(无符号型). 其实当我们在选择使用 INT 的类型的时候, 不论是 INT(1)还是 INT(11), 它在数据库里面存储的都是 4 个字节的长度.
INT(M) ZEROFILL, 加上 ZEROFILL 后 M 才表现出不同, 比如 INT(3) ZEROFILL, 你插入到数据库里的是 10, 则实际插入为 010, 也就是在前面补充加了一个 0. 如果 INT(3)和 INT(10)不加 ZEROFILL, 则它们没有什么区别.M不是用来限制 INT 列内保存值的范围的.int(M)的最大值和最小值与 UNSIGNED 有关.
总体来说, 两种类型最主要的区别就是 InnoDB 支持事物处理与外键和行级锁. 而 MyISAM 不支持. 所以 MyISAM 往往会被认为只适合在小项目中使用, 而如果就方便性和高扩展性来说, MyISAM 绝对是首选. 原因如下:
1, 平台上承载的大部分项目是读多写少的项目, 而 MyISAM 的读性能是比 Innodb 强不少的.
2,MyISAM 的索引和数据是分开的, 并且索引是有压缩的, 内存使用率就对应提高了不少. 能加载更多索引, 而 Innodb 是索引和数据是紧密捆绑的, 没有使用压缩从而会造成 Innodb 比 MyISAM 体积庞大不小.
3, 经常隔 1,2 个月就会发生应用开发人员不小心 update 一个表 where 写的范围不对, 导致这个表没法正常用了, 这个时候 MyISAM 的优越性就体现出来了, 随便从当天拷贝的压缩包取出对应表的文件, 随便放到一个数据库目录下, 然后 dump 成 sql 再导回到主库, 并把对应的 binlog 补上. 如果是 Innodb, 恐怕不可能有这么快速度, 别和我说让 Innodb 定期用导出 xxx.sql 机制备份, 因为最小的一个数据库实例的数据量基本都是几十 G 大小.
4, 从接触的应用逻辑来说, select count(*) 和 order by 是最频繁的, 大概能占了整个 sql 总语句的 60% 以上的操作, 而这种操作 Innodb 其实也是会锁表的, 很多人以为 Innodb 是行级锁, 那个只是 where 对它主键是有效, 非主键的都会锁全表的.
5, 还有就是经常有很多应用部门需要我给他们定期某些表的数据, MyISAM 的话很方便, 只要发给他们对应那表的 frm.MYD,MYI 的文件, 让他们自己在对应版本的数据库启动就行, 而 Innodb 就需要导出 xxx.sql 了, 因为光给别人文件, 受字典数据文件的影响, 对方是无法使用的.
6, 如果和 MyISAM 比 insert 写操作的话, Innodb 还达不到 MyISAM 的写性能, 如果是针对基于索引的 update 操作, 虽然 MyISAM 可能会逊色 Innodb, 但是那么高并发的写, 从库能否追的上也是一个问题, 还不如通过多实例分库分表架构来解决.
7, 如果是用 MyISAM 的话, merge 引擎可以大大加快应用部门的开发速度, 他们只要对这个 merge 表做一些 select count(*)操作, 非常适合大项目总量约几亿的 rows 某一类型 (如日志, 调查统计) 的业务表.
8, 当然 Innodb 也不是绝对不用, 用事务的项目就用 Innodb 的. 另外, 可能有人会说你 MyISAM 无法抗太多写操作, 但是可以通过架构来弥补.
事物有哪几个特性:
原子性
一致性
隔离性
持久性
InnoDB 中的事务隔离级别和锁的关系
一次封锁 && 两段锁
一次封锁: 因为有大量的并发访问, 为了预防死锁, 一般应用中推荐使用一次封锁法. 就是在方法的开始阶段, 已经预先知道了会用到哪些数据, 然后全部锁住, 在方法运行之后, 再全部解锁. 这种方式可以有效的避免循环死锁. 但是这种方式在数据库中却并不适用, 因为在事务开始阶段, 数据库并不知道会用到哪些数据.
两段锁
数据库遵循的是两段锁协议, 将事务分成两个阶段, 加锁阶段和解锁阶段
虽然这种方式无法避免死锁, 但是两段锁协议可以保证事务的并发调度是串行化的(串行化很重要, 尤其是在数据库恢复和备份的时候)
加锁阶段: 在该阶段可以进行加锁操作. 在对任何数据进行读操作之前要申请并获得 S 锁(共享锁), 其他事务可以继续加共享锁, 但不能加排它锁. 在进行写操作之前要申请并获得 X 锁(排它锁), 其他事务不能再获得任何锁. 如果锁不成功, 则事务进入等待状态, 直到加锁成才能继续执行
解锁阶段: 当事务释放了一个封锁之后, 事务进入解锁极端, 在该阶段只能进行解锁不能再进行其他加锁操作
事务的四种隔离级别
在数据库操作中, 为了有效保证并发读取数据的正确性, 提出的事务隔离级别. 数据库锁也是为了构建这些级别存在的.
未提交读 : 允许脏读, 也就是可能读取到其他会话中未提交事务修改的数据
提交读 : 只能读取到已经提交的数据. Oracle 等多数数据库默认都是该级别
可重复读 : 可重复读取数据. 在同一个事务内的查询都是事务开始时保持一致的. 在 SQL 标准中, 该隔离级别消除了不可重复读, 但是还存在幻读
串行读 : 完全串行化的读取数据. 每次读都需要获得表级共享锁, 读写相互都会堵
MySQL 中的锁
MySQL 中锁的种类有很多, 有常见的表锁和行锁, 也有新加入的 Metadata Lock 等等.
表锁是对一整张表加锁, 虽然可分为读锁和写锁, 但毕竟是锁住整张表, 会导致并发能力下降, 一般是做 DDL 处理时使用
行锁是锁住数据行, 这种加速方式比较复杂, 但是由于只锁住有限的数据, 对于其他数据不加锁, 所以并发能力强, MySQL 一般都是用行锁来处理并发事务, 行锁可以防止不同事务版本额数据修改提交时造成的数据冲突的情况
在 RC(Read Committed => 读取提交内容)级别中, 数据的读取都是不加锁的, 但是数据的写入, 修改, 删除是需要加锁的
由于 MySQL 的 InnoDB 默认是使用 RR 级别, 所以需要先将该 session 开启成 RC 级别, 并且设置 binlog 的模式
如果一个条件无法通过索引快速过滤, 存储引擎层面就会将所有记录加锁后返回, 再由 MySQL Server 层进行过滤
但在实际使用过程中, MySQL 做了一些改进, 在 MySQL Server 过滤条件, 发现不满足后, 会调用 unlock_row 方法, 把不满足条件的记录释放锁(违背了二段协议的约束). 这样做, 保证了最后只会持有满足条件记录上的锁, 但是每条记录的加锁操作还是不能省略. 这种情况同样适用于 MySQL 的默认隔离级别 RR. 所以对一个数据量很大的表做批量修改的时候, 如果无法使用相应的索引, MySQL Server 过滤数据的时候特别慢, 就会出现虽然没有修改某些行的数据, 但是它们还是被锁住了的现象.
不可重读和幻读的区别:
Repeattable Read(可重读), 这是 MySQL 中 InnoDB 默认的隔离级别. 可重读这个概念是一事务的多个实例在并发读取数据时, 会看到同样的数据行. 在 MySQL 的 RR 级别中, 解决了幻读的问题
不可重读重点在于 update 和 delete, 而幻读的重点在于 insert
如果使用锁机制来实现这两种隔离级别, 在可重复读中, 该 sql 第一次读取到数据后, 就将这些数据加锁, 其他事务无法读取这些数据, 就可以实现可重复读了. 但这种方法无法锁住 insert 数据, 所以当事务 A 先前读取了数据或者修改了全部数据, 事务 B 还是可以 insert 数据提交, 这时事务 A 就会莫名其妙多了一条之前没有的数据, 这就是幻读, 不能通过行锁来避免. 需要 Serializable 隔离级别, 读用读锁, 写用写锁, 读锁和写锁互斥, 这么做可以有效避免幻读, 不可重复读, 脏读等问题, 但是会极大的降低数据库的并发能力.
不可重复读和幻读的最大区别, 就在于如何通过锁机制来解决他们产生的问题. 可以使用悲观锁机制来处理这两种问题, 但是 MySQL,Oracle,PostgreSQL 等成熟的数据库, 出于性能考虑, 都是使用了乐观锁为理论基础的 MVCC(多版本并发控制)来避免这两种问题
乐观锁和悲观锁
悲观锁 ==> 它指的是对数据被外界 (包括本系统当前的其他事务, 以及来自外部系统的事务处理) 修改持保守态度, 因此, 在整个数据处理过程中, 将数据处于锁定状态
悲观锁的实现, 往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性, 否则, 即使在本系统中实现了加锁机制, 也无法保证外部系统不会修改数据).
在悲观锁的情况下, 为了你保证事务的隔离性, 就需要一致性锁定读. 读取数据时给加锁, 其它事务无法修改这些数据. 修改数据时也要加锁, 其它事务无法读取这些数据.
悲观锁大多数情况下依靠数据库的锁机制实现, 以保证操作最大程度的独立性. 但随之而来的就是数据库性能的大量开销, 特别是对长事务而言, 这样的开销往往无法承受
乐观锁 ==> 相对于悲观锁, 乐观锁机制采取了更加宽松的加锁机制. 乐观锁, 大多数是基于数据版本 (为数据增加一个版本标识) 记录机制实现, 再基于数据库表的版本解决方案中, 一般是通过为数据库表在哪个家一个 version 字段来实现. 读取数据时, 将此版本一同读出, 之后更新时, 对此版本加一. 此时, 将提交数据的版本数据与数据库表对应的当前版本信息进行对比, 如果提交数据的版本号大于数据库表当前版本, 则予以更新, 否则认为是过期数据
MVCC 在 MySQL 的 InooDB 的实现
MVCC 的实现没有固定的规范, 每个数据库都会有不同的实现方式
在 InnoDB 中, 会在每行数据后添加两个额外的隐藏的值来实现 MVCC, 这两个值一个记录这行数据何时被创建, 另外一个记录这行数据何时过期(或者被删除). 在实际操作中, 存储的并不是时间, 而是事务的版本号, 每次开启一个新事务, 事务的版本号就会递增. 在可重读 Repeatable reds 事务隔离级别下:
select 时, 读取创建版本号 <= 当前事务版本号, 删除版本号为空或> 当前事务版本号.
insert 时, 保存当前事务版本号为行的创建版本号
delete 时, 保存当前事务版本号为行的删除版本号
update 时, 插入一条新纪录, 保存当前事务版本号为行创建版本号, 同时保存当前事务版本号到原来删除的行
通过 MVCC, 虽然每行记录都需要额外的存储空间, 更多的行检查工作以及一些额外的维护工作, 但可以减少锁的使用, 大多数操作都不用加锁, 读取数据操作很简单, 性能很好, 并且也能保证只会读取到符合标准的行, 也只锁住必要行
MySQL 中的 "读" 与事务隔离级别中的 "读" 的区别
在 RR 级别中, 虽然让数据变得可重复读, 但是我们读到的数据可能是历史数据, 是不及时的数据, 不是数据库当前的数据! 这在一些对于数据的时效特别敏感的业务中, 就可能出现问题. 对于这种读取历史数据的方式, 叫做快照读, 而在读取数据库当前版本数据的方式, 叫做当前读. 显然, 在 MVCC 中, 快照读就是 select, 当前读是特殊的读操作, insert/update/delete 操作, 属于当前读, 处理的都是当前度的数据, 需要加锁
事务的隔离级别实际上都是定义了当前读的级别, MySQL 为了减少锁的处理 (包括等待其他锁) 的时间, 提升并发能力, 引入了快照读的概念, 使得 select 不用加锁, 而 update,insert,delete 这些 "当前读", 就需要另外的模块来解决
当前读: 事务的隔离级别中虽然定义了读数据的要求, 实际上这也可以说是写数据的要求. 为了解决当前读中的幻读问题, MySQL 事务使用了 Next-Key 锁
Next-key 锁是行锁和 GAP(间隙锁)的合并, 行锁防止别的事务修改或删除, GAP 锁防止别的事务新增, 行锁和 GAP 锁结合形成的 Next-Key 锁共同解决了 RR 级别在写数据时的幻读问题
Serializable 级别: 读操作加共享锁, 写操作加排他锁, 读写互斥. 使用的悲观锁的理论, 实现简单, 数据更加安全, 但是并发能力非常差. 如果业务并发的特别少或者没有并发, 同时数据及时可靠, 可以使用这种模式, 在 Serializable 这个级别, select 还是会加锁的
来源: http://www.bubuko.com/infodetail-2606115.html