参考链接：
1. 20个数据库常见面试题讲解
2. MySQL数据库面试题（2020最新版）

小林coding-MySQL
小林coding-Redis

数据库

关系型数据库和非关系型数据库

1. 关系型数据库（MySQL）

• 优点：
  • 二维表格，容易理解
  • 操作方便
  • 易于维护
  • 支持SQL
• 缺点：
  • 读写性能较差
  • 固定的表结构，不够灵活
  • 应对高并发场景，磁盘I/O存在瓶颈
  • 海量数据的读写性能差

2. 非关系型数据库（Redis）

• 优点：
  • 不需要SQL解析，读写性能高
  • 可以使用硬盘或者内存作为载体，速度快
  • 基于键值对，数据没有耦合性，方便扩展
  • 部署简单
• 缺点：
  • 不支持SQL，增加了学习成本
  • 没有事务

数据库高并发环境解决方案

MySQL 高并发环境解决方案：分库、分表、分布式、增加二级缓存。

数据库外键的优缺点

优点：

• 能最大限度的保证数据的一致性和完整性
• 增加ER图的可读性

缺点：

• 影响数据操作的效率
• 增加开发难度，导致表过多

百万级别或以上的数据如何删除

删除数据的时候，同时还需要维护索引，并且删除数据的速度和索引的数量是成正比的。直接删除数据比较耗时。因此

1. 先删除索引
2. 删除其中无用的数据
3. 删除完数据之后再重建索引
4. 上述操作比直接删除数据快很多，还能避免删除数据造成回滚的问题。

如何保证redis和mysql数据一致性

采用以部更新缓存机制（基于MySQL binlog的同步机制）

• 涉及到更新的数据操作时，利用MySQL binlog进行消息的订阅；
• 一旦 MySQL中产生了新的写入、更新、删除操作，就可以把binlog相关的消息推送到Redis；
• Redis再根据binlog中的记录，对Redis进行更新；
• 其实这种机制，很类似MySQL的主从备份机制，因为MySQL的主备也是通过binlog来实现的数据一致性。

数据库三范式

1. 第一范式（确保每列保持原子性）
   第一范式是最基本的范式。如果数据库中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
2. 第二范式（确保表中的每列都与主键相关，完全依赖）
   第二范式需要确保数据库表中的每一列都和主建相关，而不能只与主建的一部分相关（针对联合主键）。
3. 第三范式（确保每列都和主键列直接相关，而不是间接相关，直接依赖）
   第三范式是对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖。

引擎

MySQL的存储引擎 —— InnoDB、MyISAM的区别

从MySQL5.5.5以后，InnoDB是默认引擎。

	InnoDB	MyISAM
事务	支持	不支持
行级锁	支持	不支持
应用	适合频繁修改以及设计安全性较高的应用	适合查询以及插入为主的应用
外键	支持	不支持
索引	聚类索引	非聚类索引

锁

数据库锁

1. 从锁的颗粒进行划分
   按照锁的颗粒对锁进行划分，分为行锁（InnoDB默认加锁方式）、页锁和表锁。

• 行锁就是按照行的粒度对数据进行锁定。锁定力度小，发生锁冲突概率低，可以实现的并发度高，但是对于锁的开销比较大，加锁会比较慢，容易出现死锁情况。
• 页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。
• 表锁就是对数据表进行锁定，锁定粒度很大，同时发生锁冲突的概率也会较高，数据访问的并发度低。不过好处在于对锁的使用开销小，加锁会很快。

2. 从数据库管理的角度对锁进行划分
   从数据库管理的角度对锁进行划分，可以分为共享锁和排它锁。

• 共享锁。共享锁又叫做读锁或S锁，共享锁锁定的资源可以被其他用户读取，但不能修改。在进行SELECT的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。
• 排它锁。排它锁也叫独占锁、写锁或X锁。排它锁锁定的数据只允许进行操作的事务使用，其他事务无法对已锁定的数据进行查询或修改。当我们对数据进行更新，也就是INSERT\DELETE或者UPDATE的时候，数据库也会自动使用排它锁，防止其他事务对该数据行进行操作。

3. 从程序员的角度对锁进行划分
   可以分为乐观锁和悲观锁，乐观锁和悲观锁并不是锁，而是锁的设计思想。

• 乐观锁。乐观锁认为对同一数据的并发操作不会总发生，属于小概率时间，不用每次都上锁，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号的机制来实现。
• 悲观锁。悲观锁对数据被事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。

锁的实现方式

• select … 不加锁
• select … in share mode 加读锁
• select … for update 加写锁

死锁

死锁概念：
是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。

死锁原因：

• 系统资源不足；
• 进程运行推进的顺序不合适；
• 资源分配不当。

产生死锁的四个必要条件：

