Minimax-秋招正式批-面经(SQL相关)

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID作为主键
  • 主键的值是顺序的，所以InnoDB会将每一条记录都存储在上一条记录的后面。
  • 当达到页的最大填充因子（InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改）下一条记录就会被写入到新的页中。
  • 一旦数据按照这种顺序的方式被加载，主键也就会近乎与被顺序的记录填满（二级索引页可能是不一样的）
• 维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。因此，对于InnoDB表，我们一般定义主键不可更新；另外，建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片
• 如果主键比较大的时候，那辅助索引将会变得更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用更多的物理空间

2.  mysql为什么要求id递增？

1. 索引性能：聚簇索引的数据的物理存放顺序与索引顺序是一致的，只要索引是相邻的，那么对应的数据一定也是相邻的存储在物理磁盘上的。如果主键不是自增ID，那么当插入数据时会进行频繁的页分裂或移动数据，当然也有一些措施来减少这些操作，但是却无法彻底避免。但是，如果是自增的，那就简单了，它只需要一页一页的写，索引结构相对紧凑，磁盘碎片少，效率也高。
2. 唯一性保证：自增主键保证了每一行的唯一性。每次插入新行时，数据库会自动为主键生成一个唯一的值，无需开发人员手动指定。这消除了开发人员处理唯一性约束的复杂性和潜在的错误。此外，自增主键还可以简化表之间的关联关系，作为外键的引用。
3. 索引大小和查询性能：自增主键的值通常较小，只需要占用很少的存储空间。相比于使用其他类型的列作为主键，自增主键可以减小索引的大小，提高查询性能。较小的索引大小也可以减少内存的消耗，更好地适应于内存缓存。
4. 数据可维护性：自增主键提供了一种简单且可维护的方式来标识和引用表中的每一行。开发人员可以轻松地使用主键进行数据的更新、删除和查询操作，而不需要关心主键值的生成和管理。

3. mysql回表是什么？

Mysql回表指的是在InnoDB存储引擎下，二级索引查询到的索引列，如果需要查找所有列的数据，则需要到主键索引里面去取出数据。这个过程就称为回表。因为行的数据都是存在主键B+tree的叶子节点里面，二级索引的B+树叶子节点都是存放的(索引列,主键)。

例如：有一张用户表 id(主键), username (二级索引),age

id是主键索引 username是二级索引，当我们通过二级索引查询返回记录时，例如：

select * from t where username = "bobo";

① 通过二级索引找到 username为bobo的主键id

② 在通过主键id去主键索引B+tree找到行记录

4. MySQL为什么默认引擎是InnoDB？

1. 支持事务：InnoDB 是一个支持事务的存储引擎。事务是一组数据库操作的原子性执行，可以保证操作的一致性和完整性。
2. 并发控制：InnoDB 支持行级锁定，在高并发环境下可以最大程度地减少锁冲突，提高并发性能。相比之下，MySQL 的另一个存储引擎 MyISAM 只支持表级锁定，并发性能较低。
3. 外键约束：InnoDB 支持外键约束，可以保证数据的完整性。外键用于建立表与表之间的连接，通过外键约束可以实现数据之间的关联和参照完整性。
4. 崩溃恢复：InnoDB 具有自动崩溃恢复的能力。即使在发生意外故障或系统崩溃时，InnoDB 引擎也能够自动进行崩溃恢复，保障数据的一致性。
5. 支持热备份：InnoDB 支持在线热备份，可以在不停止数据库服务的情况下进行备份操作。这对于需要实时运行且对数据可用性要求高的应用程序非常重要。

 5. mysql为什么不用uuid做主键？

UUID：UUID 是指Universally Unique Identifier（通用唯一识别码），UUID 的目的是让分布式系统中的所有元素都能有唯一的识别信息。如此一来，每个人都可以创建不与其他人冲突的 UUID，就不需考虑数据库创建时的名称重复问题。

1. 索引效率：

• 无序性：UUID是随机的，没有规律可言，因此InnoDB无法总是将新行插入到索引的最后。这导致InnoDB需要为新行寻找新的合适位置并分配新的空间，增加了额外的操作。
• 页分裂：由于写入是乱序的，InnoDB需要频繁进行页分裂操作以为新行分配空间。页分裂不仅移动了大量的数据，还降低了索引的紧凑性，增加了碎片的产生。
• 随机IO：由于UUID的随机性，写入的目标页可能尚未加载到缓存中，或者已经被刷新到磁盘并从缓存中移除。这导致InnoDB在插入之前不得不先从磁盘读取目标页到内存中，增加了随机IO的次数。

