mysql基础理论知识_mysql数据库基础理论知识

1、MySQL体系结构

mysql体系结构图

  MySQL体系结构主要分为两层，MySQL server层和存储引擎层，而在server层包括连接层和sql层。
应用程序通过接口来连接MySQL，最先连接处理的是连接层，连接层包括通信协议，线程处理，用户密码认证三个部分。

1. 通信协议负责检测客户端版本是否兼容MySQL服务器。
2. 线程处理是指每一个连接请求都会分配一个对应的线程，相当于一条sql对应一个线程，一个线程对应一个逻辑CPU，并会在多个逻辑CPU之间进行切换。
3. 用户名密码认证验证创建的号码，以及host主机授权是否可以连接到MySQL服务器。

  SQL层包括权限判断、查询缓存、解析器、预处理、查询优化器、缓存和执行计划。

1. 权限判断可以审核有没有访问某个库、某个表，或者表里某行的权限。
2. 查询缓存¹则通过Query Cache进行操作，如果数据在Query Cache中，则直接返回结果给客户端。
3. 查询解析器针对SQL语句进行解析，判断语法是否正确。
4. 预处理器对解析器无法解析的语句进行处理。
5. 优化器对SQL进行改写和相应的优化，并生成最优的执行计划，就可以调用程序的API接口，通过存储引擎层访问数据。

2、存储引擎

  在MySQL中，存储引擎是最重要的一点，也是MySQL区分于其他数据库最核心一点
  MySQL数据库极其分支版本主要的存储引擎有innoDB、MyISAM、Memory²、blackhole、TokuDB和MariaDB columnstore。

3、InnoDB体系结构

  InnoDB体系结构⁵实际上由内存结构、线程、磁盘文件三部分组成。

3.1 内存部分

  page 是innoDB磁盘I/O的最小单位，数据是存放在page中的，那么对应到内存中就是一个一个的buffer，每个buffer又分为三种状态。

• free buffer：此状态下的buffer从未被使用，像一张白纸。但是在实际的生产中，数据库很繁忙的情况下，free buffer 状态时不存在的。
• clean buffer：内存中的buffer里面的数据和磁盘page的数据一致
• dirty buffer：内存中新写入的数据还没有刷新到磁盘中，跟磁盘的数据不一致

  buffer在内存中是需要被组织起来的，由chain管理，也就是链。InnoDB是双向链表结构，由三种不同的buffer状态衍生出三条链表。

• free list：把那些free状态的buffer都串联起来，在数据库真正跑起来的时候，每次把page调到内存中，都会先判断free buffer 的使用情况，如果不够用了，就会从lru list和flush list链表中释放free buffer，以获得新的空闲buffer。
• lru list：lru list会把那些与磁盘数据一致，并且最近最少被使用的buffer串联起来，释放出free buffer，以便后续page调到内存中便于使用新的可用的buffer.
• flush list ：把那些dirty buffer 串联起来，为了方便刷新线程将刷新线程把脏数据刷新到磁盘中。推进checkpoint Lsn,使实例崩溃后，可以快速恢复。

3.2 各大刷新线程及作用

   InnoDB存储引擎属于多线程模型，后台有多种线程，负责处理不同的任务。
（1）、master thread
        master thread是后台线程中的主线程，优先级别最高，其内部有四个循环，分别为主循环loop、后台循环background loop、刷新循环 flush loop和暂停循环suspend loop，依据数据的运行状态会在这四个循环之间进行切换。在loop主循环中又包括两种操作，分别为每1s和每10s的操作。
        每1秒：

1. 日志缓冲刷新到磁盘，即使这个事务还没有提交
2. 刷新脏页到磁盘
3. 执行合并插入缓冲的操作
4. 产生checkpoint
5. 清除无用的table cache。
6. 如果当前没有用户活动，就切换到background loop。

        每10秒：

1. 日志缓冲刷新到磁盘中，即使这事务还没有提交
2. 执行合并插入缓冲的操作
3. 刷新脏页到磁盘
4. 删除无用的undo页
5. 产生checkpoint

