MySQL篇

一条 SQL 查询语句是如何执行的

1. 连接器:连接器负责跟客户端建立连接、获取权限、维持和管理连接。
2. 查询缓存: MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value 对的形式，被直接缓存在内存中。
3. 分析器:你输入的是由多个字符串和空格组成的一条SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。
4. 优化器:优化器是在表里面有多个索引的时候，决定使用哪个索引; 或者在一个语句有多表关联(join )的时候，决定各个表的连接顺序。
5. 执行器: MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

事务的四大特性有哪些？

事务的四大特性通常被称为 ACID 特性

1. 原子性：确保事务的所有操作要么全部执行成功，要么全部失败回滚，不存在部分成功的情况。
2. 一致性：事务在执行前后，数据库从一个一致性状态转变到另一个一致性状态。
3. 隔离性：多个事务并发执行时，每个事务都应该被隔离开来，一个事务的执行不应该影响其他事务的执行。
4. 持久性：一旦事务被提交，它对数据库的改变就是永久性的，即使在系统故障或崩溃后也能够保持。

数据库的事务隔离级别有哪些？

1. 读未提交（Read Uncommitted）：
   • 允许一个事务读取另一个事务尚未提交的数据修改。
   • 最低的隔离级别，存在脏读、不可重复读和幻读的问题。
2. 读已提交（Read Committed）：
   • 一个事务只能读取已经提交的数据。其他事务的修改在该事务提交之后才可见。
   • 解决了脏读问题，但仍可能出现不可重复读和幻读。
3. 可重复读（Repeatable Read）：
   • 事务执行期间，多次读取同一数据会得到相同的结果，即在事务开始和结束之间，其他事务对数据的修改不可见。
   • 解决了不可重复读问题，但仍可能出现幻读。
4. 序列化（Serializable）：
   • 最高的隔离级别，确保事务之间的并发执行效果与串行执行的效果相同，即不会出现脏读、不可重复读和幻读。

MySQL的执行引擎有哪些？

MySQL的执行引擎主要负责查询的执行和数据的存储, 其执行引擎主要有MyISAM、InnoDB、Memory 等。

• InnoDB引擎提供了对事务ACID的支持，还提供了行级锁和外键的约束，是目前MySQL的默认存储引擎，适用于需要事务和高并发的应用。

• MyISAM引擎是早期的默认存储引擎，支持全文索引，但是不支持事务，也不支持行级锁和外键约束，适用于快速读取且数据量不大的场景。

• Memory就是将数据放在内存中，访问速度快，但数据在数据库服务器重启后会丢失。

MySQL为什么使用B+树来作索引【重点】

B+树是一个B树的变种，提供了高效的数据检索、插入、删除和范围查询性能。

• 单点查询：B 树进行单个索引查询时，时间代价为O(logn)。从平均时间代价来看，会比 B+ 树稍快一些。但是 B 树的查询波动会比较大，因为每个节点既存索引又存记录，所以有时候访问到了非叶子节点就可以找到索引，而有时需要访问到叶子节点才能找到索引。B+树的非叶子节点不存放实际的记录数据，仅存放索引，所以数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

• 插入和删除效率：B+ 树有大量的冗余节点，删除一个节点的时候，可以直接从叶子节点中删除，甚至可以不动非叶子节点，删除非常快。B+ 树的插入也是一样，有冗余节点，插入可能存在节点的分裂（如果节点饱和），但是最多只涉及树的一条路径。B 树没有冗余节点，删除节点的时候非常复杂，可能涉及复杂的树的变形。

• 范围查询：B+ 树所有叶子节点间有一个链表进行连接，而 B 树没有将所有叶子节点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。存在大量范围检索的场景，适合使用 B+树，比如数据库。而对于大量的单个索引查询的场景，可以考虑 B 树，比如nosql的MongoDB。

说一下索引失效的场景？

索引失效意味着查询操作不能有效利用索引进行数据检索，从而导致性能下降，下面一些场景会发生索引失效。

1. 使用OR条件：当使用OR连接多个条件，并且每个条件用到不同的索引列时，索引可能不会被使用。
2. 使用非等值查询：当使用!=或<>操作符时，索引可能不会被使用，特别是当非等值条件在WHERE子句的开始部分时。
3. 对列进行类型转换： 如果在查询中对列进行类型转换，例如将字符列转换为数字或日期，索引可能会失效。
4. 使用LIKE语句：以通配符%开头的LIKE查询会导致索引失效。
5. 函数或表达式：在列上使用函数或表达式作为查询条件，通常会导致索引失效。
6. 表连接中的列类型不匹配： 如果在连接操作中涉及的两个表的列类型不匹配，索引可能会失效。例如，一个表的列是整数，另一个表的列是字符，连接时可能会导致索引失效。

undo log、redo log、binlog 有什么用？

