Mysql面试

mysql底层框架

sql语句执行过程？

• 当sql语句通过sql接口进入，首先会经过sql解析器，分析这条sql语句到底要干什么，再通过sql优化器选择性能最好的方案去执行器中执行这条sql语句，然后通过引擎去提供对对应的操作的支持。
• 而mysql5.6开始默认采用的引擎为innodb引擎，他里面有一个buffer pool空间，如果MySQL要加载数据，会先从磁盘中拿出数据所在的数据页，然后放到buffer pool空间中，进行缓存。
• 当我们要对数据进行修改时，其实并不会实时把缓存页中的数据直接刷写回磁盘中，因为这样做性能太低了，而是通过后台线程每隔一段时间同步一次数据到磁盘，这样就会出现缓存页中的数据和磁盘数据页中的数据不一致，这种情况就叫脏页。

mysql怎么保证事务？

• 但是mysql是强事务的，他得保证数据的安全性，所以他提供了一个undo日志文件，支持事务的回滚操作。
• 支持事务回滚了，事务什么时候提交成功呢，就是缓存中的数据写入磁盘的时候，

• MySQL在实际工作时候的两种数据读写机制 ：

  • 一种是对redo log、binlog这种日志进行的磁盘顺序读写
  • 一种是对表空间的磁盘文件里的数据页进行的磁盘随机读写
    • 前面直接把数据页中的数据读到缓存页中的方法就是磁盘随机读的方式， 因为要读取的这个数据页可能在磁盘的任意一个位置，所以在读取磁盘里的数据页的时候只能是用随机读的这种方式 ，但是这样性能是比较差的，不可能每次更新都把数据直接写回去，然后重新读。
    • 而是 Buffer Pool的缓存页里更新 一次数据，通过引擎中的redo log记录一条日志，以顺序的方式追加到磁盘中日志文件的末尾， 磁盘顺序写的性能其实是很高的，某种程度上来说，几乎可以跟内存随机读写的性能差不多 。
    • 然后mysql提供一个归档日志binglog，可以用于数据的恢复，主从同步等，这样才算事务提交成功。

      InnoDB内存结构

      buffer pool基本概念？

• buffer pool是InnoDB引擎的缓冲池，作用是用来缓存表数据与索引数据，减少磁盘IO操作，提升效率。他由缓存数据页（page）和对缓存数据页进行描述的控制块 组成, 控制块中存储着对应缓存页的所属的表空间、数据页的编号、以及对应缓存页在Buffer Pool中的地址等信息。默认大小为128M，page页大小为16kb，控制页大小大约为800b。

  怎么判断一个页是否在BuffferPool中缓存？

• mysql中有一个哈希表数据结构，使用的是表空间号+数据页号作为key，缓存页对应的控制块作为value。当需要访问某个页的数据时，先根据表空间号+数据页号查看哈希表中是否存在缓冲页。有的话直接使用，没有就从free链表中选出一个空闲的缓冲页出来，加载磁盘中对应的数据页。

  Page管理机制

  page页分为哪几类？

• free page ： 在数据库初始化时bufferPool会创建出来N多个空闲page，未被使用

• clean page：被使用但数据没有被修改过的page

• dirty page：脏页，被使用并且数据被修改过的page，缓存页中数据和磁盘的数据产生了不一致

  各种类型的page页怎么维护和管理？

• free list：表示空闲缓冲区，用于管理空闲页

  • free链表把所有空闲的缓冲页对应的控制块作为一个个的节点放到一个链表中，只有一个基点不记录缓存页信息，里面存放了链表的头节点尾节点地址和节点个数。当需要加载磁盘数据页时，就从free链表中取出一个空的控制块，填入对应缓存页的信息，把对应free链表中的节点移除。放入到哈希表中。
  • 
