面试5_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的涵义

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

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

null值会占用更多的字节。

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

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

8) 自增ID与UUID的区别

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

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

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

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

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

11) UNION、UNION ALL区别

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

12) SQL语句分类

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

14) in、exists 区别

1. in：外表和内表作 hash 连接
2. 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) 存储引擎选择

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

4) InnoDB引擎的4大特性

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

5) InnoDB如何存数据的

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

6) InnoDB索引类型，区别？

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

---

3. 索引

1) 什么是索引？

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

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

1. where
2. order by
3. join

3) 索引代价

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

4) 索引分类

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

5) 少建索引的原则

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

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

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

7) 索引创建、删除

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

8) 聚簇、非聚簇区别

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

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

1. 全表count查询优化
2. 列查询回表优化
3. 分页查询

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

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

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

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

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

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

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

   用联合索引时
   先通过前一个字段的索引找到合适位置之后
   sql引擎会自动判断后一个字段的值是否符合条件，
   如果符合条件的话，取出主键ID进行回表查询

13) 索引维护

1. 直接优化表 顺带会优化索引

   optimize table 表名;
   

2. 删除索引，从新创建
3. 修改索引

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

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

   alter index 索引名 coalesce;
   

---

4. 慢SQL优化

1) 开启慢查询

1. 修改配置文件 my.ini

   [mysqlld]
   # 5.5及以上版本配置如下选项
   # 定义查过多少秒的查询算是慢查询，我这里定义的是2秒
   long_query_time=2
   # 开启慢查询
   slow-query-log=On
   # 慢查询日志
   slow_query_log_file="mysql_slow_query.log"
   // 记录下没有使用索引的query
   log-query-not-using-indexestpspb16glos dndnorte/t
   

2. 命令行启动

   set global slow_query_log=ON
   set global long_query_time = 3600;
   set global log_querise_not_using_indexes=ON;
   

2) 分析慢SQL

1. show processlist;定位慢SQL
2. explain 分析执行计划

3) 优化方案

索引优化

1. 避免索引失效
2. 索引建立原则
3. 索引覆盖
4. 索引下推
5. 组合索引符合最左匹配原则
6. 写多读少的场景，选择普通索引
   • 更新时，普通索引可用change buffer进行优化，减少磁盘IO
   • 普通索引将更新操作记录到change buffer进行优化，到需要查询时再将数据读到内存再进行修改，以此减少磁盘IO。

sql语句优化

数据库结构优化

1. 将字段多的表分解成多个表

   有些字段使用频率高，有些低，数据量大时，sql语句会因使用频率低的存在而变慢，可以考虑分开。

2. 常联合查询的表，可考虑建立中间表

优化器优化

1. 使用 MRR优化器

   1. 原理

      MRR 【Multi-Range Read】将ID或键值读到buffer排序，
      把 「随机磁盘读」转化为「顺序磁盘读」 ，减少磁盘IO，
      从而提高了索引查询的性能。

   2. set optimizer_switch='mrr=on';启动

   3. Myisam，获取完整数据前，先按 rowid 排序，再顺序读取。

   4. Innodb，则先按聚簇索引键值排序，再顺序读取。

架构优化: 读写分离（主写从读）

5. 事务

1) 什么是数据库事务？

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

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

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

3) ACID实现原理

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

4) 事务的隔离级别

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

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

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

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

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

---

6. 锁

1) InnoDB锁的算法

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

2) 锁类别

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

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

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

4) InnoDB行锁的实现

基于索引，for update

---

7. MySQL高并发

1) 高并发解决方案

1. 水平分库分表，由单点分布到多点数据库中，从而降低单点数据库压力。

2. ** 集群**方案：解决DB宕机带来的单点DB不能访问问题。

3. 引入负载均衡策略（LoadBalancePolicy简称LB）

4. 主从复制实现读写分离策略：极大限度提高了应用中Read数据的速度和并发量。无法解决高写入压力

   1. 通过mybatis plugin拦截sql语句，仅 select访问salve库
   2. plugin 通过注解 或者 分析语句是读写方法来选定 主从库
   3. 重写一下DataSourceTransactionManager来支持事务， 将read-only的事务扔进读库， 其余扔进写库。

5. 使用redis做一个缓冲操作，让请求先访问到redis

   1. 延时双删策略
   2. 异步更新缓存(基于订阅 binlog 的同步机制)

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

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

3) mysql主从同步怎么做？

1. 主从同步的原理(异步)

   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

2. 主库挂了怎么处理

   • 全同步复制：主库写入binlog后强制同步日志到从库，所有从库都执行完成后才返回给客户端

   • 半同步复制: 解决数据丢失的问题。从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

3. 从库复制延迟：并行复制