【每日八股】MySQL篇（一）：概述

关系的三个范式是什么？

第一范式（1NF）：用来确保每列的原子性，要求每列都是不可再分的最小数据单元。
概括：表中的每一列都是不可分割的最小原子值，且每一行都是唯一的。
在这里插入图片描述

第二范式（2NF）：在第一范式的基础上更进一层，要求表中的每列都和主键相关，即要求实体的唯一性。如果一个表满足第一范式（每一列都是不可再分的数据单元），并且除了主键以外的其它列全部都依赖于该主键，那么该表满足第二范式。
概括：满足 1NF，且所有非主键列完全依赖于主键。
在这里插入图片描述

第三范式（3NF）：在第二范式的基础上更进一层，第三范式确保每列都和主键列直接相关，而不是间接相关，即限制列的冗余性。如果一个关系满足第二范式（表中的每列都和主键相关），且除了主键以外的其他列都依赖于主键列，列和列之间不存在依赖关系，则满足第三范式。
概括：满足 2NF，且所有非主键之间不存在传递依赖。
在这里插入图片描述

MySQL 中 char 和 varchar 的区别是什么？

一句话概括：CHAR是固定长度的字符串类型，适合存储长度固定的数据，存储空间固定；VARCHAR是可变长度的字符串类型，适合存储长度不固定的数据，存储空间根据实际内容动态分配。

具体来说：

CHAR 字段的最大长度为 255 字符，而 VARCHAR 字段的最大长度为 65535 字符。【如果定义 CHAR(10)，无论存储的字符串长度是多少，MySQL 都会分配 10 个字符的存储空间，如果实际存储的字符串小于定义长度，MySQL 会用空格填充；而对于 VARCHAR(10)，实际存储的字符串长度是多少，MySQL 就分配多少空间（额外需要 1 ~ 2 个字节存储字符串的长度信息），不会用空格填充剩余空间】
补充：VARCHAR(10) 最多只能存储 10 个字符。如果尝试存储长度大于 10 的字符串，MySQL 会根据 SQL 模式决定是报错（严格模式）还是截断数据（非严格模式）。在 MySQL 中，VARCHAR(10) 中的 10 是必须指定的，不能忽略。VARCHAR 是一种可变长度的字符串类型，但它需要一个明确的最大长度限制，以便 MySQL 知道该字段最多可以存储多少字符。
CHAR 类型如果存的数据量小于最大长度，剩余的空间会使用空格填充，因此可能会浪费空间，所以 CHAR 类型适合存储长度固定的数据，这样不会浪费空间，效率还比 VARCHAR 略高；VARCHAR 类型如果存到数据量小于最大长度，剩余的空间会留给别的数据使用，所以 VARCHAR 类型适合存储长度不固定的数据，这样虽然没有 CHAR 存储效率高，但至少不会浪费空间。【CHAR 的存储效率高，但是可能浪费空间；VARCHAR 的存储效率不如 CHAR，但不会浪费空间；CHAR 适合存储定长数据，VARCHAR 适合存储变长数据】
CHAR 类型的查找效率高，VARCHAR 类型的查找效率低。【由于 CHAR 是固定长度，MySQL 在读取数据时可以直接计算出每一行的偏移量，访问速度更快，故 CHAR 不需要额外的计算来解析字符串的实际长度；而由于 VARCHAR 是可变长度，MySQL 在读取数据时需要先解析长度信息，然后再访问实际数据，这种额外的解析步骤会增加 CPU 开销，尤其是在处理大量数据时。】

SQL 如何实现模糊查询？

在 SQL 中，模糊查询通常使用 LIKE 关键字来实现。LIKE 允许你使用通配符来匹配符合特定模式的数据。LIKE 用于在 WHERE 子句中进行模糊匹配，它支持两种通配符：

%：匹配任意数量的字符（包括零个字符）；
_：匹配单个字符。
例如：

SELECT * FROM users WHERE name LIKE '张%';
SELECT * FROM users WHERE email LIKE '%example.com';
SELECT * FROM users WHERE name LIKE '%小%';
SELECT * FROM users WHERE name LIKE '_三';

join 和 left join 的区别？

JOIN 返回两个表匹配的行，LEFT JOIN 返回左表所有行（右表无匹配时补 NULL），RIGHT JOIN 返回右表所有行（左表无匹配时补 NULL）。
在这里插入图片描述

SELECT users.name, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id;

在这里插入图片描述

SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

在这里插入图片描述

SELECT 的执行过程？

SELECT 的执行过程包括 SQL 解析（包括词法分析和语法分析，如果 SQL 语句不对就会直接报错）、查询缓存（MySQL 8.0 之前，MySQL 8.0 之后不再包括查询缓存这一步）、查询优化、执行计划、数据读取和结果返回。

UPDATE 的执行过程？

UPDATE 的执行过程包括语法解析、查询优化、执行计划、数据读取、修改数据、事务处理（记录 Undo Log 和 Redo Log）以及结果返回。

COUNT 性能比较？

COUNT(*) 是最快的，因为 MySQL 对其进行了特殊优化；COUNT(1) 性能与 COUNT(*) 几乎相同；COUNT(column_name) 性能较差，尤其是列没有索引时（如果列有索引，MySQL 可以使用索引来加速统计，否则，MySQL 需要扫描整个表来统计非 NULL 值的数量，性能较差）；COUNT(DISTINCT column_name) 性能最差，因为需要去重并统计不同值的数量。

补充：索引

在 MySQL 中，COUNT(column_name) 的性能与列的索引密切相关。这里的“索引”指的是数据库表中为某一列（或多列）创建的索引结构，用于加速数据的查找和统计操作。

什么是索引？

索引是一种数据结构，用于快速定位表中的特定数据。类似于书籍的目录，索引可以帮助数据库快速找到满足条件的行，而不需要扫描整个表。常见的索引类型包括：

B-Tree 索引：适用于等值查询和范围查询。
哈希索引：适用于等值查询，但不支持范围查询。
全文索引：适用于文本搜索。

索引的工作原理

B-Tree 索引是一种平衡树结构，支持快速查找、插入和删除操作。
对于覆盖索引，当索引包含了查询所需的所有列，MySQL 可以直接从索引中获取数据，而不需要回表访问数据行。

优化建议

为常用的列创建索引可以提升查询性能，例如：

CREATE INDEX idx_email ON users (email);

drop、truncate 和 delete 的区别？

DROP 删除整个表（包括表结构和数据），操作不可逆；TRUNCATE 删除表中的所有数据但保留表结构，操作不可逆且性能较快；DELETE 删除表中的部分或全部数据但保留表结构，操作可回滚但性能较慢。
在这里插入图片描述
示例：

使用 DROP：

DROP TABLE users;

使用 TRUNCATE：

TRUNCATE TABLE users;

使用 DELETE：

DELETE FROM users WHERE id = 1;
DELETE FROM users;

MySQL 会出现死锁吗？怎么检测死锁？

MySQL 的 InnoDB 存储引擎可能会发生死锁，通常是由于多个事务相互等待锁资源导致的（事物 A 持有锁 1 并等待锁 2，而事物 B 持有锁 2 等待锁 1）。InnoDB 会自动检测死锁并回滚其中一个事务。可以通过错误日志、SHOW ENGINE INNODB STATUS 和 Performance Schema 来检测死锁。避免死锁的方法包括按顺序访问资源、减少事务长度和使用索引。