MySQL 模型约束、连接聚合、统计与慢 SQL：独有实例上的真实观察
本页固定 MySQL Community 8.4.7，在独有 mysqld 实例（临时 datadir、随机端口、binlog 与 slow log 开启，结束即删）上运行九组实验，回答既有四篇没有用真实输入覆盖的问题：约束失败的错误码矩阵、排序规则与隐式类型转换、hash join 的实际选择、聚合的 ONLY_FULL_GROUP_BY、分页的实际扫描量、直方图对选择性的影响、skip scan 与 invisible index，以及真实 slow log 的字段口径。
机制讲解仍在既有四篇：索引与 EXPLAIN、MVCC 与锁、慢 SQL 与治理、SQL 手写。本页是它们之上的真实实验页，不重写结构原理；事务/日志/崩溃恢复/复制/迁移归 U52。
入口：python3 labs/mysql-engine/run.py u51，9 组检查、10 条观察行、exit 0，证据在 evidence/u51-2026-10-28.json。
一、会话状态先于任何 SQL 结论
任何“这条 SQL 在 RR 下如何”的结论都依赖会话状态。独有实例的默认值是：
U51 sessionModel=PASS autocommit=1 isolation=REPEATABLE-READ strict=ON onlyFullGroupBy=ONautocommit=1 意味着单条 DML 自动提交——讨论锁与回滚时必须显式 BEGIN（见 U52）；sql_mode 同时含 STRICT_TRANS_TABLES 与 ONLY_FULL_GROUP_BY，这两条决定了本页第二节与第五节的错误形态。驱动默认值不等于服务器默认值：Connector/J 的 autocommit 与这里的 @@autocommit 是两回事，T16 审计已记录过一次混淆。
二、约束失败矩阵：错误码与 SQLSTATE 分开读
同一张表上的七种约束失败，真实返回：
| 约束 | 错误码 | SQLSTATE | 行为 |
|---|---|---|---|
| 主键重复 | 1062 | 23000 | 拒绝插入 |
| 唯一键重复 | 1062 | 23000 | 同上（错误消息里带约束名） |
| CHECK 违反 | 3819 | HY000 | 拒绝（8.0.16+ 才真正执行） |
| NOT NULL 违反 | 1048 | 23000 | 拒绝 |
| 外键引用不存在 | 1452 | 23000 | 拒绝 |
| 删除被引用行（RESTRICT） | 1451 | 23000 | 拒绝 |
| 超长字符串（严格模式） | 1406 | — | 报错而非截断 |
三个实践要点：
- 1062 同时服务主键与唯一键——应用无法只从错误码区分是哪条约束，要看错误消息里的键名或提前查询。
- CHECK 的 SQLSTATE 是 HY000 而不是 23000：按 SQLSTATE 23000 识别完整性约束的代码会漏掉 CHECK 违反。这是本机实测，不是文档推导。
- 严格模式决定超长是报错还是截断：同样的
REPEAT('x',100)插入VARCHAR(60)，严格模式给 1406；非严格模式会截断并只给 warning。STRICT_TRANS_TABLES是 8.x 默认，但会话/全局都可改，迁移旧库时要显式核对。
“约束失败”与“事务回滚”是两个问题：错误码告诉你哪条约束拒绝，是否回滚取决于语句级/事务级的错误处理策略（U52 展开）。
三、排序规则与隐式转换：两个真实的索引陷阱
排序规则的差别只有落到比较上才可见：
SELECT 'Alice'='alice' COLLATE utf8mb4_0900_ai_ci, -- 1
'Alice'='alice' COLLATE utf8mb4_0900_as_cs, -- 0
'résumé'='resume' COLLATE utf8mb4_0900_ai_ci; -- 1ai_ci（accent-insensitive, case-insensitive）把大小写与重音都视为等价。表默认排序规则决定唯一键的语义：email 在 ai_ci 下 A@x.com 与 a@x.com 视为重复，在 as_cs 下不重复。登录系统用邮箱做唯一键时，这个默认值就是业务语义的一部分，不能等到线上发现大小写重复才补索引。
更隐蔽的是隐式类型转换。codes.code 是 10000 行、带唯一索引的 VARCHAR：
U51 collationConversion=PASS ciEqual=1 csEqual=0 accentInsensitive=1 varcharEqStringCount=1 varcharEqNumberCount=10000 varcharEqNumberFullScan=truecode = 'C000100' 精确命中 1 行；code = 0 却返回 10000 行且执行计划是 Table scan——MySQL 把字符串列转成数字比较，'C000100' 前缀非数字，转换结果是 0，于是全部行都“等于 0”。两条后果：结果错误（用户以为只查一行）与索引失效（无法用唯一索引定位）。Java 侧常见来源是 WHERE varchar_col = ? 绑定了 Integer/Long 参数，或 setInt 而不是 setString。这比“函数包裹列”更隐蔽，因为 SQL 文本里看不到任何函数。
四、连接算法：hash join 是默认，不是特例
两个都没有索引的等值连接：
U51 joinAlgorithm=PASS defaultHashJoin=true hashJoinOffStillHashJoin=true indexedJoinUsesNestedLoop=truenotes.order_note = events.code_ref（两列均无索引）默认选 Inner hash join：两边全表扫描，构建哈希表后探测。- 关闭
optimizer_switch='hash_join=off'后仍然是 hash join——MySQL 8.4 对无索引等值连接即使显式关闭也可能保留 hash join（内部实现细节），所以“关掉开关”不是可靠对照。如实记录：本实验观察到 off 后计划未变，不据此宣称开关无效，只说明该输入下开关不改变选择。 - 主键等值连接（
orders.customer_id = customers.id）走 nested loop + index lookup：被驱动表有唯一索引，每次探测一次 B+ 树查找。
读 EXPLAIN FORMAT=TREE 时先找迭代器类型：Inner hash join 与 Nested loop inner join 是两种完全不同的资源画像——前者吃内存（哈希表大小由 join_buffer_size 约束，超出会落盘）、后者吃索引查找次数。rows_examined_per_scan 与 actual time 要给到对应迭代器，不能只看顶层。
五、聚合：ONLY_FULL_GROUP_BY 与等价写法
SELECT customer_id, status, amount FROM orders GROUP BY customer_id 在默认 sql_mode 下直接失败：
U51 aggregation=PASS onlyFullGroupBy=1055 groups=4 windowEqualsGroupBy=true1055 = Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column。这是 8.x 的默认行为，很多从 5.7 迁移的应用会在这里第一次遇到；绕过方式是 ANY_VALUE() 或改写查询，不要关掉 ONLY_FULL_GROUP_BY——它保护的是“返回的 status 到底属于哪一行”这个语义问题。
GROUP BY status 与窗口函数写法在本夹具上结果一致（4 个分组），但两者执行路径不同：前者可能用临时表/排序，后者按窗口分区。执行计划顶层出现 Sort 说明有额外排序；Using temporary 说明有临时表。是否落盘取决于结果集大小与 tmp_table_size，EXPLAIN 本身不告诉你。
六、分页：同一页，扫描量差 451 倍
同一个“第 9000 行起的 20 条”，两种写法返回完全相同的 20 个 id（测试断言相等），但扫描量：
U51 pagination=PASS samePage=true offsetScanRows=9020 keysetScanRows=20LIMIT 9000, 20 的 TREE 计划里，Index scan on orders using PRIMARY 的 actual rows=9020——先顺着主键读出 9020 行，再丢掉前 9000。WHERE id > 9000 ORDER BY id LIMIT 20 是 Index range scan ... over (9000 < id)，actual rows=20。原理在诊断页用 LimitOffsetIterator 原码讲过；这里补的是真实迭代器计数：9020 与 20，不是估算。
注意页面相同不等于语义相同：LIMIT/OFFSET 在翻页期间有并发插入时会重复或漏行；keyset 以严格边界续页，但要求排序列唯一且不被并发修改。审计导出需要截止水位或一致性快照。
七、统计信息与直方图：只有无索引列才看得出差别
在均匀数据上，直方图建与不建，filtered 都是 10%——看不出任何差别。倾斜数据才有对照价值。skewed.tag 是无索引列，90% 是 COMMON、10% 散在 RARE*：
U51 statistics=PASS actualCommon=9000 actualRare=11 commonFiltered=10.0->90.0 rareFiltered=10.0->0.11 histograms=1建 32 桶直方图前，优化器对两个等值条件都按启发式给 10%；建完后 COMMON 变成 90%、RARE1 变成 0.11%，与实际（9000 / 11）吻合。直方图修正的是选择性估算，不是访问路径本身；它影响的是“下一步用什么 join、是否物化、临时表多大”。
对有索引的列，等值估算来自 index dives（对每个值精确统计），直方图通常不改变 rows_examined_per_scan——本实验在 skewed.category（有索引）上验证了这一点。这解释了一个常见困惑：“我建了直方图，计划没变”——先看那列有没有索引，有索引时统计已经足够精确。
information_schema.statistics.cardinality 在 ANALYZE TABLE 后是本实例的估算值；它是估算，不随每次写入实时更新，innodb_stats_persistent 把统计存盘以减少重启后的波动。
八、优化器边界：skip scan 未被选中与 invisible index
schema 有 idx_customer_status(customer_id, status)，查询只带 status（非最左前缀）。关闭其他干扰索引后：
U51 optimizerBoundaries=PASS skipScanUsed=false skipScanChosenPlan=coveringIndexScan invisibleIgnored=true visibleUsed=true优化器没有选 skip scan，而是选了 Covering index scan——全索引扫描比枚举前缀更便宜（3 个 customer_id 值 × 每次查找的成本 vs 一次性顺序扫完 9668 行的索引）。这说明：skip_scan=on 是能力开关，不是强制。优化器只在成本合适时启用；把“有 skip scan 优化”当成“一定会用”是常见误解。测试如实记录未选中，不硬编码“必须出现 skip scan”。
invisible index 的对照是确定性的：把 idx_status_created 设为 INVISIBLE 后，EXPLAIN 不再引用它；恢复 VISIBLE 后重新引用。这提供了一种安全的“删索引演练”：先不可见观察一段时间，确认无影响再删。
九、慢日志：字段口径与 mysqldumpslow
long_query_time=0 让所有语句入日志（只为观察字段），执行一条扫描 2000 行、返回 2000 行的查询后：
U51 slowLog=PASS queryTime=0.000597 rowsExamined=4651 rowsSent=2000 entries=131Rows_examined不等于Rows_sent：扫描 4651 行、返回 2000 行——差额来自amount > 60条件在索引扫描后的过滤。这两个字段是判断“索引是否有效”的第一手证据。Query_time只覆盖服务器执行，不含客户端网络与连接池等待；slow log 不解释应用侧的全部延迟（诊断页的“同窗对齐”正是这个意思）。mysqldumpslow -s t <logfile>按总耗时排序汇总；输出Count / Time / Lock / Rows是聚合值，正常使用时不会出现本实验的 131 条噪声（long_query_time=0导致）。
生产实践：slow_query_log=ON、long_query_time=1（或按 SLO 调低）、log_queries_not_using_indexes=ON 需谨慎（会放大日志量）；定期 mysqldumpslow 或 pt-query-digest 汇总，按 Rows_examined/Rows_sent 比值与 Query_time 分布定位。slow log 是服务端证据，与 performance_schema 的锁视图（既有双会话实验）是互补的两类观察。
十、证明边界：独有实例能证明什么
| 类别 | 本页有什么 | 不能推出什么 |
|---|---|---|
| 真实组件 | 独有 mysqld 8.4.7（真实 server、真实优化器、真实 slow log） | 生产配置、复制拓扑、崩溃恢复 |
| 合成数据 | 约 5 万行、确定性分布、一份倾斜表 | 生产数据分布与热点 |
| 观察 | EXPLAIN TREE 的 actual rows、JSON 的 filtered、slow log 字段、mysqldumpslow | 压测吞吐、P99、并发 |
| 未运行 | 崩溃恢复/redo/binlog 恢复/复制/备份迁移（U52）、8.0.39 镜像、DBngin 3306 实例 | 不能标为已通过 |
单次计时与 actual time 只作该输入下的观察，不是基准；long_query_time=0 是观察手段，不是生产建议。
教材完成不等于学习者掌握：下面的题目检查改变输入后的裁决。
闭卷验收
本页 12 道闭卷题收进了下方自测面板：先回忆，再展开参考答案与评分点，最后给三档自评。