MYSQL【一、基础】-CSDN博客

本文链接：https://blog.csdn.net/qq_30769437/article/details/140301287

1、内连、外连、交叉连、笛卡尔积

内连接（inner join）：取得两张表中满足存在连接匹配关系的记录。
外连接（outer join）：不只取得两张表中满足存在连接匹配关系的记录，还包括某张表（或两张表）中不满足匹配关系的记录。
交叉连接（cross join）：显示两张表所有记录一一对应，没有匹配关系进行筛选，它是笛卡尔积在SQL中的实现，如果A表有m行，B表有n行，那么A和B交叉连接的结果就有m*n行。
笛卡尔积：是数学中的一个概念，例如集合A={a,b}，集合B={1,2,3}，那么 A ✖ B={<a,o>,<a,1>,<a,2>,<b,0>,<b,1>,<b,2>}。

2、内连、左连、右连

在这里插入图片描述

inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集。
left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。
right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

3、三大范式

在这里插入图片描述

第一范式（1NF）：要求数据库表中的每个字段都是不可再分的原子值，避免数据冗余和异常。
1. 例如，一个学生表的字段可以是学生ID、姓名和电话号码，而不是将多个电话号码存储在同一个字段中。
第二范式（2NF）：在满足1NF的基础上，要求非主键字段完全依赖于全部主键，消除非主键字段对主键的部分依赖性。
1. 例如，一个订单表的主键是订单ID和产品ID，若存在字段为产品名称和产品描述，则这些字段应该与整个主键相关，而不仅仅是订单ID。
第三范式（3NF）：在满足2NF的基础上，要求非主键字段之间不能存在传递依赖关系，即非主键字段之间不能相互依赖。
1. 例如，一个员工表的主键是员工ID，若存在字段为部门名称和部门所在城市，则这些字段之间应该是独立的，不应该存在部门名称依赖于部门所在城市的情况。

虽然三大范式的作用是为了控制数据库的冗余，是对空间的节省，但是，一般互联网公司的设计都是反范式的，通过冗余一些数据，避免跨表跨库，利用空间换时间，提高性能。

4、字段类型区别

4.1、char 与 varchar

在这里插入图片描述

char：

char 表示定长字符串，长度是固定的
如果插入数据的长度小于 char 的固定长度时，则用空格填充
因为长度固定，所以存取速度要比 varchar 快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法
对于char来说，最多能存放的字符个数为 255，和编码无关

varchar：

varchar 表示可变长字符串，长度是可变的
插入的数据是多长，就按照多长来存储
varchar 在存取方面与 char 相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法
对于 varchar 来说，最多能存放的字符个数为 65532

日常的设计，对于长度相对固定的字符串，可以使用 char，对于长度不确定的，使用 varchar 更合适一些。

4.2、text 和 blob

在关系型数据库中，BLOB 和 TEXT 是用于存储大型二进制数据和文本数据的数据类型，它们之间有以下区别：

存储类型：BLOB（Binary Large Object）用于存储二进制数据，如图像、音频、视频等，而 TEXT 用于存储文本数据，如长文本、文章、日志等。
存储容量：BLOB 类型可以存储非常大的二进制数据，其容量限制取决于数据库的实现和配置。一般情况下，BLOB 可以存储几个字节到几个GB的数据。而 TEXT 类型也可以存储大量的文本数据，其容量限制也取决于数据库的实现和配置，一般可以存储几个字节到几个GB的数据。
存储方式：BLOB 类型存储的是二进制数据的原始字节，而 TEXT 类型存储的是字符数据，使用数据库的字符集进行编码。
操作和检索：BLOB 类型的数据一般用于存储和处理二进制数据，例如通过应用程序读取和写入图像文件或其他二进制文件。TEXT 类型的数据一般用于存储和处理文本数据，可以进行字符串的搜索、截取、连接等操作。

需要注意的是，由于 BLOB 和 TEXT 类型存储的数据比较大，对于大型数据的查询和操作可能会对数据库性能产生影响。在设计数据库时，需要根据具体的业务需求和数据特点来选择合适的数据类型，并合理使用索引和优化技术来提高查询和操作的效率。

