【笔记】【数据库】MySQL知识点整合

 架构

　　Mysql分为服务层和引擎层

　　服务层（Service）：连接器、查询缓存、分析器（词法分析、语法分析）、查询优化器、执行器；所有的内置函数，存储过程，视图等都在这里实现。

　　引擎层：InnoDB，MyISAM，Memory；负责数据的读取和存储；其架构模式是插件式的。

　　连接器：

  1. 这个阶段会等待TCP监听链接，读取用户名、密码，然后读取用户的权限，后期的操作均在这个权限上约束；
  2. 如果后期没有操作，连接会处于空闲状态，空闲过长会被断开，由参数wait_timeout决定；默认8小时；
  3. 连接过程比较复杂，所以建议采用连接池；
  4. 连接会占用资源，包括后期的查询缓存等，所以需要定期清理；

　　缓存器：（8.0版本后被删除）

  1. 拿到语句后会先查询缓存是否有该语句，有即返回；
  2. 基本是以key-value的形式存储的；
  3. 少用缓存器，弊大于利，除非你的数据很少更新，因为更新了会使得缓存失效；

　　分析器：

　　词法分析和语法分析；注意这里会包含包括表字是否存在，列名是否存在等判断；

　　优化器：

　　对于语句

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

　　先查t1的数据，和先查t2的数据，逻辑结果是一样的，但是效率可能差别很大，例如t1有索引而t2没有的情况下，优化器就是要做这个选择；

　　执行器：

　　先判断一下你对这个表 T 有没有执行查询的权限，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。

mysql> select * from T where ID=10;
  1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
  2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
  3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给

　　整个服务层架构如下图所示：

Redo Log && Bin Log

　　例子：孔乙己，酒柜老板记账两种方式，一是每一次都翻开账本，二是每次先记在黑板上，打烊的时候记录到账本；在忙的时候第二种方式销量最高，所以Mysql也是有类似机制，叫WSL，write ahead log

　　Redo Log： 例子中对应的黑板就是redo log；redo log 属于引擎层日志，黑板(redo log)是有固定大小的，如下图所示，可以配置一组为4个文件，每个文件的大小是1GB，那么总可以记录4GB操作，写到末位又从头写，如果写完了，那么就不得不停下来先持久化了；redo log其实是用两个指针维护的，一个记录当前写位置，一个记录当前擦除位置；

  • redo log主要作用是提供写速度的，因为如果每次都写磁盘，其实就是随机写，所以最好是顺序写磁盘，然后做缓存；
  • redo log还可以保证数据库拥有crash-safe能力的（主要通过双写保证），只要追加日志写完，系统崩溃也不会导致数据丢失；

　　在基于文件编程的模型，一定要设计成顺序写，顺序写有一个非常特别的点：只追究，不更新。Redo日志引入的目的就是利用顺序写，使得可以让表文件只写缓存磁盘文件的内存Pool，然后定时刷盘。由于不同表对应的数据文件不一致，如果每一次更新内存数据都刷盘，那么就会产生大量的随机写磁盘。

　　Bin Log：而另外Service层也有日志，叫binlog （归档日志）;binlog的作用主要是做数据恢复，以及数据库主从节点数据备份的。binlog具有三种模式

  • statementLevel，记录每一条修改数据的SQL语句（批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件）
    • 使用磁盘空间小，速度快
    • 如果有随机函数则无法保证一致性
  • rowLevel，记录SQL执行完的数据行变化，批量修改是批量的日志
    • 使用磁盘空间多
    • 一致性得到保障
  • mix模式，前两者的结合使用，它会自动根据执行语句是否安全（主要是备份、恢复的一致性安全）进行切换

　　区别：这两个区别是：

  1. redo log是innoDB持有的，binlog是MySql Server层实现的，所有引擎都可以使用。
  2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑；
  3. redo log是循环写的，空间固定会用完，binlog是追加写入，并不会覆盖以前日志；
  4. redo log是用来提供写速度和保证crash-safe能力，而bin log作用恢复备份数据的作用多；

　　两阶段提交：我们做的误删备份恢复一般用Binlog，另外因为redo log和binlog是在不同的地方写，所以可能存在数据不一致的情况，而解决这个不一致的方法是 “两阶段提交”；先写redo log，记为prepare状态，然后写binlog，最后再提交事物，让redo log成为commit状态；

　　这个两阶段提交是否可靠，在于程序在哪里crash，如果在写完binlog之后crash，那么下次程序启动必然先检查最早的事务，发现一个binlog对应的redolog还在prepare，就会把redolog状态改为commit，所以有了规则和状态的结合。下面看一下，这个的流程图；

　　两者关联：redo log 和 binlog 是怎么关联起来的?

　　回答：它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

  • 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
  • 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

　　Redo log buffer：

