Java 面试题 06 - MySQL

标签：面试题 Java 事务 查询 索引 06 主键 数据 id

事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的四个特性（ACID）：

• 原子性：事务不允许分割，要么全部完成，要么完全不执行。
• 一致性：逻辑上的正确性，即这组操作的结果是符合期望的。具体来说，就是不发生 丢失修改、不能重复读、脏读 这三种错误。
• 隔离性：并发访问数据库时，一个用户的事务不被其他事务干扰。
• 持久性：事务提交之后，对数据库中数据的改变是持久的，即使数据库发生故障也不影响。

并发事务可能会导致以下问题：

• 丢失修改：事务 A 和 B 先后更改数据 x（x = x0），在 A 实际更改 x 之前，B 已经读取了 x 原来的值 x0，然后 A 将 x 更改为 x1，B 的更改是基于 x0，更改后为 x2，这时 x2 将 x1 覆盖了，相当于 事务 A 针对 x 的更改丢失了。
• 脏读：事务 A 读取了 B 更改过的 x，但是 B 因为出错发生了 回滚，这时 A 读取的实际是 无效数据。
• 不可重复读：事务 A 前后两次读取 x，但是在这中间事务 B 更改了 x，导致 A 两次读取的结果不同。
• 幻读：在事务 A 读取符合某个条件的所有记录时，事务 B 增加 了一条符合该条件的记录，这就导致 A 在执行过程中 前后读取的记录可能不一致。

前三种都是 并发事务 在 修改同一份数据 的时候导致的问题，可以通过 对同一个资源加锁 的方式来解决。幻读是由不同事物并发执行时，新增数据 导致的问题，只能通过 事务串行化 来解决。而串行化和并发是矛盾的，所以要在 性能 和 一致性强度 上取得一个平衡，这就涉及到不同的隔离等级。

隔离等级 从低到高有：

• 读未提交：即允许读取未提交的数据。MySQL 事务隔离其实是依靠锁来实现的，加锁自然会带来性能的损失。而读未提交隔离级别是 不加锁 的，可以读到其他事务未提交的数据，但没有办法保证你读到的数据最终一定是提交后的数据，如果中间发生回滚，那就会出现 脏读 问题。

• 读已提交：允许读取已提交的数据，反过来说，就是 如果事务还没有提交，其他事务就不能读取该数据。数据的 读取不加锁，但是数据的 写操作加锁。可以避免脏读，但是不能避免不可重复读、幻读（读的时候其他事务可能会对正在读的数据做更新）

• 可重复读（默认）：读取时加锁，直到事务结束才释放。可以避免不可重复读、脏读 问题（两次读取之间不允许别的事务访问相关数据），不能解决幻读问题，因为其他事务仍然可以增加新数据。

• 可串行化：增加 表锁，所有事务 逐个执行，可以避免所有问题。

锁

乐观锁 vs 悲观锁

悲观锁：

• 总是假设最坏的情况 —— 每次拿数据时都认为别人会修改，所以 每次在拿数据时都会上锁。即 共享资源只能同时给一个线程用，释放锁之后其他线程才能用。
• 适用于写操作多的场景。
• 数据库中的 行锁、表锁 都是悲观锁，在操作之前先上锁。Java 中的 synchronized 和 ReentrantLock 等独占锁就是悲观锁思想的实现。

乐观锁：

• 总是假设最好的情况 —— 每次拿数据时都认为别人不会修改，所以不会上锁，但是 在更新时会判断一下在此期间有没有其他线程去更新这个数据，可以使用版本号机制和 CAS 算法实现。
  • 版本号机制：一般是在数据表中加入一个版本号字段 version，表示数据被修改的次数。当线程 A 要更新数据时，在读取数据的同时也会读取 version；在提交更新时，只有刚才读取的 version 值和当前数据库中的 version 值相等时才更新，否则重来一遍。
  • CAS 算法：即 compare and swap，旨在不使用锁的情况下实现多线程之间的变量同步。详细介绍见后文。
• 乐观锁 适用于读操作多的应用，可以提高吞吐量。
• java.util.concurrent.atomic 包中的原子变量类就是使用了乐观锁的一种实现方式（CAS）实现的。

CAS 算法：