（2）read thread 负责把日志缓冲中的内容刷新到redo log文件中
（3）、change thread负责将插入缓冲（change buffer）中的内容刷新到磁盘中
（4）、read/write thread是负责数据库读写请求线程，默认值都是4个
（5）、page cleaner thread是负责脏页刷新的线程
（6）、purge thread负责删除无用的undo页
（7）、checkpoint thread线程作用是在redo log 发生切换时，执行checkpoint。
（8）、error monitor thread 是负责数据库报错的线程
（9）、lock monitor thread 是负责锁的监控线程

3.3 内存刷新机制

    在Oracle和MySQL数据库中，讲究日志先行的策略，就是DML语句进入到数据库之后，先写日志，再写数据库文件。

（1） redo log
    MySQL默认下至少有两个数据库文件，在磁盘上用ib_logfile(0~N)命名。redo log 写的方式是顺序写、循环写。写满文件后执行切换操作，并执行checkpoint，触发脏页的刷新。而且MySQL重启时，如果参数文件中的redo log值大小与当前的redo log 不一致，会将现有的redo log删除，在重新按照参数重新生成新的redo log 文件，而在生成新的redo log文件之前，数据是先写在redo buffer中的。
redo buffer 刷新到磁盘的条件：

1. 通过innodb_flush_log_at_trx_commit参数来控制。主要分别为0、1、2：
   （1）、0的含义，redo thread每隔1s会将redo log buffer中的数据写入到redo log 文件，同时会进行刷盘操作，保证数据确实已经写入了磁盘。但是每次事务提交并不会触发redo log thread将日志中的缓冲写入到redo log文件中。
   （2）、1的含义：每次事务提交时，都会触发redo log thread 将日志缓冲的数据写入到文件，并“flush ”到磁盘，该设置下是最安全的模式，保证数据库主机断电、OS crash下不会丢失任何已提交的数据。
   （3）、2的含义：每次事务提交时，都会吧redo log buffer的数据写入redo log 文件，但是不会同时刷新磁盘。
   三种模式下，0是性能最好的，但是不太安全，MySQL进程一旦崩溃就会导致丢失一秒的数据，1是最安全的，但是数据库性能最慢，2是介于二者之间。
2. master thread 每秒进行刷新
3. redo log buffer使用超过一半时会触发刷新

（2）、binlog
    DML 语句会写redo log文件，也会写bing log 文件，binlog文件用于主从备份恢复和主从复制的。binlog可以通过sync_binlog 参数来控制，或者cache满了之后才会同步到磁盘。

    MySQL两阶段提交过程分为prepare和commit阶段：
（1）、准备阶段（transcation prepare）：事务SQL语句先写入redo log buffer，然后做一个事务的准备标记，再将log buffer中的数据刷新到redo log。
（2）、提交阶段（commit）：将事务产生的binlog写入文件，刷入磁盘

    再将redo log中做一个事务提交的标记，并把binlog写成攻标记也一并写入redolog 文件
而若是在准备阶段后发生发生问题，只需要执行回滚操作就可以保证数据库之间的一致性，若是在提交成功后发生宕机，只需要redo一次就可以保证数据库之间的一致性

脏页的刷新的条件：
（1）、重做日志ib_logfile 文件写满后，在切换的过程中会执行checkpoint，会触发脏页的刷新
（2）、通过innodb_max_dirty_pages_pet参数的值控制。
（3）、由innodb_adaptive_flushing参数控制，该参数影响每秒脏页刷新的数目，即主线程每秒刷新脏页

4、InnoDB的三大特性

1. 插入缓冲
   影响数据库的主要性能问题就是I/O，而插入缓冲的作用就是把普通索引上的DML操作的随机I/O变成顺序I/O.原理是先判断插入的普通索引页是否在缓冲池中，如果在就直接插入，如果不在就要先放在change buffer中，然后进行change buffer和普通索引的合并操作，即将一次将多个插入合并到一个操作中。
2. 两次写
   插入缓冲带来的是针对普通索引插入性能的提升，而double write就是保证写入的安全性，防止在MySQL实例宕机时，InnoDB发生数据页部分写的问题⁶，双写缓冲是一个位于系统表空间的存储区域，InnoDB缓冲池中刷出的脏页在被写入数据文件之前，都先写入double write buffer。然后再从双写缓冲区分两次，每次将1MB的数据写入磁盘共享表空间（double write），最后再从double write buffer写入磁盘文件，简答点就是在磁盘中对页做了一次备份
