《MySQL 实战 45 讲》笔记三

极客时间教程 - MySQL 实战 45 讲 学习笔记

31 误删数据后除了跑路，还能怎么办？

误删数据的分类：

• 误删行：使用 delete 语句误删数据行。
• 误删表：使用 drop table 或 truncate table 语句误删数据表。
• 误删库：使用 drop database 语句误删数据库。
• 误删实例：使用 rm 命令误删整个 MySQL 实例。

误删行的恢复：

• 使用 Flashback 工具通过解析 binlog 恢复数据，前提是 binlog_format=row 和 binlog_row_image=FULL。
• 恢复数据时，建议在临时库或从库上操作，避免对主库造成二次破坏。
• 预防措施：
  • 设置 sql_safe_updates=on，防止无条件的 delete 或 update 操作。
  • 代码上线前进行 SQL 审计。

误删库/表的恢复：

• 使用全量备份加增量日志的方式恢复数据。
• 恢复流程：
  1. 取最近一次全量备份。
  2. 用备份恢复出一个临时库。
  3. 从日志备份中取出误删操作之后的日志，应用到临时库。
• 使用 GTID 模式可以更方便地跳过误删操作的 binlog。
• 加速恢复的方法：将临时库设置为线上备库的从库，利用并行复制技术加速恢复。

延迟复制备库：

• 通过 CHANGE MASTER TO MASTER_DELAY = N 命令，设置备库延迟 N 秒复制主库的数据。
• 延迟复制可以在误删操作后，提供一个时间窗口来恢复数据，缩短恢复时间。

预防误删库/表的方法：

• 账号分离：限制业务开发人员的权限，避免误操作。
• 操作规范：删除表前先改名，观察一段时间后再删除。

误删实例的恢复：

• 对于高可用集群，误删单个节点的数据可以通过 HA 系统自动恢复。
• 建议跨机房或跨城市备份数据，防止大规模数据丢失。

总结：

• 误删数据的恢复依赖于备份和 binlog，定期检查备份的有效性非常重要。
• 预防误删数据比事后处理更为重要，建议通过账号分离、操作规范等方式减少误操作的风险。
• 数据和服务的可靠性需要各个环节共同保障，不仅仅是运维团队的责任。

32 为什么还有 kill 不掉的语句

kill 命令的类型：

• kill query + 线程 id：终止线程中正在执行的语句。
• kill connection + 线程 id：断开线程的连接，并终止正在执行的语句。

kill 命令的执行机制：

• kill 命令并不是立即终止线程，而是设置线程的状态为 KILL_QUERY 或 KILL_CONNECTION，并发送信号唤醒线程。
• 线程在执行过程中会检查自身状态，如果发现被标记为 KILL_QUERY 或 KILL_CONNECTION，才会进入终止逻辑。

kill 不掉的两种情况：

• 线程没有执行到判断状态的逻辑：例如，线程在等待进入 InnoDB 执行时，没有检查自身状态，导致 kill 命令无效。
• 终止逻辑耗时较长：例如，大事务回滚、大查询删除临时文件、DDL 命令删除临时文件等操作耗时较长，导致线程无法立即终止。

客户端误解：

• 表多导致连接慢：实际上是因为客户端在连接后执行了表名补全功能，导致连接过程变慢。可以通过 -A 参数关闭自动补全功能。
• –quick 参数：该参数并不是让服务端加速，而是让客户端跳过缓存，直接处理结果。虽然客户端响应更快，但可能会降低服务端性能。

总结：

• kill 命令并不能立即终止线程，而是通过设置状态和发送信号来触发线程的终止逻辑。
• 如果线程处于 Killed 状态，可以通过调整系统环境（如增加并发度、减少系统压力）来加速终止逻辑的执行。
• 对于长时间无法终止的线程，建议等待其完成终止逻辑，而不是强行重启 MySQL 进程。

33 我查这么多数据，会不会把数据库内存打爆？

全表扫描对 Server 层的影响：

• MySQL 采用“边读边发”的方式处理查询结果，不会在服务端保存完整的结果集。
• 查询结果会先写入 net_buffer（默认 16K），当 net_buffer 写满后，通过网络接口发送给客户端。
• 如果客户端接收数据慢，服务端的网络栈可能会被写满，导致查询状态显示为“Sending to client”。
• 对于返回结果较少的查询，建议使用 mysql_store_result 接口，直接将结果保存到客户端内存；对于大查询，可以使用 mysql_use_result 接口，逐行处理结果。

