温州免费网站建站模板,个人做公司网站,镇江论坛,wordpress 批量替换文章目录概念innodb引擎的4大特性索引结构InnoDBMyISAM区别表级锁和行级锁概念
MyISAM 是 MySQL 的默认数据库引擎#xff08;5.5版之前#xff09;#xff0c;但因为不支持事务处理而被 InnoDB 替代。
然而事物都是有两面性的#xff0c;InnoDB 支持事务处理也会带来一些…
文章目录概念innodb引擎的4大特性索引结构InnoDBMyISAM区别表级锁和行级锁概念
MyISAM 是 MySQL 的默认数据库引擎5.5版之前但因为不支持事务处理而被 InnoDB 替代。
然而事物都是有两面性的InnoDB 支持事务处理也会带来一些问题 当操作完全兼容 ACID 时InnoDB 会自动合并多个连接但每次有事务产生时仍至少须写入硬盘一次因此对于某些硬盘或磁盘阵列会造成 每秒200次 的事务处理上限。 若希望达到更高的性能且保持事务的完整性就必使用磁盘缓存与电池备援。当然 InnoDB 也提供数种对性能冲击较低的模式但相对的也会降低事务的完整性。 innodb引擎的4大特性 插入缓冲(insert buffer) 为解决非聚集索引的写性能问题插入或更新而生。
对非聚集索引的插入或更新操作不是每一次都直接插入到索引页中而是先判断插入的非聚集索引页是否在缓冲池中
若在则直接插入不在则先放入到一个 Insert Buffer 对象中。
此时看似数据库这个非聚集索引已经插入到了叶子节点然而实际上只是存放在另一个位置。然后再以一定的频率进行 Insert Buffer 与辅助索引叶子节点的 merge 操作此时通常能将多个插入合并到一个操作中这就大大提高了非聚集索引插入的性能。 二次写(double write) 数据库宕机可能会引起部分写失效partial page write解决方法有两种
数据库宕机物理文件完好无损是可以通过 redo log 进行崩溃恢复ACID中的持久性。数据库宕机物理文件由于宕机发生损坏这时就无法通过 redo log 进行数据恢复了, 而只能通过 double write 解决问题。
二次写分为三步
对缓冲池脏页进行刷新时先将脏页复制到内存的 double write buffer 第一次写 将 double write buffer 分批次写入到共享表空间中因为共享表空间是连续存储所以很快。第二次写 将 double write buffer 中数据写入到各个表空间文件中而此时的写入则是离散的因为各个表空间可能在磁盘的不同位置因此写入速度较慢。 PS参数 skip_innodb_doublewrite 可以禁用 double write 功能但不推荐这样做。对于需要提供数据高可靠性的主服务器任何时候都应该确保开启 double write 功能。 自适应哈希索引(ahi) InnoDB存储引擎 会监控对表上各个索引页的查询如果它观察到建立哈希索引可以带来速度提升则会自行建立哈希索引这也就是 自适应哈希索引。 即会自动根据 访问频率和模式 来为 热点数据 建立哈希索引。
哈希索引是数据库自身自动创建并使用的人工无法对其进行干预。 预读(read ahead) 为了提高磁盘操作性能当前的数据库系统都采用 异步IO 的方式来处理磁盘操作InnoDB 也是如此。
同步阻塞 每进行一次IO操作需要等待此次操作结束才能继续接下来的操作。
但是如果一条 SQL语句 需要扫描多个索引页也就是需要进行多次 IO操作(扫描一个页就是一次IO请求) 。同步阻塞会扫描一个页并等待其完成再进行下一个页扫描也就是等待一个IO请求完成后再发送下一个IO请求效率很低。
异步非阻塞AIO 用户可以在发出一个 IO请求 后不必等待其完成而是可以立即发出下一个 IO请求 。 索引结构
我们在MySQL 索引 哈希索引、B树索引、全文索引中介绍过聚集索引和非聚集索引现在结合两个存储引擎深入研究一下。
两者都使用 B树 作为索引结构但实现方式却截然不同
主键索引 InnoDB 的数据文件本身就是索引文件MyISAM 主键索引的叶节点存的是数据地址。辅助索引 InnoDB 的辅助索引 data域 存储相应记录主键的值而不是地址 MyISAM 辅助索引的叶节点存的还是数据地址。
InnoDB
InnoDB 是聚集索引
数据文件和主键索引绑在一起表数据文件本身就是按 BTree 组织的一个索引结构必须要有主键通过主键索引效率很高。主键索引的叶节点保存了完整的数据记录。但是辅助索引需要两次查询先查询到主键然后再通过主键查询到数据。主键不应该过大因为主键太大其他索引也都会很大。辅助索引的叶子节点是主键的值。 图源博客
MyISAM
MyISAM 是 非聚集索引
索引和数据文件是分离的主键索引和辅助索引的叶子节点都是数据文件的地址指针。主键索引要求每个叶子节点的内容是唯一的而辅助索引每个叶子节点的内容可以重复。主键索引和辅助索引是独立的。因此不同于 InnoDB 需要先在辅助索引中查找到主键再通过主键在主键索引中查找到具对应数据。MyISAM 可通过辅助索引快速找到所有的数据而不需要再遍历一边主键索引所以适用于OLAP。 区别
myisaminnodb索引类型支持 B-tree、FullTex、R-tree 索引类型支持 hash、B-tree 索引类型可以通过使用插件 Sphinx 从 InnoDB 中获得全文索引会慢一点。索引Myisam 可以不用主键InnoDB 表必须有唯一索引如主键用户没有指定的话会自己找/生产一个隐藏列 Row_id 来充当默认主键存储结构每张表存放三个文件frm格式定义 MYD(MYData)数据文件 MYI(MYIndex)索引文件。Innodb 存储文件有 frm格式定义ibd 数据文件。表的大小只受限于操作系统文件的大小一般为 2GB 。存储空间MyISAM 提供 压缩 与 简洁行terse row formats存储空间较小。InnoDB 的表需要更多的内存和存储在主内存中建立专用的缓冲池用于高速缓冲数据和索引对硬盘和高速缓存的使用量较大。读写缓存MyISAM 没有缓存管理机制必须依靠操作系统来管理读取与写入的缓存。InnoDB 有读写缓存管理机制不会将被修改的数据页立即交给操作系统。通常 InnoDB 的数据访问会比 MyISAM 更有效率。可移植性、备份由于 MyISAM 的数据是以文件的形式存储所以在跨平台的数据转移中会很方便。免费的方案可以是拷贝数据文件、备份 binlog或者用 mysqldump在数据量达到几十G的时候就相对痛苦了恢复MyISAM 遇到错误必须完整扫描全表后才能重建索引或修正未写入硬盘的错误。修复时间与数据量的多少成正比。InnoDB 可借由回滚机制来恢复程序崩溃或非预期结束所造成的数据错误 修复时间基本都是固定的。事务不支持每次查询具有原子性。支持具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全型表AUTO_INCREMENTMyISAM 表可以和其他字段一起建立联合索引InnoDB 中必须包含只有该字段的索引锁只支持表级锁select、update、delete、insert 语句都会给表自动加锁。支持表锁、默认行锁行锁大幅度提高了多用户并发操作的新能。但是 InnoDB 的行锁只是在 WHERE 的主键是有效的非主键的 WHERE 都会锁全表的。外键不支持支持对一个包含外键的 InnoDB表 转为 MYISAM 会失败。快速读取行数用一个变量保存了整个表的行数执行 select count(*) from table 语句时只需要读出该变量即可速度很快注意不能加有任何 WHERE 条件。保存表的具体行数执行 select count(*) from table 时需要全表扫描。表级锁和行级锁 表级锁 优点开销小加锁快缺点粒度大发生冲突概率高高容纳并发能力低适合查询为主的业务。 行级锁 优点粒度小发生锁冲突的概率小适用于高并发的频繁表修改因此 InnoDB 高并发性能优于 MyISAM 。缺点加锁慢系统消耗较大。索引不仅缓存自身也缓存数据因此 InnoDB 相比 MyISAM 需要更大的内存。
