MySQL

钱的字段到底该用 decimal 还是 bigint 某开发者最近在设计一套统一支付服务，走到金额字段这一步，跟数据库里的老订单表吵了一架：新表想用 bigint 存"分"，老表是 decimal(10,2)。写代码前先把这个历史遗留问题捋清楚，发现这背后是一整段软件史。 数据库里的金额字段，可能是除了主键之外被争论最多的一种类型。打开任何一本数据库教材，都会看到一句名言——“钱的字段千万别用 float”。但这句话的下半句往往没人讲：不用 float，那到底用 decimal 还是 bigint？ 教科书里写的是 decimal。现代支付 API 的契约里写的是"整数最小单位"——也就是 bigint 存分。两边都合理，为什么结论会分叉？ 从一次选型冲突说起 设计支付服务时，金额字段出现了两个候选人： ** decimal(10,2) ** —— 存的就是 100.00 ，肉眼可读 ** bigint ** —— 存 10000 ，单位是分，代码里到处都是 ÷100 老 ERP 系统的订单表选了前者，支付服务想选后者。这不是口味问题，是两个时代的设计碰撞。要理解它，得先从 float 为什么被禁说起——因为 float 才是那个真正不配碰钱的类型。 📌 前置知识：浮点数、定点数、IEEE 754 这三个概念是本文的地基，建议先有个印象再往下看。 float 的罪与罚：二进制算不清十进制 先复现那个经典翻车现场： SELECT 0.1 + 0.2; 结果是 0.30000000000000004 。 float / double 用二进制科学计数法存储： M × 2^E ，M 是尾数，E 是指数。但十进制小数 0.1 转成二进制是无限循环小数： ...

Flyway 数据库迁移 第1步：目标说明 — 从 38 个手工 SQL 脚本说起 Mall 商城项目的 README 里有一句坦诚的自我检讨： SQL 脚本丢在 sql/ 目录手工执行，没有 Flyway / Liquibase。无法追踪某台机器跑过哪些 DDL，回滚靠猜。 打开 sql/feature_1.0.1/ 目录一看——38 个 SQL 文件，命名靠日期： create_table_2024_01_05.sql create_table_2024_01_29.sql alter_table_2024_02_27.sql alter_table_2024_05_12.sql alter_table_2024_09_26.sql ... 每次上线，开发人员手动连上数据库，挑出"这次要跑的"脚本，逐个执行。脚本里还夹杂了手工更新历史数据的 DML： use mall_db; alter table mall_product add column `cover_url` varchar(200) DEFAULT NULL COMMENT '封面图片url'; -- 更新历史数据 update mall_product p inner join mall_product_photo m on p.id = m.product_id set p.cover_url = m.url where m.type=1 and m.is_del=0; -- 别忘了还有分库 use mall_db_order_0; alter table order_trade_item_0 add column `cover_url` varchar(200) ... alter table order_trade_item_1 add column `cover_url` varchar(200) ... use mall_db_order_1; alter table order_trade_item_0 add column `cover_url` varchar(200) ... alter table order_trade_item_1 add column `cover_url` varchar(200) ... 这种模式下会发生什么，写过的人都懂： ...

从 EXPLAIN 到 NULL 陷阱——优化其实有章可循 📌 前置知识：这篇是系列最后一篇，面向日常开发的实战视角。前四篇的理论基础——B+树、索引结构、MVCC、锁机制——这篇会直接引用而不重复展开。建议至少读过第一篇 B+树索引体系再看这篇。 1. EXPLAIN：优化器的自白 EXPLAIN 是 SQL 优化的第一工具。它不会替你优化 SQL，但它告诉你 MySQL 打算怎么优化你的 SQL——用了哪个索引、扫描多少行、做了什么额外操作。理解了它的输出，慢查询的根因通常一目了然。 EXPLAIN SELECT * FROM users WHERE name = 'Zhang' AND age > 20 ORDER BY id; 输出如下（省略部分列）： +----+------+---------------+------+---------+-------+------+-------------------+ | id | type | possible_keys | key | key_len | ref | rows | Extra | +----+------+---------------+------+---------+-------+------+-------------------+ | 1 | ref | idx_name | idx | 102 | const | 120 | Using index cond | +----+------+---------------+------+---------+-------+------+-------------------+ 逐字段解读： ...

