Mysql学习

Mysql学习

mysql的学习很重要参考的博客以及视频有以下这些

数据库引擎:数据库引擎

MySQL事务隔离级别和实现原理:MySQL事务隔离级别和实现原理

最完整MySQL数据库面试题（2020最新版）:最完整MySQL数据库面试题（2020最新版）

MySQL锁总结:MySQL锁总结

Mysql中MVCC的使用及原理详解:Mysql中MVCC的使用及原理详解

我以为我对Mysql事务很熟，直到我遇到了阿里面试官:我以为我对Mysql事务很熟，直到我遇到了阿里面试官

mvcc的b站视频:mvcc的b站视频

mysql中redo log 和undo log详解:mysql中redo log 和undo log详解

b+树:b+树

聚簇索引与非聚簇索引:聚簇索引

索引的类型分类、区别、优缺点:索引的类型分类、区别、优缺点

MySQL索引优化看这篇文章就够了！:[MySQL索引优化看这篇文章就够了！]

索引、explain分析:索引、explain分析

MySql的主从复制:MySql的主从复制

binlog的详解:binlog的详解

1 mysql的存储引擎

1.1 常用的存储引擎

innoDB
1. 简介:从Mysql5.5.5版本开始，InnoDB是默认的表存储引擎。InnoDB是事务安全的MySQL存储引擎，支持ACID事务。其设计目标主要面向在线事务处理(OLTP)的应用。其特点是行锁设计、支持外键，并支持非锁定读，即默认读操作不会产生锁。InnoDB存储数据是基于磁盘存储的,且其记录是按照页的方式进行管理。
2. 体系结构:
  
  缓冲池主要工作
  - 维护所有进程/线程需要访问的多个内部数据结构
  - 缓存磁盘上的数据，方便快速读取，同时在对磁盘文件修改之前进行缓存
  - 缓存重做日志（redo log）
  后台线程主要工作
  - 刷新缓冲池中的数据，保证缓冲池中缓存的数据最新
  - 将已修改数据文件刷新到磁盘文件
  - 保证数据库异常时InnoDB能恢复到正常运行状态
3. 后台线程
  
  MySQL是一个单进程多线程架构的数据库，也就是说MySQL数据库实例在系统中表现形式就是一个进程。InnoDB是多线程的模型，后台有多个不同的线程，用来负责不同的任务。主要有如下：
  1. Master Thread:
    
    这是最核心的一个线程,主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括赃页的刷新、合并插入缓冲、UNDO 页的回收等。Master thread在主循环中，分为两类操作，每秒钟的操作和每10秒钟的操作：
    - 每秒一次的操作
    - - 日志缓冲刷新到磁盘: 即使这个事务还没有提交（总是），这点解释了为什么再大的事务commit时都很快；
      - 合并插入缓冲（可能）: 合并插入并不是每秒都发生，InnoDB会判断当前一秒内发生的IO次数是否小于5，如果是，则系统认为当前的IO压力很小，可以执行合并插入缓冲的操作。
      - 至多刷新100个InnoDB的缓冲池的脏页到磁盘（可能) : 这个刷新100个脏页也不是每秒都在做，InnoDB引擎通过判断当前缓冲池中脏页的比例(buf_get_modified_ratio_pct)是否超过了配置文件中innodb_max_drity_pages_pct参数(默认是90，即90%)，如果超过了这个阈值，InnoDB引擎认为需要做磁盘同步操作，将100个脏页写入磁盘。
    - 每10秒一次的操作
    - - 刷新100个脏页到磁盘（可能）: InnoDB引擎先判断过去10秒内磁盘的IO操作是否小于200次，如果是，认为当前磁盘有足够的IO操作能力，即将100个脏页刷新到磁盘。
      - 合并至多5个插入缓冲（总是）: 此次的合并插入缓冲操作总会执行，不同于每秒操作时可能发生的合并操作。
      - 将日志缓冲刷新到磁盘（总是）: InnoDB引擎会再次执行日志缓冲刷新到磁盘的操作，与每秒发生的操作一样。
      - 删除无用的undo页（总是）: 当对表执行update，delete操作时，原先的行会被标记为删除，但是为了一致性读的关系，需保留这些行版本的信息，在进行10S一次的删除操作时，InnoDB引擎会判断当前事务系统中已被删除的行是否可以删除，如果可以，InnoDB会立即将其删除。InnoDB每次最多删除20个Undo页。
      - 产生一个检查点（checkpoing）；
  2. IO Thread :
    
    在 InnoDB 存储引擎中大量使用了异步 IO 来处理写 IO 请求，IO Thread 的工作主要是负责这些 IO 请求的回调处理。通过以下命令查询IO线程：