3. 自适应hash索引
   InnoDB存储引擎有一个机制，可以监控索引的搜索，如果InnoDB注意到查询可以通过建立hash索引得到优化时，则会自动完成,自适应hash 会占用InnoDB buffer pool

5、索引

    MySQL的两大索引是B+树索引和hash索引，
1、B+树索
    B+树索引是有二叉树—平衡二叉树—B-树—B+树演变而来：

• 二叉树：二叉树可能会导致数据变成单树，即左/右子树高于右/左子树，这样会导致树高很多，导致查询效率慢不稳定
• 平衡二叉树：解决了二叉树左右子树差距大的原因，但是在数据进行插入的过程中需要多次左旋和右旋，导致维护平衡二叉树的代价还是很大的，除此外还是没能解决当数据多大时，二叉树树深的问题，这关乎磁盘的I/O次数，红黑树也类似。
• B-树：通过尽可能多的在节点中存储尽可能多的信息，解决了平衡二叉树的树深导致磁盘I/O次数过多的问题，但由于MySQL中一般是范围查询，而B-树未能解决空间局部性的查找次数
• B+树：通过将数据保存在叶子结点，且在叶子结点加个前后指针来解决范围查询带来的查找次数

    而依据B+树索引又分为聚簇索引和非聚簇索引：

• 聚簇索引保存了行的所有数据信息，一般在创建表的时候，会显示为表创建聚簇索引，即使没有显示创建，InnoDB也会选择第一个不包含有null值的唯一索引来创建聚簇索引，若还是没有，则InnoDB会为该表生成一个6字节的roleId作为聚簇索引

    可以通过explain 来看数据是否使用索引，一般先看type，如果type为all就不必继续往下看，再看key列，如果为null，不必往下看，然后再看rows列，该列表示预估扫描的行列数，最后再看extra列

2、Hash 索引
哈希索引采用哈希算法，把键值转换成新的hash值，检索时不需要想B+索引那样从根节点遍历到叶子结点逐级查找，只需要进行一次hash算法即可定位。需要注意的是hash索引只适用于等值查询，不支持排序、范围、模糊查询等。

6、ICP、MRR和BKA优化

• Index Condition Pushdown（ICP）是MySQL使用索引从表中检索行数剧的一种优化方式，在MySQL 5.6之前，存储引擎会先通过索引定位基表中的行，然后返回给server层，再去为这些数据进行where 后的条件过滤。MySQL 5.6 后支持ICP，如果where条件可以使用索引，MySQL会把这部分过滤操作放到存存储引擎层，存储引擎通过索引过滤，把满足的行数据从表中读出，返回给server层。ICP能减少存储引擎层访问基表的次数和server层访问存储引擎层的次数。
• MRR：MySQL普通索引获取数据时，先是通过索引页的叶子结点找到对应的主键，在通过主键找到对应的行数据记录，这样会导致每次获取的主键值可能不是按顺序的，这样会导致随机I/O的行为。而MRR是把普通索引查询到的主键值的集合存储到read_rnd_buffer中，然后在改buffer中对主键值进行排序，最后再利用已经排序好的主键值的集合去访问表中的数据，这样从原来的随机I/O的行为变成了顺序I/O，降低了查询过程中的I/O开销。
• BKA（Batched Key Access）：这是提高表join性能的算法，其作用是读取被join 表的记录时使用顺序I/O。对于多表的join的语句，当MySQL使用索引访问第二个join表时，使用一个join buffer来收集第一个操作对象生成的列值。BKA构建key后，批量传给存储引擎层查找，这样连接条件也就成了顺序比对

7、事务

    事务其实就是一组DML 语句的集合，MySQL InnoDB支持事务。MyISAM 不支持事务，而且MySQL的事务默认都是自提交模式，想要开启事务，必须begin命令开始，以commit或者rollback命令结束
1、事务的特性

• 原子性(Atomicity)
  事务的原子性是指事务中包含的所有操作要么都做要么都不做，保证数据的一致性