　　表的数据在磁盘中，也会在内存页page cache中，所以会有干净页和脏页，可以做异步刷盘。而Redo Log要写入磁盘也是需要一个 buffer cache来提高能力的，但这个必须要同步刷盘，只有在commit的时候才会把redo log buffer写到磁盘中。

  • 如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页（包括索引）写盘。这个过程，甚至与 redo log 毫无关系。
  • 在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态

　　Undo log：

  • 用作事务回滚
  • 用作MVVC实现多版本控制

　　Relay Log：中继日志，主从备份用作提高性能的

事务机制：

　　事务特征：A 原子性，C 一致性，I 隔离性，D 持久性

　　隔离性：在一个数据库中有多个事务的时候，就有可能会出现脏读、不可重复读、幻读问题，为了解决这些问题，就有了隔离级别的概念；

　　隔离级别：

　　在谈隔离级别之前，你隔离的越严实，效率就会越低。很多时候，我们需要在两者之中寻找一个平衡点。

  • Read Uncommited 读未提交
    • 一个事务还没提交时，它做的变更就能被别的事物看到；
  • Read Commited 读提交；
    • 一个事务提交之后，才能被其他事务看到
    • 每一次读某个数据，都会为它生产数据库快照视图
  • Repeated Read 可重复读；
    • 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据时一致的
    • 当然在可重复读的隔离级别下，未提交变更对其他事务也是不可见的
    • 第一次读某个数据，才会为它生产数据库快照视图
  • Serializable 串行化；

　　我们来看个例子，图中假设数据表只有一列，一个值得为1，在不同隔离级别下，查询到的V1、V2、V3的值分别时多少

　　如果是Read UnCommited，那么V1 = 2，V2 =2， V3 = 2；

　　如果是Read Commited，那么V1 = 1，V2 =2， V3 = 2；

　　如果是Repeated Read，那么V1 = 1，V2 =1， V3 = 2；

　　在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个试图是在事务启动时创建的，整个事务存在期间都会用这个视图。在读提交的隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的；“读未提交”则不存在视图的概念，直接返回最新值；“串行化”就直接加锁避免并发访问。

多版本并发控制（MVVC）：

　　那么视图是怎么实现的呢，实际上，每条记录在更新的时候都会同时记录一条回滚操作；记录上的最新值，通过回滚操作，都可以得到前一个甚至几个变化前状态的值，有了这些回滚操作，我们就可以在每个时刻都记录当前的read-view，于是一个数据便可以对不同时间点有多个不同版本，每个版本可以通过执行回滚操作回到对应的read-view的值。所以要实现“Repeated Read”的每个事务的视图，只需记录事务读取记录时刻的所有read-view即可

　　假设一个值从1被按顺序改成了2、3、4在回滚日志里面就会有类似下面的记录

　　系统的当前值是4，但在查询这条记录的时候，不同时刻启动的事务会有不同的read-view。如图中所看到的，视图A，B，C里面这一个记录的值分别是1、2、4；一条记录在一个系统有多个版本，就是数据库的多版本并发控制（MVVC）；

　　当什么时候会删除那些不必要的回滚日志呢，那就是当没有任何事务再需要这些回滚日志的时候。也就是当前readView版本，没有比这个版本更早的readView在被事务使用；（所以这里我们不建议使用长事务，这样会导致MySql资源一直不释放）;

　　它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。

　　数据记录隐藏字段：

  • DB_TRX_ID 6 byte，最近修改(修改/插入)事务 ID：记录创建这条记录/最后一次修改该记录的事务 ID
  • DB_ROLL_PTR 7 byte，回滚指针，指向这条记录的上一个版本（存储于 rollback segment 里）
  • DB_ROW_ID 6 byte，隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以DB_ROW_ID产生一个聚簇索引

　　还有一个删除 flag 隐藏字段, 记录被更新或删除并不代表真的删除，而是删除 flag 变了

　　Undo日志：

　　undo日志包括insert undo log和undpate undo log，对 MVCC 有帮助的实质是 update undo log ，undo log 实际上就是存在 rollback segment 中旧记录链。

　　ReadView视图：

　　Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的 DB_TRX_ID（即当前事务 ID ）取出来，与系统当前其他活跃事务的 ID 去对比（由 Read View 维护），如果 DB_TRX_ID 跟 Read View 的属性做了某些比较，不符合可见性，那就通过 DB_ROLL_PTR 回滚指针去取出 Undo Log 中的 DB_TRX_ID 再比较，即遍历链表的 DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的 DB_TRX_ID , 那么这个 DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本

属性 m_ids min_trx_id max_trx_id creator_trx_id 备注
视图1 【3、5】 3 6 2 注意4已经结束

 

  • m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。例如【3、5】。
  • min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。对应的就是：【3】。
  • max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。注意max_trx_id并不是m_ids中的最大值，而是下一个值【6】。
  • creator_trx_id：表示生成该ReadView的事务的事务id【2】。