锁与日志：并发控制如何实现崩溃恢复 📌 前置知识：前三篇分别讲了 B+树索引、Join 原理、MVCC。这篇讲两个主题——锁（LBCC，基于锁的并发控制）和日志（Redo Log + Binlog）——它们分别在"正确性"和"持久性"上补足了 MVCC 的短板。MVCC 解决读-写冲突，锁解决写-写冲突；日志保证写入的数据断电不丢。 1. 锁的类型：InnoDB 到底有哪些锁 MVCC 让读者不需要锁就能看到一致的数据版本。但当两个事务同时修改同一行时，多版本帮不上忙——因为最终只能有一个版本成为"当前版本"。这就需要锁来协调写-写冲突。 InnoDB 的锁按粒度分为两级：表级锁和行级锁。 表级锁 锁类型 SQL 关键字 行为 表共享锁（S） LOCK TABLE t READ 自己可读不可写，其他人可读不可写 表排他锁（X） LOCK TABLE t WRITE 自己可读写，其他人连读都不行 意向共享锁（IS） 自动加 “我打算对其中某行加 S 锁”——在行上加 S 锁前必须先在表上加 IS 意向排他锁（IX） 自动加 “我打算对其中某行加 X 锁”——在行上加 X 锁前必须先在表上加 IX AUTO-INC 锁 自增列插入 插入自增主键时确保值连续递增 意向锁是 InnoDB 实现多粒度锁的关键。加行锁之前先加表级意向锁，这样其他事务要加表锁时只需检查表的意向锁就能知道该表是否有行锁，不需要逐行检查。比如事务 A 对某行加了 X 锁（先在表级加 IX 锁），事务 B 想 LOCK TABLE t WRITE（加表级 X 锁），B 一检查发现表上有 IX 锁，直接等待，不需要扫描所有的行。 ...

事务与 MVCC：多版本并发控制原理拆解 📌 前置知识：这篇需要理解前两篇的 B+树结构和聚簇索引。核心概念——隐藏列、Undo Log、ReadView——都是在 B+树的聚簇索引叶子页上工作的。建议读到这里时回想前文 InnoDB 页结构中 User Records 的记录头信息。 0. 60 秒速览：用一句话记住 MVCC 先别管术语，用一个生活场景建立直觉。 想象你正在写一份共享文档（Google Docs / 腾讯文档）。你打开它时，看到的是当时那个版本。别人在你之后改了几版，你不会突然看到"文档变了"——除非你刷新。你写的部分，别人在你保存前也看不到。 MySQL 的 MVCC 就是这个机制： 每次修改不覆盖原数据，而是生成一个新版本。读的人看到的是"自己开始读那一刻"的版本快照，写的人不影响正在读的人。 flowchart LR subgraph "同一行数据 (id=1, age=25)" V3["版本3 age=30DB_TRX_ID=300(当前行)"] V2["版本2 age=28DB_TRX_ID=200"] V1["版本1 age=25DB_TRX_ID=100(INSERT 原始版)"] end T1["事务A开始读"] -->|"ReadView 快照看到版本1"| V1 T2["事务B修改两次"] --> V2 T2 --> V3 V3 -.->|"DB_ROLL_PTR回滚指针"| V2 V2 -.->|"DB_ROLL_PTR"| V1 classDef data fill:#052e16,stroke:#16a34a,stroke-width:2px,color:#bbf7d0,font-weight:bold; classDef process fill:#1e1e24,stroke:#6b7280,stroke-width:2px,color:#e5e7eb; classDef root fill:#0f172a,stroke:#3b82f6,stroke-width:2.5px,color:#bfdbfe,font-weight:bold; class V1,V2,V3 data; class T1,T2 root; 这张图里有 MVCC 的全部核心零件，读完这篇你会逐个认识它们： ...