4.3、DATETIME 和 TIMESTAMP

相同点：

两个数据类型存储时间的表现格式一致。均为 YYYY-MM-DD HH:MM:SS
两个数据类型都包含「日期」和「时间」部分
两个数据类型都可以存储微秒的小数秒（秒后6位小数秒，5.6.4+）

区别：

日期范围：DATETIME 的日期范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999 ；TIMESTAMP 的时间范围是 1970-01-01 00:00:01.000000 UTC 到 2038-01-09 03:14:07.999999 UTC
存储空间：DATETIME 的存储空间为 8 字节，5.6.4+占5个字节（小数秒+3个字节）；TIMESTAMP 的存储空间为 4 字节
时区相关：DATETIME 存储时间与时区无关；TIMESTAMP 存储时间与时区有关，显示的值也依赖于时区
默认值：DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认值为当前时间(CURRENT_TIMESTAMP)

5、in 和 exists

MySQL中的 in 语句是把外表和内表作 hash 连接，而 exists 语句是对外表作loop循环，每次loop循环再对内表进行查询。我们可能认为 exists 比 in 语句的效率要高，这种说法其实是不准确的，要区分情景：

如果查询的两个表大小相当，那么用 in 和 exists 差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用 exists，子查询表小的用 in。
not in 和 not exists：如果查询语句使用了 not in，那么内外表都进行全表扫描，没有用到索引；而 not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用 not exists 都比 not in 要快。

6、货币类型选择

在MySQL中，记录货币金额可以使用 DECIMAL 或 NUMERIC 字段类型来存储。这两个类型都是用于存储精确数值的，可以指定小数点前后的位数和小数精度。在表示货币金额时，我们通常需要考虑到小数位数和精度的要求，因此 DECIMAL 或 NUMERIC 类型比较适合。

例如，可以使用 DECIMAL(10, 2) 来表示最大10位数，其中2位是小数位，这样可以存储类似于 99,999,999.99 的金额。你可以根据实际需求调整字段类型的位数和精度。

需要注意的是，在进行计算和比较货币金额时，应该使用专门的货币计算库或函数，而不是直接使用浮点数计算，以避免浮点数的精度问题。这样可以确保计算结果的准确性和一致性。

7、存储 emoji

utf8 编码不行，MySQL中的 utf8 是阉割版的 utf8，它最多只用 3 个字节存储字符，所以存储不了emoji表情。需要使用 utf8mb4 编码。

alter table blogs modify content text CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci not null;

8、drop、delete、truncate

在不再需要一张表的时候，用drop
在想删除部分数据行时候，用delete
在保留表而删除所有数据的时候用truncate。

	delete	truncate	drop
类型	DML	DDL	DDL
回滚	√	×	×
删除内容	表结构还在，删除表的数据行	表结构还在，删除表中的所有数据	从数据库中删除表，所有数据行，索引和权限也会被删除
删除速度	慢，需要逐行删除	快	最快

9、union 和 union all

UNION 会对结果集去重，而 UNION ALL 不去重。
UNION 返回的结果集不包含重复行，而 UNION ALL 可能包含重复的行。
UNION 相对于 UNION ALL 可能会有更高的性能开销，因为它需要对结果集进行排序和去重操作。

10、.count(1)、count(*)、count(列名)

执行效果：

count(*) 包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为 NULL
count(1) 包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
count(列名) 只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是指空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计

执行速度：

列名为主键，count(列名) 会比 count(1) 快
列名不为主键，count(1) 会比 count(列名) 快
如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)
如果有主键，则 select count(主键) 的执行效率是最优的
如果表只有一个字段，则 select count（*）最优

11、条SQL查询语句的执行顺序

在这里插入图片描述