```
mysql> show variables like 'innodb_%io_threads';
```
    　
    
    从截图中可以看出innodb引擎中io读线程和写线程的数量。5.6以后的版本可以通过innodb_write_io_threads和innodb_read_io_threads来限制读写线程，而在5.6版本以前，只有一个参数innodb_file_io_threads来控制读写总线程数。
  3. Purge Thread:
    
    事务被提交之后，undo log 可能不再需要，因此需要 Purge来回收已经使用并分配的 undo页，purge操作默认是由master thread中完成的，为了减轻master thread的工作，提高cpu使用率以及提升存储引擎的性能，用户可以在参数文件中添加如下命令来启动独立的purge thread
```
innodb_purge_threads=1
```
    　　从innodb1.2版本开始，可以指定多个innodb_purge_threads来进一步加快和提高undo回收速度。同时 Purge Thread 需要离散地读取 undo 页，这样能更进一步利用磁盘的随机读取性能。可以通过以下命令查询Purge Thread的情况：
```
show variables like 'innodb_purge%'
```
    　　其中innodb_purge_threads 为开启的Purge Thread数量，innodb_purge_batch_size为设置每次purge清理的undo页数，如果值太大则会导致CPU和磁盘IO过于集中。
  4. Page Cleaner Thread:
    
    Page Cleaner Thread 是在InnoDB 1.2.x版本新引入的，其作用是将之前版本中脏页的刷新操作都放入单独的线程中来完成，这样减轻了 Master Thread 的工作及对于用户查询线程的阻塞。
4. 缓冲池及其组成
  InnoDB 存储引擎是基于磁盘存储的，即数据都是存储在磁盘上的，由于 CPU 速度和磁盘速度之间的鸿沟，InnoDB 引擎使用缓冲池技术来提高数据库的整体性能，即通过内存的速度来弥补磁盘速度慢对数据库性能造成的影响。其工作方式总是将数据库文件按页（每页16K）读取到缓冲池，然后按最近最少使用（LRU）的算法来保留在缓冲池中的缓存数据。在数据库中进行读操作时，首先将从磁盘读到的页存放在缓冲池中，下一次读取相同的页时，首先判定是否存在缓冲池中，如果有就是被命中直接读取，没有的话就从磁盘中读取。在数据库进行改操作时，首先修改缓冲池中的页（修改后，该页即为脏页），然后在以一定的频率刷新到磁盘上。这里的刷新机制不是每页在发生变更时触发。而是通过一种checkpoint机制刷新到磁盘的。所以缓冲池的大小直接影响着数据库的整体性能，可以通过配置参数innodb_buffer_pool_size来设置。从架构图中可以看出缓冲池中缓存的数据页类型有索引页、数据页、 undo 页、插入缓存、自适应哈希索引、 InnoDB 的锁信息、数据字典信息等。索引页和数据页占缓冲池的很大一部分。
  - 数据页和索引页: Page是Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位，与数据库相关的所有内容都存储在Page结构里。Page分为几种类型，数据页和索引页就是其中最为重要的两种类型。
  - 插入缓存: 在InnoDB引擎上进行插入操作时，一般需要按照主键顺序进行插入，这样才能获得较高的插入性能。当一张表中存在非聚簇的且不唯一的索引时，在插入时，数据页的存放还是按照主键进行顺序存放，但是对于非聚簇索引叶节点的插入不再是顺序的了，这时就需要离散的访问非聚簇索引页，由于随机读取的存在导致插入操作性能下降。InnoDB为此设计了Insert Buffer来进行插入优化。对于非聚簇索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非聚集索引是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer中。看似数据库这个非聚集的索引已经查到叶节点，而实际没有，这时存放在另外一个位置。然后再以一定的频率和情况进行Insert Buffer和非聚簇索引页子节点的合并操作。这时通常能够将多个插入合并到一个操作中，这样就大大提高了对于非聚簇索引的插入性能。
  - 自适应哈希索引: InnoDB会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率。InnoDB存储引擎会监控对表上各个索引页的查询，如果观察到建立哈希索引可以带来速度上的提升，则建立哈希索引，所以叫做自适应哈希索引。自适应哈希索引是通过缓冲池的B+树页构建而来，因此建立速度很快，而且不需要对整张数据表建立哈希索引。其有一个要求，即对这个页的连续访问模式必须是一样的，也就是说其查询的条件(WHERE)必须完全一样，而且必须是连续的。
  - 锁信息 : InnoDB存储引擎会在行级别上对表数据进行上锁。不过InnoDB也会在数据库内部其他很多地方使用锁，从而允许对多种不同资源提供并发访问。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。
  - 数据字典信息 : InnoDB有自己的表缓存，可以称为表定义缓存或者数据字典。当InnoDB打开一张表，就增加一个对应的对象到数据字典。数据字典是对数据库中的数据、库对象、表对象等的元信息的集合。在MySQL中，数据字典信息内容就包括表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容。MySQL INFORMATION_SCHEMA库提供了对数据局元数据、统计信息、以及有关MySQL server的访问信息（例如：数据库名或表名，字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典。
  - 重做日志缓冲：
    
    InnoDB有buffer pool（简称bp）。bp是数据库页面的缓存，对InnoDB的任何修改操作都会首先在bp的page上进行，然后这样的页面将被标记为dirty并被放到专门的flush list上，后续将由master thread或专门的刷脏线程阶段性的将这些页面写入磁盘。这样的好处是避免每次写操作都操作磁盘导致大量的随机IO，阶段性的刷脏可以将多次对页面的修改merge成一次IO操作，同时异步写入也降低了访问的时延。然而，如果在dirty page还未刷入磁盘时，server非正常关闭，这些修改操作将会丢失，如果写入操作正在进行，甚至会由于损坏数据文件导致数据库不可用。为了避免上述问题的发生，Innodb将所有对页面的修改操作写入一个专门的文件，并在数据库启动时从此文件进行恢复操作，这个文件就是redo log file。这样的技术推迟了bp页面的刷新，从而提升了数据库的吞吐，有效的降低了访问时延。带来的问题是额外的写redo log操作的开销（顺序IO，当然很快），以及数据库启动时恢复操作所需的时间。redo日志由两部分构成：redo log buffer、redo log file。innodb是支持事务的存储引擎，在事务提交时，必须先将该事务的所有日志写入到redo日志文件中，待事务的commit操作完成才算整个事务操作完成。在每次将redo log buffer写入redo log file后，都需要调用一次fsync操作，因为重做日志缓冲只是把内容先写入操作系统的缓冲系统中，并没有确保直接写入到磁盘上，所以必须进行一次fsync操作。因此，磁盘的性能在一定程度上也决定了事务提交的性能。InnoDB 存储引擎先将重做日志信息放入这个缓冲区,然后以一定频率将其刷新到重做日志文件。重做日志文件一般不需要设置得很大,因为在下列三种情况下重做日志缓冲中的内容会刷新到磁盘的重做日志文件中。额外的缓冲池
    - 　　Master Thread 每一秒将重做日志缓冲刷新到重做日志文件
    - 　　每个事物提交时会将重做日志缓冲刷新到重做日志文件
    - 　　当重做日志缓冲剩余空间小于1/2时,重做日志缓冲刷新到重做日志文件
  - 额外的缓冲池：在 InnoDB 存储引擎中，对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请。例如: 分配了缓冲池,但是每个缓冲池中的帧缓冲还有对应的缓冲控制对象，这些对象记录以一些诸如 LRU, 锁,等待等信息,而这个对象的内存需要从额外的内存池中申请。
