【八股】MySQL八股速记版

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

• 连接器： 建立连接，管理连接、校验用户身份。
• 查询缓存： 如果SQL语句直接命中缓存，直接返回，否则继续向下执行，MySQL8.0中已废除
• 解析SQL：对SQL语句进行语法分析、词法分析，然后构建语法树，方便之后后续模块解析表名、字段、语句类型
• 执行SQL：
  • 预处理阶段：检查表名、字段名是否合法，将selet *语句扩展为表上所有列
  • 优化阶段：出于查询成本考虑，选择查询成本最小的执行计划
  • 执行阶段：根据执行计划执行SQL语句，从储存引擎或许查询结果，返回给客户端

2. 事务隔离级别

当数据库上有多个事务同时执行的时候，可能会出现：脏读、不可重复读、幻读等问题，为了解决这些问题，就出现了事务隔离级别的概念。
事物隔离级别可以分为：读未提交、读提交、可重复读、串行化。

• 读未提交：一个事务还没提交时，它做的变更就能被其他事物看到。
• 读提交：一个事务提交之后，它做的变更才能被其他事物看到。
• 可重复读：指事务执行过程中看到的数据总是跟这个事务启动时看到的数据是一致的。当然在可重复读的隔离级别下，未提交变更对其他事务也是不可见的。
• 串行化：对于同一行记录，读会加“读锁”， 写会加“写锁”，当事务读写冲突的时候，只有在上一个事务执行完成后下一个事务才可以执行。

3. 事务的四大特性

• 原子性：事务是不可分割的最小单元，一个事务对应一个完整的业务。一个事务的所有操作要么全部执行完毕，要么全部失败，不允许出现中间状态。如果一个事务在执行过程中出错或者失败了，事务会回滚到执行之前的状态。
• 一致性：一个事务执行之前和执行之后的状态是一致的。例如账户a和b一共有1000元钱，a向b转账无论成功还是失败，它们账户的总和总是1000元。
• 隔离性：SQL允许多个事务同时对数据修改和读取，执行互不干扰，它们之间是相互隔离的，在多个事务同时发生读写操作时，它们之间互不干扰。
• 持久性：事务所做的变化，在它执行结束后永久存在。

4. 索引有哪些种类

从数据结构进行分类：

• B+树索引：所有的数据都储存在叶子节点，查询速度O(nlogn)，支持范围查询。
• 哈希索引：支持等值查询，等值查询的速度很快，但范围查询效率低
• 全文索引：Inodb和MyISAM都支持全文检索，一般在文本类型chat、text、varchar类型上创建
  从物理存储维度进行分类：
• 聚类索引：指数据和索引存储在一起，叶子结点中存储的是一行记录，找到索引也就找到了数据
• 非聚类索引：指数据和索引分开存储，叶子结点中存储的是记录的地址。
  从逻辑维度进行分类：
• 主键索引：一种特殊的唯一标识，不允许有空值
• 唯一索引：索引中的值必须是唯一的，但是允许有空值
• 普通索引：MySQL中基本的索引类型，允许空值和重复值
• 联合索引：多个字段联合创建的索引，查询时遵循左值查询
• 空间索引：MySQL5.7后支持空间索引，在空间索引方面遵循OpenGIS几何数据模型规则

5. MySQL为什么使用B+树来作索引，它的优势是什么？

1. 单点查询：B树进行单个索引查询时，最快可以在O（1）的时间查到。从平均时间代价来看，会比B+树稍快一些。但是B树的查询波动会比较大，因为每个节点既存索引又存记录，所以有时候访问到了非叶子节点就可以找到索引，而有时需要访问到叶子结点才能找到索引。B+树的非叶子节点不存放实际的记录数据，仅存放索引，数据量相同的情况下，B+树的非叶子结点可以存放更多索引，查询底层节点的磁盘I/O次数会更少。
2. 插入和删除效率：B+树有大量冗余节点，删除一个节点的时候，可以直接从叶子结点中删除，甚至可以不动非叶子节点，删除非常快。B+树的插入也是一样，有冗余节点，插入可能存在节点的分裂（如果节点饱和），但是最多只涉及树的一条路径。B树没有冗余节点，删除节点的时候非常复杂，可能涉及复杂的树的变形。
3. 范围查询：B+树所有的叶子结点间有一个链表进行连接，而B树没有将所有叶子结点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询，查询效率不如B+树。B+树的插入和删除效率更高。存在大量范围检索的场景，适合使用B+树，比如数据库。而对于大量的单个索引查询的场景，可以考虑B树，比如nosql的MongoDB。

6. 什么时候需要创建索引

• 表的主关键字：自动创建唯一索引。
• 直接条件查询的字段：经常用于WHERE查询条件的字段，这样能够提高整个表的查询效率
• 查询中与其他表关联的字段：例如字段建立了外键关系
• 查询中排序的字段：排序的字段如果通过索引去访问将大大提高排序速度
• 唯一性约束列：如果某列具有唯一性约束，那么为了确保数据的一致性，可以在这些列上创建唯一性索引
• 大表中的关键列：在大表中，如果查询的效率变得很低，可以考虑在关键列上创建索引。

7.什么时候不需要创建索引

