网站Logo 有一本小书
首页
全部
瞬间
友链
足迹
关于

MYSQL篇（八股）

tanqi
4
2025-12-29

MYSQL篇

MYSQL的主要存储引擎包括那些？

  • InnoDB

    • 支持事务，行级锁和外键

    • 提供高并发性能，适用于高负载的OLTP应

    • 数据以聚类索引的方式存储，提高检索效率

    • 不支持集群数据库支持，哈希索引、T-tree索引

    • 存储限制64TB

  • MyISAM

    • 不支持事务和外键，使用表级锁

    • 适合读取多，更新少的场景，如数据仓库

    • 具有较高的读性能和较快的表级锁定

    • 不支持集群数据库，聚簇索引，数据缓存，外键支持，哈希索引，MVCC，T-tree索引，事务

  • MEMORY

    • 数据存储再内存中，速度快，但数据在服务器重启后丢失

    • 使用与临时数据存储或快速缓存

  • NOB（NDBCluster）

    • 支持高可用性和数据分布，适合大规模分布式应用

    • 提供行级锁和自动分区

  • ARCHIVE

    • 用于存储大量历史数据，支持高效的插入和压缩

    • 不支持索引，适合日志数据存储

mysql存储引擎.png
MYSQL的主要存储引擎包括那些？
如何回答:在我了解的情况下，官网上是10个存储引擎，常用五个存储类型，InnoDB、MYISAM、MEMORY、NOB、ARCHIVE
1、InnoDB：在存储方面限制是64TB，是支持事务、行级锁和外键的，然后提供了高并发性能，适用于高负载的OLTP应用，数据以聚类索引存储，可提高检索的效率，不过不支持集群，哈希索引，T-tree索引。
2.MyISAM：不支持事务和外键，使用表级锁，适合读取多，更新少的场景如数据仓库，具有很高的读性能
3.MEMORY：数据存储在内存，速度很快，但数据在重启服务器后就丢失，可以使用临时数据存储或快速缓存
4.NOB：支持高可用性和数据分布，适合大规模分布式应用，提供行级锁还有自动分区
5.ARCHIVE：用于存储大量历史数据，支持高效的插入和压缩，不支持索引，适合日志数据存储

MYSQL InnoDB 引擎中的聚簇索引和非聚簇索引有什么区别？

  • 聚簇索引

    • 索引叶子节点存储的是数据行，可以直接访问完整数据。

    • 每个表只能有一个聚簇索引，通常是主键索引，适合范围查询和排序

  • 非聚簇索引

    • 索引叶子节点存储的是数据行的主键和队友索引列，需通过主键才能访问完成的数据行。

    • 一个表可以有多个非聚簇索引（称为非主键索引、辅助索引、二级索引），适用于快速查找特定列的数据

mysql聚簇索引和非聚簇索引有什么区别.png
MYSQL InnoDB 引擎中的聚簇索引和非聚簇索引有什么区别？
如何回答:在聚簇索引和非聚簇索引中，聚簇索引相当于主键索引，用于范围查询和排序但只能有一个聚簇索引可以直接访问完整数据，非聚簇索为非主键索引和辅助索引、二级索引，可用于查询特定列，而索引叶子节点存储的是数据行的主键和对应索引列，需要主键才能访问完成的数据行

MYSQL 的索引类型有哪些？

从数据结构角度来看，mysql索引可以分为以下几类

  • B+索引

  • 哈希索引

  • 倒排索引（即全文索引 Full-Text）

  • R-树索引（多维度空间树）

从常见的基于InnDB B+树索引角度来看，可以分为

  • 聚簇索引

  • 非聚簇索引

从索引性质的角度来看，可以分为：

  • 普通索引（二级索引、辅助索引）

  • 主键索引

  • 联合索引

  • 唯一索引

  • 全文索引

  • 空间索引

mysql索引类型有哪些.png
MYSQL 的索引类型有哪些？
如何回答：从数据结构来说：有B+索引，哈希索引，倒排索引、R-索引，从常见的lnnDB B+树索引的角度来看可分为聚簇索引和非聚簇索引，从索引性质来看就是普通索引、主键索引、联合索引、唯一索引、全文索引、空间索引

为什么MYSQL选择使用B+树作为索引结构？

B+树在数据库系统中具有以下几个显著优势：

  • 高效的查找性能

    • B+树是一种自平衡树，每个叶子节点到根节点的路径长度相同，B+树在插入和删除节点时会进行分裂和合并操作，以保证树的平衡，但它又会有一定的冗余节点，使得删除的时候树结构的变化小，更高效。查找、插入、删除等操作的时间复杂度为0(log n)，能够保证在大数据量的情况下也能有较快的响应时间。

  • 树的高度增长不会过快，使得查询磁盘的I/O次数减少：

    • B+树不像红黑树，数据越多树的高度增长就越快。它是多叉树，非叶子节点仅保存主键或索引值和页面指针，使得每一页能容纳更多的记录，因此内存中就能存放更多索引，容易命中缓存，使得查询磁盘的I/O次数减少。

  • 范围查询能力强

    • B+树特别适合范围查询，因为叶子节点通过链表链接，从根节点定位到叶子节点查找到范围的起点之后，只需要顺序扫描链表即可遍历后续的数据，非常高效

MYSQL选择使用B+树作为索引结构.png
为什么MYSQL选择使用B+树作为索引结构？
如何回答:B+树在数据库系统中具有高效的查询性能，树的高度增长不会过快，磁盘I/O次数少，范围查询能力强等优势。
在高效的查询性能方面，因为B+树是一种平衡树，每个叶子节点到根节点的路径长度相同，B+树在插入和删除节点时会进行分裂和合并，这样保证了树的平衡，但他有一定的冗余节点，使得删除的时候变化小更高效，在查找插入删除等操作时间复杂度为0(logn).
而且B+树也不像红黑树，数据越多高度就越增长，它是多叉树，非叶子节点仅保存主键或索引值页面指针，使得能容纳更多记录放更多索引容易命中缓存，磁盘IO次数减少，还有B+树更适合范围查询，叶子节点通过链表链接的，只需要顺序扫描链表即可便利后续的数据

