03-MySQL索引实战从B树到覆盖索引、change-buffer与选错索引排查参考丁奇《MySQL实战45讲》第4讲索引基础、第5讲索引维护、第9讲普通/唯一索引、第10讲选错索引、第16讲order by 与排序此处为索引铺垫。本文所有命令与回显均取自真实云服务器实验的完整留档日志未做任何编造宁缺毋假。一、引言一面被问懵的索引题面试官往往会这样开场“select * from t where a500走了索引为什么select a,b from t where a500反而要回表覆盖索引到底省了什么where b500b 在联合索引第二列为什么不一定用索引普通索引和唯一索引插入性能差在哪我analyze table之后执行计划怎么突然变了”这些问题光背八股是答不牢的。下面我们用10 万行真实数据 20 万行批量插入 一致性快照下的删插并发在实验机上逐一跑出来把EXPLAIN的每一列、每一次Using index/Using index condition都钉死在实机回显上。本文还包含一个反直觉的诚实结论本机普通索引插入1.2747s反而比唯一索引1.1440s慢了一点点——这正是理解 change buffer 生效条件的最好案例。二、实验环境说明所有实验在一台全新云服务器上完成从零安装 MySQL以下为实机回显$ uname -a Linux ecs-fb52-0002 6.8.0-106-generic #106-Ubuntu SMP PREEMPT_DYNAMIC Fri Mar 6 07:58:08 UTC 2026 x86_64 x86_64 x86_64 GNU/Linux $ free -h total used free shared buff/cache available Mem: 14Gi 510Mi 14Gi 2.5Mi 476Mi 14Gi Swap: 0B 0B 0B $ nproc 8 $ df -h / Filesystem Size Used Avail Use% Mounted on /dev/vda1 40G 3.4G 35G 9% / $ mysql --version bash: line 1: mysql: command not found -- 安装前确实没有 $ apt-get install -y mysql-server ... -- 安装完成 $ service mysql start ● mysql.service - MySQL Community Server Active: active (running) since Wed 2026-07-29 11:45:56 CST $ mysql -uroot -e select version(); show variables like innodb_version; version() 8.0.46-0ubuntu0.24.04.3 Variable_name Value innodb_version 8.0.46项目配置操作系统Ubuntu 24.04内核 6.8.0-106-genericx86_64CPU8 核内存14 GiB可用约 14 GiBSwap 为 0磁盘/dev/vda140G已用 3.4GMySQL 版本8.0.46innodb_version 8.0.46安装时间2026-07-29 11:45:56 CST合规说明本文不出现任何密码如涉及公网 IP 一律打码为124.70.***.***本次日志中出现的登录来源 IP 均已打码。基础造数建库b2建表t(id int primary key, a int, b int, index(a))并用递归 CTE 造10 万行a、b为floor(rand()*100000)的随机值贴近真实业务里“无序”的数据分布$ mysql -uroot -e use b2; create table t(id int primary key, a int, b int, index(a)); $ mysql -uroot -e use b2; set cte_max_recursion_depth200000; insert into t with recursive s(seq) as (select 1 union all select seq1 from s where seq100000) select seq, floor(rand()*100000), floor(rand()*100000) from s; $ mysql -uroot -e use b2; select count(*) from t; count(*) 100000三、索引的物理本质B树3.1 聚簇索引 vs 二级索引InnoDB 的索引本质是B树。主键索引聚簇索引的叶子节点存的是整行数据二级索引如index(a)的叶子节点存的是索引列 主键值。这一设计决定了后面所有“回表”“覆盖索引”的现象。聚簇索引主键 idB树 ┌─────────── 根节点 ───────────┐ │ 17 33 │ └───┬────────┼────────┬────────┘ ┌─────────┘ │ └─────────┐ ▼ ▼ ▼ [叶子页1] [叶子页2] [叶子页3] id1..16 id17..32 id33.. (整行数据) (整行数据) (整行数据) ◀── 双向链表 ──▶ ◀── 双向链表 ──▶ ◀── 双向链表 ──▶ 二级索引 index(a) B树 ┌─────────── 根节点 ───────────┐ │ a500 a2000 │ └───┬────────┼────────┬────────┘ ▼ ▼ [叶子页] [叶子页] (a1 ,id1) (a500,id500) (a2 ,id2) ... (a3 ,id3) 注意叶子只存 (a, 主键id)不含 b为什么是 B树而不是哈希表或二叉搜索树哈希表只能做等值、不能做范围直接出局红黑树等二叉树每个节点只存一个键树高超高100 万行大约要 20 层每层一次磁盘 IO根本扛不住随机读B树把多个键压在一个 16KB 的页里三到四层就能索引上亿行而且叶子节点之间用双向链表串起来范围查询和order by都极其顺滑。这也就是 InnoDB 在众多数据结构里最终选 B树的根本原因。回表的真实代价怎么估二级索引查到主键之后回表是一次针对聚簇索引的「按主键随机 IO」。如果只命中 1 行回表 1 次如果命中 1 万行就要做 1 万次随机 IO——这正是「小范围走索引很快、大范围反而不如全表扫描」的本质。所以「要不要回表」不是小事它直接决定了索引查询是快是慢。3.2 回表 vs 覆盖索引实机 EXPLAIN我们用EXPLAIN把“要不要回表”看得清清楚楚。1主键等值直接命中聚簇索引只扫 1 行$ mysql -uroot -e use b2; explain select * from t where id12345; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t const PRIMARY PRIMARY 4 const 1 100.00 NULL2二级索引等值必须回表select *需要b列但索引a里没有b所以先通过a50000在二级索引找到(a50000, id?)再拿id回聚簇索引取整行$ mysql -uroot -e use b2; explain select * from t where a50000; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t ref a a 5 const 1 100.00 NULLtyperef、keya、key_len5、ExtraNULL无Using index→ 发生了回表。3非覆盖的列组合仍然回表select a,b中b不在索引a里所以即使a命中索引也要回表取b$ mysql -uroot -e use b2; explain select a,b from t where a50000; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t ref a,ab a 5 const 1 100.00 NULL注意keya且ExtraNULL——没有Using index说明回表了。这里优化器在a单列和ab联合之间选了更窄的a。4联合索引能实现覆盖建联合索引ab(a,b)后叶子变成(a,b,id)于是select a,b可以被完全覆盖。日志里我们用force index(ab)验证$ mysql -uroot -e use b2; alter table t add index ab(a,b); $ mysql -uroot -e use b2; explain select a,b from t force index(ab) where a50000; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t ref ab ab 5 const 1 100.00 Using indexExtraUsing index证明a,b以及隐含的id都从索引ab直接返回零回表。顺带一提不加force index时优化器对select a,b where a50000反而选了更窄的索引akeya, ExtraNULL需要回表。这正说明——覆盖索引存不存在和优化器选不选它是两回事必要时可用force index/ 调整索引设计来引导。四、最左前缀原则与索引下推ICP4.1 最左前缀原则实机联合索引ab(a,b)的 B树先按a排序再按b排序。因此只有最左列a能用于范围/等值定位b单独出现无法走索引定位只能扫描整个索引。$ mysql -uroot -e use b2; show index from t; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Index_type Visible t 0 PRIMARY 1 id A 11 BTREE YES t 1 a 1 a A 13 BTREE YES t 1 ab 1 a A 62671 BTREE YES t 1 ab 2 b A 99978 BTREE YES注本实验a是rand()随机值采样统计给出的 Cardinality 偏低但ab的两列基数已接近 10 万属于高选择性。各场景EXPLAIN查询typekeykey_lenrowsExtra说明where a50000refa51NULL走a回表where a50000 and b50000refa51Using where用上ab在Using where过滤where b50000indexab10100000Using where; Using index全索引扫描 ab覆盖但非定位where a between 10000 and 20000 and b between 50000 and 60000rangeab1018304Using where; Using index最左列范围 覆盖重点看where b50000typeindex表示扫描整棵二级索引ab因为b不是最左前缀不能做索引查找但因为ab叶子含(a,b,id)恰好覆盖查询所需列所以Extra出现Using index——这是“全索引扫描”而非“全表扫描”比全表扫描省了回表但仍要扫描 10 万行。这正是“违反最左前缀不会报错只是退化”的实机证据。4.2 索引下推 ICPIndex Condition PushdownICP 允许存储引擎在遍历索引时就提前用WHERE里属于该索引的条件做过滤减少回表次数。看下面这个查询用force index(ab)条件a50000走索引定位而b like %123%无法用索引定位但b是索引ab的列于是被下推到引擎层在索引上直接判断建表时t上已增加列c使select *无法仅靠ab覆盖$ mysql -uroot -e use b2; alter table t add column c varchar(20) default x; $ mysql -uroot -e use b2; explain select * from t force index(ab) where a50000 and b like %123%; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t ref ab ab 5 const 1 11.11 Using index conditionExtra里的Using index condition就是 ICP 的实机标志存储引擎在扫描ab索引、a50000的条目时顺手用b like %123%在引擎层过滤只有真正满足的行才回表。对比关闭 ICP 后同样的语句Extra退化为Using where没有Using index condition意味着b like只能在回表后于 Server 层判断回表次数变多$ mysql -uroot -e use b2; set optimizer_switchindex_condition_pushdownoff; explain select * from t force index(ab) where a50000 and b like %123%; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t ref ab ab 5 const 1 11.11 Using where $ mysql -uroot -e use b2; set optimizer_switchindex_condition_pushdownon; select icp restored; icp restoredICP 流程force index(ab)a50000 and b like %123% 二级索引 ab 扫描 a50000 的条目 ├─ 取出 (a, b, 主键id) ├─ 引擎层顺手判断 b like %123% ? -- 索引下推提前过滤 │ ├─ 不满足 → 丢弃不回表 │ └─ 满足 → 用 id 回表取 * 回表次数大幅减少 └─ 返回结果经验ICP 只对联合索引里、定位列之后、且无法用于索引查找的剩余列条件生效。它把“先回表再过滤”优化成“先过滤再回表”在数据量大、命中行多时收益明显。五、change buffer写性能的另一半含诚实结论5.1 原理为什么需要 change buffer对于普通二级索引的插入/更新如果目标索引页不在 Buffer Pool 里InnoDB 不必立即把磁盘页读进来而是把“要改什么”先记到change buffer内存结构持久化在系统表空间并写 redo等以后该页被读入内存时再 merge。这样就把随机读盘变成了顺序写日志对写多读少、索引页常不在内存的场景收益明显。普通二级索引插入 a新值页不在内存 1) 记录到 change buffer内存 2) 记 redo log顺序写快 3) 事务提交返回 4) 后续该页被读入 / 后台 master 线程 → merge 进真正的数据页 避免了“为了写而先随机读盘” 唯一索引插入 a新值 必须立刻把索引页读入内存检查是否重复唯一性约束 页不在内存也要读入 无法走 change buffer查看变更缓冲相关参数$ mysql -uroot b2 -e show variables like innodb_change_buffer%; Variable_name Value innodb_change_buffer_max_size 25 -- 最多占 buffer pool 的 25% innodb_change_buffering all -- 所有操作都缓冲5.2 普通索引 vs 唯一索引本机实测没拉开差距建两张表t_normal(id,a,index(a))普通索引、t_uniq(id,a,unique(a))唯一索引各插入20 万行$ mysql -uroot b2 -e create table t_normal(id int primary key auto_increment, a int, index(a)); create table t_uniq(id int primary key auto_increment, a int, unique(a)); $ mysql -uroot b2 -e set cte_max_recursion_depth200001; set startnow(6); insert into t_normal(a) with recursive s(seq) as (select 1 union all select seq1 from s where seq200000) select floor(rand()*100000000) from s; select timestampdiff(microsecond,start,now(6))/1000000 as normal_index_seconds; normal_index_seconds 1.2747 $ mysql -uroot b2 -e set cte_max_recursion_depth200001; set startnow(6); insert ignore into t_uniq(a) with recursive s(seq) as (select 1 union all select seq1 from s where seq200000) select floor(rand()*100000000) from s; select timestampdiff(microsecond,start,now(6))/1000000 as unique_index_seconds; unique_index_seconds 1.1440场景索引类型20 万行插入耗时t_normal普通索引可走 change buffer1.2747 st_uniq唯一索引无法走 change buffer1.1440 s反直觉但诚实的结论本机普通索引1.2747s反而比唯一索引1.1440s慢了约 0.13s两者根本没有拉开差距普通索引甚至略慢。为什么会这样关键在于——change buffer 只在“目标索引页不在内存”时才有收益。本机 14GiB 内存、Buffer Pool 充足我们用show engine innodb status看一下实机状态BUFFER POOL AND MEMORY Buffer pool size 8192 Free buffers 5082 Database pages 3086 Modified db pages 1072 Buffer pool hit rate 1000 / 1000 INSERT BUFFER AND ADAPTIVE HASH INDEX Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0Buffer pool hit rate 1000 / 1000100% 命中Ibuf ... 0 merges——整个插入过程中 change buffer 几乎没被用到因为目标索引页几乎一直就在 Buffer Pool 里。此时唯一索引虽然“必须读页校验唯一性”但页本来就在内存读的是内存页代价极低普通索引虽然能“缓冲写”但页在内存时它同样要更新内存里的索引页反而多了一点 change buffer 的簿记开销。所以在本机“页全在内存”的条件下普通索引借不到 change buffer 的便宜唯一索引也没付出随机读盘的代价两者基本持平、普通索引微慢。5.3 何时差距才会显现原理推导change buffer 的收益来自“把本应发生的随机读盘推迟/合并”。它要显现必须满足目标二级索引页不常驻内存——通常发生在表很大索引远超 Buffer Pool、冷启动、或写多读少导致页被淘汰写操作多、且之后不会立即读这些页——这样 change buffer 才能累积、等 page 被淘汰前一次性 merge普通非唯一索引——唯一索引必须即时校验永远走不了 change buffer。一旦满足普通索引可以把“每插入一行就可能触发一次随机读盘”变成“顺序写 change buffer 后续批量 merge”在机械盘/大表/写多读少场景下能把写入吞吐提升一个数量级。这个结论反过来也告诉我们如果业务是“读多写少”或“数据量小、页都在内存”唯一索引并不会比普通索引慢选唯一索引还能顺带保证数据正确性——不要为了一个不存在的收益盲目把唯一索引改成普通索引。六、字符串前缀索引真实区分度与体积对比长字符串如邮箱建完整索引会很占空间。可以用前缀索引email(6)只索引前 6 个字符。但前缀长度要选得“区分度够”。建t_email(id, email varchar(64))灌 1 万行随机邮箱$ mysql -uroot b2 -e create table t_email(id int primary key auto_increment, email varchar(64)); set cte_max_recursion_depth10001; insert into t_email(email) with recursive s(seq) as (select 1 union all select seq1 from s where seq10000) select concat(substring(md5(rand()),1,floor(rand()*6)5),, elt(floor(rand()*4)1,gmail.com,qq.com,163.com,outlook.com)) from s; select count(*) from t_email; count(*) 10000计算各前缀长度的区分度count(distinct left(email,N))/count(*)$ mysql -uroot b2 -e select count(distinct email)/count(*) as full_sel, count(distinct left(email,4))/count(*) as sel4, count(distinct left(email,5))/count(*) as sel5, count(distinct left(email,6))/count(*) as sel6, count(distinct left(email,7))/count(*) as sel7, count(distinct left(email,8))/count(*) as sel8 from t_email; full_sel sel4 sel5 sel6 sel7 sel8 0.9999 0.9274 0.9954 0.9994 0.9999 0.9999对比完整索引与前缀索引的体积注意这里用的是innodb_index_stats的size估算单位 KB$ mysql -uroot b2 -e alter table t_email add index idx_e6(email(6)); alter table t_email add index idx_full(email); select index_name, stat_value*innodb_page_size/1024 as size_kb from mysql.innodb_index_stats where table_namet_email and stat_namesize; index_name size_kb PRIMARY 16.0000 idx_e6 192.0000 idx_full 304.0000 $ mysql -uroot b2 -e explain select id from t_email where emailabcqq.com; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t_email ref idx_e6,idx_full idx_e6 27 const 1 100.00 Using where索引前缀长度体积区分度查询命中idx_e66192 KBsel60.9994rows1走前缀索引idx_full完整304 KB0.9999—解读前 6 个字符的区分度已达 0.9994万分之一的冲突而索引体积从 304KB 降到 192KB省了约 37%。再长sel7/sel8区分度只多了 0.0005收益递减所以选6是性价比最高的前缀长度。前缀索引的代价由于只存了前缀无法用于ORDER BY/GROUP BY的覆盖且LIKE %xxx这类后缀匹配也用不上。它最适合“等值查询且前缀区分度足够”的场景。七、一致性读 vs 当前读undo 链实测178 倍差距InnoDB 的 MVCC 让“普通SELECT”走一致性读快照读它要沿 undo 链一路回溯拼出本事务开始时的数据版本。而lock in share mode/for update走当前读直接读最新已提交版本。两者代价可以天差地别。实验建t_undo(id,c)c1会话 A 开启一致性快照并先读一次c1然后另一个会话对c狂做10 万次update使c累加到 100001最后在会话 A 里用 profiling 对比两种读-- 会话 A一致性快照 mysql start transaction with consistent snapshot; mysql select * from t_undo where id1; -- 第一次读c1 ---------- | id | c | ---------- | 1 | 1 | ---------- -- 另一个会话10 万次累加 mysql call hammer(); -- update t_undo set cc1 where id1循环 10 万次 mysql select c from t_undo where id1; -- 最新值 c 100001 -- 回到会话 A开 profiling 对比 mysql set profiling1; mysql select * from t_undo where id1; -- 一致性读快照 ---------- | id | c | ---------- | 1 | 1 | -- 仍是 1要穿越 10 万版 undo ---------- mysql select * from t_undo where id1 lock in share mode; -- 当前读 ------------ | id | c | ------------ | 1 | 100001 | -- 最新值直接读 ------------ mysql show profiles; -------------------------------------------------------------------------- | Query_ID | Duration | Query | -------------------------------------------------------------------------- | 1 | 0.04572950 | select * from t_undo where id1 | | 2 | 0.00025750 | select * from t_undo where id1 lock in share mode | --------------------------------------------------------------------------读方式含义结果耗时一致性读快照读沿 undo 链回溯到事务开始时的版本c10.04572950 s当前读加锁读直接读最新已提交版本c1000010.00025750 s两者相差约 178 倍0.0457 / 0.0002575 ≈ 177.6。根因就是一致性读为了还原“10 万次 update 之前”的版本必须沿着长达 10 万节点的 undo 链逐版本回溯、应用回滚而当前读直接拿聚簇索引上的最新行一步到位。排障启示长事务 热点行被频繁更新 一致性读极其昂贵。这也是为什么“一个开着没提交事务的SELECT”有时能把一条本该毫秒级的查询拖慢上百倍。线上遇到“明明走主键、却奇慢无比”的诡异SELECT先怀疑它是不是在一个老快照里读、背后压着一条超长的 undo 链。八、选错索引——一致性快照下的删插并发“选错索引”通常不是优化器真笨而是统计信息不准或数据分布使然。本实验复现一个教科书级场景一个长事务的一致性快照叠加另一个会话的“删全表 重插 10 万行”导致快照内看到的行版本数翻倍优化器据此给出了全表扫描。先重置并analyze表确保统计信息新鲜$ mysql -uroot b2 -e set cte_max_recursion_depth200000; insert into t(id,a,b) with recursive s(seq) as (select 1 union all select seq1 from s where seq100000) select seq, floor(rand()*100000), floor(rand()*100000) from s; analyze table t; Table Op Msg_type Msg_text b2.t analyze status OK会话 A开启一致性快照并统计此时表有 10 万行mysql start transaction with consistent snapshot; mysql select count(*) from t; ---------- | count(*) | ---------- | 100000 | ----------与此同时另一个会话删除全部数据并重插 10 万行$ mysql -uroot b2 -e delete from t; set cte_max_recursion_depth200000; insert into t(id,a,b) with recursive s(seq) as (select 1 union all select seq1 from s where seq100000) select seq, floor(rand()*100000), floor(rand()*100000) from s;此时回到会话 A快照未提交执行计划已经“疯了”——本应走索引a的范围查询居然变成全表扫描$ mysql -uroot b2 -e explain select * from t where a between 10000 and 20000; show index from t; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t ALL a,ab NULL NULL NULL 100320 39.93 Using where Table Non_unique Key_name Seq Column Cardinality t 0 PRIMARY 1 id 100320 t 1 a 1 a 64313 t 1 ab 1 a 61053 t 1 ab 2 b 92649typeALL、keyNULL、rows100320——全表扫描索引a/ab全被放弃。原因在会话 A 的一致性快照里原来的 10 万行被 delete 标记但未真正删除对快照仍可见加上新插入的 10 万行可见版本数约为 20 万。优化器基于这个“翻倍”的可见行数估算成本认为扫全表比走索引更划算于是rows被估到 100320。show index的 Cardinality 也出现失真a 仅 64313、ab(a) 仅 61053。会话 A 提交后统计信息恢复、快照消失再analyze table执行计划立刻回到正确轨道$ mysql -uroot b2 -e commit; -- 会话 A 提交外部另测用等价操作 $ mysql -uroot b2 -e analyze table t; explain select * from t where a between 10000 and 20000; explain select * from t force index(a) where a between 10000 and 20000; Table Op Msg_type Msg_text b2.t analyze status OK id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t range a,ab ab 5 NULL 16856 100.00 Using index condition id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t range a a 5 NULL 1 100.00 Using index condition阶段typekeyrows说明快照内删插并发后ALLNULL100320全表扫描选错索引提交 analyze 后rangeab16856恢复正常走索引强制force index(a)rangea1索引被显式指定结论长事务的一致性快照 并发大量删插会让优化器看到的“可见行版本数”严重失真进而选错索引本例从range退化到ALL。解决手段先analyze table更新统计信息本例提交后 analyze 即从ALL恢复为range若确认是优化器误判、且统计已新再用force index引导。顺带呼应第七节这个事故的根因正是“一致性快照 长 undo 链”它既能让单条SELECT慢 178 倍也能让优化器基于失真的行数估算选错索引——长事务是线上索引与性能问题的隐形推手。九、踩坑记录真实报错/反直觉点普通索引插入反而比唯一索引慢第五节本机 14GiB 内存、Buffer Pool 命中率 100%Ibuf 0 mergeschange buffer 根本没被触发于是“普通索引应更快”的教科书结论在本机不成立。记住change buffer 只在目标页不在内存时才有收益。前缀索引区分度别拍脑袋本例sel40.9274还很低、sel60.9994才够用若只取 4 个字符会有约 7% 的冲突率前缀索引的等值查询可能返回多行甚至漏判。用count(distinct left(col,N))实测选 N。一致性读被长 undo 链拖慢 178 倍第七节开着一致性快照去读一个被更新了 10 万次的热点行耗时从 0.00026s 涨到 0.0457s。选错索引源于一致性快照第八节一个未提交的长事务 并发删插会让EXPLAIN从range退化成ALLanalyze table是比force index更治本的解法。十、面试高频问答Q1聚簇索引和二级索引的区别回表是什么聚簇索引叶子存整行二级索引叶子存“索引列主键”。用二级索引查到主键后再去聚簇索引取完整行这一步叫“回表”。若查询所需列全部包含在二级索引叶子中则为“覆盖索引”免回表EXPLAINExtraUsing index。本实验select * where a50000的ExtraNULL就证明发生了回表。Q2什么是覆盖索引有什么好处覆盖索引指查询的列都在某个索引的叶子节点里不需要回表。好处是省一次聚簇索引的随机 IO。本实验select a,b from t force index(ab) where a50000打出ExtraUsing index零回表。Q3最左前缀原则是什么where b50000会怎样联合索引(a,b)先按a排序再按b。where b50000无法用索引定位会退化成全索引扫描typeindex因覆盖而Using index仍要扫全部索引条目只是不回表。Q4索引下推 ICP 是什么ICP 让存储引擎在扫描索引时就用上WHERE中属于该索引的条件过滤减少回表次数。EXPLAINExtra出现Using index condition即表示启用关闭index_condition_pushdown后同一语句退化为Using where。Q5普通索引和唯一索引在性能上有什么差异查询性能几乎无差。写性能差异本应来自 change buffer唯一索引插入必须立即读页校验唯一性无法利用 change buffer普通索引可借 change buffer 把随机读转成顺序写。但本实验 20 万行插入实测普通索引 1.2747s、唯一索引 1.1440s反而没拉开差距——因为本机 14GiB 内存、Buffer Pool 命中率 100%目标页都在内存change buffer 无从发挥。change buffer 只有在“页不在内存”的大表/写多读少场景才会显现收益。Q6为什么一致性读可能比当前读慢上百倍一致性读要沿 undo 链回溯到事务开始时的版本。本实验对一行做 10 万次 update 后快照内的一致性读耗时 0.0457s而当前读lock in share mode仅 0.00026s相差约 178 倍。长事务 热点行频繁更新时尤需警惕。Q7什么时候该用analyze table/force index统计信息失真如本实验长事务快照 并发删插使EXPLAIN从range退化成ALL导致计划异常时先analyze table若确认优化器误判且统计已新再用force index引导。十一、总结本文用真实云服务器、10 万行与 20 万行数据把索引相关的核心机制逐一钉死在 EXPLAIN 回显上B树结构决定了“回表”与“覆盖索引”的存在ExtraUsing index是覆盖索引的实机标志而typeindex则意味着退化成了全索引扫描。最左前缀意味着违反它不会报错只是退化为全索引扫描联合索引里列的先后顺序直接决定了哪些查询能命中where b50000实测typeindex扫 10 万行。ICP以Using index condition显形把过滤下推到引擎层、减少无谓的回表关闭index_condition_pushdown后同一语句退化为Using where。change buffer是普通索引的写性能利器但对唯一索引完全无效且只在目标页不在内存时才有收益。本实验 20 万行插入普通索引 1.2747s、唯一索引 1.1440s——因 Buffer Pool 命中率 100%、change buffer 0 merges差距不仅没显现、普通索引反而微慢。这是比“教科书结论”更诚实、也更有教学价值的实机证据。前缀索引用count(distinct left(col,N))选长度本例 6 即可达 0.9994 区分度索引体积从 304KB 降到 192KB。一致性读 vs 当前读快照读沿 10 万节点 undo 链回溯比当前读慢约178 倍0.0457s vs 0.00026s。“选错索引”多源于统计信息失真或长事务快照本实验在一致性快照 并发删插下EXPLAIN从range/rows16856退化到ALL/rows100320analyze table后恢复。长事务是索引与性能问题的隐形推手。索引不是银弹而是「用空间换时间、用写成本换读性能」的取舍。把回表、覆盖、最左前缀、ICP、change buffer、一致性读这几件事在脑子里跑通再配合实机EXPLAIN就能在设计与排障时做到心中有数、手下有谱。下一讲04 篇我们将进入慢 SQL 排查count 写法对比、order by 排序模式与 8.0 排序机制演进、隐式转换导致索引失效以及 JOIN 的 INLJ 与 Hash Join 实测。本文实验均在真实云服务器完成输出为实机回显。
03-MySQL索引实战：从B+树到覆盖索引、change-buffer与选错索引排查
发布时间：2026/7/30 5:03:31
03-MySQL索引实战从B树到覆盖索引、change-buffer与选错索引排查参考丁奇《MySQL实战45讲》第4讲索引基础、第5讲索引维护、第9讲普通/唯一索引、第10讲选错索引、第16讲order by 与排序此处为索引铺垫。本文所有命令与回显均取自真实云服务器实验的完整留档日志未做任何编造宁缺毋假。一、引言一面被问懵的索引题面试官往往会这样开场“select * from t where a500走了索引为什么select a,b from t where a500反而要回表覆盖索引到底省了什么where b500b 在联合索引第二列为什么不一定用索引普通索引和唯一索引插入性能差在哪我analyze table之后执行计划怎么突然变了”这些问题光背八股是答不牢的。下面我们用10 万行真实数据 20 万行批量插入 一致性快照下的删插并发在实验机上逐一跑出来把EXPLAIN的每一列、每一次Using index/Using index condition都钉死在实机回显上。本文还包含一个反直觉的诚实结论本机普通索引插入1.2747s反而比唯一索引1.1440s慢了一点点——这正是理解 change buffer 生效条件的最好案例。二、实验环境说明所有实验在一台全新云服务器上完成从零安装 MySQL以下为实机回显$ uname -a Linux ecs-fb52-0002 6.8.0-106-generic #106-Ubuntu SMP PREEMPT_DYNAMIC Fri Mar 6 07:58:08 UTC 2026 x86_64 x86_64 x86_64 GNU/Linux $ free -h total used free shared buff/cache available Mem: 14Gi 510Mi 14Gi 2.5Mi 476Mi 14Gi Swap: 0B 0B 0B $ nproc 8 $ df -h / Filesystem Size Used Avail Use% Mounted on /dev/vda1 40G 3.4G 35G 9% / $ mysql --version bash: line 1: mysql: command not found -- 安装前确实没有 $ apt-get install -y mysql-server ... -- 安装完成 $ service mysql start ● mysql.service - MySQL Community Server Active: active (running) since Wed 2026-07-29 11:45:56 CST $ mysql -uroot -e select version(); show variables like innodb_version; version() 8.0.46-0ubuntu0.24.04.3 Variable_name Value innodb_version 8.0.46项目配置操作系统Ubuntu 24.04内核 6.8.0-106-genericx86_64CPU8 核内存14 GiB可用约 14 GiBSwap 为 0磁盘/dev/vda140G已用 3.4GMySQL 版本8.0.46innodb_version 8.0.46安装时间2026-07-29 11:45:56 CST合规说明本文不出现任何密码如涉及公网 IP 一律打码为124.70.***.***本次日志中出现的登录来源 IP 均已打码。基础造数建库b2建表t(id int primary key, a int, b int, index(a))并用递归 CTE 造10 万行a、b为floor(rand()*100000)的随机值贴近真实业务里“无序”的数据分布$ mysql -uroot -e use b2; create table t(id int primary key, a int, b int, index(a)); $ mysql -uroot -e use b2; set cte_max_recursion_depth200000; insert into t with recursive s(seq) as (select 1 union all select seq1 from s where seq100000) select seq, floor(rand()*100000), floor(rand()*100000) from s; $ mysql -uroot -e use b2; select count(*) from t; count(*) 100000三、索引的物理本质B树3.1 聚簇索引 vs 二级索引InnoDB 的索引本质是B树。主键索引聚簇索引的叶子节点存的是整行数据二级索引如index(a)的叶子节点存的是索引列 主键值。这一设计决定了后面所有“回表”“覆盖索引”的现象。聚簇索引主键 idB树 ┌─────────── 根节点 ───────────┐ │ 17 33 │ └───┬────────┼────────┬────────┘ ┌─────────┘ │ └─────────┐ ▼ ▼ ▼ [叶子页1] [叶子页2] [叶子页3] id1..16 id17..32 id33.. (整行数据) (整行数据) (整行数据) ◀── 双向链表 ──▶ ◀── 双向链表 ──▶ ◀── 双向链表 ──▶ 二级索引 index(a) B树 ┌─────────── 根节点 ───────────┐ │ a500 a2000 │ └───┬────────┼────────┬────────┘ ▼ ▼ [叶子页] [叶子页] (a1 ,id1) (a500,id500) (a2 ,id2) ... (a3 ,id3) 注意叶子只存 (a, 主键id)不含 b为什么是 B树而不是哈希表或二叉搜索树哈希表只能做等值、不能做范围直接出局红黑树等二叉树每个节点只存一个键树高超高100 万行大约要 20 层每层一次磁盘 IO根本扛不住随机读B树把多个键压在一个 16KB 的页里三到四层就能索引上亿行而且叶子节点之间用双向链表串起来范围查询和order by都极其顺滑。这也就是 InnoDB 在众多数据结构里最终选 B树的根本原因。回表的真实代价怎么估二级索引查到主键之后回表是一次针对聚簇索引的「按主键随机 IO」。如果只命中 1 行回表 1 次如果命中 1 万行就要做 1 万次随机 IO——这正是「小范围走索引很快、大范围反而不如全表扫描」的本质。所以「要不要回表」不是小事它直接决定了索引查询是快是慢。3.2 回表 vs 覆盖索引实机 EXPLAIN我们用EXPLAIN把“要不要回表”看得清清楚楚。1主键等值直接命中聚簇索引只扫 1 行$ mysql -uroot -e use b2; explain select * from t where id12345; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t const PRIMARY PRIMARY 4 const 1 100.00 NULL2二级索引等值必须回表select *需要b列但索引a里没有b所以先通过a50000在二级索引找到(a50000, id?)再拿id回聚簇索引取整行$ mysql -uroot -e use b2; explain select * from t where a50000; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t ref a a 5 const 1 100.00 NULLtyperef、keya、key_len5、ExtraNULL无Using index→ 发生了回表。3非覆盖的列组合仍然回表select a,b中b不在索引a里所以即使a命中索引也要回表取b$ mysql -uroot -e use b2; explain select a,b from t where a50000; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t ref a,ab a 5 const 1 100.00 NULL注意keya且ExtraNULL——没有Using index说明回表了。这里优化器在a单列和ab联合之间选了更窄的a。4联合索引能实现覆盖建联合索引ab(a,b)后叶子变成(a,b,id)于是select a,b可以被完全覆盖。日志里我们用force index(ab)验证$ mysql -uroot -e use b2; alter table t add index ab(a,b); $ mysql -uroot -e use b2; explain select a,b from t force index(ab) where a50000; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t ref ab ab 5 const 1 100.00 Using indexExtraUsing index证明a,b以及隐含的id都从索引ab直接返回零回表。顺带一提不加force index时优化器对select a,b where a50000反而选了更窄的索引akeya, ExtraNULL需要回表。这正说明——覆盖索引存不存在和优化器选不选它是两回事必要时可用force index/ 调整索引设计来引导。四、最左前缀原则与索引下推ICP4.1 最左前缀原则实机联合索引ab(a,b)的 B树先按a排序再按b排序。因此只有最左列a能用于范围/等值定位b单独出现无法走索引定位只能扫描整个索引。$ mysql -uroot -e use b2; show index from t; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Index_type Visible t 0 PRIMARY 1 id A 11 BTREE YES t 1 a 1 a A 13 BTREE YES t 1 ab 1 a A 62671 BTREE YES t 1 ab 2 b A 99978 BTREE YES注本实验a是rand()随机值采样统计给出的 Cardinality 偏低但ab的两列基数已接近 10 万属于高选择性。各场景EXPLAIN查询typekeykey_lenrowsExtra说明where a50000refa51NULL走a回表where a50000 and b50000refa51Using where用上ab在Using where过滤where b50000indexab10100000Using where; Using index全索引扫描 ab覆盖但非定位where a between 10000 and 20000 and b between 50000 and 60000rangeab1018304Using where; Using index最左列范围 覆盖重点看where b50000typeindex表示扫描整棵二级索引ab因为b不是最左前缀不能做索引查找但因为ab叶子含(a,b,id)恰好覆盖查询所需列所以Extra出现Using index——这是“全索引扫描”而非“全表扫描”比全表扫描省了回表但仍要扫描 10 万行。这正是“违反最左前缀不会报错只是退化”的实机证据。4.2 索引下推 ICPIndex Condition PushdownICP 允许存储引擎在遍历索引时就提前用WHERE里属于该索引的条件做过滤减少回表次数。看下面这个查询用force index(ab)条件a50000走索引定位而b like %123%无法用索引定位但b是索引ab的列于是被下推到引擎层在索引上直接判断建表时t上已增加列c使select *无法仅靠ab覆盖$ mysql -uroot -e use b2; alter table t add column c varchar(20) default x; $ mysql -uroot -e use b2; explain select * from t force index(ab) where a50000 and b like %123%; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t ref ab ab 5 const 1 11.11 Using index conditionExtra里的Using index condition就是 ICP 的实机标志存储引擎在扫描ab索引、a50000的条目时顺手用b like %123%在引擎层过滤只有真正满足的行才回表。对比关闭 ICP 后同样的语句Extra退化为Using where没有Using index condition意味着b like只能在回表后于 Server 层判断回表次数变多$ mysql -uroot -e use b2; set optimizer_switchindex_condition_pushdownoff; explain select * from t force index(ab) where a50000 and b like %123%; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t ref ab ab 5 const 1 11.11 Using where $ mysql -uroot -e use b2; set optimizer_switchindex_condition_pushdownon; select icp restored; icp restoredICP 流程force index(ab)a50000 and b like %123% 二级索引 ab 扫描 a50000 的条目 ├─ 取出 (a, b, 主键id) ├─ 引擎层顺手判断 b like %123% ? -- 索引下推提前过滤 │ ├─ 不满足 → 丢弃不回表 │ └─ 满足 → 用 id 回表取 * 回表次数大幅减少 └─ 返回结果经验ICP 只对联合索引里、定位列之后、且无法用于索引查找的剩余列条件生效。它把“先回表再过滤”优化成“先过滤再回表”在数据量大、命中行多时收益明显。五、change buffer写性能的另一半含诚实结论5.1 原理为什么需要 change buffer对于普通二级索引的插入/更新如果目标索引页不在 Buffer Pool 里InnoDB 不必立即把磁盘页读进来而是把“要改什么”先记到change buffer内存结构持久化在系统表空间并写 redo等以后该页被读入内存时再 merge。这样就把随机读盘变成了顺序写日志对写多读少、索引页常不在内存的场景收益明显。普通二级索引插入 a新值页不在内存 1) 记录到 change buffer内存 2) 记 redo log顺序写快 3) 事务提交返回 4) 后续该页被读入 / 后台 master 线程 → merge 进真正的数据页 避免了“为了写而先随机读盘” 唯一索引插入 a新值 必须立刻把索引页读入内存检查是否重复唯一性约束 页不在内存也要读入 无法走 change buffer查看变更缓冲相关参数$ mysql -uroot b2 -e show variables like innodb_change_buffer%; Variable_name Value innodb_change_buffer_max_size 25 -- 最多占 buffer pool 的 25% innodb_change_buffering all -- 所有操作都缓冲5.2 普通索引 vs 唯一索引本机实测没拉开差距建两张表t_normal(id,a,index(a))普通索引、t_uniq(id,a,unique(a))唯一索引各插入20 万行$ mysql -uroot b2 -e create table t_normal(id int primary key auto_increment, a int, index(a)); create table t_uniq(id int primary key auto_increment, a int, unique(a)); $ mysql -uroot b2 -e set cte_max_recursion_depth200001; set startnow(6); insert into t_normal(a) with recursive s(seq) as (select 1 union all select seq1 from s where seq200000) select floor(rand()*100000000) from s; select timestampdiff(microsecond,start,now(6))/1000000 as normal_index_seconds; normal_index_seconds 1.2747 $ mysql -uroot b2 -e set cte_max_recursion_depth200001; set startnow(6); insert ignore into t_uniq(a) with recursive s(seq) as (select 1 union all select seq1 from s where seq200000) select floor(rand()*100000000) from s; select timestampdiff(microsecond,start,now(6))/1000000 as unique_index_seconds; unique_index_seconds 1.1440场景索引类型20 万行插入耗时t_normal普通索引可走 change buffer1.2747 st_uniq唯一索引无法走 change buffer1.1440 s反直觉但诚实的结论本机普通索引1.2747s反而比唯一索引1.1440s慢了约 0.13s两者根本没有拉开差距普通索引甚至略慢。为什么会这样关键在于——change buffer 只在“目标索引页不在内存”时才有收益。本机 14GiB 内存、Buffer Pool 充足我们用show engine innodb status看一下实机状态BUFFER POOL AND MEMORY Buffer pool size 8192 Free buffers 5082 Database pages 3086 Modified db pages 1072 Buffer pool hit rate 1000 / 1000 INSERT BUFFER AND ADAPTIVE HASH INDEX Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0Buffer pool hit rate 1000 / 1000100% 命中Ibuf ... 0 merges——整个插入过程中 change buffer 几乎没被用到因为目标索引页几乎一直就在 Buffer Pool 里。此时唯一索引虽然“必须读页校验唯一性”但页本来就在内存读的是内存页代价极低普通索引虽然能“缓冲写”但页在内存时它同样要更新内存里的索引页反而多了一点 change buffer 的簿记开销。所以在本机“页全在内存”的条件下普通索引借不到 change buffer 的便宜唯一索引也没付出随机读盘的代价两者基本持平、普通索引微慢。5.3 何时差距才会显现原理推导change buffer 的收益来自“把本应发生的随机读盘推迟/合并”。它要显现必须满足目标二级索引页不常驻内存——通常发生在表很大索引远超 Buffer Pool、冷启动、或写多读少导致页被淘汰写操作多、且之后不会立即读这些页——这样 change buffer 才能累积、等 page 被淘汰前一次性 merge普通非唯一索引——唯一索引必须即时校验永远走不了 change buffer。一旦满足普通索引可以把“每插入一行就可能触发一次随机读盘”变成“顺序写 change buffer 后续批量 merge”在机械盘/大表/写多读少场景下能把写入吞吐提升一个数量级。这个结论反过来也告诉我们如果业务是“读多写少”或“数据量小、页都在内存”唯一索引并不会比普通索引慢选唯一索引还能顺带保证数据正确性——不要为了一个不存在的收益盲目把唯一索引改成普通索引。六、字符串前缀索引真实区分度与体积对比长字符串如邮箱建完整索引会很占空间。可以用前缀索引email(6)只索引前 6 个字符。但前缀长度要选得“区分度够”。建t_email(id, email varchar(64))灌 1 万行随机邮箱$ mysql -uroot b2 -e create table t_email(id int primary key auto_increment, email varchar(64)); set cte_max_recursion_depth10001; insert into t_email(email) with recursive s(seq) as (select 1 union all select seq1 from s where seq10000) select concat(substring(md5(rand()),1,floor(rand()*6)5),, elt(floor(rand()*4)1,gmail.com,qq.com,163.com,outlook.com)) from s; select count(*) from t_email; count(*) 10000计算各前缀长度的区分度count(distinct left(email,N))/count(*)$ mysql -uroot b2 -e select count(distinct email)/count(*) as full_sel, count(distinct left(email,4))/count(*) as sel4, count(distinct left(email,5))/count(*) as sel5, count(distinct left(email,6))/count(*) as sel6, count(distinct left(email,7))/count(*) as sel7, count(distinct left(email,8))/count(*) as sel8 from t_email; full_sel sel4 sel5 sel6 sel7 sel8 0.9999 0.9274 0.9954 0.9994 0.9999 0.9999对比完整索引与前缀索引的体积注意这里用的是innodb_index_stats的size估算单位 KB$ mysql -uroot b2 -e alter table t_email add index idx_e6(email(6)); alter table t_email add index idx_full(email); select index_name, stat_value*innodb_page_size/1024 as size_kb from mysql.innodb_index_stats where table_namet_email and stat_namesize; index_name size_kb PRIMARY 16.0000 idx_e6 192.0000 idx_full 304.0000 $ mysql -uroot b2 -e explain select id from t_email where emailabcqq.com; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t_email ref idx_e6,idx_full idx_e6 27 const 1 100.00 Using where索引前缀长度体积区分度查询命中idx_e66192 KBsel60.9994rows1走前缀索引idx_full完整304 KB0.9999—解读前 6 个字符的区分度已达 0.9994万分之一的冲突而索引体积从 304KB 降到 192KB省了约 37%。再长sel7/sel8区分度只多了 0.0005收益递减所以选6是性价比最高的前缀长度。前缀索引的代价由于只存了前缀无法用于ORDER BY/GROUP BY的覆盖且LIKE %xxx这类后缀匹配也用不上。它最适合“等值查询且前缀区分度足够”的场景。七、一致性读 vs 当前读undo 链实测178 倍差距InnoDB 的 MVCC 让“普通SELECT”走一致性读快照读它要沿 undo 链一路回溯拼出本事务开始时的数据版本。而lock in share mode/for update走当前读直接读最新已提交版本。两者代价可以天差地别。实验建t_undo(id,c)c1会话 A 开启一致性快照并先读一次c1然后另一个会话对c狂做10 万次update使c累加到 100001最后在会话 A 里用 profiling 对比两种读-- 会话 A一致性快照 mysql start transaction with consistent snapshot; mysql select * from t_undo where id1; -- 第一次读c1 ---------- | id | c | ---------- | 1 | 1 | ---------- -- 另一个会话10 万次累加 mysql call hammer(); -- update t_undo set cc1 where id1循环 10 万次 mysql select c from t_undo where id1; -- 最新值 c 100001 -- 回到会话 A开 profiling 对比 mysql set profiling1; mysql select * from t_undo where id1; -- 一致性读快照 ---------- | id | c | ---------- | 1 | 1 | -- 仍是 1要穿越 10 万版 undo ---------- mysql select * from t_undo where id1 lock in share mode; -- 当前读 ------------ | id | c | ------------ | 1 | 100001 | -- 最新值直接读 ------------ mysql show profiles; -------------------------------------------------------------------------- | Query_ID | Duration | Query | -------------------------------------------------------------------------- | 1 | 0.04572950 | select * from t_undo where id1 | | 2 | 0.00025750 | select * from t_undo where id1 lock in share mode | --------------------------------------------------------------------------读方式含义结果耗时一致性读快照读沿 undo 链回溯到事务开始时的版本c10.04572950 s当前读加锁读直接读最新已提交版本c1000010.00025750 s两者相差约 178 倍0.0457 / 0.0002575 ≈ 177.6。根因就是一致性读为了还原“10 万次 update 之前”的版本必须沿着长达 10 万节点的 undo 链逐版本回溯、应用回滚而当前读直接拿聚簇索引上的最新行一步到位。排障启示长事务 热点行被频繁更新 一致性读极其昂贵。这也是为什么“一个开着没提交事务的SELECT”有时能把一条本该毫秒级的查询拖慢上百倍。线上遇到“明明走主键、却奇慢无比”的诡异SELECT先怀疑它是不是在一个老快照里读、背后压着一条超长的 undo 链。八、选错索引——一致性快照下的删插并发“选错索引”通常不是优化器真笨而是统计信息不准或数据分布使然。本实验复现一个教科书级场景一个长事务的一致性快照叠加另一个会话的“删全表 重插 10 万行”导致快照内看到的行版本数翻倍优化器据此给出了全表扫描。先重置并analyze表确保统计信息新鲜$ mysql -uroot b2 -e set cte_max_recursion_depth200000; insert into t(id,a,b) with recursive s(seq) as (select 1 union all select seq1 from s where seq100000) select seq, floor(rand()*100000), floor(rand()*100000) from s; analyze table t; Table Op Msg_type Msg_text b2.t analyze status OK会话 A开启一致性快照并统计此时表有 10 万行mysql start transaction with consistent snapshot; mysql select count(*) from t; ---------- | count(*) | ---------- | 100000 | ----------与此同时另一个会话删除全部数据并重插 10 万行$ mysql -uroot b2 -e delete from t; set cte_max_recursion_depth200000; insert into t(id,a,b) with recursive s(seq) as (select 1 union all select seq1 from s where seq100000) select seq, floor(rand()*100000), floor(rand()*100000) from s;此时回到会话 A快照未提交执行计划已经“疯了”——本应走索引a的范围查询居然变成全表扫描$ mysql -uroot b2 -e explain select * from t where a between 10000 and 20000; show index from t; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t ALL a,ab NULL NULL NULL 100320 39.93 Using where Table Non_unique Key_name Seq Column Cardinality t 0 PRIMARY 1 id 100320 t 1 a 1 a 64313 t 1 ab 1 a 61053 t 1 ab 2 b 92649typeALL、keyNULL、rows100320——全表扫描索引a/ab全被放弃。原因在会话 A 的一致性快照里原来的 10 万行被 delete 标记但未真正删除对快照仍可见加上新插入的 10 万行可见版本数约为 20 万。优化器基于这个“翻倍”的可见行数估算成本认为扫全表比走索引更划算于是rows被估到 100320。show index的 Cardinality 也出现失真a 仅 64313、ab(a) 仅 61053。会话 A 提交后统计信息恢复、快照消失再analyze table执行计划立刻回到正确轨道$ mysql -uroot b2 -e commit; -- 会话 A 提交外部另测用等价操作 $ mysql -uroot b2 -e analyze table t; explain select * from t where a between 10000 and 20000; explain select * from t force index(a) where a between 10000 and 20000; Table Op Msg_type Msg_text b2.t analyze status OK id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t range a,ab ab 5 NULL 16856 100.00 Using index condition id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t range a a 5 NULL 1 100.00 Using index condition阶段typekeyrows说明快照内删插并发后ALLNULL100320全表扫描选错索引提交 analyze 后rangeab16856恢复正常走索引强制force index(a)rangea1索引被显式指定结论长事务的一致性快照 并发大量删插会让优化器看到的“可见行版本数”严重失真进而选错索引本例从range退化到ALL。解决手段先analyze table更新统计信息本例提交后 analyze 即从ALL恢复为range若确认是优化器误判、且统计已新再用force index引导。顺带呼应第七节这个事故的根因正是“一致性快照 长 undo 链”它既能让单条SELECT慢 178 倍也能让优化器基于失真的行数估算选错索引——长事务是线上索引与性能问题的隐形推手。九、踩坑记录真实报错/反直觉点普通索引插入反而比唯一索引慢第五节本机 14GiB 内存、Buffer Pool 命中率 100%Ibuf 0 mergeschange buffer 根本没被触发于是“普通索引应更快”的教科书结论在本机不成立。记住change buffer 只在目标页不在内存时才有收益。前缀索引区分度别拍脑袋本例sel40.9274还很低、sel60.9994才够用若只取 4 个字符会有约 7% 的冲突率前缀索引的等值查询可能返回多行甚至漏判。用count(distinct left(col,N))实测选 N。一致性读被长 undo 链拖慢 178 倍第七节开着一致性快照去读一个被更新了 10 万次的热点行耗时从 0.00026s 涨到 0.0457s。选错索引源于一致性快照第八节一个未提交的长事务 并发删插会让EXPLAIN从range退化成ALLanalyze table是比force index更治本的解法。十、面试高频问答Q1聚簇索引和二级索引的区别回表是什么聚簇索引叶子存整行二级索引叶子存“索引列主键”。用二级索引查到主键后再去聚簇索引取完整行这一步叫“回表”。若查询所需列全部包含在二级索引叶子中则为“覆盖索引”免回表EXPLAINExtraUsing index。本实验select * where a50000的ExtraNULL就证明发生了回表。Q2什么是覆盖索引有什么好处覆盖索引指查询的列都在某个索引的叶子节点里不需要回表。好处是省一次聚簇索引的随机 IO。本实验select a,b from t force index(ab) where a50000打出ExtraUsing index零回表。Q3最左前缀原则是什么where b50000会怎样联合索引(a,b)先按a排序再按b。where b50000无法用索引定位会退化成全索引扫描typeindex因覆盖而Using index仍要扫全部索引条目只是不回表。Q4索引下推 ICP 是什么ICP 让存储引擎在扫描索引时就用上WHERE中属于该索引的条件过滤减少回表次数。EXPLAINExtra出现Using index condition即表示启用关闭index_condition_pushdown后同一语句退化为Using where。Q5普通索引和唯一索引在性能上有什么差异查询性能几乎无差。写性能差异本应来自 change buffer唯一索引插入必须立即读页校验唯一性无法利用 change buffer普通索引可借 change buffer 把随机读转成顺序写。但本实验 20 万行插入实测普通索引 1.2747s、唯一索引 1.1440s反而没拉开差距——因为本机 14GiB 内存、Buffer Pool 命中率 100%目标页都在内存change buffer 无从发挥。change buffer 只有在“页不在内存”的大表/写多读少场景才会显现收益。Q6为什么一致性读可能比当前读慢上百倍一致性读要沿 undo 链回溯到事务开始时的版本。本实验对一行做 10 万次 update 后快照内的一致性读耗时 0.0457s而当前读lock in share mode仅 0.00026s相差约 178 倍。长事务 热点行频繁更新时尤需警惕。Q7什么时候该用analyze table/force index统计信息失真如本实验长事务快照 并发删插使EXPLAIN从range退化成ALL导致计划异常时先analyze table若确认优化器误判且统计已新再用force index引导。十一、总结本文用真实云服务器、10 万行与 20 万行数据把索引相关的核心机制逐一钉死在 EXPLAIN 回显上B树结构决定了“回表”与“覆盖索引”的存在ExtraUsing index是覆盖索引的实机标志而typeindex则意味着退化成了全索引扫描。最左前缀意味着违反它不会报错只是退化为全索引扫描联合索引里列的先后顺序直接决定了哪些查询能命中where b50000实测typeindex扫 10 万行。ICP以Using index condition显形把过滤下推到引擎层、减少无谓的回表关闭index_condition_pushdown后同一语句退化为Using where。change buffer是普通索引的写性能利器但对唯一索引完全无效且只在目标页不在内存时才有收益。本实验 20 万行插入普通索引 1.2747s、唯一索引 1.1440s——因 Buffer Pool 命中率 100%、change buffer 0 merges差距不仅没显现、普通索引反而微慢。这是比“教科书结论”更诚实、也更有教学价值的实机证据。前缀索引用count(distinct left(col,N))选长度本例 6 即可达 0.9994 区分度索引体积从 304KB 降到 192KB。一致性读 vs 当前读快照读沿 10 万节点 undo 链回溯比当前读慢约178 倍0.0457s vs 0.00026s。“选错索引”多源于统计信息失真或长事务快照本实验在一致性快照 并发删插下EXPLAIN从range/rows16856退化到ALL/rows100320analyze table后恢复。长事务是索引与性能问题的隐形推手。索引不是银弹而是「用空间换时间、用写成本换读性能」的取舍。把回表、覆盖、最左前缀、ICP、change buffer、一致性读这几件事在脑子里跑通再配合实机EXPLAIN就能在设计与排障时做到心中有数、手下有谱。下一讲04 篇我们将进入慢 SQL 排查count 写法对比、order by 排序模式与 8.0 排序机制演进、隐式转换导致索引失效以及 JOIN 的 INLJ 与 Hash Join 实测。本文实验均在真实云服务器完成输出为实机回显。
