我都把MySql啃得这么透彻了,你还不进来看一下吗?

187 阅读7分钟

在数据库中存的就是一张张有着千丝万缕关系的表,所以表的设计的好坏,将直接影像这整个数据库。而在设计表的时候,我们都关注一个问题,使用什么存储引擎。接下来小编将重点为大家介绍对比两种常见的innodb和MyISAM搜索引擎~

友情链接:MySQL笔记文档

一 事务

1、innodb

  • 具有事务,支持4个事务隔离级别,其中四种隔离有:

a、Serializable (串行化):可避免脏读、不可重复读、幻读的发生。

b、Repeatable read (可重复读):可避免脏读、不可重复读的发生。(默认的数据库隔离级别)

c、Read committed (读已提交):可避免脏读的发生。

d、Read uncommitted (读未提交):最低级别,任何情况都无法保证。

  • 回滚,崩溃修复能力和多版本并发的事务安全,包括ACID事务,事务ACID指 原子性

(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。

2、MyISAM

  • 管理非事务表
  • 提供高速存储和检索,以及全文搜索能力

3、总结:

  • 如果需要执行大量的INSERT或UPDATE操作,则应该使用InnoDB,这样可以提高多

用户并发操作的性能。

  • 如果需要执行大量的SELECT查询,那么MyISAM是更好的选择。

二 行级锁和外键约束

mysql支持三种锁定级别,行级、页级、表级。其中:

表级:直接锁定整张表,在你锁定期间,其它进程无法对该表进行写操作。如果你是写锁,则其它进程则读也不允许行级:仅对指定的记录进行加锁,这样其它进程还是可以对同一个表中的其它记录进行操作

页级:表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录

1、InnoDB

支持行级锁定,但是表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围

即 update table_name set age='24' where name like '%福%';不明确主键,在执行增删改查操作时,会锁全表

2、MyISAM

支持表级锁定,没有提供对数据库事务的支持,也不支持行级锁和外键,因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表

三 存储

1、InnoDB

基于磁盘的资源是InnoDB表空间数据文件和它的日志文件,InnoDB 表的大小只受限于操作系统文件的大小

2、MyISAM

  • 索引和数据是分开的,并且索引是有压缩的。
  • 在磁盘上存储成三个文件。一个是.frm文件用于存储表的定义,一个是.MYD文件用于

存储表的数据,另一个是.MYI文件,存储的是索引。操作系统对大文件的操作是比较

慢的,这样将表分为三个文件,那么.MYD这个文件单独来存放数据自然可以优化数据

库的查询等操作。有索引管理和字段管理。

四 索引

1、InnoDB(索引组织表)

使用的聚簇索引、索引就是数据,顺序存储,因此能缓存索引,也能缓存数据

2、MyISAM(堆组织表)

使用的是非聚簇索引、索引和文件分开,随机存储,只能缓存索引

我都把MySql啃得这么透彻了,你还不进来看一下吗?

五 服务器数据备份

1、InnoDB

必须导出SQL来备份,LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

2、MyISAM

应对错误编码导致的数据恢复速度快。MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

我都把MySql啃得这么透彻了,你还不进来看一下吗?

六 使用场景

1、InnoDB

  • 需要事务支持(具有较好的事务特性)
  • 行级锁定对高并发有很好的适应能力,但需要确保查询是通过索引完成
  • 经常更新的表,适合处理多重并发的更新请求
  • 数据一致性要求较高
  • 硬件设备内存较大,可以利用InnoDB较好的缓存能力来提高内存利用率,尽可能减少磁盘 IO
  • 主键尽可能小,避免给Secondary index带来过大的空间负担
  • 避免全表扫描,因为会导致锁表。(sql语句中含有where条件同时明确主键)
  • 尽可能缓存所有的索引和数据,提高响应速度
  • 在大批量小插入的时候,尽量自己控制事务而不要使用autocommit自动提交
  • 合理设置innodb_flush_log_at_trx_commit参数值,不要过度追求安全性
  • 避免主键更新,因为这会带来大量的数据移动

2、MyISAM

  • 不需要事务支持(不支持)
  • 并发相对较低(锁定机制问题)
  • 插入修改不频繁,查询非常频繁
  • 数据一致性要求不是非常高
  • 尽量索引(缓存机制)
  • 调整读写优先级,根据实际需求确保重要操作更优先
  • 启用延迟插入改善大批量写入性能
  • 尽量顺序操作让insert数据都写入到尾部,减少阻塞
  • 分解大的操作,降低单个操作的阻塞时间
  • 降低并发数,某些高并发场景通过应用来进行排队机制
  • 对于相对静态的数据,充分利用Query Cache可以极大的提高访问效率
  • MyISAM的Count只有在全表扫描的时候特别高效,带有其他条件的count都需要进行实际的数据访问

七 Innodb和MyIASM的引擎原理

1、Innodb

  • 引擎的索引结构,使用B+Tree作为索引结构
  • InnoDB的数据文件本身就是索引文件,数据文件本身就是按B+Tree组织的一个索引

结构,这棵树的叶节点data域保存了完整的数据记录,这种索引就是聚集索引。

  • 主键索引:

a、因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有)。

b、如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键。

c、如果不存在唯一标识数据记录的列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

我都把MySql啃得这么透彻了,你还不进来看一下吗?

  • 辅助索引

InnoDB的辅助索引data域存储相应记录主键的值而不是地址即InnoDB的所有辅助索引都引用主键作为data域

我都把MySql啃得这么透彻了,你还不进来看一下吗?

2、MyIASM

  • 引擎的索引结构:使用B+Tree作为索引结构
  • MyISAM索引实现:

MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址,即叶节点的data域存放的是数据记录的地址

  • 索引检索的算法:

首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。

  • 主键索引和辅助索引(结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复)

我都把MySql啃得这么透彻了,你还不进来看一下吗?

3、注意

  • 对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在

MyISAM表中,可以和其他字段一起建立联合索引。

  • InnoDB 中不保存表的具体行数,也就是说,执行select count() fromtable时,

InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行

数即可。注意的是,当count()语句包含where条件时,两种表的操作是一样的