[Day9] 两天复习完Mysql [2/2]
2025 年 3 月 5 日 Day 9
**两天复习完 Mysql **
索引
innodb 和 MyISAM 索引的区别
innodb 是聚簇索引, MyISAM 是非聚簇索引也就是索引和数据分开存放; innodb 中支持的最小粒度的锁时行锁, MyISAM 最小的锁时表锁, 并发性能低下； innodb 支持事务、外键 MyISAM 不支持;
innodb 的索引类型有哪些?
- 根据底层结构分
- B+树索引
- Hash 索引
- 全文索引
- 存储结构分
- 聚簇索引
- 非聚簇索引
- 按照类型分
- 主索引
- 普通二级索引
- 唯一索引
为什么 innodb 选择 B+树进行存储索引?
innodb 选用 B+树的过程是一步步优化的过程; 最开始为了以 O (logn) 的时间复杂度定位记录选用的是平衡二叉树每次查找都能筛选掉一半节点；但是平衡二叉树的树层太高, 所以就增加每个节点的容量就升级成为了 B 树, 但是 B 树也有比较明显的问题, 就是在进行范围查找的时候只能通过中序遍历实现; 所以 B+树就诞生了, 在 B 树中的数据都存在每个树的节点中, B+树将所欲的数据都存放到了叶子节点, 并用链表的结构把叶子节点都串联起来了, 这样一来非叶子节点的体积就变小了提高了根节点到叶子节点的速度, 并且继续范围查询的时候可以通过链表快速实现;
哪些情况下适合创建索引哪些情况又不适合创建索引?
我们可以先从索引的优势分析, 索引的优势在于可以保证取值唯一、查询速度快、天然的具备有序； 所以适合建立所以的字段:
- 需要保证唯一性的字段比如学号 id 等
- 需要进行频繁查询的字段
- 需要进行分组和排序的字段；排序很好理解因为索引天然具备有序性, 分组也同样, 同样取值的记录会被记录在同一个叶子节点下, 可以加快统计速度
- 需要进行联合查询的字段需要建立索引, 涉及联合查询如果不见索引每次都会进行一次全表扫描性能很低
- 结合索引覆盖和索引下推, 如果一个字段建立索引可以减少回表次数, 那么也可以考虑建立索引
不适合建立所以的字段：
- 经常发生值变动的字段; 一次字段值的变动 B+树需要花费很大的开销
- 参与计算的字段不建议创建索引, 因为会让索引失效；比如 where age+1=10 就不能走 age 的索引只能全表扫描
- 区分度比较低重复率高的字段不建议建立索引
什么是聚簇索引和非聚簇索引?
聚簇索引和非聚簇索引的区别就是索引和数据的存放关系; 聚簇索引中数据和索引存放在一起; 准确的了来说是将数据存储在索引 B+树中的叶子节点中; 而非聚簇索引中的叶子节点存放的树叶子本身的索引值和主键值 (如果是在 MyISAM 中则不是主键值而是记录的地址值), 有了主键值我们再进行一次回表操作, 从聚簇索引中根据主键的值来定位数据；这也就解释来为什么 innodb 中必须创建主索引, 就算不主动创建也会默认用隐藏字段来建立, 为了就是实现聚簇索引
什么是 Mysql 的最左前缀原则?
最左前缀原则是为了让我们的复合索引在查询中尽可能多的加速查询速度; 比如我们用 (a, b, c)字段建立了复合索引; 那么这个索引只能加速条件为 a，ab， abc 这样的查询条件; 这很好理解, 比如在字典中我们要查找一 a 开头的我们可以通过索引直接找到，但是如果我们要找以 c 结尾的单词那么就只能进行全表扫描, 索引就没有在本次查询中生效 打破最左匹配原则的除了中间的条件没有传入导致后面字段的索引失效外, 还会在中间字段进行范围条件查询的时候被打破;
举个例子：我们对班级、年龄、性别字段建立了复合索引; 如果我们查询三年一班，年龄是 10 岁的男生; 这时候就可以命中全部的字段索引, 可以完全加速本次查询; 如果我们查询的是三年一班的所有女生, 那么我们会先通过所以查询所有三年一班的同学, 但是因为年龄字段没有传入所以无法定位, 只能对班级加速的查询结果进行扫描; 如果查询三年一班大于 10 岁的男生依然只能在第二层查询出大于 10 岁的所有同学再进行扫描
如果是 is null not null 这样的判断是不会打断匹配的, 因为 null 会作为最小值排在 B+树的最左侧
索引 A B C 联合索引; 可以走索引:
- BA 同 AB 可以
- CBA 同 ABC 可以走
- AC 只能走 A 的部分
什么是回表? 什么是索引下推 ? 什么是索引覆盖?
回表: 指的是通过非聚簇索引查询到叶子节点后, 通过叶子节点上标记的主索引值通过再去存储数据的聚簇索引中查询记录的这个过程就叫做回表;
索引覆盖: 当需要查询的字段可以被二级索引覆盖那么就不需要进行回表操作； 比如一个表中又姓名和年龄字段和其他字段, 并且姓名和年龄已经建立了复合索引, 如果一条 SQL 语句是查询年龄为 20 的记录姓名那么就可以通过这个复合索引直接将复合条件的姓名字段返回; 不需要进行回表;(可以通过 Explain 关键字 extra 字段查看是否实现了索引覆盖 Using index）
索引下推: 是将数据的一部分过滤操作从 Server 层下推到索引层; 就是在索引成进行部分的过滤来实现减少回表次数 比如表中有年龄和性别两个字段并且建立了索引; 如果查询年龄大于 20 岁的女生; 如果没有索引下推就会将所有年龄大于 20 的记录通过二级索引叶子节点找到索引符合年龄的记录的主键值进行回表操作, 然后将这些记录交给 Server 层, 然后 server 层进行性别条件的过滤; 如果有了索引下推那么就会在二级索引查找大于 20 年龄的这个过程中同时过滤性别为男的记录, 最对终符合两个条件的主键值进行回表找到数据再交给 server;
有哪些场景会导致索引失效? 如何排查?
可能导致索引失效的场景:
- 使用复合索引时没有遵行最左前缀原则
- 在条件中使用了表达式计算或则函数
- like 关键字中以%开头
- OR 条件表达式
- 范围查询 > < in not in
- 数据不均匀导致优化器认为全表扫描更快; 比如查询性别为男的记录; 可能导致走索引速度不如全表扫描
要排查索引是否失效; 通过 explain 关键字开启执行计划; type 字段为 all 则表示进行了全表扫描；如果是 ref 或则 range 则说明索引生效了
explain select * from student where age = (18-1) # 这样可以走索引
explain select * from student where age + 1 = 1) # 这样不能走索引
锁
介绍一下 Mysql 中的锁
Mysql 中的锁按粒度划分可以分为: 表锁、行锁还有用得最少的页锁 (锁定数据页)；
按类型来分的话可以分为共享锁和排他锁, 还有意向锁, 共享锁指允许对个事务同时读取记录, 如果有事务需要修改被上了共享锁的记录那么就需要等其他读取这个记录的事务全部提交或则回滚；独占锁指的是一个记录只能被一个线程修改和操作, 因为是同步操作所以性能最低但是安全性最高；意向锁分为意向共享锁意向拍他锁, 意向锁可以快速判断行锁和表锁是否会冲突避免了全表遍历; 比如事务 A 对一条记录上了排他锁, 如果这个时候事务 B 像对整张表上排他锁那么它就需要遍历所有的行判断是否存在排他锁, 然后进行阻塞等待直到表中没有其他锁才能上表锁；有了意向锁之后就可以快速判断当前需要上的锁是否需要进行阻塞等待；
如果按实现思想分的话可以分为悲观锁和乐观锁
场景 1：意向锁在表级别协调冲突
-
事务 A 修改某一行：
1BEGIN; 2SELECT * FROM users WHERE id = 1 FOR UPDATE; -- 表级 IX 锁 + 行级 X 锁 -
事务 B 尝试修改另一行：
1BEGIN; 2SELECT * FROM users WHERE id = 2 FOR UPDATE; -- 表级 IX 锁 + 行级 X 锁- 结果：两个事务的 IX 锁在表级别兼容（允许并发修改不同行），事务 B 不会被阻塞。
场景 2：意向锁与表级锁冲突
-
事务 A 对表加 X 锁：
1LOCK TABLES users WRITE; -- 表级 X 锁 -
事务 B 尝试修改某一行：
1BEGIN; 2SELECT * FROM users WHERE id = 1 FOR UPDATE; -- 需要表级 IX 锁，但表已有 X 锁，冲突！- 结果：事务 B 被阻塞，直到事务 A 释放 X 锁。
MySQL 的行级锁锁的到底是什么？
行级锁可以根据粒度不同分为记录锁、间隙锁还有结合前两者的 next-key 锁 记录锁锁定的是索引记录；
- 例如：
SELECT * FROM users WHERE id = 1 FOR UPDATE;- 如果id是主键，则锁住主键索引中id=1的记录。 - 如果是唯一索引那么就锁定所有符合条件的记录, 如果是主索引就锁定唯一记录 间隙锁锁定的是索引之间的范围；为 RR 级别下避免了幻读- 例如：
SELECT * FROM users WHERE id > 5 AND id < 10 FOR UPDATE;- 锁住id=5到id=10之间的间隙，阻止插入id=6、id=7等数据。 next-key 锁定的锁索引记录和间隙，它的范围锁左开右闭的- next-key 是锁住索引记录的同时也不允许中间被插入其他记录是前两者的结合
无索引时的锁升级： 如果查询条件没有索引, 那么 mysql 就需要进行全表扫描, 锁就会从行锁升级为表锁
什么是 mysql 中的死锁？如何避免和解决？
死锁就是指两个或者多个事务之间互相等待对面手上的锁同时有持有对象需要的锁；比如事务 A 有锁 1 但是需要锁 2；事务 B 有锁 2 但是需要锁 1;
避免出现死锁可以从以下几方面考虑：
- 控制资源获取的顺序; 如果多个事务以相同的顺序去获取锁就能避免一定程度的死锁
- 控制锁的粒度或则少用锁；比如可以将隔离级别从 RR 改成 RC 这样就可以避免间隙锁和 next-key 锁带来的死锁情况；大厂中一般都是将隔离级别从默认的 RR 改为 RC 然后通过其他手段比如乐观锁分布式锁来保证数据的一致性
- 减少锁持有资源的时间；比如一个事务锁长事务, 那么我们就可以尽可能将 sql 语句集中在事务的最前面或则最后面
解决死锁
- 目前的数据库都一般都具备自动干预死锁的能力；比如 mysql 中可以通过同等待图机制检测到死锁后选择回滚一个开销较小的事务来解决死锁; 还可以通过设置一个事务的超时时间来避免死锁；
- 误删数据后如何快速恢复？
日志
介绍一下 MySQL 中的常见日志
mysql 中常用的日志有 bIn log, redo log, undo log 还有使用较少慢查询日志，错误日志、relay log 最重要的是就是 undo log, redo log 和 bin log
undo log:
- undo log 是为了实现事务的原子性, 它相当于记录了每条数据的历史记录; 在事务进行回滚时可以找到历史版本, 并且搭配 readView 可以实现 MVCC
redo log:
- redo log 是为了在数据库崩溃故障后恢复事务状态的日志；因为 sql 语句所有对数据的操作都是先在内存区域 buffer pool 中进行操作的然后同步到硬盘中, 如果突然发生崩溃, 那么内存中的脏页就会丢失从而导致数据丢失; 而 redo log 记录的就是事务对内存中数据页的操作, 如果奔溃了那么可以通过 redo log 进行恢复;
- redo log 是通过两个文件进行存储的; 两个文件可以看作收尾相连的环; 目的是在有限的空间写入数据;
bin log:
bin log 的主要目的是做数据的备份, 主从的同步；
bin log 和 undo log 都是逻辑日志, redo log 是物理日志； redo log 和 undo log 都是 innodb 层面的都是为了服务于事务的;
undo log 和 redo log 的区别
undo log 是记录事务之过程中数据的变化; 如果需要回滚或则事务并发时需用通过 MVCC 都可以定位到数据的历史版本;
redo log 是为了保证事务的持久性, 就算数据库发生了奔溃, 也可以通过 redo log 进行事务的恢复
redo log 和 bin log 的区别是什么? 为什么需要两阶段提交?
redo log 是物理日志它记录的是具体数据页上数据的变化; bin log 是逻辑变化记录的是执行的语句; redo log 是为了保证事务的持久性；bin log 是数据的备份，主从同步；
需要二阶段提交的原因就是为了保证逻辑日志和物理日志的一致性；如果没有二阶段提价的话；如果插入操作先提交了 redo log 此时 undo log 还没有进行写盘此时断电了；再次重启数据库后 bin log 就无法感知导致从数据库比主数据库少一条数据；如果先写入 bin log 再写入 redo log, 如果 bin log 完成写入后崩溃那么 redo log 就无法感知未记录的事务, 就会导致从数据中有这个事务但是主数据中没有
如何通过 bin log 实现主从同步
- 主从服务器建立连接后从节点会开启两个线程 I/O 线程和 SQL 线程；
- 从服务器会通过 IO 线程尝试查看主服务器 bin log 的变化；同样主服务器也会开启 dump 线程与之交互；
- 从服务器如果发现主服务器 bin log 的变化那么就会根据自己目前已经同步的进度开始进行拉去数据；
- 数据被拉起后保存到 relay log 中; 到这一步主从同步的流程就执行完成了
- 最后从节点通过 SQL 线程进行 relay log 日志指令的重现
bin log 的格式
statment： 记录执行的 SQL 语句 row：记录执行语句前后行的变化 mixed：前两者的结合会根据不同的指令选择适合的记录方式
在主从同步的过程中可能出现同一条 sql 在主从数据库执行过程中得到不同的结果, 比如自动生成 id、随机数函数等；解决这个问题只需要将格式改成 row 即可；
提交事务时 Redo log 和 bin log 的写入顺序是怎么样的? 为什么?
2 阶段提交顺序: redo log 写盘并将状态设为已准备状态, 但是尚未提交; 然后 bin log 进行修改写入; 最后 redo log 将状态改为 commit 已提交状态;
这样实现的目的是保证主从一致性;
其他
Mysql 分为哪些层分别有什么作用
连接层：
- 建立与客户端的连接, 身份验证权限校验等工作就在这一层进行
服务层：
- mysql 核心的解析器、优化器、执行器所在的位置；查询和处理数据结果等操作
存储引擎层：
- 负责数据的增删改查操作；事务、锁等机制
innodb 和 MyISAM 索引的区别
- 索引的区别
- MyISAM 的数据和索引是分开存储的而 Innodb 数据和索引是存储在一起的
- MyISAM 这样方式就是非聚簇索引, innodb 是聚簇索引
- innodb 支持事务, MyISAM 不支持
- innodb 最小锁粒度为行锁, MyISAM 为表锁
- MyISAM 维护表的行数; innodb 没有维护, 只能全表扫描统计
- innodb 支持外键; MyISAM 不支持
什么是索引合并原理是什么？
比如一个语句中对两个字段分别创建了索引; 并且查询语句中分别指定了两个字段的条件，那么执行器就会查询出符合条件 1 的记录然后查询符合条件 2 的记录对他们的结果进行取交集；
索引合并就是数据对查询的一种优化，通过交集并集等逻辑运算将多个索引合并层一个复合索引进行快速查找，减少查询返回提升查询效率；
如果发生了索引合并那么在 explain 中的 type 字段就是 index_merge
今日算法
括号生成
LC 22
class Solution {
private List<String> res = new ArrayList<>();
private StringBuilder path = new StringBuilder();
public List<String> generateParenthesis(int n) {
nextLevel(n, n);
return res;
}
private void nextLevel(int restLeft, int restRight){
if(restLeft == 0 && restRight == 0)
res.add(path.toString());
if(restRight < restLeft)
return;
if(restLeft > 0){
path.append('(');
nextLevel(restLeft - 1, restRight);
path.deleteCharAt(path.length() - 1);
}
if(restRight > 0){
path.append(')');
nextLevel(restLeft, restRight - 1);
path.deleteCharAt(path.length() - 1);
}
}
}
合并 K 个升序链表
import org.w3c.dom.NodeList;
import java.util.PriorityQueue;
/**
* Definition for singly-linked list.
* public class ListNode {
* int val;
* ListNode next;
* ListNode() {}
* ListNode(int val) { this.val = val; }
* ListNode(int val, ListNode next) { this.val = val; this.next = next; }
* }
*/
class Solution {
public ListNode mergeKLists(ListNode[] lists) {
PriorityQueue<ListNode> heap = new PriorityQueue<>((n1, n2) -> {
return n1.val - n2.val;
});
for (ListNode listNode : lists) {
if (listNode != null)
heap.offer(listNode);
}
ListNode res = new ListNode();
ListNode cur = res;
while (heap.size() > 0){
ListNode minNode = heap.poll();
cur.next = minNode;
cur = cur.next;
if(minNode.next != null)
heap.offer(minNode.next);
}
return res.next;
}
}
下一个排列
class Solution {
public void nextPermutation(int[] nums) {
int l = nums.length;
if (l == 1) return;
int minPos = l - 2;
while (minPos >= 0 && nums[minPos] >= nums[minPos + 1]) {
minPos--;
}
if (minPos == -1) {
// 目前是最大字典序 逆序即可
reverse(nums, 0, l - 1);
return;
}
int closePos = l - 1;
while (nums[minPos] >= nums[closePos]){
closePos--;
}
swap(nums,minPos,closePos);
reverse(nums, minPos + 1, l - 1);
}
public void swap(int[] nums, int i, int j) {
nums[i] = nums[i] ^ nums[j];
nums[j] = nums[i] ^ nums[j];
nums[i] = nums[i] ^ nums[j];
}
public void reverse(int[] nums, int i, int j) {
while (i < j) {
swap(nums, i++, j--);
}
}
}
最长有效括号
class Solution {
public int longestValidParentheses(String s) {
int l = s.length();
if (l < 2) return 0;
int[] dp = new int[l];
int res = 0;
for (int i = 1; i < l; i++) {
if(s.charAt(i) == ')'){
// 查找与之匹配的左括号
if(i - dp[i - 1] - 1 >= 0 && s.charAt(i - dp[i - 1] - 1) == '('){
dp[i] = dp[i - 1] + 2;
if(i - dp[i] > 0)
dp[i] += dp[i - dp[i]];
}
}
res = Math.max(dp[i], res);
}
return res;
}
}
搜索旋转排序数组
class Solution {
public int search(int[] nums, int target) {
int l = 0;
int r = nums.length - 1;
int m;
while (l <= r){
m = (l + r) >> 1;
if(nums[m] == target)
return m;
if(nums[0] <= nums[m]){
// 说明左边是有序的
if(nums[0] <= target && target < nums[m]){
r = m - 1;
}else{
l = m + 1;
}
}else{
// 说明右边是有序的
if(target <= nums[nums.length - 1] && target > nums[m]){
l = m + 1;
}else{
r = m - 1;
}
}
}
return -1;
}
}
在排序数组中查找元素的第一个和最后一个位置
class Solution {
public int[] searchRange(int[] nums, int target) {
int len = nums.length;
int[] res = new int[]{-1, -1};
if (len < 1) return res;
int l = -1;
int r = len;
int m;
// 求出>=target的范围
while (l + 1 < r) {
m = (l + r) >> 1;
if (nums[m] >= target)
r = m;
else
l = m;
}
if (r == len || nums[r] != target)
return res;
res[0] = r;
l = -1;
r = len;
while (l + 1 < r) {
m = (l + r) >> 1;
if (target >= nums[m])
l = m;
else
r = m;
}
res[1] = l;
return res;
}
}
查看12道真题和解析
淘天集团一面226人在聊