MySQL八股文

1、什么是MySQL？

MySQL是一种关系型数据库，在Java企业级开发中非常常用，因为MySQL是开源免费的，并且方便扩展。阿里巴巴数据库系统也大量用到了MySQL，因此它的稳定性是有保障的，Mysql是开发源代码的，任何人都可以在GPL(General Public Licence)的许可下下载并根据个性化需要对其进行修改。MySQL的默认端口号是3306。

2、存储引擎

• InnoDB：默认引擎，唯一支持事务的引擎，行级锁，外键
• MyISAM：全文检索，表级锁
• Memory：基于哈希，保存在内存
• CSV
• BlackHole
• Archive
• MRG_MyISAM
• Performance_Schema
• Federated

查看所有引擎：

show engines;

查看当前默认引擎

show variables like '%storage_engine%';

查看表的存储引擎

show table status like '%[table_name]%';

3、InnoDB和MyISAM的区别

• 支持的版本：MyISAM是MySQL 5.5之前的默认引擎，5.5版本之后引入了InnoDB引擎
• 是否支持行级锁：MyISAM只有表级锁，而InnoDB支持行级锁和表级锁，默认为行级锁；
• 是否支持事务：MyISAM强调性能，每次查询具有原子性，执行速度比InnoDB更快，但不支持事务。而InnoDB支持事务操作，具有事务提交、回滚和崩溃修复能力的事务安全型表
• 崩溃后的安全恢复：MyISAM崩溃后无法安全恢复，InnoDB崩溃后可以安全恢复
• 是否支持外键：MyISAM不支持外键，而InnoDB支持外键
• 是否支持MVCC：仅InnoDB支持。应对⾼并发事务, MVCC（多版本并发控制）⽐单纯的加锁更⾼效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下⼯作;MVCC可以使⽤乐观(optimistic)锁 和 悲观(pessimistic)锁来实现，各数据库中MVCC实现并不统⼀。

4、事务和事务的四大特性（ACID）

事务是逻辑上的一组操作，要么全部执行成功，要么都执行失败。例子：银行转账

• 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么全部执行失败
• 一致性（Consistency）：数据库总是从一个一致性状态转换到另一个一致性状态，例子：转账前后总和不变
• 隔离性（Isolation）：针对并发事务而言，多个事务之间相互隔离，互不影响。
• 持久性（Durability）：一旦事务提交成功，其修改就会永久保存到数据库中，即使数据库发生故障，也不会对其产生任何影响

5、并发事务带来的问题

• 脏读：一个未提交的事务读取到了另一个未提交事务修改的数据。
• 丢失修改：一个未提交的事务修改某个数据，另一个未提交的事务也对该数据进行了修改。例如，事务1读取数据A=20，事务2也读取了A=20，此时事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，丢失了其中的一次修改。
• 不可重复读：一个未提交的事务多次读取到另一个事务提交的修改的数据。指在⼀个事务内多次读同⼀数据。在这个事务还没有结束时，另⼀个事务也访问该数据。那么，在第⼀个事务中的两次读数据之间，由于第⼆个事务的修改导致第⼀个事务两次读取的数据可能不太⼀样。这就发⽣了在⼀个事务内两次读到的数据是不⼀样的情况，因此称为不可重复读。
• 幻读（虚读）：一个未提交的事务读取到另一事务提交添加数据。当一个事务1读取了几行数据，接着另一个事务2插入了一些数据，当事务1再次读取时发现多了一些原本不存在的记录。

6、事务隔离级别

• 未提交读（READ-UNCOMMITTED）：事务中的修改即使没有提交，对其他事务也是可见的。会产生脏读、不可重复读、幻读
• 提交读（READ-COMMITTED）：多数数据库的默认隔离级别，事务只能看见已提交事务的修改。存在不可重复读、幻读
• 可重复读（REPEATABLE-READ）：MySQL的默认隔离级别。解决了不可重复读，保证同一事务中多次读取同样的记录结果一致，InnoDB通过MVCC解决。存在幻读
• 可串行化（SERIALIZABLE）：最高隔离界别，通过强制事务串行执行，避免幻读。在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题，实际使用很少，只有需要确保数据一致性时考虑。

隔离级别	脏读	不可重复读	幻读
Read-Uncommitted	√	√	√
Read-Committed	×	√	√
Repeatable-Read	×	×	√
Serializable	×	×	×

