【八股文】Mysql篇

1. 数据库的三大范式是什么

1. 确保每列保持原子性（第一范式）

所有字段值都是不可分解的原子值，比如说code和name是两个字段，不能合并成一个

2. 确保表中的每列都和主键相关（第二范式）

意思就是不要冗余字段，所属单位就和订单编号无关（主键）

3. 确保每列和主键是直接相关（第三范式）

确保每列都和主键列直接相关,而不是间接相关

2. Mysql的存储引擎

1. Mysql的默认存储引擎

现在是InnoDB

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎
MySQL 5.5 之后，InnoDB 引擎是 MySQL 的默认存储引擎

2. InnoDB和MyISAM的区别

1. 是否支持行级锁

MyISAM：只支持表锁
InnoDB：支持表锁和行锁，默认是支持行锁的（采用MVCC来支持高并发）

2. 是否支持事务

MyISAM：不支持事务
InnoDB：支持事务

3. 是否支持外键

MyISAM：不支持
InnoDB：支持

4. 是否支持MVCC

MyISAM：不支持
InnoDB：支持，MVCC下面我会讲的

5. 是否支持数据恢复

MyISAM：不支持
InnoDB：支持，InnoDB引擎会记录日志，通过redolog重做日志 操作可以恢复数据

3. 存储引擎的选择

在日常开发业务中，基本都有涉及数据库事务的，所以选择InooDB，对于一些特定的业务如果不需要事务（如只需要读的业务，查字典业务？），且不需要数据恢复的话，可以选择MyISAM

4. 硬盘存储结构

1. MyISAM：3个文件

表定义文件、索引文件、数据文件

2. InnoDB：2个文件

1. Frm文件：表定义文件

2. Idb文件：数据和索引存储的文件（这是区别！）

5. 索引结构不同

1. MyISAM（非聚集索引）

B+树的叶子节点存放的是物理地址，指向数据文件的数据

2. InnoDB（聚集索引）

• 聚集索引的B+树的叶子节点直接存放真实数据的
• 辅助索引的B+树叶子节点存放的是主键Id值
  

3. 什么是MVCC（多版本并发控制）

关键字：快照读、事务的隔离性（ACID）、trx_id、roll_pointer、undolog、版本链、readView、判断逻辑、判断顺序

题外话：当前读和快照读

1. 当前读（行锁 + 间隙锁）

就是读取当前数据的最新记录，会加锁，为了保证其他事务不能修改记录（如select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)）

当前读由行锁+间隙锁实现

2. 快照读（mvcc + undolog）

不加锁的select操作就是快照读（隔离级别不能是串行化，如果是串行化快照读就变成了当前读）

快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

图片链接：https://www.bilibili.com/video/BV1t5411u7Fg?spm_id_from=333.337.search-card.all.click

1. 定义（Mutli Version Concurreny Control）

参考链接：mysql MVCC+间隙锁解决幻读理解

MVCC其实就是一个多版本并发控制，即多个不同版本的数据实现并发控制的技术，其基本思想是为每次事务生成一个新版本的数据，在读数据时选择不同版本的数据即可以实现对事务结果的完整性读取。

2. MVCC的组成要素

1. 每条记录有三个隐藏列

1. 事务ID（trx_id）

记录创建这条记录/最后一次修改该记录的事务ID

2. 回滚指针（roll_pointer）

指向undolog日志的上一个版本

3. 隐藏主键（row_id）

隐含的自增ID。现在不知道干嘛用的

2. undolog日志

每次update操作都会生成一条undo_log日志，分为insert undo log和update undo log

1. 分类

1. insert undo_log（insert操作，给自己事务看的）

如insert操作，只针对当前事务，在insert操作后产生undo_log记录，在事务提交后删除undo_log记录，说白了就是给当前事务自己看的.

2. update undo_log（update、delete操作）

update 和 delete操作，会根据隔离级别不同事务版本的数据可见性不同

注意：update操作生成undolog日志，select操作生成readView读视图对象！两个生成时机要区分开

3. 版本链

通过回滚指针将undolog日志连接起来，形成的就叫做版本链

3. readView(读视图)

1. 组成

1. m_ids（存活的事务id列表）

表示生成readView时，当前系统中活跃的读写事务的事务id列表

白话文：存活、活跃，指的就是未commit

2. min_trx_id（m_ids中的最小事务id）

