面试_mysql

1. 基础知识
- 1) 数据库三大范式是什么
- 2) mysql有关权限的表
- 3) binlog录入格式
- 4) char、varchar 区别
- 5)int(20)中20的涵义
- 6) 字段为什么要求定义为not null？
- 7) 雪花算法原理？为什么是顺序ID? 如何解决时钟回拨？
- 8) 自增ID与UUID的区别
- 9) 为何用自增主键(自增ID的好处)？
- 10) 什么是临时表，临时表什么时候删除?
- 11) UNION、UNION ALL区别
- 12) SQL语句分类
- 14) in、exists 区别
2. 存储引擎
- 1) InnoDB、MyIsam、Memmry的区别
- 2) MyISAM索引、InnoDB索引的区别
- 3) 存储引擎选择
- 4) [InnoDB引擎的4大特性](https://zhuanlan.zhihu.com/p/109528131)
- 5) InnoDB如何存数据的
- 6) InnoDB索引类型，区别？
3. 索引
- 1) 什么是索引？
- 2) 索引使用场景（重点）
- 3) 索引代价
- 4) 索引分类
- 5) 少建索引的原则
- 6)创建索引的原则（重中之重）
- 7) 索引创建、删除
- 8) 聚簇、非聚簇区别
- 9) 适合使用索引覆盖来优化SQL的场景
- 10) B+树索引、 Hash索引区别
- 11) B+树索引、B树索引区别？为什么使用B+树而不是B树？
- 12) 如何利用索引提升查询性能？
- 13) 索引维护
4. 事务
- 1) 什么是数据库事务？
- 2) 事物的四大特性(ACID)
- 3) [ACID实现原理](https://www.cnblogs.com/kismetv/p/10331633.html)
- 4) 事务的隔离级别
- 5) [四个隔离级别的实现原理](https://blog.csdn.net/QEcode/article/details/97274409)
5. 锁
- 1) InnoDB锁的算法
- 2) 锁类别
- 3) 死锁判定原理和具体场景，死锁怎么解决？
- 4) InnoDB行锁的实现
6. MySQL高并发
- 1) 高并发解决方案
- 2) 分表后的ID怎么保证唯一性？
- 3) mysql主从同步怎么做？

1. 基础知识

1) 数据库三大范式是什么

范式	规则
1NF	列不可以拆分。
2NF	1NF + 非主键列完全依赖于主键（非一部分）
3NF	2NF + 非主键列只依赖于主键

2) mysql有关权限的表

表名	内容
user	记录允许连接到服务器的用户帐号信息，里面的权限是全局级的
db	记录各个帐号在各个数据库上的操作权限
table_priv	记录数据表级的操作权限
columns_priv	记录数据列级的操作权限
host	配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响

3) binlog录入格式

格式	内容
statement	每一条会修改数据的sql都会记录在binlog中
row	不记录 sql 语句上下文相关信息，仅保存哪条记录被修改
mixed	普通操作使用statement记录，当无法使用statement的时候使用row。

4) char、varchar 区别

char	varchar
定长	长可变
存取速度快，便于存储查找	慢
最多存 255，非unicode	最多存 65532，非unicode
英文1字节，中文2字节	均为2字节
长度 < 定长，空格填充	按插入数据的长度来存储

5)int(20)中20的涵义

显示字符的长度。
20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；
不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示

6) 字段为什么要求定义为not null？

null

值会占用更多的字节。

7) 雪花算法原理？为什么是顺序ID? 如何解决时钟回拨？

1bit无用符号位 + 41bit时间戳 + 10bit机器ID + 12bit序列号
时间戳在高位，整个ID都是趋势递增的。
时钟回拨问题的解决方案讨论
- 时间戳自增（彻底解决）
- 缓存历史序列号（缓解）
- 等待时钟校正

8) 自增ID与UUID的区别

UUID只是 全球唯一Id ，在高并发情况下不会出现ID冲突
自增ID
1. 字段长度较uuid小很多， 占空间小
2. 增量增长，按顺序存放，对于检索非常有利，作为聚簇索引 提升查询效率 。

9) 为何用自增主键(自增ID的好处)？

InnoDB使用主键索引，用自增主键，每次插入记录会 顺序添加 到当前索引节点的后续位置
不是自增主键，可能会在中间插入引起B+树的 节点分裂