• 小表：对小表创建索引可能会带来额外的开销，因为在小数据集中扫描整个表可能比使用索引更快
• 频繁的插入、更新和删除操作：索引的维护成本会随着数据的插入、更新和删除操作而增加。如果表经常被修改，过多的索引可能会影响性能。
• 数据重复且分布平均的表字段：假如一个表有10万行记录，性别只有男和女两种值，且每个值的分布大概率为50%，那么对这种字段建立索引一般不会提高数据库的查询速度。
• 很少被查询的列：如果某列很少被用于查询条件，那么为它创建索引可能没有明显的性能提升。
• 查询结果总行数较少的表：如果查询的结果集总行数很少，使用索引可能不会有太大的性能提升。

8. 索引失败的场景有哪些

1. OR条件：当查询中使用多个OR条件时，如果这些条件不涉及同一列，索引有可能无法有效使用。数据库可能会选择全表扫描而不是使用多个索引。
2. 对列进行类型转换：如果在查询中对列进行类型转换，例如将字符列转换为数字或日期，索引可能会失效。
3. 使用通配符前缀搜索：在使用通配符前缀（如‘LIKE’，‘perfix%’）进行搜索时，大多数索引无法使用，因为索引通常是按照列的完整值进行排列的
4. 不等号条件：当查询中包含不等号条件时，索引可能会失效，通常情况下，索引只能用于等值比较。
5. 表连接中的列类型不匹配：如果在连接操作中涉及的两个表的列类型不匹配，索引可能会失效。

9. MVCC机制

MVCC: 多版本并发控制，用于管理多个事务同时访问和修改数据库的数据，而不会导致前后数据不一致的情况，MVCC的核心思想是每个事务在数据库中看到的数据版本是事务开始时的一个快照，而不是实际的最新版本。这使得多个事务可以并发执行，而不会互相干扰。
数据库的事务有ACID四大属性，其中隔离性就是通过MVCC和锁来实现的，MVCC在一些锁的适用性较差的情况下使用。

如何实现：
每一个UndoLog日志中都会有一个roll_pointer（回滚指针）指针，用来指向之前的UndoLog版本。对于每一条记录就会构成一个版本链，用于记录所有的修改，每一次进行新的修改后，新的UndoLog会放在版本链的头部。

在我们进行查询的时候应该查询哪个版本？——通过ReadView来实现。
在事务SELECT查询数据的时候，就会构造一个ReadView，它包含了版本链的统计信息：

• m_ids： 当前活跃的所有事物的id，（所有未提交的事务）
• min_trx_id：版本链尾的id
• max_trx_id：下一个将要分配的事务id（版本链头事务id+1）
• creator_trx_id：创建这个ReadView的事务的id

查询规则：
该版本是否为当前事务创建（读取自己修改的数据），如果是就返回，否则进入下一个判断
该版本的事务id是否小于min_trx_id（在创建ReadView之前，数据已经提交），可以直接访问
该版本的事务id是否大于max_trx_id（在ReadView创建后，该版本才开启），不能被访问
该版本事务id在[min_trx_id, max_trx_id]之间，则判断当前版本事务id是否在m_ids中，如果不在，说明事务已经提交可以访问，否则不能访问

10. MySQL的执行引擎有哪些

主要有MySAM，InnoDB, Memory等引擎：

• InnoDB引擎提供了对事务ACID的支持，还提供了行级锁和外键的约束
• MyISAM引擎不支持事务，也不支持行级锁和外键约束
• Memory就是将数据放在内存中，数据处理速度很快，但是安全性不高。

11.MySQL日志文件有哪几种

• undo log是Innodb储存引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC。
• redo log是物理日志，记录了某个数据页做了什么修改，每当执行一个事务就会产生一条或者多条物理日志。
• binlog(归档日志)是server层生成的日志，主要用于数据备份和主从复制
• relay Log ：中继日志，用于主从复制场景下，slave通过IO线程拷贝master的bin log后本地生成的日志

12. MySQL有哪些锁 作用是什么

• 全局锁：主要应用于全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样，加上全局锁，意味着整个数据库都是只读状态。
• 表级锁：
  • 元数据锁（MDL）：对数据库表进行操作时，会自动给这个表加上元数据锁，为了保证当用户对表执行CRUD操作时，其他线程对这个表结构做了变更。元数据锁在事务提交后才会释放。
  • 意向锁：对某些记录加上共享锁之前，需要先在表级别加上一个意向共享锁，对某些记录加上独占锁之前，需要先在表级别上加上一个意向独占锁。普通的select是不会加行级锁的，普通的select语句是利用MVCC实现一致性读，是无锁的。
  • AUTO-INC锁：表的主键通常会设置为自增的，之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋一个递增的值，这是通过AUTO-INC锁实现的。在插入数据时，会加一个表级别的AUTO-INC锁，然后被AUTO-INCREMENT修饰的字段赋递增的值，等插入语句执行完成后，才会把AUTO-INC锁释放掉。其他事务如果要向该表插入语句都会被阻塞，从而保证插入数据时字段的值是连续递增的。
• 行锁：
  • 记录锁：锁住的是一条记录，记录锁分为排他锁和共享锁
  • 间隙锁：只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。间隙锁之间是兼容的，两个事物可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系。
  • Next-Key Lock：临时锁，是Record Lock + Gap Lock的组合，锁定一个范围，并且锁定记录本身。 Next-Key Lock既能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。
  • 插入意向锁：一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事物加了间隙锁（ Next-Key Lock也包含间隙锁）。如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止，在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新纪录，但是现在处于等待状态。