表示生成readView时，当前系统中活跃的读写事务中最小的事务id

3. max_trx_id（新的事务id值）

表示生readView时，系统中应该分配给下一个事务的id值

4. creator_trx_id（生成这个readView的事务id）

表示生成这个readView的事务id

2. 有什么作用

让我知道 我在这个版本链中选择哪一条undolog记录

3. readView是如何判断版本链中的那个版本可用的

每一个undolog有自己的事务id，叫做trx_id

图片链接：https://www.bilibili.com/video/BV1t5411u7Fg?spm_id_from=333.337.search-card.all.click

1.什么时候生成Read-View？

要看数据库的隔离级别：

• 可重复读：返回的是第一次查询生成的【read-view】
• 读已提交每次select时都会重新生成一个新的【read-view】

3. MVCC实现原理

查询的时候会读取出【read-view】，并根据read-view从undo_log日志中最新的记录依次往下找

有点晦涩难懂：具体的操作看这篇文章，写的非常棒！拉倒吧，还是看B站懂得快
https://www.cnblogs.com/boluopabo/p/13111961.html

5. MVCC主要有什么作用

1. 非阻塞的并发读写

提高并发读写性能，操作时会生成事务id，能够更好地解决读-写冲突，不加锁、非阻塞的并发读

2. 实现读已提交和可重复读

MVCC只能实现读已提交和可重复读；如果是读未提交，那么每次查询都能获取最新的修改值。

1. 问：MVCC如何实现RC（以select为单位的）

读已提交生成readView是以select为单位的，每次select时都会重新生成一个新的【read-view】，然后去【undo】日志中寻找符合结果的一条数据，

2. 问：MVCC如何实现RR（以事务为单位的）

可重复读生成readView是以事务为单位的，第一次查询的时候生成一个新的【read-view】，后面的查询会继续沿用第一次的【read-view】，然后找出一个可读的数据

4. Mysql的隔离级别，以及存在的并发事务问题

1. 数据库在并发情况下的常见操作

1. 读-读

不存在任何问题，也不需要并发控制

2. 读-写

有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、不可重复读、幻读

3. 写-写

有线程安全问题，可能会存在更新丢失问题

2. Mysql的隔离级别以及解决的问题

1. READ-UNCOMMITTED（读未提交）

可能导致的问题：脏读
解决：用行锁，隔离级别升级，读已提交或以上

2. READ-COMMITTED（读已提交）

可能导致的问题：不可重复读
解决：隔离级别升级，升级到可重复读或以上

3. REPEATABLE-READ（可重复读，默认隔离级别）

可能导致的问题：幻读

有疑问：有人说，在innodb存储引擎下，默认开启间隙锁，是能解决幻读的问题的

解决：隔离级别升级，升级到串行化

定义：可重复读指的是在一个事务里，我两次select查询到的数据应该是一样的

4. SERIALIZABLE（串行化）

可避免幻读。读加共享锁，写加排他锁。这样读取事务可以并发，但是读写，写写事务之间都是互斥的

3. 问：不可重复读和幻读的区别是什么？

维度不同，不可重复读是针对某个数据来说的，幻读针对某一行来说的

• 不可重复读侧重于update这种操作，同一条数据前后读起来不一样的情况,
• 幻读侧重于insert、delete这种操作，前后两次select 数据的数量会发生变化

5. 索引

1. 索引是什么

索引是一种优化查询的数据结构，是表的一部分，记录这表里所有记录的引用指针，可以理解为一本书的目录

2. 索引的优点（加快查询效率）

1. 查询效率变快
2. 唯一索引可以保证数据行的唯一性
3. 加速表与表之间的连接
4. 在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间

3. 索引的缺点（占用空间、更新变慢）

1. 创建索引和维护索引需要时间，随数据量的增加而增加
2. 索引占用空间
3. 更新数据的同时会更新索引，降低了更新的速度

4. 索引的类型

1. hash

HASH的唯一（几乎100%的唯一）及类似键值对的形式，特点：“=”和“in”条件下高效，对于范围查询、排序效率不高

2. 平衡二叉树AVL

1. 定义

左子树和右子树的高度差不会超过1（通过左旋右旋来实现的）

2. 问题

1. 随着数据量的增加，树的高度会越高，查询的效率会变慢
2. 对于范围查询，需要用到回溯算法进行查找，效率特别慢