5. 缓冲池的原理
  
  上边已经介绍了缓冲池的概念以及缓冲池中的组成，这里介绍下缓冲池工作原理。
  
  （1）怎么将磁盘上的页缓存到内存中的Buffer Pool中呢？
  
  Buffer Pool其实是一片连续的内存空间，那么怎么将磁盘上的页缓存到内存中的Buffer Pool中呢？直接把需要缓存的页向Buffer Pool里一个一个往里怼么？不不不，为了更好的管理这些被缓存的页，InnoDB为每一个缓存页都创建了一些所谓的控制信息，这些控制信息包括该页所属的表空间编号、页号、页在Buffer Pool中的地址、一些锁信息以及LSN信息，当然还有一些别的控制信息。
  
  每个缓存页对应的控制信息占用的内存大小是相同的，我们就把每个页对应的控制信息占用的一块内存称为一个控制块，控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中，其中控制块被存放到 Buffer Pool 的前边，缓存页被存放到 Buffer Pool 后边，所以整个Buffer Pool对应的内存空间看起来就是这样的：
  
  控制块和缓存页之间的那个碎片是个什么呢？每一个控制块都对应一个缓存页，那在分配足够多的控制块和缓存页后，可能剩余的那点儿空间不够一对控制块和缓存页的大小，自然就用不到喽，这个用不到的那点儿内存空间就被称为碎片了。当然，如果你把Buffer Pool的大小设置的刚刚好的话，也可能不会产生碎片。
  
  （2）InnoDB存储引擎是怎么对缓冲池进行管理的？
  
  空闲链表：
  
  当我们最初启动MySQL服务器的时候，需要完成对Buffer Pool的初始化过程，就是分配Buffer Pool的内存空间，把它划分成若干对控制块和缓存页。但是此时并没有真实的磁盘页被缓存到Buffer Pool中（因为还没有用到），之后随着程序的运行，会不断的有磁盘上的页被缓存到Buffer Pool中，那么问题来了，从磁盘上读取一个页到Buffer Pool中的时候该放到哪个缓存页的位置呢？或者说怎么区分Buffer Pool中哪些缓存页是空闲的，哪些已经被使用了呢？我们最好在某个地方记录一下哪些页是可用的，我们可以把所有空闲的页包装成一个节点组成一个链表，这个链表也可以被称作Free链表（或者说空闲链表）。因为刚刚完成初始化的Buffer Pool中所有的缓存页都是空闲的，所以每一个缓存页都会被加入到Free链表中，假设该Buffer Pool中可容纳的缓存页数量为n，那增加了Free链表的效果图就是这样的：
  
  从图中可以看出，我们为了管理好这个Free链表，特意为这个链表定义了一个控制信息，里边儿包含着链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。我们在每个Free链表的节点中都记录了某个缓存页控制块的地址，而每个缓存页控制块都记录着对应的缓存页地址，所以相当于每个Free链表节点都对应一个空闲的缓存页。有了这个Free链表事儿就好办了，每当需要从磁盘中加载一个页到Buffer Pool中时，就从Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的Free链表节点从链表中移除，表示该缓存页已经被使用了～
  
  上面介绍了缓存池中的空闲链表，free list用来维护buffer pool的空闲缓存页的数据结构。
  
  内存空间不足引起的问题？
  
  简单地回顾Buffer Pool的工作机制。Buffer Pool两个最主要的功能：
  - 一个是加速读，加速读呢？就是当需要访问一个数据页面的时候，如果这个页面已经在缓存池中，那么就不再需要访问磁盘，直接从缓冲池中就能获取这个页面的内容。
  - 一个是加速写，加速写呢？就是当需要修改一个页面的时候，先将这个页面在缓冲池中进行修改，记下相关的重做日志，这个页面的修改就算已经完成了。至于这个被修改的页面什么时候真正刷新到磁盘，这个是后台刷新线程来完成的。
  在实现上面两个功能的同时，需要考虑客观条件的限制，因为机器的内存大小是有限的，所以MySQL的InnoDB Buffer Pool的大小同样是有限的，如果需要缓存的页占用的内存大小超过了Buffer Pool大小，也就是Free链表中已经没有多余的空闲缓存页的时候岂不是很尴尬，发生了这样的事儿该咋办？当然是把某些旧的缓存页从Buffer Pool中移除，然后再把新的页放进来喽～那么问题来了，移除哪些缓存页呢？
  
  LRU list
  
  为了回答上边的问题，我们还需要回到我们设立Buffer Pool的初衷，我们就是想减少和磁盘的I/O交互，最好每次在访问某个页的时候它都已经被缓存到Buffer Pool中了。假设我们一共访问了n次页，那么被访问的页已经在缓存中的次数除以n就是所谓的缓存命中率，我们的期望就是让缓存命中率越高越好～
  
  怎么提高缓存命中率呢？InnoDB Buffer Pool采用经典的LRU算法来进行页面淘汰，以提高缓存命中率。当Buffer Pool中不再有空闲的缓存页时，就需要淘汰掉部分最近很少使用的缓存页。不过，我们怎么知道哪些缓存页最近频繁使用，哪些最近很少使用呢？我们可以再创建一个链表，由于这个链表是为了按照最近最少使用的原则去淘汰缓存页的，所以这个链表可以被称为LRU链表（Least Recently Used）。当我们需要访问某个页时，可以这样处理LRU链表：
  - 如果该页不在Buffer Pool中，在把该页从磁盘加载到Buffer Pool中的缓存页时，就把该缓存页包装成节点塞到链表的头部。
  - 如果该页在Buffer Pool中，则直接把该页对应的LRU链表节点移动到链表的头部。
  但是这样做会有一些性能上的问题，比如你的一次全表扫描或一次逻辑备份就把热数据给冲完了，就会导致缓冲池污染问题！Buffer Pool中的所有数据页都被换了一次血，其他查询语句在执行时又得执行一次从磁盘加载到Buffer Pool的操作，而这种全表扫描的语句执行的频率也不高，每次执行都要把Buffer Pool中的缓存页换一次血，这严重的影响到其他查询对 Buffer Pool 的使用，严重的降低了缓存命中率！
  
  所以InnoDB存储引擎对传统的LRU算法做了一些优化，在InnoDB中加入了midpoint。新读到的页，虽然是最新访问的页，但并不是直接插入到LRU列表的首部，而是插入LRU列表的midpoint位置。这个算法称之为midpoint insertion stategy。默认配置插入到列表长度的5/8处。midpoint由参数innodb_old_blocks_pct控制。midpoint之前的列表称之为new列表，之后的列表称之为old列表。可以简单的将new列表中的页理解为最为活跃的热点数据。同时InnoDB存储引擎还引入了innodb_old_blocks_time来表示页读取到mid位置之后需要等待多久才会被加入到LRU列表的热端。可以通过设置该参数保证热点数据不轻易被刷出。
  
  FLUSH链表
  
  前面我们讲到页面更新是在缓存池中先进行的，那它就和磁盘上的页不一致了，这样的缓存页也被称为脏页（英文名：dirty page）。所以需要考虑这些被修改的页面什么时候刷新到磁盘？以什么样的顺序刷新到磁盘？当然，最简单的做法就是每发生一次修改就立即同步到磁盘上对应的页上，但是频繁的往磁盘中写数据会严重的影响程序的性能（毕竟磁盘慢的像乌龟一样）。所以每次修改缓存页后，我们并不着急立即把修改同步到磁盘上，而是在未来的某个时间点进行同步，由后台刷新线程依次刷新到磁盘，实现修改落地到磁盘。
  
  但是如果不立即同步到磁盘的话，那之后再同步的时候我们怎么知道Buffer Pool中哪些页是脏页，哪些页从来没被修改过呢？总不能把所有的缓存页都同步到磁盘上吧，假如Buffer Pool被设置的很大，比方说300G，那一次性同步这么多数据岂不是要慢死！所以，我们不得不再创建一个存储脏页的链表，凡是在LRU链表中被修改过的页都需要加入这个链表中，因为这个链表中的页都是需要被刷新到磁盘上的，所以也叫FLUSH链表，有时候也会被简写为FLU链表。链表的构造和Free链表差不多，这就不赘述了。这里的脏页修改指的此页被加载进Buffer Pool后第一次被修改，只有第一次被修改时才需要加入FLUSH链表（代码中是根据Page头部的oldest_modification == 0来判断是否是第一次修改），如果这个页被再次修改就不会再放到FLUSH链表了，因为已经存在。需要注意的是，脏页数据实际还在LRU链表中，而FLUSH链表中的脏页记录只是通过指针指向LRU链表中的脏页。并且在FLUSH链表中的脏页是根据oldest_lsn（这个值表示这个页第一次被更改时的lsn号，对应值oldest_modification，每个页头部记录）进行排序刷新到磁盘的，值越小表示要最先被刷新，避免数据不一致。
  
  注意：脏页既存在于LRU列表中，也存在与Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响。
  
  这三个重要列表（LRU list， free list，flush list）的关系可以用下图表示：
  
  Free链表跟LRU链表的关系是相互流通的，页在这两个链表间来回置换。而FLUSH链表记录了脏页数据，是通过指针指向了LRU链表，所以图中FLUSH链表被LRU链表包裹。