10) 什么是临时表，临时表什么时候删除?

存储一些中间结果集的表
临时表只在当前连接可见
当关闭连接时，Mysql会自动删除表并释放所有空间。
1. 内存临时表( memory )
2. 磁盘临时表(5.7.6后 innodb ，之前 myisam )

11) UNION、UNION ALL区别

UNION ALL 不会合并重复的记录行
UNION 效率高于 UNION ALL

12) SQL语句分类

数据定义语言DDL： CREATE，DROP，ALTER
数据查询语言DQL： SELECT
数据操纵语言DML： INSERT，UPDATE，DELETE
数据控制功能DCL： GRANT，REVOKE，COMMIT，ROLLBACK

14) in、exists 区别

in：外表和内表作 hash 连接
exists ：对外表作 loop 循环，每次 loop 循环再对内表进行查询。

2. 存储引擎

1) InnoDB、MyIsam、Memmry的区别

种类	InnoDB	MyISAM	Memmry
锁机制	行锁、表锁	表锁	表锁
B+树索引	支持（ `索引组织表` ）	支持（ `堆表` ）	支持
哈希索引	X	X	支持
全文索引	5.6后支持	支持	X
外键	支持	X	X
事务	支持	X	X
记录存储顺序	按插入顺序保存	主键大小有序插入

2) MyISAM索引、InnoDB索引的区别

InnoDB索引	MyISAM索引
`聚簇`	`非聚簇`
`主键` 索引的叶子节点存储着 `行数据` `非主键` 索引的叶子节点存储的是 `主键、带索引的列数据`	叶子节点存储的是 `行数据地址` 需要 `再寻址` 一次才能得到数据

3) 存储引擎选择

默认的 Innodb
1. 更新（删除）频率高
2. 数据的完整性
3. 并发量高，支持事务和外键
4. 崩溃后更容易恢复
MyISAM
1. 读写插入为主的应用
2. 全文搜索
3. 高速存储、检索

4) InnoDB引擎的4大特性

插入缓冲（insert buffer)
二次写(double write)
自适应哈希索引(ahi)
预读(read ahead)

5) InnoDB如何存数据的

5.6 之前系统表空间 , 对应 ibdata1 文件
5.6 之后独立表空间
1. 8.0 之前
  1. ibd 文件里保存的仅仅是该表的数据
  2. frm 文件里保存表结构
2. 8.0 之后, 表结构信息以 SDI 的形式放在了 ibd 文件中

6) InnoDB索引类型，区别？

主键索引	非主键索引
聚簇	辅助
ID主键即可查询出数据行	可能需要回表

3. 索引

1) 什么是索引？

一种特殊的 文件
一种用于快速查找数据的 数据结构

2) 索引使用场景（重点）

where
order by
join

3) 索引代价

占用磁盘空间
新建或修改等操作时，比没有索引或没有建立覆盖索引时的要慢。
索引是有大量数据的时候才建立的，没有大量数据反而会浪费时间。

4) 索引分类

分类标准	分类
数据结构	B+Tree 、 Hash索引、全文索引
物理存储	聚簇索引、非聚簇索引(二级、辅助)
字段特性	普通索引唯一索引（值唯一，允许有空值）、主键索引（不允许有空值）前缀索引（选择索引列的最左n个字符来建立索引）
字段个数	单列索引、联合索引

5) 少建索引的原则

表记录太少
经常插入、删除、修改的表
数据重复且分布平均的表字段。（字段A只有T和F两种值，且每个值的分布概率大约为50%）
经常和主字段一块查询但主字段索引值比较多的表字段

6)创建索引的原则（重中之重）

最左前缀匹配原则
查询频繁的字段
有外键的数据列
字段唯一
查询中排序的字段
查询中统计或分组统计的字段

7) 索引创建、删除

CREATE TABLE 时创建
ALTER TABLE 添加
CREATE INDEX 命令创建
删除普通索引、唯一索引、全文索引： alter table 表名 drop KEY 索引名
删除主键索引： alter table 表名 drop primary key

8) 聚簇、非聚簇区别