“Sending data”状态的含义：

• “Sending data”状态并不一定表示正在发送数据，而是表示查询正在执行过程中，可能包括锁等待等操作。

全表扫描对 InnoDB 的影响：

• InnoDB 使用 Buffer Pool 来加速查询，Buffer Pool 的大小由 innodb_buffer_pool_size 参数控制，通常设置为物理内存的 60%~80%。
• Buffer Pool 使用改进的 LRU 算法来管理内存，将链表分为 young 区域和 old 区域，避免全表扫描导致的热数据被淘汰。
• 全表扫描的数据页会被放入 old 区域，只有在 old 区域停留超过 1 秒的数据页才会被移到 young 区域，从而避免冷数据影响热数据的命中率。

Buffer Pool 的内存命中率：

• 通过 show engine innodb status 可以查看 Buffer Pool 的命中率，稳定运行的线上系统应保证命中率在 99% 以上。
• 如果 Buffer Pool 满了，InnoDB 会淘汰最久未使用的数据页（LRU 算法）。

总结：

• 大查询不会耗尽 MySQL 服务端的内存，因为查询结果是边读边发的，不会在服务端保存完整的结果集。
• InnoDB 的 Buffer Pool 通过改进的 LRU 算法，能够有效控制全表扫描对内存的影响，避免热数据被冷数据淘汰。
• 尽管全表扫描不会打爆内存，但仍会消耗大量 IO 资源，因此在业务高峰期应避免在线上主库执行全表扫描。

34 到底可不可以使用 join

JOIN语句的执行方式：

• MySQL 支持两种主要的JOIN算法：Index Nested-Loop Join (NLJ) 和 Block Nested-Loop Join (BNL)。
• 选择哪种算法取决于被驱动表是否有可用的索引。

Index Nested-Loop Join (NLJ)：

• 当被驱动表（t2）的JOIN字段上有索引时，MySQL 会使用 NLJ 算法。
• 执行流程：
  1. 从驱动表（t1）中读取一行数据。
  2. 使用该行数据的JOIN字段值去被驱动表（t2）中查找匹配的行。
  3. 将匹配的行与驱动表的行组合成结果集的一部分。
  4. 重复上述过程，直到驱动表的所有行都被处理。
• 优点：由于使用了索引，查询效率较高，扫描行数为N + N*2*log2M（N为驱动表的行数，M为被驱动表的行数）。
• 驱动表选择：应该让小表做驱动表，因为扫描行数主要由驱动表的行数决定。

Block Nested-Loop Join (BNL)：

• 当被驱动表（t2）的JOIN字段上没有索引时，MySQL 会使用 BNL 算法。
• 执行流程：
  1. 将驱动表（t1）的数据读入内存中的join_buffer。
  2. 扫描被驱动表（t2），将每一行与join_buffer中的数据进行匹配，满足条件的行作为结果集返回。
• 优点：虽然扫描行数较多（M + N），但由于是内存操作，性能比 Simple Nested-Loop Join（SNL）要好。
• 驱动表选择：在join_buffer足够大时，选择哪个表做驱动表对性能影响不大；但在join_buffer不够大时，应该让小表做驱动表，以减少扫描次数。

join_buffer的影响：

• join_buffer的大小由参数join_buffer_size控制，默认值为 256KB。
• 如果驱动表的数据量超过join_buffer的大小，MySQL 会分段处理，导致被驱动表被多次扫描。
• 增加join_buffer_size可以减少分段次数，提升JOIN性能。

JOIN的使用建议：

• 可以使用JOIN的情况：如果被驱动表有索引，使用JOIN是高效的。
• 尽量避免使用JOIN的情况：如果被驱动表没有索引，且表数据量较大，使用JOIN可能会导致大量的全表扫描，性能较差。
• 驱动表的选择：无论使用哪种算法，通常应该让小表做驱动表。在决定哪个表是“小表”时，应该考虑参与JOIN的字段的数据量，而不是单纯的行数。数据量较小的表应作为驱动表。

35 join 语句怎么优化？

Multi-Range Read (MRR) 优化：

• MRR 优化的目的是通过顺序读盘来提升查询性能。
• 当使用索引进行范围查询时，MRR 会将主键 id 放入read_rnd_buffer中排序，然后按顺序回表查询，减少随机磁盘访问。
• 启用 MRR 需要设置optimizer_switch="mrr_cost_based=off"。