6. INNODB重要特性
  
  （1）插入缓冲
  
  一般情况下，主键是行唯一的标识符，通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的，因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。因为，对于此类情况下的插入，速度还是非常快的。（如果主键类是UUID这样的类，那么插入和辅助索引一样，也是随机的。）
  
  如果索引是非聚集的且不唯一，在进行插入操作时，数据的存放对于非聚集索引叶子节点的插入不是顺序的，这时需要离散地访问非聚集索引页，由于随机读取的存在而导致了插入操作性能下降。这是因为B+树的特性决定了非聚集索引插入的离散性。
  
  Insert Buffer的设计，对于非聚集索引的插入和更新操作，不是每一次直接插入到索引页中，而是先判断插入非聚集索引页是否在缓冲池中，若存在，则直接插入，不存在，则先放入一个Insert Buffer对象中。数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能。
  
  需要满足的两个条件：
  - 索引是辅助索引；
  - 索引不是唯一的。
  辅助索引不能是唯一的，因为在插入缓冲时，数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生，从而导致Insert Buffer失去了意义。
  
  （2）两次写
  
  如果说插入缓冲是为了提高写性能的话，那么两次写是为了提高可靠性。介绍两次写之前，说一下部分写失效：
```
场景：当数据库正在从内存向磁盘写一个数据页时，数据库宕机，从而导致这个页只写了部分数据，这就是部分写失效，它会导致数据丢失。这时是无法通过重做日志恢复的，因为重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。
```
  从上面分析我们知道，在部分写失效的情况下，我们在应用重做日志之前，需要原始页的一个副本，两次写就是为了解决这个问题，下面是它的原理图：
  
  两次写需要额外添加两个部分：
  - 内存中的两次写缓冲（doublewrite buffer），大小为2MB
  - 磁盘上共享表空间中连续的128页，大小也为2MB
  其原理是这样的：
  1）当刷新缓冲池脏页时，并不直接写到数据文件中，而是先拷贝至内存中的两次写缓冲区。
  2）接着从两次写缓冲区分两次写入磁盘共享表空间中，每次写入1MB
  3）待第2步完成后，再将两次写缓冲区写入数据文件
  
  这样就可以解决上文提到的部分写失效的问题，因为在磁盘共享表空间中已有数据页副本拷贝，如果数据库在页写入数据文件的过程中宕机，在实例恢复时，可以从共享表空间中找到该页副本，将其拷贝覆盖原有的数据页，再应用重做日志即可。其中第2步是额外的性能开销，但由于磁盘共享表空间是连续的，因此开销不是很大。可以通过参数skip_innodb_doublewrite禁用两次写功能，默认是开启的，强烈建议开启该功能。
  
  （3）自适应哈希索引
  
  哈希是一种非常快的查找方法，在一般情况时间复杂度为O(1)。而B+树的查找次数，取决于B+树的高度，在生成环境中，B+树的高度一般为3-4层，不需要查询3-4次。InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以提升速度，就会建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index, AHI)。AHI是通过缓冲池的B+树页构造而来的。因此建立的速度非常快，且不要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动的为某些热点页建立哈希索引。AHI有一个要求，对这个页的连续访问模式(查询条件)必须一样的。例如联合索引(a,b)其访问模式可以有以下情况:
  - WHERE a=XXX;
  - WHERE a=xxx AND b=xxx。
  若交替进行上述两次查询，InnoDB存储引擎不会对该页构造AHI。根据官方文档显示，启用AHI后，读取和写入的速度可以提高2倍，负责索引的链接操作性能可以提高5倍。其设计思想是数据库自由化的，无需DBA对数据库进行人为调整。
  
  （4）异步IO
  
  为了提高磁盘操作性能，当前的数据库系统都采用异步IO的方式来处理磁盘操作。InnoDB也是如此。与AIO对应的是Sync IO，即每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作。但是如果用户发出的是一条索引扫描的查询，那么这条SQL语句可能需要扫描多个索引页，也就是需要进行多次IO操作。在每扫描一个页并等待其完成再进行下一次扫描，这是没有必要的。用户可以在发出一个IO请求后立即再发出另外一个IO请求，当全部IO请求发送完毕后，等待所有IO操作完成，这就是AIO。AIO的另外一个优势是进行IO Merge操作，也就是将多个IO合并为一个IO操作，这样可以提高IOPS的性能。
  
  在InnoDB 1.1.x之前，AIO的实现是通过InnoDB存储引擎中的代码来模拟的。但是从这之后，提供了内核级别的AIO的支持，称为Native AIO。Native AIO需要操作系统提供支持。MySQL可以通过参数innodb_use_native_aio来决定是否启用Native AIO。在InnoDB存储引擎中，read ahead方式的读取都是通过AIO完成，脏页的刷新，也是通过AIO完成。
  
  （5）刷新领接页
  
  当刷新一个脏页时，innodb会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新。这样做，通过AIO将多个IO写入操作合并为一个IO操作。该工作机制在传统机械磁盘下有显著优势。但是需要考虑下面两个问题：
  - 是不是将不怎么脏的页进行写入，而该页之后又会很快变成脏页？
  - 固态硬盘有很高IOPS，是否还需要这个特性？
  为此InnoDB存储引擎1.2.x版本开始提供参数innodb_flush_neighbors来决定是否启用。对于传统机械硬盘建议使用，而对于固态硬盘可以关闭。