FROM：对FROM子句中的左表和右表执行笛卡儿积（Cartesianproduct），产生虚拟表 VT1
ON：对虚拟表VT1应用ON筛选，只有那些符合的行才被插入虚拟表 VT2
JOIN：如果指定了 OUTER JOIN（如 LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表 VT2 中，产生虚拟表 VT3。如果 FROM 子句包含两个以上表，则对上一个连接生成的结果表 VT3 和下一个表重复执行步骤1～步骤3，直到处理完所有的表为止
WHERE：对虚拟表 VT3 应用 WHERE 过滤条件，只有符合的记录才被插入虚拟表 VT4 中
GROUP BY：根据 GROUP BY 子句中的列，对 VT4 中的记录进行分组操作，产生 VT5
CUBE|ROLLUP：对表 VT5 进行 CUBE 或 ROLLUP 操作，产生表 VT6
HAVING：对虚拟表 VT6 应用 HAVING 过滤器，只有符合的记录才被插入虚拟表 VT7
SELECT：选择指定的列，插入到虚拟表 VT8
DISTINCT：去除重复数据，产生虚拟表 VT9
ORDER BY：将虚拟表 VT9 中的记录按照进行排序操作，产生虚拟表 VT10
LIMIT：取出指定行的记录，产生虚拟表 VT11，并返回给查询用户.

12、架构

12.1、基础架构

MySQL逻辑架构图主要分三层：

客户端层：最上层的服务并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或服务都有类似的架构。在客户端层，包括连接处理、授权认证、安全等功能。
服务器层：服务器层是MySQL的核心服务功能所在，包括查询解析、分析、优化、缓存以及内置函数等。在服务器层，实现了跨存储引擎的功能，如存储过程、触发器、视图等。服务器层与存储引擎层通过API进行通信。
存储引擎层：存储引擎层是MySQL的第三层，负责数据的存储和提取。不同的存储引擎可以选择不同的数据存储方式和索引结构，如 InnoDB、MyISAM 等。服务器层通过API与存储引擎进行通信，这些接口屏蔽了不同存储引擎之间的差异，使得对上层的查询过程透明。

在这里插入图片描述

12.2、SQL 查询在 MySQL 中如何执行

在MySQL中，一条SQL查询语句的执行过程通常包括以下几个步骤：

在这里插入图片描述

权限检查：MySQL首先会检查用户是否具有执行该语句的权限。如果用户没有足够的权限，将返回错误信息；如果有权限，继续执行后续步骤。
查询缓存（仅适用于MySQL 8.0版本之前）：MySQL会先检查查询缓存，如果之前已经执行过相同的查询，并且结果被缓存，则直接返回缓存的结果，避免执行相同的查询过程。
语法分析和语义检查：MySQL的解析器会对查询语句进行语法分析，判断语句的关键元素和结构是否符合SQL语法规范。同时进行语义检查，验证表名、列名等对象的存在性和合法性。
查询优化：MySQL的查询优化器会根据查询的复杂度、表的索引情况、统计信息等因素，选择最佳的查询执行计划。优化器会考虑多个可能的执行路径，并估算它们的成本，最终选择最高效的执行方式。
执行查询计划：一旦确定了查询的执行计划，MySQL会调用存储引擎接口，按照计划逐步执行查询。执行过程中涉及表的扫描、索引的匹配、数据的过滤和排序等操作。如果查询涉及多个表的连接，MySQL会根据连接条件将相关数据进行关联。
数据检索和返回：执行器根据查询计划获取数据，并将结果返回给客户端。如果查询涉及到大量数据，MySQL可能会使用临时表来存储中间结果，并进行排序和分页操作。

13、日志

1、日志文件有哪些

在这里插入图片描述

MySQL日志文件主要包括以下几种，每种日志文件都有不同的作用：