• 互斥条件：一个资源每次只能被一个进程使用；
• 不可剥夺条件：进程已获得的资源，在末使用完之前，不能强行剥夺；
• 占有且等待条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；
• 循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。

避免死锁的方法：

• 按同一顺序访问对象。
• 避免事务中的用户交互。
• 保持事务简短并在一个批处理中。
• 使用低隔离级别。
• 使用绑定连接。

解决死锁的方法：

• 查出线程并杀死
• 设置锁的超时时间

事务

事务的四大特性ACID

1. 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）： 执行事务前后，数据满足完整性约束，数据库保持一致性状态。比如转账前后，两个的总金额一致；
3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事务并发会产生的问题

1. 脏读（回滚，有无）：
   事务A读了事务B更新的数据，事务B回滚，那么事务A读到的就是脏数据。

要解决脏读现象，就要升级到“读提交”以上的隔离级别。

2. 不可重复读（修改，前后不一致）：
   事务A读取数据，事务B在事务A读取之后对数据做了修改并提交，事务A再读的时候，前后不一致。

要解决不可重复读现象，就要升级到“可重复读”的隔离级别。

3. 幻读（插入，查询结果前后不一致）：
   事务A将数据”1“修改为”2“.事务B插入了一条数据”1“并提交给数据库。而事务A的操作者再查看刚刚修改的数据，发现还是”1“，就发生了幻读。

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读。
针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读。

事务隔离级别

隔离级别从高到低为：

1. 串行化：
   串行执行，能避免所有数据不一致的情况，缺点是会导致系统的并发量大幅度下降。
2. 可重复读（MySQL的默认级别）：
   一个事务一旦开始，该事务的过程中所访问的所有数据不允许被其他事务读写。没办法解决“幻读”的问题。
3. 读提交：
   事务能读取到其他事务提交过的数据。这个隔离级别下，可能会发生“不可重复读”的问题。
4. 读未提交：
   一个事务能读取到其他事务修改过，但是还没有提交的数据。
   数据被其他事务修改过，但是还没有提交，就存在回滚的可能，因此这个ge隔离级别会发生“脏读”的问题。

数据库崩溃时事务的恢复机制

REDO日志、UNDO日志

Mysql 事务是如何实现的？

• 原子性：通过undo log实现的。每条数据变更都伴随一条undo log日志的生成，当系统发生错误或执行回滚根据undo log做逆向操作
• 持久性：通过redo log实现的。redo log记录了数据的修改日志。数据持久化到磁盘，先是储存到缓冲池里，然后缓冲池中的数据定期同步到磁盘中，如果系统宕机，可能会丢失数据，系统重启后会读取redo log恢复数据
• 隔离性：mysql数据库通过MVCC + next-key机制实现了隔离性
• 一致性：以上3大特性，保障了事务的一致性

MVCC 多版本并发控制

采用了MVCC机制后，只有写写之间相互阻塞，其他三种操作都可以并行，这样就可以提高了MySQL的并发性能。

MVCC最大的优势：读不加锁，读写不冲突，极大的增加了系统的并发性能。

可重复读隔离级是由 MVCC（多版本并发控制）实现的，实现的方式是启动事务后，在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，也不会影响查询结果，所以就很好了避免幻读问题。

数据库崩溃时事务的恢复机制（REDO重做日志、UNDO回滚日志、binlog归档日志）

数据库使用REDO日志和UNDO日志实现事务的恢复机制。

UNDO可以保证数据原子性和一致性。这两者的主要区别为

1. undo记录数据修改之前的操作，redo记录磁盘数据将要进行的操作.
2. undo用于数据的回滚操作，和实现一致性读，redo用于前滚数据库操作
3. undo存储在回滚段里，redo存储在重做日志文件里
4. undo用于在多用户并发的系统里保证一致性读，redo用于防止数据丢失

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

redolog 和 binlog 有什么区别

1. 适用对象不同

• binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
• redo log 是 Innodb 存储引擎实现的日志；

2. 文件格式不同

• binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED
• redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；

3. 用途不同

• binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
• redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

4. 用途不同

• binlog 用于备份恢复、主从复制；
• redo log 用于掉电等故障恢复。

如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复吗

不可以用 redo log 文件恢复，只能用 binlog 文件恢复

• redo log 文件是循环擦写，可能会丢失部分数据
• binlog 保存的是全量的日志，如果不小心删库了，可以用 binlog 恢复

索引

MySQL的Hash索引和B+树索引的区别

1. Hash索引只能进行等值查询，而B+树可以进行范围查询：
   这是因为Hash所以指向的数据是无序的，而B+树的叶子节点是有序的链表。
2. Hash索引不支持order by排序，而B+树支持。
   B+树索引数据是有序的，可以起到对该字段 ORDER BY 排序优化的作用
