MySQL面试题-重难点
mysql中有哪些锁？举出所有例子，各个锁的作用是什么？区别是什么？
共享锁：也叫读锁，简称S锁，在事务要读取一条记录时，先获取该记录的S锁，别的事务也可以继续获取该记录的S锁，但不能获取X锁，会阻塞。事务提交后锁会释放。（S S 兼容；S X不兼容；X X不兼容）
独占锁：也叫写锁，简称X锁，在事务要改动一条记录时，先获取该记录的X锁，别的事务不能再获取该记录的S锁和X锁，会阻塞，事务提交后锁释放。
全局锁：加了全局锁后整个数据库都处于只读的状态，进行增删改操作及表结构变更操作都会被阻塞，当会话断开后全局锁自动释放。主要用于全库逻辑备份。
表级锁：分为共享表锁和独占表锁。
本线程对一个表格加了共享表锁，允许多个事务并发的读取表，但会阻塞任何写操作包括本线程，直到事务结束后释放锁。
本线程对一个表加了独占表锁，当前事务能对表进行读写操作，其他事务不能对该表进行任何操作，保证当前事务对该表的独占访问权限，事务结束后释放。
行级锁：对表中的一行或多行记录进行加锁控制。InnoDB 支持行级锁，而 MyISAM不支持行级锁。分为三类，记录锁、间隙锁和临键锁。
-
- Record Lock：记录锁，锁住一条记录。一个事务对一条记录加S锁后，其他事务可以继续加S锁，但不能加X锁。一个事务对一条记录加X锁后，其他事务不能再对记录加S锁和X锁。
- Gap Lock：间隙锁，只存在RR隔离级别下，为了解决幻读问题。间隙锁之间是兼容的，两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系。
- Next-Key Lock：临键锁，Record Lock + Gap Lock 的组合，锁定数据本身，并锁住数据前面的间隙。因为包含记录锁，需要考虑S锁和X锁之间的冲突关系。
意向锁：分为意向共享锁（IS）和意向独占锁（IX），表示事务想要在行上设置共享锁或独占锁。是一种表锁，InnoDB自动加的，不需要用户干预。
乐观锁：假设并发操作时不会发生冲突，只在提交事务时检查数据是否被其他事务修改过，不会产生任何锁和死锁。适合多读少写的场景
悲观锁：假设数据在并发操作时总是会被其他事务修改，会对数据立即加锁。行锁、表锁、读写锁、序列化实现的锁都是悲观锁。
什么是排他锁、共享锁、意向锁？解析一下概念和使用场景？
排他锁：适用于写操作或更新操作，确保事务修改的时候其他事务不能同时修改相同数据，防止出现数据不一致或者丢失更新的问题。
共享锁：适用于读操作，允许多个事务并发读取数据，提高系统读取性能。
意向锁：尝试在具体行加锁之前，事务先需要在表级别获取意向锁，获取到意向锁后事务可以对表中具体行加锁。 使得数据库能够清楚地了解一个事务希望对表中哪些行加锁
mysql中行锁是如何实现的？行锁锁的东西是什么？索引还是具体数据？
行锁是通过索引来锁定行，InnoDB执行一条需要加行锁的SQL语句时，先找到对应的索引，然后对该索引加锁，因为索引指向的是实际的数据，所以对应的数据也被锁定。如果该表没有索引或者查询时没使用索引，行锁会退化成表锁。
mysql一条语句是如何加锁的？ 如何判断加的是行锁还是表锁？
首先对SQL语句进行解析生成执行计划。根据语句的类型及具体的操作确定需要加锁的类型（读/写）。根据涉及到的索引来确定需要加锁的行，如果没有使用索引就退化成表锁。语句执行过程中，根据锁的类型和范围对相应的索引加锁。
可以通过Explain查看执行计划，使用了索引通常使用的就是行锁，没有使用索引就可能会退化成表锁。
update没加索引会锁全表吗？
当执行 UPDATE 操作时，InnoDB需要锁定所有被更新的行，确保数据的一致性和事务的隔离性。如果没有使用索引限制要更新的行，InnoDB可能会执行全表扫描，从而锁定整个表中的所有行。
如何判断sql加的是行锁还是间隙锁还是临键锁？
通过 SHOW ENGINE INNODB STATUS 命令查看当前详细状态信息，在输出结果中查找 TRANSACTIONS部分，能够看到当前事务的锁信息。
- lock_mode X locks rec but not gap：表示行锁（Record Lock）。
- lock_mode X locks gap before rec：表示间隙锁（Gap Lock）。
- lock_mode X locks rec：表示临键锁（Next-Key Lock）。
还可以通过结合隔离级别和查询模式来分析当前的锁状态，RC级别下只有行锁，RR级别下能够使用行锁、间隙锁和临键锁。
当查询条件使用的是主键索引，并做了等值查询加的就是行锁。
当查询条件使用的是普通索引，并做了范围查询，一般使用的是间隙锁。
当查询条件使用的是普通索引，做了精准查询，一般加的就是临键锁。
-- id字段是主键 SELECT * FROM employees WHERE id = 1 FOR UPDATE; -- age字段是普通索引 SELECT * FROM employees WHERE age > 30 FOR UPDATE; -- age字段是普通索引 SELECT * FROM employees WHERE age = 30 FOR UPDATE;
都有哪些日志，分别有什么作用？
Undo Log：回滚日志，当执行一条写入型SQL时都会记录在Undo Log中，在内部会形成一个单向链表，称为Undo版本链。有一个隐藏字段roll_ptr回滚指针，日志中记录的旧数据不止一条，需要回滚时，回滚指针指向对应的记录版本，然后覆盖掉改动过的数据。
Redo Log：重做日志，当数据库发生宕机后通过Redo Log来恢复数据。MySQL在运行过程中大量操作都在内存中进行，然后由后台线程刷写到磁盘中，因为是基于内存一旦发生宕机、重启内存中的数据就会消失，Redo Log就是为了解决这个问题。在向内存写数据时，会先写到日志，后续数据没有写到磁盘中，或者发生宕机，就能通过Redo Log来恢复数据。
Bin Log：变更日志，记录所有对数据库表结构变更和表数据修改的操作（增删改），Bin Log的本地日志文件采用追加写的模式，一个文件写满就创建新的文件，Bin Log会记录SQL的语句。常用于数据灾备，误删了数据库可以直接去本地找Bin Log文件，将删库的记录移除后，利用MySQLBinLog工具导出SQL文件，然后执行该文件来恢复删除前的数据。
Erro Log：线上MySQL由于内在因素（断电、硬件损坏...）导致崩溃时，辅助线上排错的日志。记录报错信息，包括启停时间、报错的诊断信息也包括错误、警告、提示等多个级别的日志详情。
Slow Log：当一条SQL执行的时间超过规定阈值后就会被记录在慢查询日志中，线上出现相应缓慢的问题时可以通过查看慢日志来定位问题，然后进行优化。
binlog和redolog的区别？
- ①Redo-log是InnoDB专享的，Bin-log是所有引擎通用的。
- ②Redo-log是用两个文件（写到了哪，哪些已经写完了）循环写，写满之后从头开始覆盖，而Bin-log是不断创建新文件追加写，一个文件写满了就创建新文件写。
- ③Redo-log中记录的都是变更后的数据，而Bin-log会记录变更SQL语句。
- ④Redo-log主要实现故障情况下的数据恢复， Binlog 记录所有更改数据的操作，可以用于将数据库恢复到某个特定的时间点。
- ⑤ Redo Log 在事务提交前需要写入到磁盘（写到日志文件中，但此时只是预提交状态）。事务在提交前所有的操作都会记录在 Binlog 中，一旦事务提交，Binlog 会将记录持久化到磁盘。
为什么mysql数据存储在磁盘，还需要用日志多写一份数据？意义在哪里？
为了保证数据库的持久性和一致性，并且当系统发生故障时可以通过日志进行数据恢复。
持久性：事务一旦提交就要永久保存在系统中，即使系统发生崩溃。通过Redo Log能恢复已提交的事务，保证数据的持久性。
一致性：所有的修改操作先记录到日志中，日志写入成功后事务才提交，即使系统崩溃也能通过日志恢复事务，确保数据库的一致性。
系统崩溃数据恢复：通过Redo Log能够重做所有已提交的事务，让数据库恢复到最新的状态。Undo Log用于事务回滚和MVCC，事务执行过程中的所有更新会首先记录在Undo Log中。
Bin Log：记录所有对数据库的修改操作，用于数据恢复和主从复制。
mysql底层分别是怎么实现ACID的？(结合日志体系+sql执行流程去说)
原子性：事务中的操作要么全都完成，要么全都不完成。事务中的写入型语句都会记录在Undo Log中，内部形成一个链表，事务需要回滚时，回滚指针指向对应的记录版本，然后覆盖掉改动过的数据，从而保证原子性。
一致性：通过原子性+持久性+隔离性来实现
隔离性：一个事务的操作在提交之前对其他事务是不可见的，通过锁机制和MVCC机制来实现。
持久性：事务提交后数据就永久保存在系统中，系统崩溃数据也不丢失。主要通过Redo Log和Bin Log实现， 事务提交时，MySQL 会将更改记录到 Redo Log 并立即刷写到磁盘。Bin Log记录所有对数据库的修改操作，确保在崩溃恢复后可以恢复这些操作。
一条查询SQL的执行流程？
- 客户端通过MySQL连接器将查询语句发送到MySQL服务器。
- MySQL服务器的连接管理器接收客户端的查询，为客户端连接分配线程来处理查询。
- 进行词法和语法分析，词法分析识别出关键字。语法分析判断语法是否正确，然后构建出语法树。
- 在预处理器中检查语句中的表或字段是否存在。
- 查询优化器接收预处理后的查询并生成执行计划，找到最优的执行方案。
- 执行计划生成，包括使用那个索引，表的连接顺序、排序.....
- 执行查询，执行引擎根据执行计划进行实际查询操作。
- 执行引擎将查询结果返回给客户端，通过连接器传递结果。
一条更新sql的执行流程？
- 客户端发送更新查询到MySQL服务器。
- 连接管理器接收查询并分配线程处理。
- 解析器将SQL语句解析成内部数据，进行语法分析和词法分析。
- 在预处理器中检查语句中的表或字段是否存在。
- 选择最优的执行路径，生成执行计划，选择合适的索引
- 执行更新操作，生成Undo Log，记录更新前的数据，以便需要回滚，生成Redo Log记录更新后的数据。
- 提交事务，将Redo Log刷新到磁盘，更新后的数据写进内存缓冲池，提交后写进磁盘。
- 将结果返回给客户端。
mysql的 server层和引擎层有什么区别？ 为什么要分这两层？
server层用来对 sql语句进行语法分析，优化选择器选择执行计划。引擎层负责真正的执行 sql语句的操作。分层目的就是为了解耦，方便扩展，像引擎层就有innodb引擎，myisam等引擎，后续要添加其他引擎时方便扩展。并且各种引擎都共用 server 层的语法分析器，优化选择器等，减少代码冗余。
死锁问题 行锁的理解？
死锁就是两个或多个线程并发访问数据库，造成两个或多个线程相互等待对方的资源，而造成的相互等待问题。innodb 存储引擎支持行锁，是一种粒度比较小的锁。有行排他锁，和行共亨锁。因为粒度比较小，所以死锁的概率也是比较大。此外行锁也会额外的占用系统资源。
事务两阶段提交的概念能理解吗？
第一阶段，先写Redo Log标记为准备提交状态，表示Bin Log还没落盘，然后写Bin Log。
第二阶段，再写Redo Log标记为提交状态，此时Bin Log已经落盘。
为什么需要两阶段提交
确保Redo Log和Bin Log中数据都是一致的。系统宕机后恢复数据时，Redo Log中的事务是提交状态表示Bin Log一定落盘了能直接恢复，如果Redo Log中事务状态是准备状态，就要判断Bin Log中是否存在这个事务，存在就恢复，不存在就回滚来保证数据一致性。
mysql会有缓存机制吗 buffer pool和change buffer作用是什么？
MySQL由多种缓存机制，包括 Buffer Pool 和 Change Buffer。
Buffer Pool：MySQL中的内存缓存之一，用于存储数据页。作用包括，将热点数据存储在内存中来减少磁盘IO，缓存数据页和索引，来提高查询速度。
Change Buffer：MySQL中的一个附加缓存，用作优化写操作。作用包括，在执行写入类型操作时，InnoDB不会立即将数据写入数据页，先将变更记录在Change Buffer中，通过延迟数据页更新将多个变更操作合并成更少的磁盘写入操作，来降低IO操作，然后在后台线程异步刷新到对应的数据页。
为什么要有buffer pool？如何管理buffer pool
buffer pool是一块非常重要的内存区域，用来缓存数据库表和索引的磁盘数据页。读数据时先从缓冲池查，存在就直接返回，写数据先写缓冲池的数据页，然后后台异步写入磁盘，能够减少磁盘IO。内存访问速度比磁盘快能提升读写性能。
可以设置缓冲池大小一般是物理内存的70%~80%，根据硬件来设置每秒可以处理的IO操作数，可先通过性能测试工具进行负载测试。
从数据页的角度看B+树
B+树的每个节点都会尽可能填满一个数据页，能减少每次磁盘IO操作时需要读取的数据页，一个节点超过数据页的容量会分裂，因删除操作造成空间节点过大时，会触发合并操作。
count（*）和count（1）有何区别？那个性能更高？
count（*）和count（1）：都是统计结果集中的行数。
性能：count（1）会快一点，因为不需要解析或者检查具体的列值，只需要计算每一行的计数，但几乎没有什么差异， 建议使用 COUNT(*)，更加清晰和通用。