　　获取当前值的过程很简单：

  • DB_TRX_ID属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
  • DB_TRX_ID属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
  • DB_TRX_ID属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
  • DB_TRX_ID属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下DB_TRX_ID属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还没结算，无法访问。如果不在则可以访问。

　　当前读和快照读： 

　　当前读是读真正的记录，快照读是读快照的记录（例如MVVC中的视图），一般如select for update会强制用当前读，而且会给记录加gap锁；

  • 快照读是记录读当前时间的活跃事务，这些事务的修改对于快照没有影响；
  • 当前读会对数据进行加锁，此刻其他事务无法更新数据；
  • RR隔离级别的快照读是在第一次读某条记录的时候，而RC隔离级别下的快照读是每次读都生成一个新的快照读；

　　用途：可以解决数据库读写并发问题，但无法解决更新丢失问题，后者需要用锁解决。

幻读和不可重复读：

　　幻读的定义：

　　The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

　　一个例子：

　　事务A需要找到没有小明的班级的所有人员名单。

　　　　1.查找班里所有记录是否有小明

　　　　2.不存在小明则查询班级里所有人的名单。

　　事务B 把小明放进这个班级里去

　　当两个事务执行时，事务A查询了班级里没有小明，这时候B事务把小明插入到班级中，A事务执行查询全班的操作 事务A查询到的结果是：返回了全班人员名单，包含有小明。违反了事务A的要求。

　　RR隔离级别下的幻读：

　　那么RR隔离级别下是否存在幻读？

  • 因为mysql的RR通过MVVC视图实现是避免了幻读的，但如果语句触发了当前读，那么会感觉依旧出现了幻读现象；
  • 要解决这个问题，那么就要通过MVCC+间隙锁来彻底解决幻读问题

RR和RC的区别：

　　生成快照读时机不同：

  • RR隔离级别的快照读是在第一次读某条记录的时候，而RC隔离级别下的快照读是每次读都生成一个新的快照读。

　　结论：

  • MySQL的RR隔离级别下，可以解决幻读问题，但如果事务中触发当前读，也有可能幻读
  • 不可重复读和幻读就说的都是读-读，只不过不可重复读是因为别的事务update而影响了两次select结果，幻读是因为别的事务insert而影响了两次select结果；
  • RC隔离级别是无法解决不可重复读问题的。

索引：

　　索引就是目录，方便你快速找到记录，实现索引的方式有很多种，常见的有有序数组，哈希表，二叉树；下面我列出各自的优缺点

  • 有序数组（二分法查找）适合范围查询，等值查询性能也不错，但比较麻烦的是，如果要插入的时候，就需要移动数据，代价比较大，所以适合静态存储，不会怎么变的数据；
  • 哈希表支持常量级别的等值查找，但不支持有序查找；
  • 搜索二叉树，高度比较高，不太适合磁盘存储，而且顺序查找也不太适合；
  • 跳表，适合二分查找，也适合顺序查找，但需要额外的索引空间，并且索引空间的维护不态适合存储在磁盘，所以比较适合redis等内存数据库；
  • N叉树，这个就比较适合了，适配顺序查找，那么B+树就是一个很好的选择；

　　不管是哈希还是有序数组，或者N叉树，它们都是不断迭代、不断优化的产物或者解决方案。数据库发展到今天，跳表、LSM树等数据结构也被用于引擎设计中；InnoDB就是B+树；

　　B+树特点，可以多很好支持索引的N叉存储，而且让叶子结点链接起来，使得可以支持范围查询；

mysql> create table T(
　　id int primary key, 
　　k int not null, 
　　name varchar(16),
　　index (k)
)
engine=InnoDB;

　　根据以上表格，索引和存储如下所示：（什么时候你不记得B+树的结构，那么请记住跳表就好了）

　　可以看出，这是两种不同的索引，带记录R的的称之为主键索引，也称聚簇cu索引，其他的均是非聚簇索引； 

　　聚簇索引：按照数据记录的主键id顺序存储记录，并以id为索引；

　　二级索引：主键索引外的索引都是二级索引，索引的data是主键，一次查询至少走两次索引，第二次是走主键索引，我们称之为回表操作；

　　联合索引：多个列组合的索引，一个索引只建一个树所以联合索引也是一棵树，索引节点存储列组合字符串，使用时根据最左匹配原则；所以走不走联合索引基本看第一个字段存不存在where中；

　　覆盖索引：通常有些查询需要的字段在联合索引中就有了，不需要回表，这些统称为覆盖索引，是特殊的联合索引；

　　索引维护