3. Hash索引无法进行模糊查询。而B+树使用 LIKE 进行模糊查询（比如%），可以起到优化的作用
4. Hash索引在等值查询中的效率比B+树高

聚簇索引和非聚簇索引（二级索引）的区别

索引可以分为聚簇索引和非聚簇索引（二级索引），他们的区别在于叶子存放的是什么数据：

• 聚簇索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点；
• 二级索引的叶子节点存放的是主键值，而不是实际数据。

因为实际数据是放在聚簇索引的，所以 InnoDB 一定会为表创建一个聚簇索引，数据在物理上只保存一份，所以聚簇索引只能又一个。

创建聚簇索引的规则为：

1. 如果有主键，默认会使用主键作为聚簇索引的索引键；
2. 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
3. 在上面两个都没有的情况下，InnoDB将自动生成一个隐式自增 id 列作为聚簇索引的索引键。

回表：
如果某个查询语句使用了二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后，需要去聚簇索引中获得数据行，也就是说要查两个B+树才能查到数据，这个过程就叫作「回表」。
索引覆盖：
当查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作「索引覆盖」。

创建索引的原则、注意事项

1. 最左前缀匹配原则：MySQL会一直向右匹配指导遇到范围查询（>, <, between, like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2. 较频繁查询的字段才去建立索引。
3. 更新频繁的字段不适合建立索引。
4. 区分度低的（如性别只有男、女、未知）不适合做索引列。
5. 尽量扩展，而不是新建索引。
6. 有外键约束的字段一定要建立索引。
7. 定义为 text、image和bit数据类型的列不要建立索引。

什么是前缀索引？最左前缀原则？

前缀索引也是局部索引，比如给身份证的前10位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。

前缀索引能够有效减少索引文件的大小，但是不能在 order by 和 group by 中触发前缀索引。

使用场景为：当字符串本身比较长，前几个字符就开始不同，就适合用前缀索引。

为什么选择 B+ 树作为索引的数据结构

1. I/O 次数少
   B+树只在叶子节点存储数据，而B+树的非叶子节点也要存储数据，所以B+树更节省空间，相同的磁盘I/O次数下，就能查询更多的节点。

2. 查询效率稳定
   B+树更矮胖，由于数据只存在于叶子节点上，所以查找效率固定为 O(logn)

3. B+ 树利于范围查询
   B+ 树叶子节点之间用链表有序连接，所以扫描全部数据只需要扫描一遍叶子节点，利于扫库和范围查询；B树由于非叶子节点也存数据，所以只能通过中序遍历按序来扫描。因此B+树的效率更高。

什么时候需要建立索引

索引的优点：加速查询
索引的缺点：

1. 占用物理空间，数量越大，占用空间越大
2. 创建索引和维护索引要耗费时间，随数据量的增加而增大
3. 会降低表的增删改的效率，每次增删改都需要动态维护索引

什么时候适用索引？

1. 具有唯一性限制的字段
2. where、group by 和 order by 的字段

什么时候不需要创建索引？

1. 区分度不大的字段，例如性别字段，只有男女
2. 表数据太少的时候，不需要创建索引
3. 经常更新的字段不需要创建索引

优化索引的方法？

1. 前缀索引优化：用字段中字符串的前几个字符建立索引，减少索引项的大小。
2. 覆盖索引优化：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
3. 主键索引自增：因为B+树索引是按顺序存放的，如果按顺序添加，就不需要移动已有的数据，插入效率增加。
4. 索引最好设置为 NOT NULL：索引列存在 NULL 会使索引复杂，更加难以优化。
5. 避免索引失效：在写SQL语句的时候避免索引失效
   ①使用左模糊或者左右模糊，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效；
   ②当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
   ③联合索引要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效；
   ④在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

索引失效的情况有哪些

1. sql语句中被索引字段与所对应值的类型不匹配时，会进行隐式类型转换，导致索引失效；
2. 被索引字段使用了表达式计算；
3. 索引字段使用了函数；
4. like 关键字后使用左模糊匹配 ‘%xx’
5. 被使用的索引字段，不是联合索引的最左字段。

查询

查询语句不同关键字（where、jion、limit、group by、having等等）执行先后顺序?

select from > where > group by > having > order by

MySQL 什么情况下会造成慢查询，慢查询怎么处理？

相应超时会产生慢查询日志，一般有以下情况会造成慢查询：

• 没有设置索引，或查询没有用到索引
• I/O吞吐量过小
• 内存不足
• 网络速度慢
• 查询的数据量过大
• 锁或者死锁
• 返回了不必要的行或列
• 查询语句存在问题，需要优化

处理方法：