• undo log是Innodb存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC。
• redo log是物理日志，记录了某个数据页做了什么修改，每当执行一个事务就会产生一条或者多条物理日志。
• binlog (归档日志）是Server 层生成的日志，主要用于数据备份和主从复制。

什么是慢查询？原因是什么？可以怎么优化？

数据库查询的执行时间超过指定的超时时间时，就被称为慢查询。

原因：

• 查询语句比较复杂：查询涉及多个表，包含复杂的连接和子查询，可能导致执行时间较长。
• 查询数据量大：当查询的数据量庞大时，即使查询本身并不复杂，也可能导致较长的执行时间。
• 缺少索引：如果查询的表没有合适的索引，需要遍历整张表才能找到结果，查询速度较慢。
• 数据库设计不合理：数据库表设计庞大，查询时可能需要较多时间。
• 并发冲突：当多个查询同时访问相同的资源时，可能发生并发冲突，导致查询变慢。
• 硬件资源不足：如果MySQL服务器上同时运行了太多的查询，会导致服务器负载过高，从而导致查询变慢

优化：

1. 运行语句，找到慢查询的sql
2. 查询区分度最高的字段
3. explain：显示mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引、写出更优化的查询语句
4. order by limit形式的sql语句，让排序的表优先查
5. 考虑建立索引原则

MySQL 有哪些锁

1. 全局锁：

   • 全局锁是对整个数据库实例加锁。最典型的应用场景是进行全库的逻辑备份。

   • 通过执行 FLUSH TABLES WITH READ LOCK (FTWRL) 命令，可以使整个数据库处于只读状态，阻止所有对数据的修改操作（包括 INSERT, DELETE, UPDATE 以及大部分 DDL 语句）。这样可以确保备份期间数据的一致性。

   • 释放全局锁可以使用 UNLOCK TABLES 命令或客户端断开连接。

2. 表级锁： 直接锁住整张表。

   • 表锁 (Table Lock):
     ① 是最基本的表级锁，可以通过 LOCK TABLES 命令显式地对表加锁，例如 LOCK TABLES table_name READ (表读锁) 或 LOCK TABLES table_name WRITE (表写锁)。
     ② 表锁开销最小，但会严重降低并发度，因为会阻塞对该表的其他所有读写操作。MyISAM 存储引擎默认使用表锁。

   • 元数据锁 (Metadata Lock / MDL):
     ① MDL 是在访问数据库对象（如表、视图、存储过程等）时自动添加的锁。
     ② 它的主要作用是保证在执行 DDL 操作时，不会与正在进行的 DML 操作发生冲突，反之亦然。例如，当一个事务正在读取表数据时，如果另一个会话尝试修改表结构，就会被 MDL 阻塞，直到读事务完成。

   • 意向锁 (Intention Lock):
     ① 意向锁是 InnoDB 存储引擎自动添加的表级锁。
     ② 它用于表示一个事务即将对表中的行加共享锁（IS Lock） 或 排他锁（IX Lock） 。
     ③ 意向锁的存在是为了提高行级锁和表级锁的兼容性判断效率。当一个事务尝试加表级共享锁或排他锁时，只需要检查表上是否存在意向锁，而无需遍历所有行检查是否存在行级锁。

   • AUTO-INC 锁 (自增锁):
     ① AUTO-INC 锁是一种特殊的表级锁，在向包含自增列的表中插入数据时使用。
     ② 它用于保证在并发插入的情况下，自增值能够唯一且连续地生成。在插入完成后，自增锁会立即释放。
     ③ 在不同的 innodb_autoinc_lock_mode 配置下，自增锁的行为会有所不同，以平衡并发度和自增值的连续性。

3. 行级锁： 只锁住表中的某一行或某些行。行级锁开销较大，但能显著提高并发度。行级锁主要由 InnoDB 存储引擎实现。

   • 记录锁 (Record Lock):
     ① 记录锁也称为行锁，它锁住的是索引记录。
     ② 当一个事务对某条记录进行修改或删除时，会加排他记录锁；当一个事务对某条记录进行读取（在特定隔离级别下）时，会加共享记录锁。

   • 间隙锁 (Gap Lock):
     ① 间隙锁是 InnoDB 在可重复读 (REPEATABLE READ) 隔离级别下为了防止幻读而引入的一种锁。
     ② 它锁住的是索引记录之间的间隙，而不是记录本身。间隙锁之间是兼容的，但会阻塞其他事务在被锁的间隙中插入记录。

   • 临界锁 (Next-Key Lock):
     ① 临界锁是 InnoDB 默认的行锁类型，它是记录锁和间隙锁的组合，锁住的是索引记录以及该记录之前的间隙。
     ② 临界锁在可重复读隔离级别下使用，既能防止幻读，也能保证索引记录的唯一性。

   • 插入意向锁 (Insert Intention Lock):
     ① 插入意向锁是一种特殊的间隙锁，它在事务执行 INSERT 操作时产生。
     ② 当多个事务在同一个间隙中插入记录时，如果它们插入的位置不冲突，它们可以同时持有插入意向锁，而无需互相等待。
     ③ 插入意向锁表示了事务在某个间隙中插入记录的意图，但不会阻塞其他事务在该间隙中插入记录（除非有冲突的间隙锁）。

MySQL和Redis的区别？

• Redis基于键值对，支持多种数据结构；而MySQL是一种关系型数据库，使用表来组织数据。
• Redis将数据存在内存中，通过持久化机制将数据写入磁盘，MySQL通常将数据存储在磁盘上。
• Redis不使用SQL，而是使用自己的命令集，MySQL使用SQL来进行数据查询和操作。
• Redis以高性能和低延迟为目标，适用于读多写少的应用场景，MySQL 适用于需要支持复杂查询、事务处理、拥有大规模数据集的场景。

Redis 更适合处理高速、高并发的数据访问，以及需要复杂数据结构和功能的场景，在实际应用中，很多系统会同时使用 MySQL 和 Redis。