7. CheckPoint技术
  
  CheckPoint技术是用来解决如下几个问题：
  - 缩短数据库恢复时间：缩短数据库恢复时间，重做日志中记录了的checkpoint的位置，这个点之前的页已经刷新回磁盘，只需要对checkpoint之后的重做日志进行恢复。这样就大大缩短了恢复时间
  - 缓冲池不够用时，将脏页刷新到磁盘：缓冲池不够用时，根据LRU算法，溢出最近最少使用的页，如果页为脏页，强制执行checkpoint，将脏页刷新回磁盘。
  - 重做日志不可用时，刷新脏页：重做日志不可用，是指重做日志的这部分不可以被覆盖，因为由于重做日志的设计是循环使用的，这部分对应的数据还未刷新到磁盘上。数据库恢复时，如果不需要这部分日志即可被覆盖；如果需要必须强制执行checkpoint将缓冲池中的页至少刷新到当前重做日志的位置。
  InnoDB存储引擎内部，两种checkpoint，分别为:
  - Sharp Checkpoint
  - Fuzzy Checkpoint
  什么时间触发checkpoint？
  
  （1）Sharp Checkpoint发生在数据库关闭时，将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数：innodb_fast_shutdown=1。不适用于数据库运行时的刷新。
  
  （2）在数据库运行时，InnoDB存储引擎内部采用Fuzzy Checkpoint，只刷新一部分脏页。
  
  几种发生Fuzzy Checkpoint的情况？
  
  （1）MasterThread Checkpoint
  异步刷新，每秒或每10秒从缓冲池脏页列表刷新一定比例的页回磁盘。
  
  （2）FLUSH_LRU_LIST Checkpoint
  InnoDB存储引擎需要保证LRU列表中差不多有100个空闲页可供使用。在InnoDB 1.2.x版本之前，用户查询线程会检查LRU列表是否有足够的空间操作。如果没有，根据LRU算法，溢出LRU列表尾端的页，如果这些页有脏页，需要进行checkpoint。因此叫：flush_lru_list checkpoint。InnoDB 1.2.x开始，这个检查放在了单独的进程（Page Cleaner）中进行。
  
  设置参数：innodb_lru_scan_dept：控制LRU列表中可用页的数量，该值默认1024
  
  （3）Async/Sync Flush Checkpoint
  指重做日志不可用的情况，需要强制刷新页回磁盘。
  
  （4）Dirty Page too much Checkpoint
  
  即脏页太多，强制checkpoint.保证缓冲池有足够可用的页。参数设置：innodb_max_dirty_pages_pct = 75 表示：当缓冲池中脏页的数量占75%时，强制checkpoint。