注意：InnoDB引擎在 可重复读 隔离级别下使用的是Next-Key Lock算法，可以避免幻读的产生，已经达到了可串行化的隔离级别，并且并不会有任何性能损失。InnoDB引擎在分布式情况下一般会用到 可串行化 隔离级别。

7、锁机制和InnoDB锁算法

锁机制：

• 表级锁：MySQL中粒度最大的一种锁，对当前操作的整张表进行加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁，锁定粒度最大，触发锁冲突概率最高，并发度最低
• 行级锁：MySQL中粒度最小的一种锁，只对当前操作的的行进行加锁。行级锁能大大减少数据库操作的冲突。加锁粒度小，并发度高，但加锁的开销也比较大，加锁慢，会出现死锁

InnoDB锁算法：

• Record Lock：单个行记录上的锁
• Gap Lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key Lock：record+gap 锁定一个范围，包括记录本身
  

8、共享锁(S)和排它锁(X)

• 共享锁：又称读锁，若事务1对数据对象A加上S锁，其他事务只能对A加S锁，不能加X锁，可以保证其他事务可以读A，但不能对其进行修改
• 排它锁：又称写锁，若事务对数据对象加上X锁，其他事务不能再对其加任何类型的锁。

9、意向共享锁(IS)和意向排它锁(IX)

意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

• 意向共享锁（IS）： 表示事务准备给数据行记入共享锁，事务在一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）： 表示事务准备给数据行加入排他锁，事务在一个数据行加排他锁前必须先取得该表的IX锁。

10、大表优化

• 限定数据范围：务必禁止不带任何限制数据范围条件的查询语句。比如：查询历史订单时，可以控制在一个月范围内
• 读写分离：经典的数据库拆分方案，主库负责写，从库负责读
• 垂直分区：数据表按列拆分，将一张列比较多的表，拆分成多张表。可以使列数据变小，在查询时减少读取的Block数，减少IO次数，简化表结构，易于维护。但是主键会出现冗余，需要管理冗余列，并会引起Join操作。
• 水平分区：保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的，可以支撑非常大的数据量。

数据库分片的两种常见方案：

1. 客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。当当网的Sharding-JDBC、阿里的TDDL是比较常用的实现
2. 中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。Mycat，360的Atlas，网易的DDB等等都是这种架构的实现

11、池化设计思想

池化设计应该不是⼀个新名词。我们常⻅的如java线程池、jdbc连接池、redis连接池等就是这类设计的代表实现。这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。就好⽐你去⻝堂打饭，打饭的⼤妈会先把饭盛好⼏份放那⾥，你来了就直接拿着饭盒加菜即可，不⽤再临时⼜盛饭⼜打菜，效率就⾼了。除了初始化资源，池化设计还包括如下这些特征：池⼦的初始值、池⼦的活跃值、池⼦的最⼤值等，这些特征可以直接映射到java线程池和数据库连接池的成员属性中。

12、什么事是数据库连接池？为什么需要数据库连接池？

数据库连接本质就是⼀个 socket 的连接。数据库服务端还要维护⼀些缓存和⽤户权限信息之类的东西，所以占⽤了⼀些内存。我们可以把数据库连接池是看做是维护数据库连接的缓存，以便将来需要对数据库发起请求时可以重⽤这些连接。为每个⽤户打开和维护数据库连接，尤其是应用程序对数据库驱动的动态请求，既昂贵⼜浪费资源。在连接池中，创建连接后，将其放置在池子中，并再次使⽤它，因此不必建⽴新的连接。如果使⽤了所有连接，则会建⽴⼀个新连接并将其添加到池中。 连接池还减少了⽤户必须等待建⽴与数据库的连接的时间。

13、一条SQL语句在MySQL中如何执行

• MySQL主要分为Server层和引擎层，Server层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用，redolog只有InnoDB有。
• 引擎层是插件式的，目前主要包括MyISAM，InnoDB和Memory等
• SQL语句执行过程分为两类，对于查询语句：权限校验 —> 查询缓存 —> 分析器 —> 优化器 —> 权限校验 —> 执行器 —> 引擎
• 对于更新语句：(查询) —> 分析器 —> 权限校验 —> 执行器 —> 引擎 —> redolog prepare —> binlog —> redolog commit

14、MySQL的三种日志

• redo log：InnoDB引擎层特有的日志形式，是为了保证数据的可靠性，当数据库崩溃后靠此恢复数据
• bin log：MySQL server层面上生成的日志，主要用于 point in time 恢复和主从复制
• undo log：主要用于事务的回滚(undo log 记录的是每个修改操作的逆操作) 和 一致性非锁定读(undo log 回滚行记录到某种特定的版本—MVCC 多版本并发控制)。