3. B树

1. 定义

每个节点最多可以存放2个数据，从而降低了树的高度，但对于范围查询还是需要回溯的

2. 特点

• 每个节点都存储指针和data，所有节点组成这棵树
• 叶子节点指针为null
  
  图片链接：https://blog.csdn.net/qq_35529931/article/details/88925462

3. B+树（mysql索引推荐的存储结构）

1. 定义

一个节点存放2个值，叶子节点冗余存放数据，且根据从小到大形成一个单向链表

2. 特点

• 只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针
• 其他节点只存储指针，不存data
• 叶子节点还增加了顺序访问的指针
  
  图片链接：https://blog.csdn.net/qq_35529931/article/details/88925462

5. 索引的种类

1. 聚集索引（如主键索引、唯一的隐式非空索引）

1. 定义（数据和索引放到一起）

在数据库里面，所有行数都会按照主键索引进行排序，一张表就只有一个聚集索引。

白话文：可以没有主键索引，但一定会有聚集索引，InnoDB才有聚集索引

2. 非聚集索引（数据和索引分开）

就是给普通字段加上索引。

3. 联合索引

就是好几个字段组成的索引，称为联合索引。
联合索引遵从最左前缀原则，注意这里的最左前缀和where条件的先后顺序没什么关系的，mysql有个优化器会自动调整这个顺序，这个题目面试问到了，谨记谨记！

6. 什么时候需要创建索引

• 主键自动建立唯一索引（ID）

• 频繁作为查询条件的字段应该创建索引、一般创建联合索引（Where）

• 查询中排序的字段创建索引将大大提高排序的速度（Order By）

• 查询中统计或者分组的字段（Group By）

7. 什么时候不需要创建索引

• 频繁更新的字段不适合创建索引
• where条件里用不到的字段，不创建索引
• 表记录太少，不需要创建索引
• 数据离散度小的字段，不需要创建索引，例如性别

8. 哪些情况会不走索引

1. Like：%在前面的话不走索引，%在后面的话还是会走索引的
2. 索引列有计算操作，不走索引
3. 索引列使用了函数，不走索引
4. 索引列使用了不等（!=）符号，不走索引
5. 不满足联合索引的最左前缀原则，不走索引（最重要！原理就靠它了）
6. or操作，不走索引（可以用union替代）
7. 索引列的字段具体值为null，不走索引，进行全表扫描（很关键）
8. 字符串列与数字直接比较，字段属性是字符，但是你用数据类型去比较，不走索引（因为隐式类型转换，mysql5.7之后是可以使用到索引的）
9. order by操作的索引列如果也在where条件里，则不走索引
10. 对于联合索引，范围查询如between、>、< 这些条件，会造成后面的索引字段失效

9. 主键和索引的区别

• 主键一定是唯一索引，唯一索引并不一定就是主键。

• 一个表中可以有多个唯一性索引，但只能有一个主键。

• 主键列不允许为null，唯一性索引列允许为null

10. 索引失效的原理

搭配这个视频食用：https://www.bilibili.com/video/BV1t5411u7Fg?p=3

1. 联合索引为什么会失效

比如联合索引是A,B,C，生成的B+树的那个单向链表，是先根据A排序，A相同的再根据B排序，以此类推

对于其他索引失效的场景，也可以通过这个联合索引去推断

6. Sql优化

1. Explain执行计划

1. type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
   从坏到好：All、index、range。。。。。。
2. key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。
3. key_len列，索引长度。
4. rows列，扫描行数。该值是个预估值。
5. extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。
   

2. Sql优化的一般手段

• **不用select ***：SELECT语句务必指明字段名称
• 小表去驱动大表：表关联查询（left join）尽量使用数据量小的表作为主表（驱动表）
• 尽可能走索引（这里好多操作，不举例了）
• 使用limit对查询结果的记录进行限定
• 使用连接（join）来代替子查询
• 拆分大的delete或insert语句
• 可通过开启慢查询日志来找出较慢的SQL
• 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
• sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
• OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
• 不用函数和触发器，在应用程序实现
• 避免%xxx式查询
• 少用JOIN
• 使用同类型进行比较，比如用’123’和’123’比，123和123比
• 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
• 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
• 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

摘自：https://www.cnblogs.com/adolfmc/p/11497261.html

7. 在mysql中一条sql语句是如何执行的