B+树上的表连接——彻底搞懂 Join 📌 前置知识：这篇基于前一篇 B+树索引体系的内容。默认读者已经理解聚簇索引、二级索引、回表、B+树叶子链表这几个概念。这不会是一篇"查字典"式的 SQL 语法说明，而是从 InnoDB 引擎视角解释 Join 到底在干什么。 1. Join 的本质：笛卡尔积的引擎视角 从数学上讲，Join 是两张表的 笛卡尔积 + 过滤条件： SELECT * FROM A JOIN B ON A.id = B.a_id WHERE A.age > 20; 逻辑上等价于：先穷举 A × B 的所有组合（笛卡尔积），再保留满足 A.id = B.a_id AND A.age > 20 的行。但现实中没有引擎会真去算笛卡尔积——100 万 × 100 万 = 1 万亿行，物理世界做不到。 MySQL 实际的做法是：选一张表做驱动（外层循环），另一张做被驱动（内层查找），逐行匹配。算法的核心差异在于"如何查找被驱动表中匹配的行"——这才有了 SNLJ、BNLJ、INLJ、Hash Join 四种策略。 flowchart TD DRIVER["🔁 驱动表（外层）逐行读取"] --> CHECK{"被驱动表\n有可用索引?"} CHECK -->|"有"| INLJ["Index Nested-Loop\n每行走 B+树查找"] CHECK -->|"无"| BNLJ["Block Nested-Loop\nJoin Buffer 批量匹配"] BNLJ --> HASHCHECK{"MySQL 8.0+\n且等值连接?"} HASHCHECK -->|"是"| HJ["Hash Join\n构建哈希表替代 B+树"] HASHCHECK -->|"否"| BNLJ2["仍用 BNLJ 或 SNLJ"] classDef startEnd fill:#701a4c,stroke:#e11d48,stroke-width:2px,color:#fce7f3,font-weight:bold; classDef condition fill:#2a1147,stroke:#a855f7,stroke-width:1.5px,color:#ede9fe,font-weight:bold; classDef process fill:#1e1e24,stroke:#6b7280,stroke-width:1.5px,color:#e5e7eb; classDef highlight fill:#450a0a,stroke:#dc2626,stroke-width:1.5px,color:#fecaca,font-weight:bold; class DRIVER startEnd class CHECK,HASHCHECK condition class INLJ,BNLJ,HJ highlight class BNLJ2 process 这四种算法，接下来逐个拆解。 ...

MySQL B+树索引体系：从数据结构到查询执行 📌 前置知识：读者需了解磁盘与内存的速度差异（磁盘寻道 ~ 10ms，内存访问 ~ 100ns），以及基本的数据结构概念（链表、树、二分查找）。本文所有讨论基于 InnoDB 存储引擎。 1. 为什么是 B+树 MySQL 的数据是存在磁盘上的。磁盘 IO 的速度比内存慢约 10 万倍，所以数据库设计的第一原则是：尽量减少磁盘 IO 次数。 要理解为什么用 B+树，先看二叉搜索树（BST，Binary Search Tree）。 在 BST 中，每个节点只存一个键，每层只有两个子节点。如果数据量是 100 万行，树高就是 log₂(1000000) ≈ 20 层。执行一次查找最多需要 20 次磁盘 IO——因为每一层的节点都可能分散在不同的磁盘页上，每次读一个节点就是一次磁盘 IO。 这个代价太高了。解决的思路是：让每个节点存更多的键，增加每层的分叉数，降低树的高度。 flowchart LR root1["🌳 二叉树 ⚡20层 IO 100万数据"] --> root2["🌲 多路查找树 ⚡3 ~ 4层 IO 100万数据"] root2 --> leaf["叶子链表 范围扫描"] classDef startEnd fill:#701a4c,stroke:#e11d48,stroke-width:2px,color:#fce7f3,font-weight:bold; classDef process fill:#1e1e24,stroke:#6b7280,stroke-width:1.5px,color:#e5e7eb; classDef leaf fill:#052e16,stroke:#16a34a,stroke-width:1.5px,color:#bbf7d0,font-weight:bold; class root1,root2 startEnd class leaf leaf 从二叉树到 B+树的演进： ...