MyISAM

MYISAM（发音为 "my-阿塞姆"）是MYSQL的ISAM扩展格式。MYSQL-5.5版本之前默认引擎是MyISAM，之后是innoDB。MYISAM和ISAM一样，读取速度很快，除此之外还提供了索引、字段管理、表格锁定等功能，对多个并发的读写操作进行了优化。所以大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。但是它仍然没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT或UPDATE数据时需要锁定整个表，效率便会低一些。

1.2 B+树

大小

在 MySQL 中 B+ 树的一个节点大小为“1页”，也就是16k。之所以设置为一页，是因为对于大部分业务，一页就足够了：
首先InnoDB的B+树中，非叶子节点存的是key + 指针；叶子节点存的是数据行。
对于叶子节点，如果一行数据大小为1k，那么一页就能存16条数据；对于非叶子节点，如果key使用的是bigint，则为8字节，指针在mysql中为6字节，一共是14字节，则16k能存放 16 * 1024 / 14 = 1170 个索引指针。于是可以算出，对于一颗高度为2的B+树，根节点存储索引指针节点，那么它有1170个叶子节点存储数据，每个叶子节点可以存储16条数据，一共 1170 x 16 = 18720 条数据。而对于高度为3的B+树，就可以存放 1170 x 1170 x 16 = 21902400 条数据（两千多万条数据），也就是对于两千多万条的数据，我们只需要高度为3的B+树就可以完成，通过主键查询只需要3次IO操作就能查到对应数据。所以在 InnoDB 中B+树高度一般为3层时，就能满足千万级的数据存储，所以一个节点为1页，也就是16k是比较合理的。
MyISAM

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
InnoDB

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

