MySQL备份与恢复详解：mysqldump、XtraBackup、binlog和PITR
备份的目标是可恢复，不是「有个 dump 文件」。逻辑备份、物理备份与 binlog 各管一层。 三类手段 mysqldump：逻辑备份，便携、慢，适合小库或表级迁移。 XtraBackup：热物理备份，适合大库；配合 binlog 做 PITR。 binlog：增量变更日志，按时间点/位点恢复误删数据。 PITR 基本流程 恢复最近全量备份到临时实例。 mysqlbinlog --start-datetime=... --stop-datetime=... 重放增量。 验证后切换或回灌生产。 12mysqldump -uroot -p --single-transaction --master-data=2 app > full.sqlmysqlbinlog --start-position=154 mysql-bin.000012 | mysql -uroot -p app 实践建议定期做恢复演练；备份文件加密与异地存储；监控 binlog 保留天数与磁盘。 权限与脱敏备份文件含全量数据，存储加密、访问审计、定期销毁策略必备。恢复环境应与生产网络隔离...
MySQL自增主键一定是连续的吗？
自增 ID「跳号」在 InnoDB 中是常态，不等于数据丢失。弄清机制可避免误报与错误设计。 为什么不连续 回滚：插入成功又 rollback，已分配的 ID 不会回收。 失败重试：唯一键冲突等导致插入失败，自增值可能已递增。 批量插入：innodb_autoinc_lock_mode 影响分配策略，并发下可能交错。 手动指定：插入显式 id 会推进计数器到更大值。 重启：计数器按表中 MAX(id)+1 重新初始化（8.0 行为需结合持久化配置）。 设计启示自增主键适合聚簇索引与插入局部性，但不应作为业务编号（发票号、订单号需独立序列或号段服务）。 12SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';-- 0/1/2 trade-off между 并发与连续性 实践建议业务展示用雪花 ID/UUID/号段；数据库自增仅作内部 surrogate key。 插入模式与页分裂随机主键（UUID）导致页分裂多、缓冲池利用率差；自增主键顺序插入友好。UUID v7 等时间有序 ID 是折...
MySQL隐式转换造成索引失效
WHERE 明明有索引却全表扫描，经常是列类型与常量类型不一致触发隐式转换。 机制说明当比较两侧类型不同时，MySQL 按转换规则把一侧转为另一侧。若对索引列做函数或转换，优化器往往无法使用该索引，因为索引存的是原始值顺序。 典型场景123456789-- phone 为 VARCHAR，常量写数字 → 列被转数字，索引失效SELECT * FROM user WHERE phone = 13800138000;-- 正确写法SELECT * FROM user WHERE phone = '13800138000';-- 对列做函数同样失效SELECT * FROM orders WHERE DATE(created_at) = '2026-01-01';-- 改为范围查询SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'; 排查步骤EXPLAIN 看 type&#x...
SQL语句在MySQL中的执行过程
搞清 SQL 执行链路，才能理解权限校验、解析、优化、执行与结果返回各阶段在做什么。 执行阶段概览 连接器：TCP 连接、身份认证、权限缓存（修改权限需重连生效）。 查询缓存（8.0 前）：相同 SQL 命中缓存；8.0 已移除，不必再依赖。 分析器：词法语法解析，报错 Syntax error 出在这里。 优化器：选择索引、连接顺序、是否使用 ICP/MRR。 执行器：调用存储引擎接口，逐行或索引范围读取。 存储引擎：InnoDB 负责缓冲池、锁、MVCC 与日志。 查询 vs 更新SELECT 可走只读快照；UPDATE/DELETE 需加锁并写 undo/redo。同一连接内语句顺序影响锁持有时间。 实践建议1EXPLAIN FORMAT=TREE SELECT * FROM orders WHERE user_id = 1 AND status = 'PAID'; 用 profiling/performance_schema 看阶段耗时，区分解析慢还是引擎 IO 慢。 Prepared Statement...
一千行 MySQL 学习笔记
长篇笔记的价值在于「可检索的操作清单」。本文按连接、DDL/DML、权限与状态查看重组，方便日常查阅。 连接与基础操作12345mysql -h127.0.0.1 -P3306 -uroot -pSHOW DATABASES;USE app;SHOW TABLES;DESC users; DDL 与 DML 要点建表时明确字符集、主键策略与必要索引；更新删除务必带 WHERE 并先 SELECT 验证影响行数。批量变更大表用 pt-online-schema-change 或 gh-ost，避免长时间锁表。 用户与权限最小权限原则：应用账号仅授予所需库的 SELECT/INSERT/UPDATE/DELETE；DDL 与 SUPER 权限隔离给运维账号。 状态诊断入口123SHOW PROCESSLIST;SHOW ENGINE INNODB STATUS\GSHOW VARIABLES LIKE 'innodb%'; 常用信息_schema 查询查表大小、索引Cardinality、未使用索引等可从 infor...
MySQL 专题：索引、事务、日志、备份恢复、MVCC 与性能优化
MySQL 是后端核心考点。把索引、执行计划、事务隔离、三大日志连成一条链，慢 SQL 与并发问题才看得懂。 推荐学习顺序索引结构 → EXPLAIN 执行计划 → SQL 执行链路 → 隔离级别与 MVCC → redo/undo/binlog → 备份恢复与规范优化。 核心主题地图 索引：B+ 树、最左前缀、覆盖索引、回表、索引下推。 事务：ACID、四种隔离级别、幻读与间隙锁。 日志：redo 保证崩溃恢复，undo 支撑回滚与 MVCC，binlog 服务复制与 PITR。 性能：慢查询、连接池、规范建表、避免隐式转换。 实践方法每学一章做一个小实验：故意写错索引看 type 变化；开两个事务验证 repeatable read；用 binlog 做一次时间点恢复演练。 常见误区只背八股不做 EXPLAIN；忽略 buffer pool 与磁盘 IO；把分库分表当性能万能药而不先优化 SQL。 工具链配套慢日志、slow_query_log、performance_schema、sys schema、PMM/Grafana 形成闭环。S...
SQL常见面试题总结（5）
窗口函数在不折叠行的前提下做排名与累计，是报表与分析 SQL 的利器。MySQL 8.0 起支持完整窗口语法。 窗口函数结构函数 OVER (PARTITION BY ... ORDER BY ...) 1234567SELECT user_id, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn, SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amtFROM payments; 常见函数ROW_NUMBER/RANK/DENSE_RANK；LAG/LEAD 取上下行；FIRST_VALUE/NTH_VALUE。 与 GROUP BY 区别GROUP BY 聚合成更少行；窗口函数保留明细行并附加计算列。 实践建议Top-N per grou...
SQL常见面试题总结（4）
子查询可读性高但可能被优化成 JOIN。掌握 EXISTS 与 IN 的差异，避免 correlated subquery 性能陷阱。 子查询分类标量、列、表、相关子查询。相关子查询每行外层执行一次，数据量大时极慢。 EXISTS vs IN123456-- EXISTS 找到即停，常优于大 IN 列表SELECT * FROM users uWHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);-- NOT IN 注意 NULL：子查询含 NULL 可能全 falseSELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned WHERE user_id IS NOT NULL); CTE（8.0+）1234WITH recent AS ( SELECT * FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY))SELECT user_id, COUNT(*)...
SQL常见面试题总结（3）
连接是关系型威力所在。理解笛卡尔积、内连接与外连接的差异，避免「连完行数爆炸」。 JOIN 本质先产生行组合再过滤。无 ON 条件的 JOIN 即笛卡尔积，行数 = 各表行数乘积。 1234SELECT o.id, u.nameFROM orders oINNER JOIN users u ON o.user_id = u.idWHERE o.created_at >= '2026-01-01'; 外连接LEFT JOIN 保留左表无法匹配的行（右列 NULL）；用于「查所有用户及其 optional 订单」。 UNION vs UNION ALLUNION 去重合并；UNION ALL 保留重复行，性能更好。 实践建议连接列建索引；小表驱动大表是优化器工作，写法上保证 ON 条件 selective。 SEMI/ANTI JOINEXISTS 是 semi join；NOT EXISTS 是 anti join。优化器可能转为 hash join。 自连接树形结构 employee-manager 用自连接或递归 CTE（8....
SQL常见面试题总结（2）
聚合题考的是「一行变多行统计」的分组边界。HAVING 与 WHERE 的分工必须讲清楚。 聚合函数SUM/AVG/MAX/MIN/COUNT 对一组行归约；与 GROUP BY 联用；无 GROUP BY 时全表一行聚合。 WHERE vs HAVINGWHERE 在分组前过滤行；HAVING 在分组后过滤组。想过滤聚合结果必须用 HAVING。 12345SELECT dept_id, AVG(salary) AS avg_salFROM employeeWHERE status = 'ACTIVE'GROUP BY dept_idHAVING AVG(salary) > 15000; WITH ROLLUP生成小计/总计行，报表场景有用，注意 NULL 与 super-aggregate 标识。 FILTER 子句标准 SQL FILTER (WHERE …) 部分库支持；MySQL 用 SUM(CASE WHEN…) 模拟条件聚合。 空组GROUP BY 无匹配行时不返回行；需要零行报表用 ...