MySQL 查询语句执行过程
- MySQL的架构可以大致分为 Server 层 和 存储引擎层。
- Server 层主要负责客户端请求的接收、解析、执行以及结果的返回。它位于MySQL系统的上层。Server 层包括连接器、查询缓存、分析器、优化器、执行器，以及所有的内置函数，并且所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
- 存储引擎层则负责底层的数据存储、检索、事务处理等任务。每种存储引擎提供不同的存储方式和特性，适用于不同的业务场景。比如，常见的存储引擎有InnoDB、MyISAM、Memory。其中，最常用的就是InnoDB了。
连接器
- 管理客户端与MySQL服务器之间的连接。 连接器负责接收客户端发来的请求、处理会话管理、进行身份验证、权限检查等工作。当客户端发起查询请求时，连接器首先接收到客户端的连接请求。此时，客户端会使用MySQL的连接协议（通常是TCP/IP）向MySQL服务器发送请求。连接器接收到连接请求后，就会建立连接。之后，连接器会进行用户身份验证。这一步骤是通过检查用户名和密码来验证客户端是否具有连接MySQL服务器的权限。数据库内部的用户信息存储在 mysql.user 表中，包括每个用户的用户名、密码、权限等。如果用户名和密码正确，连接器允许该连接继续执行。身份验证通过，连接器会继续检查你是否有执行SQL查询的权限，而且后续的所有操作都是基于你的权限来进行的。
- 当连接建立之后，如果你后续没有任何的操作，那么这个连接就处于空闲状态，可以使用
show processlist来查看。在连接建立但没有进行任何操作时，执行show processlist会显示连接处于 Sleep 状态，这意味着连接处于空闲状态，等待后续的查询操作。Time 列表示连接空闲的时间，Info 列为空，因为没有正在执行的SQL语句。如果过了太长时间依然没有操作，那么这个连接就会自动断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。当连接断开之后，再连接数据库就需要重新建立连接。 - 数据库中的连接分为两种，分别是长连接和短连接。长连接是指客户端与数据库建立连接后，连接会保持打开状态，可以在多个操作中被复用，不会在每次操作后关闭。短连接是指客户端与数据库建立连接后，执行完一个操作或一组操作后立即关闭连接。每次执行数据库操作时，都会重新建立连接，操作完成后即断开连接。因为建立连接的过程很复杂，所以我们最好使用长连接。 但是如果连接不再使用而保持空闲状态，可能会导致数据库资源浪费。可以通过定期断开长连接或者在每次执行一个比较大的操作后，通过执行
mysql_reset_connection来重新初始化连接资源。
查询缓存（8.0之前）
- 连接建立完成之后，就可以执行select语句了，MySQL 提供了查询缓存功能，查询缓存会保存上一次执行的查询结果，下一次如果相同的查询被执行，且缓存未过期，MySQL 就可以直接返回缓存中的数据，而不需要去执行相同的查询。如果没有查到缓存，MySQL就会继续往下执行，如果查询到了结果，它就会把这个查询结果缓存起来。注意这不是 InnoDB 引擎的缓冲池。
- 但是，大多数情况下，是不建议使用缓存的。因为， 查询缓存需要维护缓存的数据一致性。当数据发生变化时，缓存会被清空或重新计算，导致缓存命中率降低。
分析器
- 分析器的动作主要有两个：词法分析和语法分析。词法分析是将查询语句的原始字符流转换成有意义的词法单元（token）。每个 SQL 查询中的关键词、表名、字段名、操作符等都会被识别为一个个的词法单元。语法分析是对词法单元序列进行检查，确定它们是否符合 SQL 的语法规则并构建出SQL语法树。例如语句：
1 | SELECT name, age FROM users WHERE age > 30; |
预处理器
- 检查SQL语句中的表或者字段是否存在，之后将通配符
*展开为表上的所有列。
优化器
- 优化器主要任务是生成最优的查询执行计划，以确保 SQL 查询可以高效地执行。优化器会对查询语句中的常量表达式进行计算，会根据索引类型和表连接方式，基于查询成本的考虑选择最优的执行计划。
执行器
- 执行器负责 实际执行查询，将查询的逻辑转化为实际操作，并从数据库中提取、处理数据，最终返回查询结果。
- 执行器阶段的具体操作是这样的：
- 访问存储引擎： 执行器会根据优化器选择的执行计划，发起对存储引擎的请求。
- 扫描表数据： 执行器根据执行计划决定扫描哪一部分数据。
- 执行 JOIN 操作： 如果查询涉及多个表的连接，执行器会根据优化器选择连接方式。
- 排序操作： 如果查询中包含了 ORDER BY 子句，执行器会根据查询计划中的指示，对查询结果进行排序。
- 聚合操作： 如果查询包含了聚合函数（如 COUNT()、SUM()、AVG()、GROUP BY 等），执行器会进行聚合计算。
1 | SELECT name, age FROM users WHERE age > 30 ORDER BY age; |
Undo Log
undo log 是一种逻辑日志，记录了事务操作之前的数据状态，用于支持事务回滚。它可以在事务开始时保存数据的“旧值”，以便在需要时可以撤销事务的操作。当事务开始的时候，undo log 中就会记录逆操作。如果事务中途发生崩溃或执行 ROLLBACK 时，undo log 会根据记录的逆操作将数据恢复到初始状态 。
对于不同类型的操作，undo log 会有不同的记录：- 当插入一行数据时，undo log 记录该行的主键和其他列数据。 回滚时，执行 DELETE 操作，删除插入的行。
- 当删除一行数据时，undo log 会完整记录被删除行的所有列值。回滚时，执行 INSERT 操作，将被删除的行重新插入。
- 当更新一行数据时，undo log 会记录被修改列的原始值。回滚时，执行逆向的 UPDATE 操作，将被修改的值恢复为旧值。
- 由于查询操作（SELECT）并不会修改任何记录，所以在查询操作执行时，并不需要记录相应的 undo log
undo log 可以用于事务回滚。当事务中断或崩溃时，undo log 会提供数据修改前的快照，允许数据库恢复到事务开始之前的状态。所以，undo log 保证了事务四大特性中的原子性（确保事务中的所有操作要么全部完成，要么全部撤销）和一致性（ 确保数据库状态可以恢复到事务开始前的一致性状态）。另外，undo log 还和 ReadView 一起实现了 MVCC，即多版本并发控制。使用Undo日志，MySQL可以为每个事务提供独立的事务视图，使得事务读取数据时能看到一致且符合隔离级别要求的数据版本。
在InnoDB存储引擎中，Undo日志分为两种：
- insert undo log：插入Undo日志是指在插入操作中生成的Undo日志。由于插入操作的记录只对当前事务可见，对其他事务不可见，因此在事务提交后可以直接删除，无需进行purge操作。
- update undo log：更新Undo日志是指在更新或删除操作中生成的Undo日志。更新Undo日志可能需要提供MVCC机制，因此不能在事务提交时就立即删除。相反，它们会在提交时放入Undo日志链表中，并等待purge线程进行最终的删除。删除操作只是设置一下老记录的 DELETED_BIT，并不真正将过时的记录删除，为了节省磁盘空间，InnoDB有专门的purge线程来清理 DELETED_BIT 为true的记录。
不同事务或者相同事务对同一记录行的修改，会使该记录行的 undo log 成为一条链表(使用roll_pointer连接)，链首就是最新的记录，链尾就是最早的旧记录，称之为版本链。在更新或删除操作之前，MySQL会将旧值写入Undo日志中。当事务需要回滚时，MySQL会根据事务的Undo日志记录，通过 DB_ROLL_PTR 找到对应的Undo日志。
根据Undo日志中记录的旧值，MySQL将旧值恢复到相应的数据行中，实现数据的回滚操作。
Redo Log
InnoDB 的 Buffer Pool
- Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。MySQL 的数据是存储在磁盘里的，但是也不能每次都从磁盘里面读取数据，这样性能是极差的。有了缓冲池后：当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘。
- Buffer Pool 在 MySQL 启动的时候根据
innodb_buffer_pool_size参数向OS申请一片连续的内存空间，默认是 128 MB。 - InnoDB 将存储的数据按页作为磁盘和内存交互的基本单位，一个页的默认大小是 16KB， 在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，接着将虚拟地址和物理地址建立映射关系。Buffer Pool 除了缓存索引页和数据页，还包括了 undo 页，插入缓存、自适应哈希索引、锁信息等等。为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括缓存页的表空间、页号、缓存页地址、链表节点等等。 控制块也是占有内存空间的，它是放在 Buffer Pool 的最前面，接着才是缓存页。
Redo Log
- Redo Log 是一种物理日志，记录了某个数据页做的具体数据修改，实现了事务的持久性。它可以保证数据库即使崩溃，也可以实现恢复。假如开启了一个事务来执行写操作，那么相关的数据页就会被加载到 Buffer Pool 并在内存中修改。这时，数据并不会立即写回磁盘，而是留在 Buffer Pool 中。 在修改 Buffer Pool 的同时，InnoDB 也会将修改操作记录到 redo log 中。 事务提交时，仅需确保 redo log 的记录被刷盘到磁盘（称为 WAL（Write-Ahead Logging）写前日志机制， MySQL的写操作不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上），此时即使内存数据未落盘，事务也被认为持久化。至于留在 Buffer Pool 中的脏页，会由后台线程在一个合适的时机异步刷回磁盘。
- Redo Log 与 实际数据刷盘：redo log 是顺序写入磁盘的，而写数据的时候，则是随机写入磁盘的。 而顺序写入的速度大约比随机写入快数倍。顺序写有点类似于磁盘顺序写入时的数据连续存储，而随机写会造成大量的寻址和磁头移动，效率低下。
- Redo Log 刷盘机制：每当产生一条 redo log 时，会先写入到 redo log buffer， redo log buffer 是在内存中的一个区域，默认大小是16MB，可以通过
innodb_log_Buffer_size调整大小，速度较快，可以通过顺序写入快速记录事务的操作。redo log buffer 中的记录并不会立即写入磁盘，而是在事务提交时，InnoDB 会将 redo log buffer 中的日志刷新到磁盘中的 redo log 文件，确保事务的持久性。除了在事务提交时刷新日志外，InnoDB 还会定期将 redo log buffer 中的内容刷盘到磁盘，以保证内存中的日志不会丢失。这样有以下优点： - 提高速度：写入内存中的 Redo Log Buffer 速度比直接写入磁盘要快得多，避免了磁盘的频繁随机写操作。
- 减少磁盘 I/O：如果每次事务修改都直接写入磁盘，会产生大量的磁盘 I/O 操作，降低性能。通过先写入内存，再根据需要批量刷新到磁盘，减少了对磁盘的直接访问。
- Redo Log会在MySQL关闭，MySQL 正常关闭时或者当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时刷盘。 InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由
innodb_flush_log_at_trx_commit参数控制）。 - Redo Log 日志文件组（redo log Group）：InnoDB 中有一个日志文件组，这个文件组由两个日志文件构成，文件名分别是ib_logfile0 和 ib_logfile1。这两个文件有一个特点，那就是它们都是环形的，在写入的时候是循环写的，也就是说，当一个文件被写满的时候，就会从头再开始写。在写入日志的时候，如果第一个日志文件（ib_logfile0）写满了，InnoDB 会自动切换到下一个日志文件（ib_logfile1），继续写入日志。一旦第二个日志文件也写满，InnoDB 会将写指针重新定位到第一个日志文件（ib_logfile0）开始覆盖已经写过的内容(前提是这些脏页已经由Buffer Pool刷入磁盘)，这时会先确保已经提交的事务的日志不会丢失。这个循环会一直进行，确保日志的不断写入。
- Redo Log 与 Undo Log : 两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：redo log 记录了此次事务修改后的数据状态，记录的是更新之后的值，主要用于事务崩溃恢复，保证事务的持久性。undo log 记录了此次事务修改前的数据状态，记录的是更新之前的值，主要用于事务回滚，保证事务的原子性。
Bin Log
- binlog 记录了逻辑操作, 是在 server 层实现的日志, 包括 SQL 语句（如 INSERT、UPDATE、DELETE，但是不会记录查询类的操作。）或者对行数据和表结构的操作。binlog 可以选择不同的格式：Statement-based 格式记录 SQL 语句；Row-based 格式记录具体数据行的变化。Mixed 格式根据操作自动选择最合适的格式。
- binlog 可以用于主从复制。在主服务器上，所有的写操作（如 INSERT、UPDATE、DELETE 等）都会被记录到 binlog 中，从服务器通过读取主服务器的 binlog 来同步数据。
- binlog 也用于 增量备份和 数据恢复。通过将 binlog 备份与数据文件一起使用，用户可以恢复到某一时刻的数据库状态。
- binlog 还可以用于审计与监控。通过分析 binlog，可以进行数据库操作的审计，查看每一条 SQL 操作。它可以帮助监控和追踪数据库的变化
- Bin log 刷盘机制：事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。
- Bin log 与 Redo log :
- binlog 是在 server 层实现的日志，所有的存储引擎都可以使用。redo log 是 InnoDB 存储引擎特有的。
- redo log 是循环写入的，记录了对数据页的 物理修改，每次对数据库表中数据的更改都会写入该日志。它并不包含 SQL 语句，而是记录修改后的物理数据页内容。并且redo log 会边写边覆盖， 保存的只有未刷盘的数据。binlog 记录的是 逻辑操作，包括 SQL 语句（如 INSERT、UPDATE、DELETE）或者对行数据的操作，保存的是全量日志，可以用来恢复整个数据库。
- redo log 实现了事务的持久性，用于崩溃恢复。binlog 主要用于备份恢复和主从复制。
- 主从复制：
- 主从复制依赖Bin log实现，复制的过程就是异步地将Bin Log传输给从库
- 主服务器在收到提交事务的请求之后，会先生成 Binlog 再提交事务，每个写入操作都会生成一个 Binlog 事件，并写入磁盘。
- 从服务器的 I/O 线程拉取 Binlog：从服务器启动 I/O线程，连接到主服务器的log dump线程，向主服务器请求 Binlog 文件。主服务器返回 Binlog 文件，并将其写入从服务器的 Relay Log。
- 从服务器的 SQL 线程执行中继日志：从服务器的 SQL 线程 从中继日志Relay Log中读取事件，执行相应的 SQL 操作，将修改同步到从服务器的数据库中。这样就确保了从服务器的数据库与主服务器一致。
两阶段提交
- MySQL 为了避免主从架构中因宕机导致的数据主从不一致问题，使用了两阶段提交来解决，两阶段提交其实是分布式事务一致性协议，它可以保证多个逻辑操作要不全部成功，要不全部失败，不会出现半成功的状态。两阶段提交把单个事务的提交拆分成了 2 个阶段，分别是准备（Prepare）阶段和提交（Commit）阶段，每个阶段都由协调者（Coordinator）和参与者（Participant）共同完成。注意，不要把提交（Commit）阶段和 commit 语句混淆了，commit 语句执行的时候，会包含提交（Commit）阶段。
- 第一阶段：准备阶段，当 MySQL 确认事务中的所有操作已经记录到 redo log 中，并且准备好将这些变更永久写入磁盘时，MySQL 会发出 PREPARE 命令，通知事务管理器该事务已经准备好。此时，所有的物理修改已经通过 redo log 被持久化，但是还没有实际提交（bin log 中的日志尚未提交，数据尚未对外可见）。如果此时发生崩溃，可以通过 redo log 来恢复数据。
- 第二阶段：提交阶段，在所有事务参与者确认准备好后（在主从复制中，所有的从库也需要确认接收到相应的日志），MySQL 将 Bin Log 写入磁盘，然后会发出 COMMIT 命令，正式提交事务。
- 半成功状态：
- 如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入。MySQL 重启后，通过 redo log 恢复数据的物理更改，但是 binlog 里面没有记录这条更新语句，在主从架构中，binlog 会被复制到从库，由于 binlog 丢失了这条更新语句，从库无法正确回滚该事务，就会导致主从数据不一致。
- 如果在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入。由于 redo log 还没写，崩溃恢复以后这个事务无效，，而 binlog 里面记录了更新语句，在主从架构中，binlog 会被复制到从库，从库执行了这条更新语句，但主库没有成功恢复写入实际的数据变更，导致主库的物理数据丢失，恢复时数据也会不一致。
事务
- 不是所有的引擎都支持事务（例如MyISAM），一般来说以 InnoDB 为准。
事务四大特性
- 持久性是通过 redo log （重做日志）来保证的； 原子性是通过 undo log（回滚日志） 来保证的； 隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的； 一致性则是通过持久性+原子性+隔离性来保证；
原子性 (Atomicity)
- 事务是一个不可分割的最小操作单元，要么全部执行，要么全部不执行。换句话说，事务中的所有操作要么全部成功，要么全部失败，没有中间状态。
- 就像银行转账一样，如果你要从账户A转账到账户B，银行会保证两个步骤（扣款和存款）要么同时成功，要么同时失败。如果中间有任何一个步骤失败，整个转账就会被撤销，不会让余额只扣掉而没有存入。
一致性 (Consistency)
- 事务必须使数据库从一个一致性状态转换到另一个一致性状态。换句话说，事务开始前和结束后，数据库的状态必须是合法的，遵循所有数据库规则、约束和触发器。
- 假设你有100元，你要转账200元给朋友。假设没有事务的一致性检查，如果系统允许你转账200元，但在扣除你账户中的100元后，系统崩溃了。那么，数据库的状态会变得不一致：你的账户余额是-100元，这就是不合法的，而你朋友的账户余额并没有增加200元，也就是转账并没有成功。这时，数据库的状态就不再一致了。
隔离性 (Isolation)
- 多个事务并发执行时，一个事务的执行不应受到其他事务的干扰。事务的隔离性确保一个事务的中间状态不会被其他事务看到。
- 假设你和朋友都去同一家餐厅吃饭，虽然你们同时点餐，但餐厅系统保证你的订单和你朋友的订单不会混在一起，虽然你们同时进行，但互不影响。换句话说，你的订单中的修改（比如加菜）在你朋友完成订单之前是不可见的。
持久性 (Durability)
- 一旦事务提交，它对数据库的改变就是永久性的，即使系统崩溃，也不会丢失。也就是说，事务成功提交之后，其结果会被永久保存在数据库中。
- 你完成了银行转账并看到“转账成功”提示，无论银行系统之后是否发生崩溃或者停机，你的账户和你朋友的账户的变动都会被永久记录。系统恢复后，银行会重新读取并保存这些变动。
事务并发问题
- 在同一个数据库中，如果有多个事务同时进行，那么可能就会出现问题。比如，脏读、不可重复读、幻读。
- 三者的严重级别：脏读 > 不可重复读 > 幻读。
脏读
- 一个事务读取了另一个事务尚未提交的数据。如果另一个事务最终回滚，当前事务读取的数据将是无效的或“脏”的。
1 | -- 假设有两个事务，T1 和 T2，它们操作同一条记录 balance （余额）。 |
不可重复读
- 在一个事务中，同一次查询，但多次读取到的数据不同，原因是其他事务提交并修改了这些数据，并且该事务没有隔离这些修改。
1 | -- 假设有两个事务，T1 和 T2，它们操作同一条记录 balance （余额）。 |
幻读
- 当一个事务读取一组数据时，另一个事务在此期间插入、更新或删除了这些数据，导致当前事务读取的数据集发生变化。
1 | -- 假设有两个事务，T1 和 T2，它们操作同一表 users（用户表），并以 age > 30 为条件查询。 |
事务隔离级别
- 在 MySQL 中，标准的隔离级别分为以下四种，从低到高分别是：读未提交 (Read Uncommitted)RU < 读已提交 (Read Committed)RC < 可重复读 (Repeatable Read)RR < 串行化 (Serializable)
读未提交 (Read Uncommitted)
- 事务可以读取其他事务尚未提交的数据，不做任何隔离，也就是可以读取“脏数据”。
- 可能导致脏读、不可重复读、幻读等问题。
读已提交 (Read Committed)
- 事务只能读取其他事务已经提交的数据。也就是说，事务只能看到其他事务提交后的数据。
- 避免了脏读问题，但仍然可能出现不可重复读和幻读的问题。
可重复读 (Repeatable Read)
- 事务在开始时读取的数据，会在整个事务期间保持一致，即事务内的多次读取操作会看到相同的数据。即使其他事务修改了数据，也不会影响当前事务的读取。
- 避免了脏读和不可重复读，在 MySQL 中可以很大程度避免幻读，所以不会使用串行化来解决，但仍可能会出现幻读。这也是 InnoDB 默认的隔离级别。
串行化 (Serializable)
- 事务通过强制加读写锁来确保所有事务按照顺序串行执行。每个事务在执行时会对相关数据加锁，直到事务完成，其他事务才能访问这些数据。
- 这样就完全避免了脏读、不可重复读和幻读问题。它是最严格的隔离级别，能完全避免并发问题，但性能开销大，效率低。
- 以下面这个例子来分析四种隔离级别：
1 | -- timeline： transaction A start && transaction B start |
- 在 RU 下，可以读到任意数据，v1是200，v2是200，v3是200
- 在 RC 下，可以读到其他事务提交后的数据，v1是100，v2是200，v3是200
- 在 RR 下，ReadView在当前事务开启时生成并固定，在当前事务提交前查询得到的值相同，v1是100，v2是100，v3是200
- 在 串行读 下，事务A和事务B读取时加共享锁，事务B修改会因为事务A读锁而阻塞，事务A提交后读锁释放，事务B才能修改数据，v1是100，v2是100，v3是200
MVCC
- 多版本并发控制, 目的主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁。这里的多版本指的是数据库中同时存在多个版本的数据，并不是整个数据库的多个版本，而是某一条记录的多个版本同时存在。
- 在数据库系统中，同时执行的事务可能涉及相同的数据，因此需要一种机制来保证数据的一致性，传统的锁机制可以实现并发控制，但会导致阻塞和死锁等问题。MVCC 会为数据库中的每一行数据保存多个版本。当一个数据被修改时，MVCC 不会直接覆盖原来的数据，而是创建一个新的版本，并记录下修改这个版本的事务 ID。这样，不同的事务在读取数据时，可以根据自己的事务 ID 和一些规则来确定应该读取哪个版本的数据。
- MVCC的优点: 提高并发性能, 读操作不会阻塞写操作，写操作也不会阻塞读操作，有效地提高数据库的并发性能。
降低死锁风险,由于无需使用显式锁来进行并发控制，MVCC可以降低死锁的风险。
当前读与快照读
- 当前读：在MySQL中，当前读是一种读取数据的操作方式，它可以直接读取最新的数据版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
- 当前读的类型：
1 | INSERT -- 检查约束不加锁 插入数据加写锁 行级锁 |
- MySQL使用读写锁来实现当前读机制：
- 锁定读（Locking Read）：锁定读是一种特殊情况下的当前读方式，在某些场景下使用。当使用锁定读时，MySQL会在执行读取操作前获取共享锁或排他锁，以确保数据的一致性。共享锁（Shared Lock）允许多个事务同时读取同一数据，而排他锁（Exclusive Lock）则阻止其他事务读取或写入该数据。锁定读适用于需要严格控制并发访问的场景，但由于加锁带来的性能开销较大，建议仅在必要时使用。
- 快照读：快照读是在读取数据时读取一个一致性视图中的数据，MySQL使用 MVCC 机制来支持快照读。具体而言，每个事务在开始时会创建一个一致性视图（Consistent View），该视图反映了事务开始时刻数据库的快照。这个一致性视图会记录当前事务开始时已经提交的数据版本。当执行查询操作时，MySQL会根据事务的一致性视图来决定可见的数据版本。只有那些在事务开始之前已经提交的数据版本才是可见的，未提交的数据或在事务开始后修改的数据则对当前事务不可见。
- 快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。
- 快照读的前提是隔离级别不是串行级别，在串行级别下，事务之间强制加锁完全串行执行，快照读会退化为当前读
- 快照读的类型：
1 | SELECT -- 不加锁 |
- 当前读和快照读的区别：当前读能获取最新数据，快照读获取的是历史版本不一定是最新；当前读使用读写锁来实现，快照读使用无锁机制而是ReadView和Undo Log版本链来实现；当前读算是悲观锁的实现思想，快照读则是乐观锁（它不加锁）
ReadView
- InnoDB 执行查询时生成的一个数据快照，记录特定事务的视图来保证事务的一致性，用于判断当前Undo Log版本链中哪些记录对当前事务可见
- 在事务执行快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前数据项活跃事务的ID
- ReadView适用范围：
- RU : 由于允许读取任意数据，没有必要
- RC : 由于能够读取事务提交后的数据，需要在当前事务每次执行查询时都生成一个新的ReadView
- RR : 由于需要保证当前事务开启到提交期间数据一致性，只在事务开启时生成一次ReadView
- 串行 ： 由于需要加锁保证事务之间串行执行，没有必要
- trx_id 与 roll_pointer: 二者都是记录的隐藏字段，前者代表最后一次修改当前记录项的事务ID, 后者是指向当前记录项在Undo Log 版本链上一个版本的数据记录
- ReadView 四字段： creator_trx_id， m_ids， min_trx_id， max_trx_id
- creator_trx_id：表示当前读取数据的事务 ID。它指明了 ReadView 是在哪个事务的上下文中创建的。它用来标识事务的唯一性。每个事务都有一个唯一的 ID，MySQL 会在 ReadView 中记录当前事务的 ID，以便在该事务读取数据时，能够过滤掉其他事务未提交的操作。
- m_ids：它是一个列表，包含了所有 活跃事务 的事务 ID。即系统中所有尚未提交的事务都在这个列表中。m_ids 使得当前事务在读取数据时，能够知道有哪些事务是“活跃的”（还在进行中），从而避免读取到这些未提交事务的变更。在 MVCC 中，当前事务只能看到已提交事务的数据，而不能看到未提交事务的变化。注意，m_ids只包含活跃事务，不包含已经提交的事务和creator_trx_id也就是当前事务
- min_trx_id：表示当前系统中所有 活跃事务（正在执行且未提交的事务）中的 最小事务 ID。也就是说，这个字段标记了系统中最早的一个未提交事务的事务 ID。min_trx_id 用来帮助识别在 ReadView 创建时，哪些事务的操作是可以被当前事务看到的。如果一个事务的ID比 min_trx_id 还小，那么这个事务已经提交，对当前事务可见
- max_trx_id：表示当前系统中所有 活跃事务 中的 最大事务 ID。即它标记了系统中最晚的一个未提交事务的事务 ID。它用来帮助判断哪些操作在 ReadView 创建时是 不可见 的。如果一个事务的ID比 max_trx_id 还大，那么这个事务还没提交，对当前事务不可见
- ReadView 可见性判断：
| trx_id所属范围 | ≤min_trx_id | ∉m_ids | ∈m_ids | ≥max_trx_id |
|---|---|---|---|---|
| creator_trx_id当前事务可见 | 已提交可见 | 已提交可见 | 未提交不可见 | 未提交不可见 |
- 先判断当前记录的trx_id是否是自己，也就是creator_trx_id，是的话自己的数据项自然是可见的
- 由于事务ID是递增的，判断当前记录的trx_id：如果小于等于min_trx_id，那么已经提交是可见的，如果大于等于max_trx_id, 那么未提交是不可见的
- 如果在 min_trx_id 和 max_trx_id 之间，那么该区间内有活跃的事务，也有提交的事务，判断trx_id是否在m_ids列表中，如果在里面那么是活跃未提交不可见的，不在那就是已提交可见的
索引
- 数据库中用于提高数据检索速度的数据结构
索引分类
- 按数据结构分类：B+tree索引、Hash索引、Full-text索引。
- 按物理存储分类：聚簇索引（主键索引）、二级索引（辅助索引）。
- 按字段特性分类：主键索引、唯一索引、普通索引、前缀索引。
- 按字段个数分类：单列索引、联合索引。
数据结构
哈希表
- 适用于等值查询的场景，查询速度快，只需要计算哈希值并定位到桶，大多数情况下查询时间接近 O(1)。但是，如果哈希表的桶数量过少或哈希函数设计不合理，冲突会增多，导致链表长度增加，从而降低查询效率。另外，哈希索引是不支持范围查询的，这种情况下只能进行全表扫描，效率是非常低的。
有序数组
- 将索引值按照 升序或降序 排列，适合用于静态数据（即数据写入后基本不更新）。数据按照键值（Key）从小到大存储在一个有序数组中，然后索引值（Key）通过数组的位置来快速定位数据。因为数组是有序的，所以它支持二分查找，查询效率为 O(log n)。它的查询效率高，特别适合范围查询，但不适合动态数据，在涉及频繁的插入、删除和更新操作时，效率就很低了。
BST 二叉搜索树
- 二叉搜索树查找效率高，平均时间复杂度为 O(log n)，比较适合动态数据的存储和查询。但是，它没有平衡机制，如果每次插入的元素都是二叉搜索树中最大的元素，那么这棵树就会退化成链表，查找的效率就降为 **O(n)**了。
AVL 平衡二叉搜索树
- 能够通过维护自身的平衡性，避免退化为链表，确保在最坏情况下的操作效率仍然为 O(log n)。
B树
- 一种平衡的多路搜索树。在 B 树中，每个节点可以拥有多个子节点。这种设计减少了树的高度，由于树的高度越高，磁盘I/O次数越多，从而降低了磁盘的 I/O 次数。
- 缺点： B 树的所有节点（包括叶子节点和非叶子节点）都存储了数据，如果目标节点是叶子节点，那么不得不通过访问非叶子节点来判断节点分支，这样就会导致每次查找都可能停留在非叶子节点读取数据，需要多次从磁盘中加载这些节点进行查询，这样就会增加磁盘 I/O 的次数，导致查询效率降低。
B+ 树
- 将所有数据都存储在叶子节点，而非叶子节点仅作为索引使用。 因此占用的空间更小。这意味着在同一层级的节点中，可以容纳更多的索引项，从而减少树的高度。树的高度降低后，从根节点到叶子节点的路径更短，查询时磁盘I/O次数减少，所以查询效率就更高。
- 所有叶子节点之间通过指针相互连接，形成一个有序双向链表。范围内的数据可以高效地从叶子节点链表中读取，避免回到上层节点查询。
MyISAM 索引
- MyISAM 存储引擎支持多种索引数据结构，比如 B+ 树索引、R 树索引、Full-Text 索引。MyISAM 存储引擎在创建表时，创建的主键索引默认使用的是 B+树索引。虽然，InnoDB 和 MyISAM 都支持 B+ 树索引，但是它们数据的存储结构实现方式不同。InnoDB 存储引擎B+ 树索引的叶子节点保存数据本身；MyISAM 存储引擎B+ 树索引的叶子节点保存数据的物理地址
InnoDB 索引
- 在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引
- 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
- 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
- 都没有，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；
- 其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。
- 创建的主键索引和二级索引默认使用的是 B+Tree 索引。
主键索引
- 建立在主键上的索引，通常在创建表的同时一起创建，一张表只能有一个主键索引，索引列可以有多个，并且索引列的值不允许有null值（主键不能为null）
- 主键索引的叶子节点不仅存储了索引值，还存储了完整的行数据，又叫做聚簇索引，因此通过主键查询可以直接获取完整的数据
- 插入数据与页分裂：如果顺序插入数据，新数据项将会链接在叶子节点之后；如果非顺序插入，由于要求主键有序，需要先定位插入的位置，然后为了腾出空间来插入需要页分裂，先申请一个新的数据页，然后把一部分数据挪动到这个新的数据页中（InnoDB 按页存储数据）。页分裂的操作，涉及较多的磁盘 I/O 操作，磁盘 I/O 开销高，；还影响了数据页的利用率，因为之前存储在一个数据页中的数据现在需要存储到两个数据页中，新数据插入后，可能需要重新排列数据，增加了插入成本。
- 删除数据与页合并：如果相邻的两个数据页中删除了部分数据，为了提高数据页的利用率，就会产生页合并。页合并的过程实际上就是页分裂的逆过程。
- 使用自增主键：
- 让主键值自增，保证插入数据的顺序性，避免页分裂的发生。因为自增主键的值是从小到大递增的，而且每次插入新数据，主键的值都会大于之前的值。所以每次插入的数据都会直接追加到索引的最后一个叶子节点，这样做的结果，就是不需要调整原有数据的顺序，不会导致索引树的复杂变化。
- 避免主键占用空间过大，影响主键索引（因为行大小的增加会占用更多的存储空间）和辅助索引的大小（因为需要存储主键值），还能避免因该字段频繁更新带来的索引更新开销。同时提高数据页的存储利用率（InnoDB 按页存储 每页16 KB）。使用自增主键而不是占用空间大的唯一字段（比如身份证号）
辅助索引
- 辅助索引是与聚簇索引相对的一种索引结构。辅助索引的特点是索引和数据是分离存储的，索引列可以有多个，索引的叶子节点只存储索引字段和主键值，也叫做二级索引。
- 回表：要获取数据字段，需要通过主键值去主键索引中，查找完整的行数据，这样涉及两个B+树的查询，也就是回表。
覆盖索引优化技术
- 一种索引优化技术，它指的是查询中所需的所有列的数据都可以直接从索引中获取，而无需访问表中的实际行（也就是回表操作）。减少了树的搜索次数，也就减少了磁盘 I/O 的次数。可以使用二级索引（所需列是主键或者多个索引列其中之一）或者联合索引（所需列是多个索引列其中之一）
唯一索引
- 建立在 UNIQUE 约束字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，并且可以有多个null值，因为 NULL 被认为是未知值，无法比较是否相等。索引列可以有多个。
- 唯一索引一般用在需要确保唯一性的字段上，不仅可以确保数据的唯一性，还能加速查询操作，有点类似于普通索引的性能优化。
普通索引
- 普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。索引列可以有多个。
前缀索引
- 前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。索引列可以有多个。
order by无法使用前缀索引，前缀索引也无法作为覆盖索引- 使用前缀索引的目的是为了减少索引字段占用的存储空间，增加索引条目，提升查询效率。
单列索引
- 建立在单列上的索引，例如单列的主键/辅助/唯一/普通/前缀索引
联合索引
- 建立在多个列上的索引，又叫做复合索引，例如多列的主键/辅助/唯一/普通/前缀索引
- 建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。区分度过低，优化器可能选择全表扫描
最左前缀匹配原则
- 当使用联合索引时，MySQL 会根据索引的最左边列开始进行匹配，并依次向右匹配，直到遇到范围查询或某列缺失时停止匹配。
- 联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。
- 假如有如下联合索引
idx_a_b_c(a,b,c): - 对于
where a = x, b = y, c = z使用全部索引，完全匹配
- 对于
- 对于
where a = x, b = y使用全部索引，完全匹配
- 对于
- 对于
where b = y，a = x不使用索引，不符合最左匹配
- 对于
- 对于
where a = x, c = z, b = y，使用部分索引，匹配a字段终止
- 对于
- 对于
where a > x, b = y， 使用部分索引，对于字段a满足条件a > x使用索引, b不使用索引
- 对于
- 对于
where a >= x, b = y，使用索引，对于字段a满足条件a >= x使用索引，对于字段b满足条件a = x AND b = y使用索引
- 对于
- 对于
where a between a1 and a2, b = y，使用索引，对于字段a满足条件a between a1 and a2使用索引，对于字段b满足条件a = a1 AND b = y OR a = a2 AND b = y使用索引
- 对于
- 对于
where a like 'x%', b = y, 使用索引，对于字段a满足条件like 'x%'使用索引，对于字段b满足条件a = x AND b = y使用索引
- 对于
索引下推
- 索引下推会让存储引擎尽可能多地利用索引条件过滤掉不符合的数据，从而减少回表次数
- 例如联合索引
idx_a_b_c(a,b,c)，对于where a > x, b = y， 使用部分索引，对于字段a满足条件a > x使用索引, b不使用索引，在MySQL 5.6之后将会在字段a筛选后直接在idx_a_b_c索引中获取b而不是回表
索引使用场景
- 索引最大的好处是提高查询速度，但是索引也是有缺点的：
- 需要占用物理空间，数量越大，占用空间越大；
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
- 会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。
- 适用索引:
- 字段有唯一性限制的，比如商品编码；
- 经常用于
WHERE查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
- 经常用于
- 经常用于
GROUP BY和ORDER BY的字段，这样在查询的时候就不需要再去做一次排序了，因为建立索引之后在 B+Tree 中的记录都是排序好的。
- 经常用于
- 不需要创建索引:
- WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引,反而占用物理空间。
- 字段中存在大量重复数据，区分度低，不需要创建索引
- 表数据太少的时候，不需要创建索引；
- 经常更新的字段不用创建索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。
索引优化
- 前缀索引
- 覆盖索引
- 自增主键索引
- 索引字段 NOT NULL 约束
- 避免索引失效
索引失效
- 使用后缀模糊查询
LIKE '%x'，或者两端模糊查询LIKE '%x%',因为索引是按照索引值排序存储的，只能根据前缀比较，这样会使用全表扫描
- 使用后缀模糊查询
- 索引列使用了函数，索引保存的是索引字段的原始值，函数计算值无法根据索引查找，只能通过把索引字段的取值都取出来，然后依次进行函数计算来进行条件判断，因此采用的就是全表扫描的方式。除非这个索引就是用函数计算值建立的（MySQL 8）
- 索引列参与了表达式计算，索引保存的是索引字段的原始值，表达式计算值无法根据索引查找，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。
- 对索引列使用了隐式类型转换(或者使用时没有注意类型匹配导致隐式类型转换)，底层有可能对索引列调用CAST函数。例如字符串与整数，MySQL会将字符串优先转换成整数，如果索引列是整数会使用索引，如果是字符串那么会因为调用CAST函数不使用索引。
- 索引列不符合最左前缀匹配原则或不完全符合导致索引截断
- WHERE 子句使用了 OR，某个条件中的列没有索引，因为只要满足其中一个条件，将会使用全表扫描
- JOIN 连接的两个字段，发生了隐式类型转换
- 索引列的条件是判断 NULL/NOT NULL
锁
- 在 MySQL 中，锁是一种机制，用于控制对数据库资源（如表或行）的并发访问，确保数据的一致性和完整性。它的作用主要有保证数据的一致性、防止并发冲突等。
锁的分类
MySQL 中的锁按照粒度的大小，可以分为全局锁、表级锁和行级锁。
- 全局锁一般使用 FTWRL 来开启，锁定整个数据库
- 表级锁一般分为表锁，元数据锁，意向锁，自增锁
- 行级锁一般分为记录锁，间隙锁，临键锁，插入意向锁。MyISAM不支持行级锁
全局锁
- 使用命令来开启，执行后整个数据库处于只读状态，所有对数据的增删改和表结构的更改操作将被阻塞，除非手动解锁或者连接断开，主要用于全库逻辑备份：
1 | flush tables with read lock; |
- 对于 InnoDB, 由于支持RR，使用 mysqldump时加上选项
-single-transaction参数的时候会在备份前开启事务并且创建ReadView，不会阻塞更新操作
表级锁
表锁
- 分为读锁(共享锁)和写锁(排它锁)，另外开启表锁的本会话（线程）将无法访问其他表
1 | LOCK TABLES my_table READ; |
元数据锁
- 元数据锁*不是用户显式加的锁，也就是说，当我们对数据库中的某个表进行操作时，这个锁就会被自动加上，并且在事务结束后才释放。它的主要作用是协调多个线程对表或其他数据库对象的访问和修改，防止DDL和DML之间的冲突。
- 元数据锁也分为读锁和写锁，对表数据进行CRUD加读锁，对表结构进行更新操作加写锁。如果写操作因为元数据读锁而阻塞，由于等待队列中写锁的优先级高于读锁，写操作之后的读操作将被一并阻塞
意向锁
- 意向锁本身是表级锁，但目的是在 InnoDB 中为记录加上行级别的共享锁/排它锁之前，为表级别加上意向共享锁/意向排他锁，并且意向共享锁和意向排它锁之间不会冲突。
- 加上意向共享锁/意向排它锁，意味着当前表正在进行行级别的DML操作（增删查改），使得意向共享锁/意向排它锁与共享表锁/排他表锁冲突，能够快速判断当前表是否有记录加锁而不必遍历查看，便于表级锁的添加。
自增锁
- 设置
AUTO_INCREMENT自增主键字段，当字段进行自增操作时会加锁，该锁不是事务提交后释放，而是执行完插入语句后释放，在插入新数据时，会加锁，然后主键自增并添加新数据，执行完成后释放锁，保证自增操作是原子的。但是对大量数据进行插入的时候会影响插入性能。 - 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。一样也是在插入数据的时候，会为被
AUTO_INCREMENT修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。 - InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用自增锁，还是轻量级的锁。
- 当 innodb_autoinc_lock_mode = 0，就采用自增锁，语句执行结束后才释放锁；
- 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。性能最高的方式，但是当搭配 binlog 的日志格式是 statement（记录SQL） 一起使用的时候，在主从复制的场景中会发生数据不一致的问题。
- 当 innodb_autoinc_lock_mode = 1：普通 insert 语句，自增锁在申请之后就马上释放；类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放
行级锁
记录锁
- 分为读锁和写锁，事务结束后自动释放，所以一定要在事务中加锁才能生效
1 | transaction A start |
间隙锁
- 锁住当前记录条目一定范围内的记录，但不包括当前记录本身，在RR/串行中被应用于解决幻读问题（一组数据之间被插入或修改），同样分为共享锁和排它锁，但间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁，共享和排他的间隙锁是没有区别的，他们相互不冲突，且功能相同，所以两个不同事务对同一范围的间隙锁不会冲突。另外，索引失效采用全表扫描时会对每一个记录加间隙锁，影响性能
临键锁
- 记录锁和间隙锁的组合，锁住当前记录条目一定范围内的记录，包括当前记录本身，既能保护当前记录，也能保护一定范围内的其他记录。由于包含记录锁，排它锁之间是会冲突的
插入意向锁
- 事务的插入操作因间隙锁/临键锁而阻塞时，会加上插入意向锁，并将锁的状态设置为等待（加锁时先生成锁结构再设置锁状态，锁状态为正常时才代表成功加锁），直到间隙锁/临键锁被释放，它不是意向锁，而是行级别的特殊意向锁，它代表的是锁定一个插入的位置（插入点），并且两个不同事务对同一范围的间隙锁/临键锁会和试图在该范围进行插入操作的插入意向锁冲突
数据库基础概念
数据库与数据库管理系统
- DB 与 DBMS 区别：数据库是通过DBMS创建和操纵的容器，我们并不直接访问数据库，而是使用是DBMS，它替我们访问数据库。
数据库DB
- 长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合，简单的说，保存有组织的数据的容器。
数据库管理系统DBMS（数据库软件）
- 操纵和管理数据库的大型软件。
关系数据库相关概念
关系数据库与非关系数据库
- 关系型数据库(RDBMS)：基于关系模型（二维表结构），建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
- 非关系型数据库（Not Only SQL）: 没有严格的数据模式（K-V/Document/Column/Graph/TimeScale），也就是不基于关系模型的数据库
关系概念设计
- 模式：关于数据库和表的布局及特性的信息
- 表（table）：某种特定同一类型数据的结构化清单。
- 列（column）：表中的一个字段。所有表都是由一个或多个列组成的。
- 数据类型： 所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据。数据类型还帮助正确地排序数据，并在优化磁盘使用方面起重要的作用。
- 行（row）：表中的一个记录。有时可以叫它为数据库记录（record）
- 主键（primary key）: 一列（或一组列，主键可以是几个列的组合），其值能够唯一区分标识表中每个行。注意，任意两行都不具有相同的主键值；每个行都必须具有一个主键值（主键列不允许NULL值）。
- SQL: 结构化查询语言，操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准，SQL语句不区分大小写，关键字建议使用大写。
关系数据库存储设计原则
E-R模型（实体-关系模型）
- 由实体/属性/关系组合而成，设计步骤是识别实体/定义属性/确定关系/标注约束/转换为物理模型
规范化
- 避免数据冗余、保证数据一致性、优化存储结构
存储设计规范化的五大范式
1NF
- 原子性：字段值不可分，单一值：每列单一数据类型，唯一表名，能够消除重复组
- 例如：电话号码字段不应存储逗号分隔的多个号码
2NF
- 满足1NF，消除部分依赖（非主键字段必须完全依赖主键），能够解决数据冗余
- 例如：订单表(订单ID,产品ID,产品名称) → 产品名称依赖产品ID而非订单ID
3NF
- 满足2NF，消除传递依赖（非主键字段间无依赖），能够解决更新异常
- 例如：员工表(员工ID,部门,部门地址) → 部门地址依赖部门而非员工ID
BCNF
- 满足3NF，主属性不能依赖非主键字段，能够解决复杂依赖
- 例如：课程表(学生,课程,教师) → 假设每位教师只教一门课但每门课多位教师
4NF
- 满足BCNF，消除多值依赖，能够解决多维关联
- 例如：员工技能证书表(员工,技能,证书) → 技能和证书相互独立
字段类型
数值类型
| 类型 | 存储空间(字节) | 范围(有符号) | 范围(无符号) | 特点说明 |
|---|---|---|---|---|
| TINYINT | 1 | -128 到 127 | 0 到 255 | 极小整数，如布尔值(0/1) |
| SMALLINT | 2 | -32,768 到 32,767 | 0 到 65,535 | 小范围整数 |
| MEDIUMINT | 3 | -8,388,608 到 8,388,607 | 0 到 16,777,215 | 中等范围整数 |
| INT | 4 | -2,147,483,648 到 2,147,483,647 | 0 到 4,294,967,295 | 标准整数，最常用 |
| BIGINT | 8 | ±9.22×10¹⁸ | 0 到 18,446,744,073,709,551,615 | 极大整数 |
| FLOAT | 4 | ±1.175×10⁻³⁸ 到 ±3.402×10³⁸ | - | 单精度浮点数，约7位精度 |
| DOUBLE | 8 | ±2.225×10⁻³⁰⁸ 到 ±1.798×10³⁰⁸ | - | 双精度浮点数，约15位精度 |
| DECIMAL | 变长 | 取决于精度和小数位 | - | 精确小数，适合财务计算 |
字符串类型
| 类型 | 最大长度 | 存储特点 | 适用场景 | 字符集影响 |
|---|---|---|---|---|
| CHAR(M) | 255字符 | 定长存储，预分配空间 | 固定长度字符串（如MD5） | ✅ |
| VARCHAR(M) | 65,535字符 | 变长存储，+1/2字节长度前缀 | 可变长度字符串（姓名/地址） | ✅ |
| TINYBLOB | 255字节 | 二进制存储 | 小二进制对象 | ❌ |
| TINYTEXT | 255字符 | 文本存储 | 短文本（简介/评论） | ✅ |
| BLOB | 65KB | 二进制存储 | 图片/音频小文件 | ❌ |
| TEXT | 65KB | 文本存储 | 文章/产品描述 | ✅ |
| MEDIUMBLOB | 16MB | 二进制存储 | 中等文件存储 | ❌ |
| MEDIUMTEXT | 16MB | 文本存储 | 书籍章节/长内容 | ✅ |
| LONGBLOB | 4GB | 二进制存储 | 大文件（视频/压缩包） | ❌ |
| LONGTEXT | 4GB | 文本存储 | 超长文本（日志/电子书） | ✅ |
日期时间类型
| 类型 | 格式 | 范围 | 存储空间 | 特点说明 |
|---|---|---|---|---|
| DATE | ‘YYYY-MM-DD’ | 1000-01-01 到 9999-12-31 | 3字节 | 只存储日期 |
| TIME | ‘HH:MM:SS[.uuuuuu]’ | -838:59:59 到 838:59:59 | 3字节 | 时间或持续时间 |
| YEAR | YYYY | 1901 到 2155 | 1字节 | 存储年份 |
| DATETIME | ‘YYYY-MM-DD HH:MM:SS’ | 1000-01-01 00:00:00 到 9999-12-31 23:59:59 | 8字节 | 日期+时间，与时区无关 |
| TIMESTAMP | ‘YYYY-MM-DD HH:MM:SS’ | 1970-01-01 00:00:01 到 2038-01-19 03:14:07 | 4字节 | 自动跟踪时间，存储UTC时间戳 |
数据库操作
权限控制 （DCL数据控制语言）
1 | USE mysql; SELECT * FROM user; -- 查询用户 相关信息存在数据库mysql中 需要先使用数据库 |
选择与切换
1 | SHOW DATABASES; -- 列出所有数据库 |
查看数据库(SHOW) （DDL数据定义语言）
1 | SHOW DATABASES; -- 返回可用数据库的一个列表。 |
数据库的创建和删除 （DDL数据定义语言）
1 | CREATE DATABASE IF NOT EXISTS db_Name DEFUALT CHARSET your_charset COLLATE COLL_Rule ; |
检索数据（SELECT）（DQL数据查询语言）
1 | SELECT Column_Name FROM Table_Name; -- 检索单个列 |
排序检索数据（ORDER BY）（DQL数据查询语言）
- 子句：SQL语句由子句构成，有些子句是必需的，而有的是可选的。一个子句通常由一个关键字和所提供的数据组
成。典型子句的例子有SELECT语句的FROM子句。
1 | -- ORDER BY 子句必须在 FROM 子句之后 |
过滤数据（WHERE）（DQL数据查询语言）
- WHERE 子句支持的条件操作符：
| 条件操作符 | 用途 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| BETWEEN-AND | [a,b] |
- NULL：无值（no value），在一个列不包含值时，称其为包含空值NULL
- NOT：WHERE子句中用来否定后跟条件的关键字。支持修饰IN, BETWEEN, EXIST 子句。
- AND：用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。
- OR：WHERE子句中使用的关键字，用来表示检索匹配任一给定条件的行。
- IN：WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当但执行更快，而且能够包含SELECT子句
1 | -- WHERE 子句必须在 FROM 子句之后 |
模糊查询（LIKE）（DQL数据查询语言）
- 通配符：用来匹配值的一部分的特殊字符。
- 搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件。在搜索模式中，区分大小写
- % 通配符：%代表搜索模式中给定位置的0个、1个或多个字符。不能匹配值NULL。
- _ 通配符：_代表搜索模式中给定位置的1个字符。不能匹配值NULL。
1 | SELECT C FROM T WHERE C LIKE 'Cia%'; -- 匹配前缀模式 |
创建计算字段（DQL数据查询语言）
- 拼接（concatenate）：将值联结到一起构成单个值。在 MySQL 中使用 ConCat(args …)来拼接多个列， 而不是’+’或者’||’。
- Trim() ：用于去除字符串两端的空格，相对的，LTrim/RTrim去除字符串左侧/右侧的空格。
- AS 关键字：为一个字段赋予别名，这个字段可以是表中的列，也可以是经过计算后的列。
1 | SELECT Concat( C_a, 'and', C_b ) FROM T; -- 组合为单个列，内容是字符串“C_aandC_b” |
数据处理函数（DQL数据查询语言）
文本处理函数
| 函数 | 用途 |
|---|---|
| Trim()/LTrim()/RTrim() | 去除两端/左端/右端空格 |
| Length() | 返回串的长度 |
| SubString() | 返回子串的字符 |
| Locate() | 查找一个子串 |
| Lower()/Upper() | 转小/大写 |
| Soundex() | 返回串的Soundex()值 |
- SOUNDEX() : 一个将任何文本串转换为描述其语音表示的字母数字模式的算法
日期和时间处理函数
- 日期的格式：yyyy-mm-dd
- 时间的格式: hh:mm:ss
- DATETIME: 数据类型，同时存储日期与时间
| 函数 | 用途 |
|---|---|
| AddDate() | 增加一个日期 |
| AddTime() | 增加一个时间 |
| CurDate() | 获取当前日期 |
| CurTime() | 获取当前时间 |
| Date() | 获取DATETIME的日期部分 |
| Time() | 获取DATETIME的时间部分 |
| DateDiff() | 计算两个日期的差 |
| Date_Add() | 高度灵活的日期运算函数 |
| Date_Format() | 返回格式化的日期/时间串 |
| Year() | 返回日期的年份部分 |
| Month() | 返回日期的月份部分 |
| Day() | 返回日期的天数部分 |
| DayOfWeek() | 返回日期对应的星期 |
| Hour() | 返回时间的小时部分 |
| Minute() | 返回时间的分钟部分 |
| Second() | 返回时间的秒钟部分 |
| Now() | 返回当前日期和时间 |
1 | SELECT C FROM T WHERE Date(dt) = '2026-01-01'; |
数值处理函数
| 函数 | 用途 |
|---|---|
| Abs() | 绝对值 |
| Sqrt() | 平方根 |
| Mod() | 取余 |
| Exp() | 指数值 |
| Pi() | 圆周率 |
| Rand() | 随机数 |
| Sin() | 正弦值 |
| Cos() | 余弦值 |
| Tan() | 正切值 |
数据聚集函数
- 聚集函数（aggregate function）：运行在行组上，计算整列和返回单个值的函数。
- 可以配合算术运算来对多个列进行计算
- 一个SELECT 子句中可以有多个聚集函数。
| 函数 | 用途 |
|---|---|
| SUM() | 求和 |
| AVG() | 平均数 |
| COUNT() | 计算行数 |
| MAX() | 最大值 |
| MIN() | 最小值 |
SUM()
- 对指定列求和。忽略NUll。SUM()也可以用来合计表达式计算值。
AVG()
- AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。忽略列值为NULL的行。
MIN() 和 MAX()
- MIN()/MAX()对于文本数据类型，如果数据按相应列排序，则返回首行/末行; 另外它们都忽略空值
COUNT()
- count() : 使用COUNT(*)对表中行的数目进行计数，包括空值。COUNT(column)对特定列中具有值的行进行计数，不包括空值。
- count(1)和count(*) : 都用于统计行数，都使用全表扫描，一个是计算表达式1的次数，一个是。MySQL优化器会将
COUNT(1)重写为COUNT(*)，如果有多个二级索引的时候，优化器会使用key_len 最小的二级索引进行扫描，实际执行完全相同操作, 性能几乎一致。 - 另外对于 MyISAM 实现，由于表的元数据包含 row_count，不同于 InnoDB，它不需要遍历表。但是如果查询语句有 WHERE 子句，那么 MyISAM 和 InnoDB 一样都需要遍历表。
count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。所以，如果要执行，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。- 不要使用 count(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。
1 | SELECT SUM(C) AS S FROM T; |
数据分组（GROUP BY 和 Having）（DQL数据查询语言）
GROUP BY 子句
- GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。
- GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
- 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）,按指定的顺序先后分组得到最终结果。
- GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。GROUP BY 的字段不能使用别名。
- 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
- 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
- GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
1 | SELECT country, |
- 一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据, 尽管多数情况下 GROUP BY 会按照分组顺序输出，但不总是这样另外用户要求按照其他排序方式时，就必须使用 ORDER BY，所以综上，GROUP BY 与 ORDER BY 一起使用，GROUP BY 先于 ORDER BY。
- GROUP BY 与 ORDER BY :
- ORDER BY 结果是排序产生的输出, GROUP BY 结果是分组行，但输出可能不是分组的顺序
- ORDER BY 对任意列都可以使用（甚至非选择的列也可以使用）,GROUP BY 只可能使用选择列或表达式列，而且必须使用每个选择列表达式
- ORDER BY 不一定需要使用, GROUP BY 如果与聚集函数一起使用列（或表达式），则必须使用
Having 子句
- HAVING非常类似于WHERE， HAVING支持所有WHERE能用的操作符，唯一的差别是WHERE过滤行，而HAVING过滤分组。或者说，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤
1 | SELECT ID, COUNT(*) AS ITEMS FROM Table_Name GROUP BY ID HAVING ITEMS >= 2; |
子查询 （DQL数据查询语言）
利用子查询过滤数据
- 在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。
- 虽然子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、不等于（<>）等
1 | SELECT ID FROM T WHERE Province IN ( |
作为计算字段使用子查询
- 相关子查询（correlated subquery）: 涉及外部查询的子查询。
1 | SELECT custmer_name, custmer_state, ( |
联结表（JOIN）（DQL数据查询语言）
- 外键（foreign key）: 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
- 联结：当所需的数据存储在不同的表中，为了能够正确检索出数据需要使用联结。在联结两个表时，实际上做
的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。如果没有 WHERE 子句限定联结条件将返回笛卡尔积 - 笛卡儿积（cartesian product）：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
- 聚集函数也能和联结一起使用。
- 注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
- 保证使用正确的联结条件，否则将返回不正确的数据。
- 应该总是提供联结条件，否则会得出笛卡儿积。
- 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。
内联结（INNER JOIN 等值联结）
1 | SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name; |
自联结
- 使用WHERE子查询在子句中联结同一张表，或者使用表别名，将指向同一张表的两个别名联结
- 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。
1 | SELECT prod_id, prod_name FROM products WHERE vend_id = ( |
自然联结
- 自然联结排除多次出现，使每个列只返回一次,保证返回结果的列的唯一性，联结时只能选择那些唯一的列。这一般是通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成的
1 | SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price |
外联结（OUTER JOIN）
- 使用 OUTER JOIN - ON 来指定联结的类型（而不是在WHERE子句中指定）。但是，与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行，也就是说，在一张表的某些行，根据关联列在另一张表中没有对应的行，外部联结也会将它们返回。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。
- 左外部联结与右外部联结的区别： 它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定。
组合查询（UNION）
- 将多个查询（多条 SELECT 语句）合并执行并将结果作为单个查询结果集返回，称为组合查询。组合查询会对多条SELECT 返回的结果进行去重，如果不想这么做应该使用
UNION ALL - 组合查询使用场景：
- 在单个查询中从不同表返回类似结构的数据
- 对单个表执行多个查询，要求按单个查询返回数据
- 多数情况下组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同，换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出，但这两种技术在不同的查询中性能也不同。
- UNION 使用规则：
- UNION 必须由两条或两条以上的SELECT语句组成， 使用 UNION 关键字将多条完整的 SELECT 语句相连即可
- UNION中的每个查询必须包含相同的列、表达式或聚集函数，但次序可以不同
- 列数据类型可以不完全相同，但必须是 DBMS 可以隐式转换的类型
- 在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。虽
然ORDER BY子句似乎只是最后一条SELECT语句的组成部分，但实际上MySQL将用它来排序所有SELECT语句返回的所有结果。
- 在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。虽
插入数据（INSERT）（DML数据操作语言）
- 插入可以用以下几种方式使用：
- 插入完整的行
- 插入行的一部分
- 插入多行
- 插入某些查询的结果
插入完整的行
- 使用 VALUES 子句，对每个列必须按定义顺序提供一个值，如果不想给定值应该使用 NULL, 另外自增主键应该使用 NULL。 由于次序固定，一旦表结构改变那么插入语句将会出现问题，不建议使用
- 使用 VALUES 子句之前，明确给定列名，插入时按照给定的次序匹配，但这不会受限于表结构定义的次序，另外使用这种语法，还可以省略列。这表示可以只给某些列提供值，给其他列不提供值。省略的列必须定义为允许NULL值（无值或空值），或者该列在表定义中给出默认值
1 | INSERT INTO T VALUES('alice', 0, 21); |
插入多行
- 可以一次使用多个INSERT 语句，语句之间分号分隔，或者使用一条INSERT语句多个VALUES子句，子句之间逗号分隔。
- 此技术可以提高数据库处理的性能，因为MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快。
1 | INSERT INTO T VALUES('alice', 0, 21), VALUES('bob', 1, 21); |
插入检索出的数据（INSERT SELECT）
- INSERT 语句在给定列，之后使用一条SELECT语句作为插入值，具体插入行数由SELECT决定，如果没有行被插入也是合法的。
- SELECT 指定的列名可以与INSERT要插入的表不同，因为值的插入是按照INSERT 语句给出的顺序决定的
更新数据和删除数据 （UPDATE & DELETE）（DML数据操作语言）
更新数据
- 更新数据有两种方式：
- 更新表中特定行
- 更新表中所有行
- UPDATE 语句：由更新的表，列名和新值，以及过滤条件(WHERE子句)组成。不要省略WHERE子句，因为稍不注意，就会更新表中所有行。
- UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据用于 SET 子句更新列数据
- 如果不想让 UPDATE 因某个字段更新失败回滚，使用
UPDATE IGNORE
1 | UPDATE T SET C_1 = 'ciallo'，C_2 = '0721' WHERE C_3 = 1; |
删除数据
- 删除数据有两种方式：
- 从表中删除特定行
- 从表中删除所有行
- DELETE 语句： 由更新的表和过滤条件(WHERE子句)组成。不要省略WHERE子句，因为稍不注意，就会删除表中所有行。
- 如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更
快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）
1 | DELETE T WHERE name = '0d00'; |
创建和操纵表（CREATE & ALTER）（DDL数据定义语言）
创建表
- 使用 关键字CREATE TABLE, 必须给出表的名字，列的名字和定义，用逗号分隔； CREATE TABLE语句也可能会包括其他关键字或选项，但至少要包括表的名字和列的细节。
- 实际的表定义（所有列）括在圆括号之中。各列之间用逗号分隔。每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型。整条语句由右圆括号后的分号结束。
- 在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表（请参阅后面的小节），然后再重建它，而不是简单地用创建表语句覆盖它。如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。
- 约束：作用于表中字段上的规则，可以限制存储在表中的数据。非空约束：NOT NULL；唯一约束：UNIQUE；主键约束 PRIMAY KEY。在建表或者修改表的时候添加约束。
- 表的主键可以在创建表时用 PRIMARY KEY关键字指定。主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。
- 每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定
- AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量，给该列赋予下一个可用的值。每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如通过使它成为主键）。 另外，如果 INSERT 给定了一个值（只要它没被使用过），后续将从它开始自增。
- 可使用
SELECT last_insert_id(), 此语句返回最后一个AUTO_INCREMENT值 - 默认值用CREATE TABLE语句的列定义中的
DEFAULT关键字指定。MySQL不允许使用函数作为默认值，它只支持常量
1 | CREATE TABLE employees ( |
更新表
- 使用ALTER TABLE语句，必须给出要更改的表名（该表必须存在，否则将出错）和所做更改的列表。
1 | ALTER TABLE Table_Name ADD Column_Name Type COMMENT CONSTRAINT; -- 添加列 |
- ALTER TABLE的一种常见用途是定义外键。
1 | ALTER TABLE 从表 ADD CONSTRAINT 外键名称 FOREIGN KEY (从表外键列) REFERENCES 主表(主表主键列) [ON DELETE 操作选项] [ON UPDATE 操作选项]; |
事务操作
- 开启事务：BEGIN; START TRANSACTION;
- 设置事务手动提交：SET @@AUTOCOMMIT = 0;
- 查询事务是否自动提交：SELECT @@AUTOCOMMIT;
- 手动提交事务：COMMIT;
- 手动回滚事务：ROLLBACK;
- 回滚点：SAVEPOINT p1; 每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。为了回退到保留点使用 ROLLBACK TO p1;
