🔴 概述¶
约 4259 个字 1 张图片 预计阅读时间 21 分钟
关系的三个范式是什么？¶
-
第一范式(1NF)：用来确保每列的原子性，要求每列(或者每个属性值)都是不可再分的最小数据单元(也称为最小的原子单元)
-
第二范式(2NF)：在第一范式的基础上更进一层，要求表中的每列都和主键相关，即要求实体的唯一性。如果一个表满足第一范式，并且除了主键以外的其他列全部都依赖于该主键，那么该表满足第二范式
-
第三范式(3NF)：在第二范式的基础上更进一层，第三范式是确保每列都和主键列直接相关，而不是间接相关，即限制列的冗余性。如果一个关系满足第二范式，并且除了主键以外的其他列都依赖于主键列，列和列之间不存在相互依赖关系，则满足第三范式
MySQL 中 varchar 和 char 的区别是什么？¶
- char 字段的最大长度为 255 字符，varchar 字段的最大长度为 65535 个字符
- char 类型如果存的数据量小于最大长度，剩余的空间会使用空格填充，因此可能会浪费空间，所以 char 类型适合存储长度固定的数据，这样不会浪费空间，效率还比 varchar 略高；varchar 类型如果存到数据量小于最大长度，剩余的空间会留给别的数据使用，所以 varchar 类型适合存储长度不固定的数据，这样虽然没有 char 存储效率高，但至少不会浪费空间
- char 类型的查找效率高，varchar 类型的查找效率较低
join 和 left join 的区别？¶
- join 等价于 inner join 内连接，是返回两个表中都有的符合条件的行
- left join 左连接，是返回左表中所有的行及右表中符合条件的行
- right join 右连接，是返回右表中所有的行及左表中符合条件的行
SQL 怎么实现模糊查询？¶
索引 B+ 树是按照索引值有序排列存储的，只能根据前缀进行比较。每一次按照模糊匹配的前缀字典序来进行比较。
什么是索引下推（ICP）？¶
索引下推（Index Condition Pushdown，ICP）是 MySQL 在利用联合索引检索数据时的一种优化：把 部分本应在 Server 层做的 WHERE 条件判断，下推到存储引擎层 去做，从而减少回表次数和与 Server 层的交互。
- 没有 ICP 时：存储引擎按索引找到一批可能满足条件的记录，先回表取完整行，再交给 Server 层逐条做 WHERE 过滤。
- 有 ICP 时：存储引擎在索引上就能先根据联合索引中的列做条件判断，只对“索引列条件已满足”的记录回表，再交给 Server 层，这样回表行数更少、性能更好。
ICP 适用于联合索引且查询条件中包含索引列但无法完全用索引覆盖的场景（例如部分条件只能做范围或过滤、不能用于索引查找）。MySQL 5.6 起 InnoDB 支持 ICP，默认开启。
select 的执行过程？¶
MySQL 的架构共分为两层：Server 层和存储引擎层：
- Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现
- 存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始，InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+ 树，且是默认使用
第一步：连接器
连接器负责与客户端建立连接、获取权限、维持和管理连接。首先客户端和 MySQL 通过三次握手建立连接，MySQL 是基于 TCP 进行传输的。MySQL 服务如果没有启动就会报错。MySQL 正常运行的话就去校验用户名和密码，如果认证信息错误也会报错。检验通过之后连接器会获取用户权限并且保存起来，后续的任何操作都会基于开始的读到权限进行判断，即便创建连接之后更改了权限也不会影响已连接的权限。
空闲连接会一直占用着吗？ 当然不是了，MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28800 秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。
MySQL 的连接数有限制吗？ MySQL 服务支持的最大连接数由 max_connections 参数控制，比如我的 MySQL 服务默认是 151 个，超过这个值，系统就会拒绝接下来的连接请求，并报错提示"Too many connections"。
MySQL 的连接也跟 HTTP 一样，有短连接和长连接的概念。使用长连接的好处就是可以减少建立连接和断开连接的过程，所以一般是推荐使用长连接。但是，使用长连接后可能会占用内存增多，因为 MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。
如何断开长连接？ 有两种解决方式：
- 定期断开长连接：既然断开连接后就会释放连接占用的内存资源，那么我们可以定期断开长连接
- 客户端主动重置连接：MySQL 5.7 版本实现了
mysql_reset_connection()函数的接口，注意这是接口函数不是命令，那么当客户端执行了一个很大的操作后，在代码里调用 mysql_reset_connection 函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态
第二步：查询缓存
连接器的工作完成后，客户端就可以向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（Query Cache）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。
如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。
缓存缺点：对于更新比较频繁的表，查询缓存的命中率很低，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓冲就被清空了，相当于缓存了个寂寞。所以，MySQL 8.0 版本直接将查询缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了。对于 MySQL 8.0 之前的版本，如果想关闭查询缓存，我们可以通过将参数 query_cache_type 设置成 DEMAND。
说明
这里说的查询缓存是 server 层的，也就是 MySQL 8.0 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer pool。
第三步：解析 SQL
在正式执行 SQL 查询语句之前，MySQL 会先对 SQL 语句做解析，这个工作交由「解析器」来完成。解析器会做如下两件事情：
- 词法分析：MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、where 条件等等
- 语法分析：根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法
如果我们输入的 SQL 语句语法不对，就会在解析器这个阶段报错。但是注意，表不存在或者字段不存在，并不是在解析器里做的，解析器只负责构建语法树和检查语法，但是不会去查表或者字段存不存在。
第四步：执行 SQL
经过解析器后，接着就要进入执行 SQL 查询语句的流程了，每条SELECT 查询语句流程主要可以分为下面这三个阶段：prepare 阶段（预处理阶段）、optimize 阶段（优化阶段）、execute 阶段（执行阶段）。
预处理器：预处理器会做如下事情：
- 检查 SQL 查询语句中的表或者字段是否存在
- 将
select *中的*符号，扩展为表上的所有列
优化器：优化器会确定 SQL 语句的执行方案，比如有索引会选择走了哪个索引，或者确定表的连接顺序等。
执行器：执行器负责具体执行，会调用存储引擎的接口。
- 走索引时：执行器将索引条件交给存储引擎；存储引擎通过 B+ 树定位数据。若未找到，则向执行器返回错误，查询结束；若找到，则将记录返回给执行器。执行器判断该记录是否满足 WHERE 等条件，满足则返回给客户端，否则跳过
- 全表扫描时：执行器与存储引擎交互后，存储引擎从表的第一条记录开始取数据；执行器逐条判断是否满足条件，满足则返回给客户端
执行器取数过程是一个 while 循环：不断取下一行、判断、再取下一行，直到读完表中所有记录。若使用 联合索引，会在存储引擎层先判断各索引列是否都满足条件（而不是先回表）；只要有一个索引条件不满足就跳过该记录，只有全部满足才回表到 Server 层，这一优化称为 索引下推（Index Condition Pushdown）。
update 的执行过程？¶
执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取一行记录：
- 如果记录所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新
- 如果记录不在 buffer pool，将数据页从硬盘读入到 buffer pool，返回记录给执行器
执行器得到聚簇索引记录后，会 比较更新前的记录和更新后的记录是否相同：
- 如果一样就不进行后续更新流程
- 如果不一样就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作
- 开启事务：首先要记录相应的 undo log，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log
InnoDB 层开始更新记录，会先更新内存(同时标记为脏页)，然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少硬盘 I/O，不会立即将脏页写入硬盘，后续由后台线程选择一个合适的时机将脏页写入到硬盘。
在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
两阶段提交：
- prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘
- commit 阶段：将 binlog 刷新到硬盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit(将事务设置为 commit 状态后，刷入到硬盘 redo log 文件)
count 性能比较？¶
count(*) = count(1) > count(主键) > count(字段)
MySQL 会将星号参数转化为参数 0 来处理，所以 count(*) 和 count(1)相等。count(主键)需要判断主键是否为空值；count(字段)会进行全表扫描，效率最差。
drop、truncate 和 delete 的区别？¶
drop 删除整张表和表结构，以及表的索引、约束和触发器；truncate 只删除表数据，表的结构、索引、约束等会被保留； delete 只删除表的全部或部分数据，表结构、索引、约束等会被保留。
- delete 语句为 DML(data maintain Language)，执行删除操作的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作
- truncate、drop 是 DDL(data define language)，删除行是不能恢复的，并且在删除的过程中不会激活与表有关的删除触发器，执行速度快，原数据不放到 rollback segment 中，不能回滚
- truncate 和 drop 不支持添加 where 条件，而 delete 支持 where 条件
- 执行速度 drop > truncate > delete，delete 是逐行执行的，并且在执行时会把操作日志记录下来，以备日后回滚使用，所以 delete 的执行速度是比较慢的；而 truncate 的操作是先复制一个新的表结构，再把原先的表整体删除，所以它的执行速度居中，而 drop 的执行速度最快
- truncate 只能对 TABLE；delete 可以是 TABLE 和 VIEW
使用场景
- 如果 想删除表用 drop
- 如果 想保留表而将所有数据删除，和事务无关，用 truncate 即可
- 如果 和事务有关，或者想触发 trigger，用 delete
- 如果是整理表内部的碎片，可以用 truncate 跟上 reuse stroage，再重新导入/插入数据
MySQL 会出现死锁吗，如何避免死锁？¶
什么情况发生死锁¶
如果 update 语句的 where 条件没有用到索引列，那么就会全表扫描，在一行行扫描的过程中，不仅给行记录加上了行锁，还给行记录两边的空隙也加上了间隙锁，相当于锁住整个表，然后直到事务结束才会释放锁。
行锁会发生死锁，表锁不会。死锁的四个必要条件： 互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。
解决办法：¶
- 设置事务等待锁的超时时间：当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒
- 应用乐观锁：在应用层控制并发操作
- 避免长查询：简化事务，减少复杂查询
- 优化事务设计：合理顺序获取锁，减少锁持有时间
- 开启主动死锁检测：主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。当检测到死锁后，就会出现提示