说说三大范式个人怎么理解的？

第一范式：每列不可再分

第二范式：在第一范式基础上，非主键列全部依赖于主键（可以接受冗余）

第三范式：在第二范式基础上，不依赖其他非主键，也就是消除冗余。比如用户表一个人的id作为主键，部门名称就违反了3NF，因为部门名称依赖于部门id，不完全依赖于id

一般满足3NF就可以了，根据业务的不同来适当进行字段冗余，减少表之间的连接，减少IO，提高查询效率

innodb的聚集索引与非聚集索引

聚集索引：表记录顺序与索引记录顺序一致。找到了索引就相当于找到了数据。所以一般来说主键就是
非聚集索引：不一致，找到索引没找到数据，根据索引上的指在回表查询

非聚集举例：select no,name from student where no = ‘test’。查到no的主键，再根据主键查一次

对innodb了解吗

innodb底层结构使用B+树
为什么不用红黑树或者AVL？
因为是二叉树的变种，而B树是多叉树，一个节点能存更多的信息，所以在相同的数据下，树的高度更低，IO次数更少
为什么不用B树？
B+的非叶子节点不存数据，在相同数据下，高度更低，IO更少

聚集索引特点

  • 聚集索引只能有一个，非可以有多个
  • 聚集物理连续，非逻辑连续

b树与b+的区别

  • b树每个节点都存数据，b+只有叶子节点
  • b+降低了b的高度，减少查询的IO时间
  • b树和b+树都是平衡树，但是b树要求每个索引后面直接跟着数据，b+树则是非叶子结点会冗余到下一层，直到叶子结点层再追加数据。mysql的innodb页默认大小为16kb，如果不跟数据只存索引，一个节点可以多存更多的索引，最后达到更多索引全放内存里，加快速度
  • InnoDB存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值+指针，在B+树中叶子节点存放数据，非叶子节点存放键值+指针。
  • 索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据

innodb与myisam的区别

  • m支持表锁，i另外还支持行锁
  • m不支持事务，i支持
  • m不支持外键，i支持
  • m不支持崩溃恢复，i支持（依赖于redo log（重做日志））这也是事务持久性的体现。（undo日志保证原子性）

innodb引擎的四大特性

  • 插入缓冲（insert buffer）
  • 二次写（double write）
  • 自适应hash（ahi）
  • 预读（read ahead）

mysql事务隔离级别（与spring一致）

由低到高

  • READ-UNCOMMITTED(读取未提交)：导致脏读、不可复读、幻读
  • READ-COMMITTED(读取已提交)：可阻止脏读，导致不可复读、幻读
  • REPEATABLE-READ(可重复读)：对同一个字段怎么读都是一致的。可阻止脏读、不可复读。导致幻读
  • SERIALIZABLE(可串行化)：全部阻止。一般用于分布式事务

脏读：A事务读取数据进行修改还没提交，B事务访问该数据，这个数据是还未提交的，所以是脏数据
不可重复读：A事务多次读同一条数据，如果在此期间B事务修改了数据，会导致A事务读取到的数据不一致—重点在修改
幻读：在A事务查询过程中，B事务插入几条数据，导致A发现了一些本来不存在的记录，如同幻觉—重点在新增、删除

mysql事务特性

ACID
原子性（Atomicity）:要么同时成功，要么同时失败。由undo log保证，记录修改前的信息
一致性（Consisency）：应该由代码去保证，出现异常就需要回滚，而不是强行提交
隔离性（Isolation）：并发执行，互不干扰，如果在同一时刻操纵同一数据，可能就会出现脏，幻，重复读的问题
持久性（Durability）：数据持久化在硬盘上。用redo log保证，如果挂了可以崩溃恢复

怎么实现隔离级别的

通过MVCC（多版本并发控制）来实现的
原理主要通过read view和undo log
read view：聚集索引都包含下面两个必要的隐藏列
trx_id：一个事务每次对某条聚集索引记录进行改动时，都会把该事务的id赋值给trx_id隐藏列
roll_pointter：每次对某条聚集索引记录进行改动时，都会把旧的版本写入undo日志中，这个隐藏列就相当于一个指针，通过他找到该记录修改前的信息
undo log存储了多个版本的历史数据，根据规则去读取某一历史版本的数据，这样就可以在无锁的情况下实现读写并行，提高数据库性能

说说mysql（innodb）的常见锁

  • 行锁：指命中索引，锁的是索引节点，如果没有命中索引，那么就是锁的整张表，也就是表锁
  • 行锁又可以分为读锁（共享锁，S锁）和写锁（排他锁，X锁）
  • 共享锁又称读锁。若事务A加上S锁，其他事务只能读，不能修改（加X锁）
  • 排他锁又称写锁。若事务A加上X锁，不能读也不能写
  • 表锁：锁整张表，不会死锁，容易锁冲突，性能相对较低
  • 乐观锁（需自己实现）通过版本号去实现，减少上锁开销，适合于读多写少的情况

说说你对mysql是怎么调优的

  1. explain查看sql是否走了索引
  2. 是否索引失效，造成的原因是函数 / 语法 / 计算等
  3. 是否select了过多的字段
  4. join的表是否过多，加大join buffer
  5. 是否进行了排序，加大sort buffer
  6. 通过优化逻辑来提高查询速度，比如深分页的问题
  7. 数据过多，分库分表
  8. 机器配置导致性能过低，适当增加资源
  9. 本地缓存，分布式缓存