　　在上面B+树中，如果插入的是700，那很好办，再新加个节点就好了，但如果是400，那就麻烦了，需要移动后面的数据，空出位置，但数据页以及满了，根据B+树的算法，需要申请一个新的数据页。然后挪动部分数据过去这个过程称之为分页。这种情况下，性能自然会受影响，而且会降低数据页的利用率（因为都只存了一半，但也有可以合并两个稀疏数据页的过程）；

　　自增主键

　　自增主键的特点，正好符合了B+树每次插入都是追加记录的场景，不需要挪动其他记录，也不会触发叶子的分裂，而业务逻辑做主键往往无法做到顺序插入，成本比较高，所以我们建议用自增主键，那么什么情况下不需要自增主键呢？就是如果你的数据是只有一个id索引，而不需要其他索引的情况下，用业务主键会好一点，因为不需要回表查询了

　　优化点：

  • 尽量使用自增主键，因为这样可以减少插入的页分裂，因为基本是插入树的最后一页，自增主键基本不需要过多页移动操作；而业务逻辑字段做主键写数据成本较高。
  • 自增主键长度较小，使得页可以装更多的索引，而业务主键数据长度较大，索引也大，占用空间；
  • 可以建立一个联合索引，该索引可以减少一个二级索引（原理是最左前缀），具体见mysql 45讲，索引篇下；

锁：

 

　　锁的分类：

  • 属性：共享锁（S），排他锁（X）两种
  • 粒度：表锁、行锁（记录锁、间隙锁）
  • 状态：意向排他锁、意向共享锁

 

　　数据库里面的锁是基于索引实现的，在Innodb中我们的锁都是作用在索引上面的，当我们的SQL命中索引时，那么锁住的就是命中条件内的索引节点(行锁)，如果没有命中索引的话，那我们锁的就是整个索引树（表锁），如下图一下锁住的是整棵树还是某几个节点，完全取决于你的条件是否有命中到对应的索引节点。

　　间隙锁：

　　间隙锁是用作锁住一段间隙的，例如有：select * from user_info where id>1 and id<4 for update，这里就会对（1，4]这段区间的间隙进行加锁；

　　意向锁：

　　意向锁是用作加锁的效率优化上面来的；

　　意向锁的解释：当一个事务试图对整个表进行加锁（共享锁或排它锁）之前，首先需要获得对应类型的意向锁（意向共享锁或意向共享锁）意向共享锁当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。意向排他锁当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁。

实战：

　　字符串索引：一定要区分度高，如果字符串很长，那么索引就会占用大量空间，同一个Page页可能存的索引数量少，导致性能差；

  • 可以用前缀索引，截取前面的固定字符做索引
    • 可以节省空间，但需要多次回表，而且无法使用覆盖索引特性，如下，为针对email字段做前缀索引
mysql> alter table SUser add index index1(email);

mysql> alter table SUser add index index2(email(6));
  • 可以截取区分度高的索引
    • 例如反转字符串
    • 例如对字符串做hash值处理，该方式需要多存一列

Order BY：

　　MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

　　执行如下语句：

select city,name,age from t where city='杭州' order by name limit 1000  ;

　　已知name有索引，通常情况下，这个语句执行流程如下所示 ：

  1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
  2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id；
  3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
  4. 从索引 city 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到 city 的值不满足查询条件为止；
  6. 对 sort_buffer 中的数据按照字

　　现在的问题是，如果要排序的数据项很多，那么sort_buffer就不能做快排，而是要利用磁盘做归并排序。

　　Rowid 排序：上面的排序是先取记录内容放到sort_buffer中，然后排序，但记录过大的话，这种方式效率就很低，因为相同的内存能排的数据量就少。所以产生了另一种排序rowId排序

  • 去表取数据只取id字段和排序字段；
  • 拍完序后利用id回表取所有查询数据

　　明显以上性能回出现在回表上，所以就要衡量到底哪一种方式好。

　　天然有序：如果order by字段在索引（组合索引）中，那么就是天然有序不需要排序，效率就很快，如果再加上覆盖索引，那么就回表页不需要了。所以创建索引很重要。这证明了mysql其实是应用驱动型的配置库。

小知识：

　　为了快速查找记录，Innodb在每个Page的行记录后定义了一个称之为目录槽（directory slot）的结构，用于快速定位页面中行记录。

　　directory slot中记录了行记录在页面内偏移。需要注意的是，目录槽和行记录并非一一对应的关系，每个dir slot最多可管理8个物理行记录。定位时首先是二分查找dir slot，再通过其中记录的偏移找到行记录，进而再进行顺序查找定位特定行。

参考资料：

https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_phantom

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_non_repeatable_read

 

posted @ 2020-12-09 00:26  饭小胖  阅读(138)  评论(0)  编辑  收藏  举报