Mysql 的基础架构？

• MySQL 分为 Server 层和存储引擎两部分
• Server 层包括连接器、查询缓存、分析器、优化器、执行器等
  • 连接器：管理连接和权限验证
  • 查询缓存：命中直接返回结果
  • 分析器：词法分析、语法分析
  • 优化器：执行计划生成、索引选择
  • 执行器：操作引擎、返回结果
• 存储引擎负责存储数据和提供读写 IO 接口，其架构是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎

MySQL 的长连接和短连接

• 长连接：连接成功后，如果客户端持续有请求，则一直使用同一个连接
• 短连接：每次执行完很少的几次查询就断开连接，下次查询再重新建立

长连接引起的异常重启问题？

• 引起异常的原因：MySQL 在执行过程中使用的内存是在连接对象里的，这些资源在连接断开的时候才会释放
• 解决方法：
  • 定期断开长连接
  • 重新初始化连接资源

说一下 MySQL 执行一条查询语句的内部执行过程？

连接器 → 查询缓存 → 分析器 → 优化器 → 执行器

• 客户端先通过连接器连接到 MySQL 服务器
• 连接器权限验证通过之后，先查询是否有查询缓存，如果有缓存则直接返回缓存数据，如果没有缓存则进入分析器
• 分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误，会直接返回给客户端错误信息，如果语法正确则进入优化器
• 优化器对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好
• 优化器执行完就进入执行器，开始执行查询语句直到查询出满足条件的所有数据，然后返回

MySQL 查询缓存的功能有何优缺点？

• 优点是效率高，如果已经有缓存则会直接返回结果
• 但是，如果数据库表中数据和结构发生变化时（增删改、表结构调整），查询缓存将会失效，导致缓存命中率比较低

MySQL 的常用引擎都有哪些？

• MySQL 的常用引擎有 InnoDB、MyISAM[maiˈzæm]、Memory（所有数据放在内存中） 等
• InnoDB 是默认的存储引擎

InnoDB vs MyISAM

• 最大的区别是 InnoDB 支持事务，而 MyISAM 不支持事务
• InnoDB 支持崩溃后安全恢复，MyISAM 不支持崩溃后安全恢复
• InnoDB 支持行锁，MyISAM 不支持行锁，只支持表锁
• InnoDB 支持外键，MyISAM 不支持外键

InnoDB 有哪些特性？

• 插入缓冲： 对于非聚集索引页，**不是直接，提高性能
• 两次写：要进行写操作时，不直接写硬盘，而是先拷贝到内存中的两次写缓冲区，然后把两次写缓冲区的内容分两次写到磁盘上
• 自适应哈希索引：为了提高性能会自动建立一些 hash 索引，查找效率很高，直接通过 hash 值定位到数据

一张自增表中有三条数据，删除了两条数据之后重启数据库，再新增一条数据，此时这条数据的 ID 是几？

• MyISAM 存储引擎：ID 为 4，因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里面，重启 MySQL 后不会丢失
• InnoDB 存储引擎
  • MySQL 8 以前: ID 为 2，因为 InnoDB 表把自增主键的最大 ID 记录到内存中，所以重启数据库后会使最大 ID 丢失
  • MySQL 8.0 ：ID 为 4，InnoDB 每次更改时，当前最大计数器值写入重做日志 redo log

自增主键能不能被持久化的问题？

• MyISAM：自增主键会持久化到数据文件中
• InnoDB：
  • 8 之前：不会持久化
  • 从 8 开始：自增主键会持久化到 redo log

为什么 InnoDB 不把总条数记录下来，查询的时候直接返回呢？

• 因为 InnoDB 支持事务
• 即使是在同一时间进行查询，得到的结果也可能不相同

InnoDB 和 MyISAM 执行 select count(*) from t，哪个效率更高？为什么？

• MyISAM 效率最高
• 因为 MyISAM 内部维护了一个计数器，直接返回总条数
• 而 InnoDB 要逐行统计

在 InnoDB 引擎中 count(*)、count(1)、count(主键)、count(字段) 哪个性能最高？

• 对于 count(字段) 来说，遍历整张表，取这个字段，然后判断是否为 null，不为 null 则加 1
• 对于 count(主键 id) 来说，遍历整张表，取主键 id，每次加 1
• 对于 count(1) 来说，遍历整张表，不取值
• 对于 count(_) 来说，遍历整张表，不取值，直接按行累加

结果：count(字段)<count(主键 id)<count(1)≈count(*)

MySQL 可以针对表级别设置数据库引擎吗？怎么设置？

• 可以针对不同的表设置不同的引擎
• 在 create table 语句中使用 engine=引擎名（比如 Memory）来设置此表的存储引擎