Batched Key Access (BKA) 优化：

• BKA 是对 NLJ 算法的优化，通过将驱动表的多行数据一次性放入join_buffer，然后批量传递给被驱动表进行JOIN操作。
• BKA 依赖于 MRR 优化，启用 BKA 需要设置optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'。

BNL 算法的性能问题：

• BNL 算法在大表JOIN时会导致多次扫描被驱动表，占用大量 CPU 和 IO 资源。
• 多次扫描冷表可能会导致 Buffer Pool 中的热数据被淘汰，影响内存命中率。
• 优化 BNL 的方法包括增大join_buffer_size或给被驱动表的JOIN字段加索引，将 BNL 转为 BKA 算法。

BNL 转 BKA 的优化方法：

• 如果无法直接在被驱动表上建索引，可以使用临时表的方式：
  1. 将满足条件的被驱动表数据插入临时表。
  2. 在临时表的JOIN字段上创建索引。
  3. 使用临时表与驱动表进行JOIN操作。
• 这种方法可以显著减少条件判断的次数，提升查询性能。

Hash Join 的模拟：

• MySQL 目前不支持 Hash Join，但可以通过应用端模拟实现：
  1. 将驱动表的数据加载到内存中的哈希结构。
  2. 将被驱动表的数据逐行与哈希结构中的数据进行匹配。
• 这种方法理论上比临时表方案更快。

总结与建议：

• BKA 优化：建议默认启用 BKA 优化，提升JOIN性能。
• BNL 优化：尽量通过加索引将 BNL 转为 BKA 算法，避免使用 BNL。
• 临时表方案：对于能够提前过滤出小数据的JOIN语句，使用临时表方案效果显著。
• Hash Join：虽然 MySQL 不支持，但可以通过应用端模拟实现，效果优于临时表方案。

36 为什么临时表可以重名？

临时表与内存表的区别

• 内存表：使用 Memory 引擎，数据存储在内存中，重启后数据丢失，但表结构保留。
• 临时表：可以使用多种引擎（如 InnoDB、MyISAM 等），数据可以存储在磁盘上，也可以使用 Memory 引擎。

临时表的特性

• 创建语法为create temporary table …。
• 临时表只能被创建它的 session 访问，对其他线程不可见。
• 临时表可以与普通表同名，且在 session 内有同名临时表和普通表时，操作的是临时表。
• show tables命令不显示临时表。
• 临时表在 session 结束时自动删除。

临时表的应用

• 临时表常用于复杂查询的优化，特别是在分库分表系统中进行跨库查询时。
• 在分库分表系统中，临时表可以用于汇总多个分库的数据，然后在汇总表上进行排序、过滤等操作。

临时表的存储机制：

• 临时表的表结构定义存储在临时文件目录下的.frm文件中，文件名格式为#sql{进程 id}_{线程 id}_序列号。
• 在 MySQL 5.6 及之前版本，临时表的数据存储在.ibd文件中；从 5.7 版本开始，数据存储在临时表表空间中。
• 临时表的table_def_key由“库名 + 表名 + server_id + thread_id”组成，确保不同线程的同名临时表不会冲突。

临时表在主备复制中的行为：

• 在binlog_format=statement/mixed时，临时表的创建和删除操作会记录到 binlog 中，以便备库同步。
• 在binlog_format=row时，临时表的操作不会记录到 binlog 中。
• 备库通过主库的线程 id 来区分不同线程的同名临时表，确保不会冲突。

临时表的自动删除：临时表在 session 结束时自动删除，binlog 中会记录DROP TEMPORARY TABLE命令，以便备库同步删除临时表。

临时表的改名操作：临时表可以使用alter table语法修改表名，但不能使用rename语法。这是因为临时表的存储机制和table_def_key的特殊性。

37 什么时候会使用内部临时表？

内部临时表的作用：内部临时表用于存储 SQL 语句执行过程中的中间结果，辅助完成复杂的查询操作，如UNION和GROUP BY。

UNION操作的执行流程：

• UNION操作需要对两个子查询的结果进行去重合并，因此需要使用临时表来存储中间结果。
• 执行流程：
  1. 创建一个内存临时表，用于存储子查询的结果。
  2. 执行第一个子查询，将结果插入临时表。
  3. 执行第二个子查询，尝试将结果插入临时表，若遇到重复值则忽略。
  4. 从临时表中读取数据并返回结果，最后删除临时表。
