03 深入专题：性能优化
这一篇聚焦 MySQL 性能优化：慢 SQL 定位与执行计划分析、索引策略、参数调优、大表 DDL/删除优化、连接池、磁盘 IO 与内存分配等实战要点。内容整理自爱可生开源社区《大智小技》系列（2019/2020 两册）技术文章精选。
MySQL 8.0 不可见索引（Invisible Index）是什么，与 DROP INDEX 有何区别？
Invisible Index 对优化器不可见，查询时不会作为候选索引，但索引物理仍存在。与 DROP INDEX 相比，改为不可见只修改 metadata，开销极小（文中 ALTER TABLE f1 ALTER INDEX idx_f2 INVISIBLE 仅 0.05s），而 DROP 要删结构且不可快速恢复。用法 ALTER TABLE t ALTER INDEX idx_name INVISIBLE/VISIBLE，SHOW CREATE TABLE 显示为 /*!80000 INVISIBLE */。适用场景：犹豫是否删除的“疑似无用索引”、仅月度报表使用的索引、测试新建索引对现网影响的灰度开关。
MySQL 8.0 为什么弃用 SQL_CALC_FOUND_ROWS，InnoDB 下有哪些获取总数的方法？
8.0 起 SQL_CALC_FOUND_ROWS + found_rows() 被标记 deprecated（warning 1287）并将移除，因其本质是全表扫且 found_rows() 为语句级存储、在 STATEMENT 格式主从下从机可能不准（改 ROW 格式可缓解）。InnoDB 无内置计数（MyISAM 才有），常见替代：①触发器维护计数表（写性能下降）；②information_schema.tables 的 table_rows（近似值，适合分页展示）；③主键连续无间隙时 MAX(id)；④8.0.17 起推荐直接实时 SELECT COUNT(*)。
MySQL 8.0 倒序索引（Descending Index）解决了什么问题？
8.0 之前索引只能正向(asc)存储，即使建 desc 也被忽略。对于 ORDER BY f1 ASC, f2 DESC 这类混合排序，5.7 执行计划会出现 Using temporary; Using filesort，需借助临时表与排序，资源消耗巨大（文中示例反向排序比正向扫描行数、临时表读写记录数均多约 10 倍）。8.0 的 Descending Index 以反向顺序存储（注意并非搜索引擎的“倒排索引”），可消除此类临时表与 filesort，使排序直接由索引满足，性能显著提升。
MySQL 8.0 函数索引（表达式索引）是什么，底层如何实现？
MySQL 8.0 引入函数索引（也称表达式索引），可对字段套用函数或表达式建索引，内部基于 5.7 已有的虚拟列（generated/virtual column）实现。建法为 ALTER TABLE t ADD INDEX idx((date(log_time)))，注意表达式放在双层括号内。它适合无法或不便改写的 WHERE 条件（如 date(time)=curdate、字段相加、子串、JSON 取值等），避免增加冗余列+触发器。缺点：SQL 必须严格按索引定义的函数写法书写，否则优化器无法识别而全表扫描（如 rank1+rank2=121 命中 idx_u1，而 rank1=121-rank2 退化为 ALL）。可用 SHOW EXTENDED 查看其隐式生成的虚拟列。
MySQL 8.0 对 COUNT(*) 做了怎样的优化，相比 5.7 提升多少？
8.0 对 count(*) 的执行成本做了优化。文中同表同查询 explain format=json 对比：5.7.27 的 query_cost=622.40（read_cost 8.00）；8.0.17 的 query_cost=309.95（read_cost 2.75），约提升一倍，且两者均走 PRIMARY 的 using_index（覆盖索引全索引扫描），rows 相同。因此 8.0 后不必再用 SQL_CALC_FOUND_ROWS 等变相写法，直接实时计算即可。
MySQL 8.0 的索引跳跃扫描（Index Skip Scan, ISS）是什么，适用什么场景？
ISS 是 MySQL 8.0 引入的优化，用于“组合索引未包含最左列、却只过滤非最左列”的查询。例如联合索引 idx(rank1,rank2)，查询 WHERE rank2>400 在 5.7 只能全表扫描；8.0 的 ISS 会对最左列做 DISTINCT，把 SQL 等价改写为 ... WHERE rank1=1 AND rank2>400 UNION ALL ... WHERE rank1=5 AND rank2>400，从而走索引，省去为 rank2 单列建索引（减少写开销）。ISS 适合最左列 distinct 值较少的情况（如性别、状态），扫描行数远少于全表。
MySQL 8.0 直方图（Histogram）解决什么痛点，相比索引有什么优势与限制？
优化器选计划需要列的取值分布（不同值个数、NULL 数、最大/最小值等），但无法给每列都建索引（写入代价大）。直方图以较小开销为列提供分布统计。MySQL 8.0 支持等宽(equi-width)与等高(equi-height)两种，类型由系统自动选择（文中示例自动分配 equi-height、默认 100 桶）。存储在 information_schema.column_statistics（JSON），由参数 histogram_generation_max_mem_size 控制建图内存。限制：不支持 geometry/json 类型、不支持加密表与临时表、不支持列值完全唯一、需手动 ANALYZE 更新分布。
为什么函数索引要求 SQL 写法必须和索引定义的函数完全一致？
函数索引本质建立在“表达式计算结果”的虚拟列上，优化器只能对“同一表达式出现在 WHERE/ORDER BY”做匹配。文中示例：WHERE rank1+rank2=121 使用 idx_u1（type=ref，rows=878）；等价的代数形式 WHERE rank1=121-rank2 改写后 possible_keys 变 NULL、type=ALL、rows≈16089、Extra=Using where，退化为全表扫描。因此使用函数索引必须让函数/表达式与定义逐一对应，不能用等价变换替代，否则失去优化效果。
如何在 MySQL 8.0 中创建和查看直方图，它对执行计划有何影响？
创建：ANALYZE TABLE t3 UPDATE HISTOGRAM ON rank1, log_date;（默认 100 桶，可加 WITH 桶数）。删除：ANALYZE TABLE t3 DROP HISTOGRAM ON rank1。查看：SELECT * FROM information_schema.column_statistics WHERE table_name='t3'，或用 json_pretty(histogram) 看 buckets/null-values/sampling-rate/histogram-type。文中示例：同一查询无直方图时优化器选 idx_rank1、cost≈2796；有直方图后因掌握 log_date 分布改选 idx_log_date、cost≈0.71，性能提升数千倍。
如何在 MySQL 8.0 中利用倒序索引优化混合排序？
针对混合排序需求建立对应顺序的联合索引，例如 KEY idx (rank1 ASC, rank2 DESC) 可满足 ... ORDER BY rank1 ASC, rank2 DESC。建好后 EXPLAIN 会命中 idx 且 Extra 中 Using temporary; Using filesort 消失（文中把原索引改为倒序索引后印证）。也可按 (rank1 asc/desc, rank2 asc/desc) 四种组合分别建索引覆盖不同排序。注意 8.0 前版本即使写 DESC 也按 ASC 存，无法消除 filesort。
对不可见索引使用 FORCE INDEX 会怎样？如何让优化器临时使用它？
不可见索引对优化器隐藏，即使 FORCE INDEX(idx_f2) 也会报错 ERROR 1176 (42000): Key 'idx_f2' doesn't exist in table 'f1'，即 FORCE INDEX 同样失效。若要临时让优化器“看得见”不可见索引作验证，可打开开关：SET @@optimizer_switch='use_invisible_indexes=on';，此后该索引重新可被选用（EXPLAIN 中 key 变为 idx_f2）。验证完毕再关掉即可，不影响其 INVISIBLE 属性。
索引跳跃扫描（ISS）在什么情况下优化器不会选择，为什么？
当组合索引最左列 distinct 值很多时（文中重造数据使 rank1 唯一值近万个），优化器不会再选 ISS，而改走 FULL INDEX SCAN（全索引扫描），此时必须为 rank2 单独建索引才能高效过滤。原因在于 ISS 内部要对最左列做 DISTINCT 并拆成多个子范围 UNION，若最左列基数大，拆分出的子查询数量爆炸，代价反而高于索引/全表扫描。结论：ISS 仅在最左列唯一值较少时才有收益。
JDBC 使用 useCursorFetch=true 为什么会把 MySQL 临时表空间 ibtmp1 撑爆，如何根治？
useCursorFetch=true 采用游标/分段读取，会使结果集存放在 mysqld 的共享临时表空间（ibtmp1）中。当查询（如 group by）产生的内部临时表超过 tmp_table_size/max_heap_table_size 时，5.7 会创建 InnoDB 磁盘临时表放入 ibtmp1，默认无限扩展（文中曾涨到 90+G 耗尽磁盘）。限制 innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:2G 后超限仅报错，但 useCursorFetch 模式下 JDBC 不返回错误、连接一直 sleep 等待超时，不利于排查。根治：放弃游标取数，改用流读取（setFetchSize(Integer.MIN_VALUE)），既防止撑爆 JVM 又能在 SQL 报错时让程序正常报错。
MySQL 5.7 共享临时表空间 ibtmp1 有什么特点，如何查看、限制与回收？
5.7 把临时表空间从 ibdata 分离为独立文件 ibtmp1，启动初始 12M，默认无限扩展；存放非压缩 InnoDB 临时表、回滚段等。与 tmpdir 区别：tmpdir 存放压缩 InnoDB 临时表（ROW_FORMAT=COMPRESSED）及临时文件。查看用 SELECT ... FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'（含 INITIAL_SIZE/TOTAL_EXTENTS/MAXIMUM_SIZE）。限制大小需配置 innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:2G（需重启生效），达到上限查询报 ERROR 1114 The table ... is full。回收唯一方式是重启 MySQL。
“执行计划显示走了索引但 SQL 还是很慢” 案例中，type 为 index 代表什么？
文中慢 SQL 的 EXPLAIN 选了 INDX_…_TASK3(TASK_ID)，type 为 index，即“全索引扫描”，和全表扫描代价相近，只是按索引顺序而非行顺序扫描，最大优点是避免了 ORDER BY TASK_ID DESC 的文件排序。Extra 为 Using where 表示扫完索引后还需回表逐行过滤。优化器选它是为了规避排序，但实际扫描行数≈全表（slow log 的 Rows_examined≈116 万）。结论：执行计划“用到索引”≠执行快，type 至少应达到 range、最好 ref 才有意义。
为什么 timestamp 字段的时区转换会导致 MySQL 的 CPU %sy 飙高？如何定位？
timestamp 内部以 epoch 秒存储（4 字节），展示时需做时区转换，且“每行符合条件的数据都要转换”。当 time_zone=SYSTEM 时，MySQL 调用 OS API（__tz_convert）经 Time_zone_system::gmt_sec_to_TIME 转换，该 OS 调用涉及内部锁（__lll_lock_wait_private），高并发下导致 %sy（系统态）飙升（文中达 40.4%）。定位方法：采集 pstack，若大量线程栈出现 __tz_convert / Time_zone_system::gmt_sec_to_TIME / Field_timestampf::get_date_internal 即可确认。
修复 timestamp 时区转换导致 %sy 高的方案有哪些？time_zone=SYSTEM 与 +08:00 的实现差异是什么？
差异：time_zone=SYSTEM 用 OS 会话时区+OS API 转换（Time_zone_system，锁竞争重、%sy 高）；time_zone='+08:00' 用 MySQL 自带实现 Time_zone_offset::gmt_sec_to_TIME（无 OS 锁，%sy 降但 %us 升）。修复方案：①将 time_zone 设为具体时区如 ‘+08:00’，避免 OS API；②改用 datetime 替代 timestamp（新版本 datetime 为 5 字节，仅比 timestamp 多 1 字节）。文中朋友将 time_zone 改为 ‘+08:00’ 后 %sy 明显下降。
共享临时表空间（ibtmp1）和 tmpdir 在存储内容上有何不同？
共享临时表空间(ibtmp1)用于存储非压缩的 InnoDB 临时表(non-compressed InnoDB temporary tables)、关系对象(related objects)、回滚段(rollback segment)等；而 tmpdir 用于存放指定临时文件和临时表，且 tmpdir 中存储的是压缩的 InnoDB 临时表(compressed InnoDB temporary tables)。可用 CREATE TEMPORARY TABLE ... ROW_FORMAT=COMPRESSED 验证压缩表落在 tmpdir。当内部临时表超过 tmp_table_size/max_heap_table_size 时，5.7 默认引擎为 InnoDB 的临时表会写入 ibtmp1。
分析慢 SQL 时，应以 EXPLAIN 的 rows 还是 slow log 的 Rows_examined 判断真实扫描行数？
应以 slow log 的 Rows_examined 为准。文中 EXPLAIN 显示 rows=644，实际慢日志 Rows_examined:1161559，二者差异巨大——EXPLAIN 的 rows 仅是优化器的估算值，并不准确。同理，文中 SQL 强制走 TASK_DATE 索引（force index）虽 EXPLAIN 估算代价更高，实际却比优化器所选索引更快（<10s vs 10s+），说明优化器基于代价的判断不等于真实执行速度，排查要以实测 Rows_examined 和响应时间为准。
针对“ORDER BY + 多过滤条件”的慢查询，应如何设计组合索引？文中还要注意什么隐患？
因有 ORDER BY TASK_ID DESC，应把排序列纳入索引以避免 filesort；同时选择度高（区分度好）的过滤列放前面。文中 TASK_DATE 区分度极低（116 万行仅 223 个 distinct 值），优化器不走高选择性低索引；而 REL_DEVID distinct 达 62235、与 TASK_ID 组合区分度 100%，故建 IDX_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID)，执行时间从 10s+ 降到 0.00s。注意隐患：REL_DEVID 是 varchar，SQL 中必须加引号 '000000025xxx' 避免隐式转换（数字 vs 字符串）导致索引失效。
Block Nested-Loop Join (BNLJ) 如何利用 join_buffer 提升无索引 JOIN 性能？
NLJ 对驱动表每行都需扫描一次内表；若驱动表 p2 有 1000 行、内表 p1 约 1000 万行，内表要被匹配 1000 次，read_cost 高达 5198505.87。启用 BNLJ 后，MySQL 把驱动表的 JOIN KEY 记录批量缓存进 join_buffer，一次性与内表匹配，匹配次数由 1000 次降到 1 次；EXPLAIN 出现 using_join_buffer: "Block Nested Loop"，read_cost 降到 5199.01，性能提升约 1000 倍。前提是 join_buffer_size 能容纳驱动表相关记录。
MySQL 8.0.18 的 Hash Join 与 join_buffer 是什么关系，如何确认用到了它？
8.0.18 引入 Hash Join，同样利用 join_buffer：在 buffer 中以外表为基础建立哈希表，内表通过哈希算法与哈希表匹配，进一步减少内表匹配次数。确认方式是用 explain format=tree 查看，计划会显示 Inner hash join (p1.r1 = b.r1) 与 Hash / Table scan on b 结构（文中示例 cost=997986300.01）。注意当前仅能简单查看是否启用，暂无其他更多细节展示，且仅支持简单查看。
MySQL 慢查询的判定标准是什么？为什么一条“总耗时很长”的 SQL 可能不被记录为慢查询？
慢查询基于 long_query_time 判定，但判定的是“实际执行时间”而非“实际消耗时间”。源码中 res = cur_utime - thd->utime_after_lock，当 res > long_query_time 才标记 SERVER_QUERY_WAS_SLOW。即：实际消耗时间 = 实际执行时间 + 锁等待消耗时间，而慢日志记录的 Query_time，判定标准是 Query_time - Lock_time（实际执行时间）。因此若语句总耗时很长但是因为锁等待（MDL、行锁、表锁）导致、实际执行时间很短，则不会记入慢日志。排查“明明很慢却没慢日志”时应优先怀疑锁等待。
Percona 慢日志详尽字段中，Rows_examined、Full_scan、Full_join、InnoDB_rec_lock_wait 分别代表什么？
在 log_slow_verbosity=full 的 Percona 慢日志中：Rows_examined 为 InnoDB 引擎层扫描/评估的行数（含 filesort 等多次计数，可能重复计数）；Full_scan(=Select_scan) 表示是否做了全扫描（含 using index）；Full_join(=Select_full_join) 表示被驱动表是否用到索引，未用到则为 Yes；InnoDB_rec_lock_wait 为等待行锁消耗的时间，InnoDB_queue_wait 为等待进入 InnoDB 引擎的时间。这些字段对定位“是否全扫/是否缺索引/是否锁等待”非常有用，配合 microtime/query_plan/innodb 选项开启。
join_buffer_size 在什么 INNER JOIN 场景下才生效，应如何调优？
join_buffer_size 用于缓存 JOIN KEY 无索引（或仅二级索引）时的检索，仅对 NLJ/BNLJ 中“无索引”或“二级索引”类别生效，主键索引（索引查找）场景不需要。官方建议 GLOBAL 设很小的值，按需用 SESSION 临时放大，例如 set session join_buffer_size=1024*1024*1024 跑完再 set session join_buffer_size=default；或用优化器 hint：select /*+ set_var(join_buffer_size=1G) */ * from ...。切忌把 GLOBAL 设得过大，否则高并发下按连接占用内存易引发 OOM。
慢日志中的 Lock_time 包含哪些锁等待？为什么一条 SELECT 也可能 Lock_time 不为 0？
Lock_time 包含三类锁等待消耗时间：①MySQL 层 MDL LOCK 等待；②MySQL 层 MyISAM 表锁等待；③InnoDB 层行锁等待。MySQL 层在 mysql_lock_tables 末尾调用 THD::set_time_after_lock 记录 utime_after_lock，MDL 和 MyISAM 表锁获取时间都包含在内，所以即便 SELECT 也常能看到非 0 的 Lock_time。InnoDB 行锁等待则经 thd_storage_lock_wait 累加。注意 Lock_time 并不全是纯锁等待，仅是“包含锁等待”的总时间段，记录并不十分精确。
中间件性能对比测试中，DBLE 在 B-SQL 压测下为何远低于 MyCat？根因是什么？
根因是 B-SQL（压测工具）的一个 bug 在 RR 隔离级别下被放大：当并发 delete 影响行数为 0 时会陷入死循环，不断下发排序 SQL，DBLE 因排序请求暴增而 CPU 飙升（火焰图显示纯排序占 15%+）。另外 DBLE 排序用的是多路归并（一处 cmp 初始化值实现缺陷使复杂度从 O(N) 变为 O(NlogK2)），也比 MyCat 的 timsort 略慢（约 10%，非主因）。综合之下 DBLE 性能仅为 MyCat 的约 70%。
为什么同样的并发条件 MyCat 只有约 25% 概率触发该死循环，而 DBLE 几乎 100% 触发？
关键在于隔离级别同步实现不同。二者默认都是 REPEATABLE READ，但 MyCat 有 bug：除非客户端显式 set，后端连接用的都是下属节点的默认隔离级别；DBLE 则会在获取后端连接后同步上下文，使 session 级隔离级别与配置一致。而后端 4 个节点中 1 个 RR、3 个 RC——MyCat 约 25% 概率落到 RR 节点才触发，DBLE 则 100% 以 RR 运行而稳定触发。规避方法：把 MySQL 节点统一改为 READ_COMMITTED，并在 DBLE 配置 <property name="txIsolation">2</property>（默认 3）。
MySQL 官方对 table_open_cache 的建议值公式是什么，为什么这样定？
文档建议值 = 最大并发连接数 × 单条 join 语句涉及的最大表个数。文中通过实验理解其缘由：table_open_cache（表缓存）是“针对线程”的，每个线程有自己的一份缓存只缓存本线程用到的表结构，所以需要“最大并发数”份；同时一条 join 语句涉及的多个表必须同时存在于缓存中，故最小缓存大小等于 join 涉及的最大表数。两者相乘即得建议值。补充：table cache 未命中时不一定读表结构文件（strace 中只有 stat 无 open），可能是命中了 table_definition_cache。
pt-table-checksum 在做数据一致性校验时，通过哪些设计把对线上业务的影响降到最低？
文中抓取其 general log 分析机制：①特意调小 InnoDB 锁等待超时，业务中稍有锁等待就立刻放弃操作（“退让”），对业务影响极小；②把隔离级别设为 RR（数据对比的基本要求），虽 RR 维护代价高于 RC，但每个事务都很小、叠加极小锁等待超时，成本可控；③按数据块逐块校验，每块 SQL 前先 EXPLAIN 评估成本，小心翼翼；④若与业务流量冲突，触发 InnoDB 锁超时立即退让。此外还提供 –max-load、–pause-file 等参数，以及精心设计的块划分与索引选择方法配合使用。
MySQL CPU 飚高时，如何用 top -H 配合 performance_schema 定位并 kill 掉捣乱的 SQL？三种线程号有何区别？
先用 top -H 找到持续高 CPU 的线程 OS 号（如 17967，若线程号不断变化则多半不是单条 SQL，需别的方法）。在 performance_schema / processlist 中按该 THREAD_OS_ID 关联到对应线程，即可看到 processlist 信息、PROCESSLIST_ID，从而下 kill 结束 SQL；还能看到 SQL 开始时间、是否用了磁盘临时表等。关键要分清三种线程号：①PROCESSLIST_ID——用户视角，可直接用于 kill；②THREAD_ID——MySQL 内部视角编号；③THREAD_OS_ID——操作系统视角编号（与 top -H 对应）。务必对应正确，避免 kill 错 SQL。
如何构造一个“磁盘 IO 慢”的实验环境来观察 MySQL 行为？
文中用 dbdeployer 安装 MySQL，把 binlog 位置指向一个带 IO 延迟的设备（如 /mnt/slow，通过 dm 设备 dm-0 模拟延迟），并开启双 1 刷盘参数（sync_binlog=1、innodb_flush_log_at_trx_commit=1）以放大 IO 压力，再用 mysqlslap 压测。iostat 观察到 binlog 所在块设备 dm-0 出现 IO 排队(aqu-sz)、使用率饱和，而其底层真实设备 loop3 仍有余量，证明延迟来自 dm-0；pt-ioprofile 显示花在 binlog IO 上的时间远高于其他。由此可制造“binlog IO 慢”场景，进而分析 IO 局部变慢时 MySQL 的状态量变化与行为。
磁盘 IO 报警时，如何快速定位是 MySQL 的哪个文件（binlog/redo/某张表）读写变慢？
启用 performance_schema 的 waits 相关 instrument（生产者，默认配置中 IO 类 instrument 已开启）与 waits consumer（消费者），清零已记录的性能数据，然后对 MySQL 施加压力。在另一 SESSION 观察最近的 IO 行为，借助 sys.x$latest_file_io 视图对最近的 IO 操作记录排序（x$ 视图是原始数据、未做单位美化），即可看到哪类文件（如 binlog 刷盘）的 IO 明显更慢，从而定位具体文件；结合线程号还能进一步定位其对应的操作。注意 sys.x$latest_file_io 只反映“当前活跃线程”的最近 IO，已退出线程不出现。
只有慢查询日志文件、没有监控系统时，如何快速生成按时间段（分时）的慢查询报告？
用 pt-query-digest --timeline 输出带时间戳的慢查询条目，再用 sed 将 timeline 报告滤出；接着安装 termsql，把该文本导入 SQLite，termsql 会把 timeline 的每一行整理成一条数据行。之后即可用 SQL 灵活统计，例如按小时聚合得到每小时慢查询条数的分时报告，轻松定位慢查询热点时段、发现业务周期性规律。补充：sys 中以 x$ 开头的视图是原始数据，不带 x$ 的是人类可读视图（时间带单位）。
如何用 performance_schema 观测内部临时表的内存占用？它与显式 MEMORY 表有何关系？
构造一个会用到内部临时表的 SQL（如带 UNION 的子查询，EXPLAIN 确认有临时表），主 SESSION 执行，另起 SESSION 用 performance_schema 观察该线程的内存分配：先记录初始状态，执行后查看，文中示例 SQL 处理过程中共分配 4M 多内存用于内部临时表。为验证，手工建一张显式 MEMORY(heap) 引擎表并插入相同数据，P_S 显示其驻留字节数与内部临时表使用的字节数相同——说明内部临时表采用 MEMORY 引擎格式。注意：information_schema.INNODB_TEMP_TABLE_INFO 并不展示内部临时表信息；且 memory 引擎会多划分空间（文中 1.2M 数据分出 4M+），估算时需乘较大系数。
为什么内部临时表即便在一条很短的 SQL 中使用并随即释放，落盘后仍会消耗 IO？
当磁盘临时表引擎配置为 InnoDB 时，数据写入临时表空间后，即使 SQL 执行时间很短、临时表使用后立即释放，InnoDB 仍会在后台通过 page_cleaner_thread 将相关脏页逐步刷盘（每次 IO 约 16K，即刷数据页），数据“慢慢逐渐写入”。这与 MEMORY 引擎纯内存、用完即弃不同：InnoDB 临时表落到 ibtmp1 共享临时表空间，刷脏是常态行为。所以短 SQL 的磁盘临时表也会产生刷脏 IO 开销，排查 IO 异常时需纳入考虑。
内部临时表在什么条件下会从内存转到磁盘？受哪些参数控制？
内存临时表大小受 tmp_table_size 与 max_heap_table_size 中较小者限制。当临时表所需空间超过该上限，MySQL 会基本遵守设定、把表转存到磁盘；EXPLAIN/语句特征值会显示“使用了一次磁盘临时表”。磁盘临时表的引擎由 internal_tmp_disk_storage_engine 决定（如 InnoDB），因此落盘的数据量与内存阶段的数据量可能不同。实验中把会话级 max_heap_table_size 设为 2M（小于实际所需），即触发落盘，临时表空间被写入约 7.92MiB。
如何用 performance_schema 观测 innodb_buffer_pool_instances 对性能（buffer pool 锁竞争）的影响？实验结论趋势是什么？
方法：开启 P_S 的 hash_table_locks instrument（生产者）与 waits consumer，并排除后台刷盘线程以免干扰；压测前清零观测数据，sysbench 压 60s 后分析。文中采集约 100 万条 hash_table_locks 观测，取平均、90%、99% 分位。结论：①平均值都落在 99% 分位以上，少数极端 buffer pool 锁等待严重拉高均值；②随 innodb_buffer_pool_instances 增大，极端影响渐减；③对 90%/99% 分位（大部分 SQL 取锁时间）影响不大。instances=1 时平均取锁约 2.7ms（6535546 cycle，1 cycle=1/2387771144 秒）。要点：参数过小锁冲突集中、过大维护成本升，需权衡；本实验重在传授手法，结论勿照搬。
MySQL 8.0.21 引入的 Disable Redo Log 功能如何使用，有哪些注意事项？
8.0.21 提供 ALTER INSTANCE DISABLE INNODB REDO_LOG;（恢复用 ENABLE）。文中在 100 万行（1.8G）load data 场景测试，禁用 redo log 相比启用有 10%30% 的执行时间差异（如 1000 万行 load data：禁用 redo 约 2 分30秒，启用约 3 分37秒；add index：禁用约 3639 秒，启用约 47 秒）。要点：①禁用 redo log 不影响 binlog，主从仍可正常同步；②是实例级、不支持表级；③若发生 crash 将无法 recovery，OLTP 系统谨慎使用；④适用于大批量数据导入/建索引场景。
禁用 redo log 与仅调低双 1 参数（sync_binlog/innodb_flush_log_at_trx_commit）在导入性能上有何关系？
文中测试为叠加对比：禁用 redo log 的同时把 sync_binlog、innodb_flush_log_at_trx_commit 设为 0，load 1000 万行约 2 分30秒；启用 redo log 并恢复双 1 为 1 时约 3 分37秒。可见 redo log 的 IO 开销是导入瓶颈之一，禁用它能减少日志 IO；即便启用 redo，把双 1 调成 0 也能显著提速（实验末段启用 redo+双1=0 约 2 分49秒）。不过禁用 redo 会牺牲 crash 可恢复性，仅适合可重跑的批量导入，且必须保证该期间不崩溃。
MySQL 的 Semi-join 与 Materialization 子查询优化分别是什么，如何通过 optimizer_switch 控制？
二者都是 MySQL 5.6 引入、用于优化 IN (SELECT ...) 子查询（避免被改写成 DEPENDENT SUBQUERY）。Semi-join 把子查询当作“半联接”，利用 IN 只需每组返回一个值的语义去重，典型执行是把子查询结果物化进带主键去重的临时表再与外层联接（select_type=MATERIALIZED，文中扫描行数从 1000 降到 27）。Materialization 则是单纯把子查询结果物化成临时表（含主键/hash 索引去重）代入外查询，外层仍可能全表扫描，文中总扫描 100+9=109 行。二者通过 optimizer_switch 的 semijoin={on|off}、materialization={on|off} 开关控制；5.6 之前只有 exists 策略（无法关闭）。
为什么 DELETE 语句中的子查询无法享受 semijoin/materialization 优化，应如何改写？
文中指出 delete 无法使用 semijoin、materialization 优化策略，会以 exists 方式执行，外层 delete 表必须全表扫描。优化方法很简单：改写成 join（delete 不用担心重复行问题），例如把 DELETE ... WHERE a.bizCustomerIncoming_id IN (SELECT id FROM b WHERE ...) 改写为 DELETE a FROM a JOIN b ON a.bizCustomerIncoming_id=b.id AND b.cid='...'。这样即可走 join 的高效执行路径，避免对大表反复 exists 子查询。
为什么 WHERE a IN (SELECT ...) 这类子查询在 MySQL 中常被改写成 DEPENDENT SUBQUERY（关联子查询），有什么性能隐患？
直觉上会先执行子查询拿到结果集再代入外层，但 MySQL 优化器会把外层表“压入”子查询，改写成 EXISTS(SELECT ... AND 外表列=子查询列)，select_type 变为 DEPENDENT SUBQUERY。这意味着子查询无法独立于外层先执行，只能：扫描外层表每行→用该行去执行一次子查询→判断是否命中。文中 t1(100 行) 示例总扫描约 100+100*9=1000 行。隐患在于：若外层表很大，子查询就要被反复执行，性能极差，这正是后来需用 semijoin/materialization 或改写成 join 的原因。
MySQL Semi-join 优化有哪些使用限制？如何通过 EXPLAIN 判断实际采用了哪种 semijoin 策略？
限制：子查询须出现在顶层 WHERE/ON 后的 IN 或 =ANY，为单个 SELECT、无 group by/having、无 order by with limit、无 STRAIGHT_JOIN。实现策略有 Duplicate Weedout、FirstMatch、LooseScan、Materialize，对应 optimizer_switch 中 semijoin 及 firstmatch/loosescan/duplicateweedout/materialization 开关（默认全开）。EXPLAIN 识别：Extra 出现 Start temporary/End temporary 为 Duplicate Weedout，select_type=MATERIALIZED 且 table=
Semi-join Materialization 的 scan 与 lookup 两种子策略有何区别？为什么 MySQL 中子查询带 GROUP BY 就完全无法使用 semijoin？
Materialization-scan：先物化子查询（临时表以子查询列为 PK 去重），再从物化表扫起、按 PK 去 Country 表查找（文中扫 15+15+151=45 行）。Materialization-lookup：反过来，从驱动表（如 Country 239 行）每行去物化表按 PK 查找（238+2391=477 行），区别在联接顺序与谁做驱动。关于 GROUP BY：MariaDB 中子查询带 group by 仍可用 Semi-join Materialization；但 MySQL 中子查询一旦有 group by，所有 semijoin 策略都不可用，只能退化为 Non-semijoin materialization（普通 Materialization，select_type=SUBQUERY），需靠 materialization=on 单独生效。
MySQL 的 MRR（Multi-Range Read）优化是什么，能带来多大性能提升？
MRR 在通过二级索引范围扫描回表时，先把查到的主键排序，再按主键顺序去聚簇索引取数据，把原本的随机磁盘读转换成顺序读（用 CPU/内存换磁盘顺序 IO）。EXPLAIN 的 Extra 出现 Using MRR 即生效。文中 WHERE i0 BETWEEN 1 AND 2 走 idx_i0：开启 MRR 时 22400 行耗时 0.80s，关闭（mrr=off）达 2.56s，约 3 倍提升。MRR 需要 read_rnd_buffer_size 提供排序内存，该参数过小（如默认 256K 在较大范围下）会导致无法启用 MRR，调大（如 32M）后即可生效。
为什么 mrr_cost_based 应该保持开启、不要强行关闭 MRR？
optimizer_switch 中 mrr=on 仅表示允许 MRR，而 mrr_cost_based=on 表示由优化器基于代价决定是否真的使用 MRR。若把 mrr_cost_based 设为 off，则“任何能走 MRR 的都走”，这其实是个坑：MRR 并非总是更快，有时全表扫描反而更快。文中 i0 BETWEEN 1 AND 10 的对比：mrr_cost_based=off（强制 MRR）耗时 4.86s，而 mrr_cost_based=on（优化器改选 ALL 全表扫描）仅 1.52s。因此 mrr_cost_based 非常关键、建议始终打开，让优化器自行判断。
JOIN 优化中 NLJ 与 BNL 算法的根本区别是什么？优化器何时会选择 BNL？
NLJ（Index Nested-Loop Join）要求被驱动表的关联字段“能用到索引查找”，流程是取驱动表一行→拿关联值去被驱动表按索引查→循环；总计算次数≈驱动表满足条件的行数（如各 1 万行只需 1 万次）。BNL（Block Nested-Loop Join）则把驱动表数据放进 join buffer，扫描被驱动表每行去 buffer 中匹配（无序数组需遍历全部），总内存计算次数=驱动行×被驱动行（文中 11 万×1.9 万≈20 亿次），Extra 显示 Using join buffer (Block Nested Loop)。关键结论：算法选择根本在于“被驱动表能不能使用索引查找”，而不只是“有没有索引”；只要被驱动表无法用索引，优化器就退化为 BNL。8.0.18+ 这类场景会改用 hash join。
为什么两表关联字段字符集/校对规则不一致会导致索引失效、优化器退化为 BNL？与关联顺序有何关系？
字符集/校对不一致会使被驱动表上的关联索引“无法用于查找”（索引失效），从而触发 BNL。文中实测：t3(utf8) 与 t4(latin1) 关联，t3 做驱动表（utf8 连 latin1）时索引失效用 BNL；t4 做驱动表（latin1 连 utf8）时索引正常用 NLJ。规律是“被驱动表字段的字符集更大时索引可用，反之失效”（utf8 连 utf8mb4 正常，反向失效）。因此 join 各表关联字段应保持字符集与校对一致；无法改表时，可借 inner join 让优化器选“小字符集→大字符集”的顺序以保住 NLJ（也解释了为何文中 left join 慢、inner join 快）。
InnoDB 索引（B+Tree）由哪些 page 组成？如何估算索引高度，不同主键类型有何影响？
InnoDB 以 16KB page 为最小单元，B+Tree 由三类 page 组成：root page（创建时分配，page id 存于数据字典）、non-leaf/internal page（key 存子页最小值、value 存子页编号）、leaf page（存数据，level=0，含 infimum/supremum 伪记录，记录按 next-record 升序）。同层多页用双向链表连接，root 的 level 即树高。估算示例（各 90 万行）：int 主键 leaf 73 行/页、non-leaf 1203 行/页；bigint 72/928；uuid 63/357。可见主键越长（uuid 238 字节/记录 vs int 206 字节），leaf 每页行数越少、同样数据需更高树高，故主键宜短。
什么是 MySQL 的 derived_merge（派生表合并）优化？它的生效条件与限制是什么？
派生表（Derived table）是 FROM 子句中的子查询，视作独立表。5.7 前对派生表总是物化（materialize）成临时表再参与父查询；5.7 引入 derived_merge（受 optimizer_switch=‘derived_merge=ON’ 控制，默认开），可把符合条件的派生表与父查询表合并直接 JOIN，类似 Oracle 的子查询展开（unnesting）。限制：当派生子查询含 DISTINCT、GROUP BY、UNION/UNION ALL、HAVING、关联子查询、LIMIT/OFFSET、聚合操作时该特性无法生效，只能走物化+全表扫描。示例：派生表带 where a.rowguid='...'，开 derived_merge 可走主键索引，关掉则全表扫描。
派生表作为被驱动表无法走索引导致极慢时，如何改写 SQL 优化？有什么语义陷阱？
文中 SQL：bm_id LEFT JOIN (14 张子表 UNION ALL 形成的派生表 t) ON ... GROUP BY name 跑数小时。执行计划显示 t（约 164 万行）作为被驱动表被全表扫描 1.3 万次。因子查询含 UNION ALL，派生表无法用 derived_merge，只能物化全扫。改写思路：把驱动表 bm_id 也移入子查询，让各 UNION ALL 分支在子查询内部就 bm_id LEFT JOIN 子表（被驱动表 type=ref 走索引），再 UNION ALL 汇聚后只全扫一次派生表做分组，耗时从数小时降到 13s。陷阱：LEFT JOIN 改写后 bm_id 被关联多次会产生重复行，结果集会多数据；INNER JOIN 不会重复，需验证语义一致性（文中用带索引临时表比对确认）。
为什么一张只有主键的大表 select count(*) 会非常慢，即使执行计划显示走了覆盖索引？
5.7.24 上 select count(*) from api_runtime_log（571 万行）耗时 42.95s，EXPLAIN 显示 type=index、key=PRIMARY、Extra=Using index（覆盖索引全索引扫描）。原理：InnoDB 的 count(*) 先把索引读入内存缓冲再统计行数。InnoDB 优先选二级索引；若无二级索引则走主键（聚簇索引），而聚簇索引大小≈整表数据量（文中 10GB+），需把整个主键索引读进缓冲，极慢。文中用 sys.innodb_buffer_stats_by_table 验证：走主键时缓冲缓存了 1.08GiB（≈全表）。另：若有多二级索引会选 key_len 最小的；没有 PK 则全表扫描。
如何通过建立二级索引把大表 count(*) 从 40 多秒优化到 1 秒以内？
只需为表建一个二级索引：create index idx_rowguid on api_runtime_log(rowguid); 之后 count() 降到 0.89s，EXPLAIN 的 key 变为 idx_rowguid。原因：二级索引只存“索引列+主键列”，体积远小于聚簇索引（文中 500 万行测试表主键索引 1125MB、二级索引仅 55MB），count() 只需把几十 MB 的二级索引读入缓冲即可。再次用 innodb_buffer_stats_by_table 验证：走二级索引时缓冲仅缓存约 50MB。注意：表越大二级索引也越大（上千万/亿行时二级索引也会几百 MB~GB），此时应改用触发器+统计表、MyISAM、ETL 或 MySQL 8 并行查询等避免直接 count(*)。
MySQL 实例被 Linux OOM-killer 杀掉，常见原因有哪些？如何从内存规划角度排查？
OOM-killer 在系统内存严重不足时按“损失最小、收益最大”原则（杀掉使用内存多、子进程多、非系统进程的那个）释放内存，而数据库服务器上 MySQL 占用内存大易中招。常见原因：①MySQL 自身内存规划不当，尤其是 innodb_buffer_pool_size。官方建议专用机设物理内存的 80%，经验值 50%~80%。文中案例：buffer pool 76G + 每连接最大 160M × 最多 3000 连接 → 理论最大 545G，而物理内存仅 97G，必被 OOM。该实例写多读少，buffer pool 降到 50% 即可。②服务器上监控/定时脚本未做内存限制，高峰期抢占内存间接导致 MySQL 被误杀。
如何用 valgrind 的 memcheck 排查 MySQL 的潜在内存泄漏？
怀疑内存泄漏导致 OOM 时，可用 Valgrind 的 memcheck 分析：以 valgrind --tool=memcheck --leak-check=full 启动 mysqld 并 sysbench 模拟负载，进程退出后看报告。文中对比：开启 performance_schema 时 LEAK SUMMARY 显示 possibly lost 549,072 bytes、still reachable 446,492,944 bytes；完全禁用 P_S 后 possibly lost: 0、ERROR SUMMARY: 0，说明该测试下潜在泄漏来自 P_S。memcheck 还能检测未初始化内存、越界、双重释放等。
同一条 SQL 为什么在 MariaDB 正常、在 MySQL 5.7 却很慢？根源与解法是什么？
文中 SQL 在 MariaDB 正常、MySQL 5.7 极慢。看 5.7 执行计划 warnings 提示 id 字段“类型或排序规则转换”，致索引失效。核对结构：sbtest1.id 为 char(32) collation=utf8_bin，sbtest2.id 同为 char(32) 但 collation=utf8_general_ci，排序规则不同触发隐式转换、索引失效。解法：①把 sbtest1.id 的 collation 改为 utf8_general_ci；②或在 SQL 中对 sbtest1.id 用 CONVERT(…) 显式转换。结论：MySQL 5.7 对 collation 不一致敏感、会放弃索引；MariaDB 转换规则不同未影响效率。这也证明 JOIN 关联字段务必保持字符集/排序规则一致。
DBLE 中“关联条件是分片键、分片规则也相同”的 SQL 为何仍全分片扫描、性能极差？
DBLE 2.19.07.3 + MySQL 5.7.25，两张拆分表（cusvaa 67 行、cusm 7600 万行）以分片键 stringhash 各拆 8 片。客户疑惑：join 条件是分片键、规则相同为何慢。DBLE 层 EXPLAIN 发现：SQL 下发各分片后，从各分片全量取数、排序，再在 DBLE 中间层做 MERGE 与 JOIN（本应直接下发取结果后 MERGE）。根因：虽都叫 stringhash，但 rule.xml 中分片函数 function 配置不同（缺一致的 hashSlice 二元组），实际是两套分片规则，DBLE 路由判定不一致。修正分片函数配置并动态加载后秒级响应。结论：stringhash 需配齐 partitionLength[]、partitionCount[] 与 hashSlice；DBLE 只有判定规则完全一致才直接路由做 MERGE。