MySQL 中什么情况会导致自增主键不能连续？

1. 唯一键冲突
2. 事务回滚

• 执行器执行插入，自增键 +1，但是插入时发现唯一键冲突，无法插入，导致的不连续
• 事务添加自增键 +1，但是由于事务回滚，数据被清除，导致的不连续

什么是覆盖索引？

覆盖索引是指，索引上的信息足够满足查询请求，不需要回表取数据

如果把一个 InnoDB 表的主键删掉，是不是就没有主键，就没办法进行回表查询了？

如果把主键删掉了，那么 InnoDB 会自己生成一个 rowid 作为主键

内存表和临时表有什么区别？

• 内存表，指的是使用 Memory 引擎的表，数据放在内存中，重启会被清空
• 临时表，指的是使用 InnoDB 引擎或者 MyISAM 引擎的表，数据放在磁盘上，重启不会清空

MySQL 中的字符串类型都有哪些？

• CHAR
• VARCHAR
• BLOB
• TEXT
• LONGTEXT
• VARBINARY
• BINARY

VARCHAR 和 CHAR 的区别是什么？分别适用的场景有哪些？

• VARCHAR 的长度可变，而 CHAR 是固定长度
• CHAR 比较适合长度较短的字段和固定长度的字段，如身份证号、手机号等
• 反之则适合使用 VARCHAR

MySQL 存储金额应该使用哪种数据类型？为什么？

MySQL 存储金额应该使用 decimal ，因为如果存储其他数据类型，比如 float 有导致小数点后数据丢失的风险

limit 3,2 的含义是什么？

去除前三条数据之后查询两条信息

删除表的数据有几种方式？它们有什么区别？

删除数据有两种方式：delete 和 truncate，它们的区别如下：

• delete 可以添加 where 条件删除部分数据，truncate 不能添加 where 条件，只能删除整张表
• delete 的删除信息会在 MySQL 的日志中记录，而 truncate 的删除信息不被记录， 因此 detele 的信息可以被找回，而 truncate 的信息无法被找回
• truncate 因为不记录日志所以执行效率比 delete 快

MySQL 中支持几种模糊查询？它们有什么区别？

MySQL 中支持两种模糊查询：regexp 和 like

• like 是模糊匹配
• regexp 支持正则表达式

count(column) 和 count(*) 有什么区别？

• count(column) 统计不会统计列值为 null 的数据
• count(*) 则会统计所有信息

MySQL 中内连接、左连接、右连接有什么区别？

• 内连（inner join）— 把匹配的关联数据显示出来
• 左连接（left join）— 把左边的表全部显示出来，右边的表显示出符合条件的数据
• 右连接（right join）— 把右边的表全部显示出来，左边的表显示出符合条件的数据

MySQL 日志篇

MySQL 有哪些重要的日志文件？

• 错误日志：记录 MySQL 服务器运行过程中的错误信息
• 常规查询日志：记录了数据库执行的所有命令
• 慢日志：记录查询超过指定时间的语句
• redo log 重做日志：一种缓存机制，先将数据写入内存中，再批量把内存中的数据统一刷回磁盘
• undo log 回滚日志：存储日志被修改前的值
• bin log 二进制日志：主要记录了对 MySQL 数据库执行更改的所有操作

redo log 和 binlog 有什么区别？

• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”
• binlog 是逻辑日志，记录的是这个语句的原始逻辑
• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的

什么是 crash-safe？

crash-safe 是指发生宕机等意外情况下，服务器重启后数据不会丢失的情况

什么是脏页和干净页？

MySQL 为了操作的性能优化，会把数据更新先放入内存中，之后再统一更新到磁盘。
当内存数据和磁盘数据内容不一致的时候，我们称这个内存页为脏页
内存数据写到磁盘后，内存的数据和磁盘上一致，我们称为“干净页”

什么情况下会引发 MySQL 刷脏页（flush）的操作？

• 内存写满了，这个时候就会引发 flush 操作，InnoDB 中就是 redo log 写满
• 系统的内存不足，当需要新的内存页的时候，就会淘汰一些内存页，如果淘汰的是脏页这个时候就会触发 flush 操作
• 系统空闲的时候，MySQL 会同步内存中的数据到磁盘也会触发 flush 操作
• MySQL 服务关闭的时候也会刷脏页，触发 flush 操作

MySQL 刷脏页的速度很慢可能是什么原因？

• 在 MySQL 中单独刷一个脏页的速度是很快的
• 如果发现刷脏页的速度很慢，说明触发了 MySQL 刷脏页的“连坐”机制
• MySQL 的“连坐”机制是指当 MySQL 刷脏页的时候如果发现相邻的数据页也是脏页也会一起刷写到磁盘