• flush list： 表示需要刷新到磁盘的缓冲区，管理脏页，内部page按修改时间排序。
  • InnoDB引擎为了提高处理效率，并不是每次修改缓冲页数据后就立刻把数据刷新到磁盘上，而是在未来的某个时间点进行刷新操作. 所以需要使用到flush链表存储脏页，凡是被修改过的缓冲页对应的控制块都会作为节点加入到flush链表.

• lru list：表示正在使用的缓冲区，管理clean page和dirty page

  • 每使用一次数据，就把这个数据提到靠近头的位置，距离上次使用时间最长的就会 排到尾节点上，释放空间时从末尾淘汰。

    MySQL索引原理与优化

    B-Tree

• B-Tree的思想设计是为了减少磁盘IO的次数，这是数据库提升性能的关键，每个节点尽可能多的存储一些数据,每次磁盘IO就能多加载一些数据到内存，他是一种平衡的多路查找树，允许一个节点存放多个数据。

B-Tree结构存储索引的特点

• 以键值对的形式存储一条记录，key为记录的键值，data记录除主键之外的数据，索引值和data数据分布在整棵树结构中，索引和索引之间部分是指针，记录着子节点的地址信息，每个节点可以存放多个索引值及对应的data数据，树节点中的多个索引值从左到右升序排列。

B-Tree的查找过程

• 因为B-Tree通常是在磁盘上存储的,所以首先要查找节点，进行磁盘IO操作，把该节点读入内存中, 然后通过折半查找来查找关键字,如果查到就结束查找。没有找到就需要判断大小来找到合适的分支继续查找,如果已经找到了叶子节点,就结束查询。

B-Tree的优缺点及使用场景

• 优点: B树可以在内部节点存储键值和相关记录数据，因此把频繁访问的数据放在靠近根节点的位置将大大提高热点数据的查询效率。
• 缺点: B树中每个节点不仅包含数据的key值,还有data数据. 所以当data数据较大时,会导致每个节点存储的key值减少,并且导致B树的层数变高.增加查询时的IO次数.

• 使用场景: B树主要应用于文件系统以及部分数据库索引，如MongoDB，大部分关系型数据库索引则是使用B+树实现

  B+Tree

• B+Tree的特征

  • InnoDB存储引擎就是用B+Tree实现其索引结构，其非叶子节点只存储键值信息，所有叶子节点之间都有一个链指针，而数据记录都存放在叶子节点中。

• B+Tree的优势

  • B+树的磁盘读写代价更低
    • B+Tree的节点的大小等于一个数据页大小，并且B+Tree的根节点是保存在内存中的,子节点才是存储在磁盘上，B+树的非叶子节点并没有指向关键字具体信息的指针（data），可以容纳的关键字数量就越多。相对磁盘IO次数也就变少了。
  • B+树查询效率更加稳定
    • 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当
  • B+树便于范围查询
    • 相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

      聚簇索引与非聚簇索引

• 聚簇索引

  • 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据，在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储，按照主键顺序构建 B+Tree结构。
  • InnoDB的表要求必须要有聚簇索引：
    • 如果表定义了主键，则主键索引就是聚簇索引
    • 如果表没有定义主键，则第一个非空unique列作为聚簇索引
    • 否则InnoDB会重建一个隐藏的row-id作为聚簇索引
  • 使用聚簇索引时要注意的问题
    • 当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出线新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
    • 建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。
• 非聚簇索引
  • 非聚簇索引是InnoDB的二级索引，是根据索引列构建B+Tree结构，但在 B+Tree 的叶子节点中只存了索引列和主键的信息，二级索引占用的空间会比聚簇索引小很多， 通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。
  • 回表问题：先根据普通索引查询到主键值，再根据主键值在聚集索引中获取行记录。
    • 覆盖索引是一种避免回表查询的优化策略: 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
      • 具体实现方式：
        • 将被查询的字段建立普通索引或者联合索引，这样的话就可以直接返回索引中的数据，不需要再通过聚集索引去定位行记录，避免了回表的情况发生。