• 把数据、日志、索引放到不同的I/O设备上，增加读取速度
• 纵向、横向分割表，减少表的尺寸
• 升级硬件
• 根据查询条件，建立索引，索引优化
• 提高网速
• 扩大服务器内存
• 分库分表

查询优化方法

1. 使用索引
在经常查询的字段上建立索引，首先考虑在where、order by、group by 涉及的列上建立索引

2. 优化 SQL 语句

可以通过 explain 来查看 SQL 语句的执行效果，可以帮助选择更好的索引和优化查询语句。
explain select * from news;

• 不要返回用不到的字段；
• 不在索引列做运算或者使用函数；
• 查询尽可能使用limit减少返回的行数，减少数据传输时间和带宽浪费。

3. 优化数据库表
①分表：

• 垂直拆分：将主键和一些列放在一个表中，将主键和剩下的列放在另一个表中，可以把常用的和不常用的区分开来；
• 水平拆分：根据一行或者多行数据的值，把数据行放到两个独立的表中。

4. 硬件优化

• CPU 优化：选择多核和主频高的CPU
• 内存优化：内存大
• 磁盘I/O优化：

5. 应用优化

• 使用数据库连接池
• 使用查询缓存

执行一条 select 期间发生了什么

MySQL架构共分为两层：Server 层和存储引擎层

• Server 层负责建立连接、分析和执行 SQL。
• 存储引擎层负责数据的存储和提取。

第一步：连接器
连接 MySQL 服务需要经过三次握手，因为 MySQL 是基于 TCP 协议进行传输的。

第二步：查询缓存
如果是 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，查询缓存以 key-value 形式保存在内存中。

第三步：解析 SQL

1. 词法分析：根据关键词构建语法树
2. 语法分析：根据此法分析的结果，语法解析器判断 SQL 语句是否满足语法规则

第四步：执行 SQL

1. prepare 阶段，也就是预处理阶段，查询字段是否存在；
2. optimize 阶段，也就是优化阶段，如果有多个索引，考虑使用什么索引；
3. execute 阶段，也就是执行阶段，和存储引擎交互；

小林coding：执行一条 select 期间发生了什么

SQL的生命周期？

1. 应用服务器与数据库服务器建立一个连接
2. 数据库进程拿到请求sql
3. 查询缓存
4. 解析并生成执行计划，执行
5. 读取数据到内存并进行逻辑处理
6. 通过步骤一的连接，发送结果到客户端
7. 关掉连接，释放资源

MySQL的加载机制

MySQL执行器会与缓冲池做连接，缓冲池存储包括磁盘文件数据、undo log日志、redo log 日志、binlog 日志，在内存中开辟一个空间作为缓冲池，缓冲池能够避免每次都去访问磁盘，提高数据的访问速度。

count(*)、count(1)、count(主键字段)和count(字段)的性能差异

哪种 count 性能最好？
count(*) = count(1) > count(主键字段) > count(字段)

• count(1)或count(*) 不会读取记录中的任何字段的值
• count(*) 其实就是 count(0)，执行过程基本和count(1)一样，没有性能差异
• count(主键字段)
• count(字段) 会用全表扫描的方式来计数

删除一张表的方法delete、drop、truncate

delete、drop、truncate 这三种方法都可以删除一张表，但是有所区别

1. 效率：
   delete效率低、drop中等、truncate最快。delete每次从表中删除一行数据，并将日志记录到日志中保存以便回滚，会触发trigger。truncate 一次性删除所有数据，并不记录到日志中，所以truncate是不能恢复的，不会触发trigger，所以快很多。

2. 表和索引占用空间：

• drop：删除内容和定义，释放空间。（表结构和数据一同删除）
• truncate：删除内容，释放空间，但不删除定义。（表结构还在，数据删除）
• delete：删除内容，不删除定义，也不释放空间。

数据库查询的连接方式

1. 内连接（等值查询）：
   关键字，[inner] join
   从左表中取出每一条记录，去右表中与所有的记录进行匹配； 匹配必须是某个条件是左表中与右表中相同才会保留结果，否则不保留。

2. 外连接（左连接和右连接）：
   关键字，outer join
   以某张表为主，取出里面的所有记录， 然后每条与另外一张表进行连接， 不管能不能匹配上条件，最终都会保留， 能匹配，正确保留； 不能匹配其他表的字段都置空null。

3. 交叉连接（笛卡尔积，没有则存为null）：
   关键字，cross join

交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

左连接和右连接的区别

左连接：只要左边表有数据就能检索出来；
右连接：只要右边表右数据就能检索出来；

举例：左边表有4个数据，右边表有三个数据，

左连接：SELECT * FROM t_left_tab a LEFT JOIN t_right_tab b ON a.id = b.id;

右连接：SELECT * FROM t_right_tab a RIGHT JOIN t_left_tab b ON a.id = b.id;