如何控制 MySQL 只刷新当前脏页？

在 InnoDB 中设置 innodb_flush_neighbors 这个参数的值为 0，来规定 MySQL 只刷当前脏页，MySQL 8 这个值默认是 0

MySQL 的 WAL 技术是解决什么问题的？

A.防止误删除，找回数据用的 B.容灾恢复，为了还原异常数据用的 C.事务处理，为了数据库的稳定性 D.为了降低 IO 成本
答：D
题目解析：WAL 技术的全称是 Write Ahead Logging（预写式日志），是先写日志，再写磁盘的方式，因为每次更新都写磁盘的话 IO 成本很高，所以才有了 WAL 技术

为什么有时候会感觉 MySQL 偶尔卡一下？

如果偶尔感觉 MySQL 卡一下，可能是 MySQL 正在刷脏页，正在把内存中的更新操作刷到磁盘中

redo log 和 binlog 是怎么关联的?

每个事务都有一个唯一的 XID，用于标识事务

• 在 redo log 中，事务的开始和结束会被记录，这些记录包括 XID
• 如果在 redo log 中发现只有 prepare 而没有 commit 的记录，MySQL 会使用 XID 去 binlog 中找到相应的事务，以确定该事务的最终状态，然后提交或回滚事务

MySQL 怎么知道 binlog 是完整的?

• statement 格式的 binlog，完整的标识是最后有 COMMIT 关键字
• row 格式的 binlog，完整的标识是最后会有一个 XID 关键字

MySQL 中可不可以只要 binlog，不要 redo log？

不可以，binlog 没有崩溃恢复的能力

MySQL 中可不可以只要 redo log，不要 binlog？

不可以，原因有以下两个：

• redo log 是循环写，不能保证所有的历史数据，这些历史数据只能在 binlog 中找到
• binlog 是高可用的基础，高可用的实现原理就是 binlog 复制

为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？

• 因为 binlog 是不能被打断的，一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里
• redo log 并没有这个要求，中间有生成的日志可以写到 redo log buffer 中，在其他事务提交的时候可以被一起写到磁盘中

事务执行期间，还未提交，如果发生 crash，redo log 丢失，会导致主备不一致呢？

不会，因为这时候 binlog 也还在 binlog cache 里，没发给备库，crash 以后 redo log 和 binlog 都没有了，从业务角度看这个事务也没有提交，所以数据是一致的

在 MySQL 中用什么机制来优化随机读/写磁盘对 IO 的消耗？

• redo log 是用来节省随机写磁盘的 IO 消耗
• change buffer 主要是节省随机读磁盘的 IO 消耗

以下说法错误的是？

A.redo log 是 InnoDB 引擎特有的，它的固定大小的
B.redo log 日志是不全的，只有最新的一些日志，这和它的内存大小有关
C.redo log 可以保证数据库异常重启之后，数据不丢失
D.binlog 是 MySQL 自带的日志，它能保证数据库异常重启之后，数据不丢失 答：D
题目解析：binlog 是 MySQL 自带的日志，但它并不能保证数据库异常重启之后数据不丢失

以下说法正确的是？

A.redo log 日志是追加写的，后面的日志并不会覆盖前面的日志
B.binlog 日志是追加写的，后面的日志并不会覆盖前面的日志
C.redo log 和 binlog 日志都是追加写的，后面的日志并不会覆盖前面的日志
D.以上说法都正确
答：B
题目解析：binlog 日志是追加写的，后面的日志并不会覆盖前面的日志，redo log 日志是固定大小的，后面的日志会覆盖前面的日志

有没有办法把 MySQL 的数据恢复到过去某个指定的时间节点？怎么恢复？

• 可以恢复，只要你备份了这段时间的binlog，同时做了数据库的定期备份
• 可以把之前备份的数据库先还原到测试库
• 从备份的时间点开始，将备份的 binlog 依次取出来，重放到要恢复数据的那个时刻，这个时候就完成了数据到指定节点的恢复

MySQL 性能优化

MySQL 性能指标都有哪些？

• TPS：每秒事务数
• QPS：每秒请求次数
• IOPS：每秒处理的 IO 请求次数

什么是慢查询？

慢查询是 MySQL 中提供的一种慢查询日志，它用来记录在 MySQL 中响应时间超过阀值的语句

如何定位慢查询？

使用 MySQL 中的 explain 分析执行语句

explain select * from t where id=5

⭐MySQL 的优化手段都有哪些？

• 查询优化
• 优化索引的使用
• 表结构设计优化
• 表拆分
• 读写分离

查询优化

• 避免 select *
• 小表驱动大表
• 使用连接代替子查询

优化索引的使用

