7.20笔记（MySQL和InnoDB）

InnoDB

  1. MySQL 的架构：第一层用来连接和线程的处理；第二层包含了MySQL的核心业务，主要用来对SQL的解析，分析，优化和缓冲的功能，以及存储过程触发器和视图都在这上面完成；第三层才是MySQL真正负责数据存储和提取的存储引擎（MyISAM和InnoDB）
  2. 存储器会以二进制的形式去存储数据，其中表空间时最高的存储逻辑单位，默认表中的页大小默认为16K
  3. 表的存储：InnoDB 中会把表的定义和数据索引分开存储，其中表的定义存在.frm中，数据索引存在.idb中

MyISAM和InnoDB的区别

  1. InnoDB支持事务，MyISAM不支持事务。InnoDB默认把sql语句封装成事务自动提交（这样效率很低，应该把多条sql语句放在begin和commit之间，组成一个事务）

  2. InnoDB支持外键，MyISAM不支持外键

    外键：是用于加强两个数据表数据之间的连接的一列或者多列，通常用主键当作两张表的连接

  3. InnoDB和MyISAM都使用到了B+树作为索引结构，InnoDB只有叶子节点才是数据文件，中间节点起到了辅助索引的作用。而MyISAM的所有节点（包括中间节点和叶子节点）都是存储数据文件的地址指针

    • InnoDB的索引和数据文件绑定在一起，所以是聚集索引
    • MyISAM的索引和数据文件在两个存储空间，所以是非聚集索引、

  4. InnoDB不保存表的具体行数，执行select count(*) from table是要进行全局扫描的。而MyISAM使用了一个变量记录的表的行数

  5. InnoDB不支持全局索引，MyISAM支持全局索引。所以在设计到全局的查询时MyISAM效率更高（但是在MySQL5.7以后InnoDB也支持全局索引）

  6. InnoDB支持表锁和行锁，MyISAM只支持表锁

  7. InnoDB必须有唯一索引（主键），如果没有定义的话，InnoDB会自己生产一个Row_id充当默认主键。MyISAM可以没有主键

InnoDB使用自增ID作为主键的原因：

自增ID可以保证每次插入数据时，B+树的索引是向右边扩展的，可以避免B+树的频繁合并和分裂。如果使用字符串主键和随机主键，就会导致数据随机插入从而降低效率

怎么选择这两种引擎？？

  • 是否支持事务？如果需要，就选择InnoDB。否则就选择MyISAM
  • 如果是表中的大多数数据是只读的，那么MyISAM好一些。如果读写都有就选择InnoDB
  • 数据库崩溃后MyISAM比InnoDB更难恢复
  • 在MySQL5.5后InnoDB成为默认引擎，InnoDB的适用范围更广

