10 深入专题：综合技术要点
这一篇收录 MySQL 生态的综合技术要点：SQL 写法与字符集、JSON/全文检索、用户权限与安全、时区处理、以及一些不常见但值得了解的细节。内容整理自爱可生开源社区《大智小技》系列（2019/2020 两册）技术文章精选。
MySQL 8.0 中 DDL 为什么成本高？按什么维度分类？
InnoDB 数据按聚簇索引（B+树）排列，加列需重建整表数据，成本高。8.0 按五维度分类：Instant（立刻完成）、In Place（引擎独立完成省开销）、Rebuild Table（重建聚簇索引）、Permits Concurrent DML（是否允许并发 DML）、Only Modifies Metadata。例：SET DEFAULT 仅改元信息（Instant）；DROP INDEX 标记删除（低成本）；ADD INDEX 建二级索引并回放并发 DML；DROP COLUMN 重建聚簇索引（高成本）；MODIFY COLUMN 需 server 层复制表（高成本）。运维建议显式指定 ALGORITHM 从低到高尝试。
MySQL Instant Add Column（立刻加列）的原理与限制？
腾讯引入。立刻加列只变更数据字典：增加新列定义与默认值；读取"立刻加列"之前写入的旧数据时，MySQL 在行后"伪造"追加该列的默认值使结果正确，读取之后写入的新数据时用新格式（行头 instant 标志位+“列数"字段）如实读取。故高效但根本不变更数据行结构。限制：加列只能在表最后（元数据只记列数不记位置）、不能加主键列（会涉及聚簇索引变更变重建）、不支持 COMPRESSED 表格式（WL 称 no need to supported）。“伪造"手法不能一直维持，出现不兼容 DDL（如删除列）时表数据需重建。“立刻"指不变更行结构，并非零成本——仍需上锁变更数据字典。
MySQL 组提交（binlog group commit）的三阶段是什么？
前提双1：sync_binlog=1、innodb_flush_log_at_trx_commit=1。引入 binlog 后二阶段提交，binlog 组提交分三队列阶段：Flush（刷 Redo log prepare 数据、写 binlog 到文件系统缓冲，提供 Redo 组提交）、Sync（binlog_group_commit_sync_delay=N 微秒等待、binlog_group_commit_sync_no_delay_count=N 达量则忽略延迟，直接刷盘，提供 binlog 组提交）、Commit（引擎层提交，不刷盘）。每阶段由首个进入的 leader 领导整队事务。5.7.19 中 sync_delay 非10倍数致长时间等待的 bug 已在 5.7.24/8.0.13 修复。
固定的 server_id 为何会导致数据丢失？如何避免？
复制中 slave 的 io 线程发现 binlog 的 server_id 与自身相同则跳过写入 relay log。高可用切换后，旧 master 从备份恢复仍用原 server_id A，会跳过 A:2 事务致数据丢失；级联复制中不相邻实例 server_id 相同也会丢。建议：每实例配不同 server_id；备份还原后配新 server_id。MySQL 5.6+ 引入 server_uuid（存 data_dir/auto.cnf，相同则复制初始化报错；GTID 用它做全局标识），直接拷贝数据建 slave 须删 auto.cnf 重启重新生成。
FTWRL（flush table with read lock）的堵塞与被堵塞场景？
FTWRL 步骤：加 GLOBAL S 锁（DDL/DML/FOR UPDATE 持 GLOBAL IX 锁不兼容→Waiting for global read lock）；推进全局表缓存版本并释放空闲 table 缓存（长 select 占用缓存未释放→Waiting for table flush，且 KILL FTWRL 也无用，须 KILL 长 select）；加 COMMIT S 锁（大事务提交→Waiting for commit lock）。FTWRL 堵塞 DDL/DML/FOR UPDATE（GLOBAL S）和 commit（COMMIT S），不堵塞普通 select。table 缓存含 table_def_cache 与 table_open_cache。
HASH_SCAN 导致从库 Can’t find record 的 bug 及解决办法？
无主键表 row 模式复制，slave_rows_search_algorithms=‘INDEX_SCAN,HASH_SCAN’ 时，同一 Update_rows_log_event 中一行被更新两次会漏掉第二次更新，从库报 Can’t find record。复现：CREATE TABLE t1(A INT UNIQUE KEY,B INT); replace into t1 values(1,3),(1,4); 官方确认为 bug，修复于 8.0.17。解决：1) 给表加主键（规范必须有主键）；2) 改参数 ‘INDEX_SCAN,TABLE_SCAN’；3) 升级到 8.0.17。注意 8.0.2 起默认值已改为 INDEX_SCAN,HASH_SCAN。
MySQL 字段默认值该选空字符串还是 NULL？
NULL 在每行行首有 NULL 标志位，实际不占空间；空字符串占空间。但 NULL 行为特殊：min/max/sum 忽略 NULL，avg 结果可能非预期；1+NULL=NULL；order by 升序 NULL 排最前、降序排最后；group by/distinct 视 NULL 为相同值；count(列) 不含 NULL 行。字符型 NULL 与字符串 ‘NULL’ 难区分，length(NULL) 返回 NULL。尽管存储/索引未必差于空值，为避免不确定性，建议默认值不用 NULL。
gh-ost 在线 DDL 的核心原理与 cut-over 切换？
gh-ost 模拟 slave 读 binlog，建 _ghc（日志表）与 _gho（影子表，先 alter 成目标结构），row copy 原表数据（lock in share mode 防改）到 _gho，再持续 apply binlog 增量保证一致。cut-over 原子切换：c10 建 _b_del 哨兵表并 LOCK TABLES b WRITE；c20 设 lock_wait_timeout=1 执行 rename（被阻塞）；c10 查 c20 在等 MDL 后 drop _b_del，再 UNLOCK TABLES，rename 优先于 DML 执行。对复制无损（binlog 不复制 lock，只复制 rename）。
如何用可传输表空间跨实例快速 copy 大表？
MySQL 5.6 借鉴 Oracle 引入，适用 InnoDB，规避昂贵 SQL 解析与 B+树叶分裂。步骤：目标库建表结构后 ALTER TABLE t DISCARD TABLESPACE（仅留 frm）；源库 session1 执行 FLUSH TABLES t FOR EXPORT（加锁、刷脏、生成 t.cfg），session2 传 t.cfg+t.ibd 到目标（主从需分别传，改属主 mysql:mysql）；源库 unlock tables；目标库 ALTER TABLE t IMPORT TABLESPACE（主库执行即可）。实测 25G ibd 导入仅 6 分钟。限制：源目标版本一致、仅 InnoDB、export 时表不可写。
一次 INSERT 语句会触发几次刷盘？
用 pt-ioprofile（基于 strace 监听 MySQL 进程 IO 系统调用，统计次数/时间）跟踪，一次 insert 对 redo log 刷盘 3 次（fsync），对 binlog 刷盘 1 次（fdatasync），且两者刷盘方法不同。注意：MySQL 多个逻辑会触发刷盘（如主线程刷脏），每次 fsync 若无数据则不造成压力，3 次不必过忧。双1（sync_binlog=1、innodb_flush_log_at_trx_commit=1）保证事务持久化，redo 顺序写比刷数据文件高效。
不小心对大表执行了 update，如何查看进度？
通过 performance_schema 观察 rows_examined。更新主键时从引擎获取行数约为表大小的 2 倍（删旧+插新），进度≈rows_examined/(2×表行数)；仅更新内容不更新主键时约为表大小的 1 倍。表行数可用 information_schema.tables 估算（成本低，几乎可忽略）。准确倍数靠经验（where 扫描行数、是否改主键/唯一键）或同结构小表试验获得，从而估算大型 update 进度。
MySQL Table Cache 的作用是什么？
Table Cache 是表定义缓存：table_def_cache 对应 TABLE_SHARE（静态表定义），table_open_cache 对应会话实际使用的实例（Open_tables）。未命中时需打开并读取 .frm 表结构文件（strace 可见 mysqld 打开 test_tbl.frm 并读取内容）；同一会话第二次 select 命中缓存，省去读文件开销。不同会话也可共享缓存，减少打开表的结构文件 IO。调优 table_open_cache/table_definition_cache 可降低高频建连场景的表打开开销。
如何评估 ALTER TABLE 的执行进度？
用 performance_schema 评估 ALTER 进度：开启相关 instruments 后执行 DDL，关联 events_stages_current（阶段）与 events_statements_current（语句），条件 stage.THREAD_ID=stmt.THREAD_ID 且 stage.NESTING_EVENT_ID=stmt.EVENT_ID。可取当前阶段、起止时间及工作量 WORK_COMPLETED/WORK_ESTIMATED，二者比值即进度（注意时间是当前阶段、工作量是整句累计）。开启 P_S 仅多约 1% CPU，开销很小。
误删 ibd 数据文件后如何恢复？
Linux 的 rm 只是减少文件使用计数，文件仍被 MySQL 占用并未真删，无法经文件系统访问。步骤：锁流量（支持 offline_mode 的版本设 offline_mode）；记录表记录数与校验值；通过 /proc/
大 SQL 文件回放如何不影响在线业务？
直接回放 dump 文件会使 MySQL CPU 飙升，影响业务。用 PV 工具限制 SQL 文件发往 mysql client 的速度：pv dump.sql | mysql -u… -p…，从而限制回放速率，使 CPU 保持冷静缓慢处理。PV 既显示文件流进度也可限速，避免压死在线业务。对比直接 source 与 pv 限速两种方式的 CPU 占用差异明显。适合需立即回放又怕冲击业务的场景。
一条 INSERT 语句涉及哪些磁盘写入？顺序如何？
事务提交前：检查不写盘；InnoDB 改 buffer pool 数据页（不立即刷盘）；双1下 redo log 刷盘（顺序写高效）；binlog 刷盘（sync_binlog=1）。提交后：脏页达阈值或 IO 压力小时刷盘，先写 double write（防部分写失败，存共享表空间，顺序写），再刷数据文件；insert buffer 合并非聚集索引变更（buffer pool 不足时换出到共享表空间）；innodb_stats_persistent=ON 时刷统计信息到 innodb_table_stats/index_stats。汇总顺序：redo log→binlog→(double write, insert buffer) 共享表空间→用户表空间。
如何用 mysql_random_load_data 生成测试数据（含外键）？
percona 出品的 golang 工具，按自定义表结构生成随机数据，比 sysbench/mysqlslap 易用。支持外键：先灌父表基础数据，再灌子表时自动生成符合外键规范的数据（外键采样数量默认100）。26 列表 10 万条约 4 分钟；也支持根据外键引用关系生成关联数据。坑：v0.1.12 中 –max-fk-samples 不生效恒为100（有作者临时修复版），急用可下修复版或每张表分别配外键列再合并。
MySQL 崩溃如何保留现场（coredump）？
三步：1) 系统级开启 coredump（core 文件生成到 /tmp，需足够磁盘空间）；2) 调整 MySQL 运行用户 ulimit 的 core file 限制；3) MySQL 配置允许生成 coredump（相关 core 参数）。MySQL 8.0.14+ 提供 innodb_buffer_pool_in_core_file 参数，将 buffer pool 排除出 coredump 以减小体积。崩溃后可用 gdb 访问 core 文件获取所有线程堆栈；复杂崩溃仍需交研发或官方分析。
MySQL 8.0 对 Undo 表空间做了哪些改进？
默认两个 undo 表空间 undo_001/undo_002（不能直接 SQL 管理）。innodb_rollback_segments 改为每个表空间限制（最多128），可设多个表空间，缓解 5.7 高并发回滚段争抢；innodb_undo_log_truncate 默认开自动收缩防磁盘过大；废弃 innodb_undo_tablespaces。可 CREATE UNDO TABLESPACE undo_ts1 ADD DATAFILE ‘undo_ts1.ibu’（须 .ibu 后缀），ALTER UNDO TABLESPACE … SET INACTIVE 后 DROP；目录由 innodb_undo_directory 指定，可建在非默认目录。
MySQL 8.0.20 的 binlog 压缩功能是什么？
8.0.20 集成 ZSTD 算法，以事务为单位压缩写入 binlog，缓解高并发 binlog 暴涨占满挂载点与主从带宽瓶颈。新增事件类型 Transaction_payload_event 表示压缩事务；新增编码器/解码器实现编码解码。mysqlbinlog 解压解码后输出与原日志相同，并打印所用压缩算法、事务形式、压缩/未压缩大小作为注释。压缩后的事务以压缩状态有效负载在复制流中发往从库（MGR 中为 group member）或客户端（如 mysqlbinlog）。
有哪些实用的 MySQL DBA 诊断 SQL？
几类实用诊断 SQL：长事务——查 INFORMATION_SCHEMA.INNODB_TRX 关联 processlist 找运行≥5s 连接；MDL 锁——开启 performance_schema 中 wait/lock/metadata/sql/mdl 后查 metadata_locks 定位 FTWRL（LOCK_DURATION=‘EXPLICIT’）或 kill 阻塞源；锁等待——查 sys.x$innodb_lock_waits；内存——开启 memory instruments 后查 sys.memory_global_by_current_bytes；分区表/无主键表——查 INFORMATION_SCHEMA.PARTITIONS/COLUMNS 做巡检。这些覆盖日常最频繁的故障定位场景。
如何追溯是谁删除了表或数据（审计）？
通过 init_connect 在用户连接时写审计表：set global init_connect=‘insert into auditdb.accesslog(connectionID,ConnUser,MatchUser,LoginTime) values(connection_id(),user(),current_user(),now())’，并对所有用户授 auditdb.accesslog 的 insert 权限（不可授 update/delete 防手动删）。误删后 mysqlbinlog -v –base64-output=decode-rows 解析 binlog，搜 delete 语句定位 thread_id，再查 accesslog 对应 Connectionid 得到用户与 IP，进而定位责任人。
查询字段数量对查询效率有何影响？
全表扫描执行计划相同但字段越少越快（sending data 状态耗时少）。流程：MySQL 层构建 read_set 位图标记访问字段→InnoDB 层据 read_set 构建 mysql_row_templ_t 模板（只含需访问字段）→取整行指针→按模板逐字段转换为 MySQL 格式（实际内存拷贝，每行每字段都做）→where 过滤。字段越多 read_set/模板越大，每行转换循环次数越多，最耗时在格式转换环节。建议只查需要的字段而非 *，规避无谓转换开销。
大数据量更新后如何提升回滚效率？
官方两法：1) 临时调大 innodb_buffer_pool_size（动态调整、温和，buffer pool 大于数据量时回滚明显加快；实测 1600 万行 update 7m23s、回滚 6m39s，调大后略快）；2) 暴力法：kill -9 mysqld，备份数据/日志，设 innodb_force_recovery=3 启动完成回滚后正常关闭，再去掉参数启动（错误日志记 “Rollback of non-prepared transactions completed”），无需等回滚但短暂影响业务。生产慎用 innodb_force_recovery，须明确对数据的影响。
MySQL 从库 SQL_THREAD 出现 System lock 状态的原因？
row 格式下从库无语句执行、直接 apply event，状态无切换机会。小事务（单行 DML event）逻辑：reading event from relay log→读 event→system lock→InnoDB 查找修改；大事务则循环且状态停在 reading event。故 system lock 实为正在干活而非真等待锁。加剧条件：大量小事务、表无主键/唯一键、InnoDB 层锁堵塞。可调 slave_rows_search_algorithms 缓解无主键问题。延迟计算：time(0)-last_master_timestamp-clock_diff_with_master。
什么是 InnoDB 半一致性读（semi-consistent read）？
一种 Update 中的读优化，结合 RC 隔离级别与一致性读。当 update 的 where 匹配记录已上锁时，会再次到 InnoDB 读最新已提交版本，判断是否真的需要上锁（第一次需由 InnoDB 返回最新已提交版）。仅 RC 隔离级别或 innodb_locks_unsafe_for_binlog=1 时发生；8.0 已去除该参数（官方不建议用）。好处：减少锁冲突、提升并发 update 效率，避免不必要的行锁等待。
如何快速处理 MySQL 中的重复数据？
完全重复无主键：建克隆表 insert into d2 select distinct 或系统层 select into outfile + sort|uniq + mysqlimport（OS 层更快，约一半时间）。有主键留最大值：delete a from d4 a left join (select max(id) id from d4 group by r1,r2) b using(id) where b.id is null。字段多余空白：trim 去首尾空白；中间空白用 regexp_replace(r1,’[[:space:]]+’,’ ‘)（MySQL 8.0 正则）或导出 sed 处理。业务上达到超多字段/重复场景建议先与业务确认保留规则。
MySQL 主机 OOM 与 hugepage（大内存页）的关系？
案例：64G 内存，MySQL RES 仅 20G 却 OOM 被 kill。根因：曾开 20000 传统大页（每页2M=40G）预留给 mysql 组（hugetlb_shm_group=27），后仅禁用 MySQL 端 large page 但未改主机大页配置，40G 长期空闲且不可交换，导致主机内存饱和触发 OOM，mysqld_safe 又拉起。建议：MySQL 主机一般不用 hugepage（MySQL 自管 buffer pool 分页）；上线前检查是否有未使用传统大页，避免影响业务。
从库单表数据不一致时如何单独恢复该表？
场景：复制报错停在 GTID aaaa:1-100，主库持续更新。正确步骤：主库 mysqldump –single-transaction –master-data=2 备份表 t（快照 GTID aaaa:1-10000）恢复到从库；设复制过滤 CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=(‘db.t’)；START SLAVE UNTIL SQL_AFTER_GTIDS=‘aaaa:10000’ 回放到一致；删除过滤正常启动。这样跳过 aaaa:101-10000 中修改 t 的事务，避免主键冲突/记录不存在，保证全库状态一致。
MySQL TEXT 字段的数量限制与行大小限制？
Server 层单行≤65535 字节；InnoDB 单行≤innodb_page_size/2（默认16K页→8126字节，保证每页≥2行）。InnoDB 列数上限1017。DYNAMIC 格式下 TEXT 内容≤40字节存本记录(40+1)，超出存溢出页（本记录留20字节指针）。严格模式(innodb_strict_mode=on) 极端可建196个TEXT（公式 5+ceil(x/8)+6+6+7+41x≤8126）；关严格模式可到1017但 insert/update 可能失败。建议超多字段用 JSON 类型或拆表，而非堆 TEXT。
MySQL DBA 35 岁是否会被失业？职业发展建议？
观点型。相对网络、传统运维被 DevOps 替代，云对 DBA 冲击较小，DBA 角色正向数据架构师演进（分基础架构与业务架构，业务架构师需结合电商/金融等行业、要求全链路能力）。MySQL 在 Oracle 旗下十年进步显著（5.5→5.6→5.7→8.0，InnoDB 与 MySQL 整合，已进入金融级场景）。因金融业务对事务型数据库要求最高，能在金融用意味着其它行业也没问题，故建议 MySQL DBA 往金融行业（如银行核心周边）发展以发挥技术价值。连线答疑的结论是：职业年龄焦虑多为个别情况与无良小编贩卖焦虑，关键在好好工作、持续学习、提高业务能力与全链路视野。
MySQL 高可用与 Oracle（RAC/Dataguard）架构有何差异？
MySQL 主从半同步（5.5）/无损半同步（5.7）/MGR（5.7.17，基于 Paxos，share nothing 多写）；Oracle Dataguard 类似日志复制，RAC 是 share everything 共享存储（单点风险）。MySQL binlog 是逻辑日志（灵活，易同步到大数据/ClickHouse，但 DDL 有延时，5.7 并行复制、8.0 快速加列缓解）；Oracle 物理日志同步快但封闭。结论：share nothing + 逻辑日志更适应当下数据库发展，MySQL 正在金融核心逐步替代 Oracle。
什么是 NewSQL？分布式中间件+MySQL 算 NewSQL 吗？
观点型：NewSQL 指支持分布式水平扩展+海量并发+事务的数据库。2016 论文分三类：完全重做（TiDB/CockroachDB）、中间件+数据库节点（DBLE/TDSQL，兼容旧业务但多一次解析）、计算存储分离（Aurora）。分布式中间件+MySQL 也属 NewSQL 且发展最好（银行有案例），作者认为"没有 NewSQL”，能解决业务痛点即可。工程中更关注实用：分布式事务占比约1%，无需过度追求架构新颖。
国产数据库谁会最终胜出？MySQL DBA 需要什么技能？
观点型：传统国产（达梦/金仓，偏 PG 生态弱）；新型基于 MySQL 协议——TiDB 开源国际化、OceanBase 纯自研 LSM-Tree 蚂蚁验证最被看好但闭源生态慢、巨杉自研存储引擎、GaussDB 类 Aurora。基于 MySQL 生态最可控。DBA 技能：学 Java/Python、写代码（如用 Python 重写 MHA/mydumper）、看优秀源码（MySQL/Percona 驱动）、参与开源成 commiter。开发能力是避免沦为纯运维的关键。