• 如果使用UNION ALL，则不需要去重，因此不需要临时表。

GROUP BY操作的执行流程：

• GROUP BY操作用于对数据进行分组统计，通常需要使用临时表来存储分组结果。
• 执行流程：
  1. 创建一个内存临时表，包含分组字段和计数字段。
  2. 扫描表数据，计算分组字段的值，并更新临时表中的计数。
  3. 对临时表中的数据进行排序（如果需要），然后返回结果。
• 如果内存临时表的大小超过tmp_table_size的限制，MySQL 会将内存临时表转换为磁盘临时表。

GROUP BY的优化方法：

• 使用索引：如果分组字段上有索引，MySQL 可以利用索引的有序性来避免使用临时表和排序操作。可以通过generated column机制创建一个计算列，并在该列上创建索引，从而优化GROUP BY操作。
• 直接排序：对于数据量特别大的GROUP BY操作，可以使用SQL_BIG_RESULT提示，告诉优化器直接使用磁盘临时表和排序算法，避免内存临时表的开销。

内部临时表的使用场景：

• 当 SQL 语句的执行过程需要存储中间结果时，MySQL 会使用内部临时表。
• 内部临时表的使用场景包括：
  • UNION操作（去重合并）。
  • GROUP BY操作（分组统计）。
  • 其他需要二维表结构的操作。

优化建议：

• 对于GROUP BY操作，如果没有排序要求，可以在语句末尾加上ORDER BY NULL，避免不必要的排序。
• 尽量让GROUP BY操作使用索引，避免使用临时表和排序。
• 如果数据量较大，可以适当调大tmp_table_size参数，避免使用磁盘临时表。
• 对于非常大的数据集，可以使用SQL_BIG_RESULT提示，直接使用排序算法。

38 都说 InnoDB 好，那还要不要使用 Memory 引擎？

Memory 引擎与 InnoDB 引擎的数据组织方式：

• InnoDB：采用索引组织表（Index Organized Table），数据存储在 B+树的主键索引上，数据有序存储。
• Memory 引擎：采用堆组织表（Heap Organized Table），数据存储在数组中，主键索引是哈希索引，数据按写入顺序存储。

Memory 引擎的特性：

• 数据存储：数据存储在内存中，读写速度快，但数据库重启后数据会丢失。
• 索引支持：默认使用哈希索引，但也支持 B-Tree 索引。
• 锁机制：只支持表锁，不支持行锁，并发性能较差。
• 数据类型限制：不支持 Blob 和 Text 字段，varchar(N) 实际存储为固定长度字符串。

Memory 引擎的缺点：

• 锁粒度问题：由于只支持表锁，更新操作会阻塞其他读写操作，影响并发性能。
• 数据持久性问题：数据库重启后，内存表中的数据会丢失，可能导致主备同步问题。
• 主备同步问题：在 M-S 架构或双 M 架构下，备库重启会导致内存表数据丢失，进而导致主备同步停止。

Memory 引擎的适用场景：

• 内存临时表：在数据量可控、不需要持久化的场景下，内存临时表可以发挥其优势，尤其是在复杂查询中，哈希索引的查找速度较快。
• 临时表的使用：内存临时表适合用于临时存储中间结果，尤其是在需要快速查询的场景下。

InnoDB 引擎的优势：

• 数据持久化：数据存储在磁盘上，重启后数据不会丢失。
• 行锁支持：支持行锁，并发性能较好。
• 数据安全：支持事务和崩溃恢复，数据安全性更高。

生产环境中的建议：

• 不建议使用普通内存表：由于数据持久性和锁粒度问题，生产环境中不建议使用 Memory 引擎的普通表。
• 推荐使用 InnoDB 表：InnoDB 表在性能和安全性上更有保障，尤其是在高并发和需要持久化的场景下。
• 内存临时表的适用性：在数据量较小、不需要持久化的临时表场景下，可以使用内存临时表来加速查询。

39 自增主键为什么不是连续的？

自增主键的存储机制

• MyISAM 引擎：自增值保存在数据文件中。
• InnoDB 引擎：自增值保存在内存中，直到 MySQL 8.0 版本才实现了自增值的持久化，通过 redo log 记录自增值的变更，确保重启后自增值不变。

自增主键的修改机制

