MySQL 基本原理

    MySQL

1、 B+树原理

a)      B+ 树：平衡查找树 叶子节点顺序访问指针，具有平衡性 通过顺序访问指针提高区间查找性能

b)      索引节点的key 从左到右非递减排列，某指向节点的区域的key 处于 该指针的左右key之间

c)      查找：在索引节点上递归二分查找，找到key 所在的数据节点，接着再数据节点二分查找

d)      插入：插入破坏树的平衡性，需要对树进行分裂 合并 旋转 维护平衡性

与红黑树的比较：

                 i.          树高低：平衡树的树高 logdN d为节点出度 红黑树为2 ；B+ 树出度可以很大，树高更低

                ii.          磁盘访问原理：操作系统将内存和磁盘分成固定大小的块，每一块称为一页。内存和磁盘以页为单位交换数据，数据库也将索引的一个节点的大小设置为页的大小，一次IO就能完全载入一个节点。 若数据在不同块上，需要磁盘移动制动手臂寻道，效率低下。 b+树有更低的树高，查询到所需key需要加载的块更少，寻道次数更少，更适合磁盘数据的读取，效率更高。

               iii.          磁盘预读特性：为减少IO，磁盘不是严格按需读取，每次都会预读。预读时顺序读取，不需要寻道，效率高，因此利用预读特性，相邻节点可以被预先加载，提高了效率。

与Hash比较: 哈希虽然提供了复杂度为O（1）的单数据行操作性能，但不支持范围查询和排序，会导致全表扫描

与B树比较：B树能在非叶子节点存储数据，这可能导致在查询连续数据时带来更多的随机IO，而B+树所有叶子节点通过指针相互连接，减少顺序遍历时产生额外的随机IO

f)       聚簇索引和非聚簇索引

                 i.          聚簇索引：索引结构和数据一起存放

                ii.          非聚簇索引：索引结构和数据分开存放

g)      索引类型

                 i.          InnoDB B+ 树索引

    1. 　　主索引（聚簇索引）：叶子节点的数据域记录完整的数据记录
    2. 　　辅助索引：叶子节点的data域 记录主键的值，查询辅助索引外的数据需回表
    3. 　　自适应哈希索引： 当某索引值被频繁使用时 会在B+树索引之上 建立哈希索引，使得可以以O1时间快速哈希查找

                ii.          哈希索引

    1. 　　无法排序和分组
    2. 　　便于精确查找，无法用于部分查找和范围查找

               iii.          MyISAM全文索引

    1. 　　通过倒排索引实现 记录关键字到文档的映射
    2. 　　查询条件使用MATCH AGAINST 而不适用WHERE
    3. 　　InnoDB 在MySQL 5.6.4也开始支持全文索引

               iv.          MyISAM空间数据索引

    1. 　　空间数据索引R Tree， 用于地理数据存储
    2. 　　必须使用GIS相关函数维护

                v.          前缀索引：适用于字符串类型数据，对文本的前几个字符创建索引

 

h)      索引的选择方式：选择性强的索引放在最前面

                 i.          索引的选择性：不重复的索引值和记录总数的比值

i)       索引优点：

                 i.          减少服务器需要扫描数据的行数

                ii.          避免分组和排序过程中创建临时表。B+树索引是有序的 可用于order by 和 group by操作

               iii.          B+树索引的有序的 相邻数据存储在一起，会将随机IO变为顺序IO

j)       Explain分析

                 i.          Select_type ：查询类型 简单查询 联合查询 子查询

                ii.          Key：使用的索引

               iii.          Rows：扫描的行数

k)      优化查询：

                 i.          减少请求的数据量：

  1. 只返回必要的列
  2. 只返回必要的行 limit
  3. 缓存热点数据

                ii.          减少服务器扫描的行数：索引

               iii.          分解大的连接查询

  1. 分解成多个单表查询，在应用层中进行关联
  2. 优点：

　　　　a)      减少锁竞争

　　　　b)      让缓存更高效：单表查询的缓存结果更容易被其他查询用到

　　　　c)      高性能和可伸缩：应用层连接 更容易对数据库进行拆分

2、 存储引擎

　　a)      事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。

　　b)      并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。

　　c)      外键：InnoDB 支持外键。

　　d)      备份：InnoDB 支持在线热备份。

　　e)      崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