• 一致性（Consistency）
  一致性是指数据库中的数据在事务操作前和事务处理后，必须满足业务的规则约束。如在转账的过程中，甲乙账户的总金额的转账前和转账后必须一致.简单的理解就是要符合常识，不存在我成功插入数据但数据库不存在的情况。

• 隔离性（Isolation）
  隔离性是指数据库允许多个并发事务同时对数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致性。

4.持久性（Durability）
事务处理结束后，对数据的修改是永久的，即便发生故障也不会丢失。

MySQL的事务默认是自提交的模式，如果关闭自提交，在对个事务提交的期间，若其中一个事物发生了问题，那其他事务会等待行锁，这样会影像数据库的TPS。

2、 事务的隔离级别

     MySQL InnoDB存储引擎实现了SQL标准的4种隔离级别，用来限定事务内哪些是可见的哪些是不可见的，低级别的隔离级别可支持更高的并发处理，并拥有更低的系统开销，MySQL的默认隔离级别是REPEATABLE-READ

• 读未提交（RU：read uncommitted）：在事务内可以读取到其他事物未提交的事务的数据变化，这也叫脏读现象。
• 读已提交（RC：read committed）：在事务内可以读取到其他事务已经提交的数据变换，解决了脏读，但是会产生不可重复读和幻读的情况
• 可重复读（RR：repetable read）:在事务中的生命周期，其读取其他事务的数据变幻是一致的。解决了不可重复读的现象，理论上来说也解决了脏读的现象
• 串行（serializable）：每个读的数据行都加表级共享锁，每次写数据时都加表级排它锁，这样解决了幻读和不可重复读，但是导致数据的并发能力下降

8、锁

    MySQL的锁机制比较简单，最显著的特点就是不同的存储引擎支持不同的锁机制，InnoDB支持行锁，有时也会升级为表锁，MyIsAM只支持表锁

1、InnoDB的锁类型：

• 读锁：简称S锁，一个事务获取了一个数据行的读锁，其他事务也可以给该行加读锁，但不获得写锁
• 写锁：简称X锁，一个事务给一个数据行加了写锁，其他事务不能给该行加任何锁，写锁的优先级更高
• MDL 锁：MySQL5.5 引进了meta data lock，用不保证表中元数据的信息。如：会话A，表开启了查询事务后，会自动会的一个MDL锁，其他会话，不能再对表进行任何的DDL操作
• 意向锁共享锁（IS）：是表级锁，指在一个数据行加共享锁前必须要获得该表的IS锁，防止加S锁时，有其他表级的X锁，导致锁冲突
• 意向锁排他锁（IX）：是表级锁，只在对一个数据行加排他锁时，要先获得表的IX锁，同样防止加行级X锁时，有其他事务加表级X锁或者表级S锁，导致锁冲突

2、InnoDB的行级锁
    行级锁的种类有三种：

1. 单个记录行的锁（record lock）：顾名思义，直接对某一行加的锁，需要注意的是Record Lock加的是索引上的
2. 间隙锁（GAP Lock）：在RR的隔离级别，引入了GAP Lock，它置锁定了行记录的范围，但不包括行记录本身，即改范围内不能新增，但是行记录还是可以修改
3. 记录锁和间隙锁额组合（next-key lock）:是Record Lock和GAP Lock的组合，当InnoDB扫描索引记录时，会先对选中的索引记录加记录锁，再对索引记录两边的间隙加加息锁。即范围内不能新增，也不能修改

[1] Query Cache 建议关闭，即将query_cache_size 置为0,query_cache_type 置为off
[2] 由于Memory引擎不持久化，所以数据库重启就会失掉，而redis 有RDB和AOf两种持久化，所以缓存一般选用Redis不选用MySQL的Memory引擎
[3] OLTP 是指关系型数据库系统
[4] OLAP 是指数据仓库系统，MySQL也可以用作数据仓库，但和hive的选用一般看数据量级别
[5] InnoDB逻辑存储单元为表空间、段、区、页
层级关系为tableSpace -->segment–>extent（64个page,1M）–>page–>行.
[6] redo log 记录的是页的物理操作，如果页损坏了，则无法进行数据库的任何恢复操作。即如果redo log 完成，在写入数据时，写入一发生错误，这是物理页是不完整的，无法通过redo log来恢复

参考：张甦–《MySQL王者晋级之路》