🗄️ 数据库迁移实战：不停机迁移方案、数据一致性保障与工具选型全解析 从一个凌晨 3 点的故障说起 某电商平台的订单表 orders 有 2.3 亿行数据，运行在 MySQL 5.7 上，单表体积接近 400GB。团队计划将这张表迁移到 TiDB 分布式数据库，以应对即将到来的双十一流量峰值。 DBA 团队的迁移方案是： 凌晨 2 点，停止所有写入服务 用 mysqldump 导出全量数据（耗时 1 小时 20 分钟） 将 dump 文件导入 TiDB（耗时 3 小时） 凌晨 6 点 20 分，恢复写入服务 结果：凌晨 4 点 30 分，dump 文件导入到一半时报错——导出文件中有 3 行数据包含 MySQL 5.7 特有的 utf8mb4_general_ci 排序规则下的隐藏字符，TiDB 解析失败。此时 MySQL 5.7 已被设置为只读，TiDB 导入中断， 整个订单系统处于不可用状态 。 最终临时回滚 MySQL 只读限制，恢复业务。迁移失败，双十一扩容计划延期。 这次故障暴露了数据库迁移中的核心难题：如何在保证数据一致性的前提下，尽可能缩短甚至消除停机时间，并且始终保留可靠的回滚路径。 数据库迁移策略总览 数据库迁移不是单一操作，而是一整套工程方法论。先通过思维导图建立全局认知： flowchart LR classDef root fill:#0f172a,stroke:#3b82f6,stroke-width:2px,color:#bfdbfe,font-weight:bold; classDef branch fill:#2d1a05,stroke:#f59e0b,stroke-width:2px,color:#fde68a,font-weight:bold; classDef leaf fill:#1e1e24,stroke:#6b7280,stroke-width:1.5px,color:#e5e7eb; classDef highlight fill:#450a0a,stroke:#dc2626,stroke-width:1.5px,color:#fecaca,font-weight:bold; ROOT[数据库迁移策略体系] ROOT --> B1(1. 按停机时间分类) B1 --> L1["🛑 停机迁移\n• 停服→导出→导入→恢复\n• 停机: 小时至天级\n• 风险: 业务中断"] B1 --> L2["⚡ 零停机迁移\n• 双写/CDC/灰度切换\n• 停机: 秒级切换\n• 风险: 数据不一致"] B1 --> L3["🔄 滚动迁移\n• 按分片/租户逐批切\n• 停机: 每批秒级\n• 风险: 跨片依赖"] ROOT --> B2(2. 按数据同步方式分类) B2 --> L4["📦 全量+增量\n• 全量快照 + binlog 追赶\n• 代表: DTS/Canal/Debezium"] B2 --> L5["✍️ 双写\n• 应用层同时写新旧库\n• 全量回溯 + 双写 + 校验"] B2 --> L6["🔁 主从复制\n• 新库作为旧库的从库\n• 追平后切换"] ROOT --> B3(3. 按迁移目标分类) B3 --> L7["🏗️ 同构迁移\n• MySQL→MySQL 版本升级\n• 工具: gh-ost/pt-osc"] B3 --> L8["🔀 异构迁移\n• MySQL→TiDB/PostgreSQL\n• 需处理类型/SQL差异"] B3 --> L9["☁️ 上云迁移\n• 自建→RDS/云原生DB\n• 工具: DTS/DataX"] class ROOT root; class B1,B2,B3 branch; class L1,L2,L3,L4,L5,L6,L7,L8,L9 leaf; class L2,L4 highlight; 三类策略并非互斥——零停机迁移通常是"双写 + 全量快照 + 增量追赶 + 灰度切换"的组合。 ...