[大数据]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）：最高隔离界别，通过强制事务串行执行，避免幻读。在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题，实际使用很少，只有需要确保数据一致性时考虑。

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

7、锁机制和InnoDB锁算法

锁机制：

• 表级锁：MySQL中粒度最大的一种锁，对当前操作的整张表进行加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁，锁定粒度最大，触发锁冲突概率最高，并发度最低
• 行级锁：MySQL中粒度最小的一种锁，只对当前操作的的行进行加锁。行级锁能大大减少数据库操作的冲突。加锁粒度小，并发度高，但加锁的开销也比较大，加锁慢，会出现死锁
  InnoDB锁算法：
• Recode 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次更新缓存就没有必要，反之如果是删除缓存，在这一分钟用到该缓存不过就是重新计算一次而已，开销大幅降低。

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