• 当插入数据时，如果未指定自增字段的值，MySQL 会使用当前的自增值。
• 如果插入的值大于当前自增值，MySQL 会更新自增值为新的值（插入值 + 1）。
• 自增值的生成算法基于auto_increment_offset和auto_increment_increment两个系统参数，默认值均为 1。

自增主键不连续的原因

• 唯一键冲突：当插入数据时发生唯一键冲突，自增值已经增加，但插入失败，导致自增值不连续。
• 事务回滚：事务回滚时，自增值不会回退，导致自增值不连续。
• 批量插入：在批量插入数据时，MySQL 会预先分配多个自增值，但实际使用的自增值可能少于分配的数量，导致自增值不连续。

自增锁的优化

MySQL 5.1.22 版本引入了innodb_autoinc_lock_mode参数，控制自增锁的范围：

• 0：语句级别锁，语句执行结束后释放锁。
• 1（默认）：普通插入语句申请后立即释放锁，批量插入语句（如insert … select）在语句结束后释放锁。
• 2：所有插入语句申请后立即释放锁。

为了提升并发性能，建议将innodb_autoinc_lock_mode设置为 2，并将binlog_format设置为row，以避免数据不一致问题。

批量插入的自增 ID 分配策略

• 批量插入时，MySQL 采用指数级分配策略，第一次分配 1 个 ID，第二次分配 2 个，第三次分配 4 个，依此类推。
• 这种策略可能导致自增 ID 的浪费，进一步导致自增 ID 不连续。

40 insert 语句的锁为什么这么多？

INSERT … SELECT语句的加锁行为：

• 在可重复读隔离级别下，INSERT … SELECT语句会对源表（SELECT的表）的所有记录和间隙加锁（next-key lock），以防止并发操作导致的数据不一致。
• 这种加锁行为是为了保证主备数据一致性，尤其是在binlog_format=statement的情况下，避免主备数据不一致的问题。

INSERT循环写入的问题：

• 当INSERT和SELECT操作的对象是同一个表时，可能会导致循环写入问题。为了避免这种情况，MySQL 会使用临时表来存储中间结果。
• 通过使用临时表，可以避免在遍历数据的同时更新数据，从而防止逻辑错误。

INSERT语句中的唯一键冲突：

• 当INSERT语句发生唯一键冲突时，MySQL 会在冲突的唯一键上加共享的next-key lock（读锁），以防止其他事务删除该行数据。
• 这种加锁行为可能会导致死锁，尤其是在多个事务同时插入相同唯一键的情况下。

INSERT INTO … ON DUPLICATE KEY UPDATE语句：

• 当INSERT语句发生唯一键冲突时，可以使用ON DUPLICATE KEY UPDATE来更新冲突的行。
• 这种情况下，MySQL 会在冲突的唯一键上加排他的next-key lock（写锁），并执行更新操作。
• 如果有多个唯一键冲突，MySQL 会按照索引顺序处理第一个冲突的行。

死锁场景：

• 在唯一键冲突的情况下，多个事务同时插入相同唯一键时，可能会导致死锁。例如，事务 A 插入冲突行并加锁，事务 B 和事务 C 也尝试插入相同行并加锁，当事务 A 回滚时，事务 B 和事务 C 会互相等待对方的锁，导致死锁。

优化建议：

• 对于INSERT … SELECT操作，可以使用临时表来避免循环写入问题。
• 在唯一键冲突的情况下，应尽快提交或回滚事务，避免长时间持有锁，减少死锁的风险。

总结：

• INSERT语句在特殊情况下（如INSERT … SELECT、唯一键冲突等）会有复杂的加锁行为，理解这些行为有助于避免数据不一致和死锁问题。
• 使用临时表、合理设计事务提交策略等方法可以优化INSERT操作的性能和数据一致性。

41 怎么最快地复制一张表？

mysqldump 方法：

• 使用mysqldump命令将数据导出为一组INSERT语句，然后将这些语句导入到目标表中。
• 优点：可以控制导出的数据范围（如WHERE条件），适合导出部分数据。
• 缺点：不能处理复杂的WHERE条件（如JOIN），且导出的是逻辑数据，速度较慢。

导出 CSV 文件方法：

• 使用SELECT … INTO OUTFILE将查询结果导出为 CSV 文件，然后使用LOAD DATA INFILE将数据导入到目标表中。
• 优点：灵活，支持所有 SQL 写法，适合导出单表数据。
• 缺点：每次只能导出一张表的数据，表结构需要单独备份，且需要处理文件路径和权限问题。