错误日志（Error Log）：记录MySQL的启动、运行和关闭过程中的错误信息。它可以帮助诊断和解决MySQL的问题，如数据库启动失败、连接问题、语法错误等。
慢查询日志（Slow Query Log）：用于记录执行时间超过预设阈值（由配置参数 long_query_time 定义）的查询语句。慢查询日志可以帮助开发人员和 DBA 分析和优化执行效率较低的查询语句，以提升数据库性能。
一般查询日志（General Log）：记录所有对MySQL数据库的请求，无论请求是否成功执行。一般查询日志对于调试和排查问题非常有用，但由于记录了大量信息，对数据库性能有一定影响，因此在生产环境中一般不启用。
二进制日志（Binary Log）：记录数据库的所有 DDL语句（数据定义语言）和 DML语句（数据操作语言），但不包括 SELECT语句。二进制日志以二进制格式记录，可以用于数据恢复、主从复制以及数据变更的回放。

对于InnoDB存储引擎，还有以下两种特有的日志文件：

重做日志（Redo Log）：重做日志是InnoDB引擎的事务日志，用于保证数据库的事务持久性和崩溃恢复能力。当事务提交时，相关的修改操作会首先被记录到重做日志中，然后才会写入磁盘。
回滚日志（Undo Log）：回滚日志也是InnoDB引擎的事务日志，用于支持事务的回滚操作。在事务执行期间，对数据的修改操作会同时被记录到回滚日志中，以便在事务回滚时恢复数据到修改之前的状态。

2、binlog 和 redo log

binlog和redo log是MySQL中两种不同的日志，它们有以下区别：

功能不同：binlog（二进制日志）主要用于数据恢复、主从复制和数据变更的回放，记录了数据库的 DDL语句和 DML语句（不包括SELECT语句）。redo log（重做日志）是InnoDB存储引擎的事务日志，用于保证事务的持久性和崩溃恢复能力。
记录内容不同：binlog 记录了对数据库的逻辑操作，是以文本形式的二进制文件存储，可以通过解析 binlog 文件来还原出具体的数据变更操作。redo log 记录了InnoDB存储引擎中对数据页的物理修改，是以循环写的方式在磁盘上顺序存储的。
存储位置不同：binlog 存储在磁盘上，通常以一系列的二进制文件的形式存在。redo log 存储在InnoDB存储引擎的共享表空间中，以固定大小的日志文件（通常是两个）的形式存在。
写入时机不同：binlog 在事务提交后才会被写入磁盘，因此可能会有一定的延迟。redo log 在事务提交之前就会被写入磁盘，确保了事务的持久性。
对性能的影响不同：由于 binlog 记录了更多的信息，包括逻辑操作和具体的数据值，因此对数据库性能有一定的影响。redo log 只记录了对数据页的物理修改，因此对性能的影响相对较小。

需要注意的是，binlog 和 redo log 都是用于数据恢复和故障恢复的重要组成部分，它们在不同的情况下起着不同的作用，并且可以配合使用来提供更高的数据安全性和可用性。

3、更新语句执行

更新语句的执行是Server层和引擎层配合完成，数据除了要写入表中，还要记录相应的日志。

执行器先找引擎获取 ID=2 这一行。ID是主键，存储引擎检索数据，找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交 commit 状态，更新完成。

在这里插入图片描述

从上图可以看出，MySQL在执行更新语句的时候，在服务层进行语句的解析和执行，在引擎层进行数据的提取和存储；同时在服务层对 binlog 进行写入，在InnoDB内进行 redo log 的写入。不仅如此，在对 redo log 写入时有两个阶段的提交，一是 binlog 写入之前 prepare 状态的写入，二是 binlog写入之后 commit 状态的写入。

● 为什么要两阶段提交

如果采用单阶段提交，那就是要么先写入 redo log，后写入 binlog；要么先写入 binlog，后写入 redo log。这两种方式的提交都会导致原先数据库的状态和被恢复后的数据库的状态不一致。

● 先写入redo log，后写入binlog

在写完 redo log 之后，数据此时具有 crash-safe 能力，因此系统崩溃，数据会恢复成事务开始之前的状态。但是，若在 redo log 写完时候，binlog 写入之前，系统发生了宕机。此时 binlog 没有对上面的更新语句进行保存，导致当使用 binlog 进行数据库的备份或者恢复时，就少了上述的更新语句。从而使得 id=2 这一行的数据没有被更新。

在这里插入图片描述