• 算法过程：涉及三个操作数——需要读写的内存中的值 V、进行比较的值 A、拟写入的新值 B。过程如下：

  1. 线程 1 和 2 在同一时间并发更新主存的值。

  2. 线程 1 和 2 都先将主存中的值 V 备份到自己的本地内存中，备份值为 A （预期值）。

  3. 线程 1 要将 V 更新为 1，线程 2 要将 V 更新为 2。假设线程 1 先获得 CPU。

     1. 线程 1 更新时先将自己的备份值 A 和主存中的值 V 比较，此时 A=V=0，即 主存中的值符合预期，则进行更新操作。

     2. 线程 2 更新时将自己的备份值 A 和 V 比较，此时主存中的值已经被线程 1 更新过了，A=0, V=1，主存中的值不满足预期，所以线程 2 不能进行更新操作，进入 自旋 —— 不断重新读取主存中的值并加以备份，此时 A=V=1，满足预期，进行更新操作。

• 可能出现的问题：

  • ABA 问题：
    • 如果一个变量 V 初次读取是值为 A，在准备赋值的时候检查到它仍然是 A，那么就能说明它的值没有被其他线程修改过吗？显然不能，其他线程可能将它修改成了 B，然后又改回了 A。但是 CAS 会误认为它没有被修改过。
    • 解决方案可以是：在变量值之前追加版本号，更新变量时将版本号加一，这样 A → B → A 就变成了 1A → 2B → 3A。
    • JDK 的 AtomicStampedReference 类提供了 compareAndSet 方法，首先检查当前引用是否等于预期引用，然后检查当前标志是否等于预期标志，都相等才会更新引用的值和标志的值。
  • 循环时间长：如果主存的值不等于预期值，那么会进入 自旋，如果多次自旋，会造成较大的开销。
  • 只能保证一个共享变量的原子操作：当涉及多个共享变量时 CAS 无效。JDK 1.5 之后提供了 AtomicReference 类，可以保证 引用对象之间的原子性，可以把多个共享变量放在一个对象里进行 CAS 操作。

行锁 vs 表锁

• MyISAM 支持表锁；InnoDB 支持行锁和表锁，默认为行锁。
• 表锁：对当前访问的整张表加锁，
  • 消耗资源少，实现简单，加锁快，不会出现死锁。
  • 锁定粒度最大，触发锁冲突的概率高，并发度最低。
• 行锁：只对当前访问的行加锁，
  • 减少冲突，并发度高。
  • 加锁开销大，加锁慢，会出现死锁。

共享锁 vs 排他锁

• 共享锁：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取。但对一条记录加共享锁后，不能再加排他锁（共享锁仅和共享锁兼容）。
• 排他锁：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（排他锁与任何锁都不兼容）。

---

索引

索引分类

• 按存储结构分类：B 树索引、哈希索引（不能排序，不支持范围查询，不支持多字段查询）、全文索引。
• 从应用层次上划分：主键索引、唯一索引、复合索引、普通索引（普通列上的索引）。
• 根据表记录和索引的排列顺序是否一致来划分
  • 聚集索引：顺序一致，B+ 树叶结点存储的数据就是表记录（主键索引）
  • 非聚集索引：顺序不一致，B+ 树叶结点存储的数据是主键（也叫二级索引，辅助索引）

InnoDB 的主键索引是聚集索引，唯一索引、普通索引、前缀索引属于非聚集索引。

---

B+ 树相对 B 树的优势

• B+ 树磁盘读写代价低。B+ 树内部结点不存储数据，占用空间更小，所以同一块磁盘就能容纳更多结点，IO 次数更低。
• 查询效率更稳定。所有查询都在叶子结点命中。
• B+ 树只需要遍历叶结点就能实现整棵树的遍历。
• 所有叶结点形成有序链表，便于范围查询。

---

聚集索引 vs 非聚集索引

聚集索引：表记录在磁盘上存储的顺序和索引的排列顺序一致。为了保证记录的物理顺序和索引顺序的一致，在记录插入的时候，会对数据页重新排序，所以插入操作比较慢。在叶子结点中存储的数据是表记录。

非聚集索引：表记录的物理顺序和索引顺序不一致。在叶子结点中存储的是索引列和主键，所以使用非聚集索引查询数据时，找到对应叶子结点后，需要通过得到的主键再次查询（这个过程就是回表，但如果发生覆盖索引，就会直接返回对应值）。

覆盖索引，即 需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

---

InnoDB 的主键索引和辅助索引

InnoDB 的主键索引是聚集索引，索引 + 数据 = 表数据文件（.ibd 文件），叶结点保存着 主键值 和对应的 表记录。而在辅助索引中，叶结点只保存着 索引列 和对应的 主键，需要通过这个主键进行二次查询，得到完整记录。