　　f)       其它特性：MyISAM 支持压缩表和空间数据索引。

3、 并发带来的问题

　　a)      丢失修改：修改未提交的数据

                 i.          写时加x锁

　　b)      脏读：读未提交的数据。 Read-Committed 读提交

　　c)      不可重复读：一个事务 多次读同一数据 值不一样。Repeatable Read 可重复读（默认）

　　d)      幻读：一个事务 多次查记录 记录条数不一样。SERIALIZABLE 可串行化

4、 事务回滚恢复机制：通过回滚日志（undo log） ，所有事务进行的修改先记录到回滚日志，再执行相关操作。执行过程中遇到异常，利用回滚日志的信息，将数据回滚到修改之前的样子。回滚日志会先于数据持久化到硬盘。

5、 MVCC(multi version concurrency control， 多版本并发控制)：提高数据库并发性能，读写冲突时，维持数据的多个版本，**不加锁非阻塞并发读**

a)      数据库读数据的方式

                 i.          锁定读（当前读）：（悲观锁）

    1. 　　共享锁： select … lock in share mode
    2. 　　排他锁： select… for update/insert/delete

                ii.          快照读:

    1. 　　不加锁 非阻塞读
    2. 　　MVCC实现了 读写冲突时不加锁，但可能读历史版本

b)      实现原理（3个隐藏字段；undo日志；Read View）

                 i.          每行数据增加三个隐藏字段

    1. 　　DB_TRX_ID(6 byte): 最近修改/插入 本记录的 事务ID
    2. 　　DB_ROLL_PTR(7byte)：回滚指针，指向这条记录的上一个版本
    3. 　　DB_ROW_ID(6byte)： 隐藏的自增ID ，表无主键时使用

                ii.          undo 日志（回滚日志）

    1. 　　作用

　　　　　　　　a)      通过事务回滚时 将数据恢复到修改前的样子

　　　　　　　　b)      在MVCC中 通过undo log 找到当前事务可见的版本

　　　　　　2.　　分类

　　　　　　　　a)      insert undo log :插入记录值对本事务可见 事务提交后可直接删除

　　　　　　　　b)      update undolog:事务回滚不涉及该日志时，由purge线程清理

  1. 存储更改的行（包括 事务ID、回滚指针、自增ID）
  2. 不同事务对同一记录修改 undo log 形成版本链，链首为最新记录

               iii.          ReadView 可见性判断，保存了 当前对本事务不可见的其他活跃事务

    1. 　　m_low_limit_id: 目前出现过最大事务ID+1, 大于此ID的数据版本不可见
    2. 　　m_up_limit_id: 活跃事务列表m_ids中最小的事务id，小于这个ID的版本可见
    3. 　　m_ids：ReadView创建时 其他未提交活跃事务的ID列表：ID在此列表的版本不可见
    4. 　　m_creator_trx_id:创建该ReadView的事务ID

               iv.          可见性判断：通过ReadView 中的DB_TRX_ID 判断

    1. 　　DB_TRX_ID<m_up_limit_id,则可见。当前记录的事务ID 小于 当前活跃事务列表的最小ID
    2. 　　DB_TRX_ID>=m_low_limit_id 则不可见（to 第六步） 当前记录的事务ID 大于等于快照创建时的最大事务ID+1
    3. 　　m_ids空 可见。当前记录的事务ID 在创建快照前已提交
    4. 　　m_up_limit_id<=DB_TRX_ID<m_low_limit_id, DB_TRX_ID in m_ids 则不可见（to 第六步）,1、创建快照后修改了当前记录，2、修改了当前记录还未提交
    5.   　 否则 则可见:

　　　　　　6.        取出当前记录DB_ROLL_PTR指向的undo log(to第一步)

c)      MySQL 读提交RC 和 可重复读 RR的实现利用MVCC的原理

                 i.          在 RC 隔离级别下的 每次select 查询前都生成一个Read View (m_ids列表)

                ii.          在 RR 隔离级别下只在事务开始后 第一次select 数据前生成一个Read View（m_ids列表）