原文链接：https://www.cnblogs.com/lfri/p/12598339.html

执行的流程图如下：

原文链接：https://blog.csdn.net/Megustas_JJC/article/details/84380108

1. 连接器（连接数据库）

连接器负责跟客户端建立连接、获取权限、维持和管理连接

mysql -h$ip -P$port -u$user -p

连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成经典的 TCP 握手后，连接器就要开始认证你的身份

2. 查询缓存（Mysql8.0弃用了）

Mysql拿到一个select请求后，会去缓存中查看是否存在（以key-value的形式，key：select语句，value：查询的结果）

弃用原因：更新频繁的数据，缓存经常没有命中，查缓存浪费时间

MySQL 8.0 版本直接将查询缓存的整块功能删掉了

3. 分析器（语法分析）

分析语法，看看sql语句有没有问题

4. 优化器（生成explain）

生成执行计划Explain，还有比如，选择哪个索引，表关联查询的时候决定表的连接顺序

5. 执行器（选择存储引擎、执行sql）

判断权限、选择存储引擎、执行sql语句

8. Mysql中的binlog、redolog、undolog（重要）

Mysql执行器在执行更新相关的语句时候，会记录日志

1. binlog 二进制日志

binlog是Mysql自带的日志模块（叫什么归档日志），所有引擎都可以使用

1. 发生时机（commit之前）

MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性

图片来源：JavaGuide链接 MySQL三大日志(binlog、redo log和undo log)

2. redolog 事务日志

redolog是InnoDB引擎自带的日志模块（重做日志），InnoDB就是通过redolog来保证事务操作的

使用 redo log(重做日志) 保证事务的持久性

比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

图片来源：JavaGuide链接 MySQL三大日志(binlog、redo log和undo log)

3. undolog 回滚日志

在异常发生时，对已经执行的操作进行回滚

使用 undo log(回滚日志) 来保证事务的原子性。

4. InnoDB模式下的更新的sql执行流程：

1. 先查询到张三这一条数据，如果有缓存就用缓存。
2. 然后拿到查询的语句，把 age 改为19，然后调用InnoDB特点接口，写入这一行数据，InnoDB引擎把数据保存在内存中，同时记录redo log，此时redo log进入prepare状态，然后告诉执行器，执行完成了，随时可以提交。
3. 执行器收到通知后记录binlog，然后调用引擎接口，提交redo log 为commit状态。
4. 更新完成。
   
   图片来源：JavaGuide链接 MySQL三大日志(binlog、redo log和undo log)

redo log 两阶段提交的方式（redo log的写入拆成了两个步骤prepare和commit），这就是两阶段提交，写完binglog后，然后再提交redo log（这里的二阶段提交和分布式事务解决方案的2PC有点区别）

9. Mysql的锁

1. 锁的种类

• 共享锁：对数据就行读操作是加的锁。也叫读锁

select ... lock in share model

• 排他锁：对数据就行写操作是加的锁。也叫写锁

select ... for update 

• 意向锁：为了在一个事务中揭示下一行将要被请求锁的类型，意向锁是表锁，分为意向共享锁和意向排他锁

2. 锁的级别

1. 表级锁

MySQL 中锁定 粒度最大 的一种锁，锁表，加锁快，不会出现死锁、并发度低

1. 问：什么时候行锁会升级为表锁

索引失效的常见，如使用了or

2. 行级锁

MySQL 中锁定 粒度最小 的一种锁，锁行，加锁慢，会出现死锁、并发度高

3. 页面锁

介于表级锁和行级锁之间，不怎么用

4. 间隙锁

范围查询的时候，会把数据库中不存在的间隙数据也加上锁，防止有插入操作，从而防止幻读

3. 锁的算法

InnoDB 存储引擎的锁的算法有三种：

• Record lock：记录锁，单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身（这个要记）
• Next-key lock：record+gap 临键锁，锁定一个范围，包含记录本身

4. 锁的性格（悲观锁和乐观锁）

1. 悲观锁

1. 定义

很悲观，查询或者更新数据的时候，总认为有人改动，所以会加锁

2. 例如

synchronize关键字（串台了兄弟，这里是mysql）

2. 乐观锁

1. 定义

很乐观，查询数据的时候，默认没人改动，所以不加锁；更新的时候会对数据

2. 常见的实现方案