通过主键索引到整条记录，通过辅助索引找到主键。

MyISAM 的主键索引和辅助索引

MyISAM 的主键索引和辅助索引都是 非聚集索引，索引和数据文件是分离的（数据是 .MYD 文件，索引是 .MYI 文件），索引保存的是到数据文件的指针。主索引和辅助索引在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。

---

联合索引中的最左前缀匹配原则

如果查询条件中存在与 创建联合索引时的最左侧字段 相匹配的字段，就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如 >、<、between 和 以 % 开头的 like 查询 等条件，才会停止匹配。

所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，可以过滤更多数据。

---

不会应用索引的情况有哪些？

• where 使用了 like，但 like 后的 模式串以 % 开始 不会使用索引。
• 如果使用了 or 运算符，第一个条件的字段有索引，但 第二个条件的字段没有索引，那么有索引也不会用。
• 如果 where 条件的字段参与了计算或者使用了函数，不使用索引。
• 数据类型出现隐式转换时，比如给 '123' 类型的字段传入 123，那么不会使用索引。
• 在索引列上使用 is not null 操作，不会使用索引。
• 在索引列上 使用不等号，不会使用索引。

---

使用索引的注意事项

1. 选择合适的字段建立索引
   • NOT NULL 字段，允许为 NULL 的字段，数据库较难优化。
   • 被 频繁查询 的字段
   • 需要 频繁排序 的字段
2. 建立联合索引而不是单列索引：索引需要占用磁盘空间，索引修改也需要较多时间。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。
3. 避免冗余索引：能够命中索引 (a, b) 就肯定能命中索引 (a) ，那么索引 (a) 就是冗余索引。应该尽量扩展已有的索引而不是创建新索引。

优化

MySQL 优化的步骤是什么？

1. 通过 show status 命令了解各种类型 SQL 执行的次数，了解到 当前数据库是以更新为主还是查询为主。

2. 通过慢查询日志 定位执行效率较低的 SQL 语句。

3. 通过 explain sql 命令分析 SQL 语句的执行情况，重点关注分析结果中的 type 字段。据此优化查询方式，使 type 尽可能接近 system 级别。

   type=const：表中 只有一条记录匹配，比如使用 主键 或者 唯一索引 进行查询时。

   type=system 表示表中仅有一条数据，性能最好。

   type=all ：全表查询 select * from xxx，性能最差。

---

常用优化策略：

• 充分利用已经存在的索引（避免索引失效的情况）
• 在 select 语句中指定确切的字段名
• 在 insert 语句中指定确切的字段名
• 建议使用预编译语句进行 DB 的操作
• 避免数据类型的隐式转换（这会导致索引失效）
• 不在 where 子句中使用函数
• 合并多个相同的操作，让数据库批量处理，效率更高
• 使用 in 代替 or，因为后者很少会用到索引

---

大表优化

单表记录数过大时，CRUD 效率会明显降低，常见的优化措施如下：

1. 限定查询范围：禁止不带过滤条件的查询语句。

2. 读写分离：主库负责写，从库负责读。

3. 垂直分区：即数据表 列的拆分，将列较多的表拆分成多个列较少的表。

   • 简化表结构，易于维护，且在查询时减少读取的 IO 次数。
   • 主键会出现冗余；使事务变得复杂。

4. 水平分区：保持 数据表结构不变，通过某种策略将 存储数据分片，每一片的数据分散到不同的表甚至库中，达到分布式的目的。能够支持很大的数据量，但 分片事务难以解决。

   尽量不要对数据分片，因为拆分会带来逻辑、部署、运维的各种复杂度。

日志

总的来说：

• redo 日志用于 数据恢复
• binlog 日志用于不同节点之间的 数据同步
• undo 日志用于操作 回滚

redo 日志

redo log 赋予了 InnoDB 崩溃恢复 能力，保证了数据的持久性和完整性。

MySQL 中数据以 页 为单位进行存储，查询一条记录时会从硬盘中加载一整页数据，放入 Buffer Pool 中。后续查询都是先从 Buffer Pool 中查找，没有命中才去磁盘加载，减少了磁盘 IO 开销。更新表数据时也是如此，先看 Buffer Pool 中有没有要更新的数据，有的话就直接在 Buffer Pool 中更新，然后会把「在某个数据页上做了什么修改」记录到 redo log buffer 中，随后伺机 刷盘 到 redo log 文件中。

那么什么时候刷盘呢？innodb_flush_log_at_trx_commit 参数控制刷盘时机，支持三种策略：