1.3 索引的分类

基于索引的范围我们分为,单列索引,组合索引,全文索引

基于是否存在全量的数据信息我们也分为聚簇索引和非聚簇索引

1.3.1 单列索引

单列索引:普通索引，唯一索引，主键索引

1.3.1.1 普通索引

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

1.3.1.2 唯一索引

索引列中的值必须是唯一的，但是允许为空值。

1.3.1.3 主键索引

是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。
而且在innoDB里主键索引中存储的也是全量的数据

1.3.2 组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询。

1.3.3 全文索引

只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 ..." 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。一般开发中，不会用到全文索引，因为其占用很大的物理空间和降低了记录修改性，故较为少用。

1.4 索引的优化

这篇文章写得很好,索引、explain分析:索引、explain分析

2 mysql的锁

2.1 不同存储引擎的锁的分类

2.1.1 MyISAM 表锁

表共享读锁（Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
表独占写锁（Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止

MyISAM加表锁方法：

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作
（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

2.1.2 InnoDB行级锁和表级锁

InnoDB 实现了以下两种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

锁模式的兼容情况：

（如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。）

InnoDB加锁方法：

意向锁是 InnoDB 自动加的，不需用户干预。
对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB
会自动给涉及数据集加排他锁（X)；
对于普通 SELECT 语句，InnoDB 不会加任何锁；
事务可以通过以下语句显式给记录集加共享锁或排他锁：
- 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
- 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