聚簇索引	非聚簇索引
顺序存储	无序存储
叶子节点 `数据行`	叶子节点 `索引 + 主键值` （InnoDB）指向存放数据块的 `指针` （MyISAM）
`只能有一个`	多个
`稀疏索引` ，数据页上一级索引存储是 `页指针`	`密集索引` ，数据页上一级索引页为 `每一个数据行存储一条记录`

9) 适合使用索引覆盖来优化SQL的场景

全表count查询优化
列查询回表优化
分页查询

10) B+树索引、 Hash索引区别

Hash索引相比B+树索引的优缺点

不支持 范围查询
不支持 模糊查询
不支持 联合索引的最左匹配规则
不支持 利用索引完成排序
存在 哈希碰撞 问题
避免不了 回表 查询数据
等值查询 效果更好，但是不稳定

11) B+树索引、B树索引区别？为什么使用B+树而不是B树？

特点	BTree	B+Tree
结点	索引 + 数据	`数据仅在叶子结点` ，且叶子结点间存在 `双向链表`
随机检索	支持	支持
`顺序检索`	X	支持
`范围查询`	X	支持
查询效率	更稳定
元素遍历	效率低下
增删文件（节点）的效率	更高
空间利用率更高， `磁盘读写代价更低`	X	支持

12) 如何利用索引提升查询性能？

覆盖索引 ：减少回表次数
联合索引的 最左前缀原则
索引下推 ：节省了一次回表次数
用 联合索引
时

先通过前一个字段的索引找到合适位置之后

sql引擎会
自动判断后一个字段
的值是否符合条件，

如果符合条件的话，
取出主键ID进行回表查询

13) 索引维护

直接优化表顺带会优化索引
删除索引，从新创建

修改索引

ALTER INDEX 索引名 REBUILD ; 
ALTER INDEX 索引名 REBUILD ONLINE; 
ALTER INDEX 索引名 REBUILD ONLINE NOLOGGING;

合并索引：不需额外存储空间，代价较低

4. 事务

1) 什么是数据库事务？

事务是一个 不可分割的数据库操作序列 。
事务是数据库并发控制的基本单位，其执行的结果必须使数据库从 一种一致性状态 变到 另一种一致性状态 。
事务是逻辑上的一组操作， 要么都执行，要么都不执行 。

2) 事物的四大特性(ACID)

原子性 ：事务是最小的执行单位，不允许分割。要么都执行，要么都不执行。
一致性 ：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性 ：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性 ：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响

3) ACID实现原理

原子性： undo log ，事务修改数据库时记录，失败回滚
一致性： 其余三大特性 + 应用层代码控制
隔离性： 锁 + MVCC（隐藏列、基于undo log的版本链、ReadView）
持久性： redo log ，数据修改时记录，事务提交时刷盘，宕机则可读取redo log中的数据来恢复

4) 事务的隔离级别

事务隔离级别	读未提交	读已提交	可重复读	串行化	备注
脏读	√	X	X	X	`读取未提交数据`
不可重复读	√	√	X	X	`前后多次读取，数据内容不一致`
幻读	√	√	√	X	`前后多次读取，数据总量不一致`

5) 四个隔离级别的实现原理

操作	读未提交	读已提交	可重复读	串行化
读	不加锁	不加锁, ReadView	不加锁, ReadView	加锁
写	行锁（排它锁）	行锁, undo log的版本链	行锁, undo log的版本链	加锁

RC 和 RR 不同之处：行记录对于当前事务的

可见性

RC级别对数据的可见性是该数据的 最新记录 ，
RR基本对数据的可见性是 事务开始时，该数据的记录 。

5. 锁

1) InnoDB锁的算法

Record lock： 行锁
Gap lock： 间隙锁 ，锁定一个范围，不包括记录本身
Next-key lock ：record + gap 锁定一个范围，包含记录本身

2) 锁类别

乐观锁： 版本号控制 、 CAS算法原理
悲观锁： 锁
1. 共享锁 （读锁）： in share mode
2. 排他锁 （写锁）： for update

3) 死锁判定原理和具体场景，死锁怎么解决？