MYSQL索引的最左前缀匹配原则是什么？

  • 重点：MYSQL索引的最左前缀匹配原则指的是使用联合索引时，查询条件必须从索引的最左侧开始匹配。如果一个联合索引包含多个列，查询条件必须包含第一个列的条件，如果是第二个列，以此类推。

  • 底层原理：

    • 因为联合索引在B+树中的排序方式遵循“从左到右”的排序，例如联合索引(frist_name,last_name,age)会按照(first_name，last_name,age）的顺序在B+树中进行排序。

      • MYSQL在查找时会优先使用frist_name作为匹配依据，然后依次使用last_name和age。因此，组合索引能够从左到右依次高效匹配，跳过最左侧字段会导致无法利用该索引

MYSQL索引的最左前缀匹配原则.png
MYSQL索引的最左前缀匹配原则是是什么？
如何回答：MYSQL索引的最左前缀匹配原则是指使用联合索引时，查询条件必须从索引的最左侧开始匹配，如果一个联合索引包含多个列，查询条件必须包含第一个列的条件，如果是第二个列依此类推。
还有一个特别有意思的是MYSQL8.0.13版本它做了个优化在基数很低的场景下，引入了Skip Scan Range Access Method,利用了范围扫描代替了全表扫描的发生，隐式的构造了查询条件，使得看起来不需要最左匹配原则而已

Mysql三层B+树能存多少数据？

  • 在Mysql中的InnoDB存储引擎中，B+树默认数据页大小为16KB。

    • 参数

      • 每个节点页大小为16KB（即16384字节）

      • 假设每个数据记录的主键和数据大小为1Kb(取整计算)

      • 每个内部节点（非叶子节点）存储的是指向子节点的指针和索引键

    • 三层B+树的存储计算

      • 叶子节点：“第三层为叶子节点，每个叶子节点页可存储16条数据记录（16KB%1KB）

      • 第二层（中间件）：假设每个指针6字节和索引键（一般为bigint）的大小为8字节，那么每个中间节点页可以指向1170个叶子节点，16KB转成字节需要乘以1024，因此（16*1024/（6+8）字节）=1170

      • 第一层（根节点）：根节点可以指向1170个中间节点

    • 由此，三层B+树大致能存储的数据总量为:1170x1170x16=21902400，一棵三层的B+树在MYSQL中可以存储大约2000万条记录。

      • 注意这里只是估算，具体数量会因为实际的大小，页大小等因素略有不同。

    三层B+树能存多少数据.png
    Mysql三层B+树能存多少数据？
如何回答：一棵三层的B+树在MYSQL中可以存储大约2000万条记录，从mysql的InnoDB存储引擎中B+树的默认页大小为16KB，假设每个数据记录的主键和数据大小为1Kb取整计算，在第三层为叶子节点可以存储16条数据16kb/1kb，在假设每个指针6字节和索引键的大小为8字节，那么每个中间页可以指向（16*1024/（6+8））=1170个叶子节点,大致计算三层B+树大致能存储的数据总理为:1170x1170x16=21902400

MYSQL中的回表是什么？

  • “回表”是指在使用二级索引（非聚簇索引）作为条件进行查询时，由于二级索引中只存储了索引字段的值和对应主键值，无法得到其他数据。如果要查询数据行中的其它数据，需要根据主键去聚簇索引查找实际的数据行，这个过程被称为回表。

    MYSQL中的回表是什么.png
    MYSQL中的回表是什么？
如何回答：回表是指二级索引作为条件查询时，由于二级索引只存储了索引字段的值和对应主键值，无法得到其他的数据。如果要查询其他数据行中的其他数据，需要根据主键去聚簇索引查找实际的数据行，而这个过程称之回表

MYSQL中使用索引一定有效吗？如何排查索引效果？

  • 索引不一定有效

    • 例如查询条件中不包含索引列，低基数索引效果不佳，或查询条件复杂且不匹配索引的顺序。

    • 对于一些小表MYSQL可能选择全表扫描而非使用索引，因为全表扫描的开销可能更小

    • 最终是否用上索引是根据MYSQL成本计算决定的，评估CPU和I/O成本最终选择用辅助索引还是全表扫描。有时候确实是全表扫描成本低所以没用上索引。但有时候确实是全表扫描成本低所以没用上索引。但有时候由于一些统计数据的不准确，导致成本计算误判，而没用上索引。

  • 排查索引效果的方法

    • 使用EXPLAN命令，通过在查询前加上EXPLAIN，可以查看MYSQL选择的执行计划，了解是否使用了索引，使用了，哪一个索引、估算的行数等信息

      • 主要观察EXPLAIN结果一下几点：

        • type(访问类型)：这个属性显示了查询使用的访问方法，ALL、index、range等。当查询使用索引时，这个属性通常会显示为index或range，表示查询使用了索引访问。如果这个值是ALL，则表示查询执行了全表扫描，没有使用索引。

        • key(使用的索引)：这个属性显示了查询使用的索引，如果查询使用了索引，则会显示索引的名称。如果这个值是NULL，则表示查询没有使用索引

        • rows（扫描行数）：这个属性显示了查询扫描的行数，需要评估下扫描量

      MYSQL中使用索引一定有效吗？如何排查索引效果？.png
      MYSQL中使用索引一定有效吗？如何排查索引效果？
如何回答：索引不一定有效，例如查询条件中不包含索引列，或者低基数索引效果不佳，查询条件复杂不匹配索引的顺序，而对于一些小表MYSQL可能会选择全表扫描而非索引，因为全表扫描的开销可能更小。最终能否使用上索引是MYSQL通过计算决定的，评估了CPU和I/O成本进行最终选择索引还是全表扫描，而有些时候由于一些统计数据的不准确，导致计算误判，而没有使用上索引
排除索引有没有效果的方法，可以使用EXPLAN命令，通过查询前加上EXPLAN,即可查看MYSQL选择的执行计划，了解是否使用了索引，哪一个索引，估算的行数等信息而主要观察EXPLAN的type，key，rows这几个属性，如果type显示为ALL则表示使用全表扫描，如果KEY值是NULL也是没有使用索引的，也可以根据rows显示的扫描行数评估下扫描量

在MYSQL中建立索引时需要注意那些事项？

  • 不能盲目建立索引，索引并不是越多越好，索引会占用空间，且每次修改的时候可能都需要维护索引的数据，消耗资源

  • 对于字段的值有大量重复的不要建立索引。比如说：性别字段，在这种重复比例很大的数据行中，建立索引页不能提高检索速度，但也不是绝对的例如定时任务的场景，大部分任务都是成功的，少部分任务状态是失败的，这时候通过失败状态去查询任务，实际上能过滤大部分成功的任务，效率还是可以的

  • 对于一些长字段不应该建立索引：比如text、longtext这种类型字段不应该建立索引。因为占据的内存大，扫描的时候大量加载至内存中还耗时，使得提升性能可能不明显，甚至可能还会降低整体性能，因为别的缓存数据可能因为它被踢出内存，下次查询还需要从磁盘中获取。

  • 当数据表的修改频率远大于查询频率时，应该好好考虑是否需要建立索引。因为建立索引会减慢修改频率，如果很少的查询较多的修改，则得不偿失。

  • 对于需要频繁作为条件的字段应该建立索引，在where关键词后经常查询的字段，建立索引能提高查询的效率，如果有多个条件经常一起查询，则可以考虑联合索引，减少索引数量。

  • 对经常在order by、group by、distinct后面的字段建立索引。这些操作通常需要对结果进行排序、分组或者去重，而索引可以帮助加快这些操作的速度

    在MYSQL中建立索引时需要注意那些事项？.png

    在MYSQL中建立索引时需要注意那些事项？
如何回答：不能随便的建索引，索引并不是越多越好，会占用空间，每次修改的时候需要维护资源消耗资源，还有对于字段大量重复的不要建立索引如性别这种字段重复比例比较大索引并不能提高检索速度。但也不是绝对的如：在定时任务场景，大部分是成功的，少部分是失败的，这种时候可以通过失败状态去查询任务，能过滤大部分成功的任务，效率还是可以的。对于text、longtext这种类型字段也不应该建立索引，因为占据内容大扫描的时候大量加载至内存还耗时，提升不明显有时候还会降低整体性能，因为别的缓存数据可能因为它被踢出资源，下次查询还需要从磁盘获取。还有当数据表修改频率远大于查询效率时，应该好好考虑是否建立索引，因为建立索引会减慢修改频率，如果是很少的查询，较多修改，则得不偿失。还有对于频繁作为条件的字段应该建立索引，在where关键词后经常查询的字段，建立索引能提高查询效率，如果多个条件经常一起查询则可以考虑联合索引减少索引数量，还有对于排序、分组、去重后面字段建立索引，索引可以加快这些操作的速度

MYSQL中的索引数量是否越多越好？为什么？

  • 索引并不是越多越好，因为索引不论从时间还是空间上都是有一定成本的

    • 从时间上

      • 每次对表的增删改的时候，索引页必须被更新，这会增加写入操作的开销。例如删除一个name为某某的记录，不仅主键索引需要修改，如果name字段有索引，那么name索引也需要修改，所以索引越多需要修改的地方越多，时间开销就大了，并且B+树可能会有页分裂、合并等操作，时间开销就会更大。

      • 还有一点要注意的是MYSQL有个优化器，它需要分析当前的查询，选择最优计划，这过程就需要考虑选择哪个索引的查询成本低。如果索引过多，那么会导致优化器耗费更多的时间在选择上，甚至可能因为数据的不准确而选择了次优的索引

    • 从空间上

      • 每建立一个二级索引，都需要新建一个B+树，默认每个数据页都是16KB，如果数据量很大，索引又很多，占用的空间可不小

      MYSQL中的索引数量是否越多越好？为什么？.png
    MYSQL中的索引数量是否越多越好？为什么？.png
    MYSQL中的索引数量是否越多越好？为什么？
如何回答：索引不是越多越好，因为索引不论从时间还是空间上都有一定成本，从时间每次对标删改的时候，索引页必须被更新，这会增加写入操作的开销。例如删除一个name未某某的记录，不仅主键索引需要修改，如果name字段有索引，那么name索引也需要修改，所以索引越多需要的修改越多，时间开销就大了，并且B+树可能会有页分裂，合并等操作，时间开销就更大了。
还有要注意的是MYSQL有个优化器，它需要分析当前的查询，选择最优计划，这过程就需要考虑选择哪一个索引的查询成本低。如果索引过多，那么导致优化器耗费更多的时间在选择上，甚至可能因为数据的不准确而选择了次优的索引
从空间上就是每建立一个二级索引，都需要新建一个B+树，默认每个页数据都是16KB，如果数据量很大索引又很多，占用的空间可不小

如何使用EXPLAN语句进行查询分析？

  • explain主要用来SQL分析，它主要的属性详解如下：

    • id：查询的执行顺序的标识符，值越大优先级越高。简单查询的id通常为1，复杂查询（如包含子查询）的id会又多个

    • select_type（重点）:查询的类型，如SIMPLE（简单查询），PRIMARY（主查询）、SUBQUERY(子查询)等

    • table ：查询的数据表

    • type（重点）：访问类型，如ALL是全表扫描，index索引扫描，range范围扫描等一般来说性能从好到差的顺序是:const>eq_ref>ref>range>index>ALL

    • possible_keys:可能用到的索引

    • key（重点）：实际用到的索引

    • key_len:用到索引长度

    • ref：显示索引的那一列被使用

    • rows（重点）：估计要扫描的行数，值越小越好

    • filterd：显示查询条件过滤掉的行的百分比。一个高百分比表示查询条件的选择性好

    • extra（重点）：额外信息如：Using index表示覆盖索引，Using where表示where条件进行过滤、using temporary表示使用临时表、using filesort 表示需要额外的排序步骤

  • type的详解

    • system：表示查询的表只有一行（系统表）。这是一个特殊情况，不常见

    • const：表示查询的表最多只有一行匹配结果。这通常发生在查询条件是主键或者唯一索引，并且是常量比较

    • eq_ref:表示对于每个来自前一张表的行，MYSQL仅访问一次这个表。这通常发生在链接查询中使用主键或唯一索引的情况下。

    • ref：MYSQL使用非唯一索引扫描来查找行。查询条件使用的索引是非唯一的（如普通索引）

    • range：表示mysql会扫描表的一部分，而不是全部行。范围扫描通常出现在使用索引的范围查询中。如berween < > >= <=

    • index： 表示mysql扫描索引中的所有行，而不是表中的所有行 即使所有的值覆盖查询，也需要扫描整个索引

    • all ：性能最差 表示mysql需要扫描表中从所有行，即全部扫描。通常出现在没有所有查询的条件中

如何使用EXPLAN语句进行查询分析？.png
如何使用EXPLAN语句进行查询分析?
如何回答：explain主要用于sql的分析，主要关注select_type,查询的类型如简单的查询、主查询、子查询等，还有type访问类型，如All就是全表扫描，index索引扫描，range范围扫描，在观察key，会显示实际用到的索引，rows估计要扫描的行数，值越小越好，extra会显示额外信息如，覆盖所有，条件进行过滤，临时表，排序步骤

MYSQL中如何进行SQL调优

  • 平时进行SQL调优，主要是通过观察慢SQL，然后利用explain分析查询语句的执行计划，识别性能瓶颈，优化查询语句

    • 合理的设计所有，利用联合索引进行覆盖索引的优化避免回表的发生，减少依次查询和随机I/O

    • 避免使用SELECT*，只查询必要的字段

    • 避免在SQL中进行函数计算等操作，使得无法命中索引

    • 避免使用%LIKE，导致全表扫描

    • 注意联合索引需满足最左匹配原则

    • 不要对无索引字段进行排序操作

    • 连表查询需要注意不同字段的字符集是否一致，否则也会导致全表扫描

    • 除此之外，还可以利用缓存优化，一些变化少或者访问频繁的数据设置到缓存中，减轻数据库压力，提升查询的效率

    • 还可以通过业务来优化，例如少展示一些不必要的字段，减少多表查询的情况，将列表查询替换分页分批次查询等

      MYSQL中如何进行SQL调优.png
MYSQL中如何进行SQL调优？
如何回答：平时的sql调优主要通过观察慢sql，然后利用explain分析查询语句的执行计划，识别性能瓶颈，优化查询语句
合理的设计，利用联合索引进行覆盖索引的优化避免回表的发生，减少依次查询和随机I/O
避免使用SELECT*，只查询必要的字段
避免在SQL中进行函数计算等操作，使得无法命中索引
避免使用%LIKE，导致全表扫描
注意联合索引是否满足最左匹配原则
不要对无索引字段进行排序操作
连表查询需要注意不同字段的字符集是否一致，否则也会导致全表扫描
除此之外，还可以利用缓存优化，一些变化少或者访问频繁的数据设置到缓存中，减轻数据库压力，提升查询效率
还可以通过业务来优化，例如少展示一些不必要的字段，减少多表查询的情况，将列表查询替换分页分批次查询等

请详情描述MYSQL的B+树中查询数据全过程

  • 数据从根节点找起，根据比较数据键值与节点中存储的索引键值（用了二分），确定数据落在哪个区间，从而确定分支，从上到下最终定位到叶子节点

  • 叶子节点存储实际的数据行记录，但是一页有16KB大小，存储的数据行不止一条

  • 叶子节点中数据行以组的形式划分，利用页目录结构，通过二分查找可以定位到对应的组

  • 定位组后，利用链表遍历就可以找到对应的数据行

描述MYSQL的B+树中查询数据全过程.png

请详情描述MYSQL的B+树中查询数据全过程？
如何回答：数据从根节点找，利用二分查找根据比较数据键值与节点中存储的索引键值，确定数据落在哪个区间，从而确定分支，从上到下最终定位到叶子节点。叶子节点存储实际的数据行记录，但是一页有16KB大小，存储的数据行不止一条，叶子节点中的数据行以组的形式划分，利用页目录结构，通过二分查找可以定位到对应的组。定位组后，利用链表遍历就可以找到对应的数据行

MYSQL中count(*)、count(1)和count(字段)有扫描区别？

  • count(*)会统计表中所有行的数量，包含null值(不会忽略任何一行数据。)由于只是计算行数，不需要对具体列进行处理，因此性能通常较高。

  • count(1)和count(*)几乎没差别，也会统计表中所有行的数量，包括null值。

  • count(字段名)会统计指定字段不为null的行数，这种写法会对指定的字段进行计数，只会统计字段值不为null的行。

  • count（1）和count（*）效率一致，网上其实众说纷坛，实际上官网

    有差异的

count(*)、count(1)和count(字段)有扫描区别.png
MYSQL中count(*)、count(1)和count(字段)有扫描区别？
如何回答：count(*)会统计表中所有行的数据量，包含null值，count(1)和count（*）在官网说几乎没差别，统计所有数据包括null数据。
count(字段)只统计字段不为null的行数。这种写法会对指定的字段进行计数

MYSQL中varchar和char有什么区别？

CHAR和CARCHAR是两种固定长度的字符串。

  • CHAR列的长度是固定的，即使存储的字符串长度小于定义的长度，MYSQL也会在字符串的末尾填充空格以达到指定长度（如果char类型的字符串后面有空格的话，inndb会忽略）

  • VARCHAR：可变长度字符串。VARCHAR列的长度是可变的，存储的字符串长度与实际数据长度相等并且在存储数据时会额外增加1到2节字节（字符超过255则使用2个字节）用于存储字符串的长度信息

  • 理论上说CHAR会比VARCHAR快，因为VARCHAR长度不固定，处理需要多一次运算，但实际上这种运算耗时微乎其微，而固定大小在很多场景下比较浪费空间，除非存储的字符确定是固定大小或者本身就很短，不然业务上推荐使用VARCHAR。

表格对比

特点

char

varchar

存储方式

定长字符串（小于定义的长度用空格）

变长字符串

存储空间

始终占用固定长度

只占用实际需要的存储空间

性能影响

使用占用固定长度，存储时浪费一些空间

只占用需要的存储空间，因此节省存储空间，在某些情况下可能稍微影响性能

适用场景

适合存储固定且短的字符串

适合存储变

长的字符串

MYSQL中varchar和char有什么区别？.png
MYSQL中varchar和char有什么区别？
如何回答：char的长度是固定的，即使存储的字符串长度小于定义的长度，mysql也会在字符串的末尾填充空格，以达到指定长度。varchar可变长度，varchar长度是可变的，存储的字符串长度与实际数据长度相等，并在存储数据时会额外增加1到2字节，用于存储字符串的长度信息
理论上说char会比varchar快，业务varchar长度不固定，处理需要多一次运算，但实际上这种运算耗时微乎其微，而固定大小在很多场景下比较浪费空间，除非存储的字符确定是固定大小或者本身就很短，不然业务上推荐使用varchar

MYSQL是如何实现事务的

  • MYSQL主要通过：锁、Redo Log、Undo Log、MVCC来实现事务。

    • 利用锁机制，使用数据并发修改的控制，满足事务隔离性

    • Redo Log(重做日志)，它会记录事务对数据库的所有修改，当MYSQL发生宕机或崩溃时，通过重放redolog就可以恢复数据，用来满足事务的持久性

    • Undo Log（回滚日志），它会记录事务的反向操作，简单地说就是保存数据的历史版本，用于事务的回滚，使得事务执行失败之后可以恢复之前的样子。实现原子性和隔离性

    • MVCC（多版本并发控制），满足了非锁定读需求，提高了并发度，实现了读已提交和可重复读两种隔离级别，实现了事务的隔离性

  • 事务主要为了实现一致性，具体通过AID，即原子性、隔离性、持久性到达一致性目的

MYSQL是如何实现事务的.png
MYSQL是如何实现事务的？
如何回答：mysql主要通过锁、redolog、undolog、mvcc来实现事务
利用锁机制，使用数据并发修改的控制，满足事务隔离性
redolog:它会记录事务对数据库的所有修改，当mysql发生宕机或崩溃的时候，通过重放redolog就可以恢复数据，用来满足事务的持久性
undolog:它会记录事务的反向操作，就是保存数据的历史版本，用于事务的回滚，使得事务执行失败之后可以恢复之前的样子。实现原子性和隔离性
MVCC:多版本并发控制，满足了非锁定读需求，提高并发度，实现了读已提交和可重复读两种隔离级别，实现了事务的隔离性
事务主要为了实现一致性。具体通过AID，即原子性、隔离性、持久性来到达一致性目的

MYSQL中的MVCC是什么？

  • MVCC（多版本并发控制）是一种并发控制机制，运行多个事务同时读取和写入数据，而无需互相等待，从而提高数据库的并发性能。

    • 在MVCC中，数据库为每个事务创建了一个数据快照。每当数据被修改时，MYSQL不会立即覆盖原有数据，而是生成新版本的记录。每个记录都保留了对应的版本号或时间戳

    • 多版本之间串联起来就形成了一条版本链，这样不同时候启动的事务可以无锁地获得不同版本的数据。此时读写操作不会阻塞

    • 写操作可以继续写，无非就是会创建新的数据版本（但只有在事务提交后，新版本才会对其他事务可见。未提交的事务修改不会影响其他事务的读取），历史版本记录可启动事务读取。

MYSQL中的MVCC是什么？.png
MYSQL中的MVCC是什么？
如何回答:MVCC多版本并发控制是一种并发控制机制，运行多个事务同时读取和写入数据，而无需互相等待，从而提高数据库的并发性能
在MVCC中，数据库为每个事务创建一个数据快照。每当数据被修改时，MYSQL不会覆盖原有数据，而是生成新版本的记录。每个记录都保留了对应的版本号或者时间戳
多版本之间串联起来就形成一条版本链，这样不同时候启动的事务可以无锁的获得不同版本的数据，此时读写操作不会阻塞
写操作可以继续写，无非就行会创建新的数据版本，历史版本记录可供已经启动的事务读取

MYSQL中日志类型有那些？binlog、redolog、undo log的作用和区别是什么？

  • MYSQL是一款流行的关系型数据库，其日志是其关键功能之一。Mysql包括三种类型的日志，分别是binlog、redolog、undolog，它们分别有不同的作用和特点。

    • binlog，是MYSQL中的二进制日志文件，用于记录mysql服务器上的所有更新和修改操作。它可以记录所有的DDL和DML操作，包括对表结构的更改、数据的插入、修改、删除等等。binlog是在事务提交后生成的，因此可以用于恢复数据库

    • reedolog，用于恢复数据，保证数据的一致性和持久性。当mysql发生修改时，redolog会将这些操作记录下来，并写入磁盘。这样，当mysql发生宕机或崩溃时，通过重放redolog就可以恢复数据。

    • undolog，用于回滚操作。当mysql发生数据新增或修改时，undolog会记录这些操作并将其写入磁盘。这样，当mysql需要回滚时，可以通过重放undolog就可以回滚事务。

  • 区别

    • binlog和redolog都是mysql中的二进制文件，但是它们的作用和实现方式有所不同。binlog是mysql记录所有的操作，而redolog则是用于保证数据的一致性和持久性。此外binlog是逻辑日志，redolog是物理日志，binlog记录的是SQL语句，而redolog记录的是数据页的修改，所以binlog可以跨平台使用，而redolog不能。undolog和redolog的区别是，undo log是用于回滚操作，而regolog是用于恢复数据的。

binlog、redolog、undo log的作用和区别是什么.png
MYSQL中日志类型有那些？binlog、redolog、undo log的作用和区别是什么？
如何回答:MYSQL是一款留下的关系型数据库，其日志是其关键功能之一。MYSQL包括三种类型的日志，分别是binlog、redolog、undolog，它们分别有不同的作用和特点。
binlog，是MYSQL中的二进制日志文件，用于记录mysql服务器上所有更新和修改操作。它可以记录所有的DDL和DML操作，包括对表结构的更改、数据插如、修改、删除等待。binlog是在事务提交后生成的，因此可以用于恢复数据库
redolog，用于恢复数据，保证数据的一致性和持久性，当mysql发生修改时候，会记录下来，并写入磁盘。这样当mysql发生宕机或崩溃时，通过重放redolog就可以恢复数据
undolog 用于回滚操作。当mysql发生数据新增或修改时，undolog会记录这些操作并将其写入磁盘。这样当mysql需要回滚时，可以通过重放undolog就可以回滚事务
区别：binlog和redolog都是mysql中的二进制文件，但是它们的作用和实现方式有所不同，binlog是mysql记录所有的操作，而redolog则是用于保证数据的一致性和持久性。此外binlog是逻辑日志，redolog是物理日志，binlog记录的是sql语句，而redolog记录从是数据页的修改，所以binlog可以跨平台使用，而redolog不能。undolog和redolog的区别是，undolog是用于回滚操作，而redolog是用于恢复数据的

MYSQL中的事务隔离级别有那些？

  • 在mysql中事务隔离级别主要有以下四种：

    • 读未提交

      • 这是最低的隔离级别，在该级别下，一个事务可以看到另一个事务尚未提交的数据修改。这可能会导致脏读问题，即读取到其他事务未提交的数据。

    • 读已提交

      • 在这个级别下，一个事务只能看到已经提交的其他事务所做的修改。这可以避免脏读问题，但是可能会引发不可重复读问题，即在同一个事务中，相同的查询可能返回不同的结果

    • 可重复读

      • 在这个级别下，确保在一个事务中的多个查询返回的结果是一致的。这可以避免不可重复读问题，但是可能引发幻读问题，即在同一个事务中，多次查询可能返回不同数量的行

    • 串行化

      • 并发sql事务在SERIALIZABLE隔离级别下的执行被保证是可串行化的。可串行化执行被定义为：并发执行的SQL事务的操作，其效果与这些SQL事务按某种顺序串行执行的效果相同。串行执行是指每个SQL事务在下一个SQL事务开始之前完成其全部操作。

      • 这是最高的隔离了级别，在这个级别下，保证了事务间的操作结果相当于一个按照顺序执行的单线程操作。这可以避免所有的并发但是会大大降低并发性能。

    MYSQL中的事务隔离级别有那些？.png
    MYSQL中的事务隔离级别有那些？
如何回答：在mysql中事务隔离级别主要有四种：读未提交，这是最低的隔离级别，在这个隔离级别下，一个事务可以看到另一个事务尚未提交的数据修改，这可能导致脏读问题，会读取到其他事务未提交的数据
读已提交：在这个级别下，一个事务只能看到已经提交的其他事务所做的修改。这样可以避免脏读问题，但可能会引发不可重复读问题即在同一个事务中相同的查询可能返回不同的结果
可重复读：在这个级别下，确保在一个事务中的多个查询的返回结果是一致的，这可以避免不可重复读的问题，但是可能引发幻读问题，即在同一个事务中，多次查询可能返回不同数据的行
串行化：并发sql事务在serializable隔离级别下的执行被保证了可串行化。可串行化执行被定义为，并发执行的sql事务的操作，其效果与这些sql事务按某种顺序串行执行的效果相同。串行执行是指每个sql事务在下一个sql事务开始前完成其全部操作
这是最高级别的隔离，在这个级别下，保证了事务的操作结果，相当于一个按照顺序执行的单线程操作，这可以避免所有的并发问题，但会大大降低并发性能。

MYSQL默认的事务隔离级别是什么？为什么选择这个级别？

  • MYSQL默认的隔离级别是可重复读，即RR

    • 原因是为了兼容早期binlog的statement格式问题，如果是使用读已提交、读未提交等隔离级别，使用statement格式的binlog会导致主从备数据库数据不一致问题

MYSQL默认的事务隔离级别是什么？为什么选择这个级别？.png
MYSQL默认的事务隔离级别是什么？为什么选择这个级别？
如何回答：mysql默认的隔离级别是可重复读即RR。原因是为了兼容早期的binlog的statement格式问题，如果使用读已提交、读未提交，使用statment格式的binlog会导致主从备数据库数据不一致问题

数据库的脏读、不可重复读和幻读分别是什么？

  • 脏读

    • 一个事务读取另一个事务未提交的数据，如果未提交的事务最终被回滚，那么第一个事务读取的数据就是不一致的

  • 不可重复读

    • 在同一个事务中，读取同一数据两次，但由于其他事务的提交，读取的结果不同。例如，事务A读取了一行数据，事务B修改并提交了这行数据，导致事务A再次读取时得到不同的值

  • 幻读

    • 在同一个事务中，执行相同的查询操作，返回的结果集由于其他事务的插入而发生变化，例如事务A查询符合条件的记录，事务B插入了新记录并提交，导致事务A再次查询时看到不同的记录数量

数据库的脏读、不可重复读和幻读分别是什么？.png
数据库的脏读、不可重复读和幻读分别是什么？
如何回答：
脏读：一个事务读取另一个事务未提交的数据，如果未提交的数据被回滚，那么这个数据是不一致的
不可重复读：在同一个事务中，读取同一数据两次，但由于其他事务的提交，读取的结果不同。例如事务A读取了一行数据，事务B修改并提交这行数据，导致事务A再次读取时得到不同的值
幻读：在用一个事务中，执行相同的查询操作，返回的结果集由于其他事务的插入而发生变化，例如事务A查询符合条件的记录，事务B插入了新记录的提交，导致事务A再次查询的时候看到了不同记录的数量

MYSQL中有那些锁类型？

  • 在mysql中，主要有以下几种锁类型

    • 行级锁(Row Lock)(重点)

      • 仅对特定的行加锁，允许其他事务并发访问不同的行，适用于高并发场景

    • 表级锁(重点)

      • 对整个表加锁，其他事务无法对该表进行任何读写操作，适用于需要保证完整性的小型表

    • 意向锁

      • 一种表级锁，用于表示某个事务对某行数据加载的意图，分为意向共享锁和意向排他锁，主要用于行级锁和表级锁的结合

    • 共享锁(重点)

      • 允许多个事务并发读取同一资源，但不允许修改。只有在释放共享锁后，其他事务才能获得排他锁

    • 排它锁（重点）

      • 只允许一个事务对资源进行读写，其他事务在获得排它之前无法访问该资源

    • 元数据锁

      • 用于保护数据库对象（如表和索引）的元数据，防止在进行DDL操作时其他事务对这些对象进行修改

    • 间隙锁(重点)

      • 针对索引中两个记录之间的间隙加锁，防止其他事务在这个间隙中插入新的记录，以免幻读。间隙锁不锁定具体行，而是锁定行与行之间的空间

    • 临键锁(重点)

      • 是行级锁和间隙锁的结合，锁定具体行和其前面的间隙，确保在一个范围内不会出现幻读。用于支持可重复度的隔离级别

    • 插入意向锁

      • 一种等待间隙锁，用于指示事务打算在某个间隙中插入记录，允许其他事务进行共享锁，但插入时候会阻止其他的排他锁

    • 自增锁

      • 在插入自增列时，加锁以保证自增的唯一性，防止并发插入导致的冲突。通常在插入操作时被使用，以确保生成的自增ID是唯一的

MYSQL中有那些锁类型？.png
MYSQL中有那些锁类型？
有行级锁，仅对特定的行加锁，允许其他事务并发访问不同的行，适用于高并发场景
表级锁，对整个表加锁，其他事务无法对该表进行任何读写操作
共享锁，允许多个事务并发读取用一资源，但不允许修改
排它锁，只允许一个事务对资源进行读写，其他事务在获得排他锁之前无法访问该资源
间隙锁，针对所有中两个记录之间的间隙加锁，防止其他事务在这个间隙中插入新纪录
临键锁，是行级锁和间隙锁的结合，锁定具体行和其它事务的间隙，确保在一个范围内不会出现幻读
还有意向锁
元数据锁等

MYSQL事务的二阶段提交是什么？

  • MYSQL事务的二阶段提交是指在MYSQL中，为了确保redolog和binlog之间的一致性，使用的一种机制。mysql通过二阶段提交来保证在crashrecovery（崩溃恢复）时，不会出现数据丢失或数据不一致的情况。

    • 二阶段提交的两个阶段

      • 准备阶段：在事务提交时，mysql的InnoDB引擎会先写入redolog，并将其状态标记为prepare，表示事务已经准备提交但还未真正完成。此时redolog是预提交状态，还没标记为完成提交

      • 提交阶段：当redolog的状态变为prepare后，mysqlserver会写入binlog。binlog写入成功后，mysql会通知InnoDB，将redolog状态改为commit，完成整个事务的提交过程

MYSQL事务的二阶段提交是什么？.png
MYSQL事务的二阶段提交是什么？
MYSQL事务的二阶段提交是指在MYSQL中为了确保redolog和binlog之间的一致性，使用的一种机制。mysql通过二阶段提交来保证在崩溃恢复时，不会出现数据丢失或数据不一致的情况。一个是准备阶段一个是提交阶段，准备阶段先写入redolog标记为prepare表示事务还未真正完成是预提交状态。提交阶段当redolog状态变为prepare后，mysqlserver会写入binlog。写入binlog成功后，mysql会通知InnoDB讲redolog状态改为commit

MYSQL中如果发生死锁应该如何解决？

  • 自动检测与回滚

    • MYSQL自带死锁检查机制，当检测到死锁时，数据库会自动回滚其中一个事务，以解除死锁。通常会回滚事务中持有最少资源的哪个。

    • 也有锁等待超时的参数，当获取锁的等待时间超过阈值时，就释放锁进行回滚。

  • 手动kill发生死锁的语句

    • 可以通过命令，手动快速找出被阻塞的事务及其线程ID，然后手动kill它，及其释放资源

MYSQL中如果发生死锁应该如何解决？.png
MYSQL中如果发生死锁应该如何解决？
如何回答：有两种，一个是mysql自带检查机制会进行回滚其中一个事务，也要锁等待超时时间，超时了就释放锁进行回滚，一个是手动kill命令，手动找出被阻塞事务和线程手动kill它

MYSQL中如何解决深度分页的问题？

  • 优化方式有三种

    • 利用子查询

      比如使用select * from mianshi where name = 'qiqi' limit 9999999,10这样的一条语句，可以优化程
​
  select * from mianshi where
  name = 'qiqi'
  and id >=
  (select id from mianshi where name='qiqi' order by id
    limit 999999,1)
    order by id limit 10;

      • name 有索引的情况下，这样的查询直接扫描name的二级索引，二级索引的数据量少，且在子查询中能得到id不需要回表。将子查询得到的id再去主键索引查询，速度很快，数据量也小。

      • 如果直接扫描主键索引的话，数据量就比较大，因为主键索引包含全部的数据

      • 当然上面的sql可以改成join也行，本质上是一样的

      select * from mianshi
inner join
(select id from mianshi where name = 'qiqi' order by id limit 9999999,10)
as mianshi on mianshi.id=mianshi.id

    • 记录id

      • 每次分页都返回当前最大id，然后下次查询的时候，带上这个id，就可以利用id>maxid过滤了。这种查询仅适合连续查询的情况，如果跳页的话就不生效了

    • elasticsearch

      • 可以考虑用搜索引擎解决这个问题，不过es也会有深度分页问题，所以如果es不熟，面试就不要这样答，防止面试官问es深度分页如何解决

MYSQL中如何解决深度分页的问题？.png
MYSQL中如何解决深度分页的问题？
如何回答:可以利用子查询，某列有索引的情况下，这样的查询直接扫描某列的二级索引，二级索引的数据量少，且在子查询能得到id不需要回表，将子查询得到id再去主键索引拆线呢速度很快数据量也小，也可以改成join本质上也是一样的
还有记录id，每次分页都返回当前最大id，然后下次查询的时候带上这个id，就可以利用id>最大id过滤，这种查询仅适合连续查询的情况，如果跳页就不生效
使用es搜索引擎也可以，不过es也会有深度分页问题，还没有仔细研究过es

什么是MYSQL的主从同步机制？它是如何实现的？

  • MYSQL的主从同步机制是一种数据复制技术，用于主数据库上的数据同步到一个或多个数据库中

  • 主要通过二进制日志binlog实现数据的复制。主数据库在执行写操作时，会将这些操作记录到binlog中，然后推送给数据库，从数据库重放对应的日志即可完成复制

什么是MYSQL的主从同步机制？它是如何实现的？.png
什么是MYSQL的主从同步机制？它是如何实现的？
如何回答：mysql的主从同步机制是一种数据复制技术，用于主数据库上的数据同步到一个或多个数据库中
主要通过二进制日志binlog实现数据的复制。主数据库在执行写操作时，会将这些操作记录到binlog中，然后推送给数据库，从数据库重放对应的日志即可完成复制

如何处理MYSQL的主从同步延迟？

  • 首先要明确一个点：延迟是必然存在的，无论怎么优化都无法避免延迟的存在，只能减少延迟的时间。

  • 常用的解决方式有以下几种

    • 二次查询

      • 如果从库查不着数据，则再去主库查询一编，由API封装这个逻辑即可，算是一个兜底策略，比较简单。不过等于读的压力又转移到主库身上了，如果有不法分子故意查询必定查不到的查询，这就对主库产生冲击了

    • 强制读写之后立马读操作转移到主库上。

      • 这种属于代码写死了，比如一些写入之后立马查询的操作，就绑定在一起了，写死都走主库比较死板

    • 关键业务读写都走主库，非关键还是读写分离。

      • 比如上面我举例的用户注册这种，可以读写主库，这样就不会有登录不存在的问题，这种访问频次应该也不会很多，所以看业务适当调整此类接口。

    • 使用缓存

      • 主库写入后同步到缓存中，这样查询时可以先查询缓存，避免了延迟问题，不过又引入了缓存数据一致性的问题

    • 除此之外也可以提一提配置问题，例如主库的配置高，从库的配置太低，可以提升从库的配置等。如果面试官对mysql比较熟可能会追问一些遍DBA侧的问题，例行并行复制等。

如何处理MYSQL的主从同步延迟？.png
如何处理MYSQL的主从同步延迟？
如何回答：首页明确一个点，延迟是必然存在的，无论如何优化都无法避免延迟，只能减少延迟时间。
常用解决方式有：二次查询，如果从库查不到数据，则再去主库查询一遍由API封装这个逻辑即可算一个兜底策略。
第二个，强制读写之后立马读操作，这种代码写死了，比如一些写入之后立马查询的操作就绑定一起了，写死比较死板
第三个关键业务读写走主库，非关键还是读写分离：比如注册的时候可以读写主库，这样就不会有登录不纯正问题，这种访问频次不会很多所以看业务适当调整接口
第四个：使用缓存，主库写入后同步到缓存中，这样查询时可以先拆性能缓存避免延迟问题，不过会出现缓存数据不一致问题
第五个，可以提高一下配置
​

一条语句在mysql中的执行过程

1.先通过连接器校验权限

2.利用分析器进行sql语句的词法分析和语法分析，构建解析树

3.使用优化器选择合适的索引和表连接顺序，最终选择一个最佳的执行计划

4.利用执行器，调用引擎层查询数据，返回结果集给客户端

一条语句在mysql中的执行过程.png

Mysql的覆盖索引是什么？

mysql的覆盖索引是指二级索引中包含了查询所需的所有字段，从而使查询可以仅通过访问二级索引而不需要访问实际的表数据 （主键索引）

Mysql的覆盖索引是什么？.png

Mysql的索引下推是什么？

索引下推是一种减少回表查询，提高查询效率的技术。它允许mysql在使用二级索引查找

将部分查询条件下推到存储引擎层过滤，从而减少需要从表中读取到数据行，减少IO

Mysql的索引下推是什么？.png

mysql中长事务可能会导致哪些问题？

1.长时间的锁竞争，阻塞资源：

  • 长时间的锁竞争，阻塞资源：

    • 长事务持有锁的时间较长，容易导致其他事务在尝试获取相同锁时发生阻塞，从而增加系统的等待时间喝降低并发性能。

    • 业务现场也会因为长时间的数据库请求等待而阻塞，部分业务的阻塞可能还会影响到别的服务，导致产生雪崩，最终使得服务全面崩盘，导致非常严重的线上事故

  • 死锁风险

    • 长事务更容易产生死锁，因为多个事务可能在互相等待对方释放锁，导致系统无法继续执行，

  • 主从延迟

    • 主库需要长时间，然后传输从库，从库又要重放好久，期间可能有很长一段时间数据是不同步的

  • 回滚导致时间浪费

    • 如果长事务执行很长一段时间，中间突发状况导致抛错，使得事务回滚了，之前做的执行都浪费了

mysql中长事务可能会导致哪些问题？.png

mysql中的乐观锁和悲观锁是什么？

悲观锁

  • 假设会发生冲突，因此在操作数据之前就对数据加锁，确保其他事务无法访问该数据。常见于对数据一致性要求较高的场景。

  • 实现方式：使用行级锁或表级锁，例如可以使用select ...for update或lock in share mode语句来加锁

乐观锁

  • 假设不会发生冲突，因此在操作数据时不加锁，而是在更新数据时进行版本控制或校验。如果发现数据被其他事务修改，则会拒绝当前事务的修改，需重新尝试。

  • 实现方式：通常通过版本号或时间戳来实现，每次更新时检查版本号或时间戳是否一致

mysql中的乐观锁和悲观锁是什么？.png

mysql中的数据排序是怎么实现的？

  • 排序过程中，如果排序字段命中索引，则利用索引排序

  • 反知，使用文件排序

  • 文件排序中，如果数据量少则在内存中排序，具体是使用单路排序或者双路排序

  • 如果数据大则利用磁盘文件进行外部排序，一般使用归并排序

  • 根据explain大结果来看，mysql可以分为索引排序和filesort

mysql中的数据排序是怎么实现的？.png

mysql的change buffer 是什么？它有什么作用？

  • change buffer是mysql innodb存储引擎的一个机制，用于暂存对二级索引插入和更新操作的变更，而不立即执行这些操作，随后，当lnnodb进行核实的条件时，会将这些变更写入到二级索引中

  • 作用

    • 提高写入性能：通过将二级索引的变更暂存，可以减少对磁盘的频繁写入，提升插入和更新操作的性能

    • 批量处理：change buffer 可以在后续的操作中批量处理这些变更，减少了随机写入的开销

mysql的change buffer 是什么？它有什么作用？.png

MySQL的IN最多能放多少个值?

  • 在mysql中的IN子句官方没有规定数量上限，真正限制来自3点：max_Pallowed_packet的SQL长度限制、内存解析开销、以及索引实效的性能风险。

    • 在实践中，会把IN列表的安全阈值控制在500个以内；超过500个就用分批次查询；超过1万个则用临时表+JOIN方案，这不是因为触发了配置而是为了避免索引失效和性能下降。

    • 底层逻辑上，IN子句有索引时走的范围扫描，无索引时转哈希表查找，并非排序+二分查找；

    • 优化的核心逻辑是利用索引和避免大列表解析开销。

      MySQL的IN最多.png

动物装饰