如果走索引线上还是慢怎么办

  • 这种情况一般是数据查询量大导致的，首先考虑把旧数据删除（挪到Hive中），数据量降低，自然就快了
  • 如果不删除，那么在访问数据库之前，能不能加一层缓存
  • 如果有字符串检索的场景，可以将表中的数据导入ElasticSearch，后续线上查询就可以直接走了（会有同步程序）
  • 如果还不是就得考虑做聚合表的，请求查聚合表，不走原表

如果除了读，提交的也很慢怎么办

  • 如果是单库，可以考虑主从读写分离，主写从读，由主库发送bin log保证数据一致性（异步）
  • 如果主从还不行，就需要考虑分库分表了。比如订单表进行拆分，这样明显的好处就是分摊请求，一般按照userId作为分库分表的键
  • 分库分表的id一般可以采用雪花算法，mysql自增

分库分表怎么进行数据迁移？

  • 增量信息都写一份
  • 将旧数据移入新表中
  • 开启双读（用于过度）
  • 读流量切新表
  • 停止老表的写入

你们使用的哪种隔离级别

读取已提交，可重复读可能会导致间隙锁导致死锁
MySQL刚开始bin log没有row模式，在读取已提交可能会存在主从不一致的问题，所以默认级别为可重复读

谈谈mysql的架构

20211025112150

总体上分为server层和存储引擎层

server层：跨存储引擎功能都在这一层实现，比如存储过程，触发器，视图，函数，还有bin log日志

存储引擎层：负责数据的存储和读取，采用可替换式插件架构，支持innodb，myisam等多个存储引擎，redolog是innodb的特有引擎

基本组件

连接 -> 分析 -> 优化 -> 执行

连接器：主要与身份验证和权限功能相关，类似门卫角色。主要负责登录数据库，进行用户身份认证，如果认证成功，会连接权限表查询用户权限，只要链接不断开，该用户权限修改不受影响

查询缓存（mysql8移除）：用来缓存select语句结果集。实际业务如果更新比较频繁，查询不建议使用缓存，因为场景比较少，所以8以后进行了移除

分析器：如果没有命中缓存，交给分析器，主要用来解析sql语句是来干嘛的，检查词法和语法

优化器：选择一个最优方案去执行。逻辑转换（化简表达式）代价优化（比如多个索引时如何选择，多表查询如何选择关联顺序）

执行器：执行优化后的语句，执行前校验用户权限，没有返回错误信息，有调用存储引擎，返回执行结果

sql语句如何执行

比如一个select语句select * from tb_student A where A.age='18' and A.name=' 张三 ';

首先检查是否有权限
如果有会查询缓存，如果没有通过分析器去进行词法分析，提取关键元素select，表名，需要查询的列，条件。
然后在去检查语法，比如关键词是否正确，没有问题进入优化器

如上述语句有两个条件，先查前面在查后面，或者反过来，优化器会选择一个效率比较高的方法

然后进行权限校验，没有返回错误信息，有执行返回结果

如果是一个更新语句update tb_student A set A.age='19' where A.name=' 张三 ';

先查询到这个语句，有缓存则用缓存

拿到查询的结果，将age改为19，innodb将数据保存在内存中，同时记录redo log，此时该日志为prepare状态，然后告诉执行器执行完毕
执行器收到通知记录binlog，调用引擎，提交redo log变更为提交状态

innodb通过redo log来支持事务，如果redo log与bin log（归档，用来备份）只用一个会出现数据不一致的问题

机器异常重启，redo log用来恢复数据，bin log没有记录，在备份的时候会出现数据不一致
或
机器异常重启，写完bin log，机器无法恢复数据，但备份又有记录，所以也会出现不一致的情况

redo log：用于崩溃恢复
undo log：用于保证原子性
bin log：用于归档备份记录

详情见这篇文章

什么是MVCC，说说它的原理？

MVCC（Multi-Version Concurrency COntrol）多版本并发控制。
通过版本链，实现多版本，可并发的读写，写读（readView生成策略不同影响）
在innodb中有三个基础点

  1. 隐式字段：包含隐式主键，事务id等
  2. undo log。事务对同一记录的修改，会导致undo log成为一张线性表，链首位最早记录
  3. ReadView
  4. 已提交隔离级别在每次查询都会生成一个独立的readview，而可重复读只会用第一次生成的readview。

你们数据量多大？分库分表怎么做的？

先垂直后水平
垂直分表 — 拆分字段
水平分表 — 数据迁移

分表怎么保证id唯一？
设置步长。分布式id（比如雪花算法）。主键不再使用，设置新字段作为唯一主键使用

分表后非sharing-key怎么处理？
做映射 + 数仓大宽表 + 异步查询

多少条数据算大表

一般来说，B+树的高度不超过3，每一层的索引格个数为16kb/（8（主键大小） + 6（页地址大小））或者
16kb /（4+6）算下来就是一千多 x 一千多 x 16（假设一条记录是1k）
就是2000万 — 4000万行记录

谈谈mysql中的锁

共享锁又称读锁（s锁），能访问，不能修改
排他锁又称写锁（x锁），不能与其他锁并存

表锁：锁表，开销大，并发度最低
行锁：锁行，不对其他行有影响，开销小，并发度高
页锁：锁页，处于之间，并发度一般