InnoDB 行锁实现方式：

InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，
别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。（更多阅读：MySQL索引总结）
由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。应用设计的时候要注意这一点。

InnoDB的间隙锁：

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

InnoDB使用间隙锁的目的：

防止幻读，以满足相关隔离级别的要求；
满足恢复和复制的需要：

MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制（复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复）有以下特点：

一是 MySQL 的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。

二是 MySQL 的 Binlog 是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。

由此可见，MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读

2.2 锁的种类和原理

2.2.1 锁机制

共享锁（读锁）：其他事务可以读，但不能写。
排他锁（写锁）：其他事务不能读取，也不能写。

2.3 锁的颗粒度

MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：

MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）
BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁
InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

不同粒度锁的比较：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
- 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
- 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
- 最大程度的支持并发，同时也带来了最大的锁开销。
- 在 InnoDB 中，除单个 SQL 组成的事务外，
  锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
- 行级锁只在存储引擎层实现，而Mysql服务器层没有实现。行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

3 mysql的事物

3.1 数据库常见的事物问题

3.1.1 脏读

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。

3.1.2 不可重复读

对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。

3.1.3 幻读

幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

3.2 数据库的ACID

事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）四个特性，简称 ACID，

3.3 数据库的隔离级别

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

读未提交（READ UNCOMMITTED）

读提交（READ COMMITTED）

可重复读（REPEATABLE READ）

串行化（SERIALIZABLE）

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题，下面展示了 4 种隔离级别对这三个问题的解决程度。

只有串行化的隔离级别解决了全部这 3 个问题，其他的 3 个隔离级别都有缺陷。

3.4 数据库的MVCC(并发版本控制)

基于undolog进行的事物控制,主要的理论是类似日志快照的概念.

详细可以参考:mvcc的b站视频:mvcc的b站视频

4 主从复制

4.1 binlog的三种方案

4.1.1 Row level

Row level（用到MySQL的特殊功能如存储过程、触发器、函数，又希望数据最大化一直则选择Row模式，我们公司选择的是row）
　简介：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
　优点：能清楚的记录每一行数据修改的细节
　缺点：数据量太大

4.1.2 Statement level（默认）

简介：每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行。在主从同步中一般是不建议用statement模式的，因为会有些语句不支持，比如语句中包含UUID函数，以及LOAD DATA IN FILE语句等
　优点：解决了 Row level下的缺点，不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高新能
　缺点：容易出现主从复制不一致

4.1.3 Mixed（混合模式）

简介：结合了Row level和Statement level的优点，同时binlog结构也更复杂。

4.2 主从原理

Replication 的原理

主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面。
从服务器上面也启动一个 I/O thread，通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log（中继日志）里面。
从服务器上面同时开启一个 SQL thread 定时检查 Realy log(这个文件也是二进制的)，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。

复制的方式:

a、异步复制原理：

b、半同步复制原理：

事务在主库写完binlog后需要从库返回一个已接受，才放回给客户端；5.5集成到mysql，以插件的形式存在，需要单独安装确保事务提交后binlog至少传输到一个从库不保证从库应用完成这个事务的binlog性能有一定的降低网络异常或从库宕机，卡主库，直到超时或从库恢复

c、并行复制mysql并行复制 ● 社区版5.6中新增 ● 并行是指从库多线程apply binlog ● 库级别并行应用binlog，同一个库数据更改还是串行的(5.7版并行复制基于事务组)设置set global slave_parallel_workers=10;设置sql线程数为10

4.3 主从延迟问题的解决

从库优化Mysql参数。比如增大innodb_buffer_pool_size，让更多操作在Mysql内存中完成，减少磁盘操作。
提高数据库物理机的性能
业务代码的妥协。将实时性要求高的某些操作，使用主库做读操作。比如我写了数据到主库了，需要马上展示数据，不要到从库去读数据，因为从库可能还没同步过去呢。直接从主库读数据，保证是最新的数据展示。
从库的线程改为多个同步线程同步数据。mysql-5.6.3为了解决这个问题，从服务器上，每一个库开一个线程来同步。
网络优化。网络堵塞，也会导致同步延迟。跨机房的数据库同步，会存在同步延迟。保证主从在同一个机房里面去。
同步机制改为半同步级别

5 实际应用

5.1 mysql的慢查询的定位以及解决方案

5.2 mysql的读写分离的方案

5.3 mysql的cpu,连接数和内存达到峰值的问题定位,以及优化方案

5.4 mysql的常用的参数配置

5.5 查看mysql的常用的命令

5.6 分布式事务

5.7 分库分表的原则和方案

posted on 2021-09-16 16:49 一只皮皮阳阅读(96) 评论(0) 编辑收藏举报