15、⼀条SQL语句执行得很慢的原因有哪些？

分类讨论：

1. 大多数情况下正常，偶尔出现很慢的情况：
   （1）数据库在刷新脏页，例如redo log写满了要同步到磁盘
   （2）执行时遇到锁
2. 在数据量不变的情况下，这条SQL语句一直以来都执行得很慢
   （1）没有用上索引：可能没索引，可能对字段进行运算或函数操作导致无法用索引
   （2）数据库选错了索引

16、索引为什么能够提高检索速度？

将无序的数据变成相对有序的数据。没有用索引时，我们需要遍历双向链表来定位对应的页，然后通过遍历页中的单向链表来检索对应的记录。MySQL的索引使用的是B+树，能够让我们很快地查询到对应的记录。

17、索引为什么会降低增删改的速度？

B+树是一种平衡树（它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树），如果我们进行增删改操作，会破坏它原有的平衡结构，需要额外的工作来维护平衡，这些额外的开销，导致索引会降低增删改的速度。

18、哈希索引

哈希索引就是采用一定的哈希算法，将键值换算成新的哈希值，检索时不像B+树那样从根节点到叶子结点逐级查找，只需一次哈希算法就能立刻定位到相应的位置，速度非常快。

缺点：

• 哈希索引没办法利用索引完成排序，
• 不支持最左匹配原则，
• 在有大量重复键值的情况下，哈希索引的效率极低，会发生哈希碰撞问题，
• 不支持范围查询

19、InnoDB支持哈希索引吗？

InnoDB主要使用的还是B+树索引，对于哈希索引，InnoDB是自适应哈希索引的，由InnoDB存储引擎自动优化创建，不能人为干预。

20、聚簇索引和非聚簇索引

• 聚簇索引：找到了索引就找到了对应的数据，这个索引就是聚簇索引，所以主键就是聚簇索引，修改聚簇索引就是修改主键
• 非聚簇索引：又称辅助索引，二级索引。索引的存储和数据的存储是分离的，找到了索引但没找到数据，需要根据索引上的值（主键）再次回表查询

主键一定是聚簇索引，MySQL的InnoDB中一定有主键，即便研发人员不手动设置，则会使用unique索引，没有unique索引，则会使用数据库内部的一个行的id来当作主键索引,其它普通索引需要区分SQL场景，当SQL查询的列就是索引本身时，我们称这种场景下该普通索引也可以叫做聚簇索引，MyisAM引擎没有聚簇索引。

21、最左匹配原则

最左匹配：即最左优先，以最左边的为起点，任何连续的索引都能匹配上。但遇到范围查询（>, <, between, like）就会停止匹配
如果SQL语句中用到了联合索引中的最左边的索引，那么这条SQL语句就会利用这个联合索引去进行匹配。

22、为什么使用联合索引

1. 减少开销：建立一个联合索引(a,b,c)，相当于建立了(a), (a,b), (a,b,c)三个索引。每多一个索引都会增加写操作的开销和磁盘空间的开销。对于当量数据的表，使用联合索引会大大减少开销
2. 覆盖索引：对联合索引（a, b, c），如果用如下sql：select a,b,c from test where a=1 and b=2，MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机IO操作，减少IO操作，特别是随机IO，其实是DBA主要的优化策略。所以在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
3. 效率高：索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where a=1 and b=2 and c=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W×10%=100w条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w × 10% × 10% ×10%=1w，效率提升可想而知！

23、如何使用索引优化查询问题

1. 如果查询语句中where, order by, group by涉及多个字段，一般需要创建多列索引
2. 多列索引中，一般把选择性高的字段放在前面
3. 避免使用范围查询
4. 尽量避免查询不需要的数据（取需要的字段）
5. 查询的数据类型要正确

24、MySQL中的char和varchar的区别

• char是定长的字符串，长度不足时用空格填充，varchar是不定长的字符串，大小根据字符串的长度而定
• 取数据时，保留varchar后面的空格，而会删除char后面的空格
• 从空间利用率来看，varchar高于char

25、id与uuid（主键自增为什么比随机id和自定义id要快）

推荐使用自增id，不推荐使用uuid
因为InnoDB引擎使用的是聚簇索引，也就是B+树叶子结点保存了主键和全部数据，如果主键索引是自增id，那么只需要不断向后排列即可，如果是UUID由于无序性，会导致数据位置频繁变动，产生很多内存碎片，插入性能降低。