1.CAS操作（无锁算法）

compare and swap（比较和互换），是一种有名的无锁算法。无锁编程，即不使用锁的情况下实现多线程之间的变量同步，也就是在没有线程被阻塞的情况下实现变量的同步，所以也叫非阻塞同步。

1. 可能存在的问题

1. ABA问题
2. 长期占用CPU问题

2. 版本号机制

在数据表中加一个版本号version字段，表示数据被修改的次数

• 当数据被修改时，version值会加1
• 当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，如果刚才读到的version值=当前数据库中的version值，才更新，否则重试更新操作，直到更新成功

5. 锁升级

锁升级：行锁→页面锁→表锁的过程

1. 锁升级的原因

1. 一条SQL对同一对象上持有锁的数量超过了阈值
2. 内存不够用，锁资源占用内存太多

10. 死锁

1. 什么是死锁（争抢资源、互相等待）

多线程因为争抢资源而出现的互相等待的状态

2. 死锁产生的四个必要条件

1. 互斥

当资源被一个线程使用(占有)时，别的线程不能使用（如：张三拿到了苹果，李四就不能拿苹果了）

2. 不可抢占

资源请求者不能强制从资源占有者手中夺取资源，资源只能由资源占有者主动释放（如：李四不能从张三手里抢苹果，只能等张三主动放弃苹果）

3. 请求和保持

当资源请求者在请求其他的资源的同时保持对原有资源的占有（如：张三拿到了苹果，他还可以去那香蕉，并不冲突）

4. 循环等待

存在一个等待队列（如张三等李四的香蕉，李四在等张三的苹果，僵住了）

3. 如何避免死锁

打破上诉四个必要条件其中一个（除了互斥），就可以有效避免死锁

1. 破坏“请求和保持”条件：所有的进程在开始运行之前，必须一次性的申请其在整个运行过程各种所需要的全部资源

2. 破坏“不可抢占”条件：当一个已经持有了资源的进程在提出新的资源请求没有得到满足时，它必须释放已经保持的所有资源，待以后需要使用的时候再重新申请

3. 破坏“循环等待”条件：可以通过定义资源类型的线性顺序来预防，可以将每个资源编号，当一个进程占有编号为i 的资源时，那么它下一次申请资源只能申请编号大于i 的资源。

简单说一说drop、delete和truncate的区别

• delete、truncate只删除表的数据而不删除表的结构
• drop会删除表的结构和数据，不支持回滚
• delete操作会触发delete触发器，支持回滚
• truncate不会触发如何触发器，不支持回滚

什么是视图（View）

视图其实就是一张虚表，本质就是一个Select语句，结果集被命名为视图名称

当原表发生改变的时候，视图的数据也会发生改变

左连接和右连接和内连接

• 左连接Left join，左表为驱动表，查询出左表的所有数据，以及右表关联数据（没有的用null表示）
• 右连接Right join，右表为驱动表，查询出右表的所有数据，以及左表关联数据（没有的用null表示）
• 内连接Inner jion，查出两表都存在的数据（交集）

Mysql大表（千万级数据量）如何处理？（难）

1. 限定数据的范围

禁止不带任何查询条件的查询语句，避免查询全表数据，尽量多的走索引。

2. 垂直拆分、水平拆分、分库分表

可以看我的另一篇文章：
【八股文】分布式篇的《数据库如何处理海量数据》

mysql中varchar和char的区别，varchar(5)和varchar(200)的区别

mysql中varchar和char的区别

存储引擎存储CHAR或者VARCHAR值的方式在内存中和在磁盘上可能不一样

VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型

CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间（CHAR会把末尾空格取消掉
）

选取：该字段数据集的平均长度与最大长度是否相差很小，选择CHAR，其他情况选择VARCHAR

varchar(5)和varchar(200)的区别

• 相同点：存同样的数据，在硬盘上的空间开销是一样的
• 不同点：分配的内存不一样，长度大的分配更多内存

MYSQL中的int(11)到底代表什么意思？

int(11)中，11代表的并不是长度，而是字符的显示宽度

• 长度不足11，左边自动补零
• 字段超过了11，仍然可存储，这是显示宽度就不起作用了

为什么 select count(*) from t，在 InnoDB 引擎中比 MyISAM 慢？

• 在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。
• 在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。

MySQL如何实现高可用（难）

通过binlog实现主从模式