物理拷贝方法：

• 使用 MySQL 的可传输表空间功能，直接拷贝表的.ibd和.cfg文件，实现物理拷贝。
• 优点：速度最快，尤其适合大表拷贝，恢复数据时非常高效。
• 缺点：只能全表拷贝，不能拷贝部分数据，且源表和目标表必须使用 InnoDB 引擎。

三种方法的对比：

• 物理拷贝：速度最快，适合大表全表拷贝，但限制较多。
• mysqldump：适合导出部分数据，但不能处理复杂查询。
• 导出 CSV 文件：最灵活，支持所有 SQL 写法，但每次只能导出一张表的数据。

binlog_format=statement 时的 load data 问题：

• 当binlog_format=statement时，LOAD DATA命令会记录到 binlog 中，并在备库执行。为了确保备库能够正确执行，binlog 中的LOAD DATA命令会带有local关键字，指示备库从本地读取文件。
• 如果不带local，备库将无法找到文件，导致主备同步停止。

42 grant 之后要跟着 flush privileges 吗？

grant 语句会同时修改数据表和内存，判断权限的时候使用的是内存数据。因此，规范地使用 grant 和 revoke 语句，是不需要随后加上 flush privileges 语句的。

flush privileges 语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。而这种不一致往往是由于直接用 DML 语句操作系统权限表导致的，所以我们尽量不要使用这类语句。

GRANT命令的作用：

• GRANT命令用于给用户赋权，它会同时更新磁盘上的权限表（如mysql.user、mysql.db等）和内存中的权限数组（如acl_users、acl_dbs等）。
• 赋权操作是即时生效的，新的连接会使用更新后的权限，但已经存在的连接不会受到影响。

权限的存储与生效：

• 全局权限：存储在mysql.user表中，内存中保存在acl_users数组中。GRANT和REVOKE操作会同时更新磁盘和内存。
• 库级权限：存储在mysql.db表中，内存中保存在acl_dbs数组中。GRANT和REVOKE操作也会同时更新磁盘和内存。
• 表级和列级权限：存储在mysql.tables_priv和mysql.columns_priv表中，内存中保存在column_priv_hash结构中。GRANT和REVOKE操作同样会同时更新磁盘和内存。

FLUSH PRIVILEGES的作用：

• FLUSH PRIVILEGES命令会清空内存中的权限数组（如acl_users、acl_dbs等），然后从磁盘的权限表中重新加载数据，重建内存中的权限数组。
• 通常情况下，如果使用GRANT和REVOKE命令来管理权限，内存和磁盘的权限数据是同步的，因此不需要执行FLUSH PRIVILEGES。

FLUSH PRIVILEGES的使用场景：

• 当直接使用 DML 语句（如INSERT、UPDATE、DELETE）修改权限表时，可能会导致内存和磁盘的权限数据不一致。此时需要执行FLUSH PRIVILEGES来重建内存中的权限数据。
• 例如，直接删除mysql.user表中的用户记录后，内存中的acl_users数组仍然保留该用户，此时需要FLUSH PRIVILEGES来更新内存数据。

总结与建议：

• 正常情况下，使用GRANT和REVOKE命令管理权限时，不需要执行FLUSH PRIVILEGES。
• 只有在直接操作权限表（如使用 DML 语句）导致内存和磁盘权限数据不一致时，才需要执行FLUSH PRIVILEGES。
• 建议避免直接操作权限表，而是使用GRANT和REVOKE命令来管理权限。

43 要不要使用分区表？

分区表的概念：

• 分区表是将一个大表按某种规则（如时间、范围等）分成多个小表，每个分区对应一个独立的物理文件（如.ibd文件）。
• 对于引擎层来说，每个分区是一个独立的表；对于 Server 层来说，分区表是一个逻辑上的表。

分区表的引擎层行为：

• InnoDB 引擎：每个分区在引擎层被视为独立的表，加锁范围仅限于当前分区。例如，间隙锁只会锁定当前分区的间隙，不会影响其他分区。
• MyISAM 引擎：MyISAM 的分区表在引擎层也是独立的表，但由于 MyISAM 只支持表锁，锁的范围是整个分区。

分区表的 Server 层行为：

• 在 Server 层，分区表被视为一个逻辑表，所有分区共享同一个 MDL 锁（Metadata Lock）。这意味着在对分区表执行 DDL 操作时，可能会阻塞其他分区的查询操作。
• 第一次访问分区表时，MySQL 需要打开所有分区的文件，如果分区过多，可能会导致打开文件数超过系统限制（open_files_limit），从而报错。