26、普通索引VS唯一索引

普通索引性能更好！！！https://blog.nowcoder.net/n/439244704bb64365a16c8db469c9023b

• 查询时：
  • 对于普通索引，查到第一条满足条件的记录后，还会继续查找下一条记录，直到找到所有满足条件的记录
  • 对于唯一索引，找到第一条满足条件的记录就会停止查询
  • 性能差别微乎其微
• 更新时：
  • 对于唯一索引，每次更新操作都必须检查是否满足唯一性约束，所以必须将数据页读入内存，无法使用Change Buffer
  • 对于普通索引，可以将先查询有关数据页是否在内存中，如果是直接修改，否则先保存在Change Buffer中，当下次读入数据页到内存时，执行Change Buffer中与这个数据页有关的操作。
  • 更新时普通索引性能更好

27、什么是Change Buffer

当更新数据页，如果数据页在内存中则直接更新，如果数据页不在内存中，可以先将更新操作缓存在 change buffer，就不需要再从磁盘中读入这个数据页了，当下次访问这个数据页时，再将数据页读入内存，然后执行 change buffer 中与这个页有关的操作

28、分库分表之后，id主键如何处理？

• UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字标识，如文件的名字
• 数据库自增id：两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的id有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈
• 利用redis生成id，性能比较好，灵活方便，不依赖于数据库，但是引入了新的组件，造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本
• Twitter的snowflake算法
• 美团的Leaf分布式ID生成系统

29、MySQL为什么用B+树，而不用其他数据结构

• 为什么不用B树
  B树的叶子结点和非叶子节点既可以保存索引，也可以保存数据，这样会导致保存的索引量会减少，为了维持索引量，就要增大B树的高度，这会导致IO操作增加，降低查询性能
• 为什么不用二叉树
  如果二叉树退化成一个链表，相当于全表扫描
• 为什么不用平衡二叉树
  一个节点最多有两个子节点，树的高度太大，IO操作多，查询性能低
• 为什么不用哈希
  哈希索引虽然等值查询效率极高，但是不支持排序、范围查找、模糊查找，且可能存在哈希冲突，性能不稳定

30、B+树的优点

• 查询性能高：B+树非叶子节点只存储索引，叶子节点既能存储索引，又能存储数据，层数少，IO操作少，查询性能高
• 范围查询方便：B+树的范围查找只需对叶子结点构成的链表进行遍历即可，非常方便
• 查询性能稳定：B+树查询性能相对稳定，每次都是从根节点查询到叶子结点

31、什么是MVCC

MVCC，即多版本并发控制。使用快照读的方式，读不加锁，读写不冲突。允许数据库中同时存在某一条记录的多个版本，根据记录的隐藏列事务版本ID，并根据事务的隔离级别去判断读取哪个版本的数据。MVCC是·乐观锁·的体现，实现了提交读和可重复读的隔离级别，可以解决脏读、不可重复读和幻读（MVCC+next-key-lock）

32、当前读和快照读

• 当前读：读取的是数据库数据记录的最新版本，在读取时要确保其他事务不会修改当前记录，所以会加锁
• 快照读：读取时不加锁，通过MVCC读取快照中的数据，避免加锁带来的数据损耗

33、MVCC的实现原理

MVCC的实现主要依赖记录中的三个隐藏字段、undo log、read view来实现。

• 三个隐藏字段：
  • row_id：单调增的行ID，不是必需的，占用6个字节，没有指定id，数据库自动生成一个row_id
  • trx_id：事务ID，记录操作该数据的事务的ID
  • roll_pointer：指向回滚段的undo日志（指向记录的上一个版本）
• undo log：回滚日志
• read view：用于判断哪个版本对当前事务可见

如果要执行更新操作，会将当前版本的记录放入undo log中，然后将roll_pointer指向undo log中的当前记录；
如果要执行查询操作，会根据read view来判断哪个版本对当前事务可见，然后进行相应的查询

34、MVCC的具体查询流程

InnoDB实现MVCC，是通过Read view+Undo log实现的，Undo log保存历史快照，Read view判断当前版本对是否是否可见。

1. 获取事务自己的trx_id（事务版本ID）
2. 获取read_view
3. 查询得到的数据，然后与read_view中的版本号进行比对
4. 如果不符合read_view的可见性规则，需要undo log中历史快照
5. 最后返回符合规则的数据

35、MySQL三大日志