• 设置为 0，表示每次提交事务时不进行刷盘。
• 设置为 1（默认），表示 每次提交事务时都进行刷盘。如果事务提交成功，那么就会立即将日志写入磁盘；如果事务执行期间 MySQL 挂了或机器宕机，那么这部分日志就无了，但是因为事务没有提交，日志无了也没有任何影响。
• 设置为 2，表示每次提交事务都只把 redo log buffer 写入 page cache（文件系统缓存）。即如果事务提交成功，会立即将日志写入文件系统缓存（后台还是每秒刷盘一次）；所以如果只是 MySQL 挂了，没有影响，如果宕机，会丢失一秒内的修改。

另外，InnoDB 存储引擎有一个后台线程，每隔 1 秒，就会把 redo log buffer 写到 page cache，然后调用 fsync 进行刷盘。

此外，当 redo log buffer 占用空间即将达到 innodb_log_buffer_size 的一半时，后台线程会主动刷盘。

图片来源：https://javaguide.cn/database/mysql/mysql-logs.html

binlog 日志

binlog 用来实现不同 MySQL 之间的 数据同步，保证 数据一致性。

会记录所有 更新操作的逻辑，例如「对 id=2 这一行的 A 字段加 1」。

刷盘机制：事务执行过程中，会把日志写到 binlog cache，提交时将 binlog cache 写入 page cache（write），后面伺机将 page cache 写入磁盘（fsync）。write 和 fsync 的时机，由参数 sync_binlog 控制：

• 0（默认），每次提交事务都只 write，由系统判断何时 fsync。宕机时 page cache 里面的 binlog 会丢失。
• 1，每次提交事务都会 write 和 fsync。
• n（>1），每次提交事务都只 write，累积 n 个事务后才 fsync。

两阶段提交

在执行更新操作的过程中，会记录这两种日志，它们都属于 持久化 的保证，但是侧重点不同：redo log 让 InnoDB 存储引擎有了崩溃恢复能力，而 binlog 保证了 MySQL 集群架构的数据一致性。

redo log 会在执行过程中不断写入，而 binlog 会在事务提交时才写入，写入时机的不同就导致这两份日志之间可能存在不一致的情况 —— 比如写完 redo log 之后，binlog 写入时发生异常，就会导致不一致。为了解决这个问题，InnoDB 存储引擎使用两阶段提交方案 —— 将 redo log 的写入拆分为两个步骤：prepare 和 commit。

本小节的图片来源：https://javaguide.cn/database/mysql/mysql-logs.html

使用两阶段提交后，在写入 binlog 时发生异常，或者在 redo log 的 commit 阶段发生异常，都不会影响一致性。具体见下图：

• 如果在写入 biglog 阶段发生异常，MySQL 根据 redo log 恢复数据时，发现 redo log 还是 prepare 阶段，并且没有对应的 binlog 日志，就会回滚该事务。
• 如果在 redo log 的 commit 阶段发生异常，虽然 redo log 处于 prepare 阶段，但是存在对应的 binlog 日志，所以会提交该事务。

undo log

所有修改操作都会记录到 undo log 中，如果执行过程中出现异常，MySQL 会根据 undo 日志进行回滚，这就保证了事务的 原子性。

引擎

MyISAM vs InnoDB

• MySQL 5.5 版本之前的默认引擎是 MyISAM，5.5 之后是 InnoDB。
• 是否支持 事务和行级锁？
  • MyISAM 不支持事务和行级锁，崩溃后无法恢复，但支持全文索引。
  • InnoDB 支持事务和行级锁，有崩溃后修复能力。
• 是否支持 外键？
  • 只有 InnoDB 支持。
• 是否支持 MVCC（多版本事务控制）？
  • 只有 InnoDB 支持，应对高并发事务，MVCC 机制比加锁更高效。
• 索引的实现方式：
  • 都使用 B+ 树作为索引的数据结构，但是实现方式不同。
  • InnoDB（聚集索引）：数据文件本身就是按 B+ Tree 组织的一个索引结构。主键索引中叶节点保存了完整的数据记录；辅助索引中叶结点保存主键的值。
  • MyISAM（非聚集索引）：索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引相互独立，叶结点都保存着到数据文件的指针。
• 在很多我们已知场景中，InnoDB 的速度都可以让 MyISAM 望尘莫及。

复杂查询实例

标签：面试题,Java,事务,查询,索引,06,主键,数据,id
From： https://www.cnblogs.com/lzh1995/p/16758164.html