分区策略：

• 通用分区策略（Generic Partitioning）：MySQL 早期支持的分区策略，性能较差，尤其是在 MyISAM 引擎下，访问分区时需要打开所有分区的文件。
• 本地分区策略（Native Partitioning）：从 MySQL 5.7.9 开始，InnoDB 引擎引入了本地分区策略，优化了分区表的文件管理和性能。从 MySQL 8.0 开始，MyISAM 分区表被弃用，仅支持 InnoDB 和 NDB 引擎的分区表。

分区表的优缺点：

• 优点：
  • 对业务透明，业务代码无需修改即可使用分区表。
  • 方便清理历史数据，可以通过ALTER TABLE ... DROP PARTITION快速删除过期数据，性能优于DELETE操作。
• 缺点：
  • 第一次访问分区表时需要打开所有分区的文件，可能导致性能问题。
  • 所有分区共享同一个 MDL 锁，DDL 操作可能会影响其他分区的查询。
  • 分区过多会导致文件管理复杂，影响性能。

分区表的适用场景：

• 适合按时间分区的场景，尤其是需要定期清理历史数据的业务。
• 分区表的分区数量不宜过多，建议按需创建分区，避免提前创建过多分区。

分区表的注意事项：

• 分区并不是越细越好，单表或单分区的数据量在 1000 万行以内时，性能通常是可以接受的。
• 分区表的主键必须包含分区字段，否则无法创建自增主键。

44 答疑文章（三）：说一说这些好问题

join 的写法：

• left join 的驱动表：使用 left join 时，左边的表不一定是驱动表，优化器可能会根据语义进行优化。
• on 和 where 的区别：在 left join 中，如果将条件放在 where 子句中，可能会导致语义变化，优化器可能会将 left join 改写为 join。
• join 条件的写法：对于 join 语句，将条件放在 on 或 where 子句中没有区别。

Simple Nested Loop Join 的性能问题：

• BNL 算法：将驱动表的数据读入内存，顺序扫描被驱动表进行匹配，性能较好。
• Simple Nested Loop Join 算法：每次匹配都需要全表扫描，性能较差，主要因为磁盘 I/O 和 Buffer Pool 的影响。

distinct 和 group by 的性能：

• 当不需要聚合函数时，distinct 和 group by 的语义和执行流程相同，性能也相同。
• 两者的执行流程都是通过创建临时表并利用唯一索引去重。

备库自增主键问题：在 binlog_format=statement 时，MySQL 通过 SET INSERT_ID 语句确保主备库的自增主键一致，即使语句执行顺序不同。

45 自增 id 用完怎么办？

表定义自增 ID：

• 表定义的自增 ID 达到上限后，再申请下一个 ID 时，值保持不变，导致插入数据时报主键冲突错误。
• 建议在建表时，如果预计数据量较大，应使用 8 字节的 bigint unsigned 类型。

InnoDB 系统自增 row_id：

• 如果表没有指定主键，InnoDB 会创建一个 6 字节的 row_id。
• row_id 达到上限后会从 0 开始循环，可能导致数据覆盖。
• 建议主动创建自增主键，以避免数据覆盖问题。

Xid：

• Xid 是事务的唯一标识，由 global_query_id 生成。
• global_query_id 是 8 字节的变量，达到上限后会从 0 开始循环，但理论上重复的概率极低。
• Xid 主要用于 redo log 和 binlog 的关联。

InnoDB trx_id：

• trx_id 是事务 ID，用于数据可见性判断。
• 只读事务不会分配 trx_id，以减少活跃事务数组的大小和锁冲突。
• max_trx_id 达到上限后会从 0 开始循环，可能导致脏读问题，但这种情况在现实中极少发生。

thread_id：

• 线程 ID 是 MySQL 中最常见的自增 ID，达到上限后会从 0 开始循环。
• MySQL 通过唯一数组逻辑确保不会分配重复的 thread_id。

总结：

• 不同的自增 ID 有不同的上限和达到上限后的行为。
• 数据库系统需要处理这些边界情况，以确保长期运行的稳定性。
• 文章还提到了一些其他自增 ID（如 table_id、binlog 文件序号等），鼓励读者自行探索。

参考资料

• 极客时间教程 - MySQL 实战 45 讲