2. 性能影响：

• 插入效率：由于上述的随机IO和页分裂问题，UUID作为主键的表在插入操作上的性能通常低于自增ID作为主键的表。
• 优化需求：在将随机值(如UUID)载入到聚簇索引后，有时需要进行OPTIMIZE TABLE操作以重建表并优化页的填充。这将消耗额外的时间和资源。

自增id：自增的主键的值是顺序的,所以Innodb把每一条记录都存储在一条记录的后面。当达到页面的最大填充因子时候(innodb默认的最大填充因子是页大小的15/16,会留出1/16的空间留作以后修改)

1. 索引效率：

• 顺序性：自增ID是顺序的，InnoDB能够高效地将新记录插入到索引的末尾，减少了页分裂和碎片的产生。这意味着数据在物理存储上也是连续的，从而提高了查询和插入的效率。
• 填充率：由于数据是按照顺序插入的，InnoDB能够近乎于顺序地填满每个索引页，提高了页面的最大填充率，减少了空间的浪费。
• 定位与寻址：新插入的行一定会在原有的最大数据行下一行，MySQL定位和寻址很快，不需要为计算新行的位置而做出额外的消耗。

2. 性能影响：

• 锁争用：在高并发的负载下，InnoDB在按主键进行插入时可能会造成锁争用，因为所有的插入都发生在主键的上界，这可能导致间隙锁竞争。
• 泄露信息：自增ID容易被外部爬取，从而暴露业务增长信息，可能对企业的经营情况造成潜在风险。

总结：

• 自增ID：适用于大多数需要高效查询和插入操作的场景。它简单易用，索引效率高，但需要注意在高并发下的锁争用问题和信息泄露风险。
• UUID：适用于需要全局唯一标识符的场景，特别是在分布式系统中。然而，其随机性导致了较低的索引效率和较高的插入成本。

9. 为什么 InnoDB 底层使用 B+ 树

索引

在 MySQL 中键以及索引表示的都是一种快速操作表的数据结构（键就是索引，把所有的主键放置到B+树这种数据结构中形成的快速操作数据库表的数据结构叫做索引），这里的键可以理解为就是数据库表中的主键，通过主键建立了索引，主键的数据在底层使用 B+ 树这种数据结构进行存储，可以通过主键访问到具体的数据；

哈希索引：哈希表按照 key value 的形式对于等值数据的查找是一个非常好的查询方式，但是对于区间查找，哈希表需要进行全表的遍历，这个查询的效率是比较低下的；

于此同时，使用基于拉链法的哈希索引值在遇到大量的哈希碰撞的时候，查询数据的效率也是十分的慢的；

为什么InnoDB 选择使用 B+ 树而不是其他的各种各样的树进行数据的存储

在二叉树中的每一个节点中只能存储一个元素，每个结点只能存放两个子节点，在数据量比较大的时候，查找一个数据在二叉树的各种结构中，需要查询的二叉树的层数是比较多的，这样层数越多，对于查询来讲，效率就会越低，使用 B 树，B+ 树，可以有效的降低树的高度，提升查询的效率；

B 树与AVL树和红黑树相比每个节点包含的关键字增多（每个结点包含的数据可以存在多个）了，从而减小了树的深度，可以相对减少磁盘IO的次数。但是B树的叶子节点没有通过链表链接起来，没有B+树更比较适合范围查找，另一个是B+树把数据指针都放在叶子节点上，在同等树高下，存储的数据量更多。（在 InnoDB 中最小的读写单位默认是 16 k（可以设置）,也就是一个 B+ 树的结点或者 B 树结点的大小是 16 k, 如果这个结点是 B 树，里面的 value 占据了一定的空间，使得索引的空间就变少了，使得 B 树变高了，使得 IO 操作变多了，效率就下降了；）

B+树的优点

1. B+树的层级更少：非叶子结点中存放的元素不存放数据，所以每一层可以容纳更多元素，比B-树更加“矮胖”，也就是磁盘中的每一页可以存放更多元素。这样在查找时，磁盘IO的次数也会减少
2. B+树查询速度更稳定：每次查找都必须匹配到叶子节点，因此每一次查找都是稳定的。B-树由于中间节点也携带数据，因此只需要匹配到要查找的元素即可，最好的情况是在根节点就结束查找，最坏的情况是在叶子节点结束查找，查找性能不稳定
3. B+树天然具备排序功能：B+树所有的叶子节点数据构成了一个有序链表，在范围查询数据时候更方便，数据紧密性很高，缓存的命中率也会比B-树高。
4. B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描
5. 因为在 B+树的底层维护了一个链表，所以进行区间查询的效率也是比较高的； B 树在进行区间查询的时候，需要使用中序遍历，效率是比 B+ 树要低一点的；

 10. 三个log：binlog，redolog，undolog的区别