• bin log：用于记录数据库执行的写入性操作(不包括查询)，以二进制形式保存在磁盘中，主要是用于主从复制和数据恢复
• redo log：InnoDB存储引擎独有的日志形式，用来崩溃恢复
• undo log：用来事务回滚（事务执行失败后进行回滚，用来保证事务的一致性）

36、两阶段提交

将redo log的写入拆分为prepare和commit两个阶段。

• 两阶段提交的原因：当redo log写完，bin log写入时发生崩溃，导致bin log写入失败，重启MySQL后，根据bin log恢复数据时会发现刚才的事务没有记录，所以修改失败变回原值，而根据redo log恢复数据时，redo log中存在刚才的事务操作，所以修改成功变为新值，两次恢复结果不一致。
• 两阶段提交后：写完redo log后，变为prepare状态，然后交给bin log进行写入操作，写入成功便将redo log置为commit状态，如果bin log写入时发生崩溃，恢复时redo log的状态为prepare状态，先进行回滚操作，然后再进行恢复，则根据bin log和redo log恢复的数据便会保持一致性。

37、MySQL主从复制

主从复制主要有三个线程：bin log线程、IO线程和SQL线程。

• bin log线程：负责将主服务器上的数据写入到bin log日志中
• IO线程：负责将主服务器bin log中的数据写入到从服务器的中继日志(relay log)中
• SQL线程：负责读取中继日志，解析出主服务器中已执行的数据更改操作，并在从服务器上重放
  中从复制的用途：
• 实时灾备，用于故障切换
• 读写分离，主服务器负责写，从服务器负责读(从库可以使用MyISAM引擎)

38、主从架构的数据一致性问题

在主从架构上，从库同步主库的过程是串行化的，也就是说主库上并行的操作，在从库上串行执行。在高并发的场景下，从库的数据一定会比主库慢一些，是有延时的。如果主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上就没有。这些数据就可能丢失了。
MySQL实际上有两个同步机制：一个是半同步复制，用于解决主库数据丢失的问题；另一个是并行复制，用来解决主从同步延时的问题。

• 半同步复制：主库写入redo log后，便强制此时立即将数据库同步到从库，从库将日志写入自己本地的relay log后，返回一个ack给主库，主库至少接收到至少一个从库的ack后才会认为写操作完成了。
• 并行复制：从库开启多个SQL线程，并行读取不同库中的relay log日志，然后并行重放不同库中的日志，属于级别的并行

39、缓存与数据库一致性

缓存 + 数据库 读写模式

• 读的时候先查缓存，缓存没有，读数据库
• 然后取出数据后放入缓存，同时返回响应
• 更新的时候，先更新数据库后删除缓存，或者先删除缓存再更新数据库

40、为什么要删除缓存而不是更新缓存？

比如数据库的在一分钟内修改50次，那么如果缓存也修改50次，在这一分钟内缓存可能只被读取一次，那这50次更新缓存就没有必要，反之如果是删除缓存，在这一分钟用到该缓存不过就是重新计算一次而已，开销大幅降低。

41、什么是视图？

当用户对数据库的一张或多张表的某些列感兴趣，但不想每次键入查询语句时，就可以构建视图来解决这个问题。视图是为了简化查询，提高响应效率而创建的一张虚拟表，它在物理上不是真实存在的。数据库中仅存放视图的定义，不存放视图的数据，这些数据还是存在原来的表中，如果表中的数据发生变化，视图对应的数据也会随之变化。

42、视图的优点和缺点

• 优点：

  • 简化复杂的查询。对于多表查询等复杂查询，对关注的列构建视图，不需要每次键入查询语句，对视图查询时相当于执行该查询语句
  • 限定特定用户进行数据访问。可以使用数据库视图向特定用户组展示一些非敏感数据
  • 提供额外的安全层。数据库可以创建只读视图向特定用户公开只读数据，只允许用户对只读数据进行检索，不允许对其进行更新
  • 可以定义计算列。通过某些列的函数关系形成动态变化的计算列
  • 支持应用的兼容性。假设有一个核心数据库，所有应用都依赖于它，如果添加、删除或修改数据表某些列，可以定义与原数据表结构相同的视图，继续为原来的应用程序提供服务

• 缺点：

  • 性能可能会变差：从视图查询数据可能会很慢，尤其是从其他视图衍生出的视图中进行查询时
  • 视图对表结构的依赖：视图时在基础表结构上定义的，如果原数据表结构发生变化，与之相关的视图也要重新定义