MySQL的InnoDB的四大特性

  • 插入缓冲
  • 两次写
  • 自适应哈希索引
  • 预先读

  1. 插入缓冲：目的就是提高插入性能。

    只对非聚集索引和非唯一索引的插入和更新有效。当进行插入操作时不是直接写到索引页中的，而是先判断是否在缓冲池中，如果在就直接插入；不在就先放到Insert Buffer中；之后按照一定频率进行批量增加通常可以把多个插入合并到一个操作中，好处就是减少了IO操作带来的性能损耗

    合并的条件

    • 辅助索引页被读到了缓冲池中

    • 辅助索引页的可用空间少于1/32时就会强制合并

    • Master Thread会在每秒和每10进行合并操作

      Master Thread的工作方式：

      ​ InnoDB存储引擎的主要的工作都在Master Thread中完成。Master Thread具有最高的线程优先原则，内部有多个循环构成：主循环（loop），后台循环（background loop），暂停循环（suspend loop），刷新循环（flush loop）

      • 主循环：有两大部分操作——每秒操作和每10秒操作

        • 每秒操作：

          1. 日志缓冲刷新到磁盘（总是）：即使事务没有提交，InnoDB也会每秒将重做日志缓冲刷新到重做日志文件中，因此可以理解为什么再大的事务提交，时间也是很短的。

          2. 合并插入缓冲insert buffer（可能）：并不是每秒刷新的，如果前一秒的IO次数小于5，则认为IO压力小，可以执行合并插入缓冲的操作。

          3. 最多刷新100个InnoDB的缓冲池脏页到磁盘（可能）：判断当前缓冲池中脏页的比例(buf_get_modifyed_ratio_pct)是否超过了配置文件中innodb_max_dirty_pages_pct这个参数（默认为90）如果超过了这个阈值，InnoDB存储引擎认为需要做同步操作，将100个脏页写入磁盘中。

          4. 如果当前没有用户活动，切换到background loop（可能）

        • 　每10秒的操作：

          1. 刷新100个脏页到磁盘(可能)

          2. 合并至多5个插入缓冲(总是)

          3. 将日志缓冲刷新到磁盘(总是)

          4. 删除无用的undo页(总是)：InnoDB存储引擎会执行full purse操作，即删除无用的Undo页，对表进行update、delete这类操作，原先行被标记删除，但是因为一致性读读关系，需要保留这些行的版本号，这时候会进行回收删除。

          5. 刷新100个或者10个脏页到磁盘(总是)

  2. 两次写：

    当数据库崩溃时，InnoDB会在doublewrite的缓存中找到数据的备份进行恢复。数据写到doublewrite消耗的IO小于写入到数据文件的消耗。在恢复过程中InnoDB可以从共享表空间的doublewrite中找到该页的副本，把其复制到表空间文件，在重做日志

  3. 自适应哈希索引

    一级索引：就是主键索引，聚簇索引；二级索引：非主键索引、普通索引、非聚簇索引

    InnoDB会监控表上二级索引的查找，如果发现了频繁访问的二级索引，那么这个二级索引就会成为热数据，就会自动建立hash表以提高访问速度

    • 特点
      　　1、无序，没有树高
      　　2、降低对二级索引树的频繁访问资源，索引树高<=4，访问索引：访问树、根节点、叶子节点
      　　3、自适应

    • 缺陷
      　　1、hash自适应索引会占用innodb buffer pool；
      　　2、自适应hash索引只适合搜索等值的查询，如select * from table where index_col='xxx'，而对于其他查找类型，如范围查找，是不能使用的；
      　　3、极端情况下，自适应hash索引才有比较大的意义，可以降低逻辑读。

  4. 预读

    InnoDB使用两种预读算法来提高IO性能：线性预读，随机预读

    • 线性预读
    • 随机预读

InnoDB的索引

在InnoDB的底层中是使用B+树来构建索引。

使用B+树构建索引的原因

首先hash表虽然可以提供时间复杂度为O(1)的查询效率，但是不适用范围查询，如果要进行范围查询会导致全表扫描

其次B树和B+树的时间效率都是O(logn)，但是不同点在于B树的中间节点是会存储数据的。计算机在读写文件时会先以页为单位加载至内存，当在数据库查询时CPU发现当前数据在磁盘而不在内存时，会先进行IO操作将数据加载到内存中及进行访问。由于B树的特性所有节点都有数据，所以在数据查找时会从根节点向下遍历查找满足条件的所有数据行，这样就带来了大量的IO操作。

而B+树的数据都在叶子节点上，这些叶子节点可以通过指针进行顺序连接，那么就可以在多个叶子节点之间进行跳转，这就比B树灵活的多也节约了大量的IO操作；我们从B+树的最左节点像遍历链表一样去遍历全部数据，当然也可以引入双向链表来对B+树进行优化。

三大范式

关系型数据库中共有六种范式：

第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。主要使用第一，第二，第三范式

  • 第一范式：数据库的表中的每一列都是不可分的原子数据项
  • 第二范式：非码属性必须完全依赖于主码（就是非码属性必须和主码相关）
  • 第三范式：在第二范式的基础上，非主属性之间不能存在依赖（在第二范式的基础上进一步消除依赖）

三大范式是设计数据库的基本理念，可以建立一个冗余较小，关系合理的数据库，但是要根据性能要求去设计合理的数据库不能一味的追求范式去建立数据库

posted @ 2021-07-20 17:31  今天不摸鱼  阅读(132)  评论(0)    收藏  举报