MySQL日志主要包括七种：

1. 重做日志 redo log
2. 回滚日志 undo log
3. 归档日志 binlog
4. 错误日志 errorlog
5. 慢查询日志 slow query log
6. 一般查询日志 general log
7. 中继日志 relay log

binlog 是记录所有数据库表结构变更以及表结构修改的二进制日志，不会记录SELECT和SHOW这类操作，主要用于数据恢复和主从同步。写入时机是事务提交的时候 ( 如果一个事务回滚了，就不会出现在binlog中 ) 。

• 主从同步：MySQL在公司使用的时候往往都是一主多从结构的，从服务器需要与主服务器的数据保持一致，这就是通过binlog来实现的
• 数据恢复：数据库的数据被干掉了，我们可以通过binlog来对数据进行恢复。

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。 

在MySQL中，如果每次的更新操作都需要写进binlog记录，磁盘要找到对应的那条记录，更新后再保存整页的数据，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL的设计者使用了redo log来保存最近的数据变更记录，这样的设计被称为WAL技术。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。具体来说，当有一条update语句要执行的时候，InnoDB 引擎就会先把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面。

redo log的存在为了：当我们修改的时候，写完内存了，但数据还没真正写到磁盘的时候。此时我们的数据库挂了，我们可以根据redo log来对数据进行恢复。因为redo log是顺序IO，所以写入的速度很快，并且redo log记载的是物理变化（xxxx页做了xxx修改），文件的体积很小，恢复速度很快。

binlog和redo log都是用来“恢复”的，他俩究竟有什么区别？

开始写入->开事务->写redolog->提交事务时写binlog

1. 目的：

• binlog：binlog主要用于恢复数据修改操作，记录了对数据库进行的所有修改语句，如INSERT、UPDATE、DELETE等。它是用于数据备份、主从复制以及高可用性方案的关键组成部分。
• redo log：redo log用于数据库事务的持久性，记录了对数据库的逻辑变化。它主要用于确保在宕机或崩溃时，可以通过重做日志来重新执行已提交的事务，保证数据的一致性。

2. 数据内容：

• binlog：binlog以文本形式记录SQL语句或SQL语句的逻辑表示。它包含了对数据库的增删改操作的详细信息，可以用来重放这些操作。
• redo log：redo log是一个循环的、预分配的固定长度的二进制文件，记录了对数据库页的物理修改操作。它包含了物理上的页地址、修改前后的数据值等，用于重做被修改的页。

3. 写入时机：

• binlog：binlog是在事务提交之后才会被写入磁盘，它记录了已经完成的事务。
• redo log：redo log是在事务执行过程中被写入磁盘，这样可以确保事务在提交之前，对数据库的修改已经被记录下来。

4. 大小和持久性：

• binlog：binlog的大小是由系统参数binlog_max_size控制的，它可以被删除或轮换。它是非持久性的，即在数据库重启后会被清空，需要依靠备份来进行数据恢复。
• redo log：redo log的大小是固定的，当redo log被写满时，会被清空并重用空间。它是持久性的，即在数据库重启后会被保留，可以确保数据的一致性。

总体而言，binlog主要用于记录已完成的操作，以实现数据备份、复制和恢复等功能；而redo log主要用于事务的持久性，以确保数据的一致性。它们在日志内容、写入时机和持久性等方面有所不同，但都是MySQL中重要的日志机制，为数据库的可靠性和可恢复性提供了支持。

什么是undo log

undo log有什么用：undo log的两个主要作用——回滚和多版本控制（MVCC）

在数据修改的时候，不仅记录了redo log，还记录undo log，如果因为某些原因导致事务失败或回滚了，可以用undo log进行回滚

undo log主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。

因为undo log存储着修改之前的数据，相当于一个前版本，MVCC实现的是读写不阻塞，读的时候只要返回前一个版本的数据就行了。

11. 联合索引(a, b)，where a>1 and b=2，这种情况下会命中哪些索引 ？

进行范围查找是会中断联合索引！！

例如a=1 and b=2此时a是精确查找，a,b都会触发索引
但当a>1 and b=2时，a进行了范围查找，虽然a触发了索引，但此时a是一个范围，在这个范围内b是无序的，所以b未能触发索引，所以说范围查找会中断索引
同理，a=1,b>2,c=3时，a,b都会匹配索引，但在a=1,b>2这个范围内c是无序的