sql语句执行过程？

  • 当sql语句通过sql接口进入，首先会经过sql解析器，分析这条sql语句到底要干什么，再通过sql优化器选择性能最好的方案去执行器中执行这条sql语句，然后通过引擎去提供对对应的操作的支持。
  • 而mysql5.5开始默认采用的引擎为innodb引擎，他里面有一个buffer pool空间，如果MySQL要加载数据，会先从磁盘中拿出数据所在的数据页，然后放到buffer pool空间中，进行缓存。

  • 当我们要对数据进行修改时，其实并不会实时把缓存页中的数据直接刷写回磁盘中，因为这样做性能太低了，而是通过后台线程每隔一段时间同步一次数据到磁盘，这样就会出现缓存页中的数据和磁盘数据页中的数据不一致，这种情况就叫脏页。

    mysql怎么保证事务安全性？

  • mysql是强事务的，他得保证数据的安全性，他是通过日志的方式保证事务安全性，当开启事务时通过记录undolog日志文件，支持事务的回滚操作。

  • 而Buffer Pool的缓存页里更新 一次数据，通过引擎中的redo log记录一条日志，以顺序的方式追加到磁盘中日志文件的末尾， 磁盘顺序写的性能其实是很高的，某种程度上来说，几乎可以跟内存随机读写的性能差不多 。

  • 然后mysql提供一个归档日志binglog，可以用于数据的恢复，主从同步等，这样才算事务提交成功。

    buffer pool基本概念？

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

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

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

    page页分为哪几类？

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

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

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

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

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

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

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

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

      MySQL索引的底层数据结构？

  • MySQL索引的底层数据结构是B+树数据结构，B+树有三个特性：

    • B+树是一个平衡多叉树，与平衡二叉树的每一个节点下面最多有两个子节点相比B+树每一个节点下面有多个子节点。
    • B+树叶子节点（也就是最下面一层的没有子节点的节点）有一个双向链表，左右是为了方便范围查找（假如我找前100条数据，那么我找到第一条叶子节点的数据就可以从叶子节点直接向后取100个数据即可，不用再从根节点向下寻找）
    • B+树的叶子节点有data数据（就是数据库中这一条所有的字段数据），非叶子节点只有索引数据。

      B树和B+树的区别，为什么MySQL底层使用B+树而不使用B树

  • B树中每个节点不仅包含数据的key值,还有data数据. 计算机IO一次只能拿一数据页的数据（大小为16KB），所以当data数据较大时,会导致每个节点存储的key值减少,并且导致B树的层数变高.增加查询时的IO次数.

  • B+树进行范围查找时，因为叶子节点有一个双向链表，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

    为什么不用二叉树作为索引的底层结构而用B+树呢

  • 因为AVL树存在的缺陷每一次抓取的数据太少，导致树的深度太高，每查询一次实际上就会走一次IO，减少磁盘IO是数据库提升性能的关键。

    聚簇索引与非聚簇索引

  • 聚簇索引

    • 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据，在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储，按照主键顺序构建 B+Tree结构。
    • InnoDB的表要求必须要有聚簇索引：
      • 如果表定义了主键，则主键索引就是聚簇索引
      • 如果表没有定义主键，则第一个非空且唯一的列作为聚簇索引
      • 否则InnoDB会重建一个隐藏的row-id作为聚簇索引
    • 使用聚簇索引时要注意的问题
      • 当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出线新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
      • 建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

  • 非聚簇索引

    • 非聚簇索引是InnoDB的二级索引，是根据索引列构建B+Tree结构，但在 B+Tree 的叶子节点中只存了索引列和主键的信息，二级索引占用的空间会比聚簇索引小很多， 通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。
    • 回表问题：先根据普通索引查询到主键值，再根据主键值在聚集索引中获取行记录。
      • 覆盖索引是一种避免回表查询的优化策略: 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
        • 具体实现方式：
          • 将被查询的字段建立普通索引或者联合索引，这样的话就可以直接返回索引中的数据，不需要再通过聚集索引去定位行记录，避免了回表的情况发生。

            sql优化

            怎么查询慢sql

  • 通过MySQL提供的慢查询日志记录响应时间超过阈值的语句，默认是关闭的，因为比较耗性能，只有需要定位慢sql才打开。

  • 定位到慢sql后通过Explain关键字判断这条sql慢的原因，一般有三个：
    • 没有设置索引
    • 设置了索引但效果不好
    • 索引失效
  • 设置索引应该遵循什么规则？
    • 遵循小表驱动大表
    • 尽量用联合索引的第一个字段进行排序
    • 尽量使用到覆盖索引，避免回表查询
    • 避免太多的 join操作，推荐不要超过三张表
    • 尽量不要用区分度很低的字段建立索引，如果迫不得已一定要使用这种字段，动态查询时客户没有输入这个值，查询时手动带上这个字段，比如gender in （0，1）.
  • 索引失效的原因？
    • 是否遵守最左前缀法则
    • 索引列上是不是进行计算了5
    • 范围查找之后的索引会失效
    • 覆盖索引记住用，避免回表。
    • ！=、is null、is not null、or都导致索引失效。
    • like百分号加左边导致索引失效，解决方法：使用覆盖索引。
  • 如何判定是否需要创建索引?
    • 较为频繁的作为查询条件的字段应该创建索引.
    • 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件.
    • 更新非常频繁的字段不适合创建索引.
    • 不会出现在WHERE子句中的字段不该创建索引.

  • 如果我建立的联合索引是A、B、C这三个字段，那么我查询的时候条件是A、B那么这个索引还有效果吗？

    • 因为最左前缀原则，假如当我们给姓名、年龄、性别三个字段建立了索引，那么从左边先开始的字段才可以索引有效果。
    • 如果查询A、C，中间断了那么C也会索引失效。
    • 因为MySQL创建联合索引的规则是: 首先会对联合索引最左边的字段进行排序，在第一个字段的基础之上 再对第二个字段进行排序 ，所以最佳左前缀原则其实是和B+树的结构有关系, 最左字段肯定是有序的, 第二个字段则是在第一个字段的基础上有序，但相对于整个表是无序的，所以如果直接使用第二个字段 通常是使用不到索引的.

      MVCC原理

  • MVCC（Multi-Version Concurrency Control）是多版本并发控制，是在多个事务情况下可以保证每个事物之间相互隔离，MVCC机制适用于读已提交和可重复读这两个事务隔离级别。

  • MVCC机制中有最重要的两部分：
    • undo日志版本链：在一行数据被多个事务依次修改过后，每次的修改前的记录都会保存到undo日志版本链中，用于回滚操作。
    • 一致性视图：read-view：每一个事务开启后，执行任何查询sql时就会生成当前事务的一致性视图。这个视图是由查询的时候所有未提交的事务id数组和已创建的最大事务id组成。
  • MySQL是通过可重复读的，所以当一个事务执行第一个查询语句的时候就生成了一个一致性视图，当第二个事务修改了这条数据后，第一个事务查询的还是原来的数据，因为第一个事务当时查询的时候生成了一致性视图，这个一致性视图对于第一个事务来说没有变化，所以查询出来的数据也没有变化。