• 尽量使用主键查询
• 不做列运算
• 查询语句尽可能简单
• 不使用 select * 查询
• 不用函数和触发器
• 避免% 在前的查询
• 避免在 where 子句中对字段进行表达式操作
• 尽量使用数字型字段

表结构设计优化

• 使用简单的数据类型，int 要比 varchar 类型在 MySQL 处理简单
• 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int
• 尽可能使用 not null 定义字段，因为 null 占用 4 字节空间
• 尽量少用 text 类型，非用不可时最好考虑分表
• 尽量使用 timestamp，而非 datetime
• 单表不要有太多字段，建议在 20 个字段以内

表拆分

当数据库中的数据非常大时，查询优化方案也不能解决查询速度慢的问题时，可以考虑拆分表，让每张表的数据量变小，从而提高查询效率

• 垂直拆分：数据表列的拆分
• 水平拆分：表的行数过大时，拆分成多张表

读写分离

• 采用数据库集群的方案
• 使用一个库作为主库，负责写入数据
• 其他库为从库，负责读取数据，这样可以缓解对数据库的访问压力

MySQL 常见读写分离方案有哪些？

• 应用层：通过应用层对数据源做理由来实现读写分离
• 中间件：通过 MySQL 的中间件做主从集群

MySQL 开放问题

有一个超级大表，如何优化分页查询？

• 数据库层面优化：利用子查询优化超多分页场景，利用子查询先快速定位需要获取的 id 段，然后再关联查询
• 程序层面优化：可以利用缓存把查询的结果缓存起来

⭐查询长时间不返回可能是什么原因？应该如何处理？

• 查询字段没有索引或者没有触发索引查询
  • 以 % 开头的 like 查询
  • 数据类型出现隐式转换时不会使用索引
  • 不符合最左前缀原则；
  • 如果查询条件有 or 分割，如果有 or 的字段没有索引，会引起全表查询
  • 如果 MySQL 认为使用索引会比全表查询更慢，则不会使用索引
• IO 压力大，读取磁盘速度变慢
• 内存不足
• 网络速度慢
• 查询出的数据量过大
• 死锁

以上问题的解决方案如下：

• 正确创建和使用索引
• 把数据、日志、索引放到不同的 IO 设备上，减少主数据库的 IO 操作。更换 MySQL 的磁盘为固态硬盘，以提高磁盘的 IO 性能
• 升级内存，更换更大的内存
• 提升网速，升级带宽
• 查询时只返回需要的字段。
• 设置死锁的超时时间，限制和避免死锁消耗过多服务器的资源

MySQL 主从延迟的原因有哪些？

• 主库有大事务处理
• 主库做大量的增、删、改操作
• 主库对大表进行字段新增、修改或添加索引等操作
• 主库的从库太多，导致复制延迟
• 主库读写压力大，导致复制延迟
• 从库之间的网络延迟

如何保证数据不被误删？

• 权限控制与分配（数据库和服务器权限）
• 避免数据库账号信息泄露，业务代码不要使用明文保存数据库连接信息
• 部署延迟复制从库，万一误删除时用于数据回档，且从库设置为 read-only
• 确认备份制度及时有效
• 启用 sql_safe_updates 选项，不允许没 where 条件的更新/删除
• 将系统层的 rm 改为 mv
• 线上不进行物理删除，改为逻辑删除
• 开启 binlog，方便追溯数据

⭐MySQL 服务器 CPU 飙升应该如何处理？

• 使用 show full processlist 查出慢查询
• 为了缓解数据库服务器压力，先使用 kill 命令杀掉慢查询的客户端
• 然后再去项目中找到执行慢的 SQL 语句进行修改和优化

MySQL 毫无规律的异常重启，可能产生的原因是什么？该如何解决？

可能是积累的长连接导致内存占用太多，被系统强行杀掉导致的异常重启

• 定期断开空闲的长连接
• 定期重新初始化连接资源

补充

查询语句不同元素（where、jion、limit、group by、having 等等）执行先后顺序？

• from: 需要从哪个数据表检索数据
• where: 过滤表中数据的条件
• group by: 如何将上面过滤出的数据分组
• having: 对上面已经分组的数据进行过滤的条件
• select: 查看结果集中的哪个列，或列的计算结果
• order by : 按照什么样的顺序来查看返回的数据

数据库三范式，根据某个场景设计数据表？

• 第一范式：所有字段值都是不可分解的原子值
• 第二范式：在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中
• 第三范式：数据表中的每一列数据都和主键直接相关，而不能间接相关

MySQL 高并发环境解决方案？

分库 分表 分布式 增加二级缓存

Explain 性能分析

• 查看执行计划：使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的
• 分析查询语句或是表结构的性能瓶颈