死锁判定原理
1. 互斥条件：一个资源每次只能被一个进程使用。
2. 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
3. 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
4. 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
有助于最大限度地降低死锁
1. 顺序访问。
2. 一次请求所有资源
3. 主动释放锁
4. mysql中避免死锁：避免事务中的用户交互。保持事务简短并在一个批处理中。使用低隔离级别。使用绑定连接。
MySQL解决死锁方法
1. 第一种: 查询是否锁表 -->查询进程 --> 杀死进程id kill id
2. 第二种: 查看当前的事务 --> 查看当前锁定的事务 --> 查看当前等锁的事务 --> 杀死进程id

4) InnoDB行锁的实现

基于索引，

for update

6. MySQL高并发

1) 高并发解决方案

水平分库分表 ，由单点分布到多点数据库中，从而降低单点数据库压力。
** 集群**方案：解决DB宕机带来的单点DB不能访问问题。
引入 负载均衡 策略（LoadBalancePolicy简称LB）
主从复制 实现读写 分离策略 ：极大限度提高了应用中Read数据的速度和并发量。无法解决高写入压力
1. 通过 mybatis plugin 拦截sql语句，仅 select 访问salve库
2. plugin 通过 注解 或者 分析语句是读写方法 来选定 主从库
3. 重写一下 DataSourceTransactionManager 来支持事务，将 read-only 的事务扔进读库，其余扔进写库。
使用 redis做一个缓冲操作 ，让请求先访问到redis
1. 延时双删 策略
2. 异步更新缓存 (基于订阅 binlog 的同步机制)

2) 分表后的ID怎么保证唯一性？

设定步长
分布式ID ，如雪花算法
每张表单独新增一个字段作为唯一主键

3) mysql主从同步怎么做？

主从同步 的原理(异步)
1. master提交完事务后，写入binlog
2. slave连接到master，获取binlog
3. master创建dump线程，推送binlog到slave
4. slave启动一个IO线程读取同步过来的master的binlog，记录到relay log中继日志中
5. slave再开启一个sql线程读取relay log事件并在slave执行，完成同步
6. slave记录自己的binglog
主库挂了 怎么处理
- 全同步复制 ：主库写入binlog后 强制同步 日志到从库， 所有从库 都执行完成后才返回给客户端
- 半同步复制 : 解决数据丢失的问题。从库写入日志成功后返回 ACK 确认给主库，主库收到 至少一个从库 的确认就认为写操作完成。
- 并行复制 : 解决从库复制延迟的问题

面试1_mysql1. 基础知识2. 存储引擎3. 索引4. 事务5. 锁6. MySQL高并发

面试_mysql

1. 基础知识

1) 数据库三大范式是什么

2) mysql有关权限的表

3) binlog录入格式

4) char、varchar 区别

5)int(20)中20的涵义

6) 字段为什么要求定义为not null？

7) 雪花算法原理？为什么是顺序ID? 如何解决时钟回拨？

8) 自增ID与UUID的区别

9) 为何用自增主键(自增ID的好处)？

10) 什么是临时表，临时表什么时候删除?

11) UNION、UNION ALL区别

12) SQL语句分类

14) in、exists 区别

2. 存储引擎

1) InnoDB、MyIsam、Memmry的区别

2) MyISAM索引、InnoDB索引的区别

3) 存储引擎选择

4) InnoDB引擎的4大特性

5) InnoDB如何存数据的

6) InnoDB索引类型，区别？

3. 索引

1) 什么是索引？

2) 索引使用场景（重点）

3) 索引代价

4) 索引分类

5) 少建索引的原则

6)创建索引的原则（重中之重）

7) 索引创建、删除

8) 聚簇、非聚簇区别

9) 适合使用索引覆盖来优化SQL的场景

10) B+树索引、 Hash索引区别

11) B+树索引、B树索引区别？为什么使用B+树而不是B树？

12) 如何利用索引提升查询性能？

13) 索引维护

4. 事务

1) 什么是数据库事务？

2) 事物的四大特性(ACID)

3) ACID实现原理

4) 事务的隔离级别

5) 四个隔离级别的实现原理

5. 锁

1) InnoDB锁的算法

2) 锁类别

3) 死锁判定原理和具体场景，死锁怎么解决？

4) InnoDB行锁的实现

6. MySQL高并发

1) 高并发解决方案

2) 分表后的ID怎么保证唯一性？

3) mysql主从同步怎么做？

继续阅读