d)      InnoDB存储引擎在 RR 级别下通过 MVCC和 Next-key Lock 来解决幻读问题：

                 i.          执行普通 select，此时会以 MVCC 快照读的方式读取数据

  1. 在快照读的情况下，RR 隔离级别只会在事务开启后的第一次查询生成 Read View ，并使用至事务提交。所以在生成 Read View 之后其它事务所做的更新、插入记录版本对当前事务并不可见，实现了可重复读和防止快照读下的 “幻读”

                ii.          执行select...for update/lock in share mode、insert、update、delete等当前读

  1. 在当前读下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！InnoDB 使用 Next-key Lock 来防止这种情况。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。只要我不让你插入，就不会发生幻读

 

ACID:

原子性：一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作

一致性：事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态，未完成的事务写入物理数据库就是不一致的状态。

隔离性： 并发执行的事务不相互干扰

持久性：一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中

 

日志：https://mp.weixin.qq.com/s/KUv-Mx-FRrfQpx09Xv5Itg

  1. 二进制日志： binlog 归档日志；redolog 重做日志；undo log 回滚日志
  2. Redo log（重做日志 innodb 独有） 保证事务的持久性 完整性 崩溃恢复能力
  3. Undo log （回滚日志） 保证事务的 原子性， 不成功则rollback
  4. Bin log （逻辑日志 记录语句原始逻辑）（保证事务的一致性 MySQL都有） ： 数据备份 主备 主主 主从

 

重做日志： redo log：

                 i.          Redo log 记录 表空间号 数据页号 偏移量 修改数据长度 具体修改数据

                ii.          刷盘：将修改真正的落到硬盘中 通过fsync 命令 将文件系统缓存 page cache 刷盘到磁盘

               iii.          后台线程 每隔1秒 就把redo log buffer的内容写到 page cacher 并fsync刷盘

               iv.          刷盘策略 innodb_flush_log_at_trx_commit

    1.  　　0 每次事务提交时不进行刷盘： MySQL 挂 或 服务器宕机都可能丢失1秒的数据
    2.  　　1 每次事务提交都刷盘（默认）： 提交的数据都进行了刷盘
    3.  　　2 每次事务提交 仅写入page cache： 服务器宕机 可能丢失1秒数据；仅仅MySQL 挂 无损失

                v.          Redo log 以日志文件组存在

    1. 　　　　Write pos 当前写的记录位置
    2. 　　Checkpoint位置

               vi.          利用redo log 形式修改内容 可以顺序读写 刷盘速度快  数据库并发能力强

 

 

bin log （不论什么引擎 都产生binlog日志）

　　a)      记录 SQL语句的原始逻辑 用于保证数据一致性

　　b)      三种格式

                 i.          Statement 记录SQL 原文 但会丢失操作的具体数据 比如说 此时刻的某元素的值 此时的时间等

                ii.          Row： 记录此时刻的具体数据 （容量大 占用空间）

               iii.          Mixed： MySQL 判断是否会引起数据不一致 会就用row 不会就用statement

　　c)      写入时间： 执行过程中写入到binlog cache 事务提交时写入binlog， 每个事务线程都由一个binlog cache 因为事务的记录不能拆开

　　d)      刷盘时机控制 sync_binlog：

                 i.          0 事务提交时 写入page cache 系统判断什么时候fsync: 速度快

                ii.          1 每次事务提交 都fsync：

               iii.          N 每次都写到 page cache 记录N 个事务fsync： 可提升性能 但宕机可能会丢失N个事务的binlog

 

 

两阶段写 解决redolog binlog 逻辑不一致问题

                 i.          两阶段写：

    1. 　　prepare阶段： redolog 正常写 但标记为prepare阶段 ；
    2. 　　commit阶段： 当事务提交时 写binlog 并标记redolog 为commit阶段

　　　　ii.      回滚事务逻辑：

                1、若redolog 为commit 则可以提交事务 恢复数据

                2、否则

    1. 　　若存在对应的binlog 则事务已完成 可利用redolog 恢复数据
    2. 　　若不存在对应的binlog 利用undo log回滚事务

a)      保证原子性

b)      发生异常时回滚，回滚日志会先于数据持久化到磁盘上

c)      MVCC的实现依赖于：隐藏字段 ReadView 和undolog（回滚日志）：

                 i.          通过DB_TEX_ID ,Read View 判断可见性

                ii.          若不可见 通过DB_ROLL_PTR 从 unlog 中找可见的版本

posted @ 2021-08-20 15:33  lancelee98  阅读(673)  评论(0)  编辑  收藏  举报